RE: Table Definitions DDS or SQL?

Birgitta,

I wouldn't normally argue with you, but I think I must disagree with the 
statement that "In SQL reference files are not supported."

Feel free to correct me if I'm wrong, but I don't believe DDS reference are 
"stored in the system tables" either.  If you change a DDS field reference 
table, the other files aren't changed until you either use CRTPF or CHGPF, 
specifying a source member that references the now changed field reference 
table.

If you recreate, ie. use CRTPF or CREATE TABLE, to update your files/tables 
when your field reference file changes, there's no difference between DDS 
created files and SQL created tables when it comes to the uses of a field 
reference file.

However, with DDS tables, you can use CHGPF and reference a source member.  
SQL's alter table doesn't allow this.  I don't see this as a limitation of SQL, 
instead it's simply a limitation of the built in tools.

You could easily write your own CHGSQLTBL command that accepted a source member 
name.  The tool would then use the specified source to create the new version 
of the table in a temporary library, compare the new version to the old; then 
build and execute the appropriate ALTER TABLE commands.

Actually, the change management package we use, Aldon LM(i), does exactly that 
as I understand it.
There's an option you can set, "Try available update for object replace" whose 
help text is as follows:
        Use this option to instruct LM(i) to use the CHGPF command
        or the SQL statement ALTER when updating an existing data 
        file in either a data set or distribution target library, 
        when it is feasible and expedient to do so.               
        
        Y (Yes)                                                 
            Before processing any change to a data object, LM(i)
            compares the current version of the file and the new
            file. If possible, LM(i) tries to change the current
            file rather than creating a new empty file, copying 
            the data from current file to the new file, and     
            rebuilding dependents.



Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx 
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of HauserBirgitta
Sent: Friday, October 06, 2006 1:38 AM
To: RPG programming on the AS400 / iSeries
Subject: Re: Table Definitions DDS or SQL?


IMO there is only one disavantage of SQL-DDL compared with 
DDS-definition: 
In SQL reference files are not supported.
Sure a SQL table can be created as:
Create Table ...
      as Select ... From ReferenceFile

But the references are not stored in the system tables, i.e. 
in the system tables only the absolute definition (such as 
Char(20) or Dec(11, 2)) are stored. So, if you have to change 
a field/column definition, it is not enough to do a simple 
change reference file and actualizing the tables with a 
simple CHGPF. You have to change the field/columns 
definitions manually in every table, where those fields are 
defined, by using the SQL command ALTER TABLE. 

Mit freunlichen Grüßen / Best regards 

Birgitta Hauser