RE: Table Definitions DDS or SQL?

We use SQL to create most of our tables using the CREATE TABLE AS with a field 
reference file.  In the field reference file we normally create the fields as 
NOT NULL WITH DEFAULT.  The CREATE TABLE AS does on retain these field 
attributes but it does retain the absolute definition.  Our solution was to 
create a utility program that uses the ALTER TABLE command to set the NOT NULL 
WITH DEFAULT attribute on each field, it is called from the program containing 
the script to build the table.

The only issue we have is that with a large field reference file we exceed the 
SQL statement length and so have to create the table with some of the fields 
and then use th ALTER TABLE command to add additional fields.  This makes the 
rebuilding of this table very slow.

Scott Feddersen
The Buckle, Inc.

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On 
Behalf Of Wilt, Charles
Sent: Friday, October 06, 2006 7:07 AM
To: RPG programming on the AS400 / iSeries
Subject: 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