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.

DSNTIAD, the sample Dynamic SQL program, is used to run the DDL that defines the DB2 table.

The options are:
Create Options
Select Create Database if you want the CREATE TABLE statement to be preceded by a CREATE DATABASE statement to define the DB2 database. You might need to ask your DB2 administrator to do this for you if you do not have sufficient authority.

Select Create Storage Group if you want the CREATE TABLE statement to be preceded by a CREATE STOGROUP statement to define the DB2 Storage Group.

Include Clock Field Components
CMF performance class Clock fields accumulate data for both their count and time components in the HDB. You have a choice as to which components to load into DB2. For example, selecting Time only will load the time component but not the count component. Time only is sufficient for most analysis requirements.
Summary Options
Specify Include Sums of Squares to load sum-of-square values into the DB2 Table. CICS PA always loads the Total. This allows you to calculate averages. Sums of Squares are required to calculate standard deviation and peak percentiles. Totals (and not Sums of Squares) is sufficient for most analysis requirements.
Upgrade Settings
For a details of the data set options you can specify when upgrading existing DB2 tables, see "Data Set Allocation Settings" in Define a Performance HDB.

Start of changeWhen you have specified the options, press Enter to generate the JCL and display it in an ISPF Edit session. Figure 1 shows an example.End of change

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 1. 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 V5R3 HDB - DDL TO DEFINE DB2 TABLE                       
000003 //RUNTIAD EXEC PGM=IKJEFT01,DYNAMNBR=20               
000004 //STEPLIB  DD DISP=SHR,DSN=DB2.V910.SDSNLOAD          
000005 //         DD DISP=SHR,DSN=DB2.V910.SDSNEXIT          
000006 //SYSTSPRT DD SYSOUT=*                                
000007 //SYSTSIN  DD *                                       
000008  DSN SYSTEM(DB2P)                                     
000009  RUN PROGRAM(DSNTIAD) -                               
000010      LIB('DB2.V910.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 ****************************