Creating DDL to define a DB2 table
Exporting HDB data into DB2® is a two-step process. The first step creates the DB2 table.
- 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 - 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 onlyFor 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.
Review the JCL and make any changes you require. Here
are some things to look for:
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 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 ****************************