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.
When you have specified the options, press Enter to
generate the JCL and display it in an ISPF Edit session. Figure 1 shows an example.

You can change the default DB2 settings from the CICS PA Profile Options Menu or the Options action bar menu.
If any required DB2 settings are not specified, CICS PA will insert parameter markers such as <setting> in the JCL stream.
- 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.

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 ****************************