Re: SQL statements for physical and logical files

Yep, Buck - true that - I was addressing the misconception that these things are completely different animals. How many attributes are there at the object level for SQL tables and PFs? How many more at the *FILE level? How about at the low-level data space MI object level?

The item you speak of here is basically 1 item with far-reaching implications. So as to behavior, there is a lot of difference - in one case it worketh, in another it worketh not!

So I was speaking more at the structural level, the several MI-objects that comprise a PF or SQL table are the same - there is no new low-level thing that we see in DMPOBJ for SQL tables.

We DO see different values for the attributes, and some new attributes. This is the beauty of the object-based nature of this, our favorite system, eh?

Someone else mentioned that some try to make a huge distinction between PFs and table - so much that people are led to believe they can't use them in the same way in RPG, for example, or in SELECT statements, as another example.

What a lovely kind of thing we work on, my friend!!

Cheers
Vern

On 2/13/2017 11:48 AM, Buck Calabro wrote:

On 2/11/2017 10:12 AM, Nathan Andelin wrote:

The physical files (aka physical database objects) created via DDS are
essentially identical to the ones created by DDL.

The problem is how one defines 'essentially'.

The system I work on - in fact, ALL the systems I ever worked on - has
DDS-defined files which contain decimal data errors. For systems like
these, switching the file definition from DDS to DDL is hazardous. Here
is a simplified example:

QDDSSRC(DDE):
A R DDERCD
A ID 9B 0
A NUM1 9S 0
A CHAR1 20

QRPGLESRC(DDE):
* Test decimal data errors between DDS and SQL
h debug dftactgrp(*no) actgrp('QILE')

// 1) Create the DDS table DDE

// 2) Retrieve the DDL with IBM i Navigator

// 3) Change 'DDE' to 'DDL'
// CREATE TABLE BUCK.DDL (
// "ID" INTEGER NOT NULL DEFAULT 0 ,
// NUM1 NUMERIC(9, 0) NOT NULL DEFAULT 0 ,
// CHAR1 CHAR(20) CCSID 37 NOT NULL DEFAULT '' )
// RCDFMT DDLRCD;

// 4) Create this DDL table

// Now have two essentially identical tables; one DDS and one DDL.
// Let's insert some data and watch what happens.

fdde o a f 33 disk
fddl o a f 33 disk

*inlr = *on;

odde tadd lr
o 19 '123459789'
o 33 'Left out the binary
fld'

oddl tadd lr
o 19 '123459789'
o 33 'Left out the binary
fld'


You'll find that the DDS-described table 'insert' works flawlessly,
despite the 'ID' field not being output. For the sake of this example,
let's say that this field will be updated during a later step in the
batch process. But for now, the record is added with blanks in columns
1 - 4.

You'll also find that the DDL-defined table /will not allow/ the record
to be inserted. The job throws CPF5029 Data mapping error on member
DDL. Followed by Error message CPF4572 appeared during CLOSE (C G D F).

To summarise, going from CRTPF to CREATE TABLE for the exact same file
layout can and does result in my application going from 'working the
same as it has for 30 years' to 'will not run.'

So when someone says that DDS and DDL/SQL are like two windows into the
same database, I almost agree.