Step 1. Create the 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.
  2. In the Export HDB Data Set panel, 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
                            Export HDB Data Set - Create DDL 
     Command ===>                                                                  
    
     Specify Create options then press Enter.
    
     HDB Name  . . . : DB2TST1  
     Data Set Name . : USERID.HISTORY.DB2T1.D16200.T095455.HDB       
    
     Create Options                     CICS versions (VRM)
        Create Database                  Transaction Server  . . : 700 
        Create Storage Group             Transaction Gateway . . : 910 

The create table JCL is generated and displayed in an edit session for review and submission.

Figure 2. Edit JCL to create DB2 table
EDIT       SYB.SPFTEMP3.CNTL                               Columns 00001 00072 
Command ===> SUB_____________________________________________ Scroll ===> PAGE 
****** ***************************** Top of Data ******************************
000001 //SYB#CPA JOB ,NOTIFY=&SYSUID                                           
000002 //* CICSPA 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(DSNTIAD)
000011 /*                                            
000012 //SYSPRINT DD SYSOUT=*                        
000013 //SYSUDUMP DD SYSOUT=*                        
000014 //SYSIN    DD *                                                         
000015    CREATE TABLESPACE DB2T1                                            
000016           IN         CPAX                                             
000017           LOCKSIZE   ANY                                                
000018           BUFFERPOOL BP0                                                
000019           CLOSE      NO                                                 
000020           SEGSIZE    32                                                 
000021           USING      STOGROUP PROD                                      
000022           PRIQTY     20                                                 
000023           SECQTY     20                                                 
000024           ERASE      NO;                                                
000025                                                                         
000026    CREATE TABLE DB2T1.CPA_HST014B (                                        
000027       START_DATE             DATE,                                      
000028       START_TIME             TIME,                                      
000029       APPLID                 CHAR(8),                                   
000030       MVSID                  CHAR(4),                                   
000031       DSA_NAME               CHAR(8),                                   
000032       DSA_LOCATION           CHAR(8),                                   
000033       ACCESS                 CHAR(8),                                   
000034       DSA_INDEX              CHAR,
...                                   

Note the DB2 table name “CICSPA.HST014B”. This name reflects the statistics ID of the selected report, in this case 014 for DSAs. The B suffix is appended to distinguish this report from the Storage Overview report that shares the same 014 ID.

CICS PA exports CICS® Transaction Gateway statistics to DB2 table names ending with the SQL identifier "HSTGnnnn" (note the letter G). This distinguishes them from the "HSTnnnn" DB2 table names for CICS Transaction Server statistics.

You can change this name to something more meaningful to you, for example CICSPA.CICSP1_DSAS.

Note: Perform the Create DDL to define table step only once for each table. After the table has been created, you can load it multiple times by performing Step 2. Load the DB2 table.

Submit the JCL to create the table.