AS400 DB2 : Create a table with a DDS and autoincrement field

Question

Good morning all.

I have this DDS to create a file(table) :

A*-------------------------------------------------------------------------
A*     MyTable                                   
A*-------------------------------------------------------------------------
A          R AUTDAF06                                                      
A            D06IDUNT       2S 0       TEXT('ID UNIT')                     
A            D06DESUN      30A         TEXT('UNIT DESCRIPTION')            
A            D06CDUNT       3A         TEXT('CODE UNIT')                   
A*                                                                         
A            D06CRTTI        Z         TEXT('CREATION TIMESTAMP')          
A            D06CRTUS      30A         TEXT('CREATION USER')               
A            D06UPDTI        Z         TEXT('UPDATE TIMESTAMP')            
A                                      ALWNULL                             
A            D06UPDUS      30A         TEXT('UPDATE USER')                 
A            D06DELTI        Z         TEXT('DELETE TIMESTAMP')            
A                                      ALWNULL                             
A            D06DELUS      30A         TEXT('DELETE USER')                 
A                                                                          
A          K D06IDUNT                                                      
A          K D06DESUN
A          K D06CDUNT

I would like to define the field D06IDUNT as a unique auto-increment.

Do you have any idea?

score 1 · Answer 1 · edited May 23 '17 at 10:28

1

If you define the table using SQL, you can do this : How to AUTO_INCREMENT in db2?

More examples creating tables in DB2: https://www.toadworld.com/platforms/ibmdb2/w/wiki/6854.create-table-examples

edited May 23 '17 at 10:28

Community

1
1

answered Mar 28 '16 at 17:43

shup88

11
2

Thank you for your feedback. As said above, I was interested with a DDS solution. But there is no way to do this with it. Have a good day. – Benjamin Mar 28 '16 at 18:42

score 1 · Accepted Answer · answered Mar 28 '16 at 18:09

You can't create an auto increment with DDS. You have to use SQL DDL.

From an application standpoint, there's no difference between a file created with DDS and one created with SQL.

IBM provides functionality inside Navigator GUI to generated the SQL DDL for any SQL based object in addition to DDS generated PF and LF.

I took your DDS, created the file and the retrieved the following SQL DDL:

CREATE TABLE MYLIB.AUTDAF06 ( 
--  SQL150B   10   REUSEDLT(*NO) in table AUTDAF06 in MYLIB ignored. 
--  SQL1506   30   Key or attribute for AUTDAF06 in MYLIB ignored. 
    D06IDUNT NUMERIC(2, 0) NOT NULL DEFAULT 0 , 
    D06DESUN CHAR(30) CCSID 37 NOT NULL DEFAULT '' , 
    D06CDUNT CHAR(3) CCSID 37 NOT NULL DEFAULT '' , 
    D06CRTTI TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP , 
    D06CRTUS CHAR(30) CCSID 37 NOT NULL DEFAULT '' , 
    D06UPDTI TIMESTAMP DEFAULT NULL , 
    D06UPDUS CHAR(30) CCSID 37 NOT NULL DEFAULT '' , 
    D06DELTI TIMESTAMP DEFAULT NULL , 
    D06DELUS CHAR(30) CCSID 37 NOT NULL DEFAULT '' )   

    RCDFMT AUTDAF06   ; 

LABEL ON TABLE MYLIB.AUTDAF06 
    IS 'SO Example' ; 

LABEL ON COLUMN MYLIB.AUTDAF06 
( D06IDUNT TEXT IS 'ID UNIT' , 
    D06DESUN TEXT IS 'UNIT DESCRIPTION' , 
    D06CDUNT TEXT IS 'CODE UNIT' , 
    D06CRTTI TEXT IS 'CREATION TIMESTAMP' , 
    D06CRTUS TEXT IS 'CREATION USER' , 
    D06UPDTI TEXT IS 'UPDATE TIMESTAMP' , 
    D06UPDUS TEXT IS 'UPDATE USER' , 
    D06DELTI TEXT IS 'DELETE TIMESTAMP' , 
    D06DELUS TEXT IS 'DELETE USER' ) ; 

GRANT DELETE , INSERT , SELECT , UPDATE   
ON MYLIB.AUTDAF06 TO PUBLIC ; 

GRANT ALTER , DELETE , INDEX , INSERT , REFERENCES , SELECT , UPDATE   
ON MYLIB.AUTDAF06 TO MYLIB WITH GRANT OPTION ;

Note the error message: -- SQL1506 30 Key or attribute for AUTDAF06 in MYLIB ignored.

SQL doesn't support a table with a non-unique key. Your DDS doesn't have the UNIQUE keyword specified. Assuming that's just missing in your post, you can simply specify D06IDUNT, D06DESUN, D06CDUNT as the primary key via SQL during the CREATE TABLE.

Thank you for your response. As you said, there is no way to do it with DDS. Only SQL helps me to do that. — Benjamin, Mar 28 '16 at 18:41

AS400 DB2 : Create a table with a DDS and autoincrement field

2 Answers2