Creating DDL to define a DB2 table

CICS PA uses DSNTIAD, the sample Dynamic SQL program to run the DDL that defines the 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.

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.

DB2 Settings
Specify the required DB2 settings for your environment. CICS PA only provides a basic facility to load data into DB2. It does not provide any management or reporting capabilities when the data is in DB2.

If you omit any DB2 settings, CICS PA will insert parameter markers such as <setting> in the JCL stream.

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.
Note: 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.

Review the JCL then submit to create the DB2 table:

EDIT       JCH.SPFTEMP1.CNTL                               Columns 00001 00072 
Command ===> ________________________________________________ Scroll ===> CSR_ 
****** ***************************** Top of Data ******************************
000001 //CICSPA  JOB ,NOTIFY=&SYSUID                                           
000002 //*  CICS PA V3R2 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                                                       
000017    CREATE DATABASE CICSPA;                            
000018                                                       
000019    COMMIT;                                            
000020                                                       
000021    CREATE TABLESPACE CICSP1H                          
000022           IN         CICSPA                           
000023           LOCKSIZE   ANY                              
000024           BUFFERPOOL BP0                              
000025           CLOSE      NO                               
000026           SEGSIZE    32                               
000027           USING      STOGROUP SYSDEFLT                
000028           PRIQTY     20              
000029           SECQTY     20              
000030           ERASE      NO ;            
000031                                      
000032    CREATE TABLE CICSPA.CICSP1H (     
000033       START_DATE             DATE,   
000034       START_TIME             TIME,   
000035       MVSID                  CHAR(4),
000036       APPLID                 CHAR(8),
000037       TRAN                   CHAR(4),
000038       TASKCNT                FLOAT,  
000039       RESPONSE_TIME          FLOAT,  
000040       RESPONSE_TIME_SSQ      FLOAT,  
000041       DISPATCH_COUNT         FLOAT,  
000042       DISPATCH_COUNT_SSQ     FLOAT,  
000043       DISPATCH_TIME          FLOAT,  
000044       DISPATCH_TIME_SSQ      FLOAT,  
000045       CPU_COUNT              FLOAT,  
000046       CPU_COUNT_SSQ          FLOAT,  
000047       CPU_TIME               FLOAT,  
000048       CPU_TIME_SSQ           FLOAT,  
000049       SUSPEND_COUNT          FLOAT,  
000050       SUSPEND_COUNT_SSQ      FLOAT,  
000051       SUSPEND_TIME           FLOAT,  
000052       SUSPEND_TIME_SSQ       FLOAT,  
000053       DISPWAIT_COUNT         FLOAT,  
000054       DISPWAIT_COUNT_SSQ     FLOAT,  
000055       DISPWAIT_TIME          FLOAT,  
000056       DISPWAIT_TIME_SSQ      FLOAT,             
000057       FCWAIT_COUNT           FLOAT,             
000058       FCWAIT_COUNT_SSQ       FLOAT,             
000059       FCWAIT_TIME            FLOAT,             
000060       FCWAIT_TIME_SSQ        FLOAT,             
000061       IRWAIT_COUNT           FLOAT,             
000062       IRWAIT_COUNT_SSQ       FLOAT,             
000063       IRWAIT_TIME            FLOAT,             
000064       IRWAIT_TIME_SSQ        FLOAT,             
000065       SC24UHWM_COUNT         FLOAT,             
000066       SC24UHWM_COUNT_SSQ     FLOAT,             
000067       SC31UHWM_COUNT         FLOAT,             
000068       SC31UHWM_COUNT_SSQ     FLOAT,             
000069       TSWAIT_COUNT           FLOAT,             
000070       TSWAIT_COUNT_SSQ       FLOAT,             
000071       TSWAIT_TIME            FLOAT,             
000072       TSWAIT_TIME_SSQ        FLOAT              
000073    ) IN CICSPA.CICSP1H;                         
000074                                                 
000075    CREATE TYPE 2 UNIQUE INDEX CICSPA.CICSP1H_IX 
000076           ON CICSPA.CICSP1H                   
000077         (                                       
000078         START_DATE,                             
000079         START_TIME,                             
000080         MVSID,                                  
000081         APPLID,                                 
000082         TRAN                                    
000083         )                                       
000084       USING STOGROUP   SYSDEFLT                                         
000085             PRIQTY     10                                               
000086             SECQTY     10                                               
000087             ERASE      NO                                               
000088             CLUSTER                                                     
000089             BUFFERPOOL BP0                                              
000090             CLOSE      NO                                               
000091 ;                                                                       
****** **************************** Bottom of Data **************************** 
Figure 1. Edit JCL for HDB Export: Define DB2 table

Review the job output in SDSF to verify that the table was created successfully.