Creating DDL to define a DB2 table

Exporting HDB data into DB2® is a two-step process. The first step creates the DB2 table.

  1. Select option 1 Create DDL to define table, and press Enter.
    Figure 1. Export Step 1. Create DB2 table
                   HDB Export Option Menu
     
     Select an option then press Enter.
     1  1. Create DDL to define table
        2. Load data into table
        3. Upgrade table to current version
  2. In the Export HDB Data Set panel, press Enter.
    Figure 2. Export HDB Data Set - Create DDL
                            Export HDB Data Set - Create DDL 
     Command ===>                                                               
    
     Specify Create options then press Enter.
    
     HDB Name  . . . : CICSP1H 
     Data Set Name . : USR.CICSP1H.D03229.T092846.HDB
    
     Create Options
        Create Database 
        Create Storage Group 
    
     Include Clock Field Components          Summary Options
     1  1. Time and Count                    /  Include Sums of Squares
        2. Time only      
        3. Count only   
     

    For more information about the Export options, see Creating DDL to define a DB2 table.

CICS PA builds the JCL that contains the CREATE TABLE statement required to define the DB2 table for this HDB data set. The HDB name is used as the table name, however you can change this by editing the JCL.

Note: DSNTIAD, the sample Dynamic SQL program, is used to run the DDL that defines the DB2 table.
Start of changeReview the JCL and make any changes you require. Here are some things to look for:
  1. Start of changeYou can change the default DB2 settings from the CICS PA Profile Options Menu or the Options action bar menu.End of change If any required DB2 settings are not specified, CICS PA will insert parameter markers such as <setting> in the JCL stream.
  2. The storage space for indexes is set to a default arbitrary value. For information on how to calculate the space required for an index, see the DB2 UDB for z/OS® Administration Guide.
End of change

When the JCL is complete, submit it to create the DB2 table. Review the job output in SDSF to verify that the table was created successfully.

Figure 3. Edit JCL for HDB Export: Define DB2 table
EDIT       JCH.SPFTEMP1.CNTL                               Columns 00001 00072 
Command ===> ________________________________________________ Scroll ===> CSR_ 
****** ***************************** Top of Data ******************************
000001 //CICSPA  JOB ,NOTIFY=&SYSUID                                           
000002 //*  CICS PA V5R4 HDB - DDL TO DEFINE DB2 TABLE                       
000003 //RUNTIAD EXEC PGM=IKJEFT01,DYNAMNBR=20               
000004 //STEPLIB  DD DISP=SHR,DSN=DB2.PROD.SDSNLOAD          
000005 //         DD DISP=SHR,DSN=DB2.PROD.SDSNEXIT          
000006 //SYSTSPRT DD SYSOUT=*                                
000007 //SYSTSIN  DD *                                       
000008  DSN SYSTEM(DB2P)                                     
000009  RUN PROGRAM(DSNTIAD) -                               
000010      LIB('DB2.PROD.RUNLIB.LOAD') PLAN(DSNTIA91)       
000011 /*                                                    
000012 //SYSPRINT DD SYSOUT=*                                
000013 //SYSUDUMP DD SYSOUT=*                                
000014 //SYSIN    DD *                                       
000015    CREATE STOGROUP SYSDEFLT VOLUMES(DA0001) VCAT USER;
000016    CREATE DATABASE CICSPA;                            
000017    COMMIT;                                            
000018    CREATE TABLESPACE CICSP1H                          
000019           IN         CICSPA                           
000020           LOCKSIZE   ANY                              
000021           BUFFERPOOL BP0                              
000022           CLOSE      NO                               
000023           SEGSIZE    32                               
000024           USING      STOGROUP SYSDEFLT                
000025           PRIQTY     20              
000026           SECQTY     20              
000027           ERASE      NO ;            
000028    CREATE TABLE CICSPA.CICSP1H (     
000029       START_DATE             DATE,   
000030       START_TIME             TIME,   
000031       MVSID                  CHAR(4),
000032       APPLID                 CHAR(8),
000033       TRAN                   CHAR(4),
000034       TASKCNT                FLOAT,  
000035       RESPONSE_TIME          FLOAT,  
000036       RESPONSE_TIME_SSQ      FLOAT,  
000037       DISPATCH_COUNT         FLOAT,  
000038       DISPATCH_COUNT_SSQ     FLOAT,  
000039       DISPATCH_TIME          FLOAT,  
000040       DISPATCH_TIME_SSQ      FLOAT,  
000041       CPU_COUNT              FLOAT,  
000042       CPU_COUNT_SSQ          FLOAT,  
000043       CPU_TIME               FLOAT,  
000044       CPU_TIME_SSQ           FLOAT,  
000045       SUSPEND_COUNT          FLOAT,  
000046       SUSPEND_COUNT_SSQ      FLOAT,  
000047       SUSPEND_TIME           FLOAT,  
000048       SUSPEND_TIME_SSQ       FLOAT,  
000049       DISPWAIT_COUNT         FLOAT,  
000050       DISPWAIT_COUNT_SSQ     FLOAT,  
000051       DISPWAIT_TIME          FLOAT,  
000052       DISPWAIT_TIME_SSQ      FLOAT,             
000053       FCWAIT_COUNT           FLOAT,             
000054       FCWAIT_COUNT_SSQ       FLOAT,             
000055       FCWAIT_TIME            FLOAT,             
000056       FCWAIT_TIME_SSQ        FLOAT,             
000057       IRWAIT_COUNT           FLOAT,             
000058       IRWAIT_COUNT_SSQ       FLOAT,             
000059       IRWAIT_TIME            FLOAT,             
000060       IRWAIT_TIME_SSQ        FLOAT,             
000061       SC24UHWM_COUNT         FLOAT,             
000062       SC24UHWM_COUNT_SSQ     FLOAT,             
000063       SC31UHWM_COUNT         FLOAT,             
000064       SC31UHWM_COUNT_SSQ     FLOAT,             
000065       TSWAIT_COUNT           FLOAT,             
000066       TSWAIT_COUNT_SSQ       FLOAT,             
000067       TSWAIT_TIME            FLOAT,             
000068       TSWAIT_TIME_SSQ        FLOAT              
000069    ) IN CICSPA.CICSP1H;                         
000070                                                 
000071    CREATE TYPE 2 UNIQUE INDEX CICSPA.CICSP1H_IX 
000072           ON CICSPA.CICSP1H                   
000073         (                                       
000074         START_DATE,                             
000075         START_TIME,                             
000076         MVSID,                                  
000077         APPLID,                                 
000078         TRAN                                    
000079         )                                       
000080       USING STOGROUP   SYSDEFLT                                         
000081             PRIQTY     10                                               
000082             SECQTY     10                                               
000083             ERASE      NO                                               
000084             CLUSTER                                                     
000085             BUFFERPOOL BP0                                              
000086             CLOSE      NO                                               
000087 ;                                                                       
****** **************************** Bottom of Data ****************************