RE: Table Definitions DDS or SQL?

Scott,

If you use:

CREATE TABLE
 as(Select myfld from mytblref)
 with no data
 including column defaults

Then you'll get the NULL/NOT null specified in your SQL reference table.

In addition, you can add a "including identity column attributes" clause if you 
want attributes for an identity field to be pulled in from a field reference 
table.


HTH, 

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

-----Original Message-----
From: rpg400-l-bounces+cwilt=meaa.mea.com@xxxxxxxxxxxx 
[mailto:rpg400-l-bounces+cwilt=meaa.mea.com@xxxxxxxxxxxx] On 
Behalf Of Scott Feddersen
Sent: Friday, October 06, 2006 9:21 AM
To: RPG programming on the AS400 / iSeries
Subject: 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

-- 
This is the RPG programming on the AS400 / iSeries (RPG400-L) 
mailing list To post a message email: RPG400-L@xxxxxxxxxxxx 
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.


-- 
This is the RPG programming on the AS400 / iSeries (RPG400-L) 
mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.