DDL-Generierung mit ADMIN_INFO_SQL (DB2 11 for z/OS)

Hallo Forum,

ziemlich cool: ADMIN_INFO_SQL generiert DDL per SQL-Anweisung. Diese kostenlose Feature könnt das eine oder andere lizensierte Tool ergänzen.

Und so geht’s:

DECLARE GLOBAL TEMPORARY TABLE SESSION.OBJ_TABLE (
        CREATOR VARCHAR(128),
        TABLE VARCHAR(128)
) ON COMMIT PRESERVE ROWS
;
SELECT COUNT(*) FROM FINAL TABLE(
        INSERT INTO SESSION.OBJ_TABLE(CREATOR, TABLE)
            SELECT CREATOR, NAME FROM SYSIBM.SYSTABLES
            WHERE CREATOR = 'DSN8810'
);
CALL SYSPROC.ADMIN_INFO_SQL(
   'SESSION', 'LIST_TABLE-OBJ_TABLE',
   'DEFAULT',
   'NONE',
   'Y', 'Y', 'Y',
   'N',
   'NONE',
   'N',
   'R',
   'NONE',
   '11111.000.000',
   ?,
   ?
);

Das Ergebnis wird als Result-Table in 3 Spalten zurückgegeben:

• TID
• SEQNO – die laufende Numerierung
• TEXT – formatierte Ausgabe auf 72 Bytes länge, vorbereitet als z/OS Job

Ein Beispiel-Output:

TID SEQNO TEXT                                                                     
--- ----- ------------------------------------------------------------------------ 
1   1     //AISQL JOB MSGLEVEL=(1,1),CLASS=A,MSGCLASS=A,USER=SYSADM,               
1   2     //             PASSWORD=PASSWORD                                         
1   3     /*ROUTE PRINT STLVM14.USERID                                             
1   4     //SETUP    EXEC TSOBATCH,DB2LEV=DB2T                                     
1   5     //FT06F001 DD SYSOUT=A                                                   
1   6     //*  FOLLOWING SYSIN IS FOR USE OF SAMPLE PROG.                          
1   7     //*FOR THE DDL TO EXECUTE PROPERLY THE BUFFERPOOLS ON THE EC             
1   8     //*WILL HAVE TO BE SET. SEE THE REQUIRED BUFFERPOOLS AT THE              
1   9     //*BOTTOM OF THIS FILE.                                                  
1   10    /*                                                                       
1   11    //SYSTSIN DD *                                                           
1   12     DSN S(DB2T) R(1) T(1)                                                   
1   13    **BUFFERPOOL INFORMATION GOES HERE                                       
1   14    **SEE BOTTOM OF THE DDL FILE                                             
1   15     RUN PROGRAM(DSNTEP2)                                                    
1   16     END                                                                     
1   17    /*                                                                       
1   18    //SYSIN    DD *                                                          
1   19    *********************************************                            
1   20    ** ADMIN_INFO_SQL V1.7.1 2014/01/16 PM94681**                            
1   21    **            DDL Information              **                            
1   22    *********************************************                            
1   23                                                                             
1   24                                                                             
1   25    **DROP DATABASE DSN8D81A;                                                
1   26    **RETCODE = SQLCHECK(0,-204);                                            
1   27    **COMMIT;                                                                
1   28                                                                             
1   29    **DROP STOGROUP "DSN8G810" ;                                             
1   30    **RETCODE = SQLCHECK(0,-204);                                            
1   31    **COMMIT;                                                                
1   32     CREATE STOGROUP "DSN8G810" VOLUMES(                                     
1   33              '*'                                                            
1   34               ) VCAT DSNDB2T                                                
1   35     ;                                                                       
1   36     COMMIT;                                                                 
1   37                                                                             
1   38    **DATABASE DSN8D81A DBID is 260                                          
1   39     CREATE DATABASE DSN8D81A                                                
1   40              BUFFERPOOL BP0                                                 
1   41              INDEXBP BP0                                                    
1   42              STOGROUP DSN8G810                                              
1   43              CCSID EBCDIC                                                   
1   44     ;                                                                       
1   45     COMMIT;                                                                 
1   46    **DROP TABLESPACE DSN8D81A.DSN8S81P;                                     
1   47    **RETCODE = SQLCHECK(0,-204);                                            
1   48    **COMMIT;                                                                
1   49                                                                             
1   50    **TABLESPACE DSN8D81A.DSN8S81P DBID/OBID is 260/7                        
1   51     CREATE TABLESPACE DSN8S81P IN DSN8D81A                                  
1   52             USING STOGROUP DSN8G810 PRIQTY 160 SECQTY 80                    
1   53             ERASE NO                                                        
1   54             FREEPAGE 0 PCTFREE 5                                            
1   55             TRACKMOD YES                                                    
1   56             COMPRESS NO   GBPCACHE CHANGED                                  
1   57             SEGSIZE 4                                                       
1   58             CCSID EBCDIC                                                    
1   59             BUFFERPOOL BP0                                                  
1   60             LOCKSIZE ROW         LOCKMAX 0                                  
1   61             CLOSE NO                                                        
1   62     ;                                                                       
1   63     COMMIT;                                                                 
1   64                                                                             
1   65    **DROP TABLE "DSN8810"."ACT";                                            
1   66    **RETCODE = SQLCHECK(0,-204);                                            
1   67    **COMMIT;                                                                
1   68                                                                             
1   69    **TABLE "DSN8810"."ACT" DBID/OBID is 260/32                              
1   70     CREATE TABLE "DSN8810"."ACT" (                                          
1   71           "ACTNO" SMALLINT NOT NULL                                         
1   72          ,"ACTKWD" CHAR(6) FOR SBCS DATA NOT NULL                           
1   73          ,"ACTDESC" VARCHAR(20) FOR SBCS DATA NOT NULL                      
1   74           , PRIMARY KEY ("ACTNO")                                           
1   75          )                                                                  
1   76          IN DSN8D81A.DSN8S81P                                               
1   77              CCSID    EBCDIC                                                
1   78     ;                                                                       
1   79     COMMIT;                                                                 
1   80                                                                             
1   81    **DROP INDEX "DSN8810"."XACT1";                                          
1   82    **RETCODE = SQLCHECK(0,-204);                                            
1   83    **COMMIT;                                                                
1   84                                                                             
1   85    **INDEX "DSN8810"."XACT1" DBID/OBID is 260/33                            
1   86     CREATE  UNIQUE  INDEX "DSN8810"."XACT1"                                 
1   87       ON  "DSN8810"."ACT"  (                                                
1   88        "ACTNO" ASC)                                                         
1   89            NOT CLUSTER                                                      
1   90                                                                             
1   91            USING STOGROUP DSN8G810  PRIQTY 48                               
1   92            ERASE NO                                                         
1   93            FREEPAGE 0 PCTFREE 10                                            
1   94            GBPCACHE CHANGED                                                 
1   95            PIECESIZE        2 G                                             
1   96            COMPRESS NO                                                      
1   97            BUFFERPOOL BP0 CLOSE NO                                          
1   98     ;                                                                       
1   99     COMMIT;                                                                 
1   100   ...
1   ...
...