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?

Answer 1

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.