Extracting Performance HDB data to CSV

After you have loaded data into an HDB it is then eligible for extract to CSV data sets.

Select option 6 Extract from the HDB menu to request an HDB extract..

Figure 1. HDB Extract

  File  Options  Help                                                         
------------------------------------------------------------------------------
                                Extract HDBs                   Row 1 to 1 of 1
Command ===> ________________________________________________ Scroll ===> CSR_
                                                                              
Select to run report.                                                         
                                                                              
   Name     Type              Description                Changed        ID    
S CICSP1H  SUMMARY  Summary HDB for CICSP1           2004/12/07 09:28 JCH     
********************************* End of list ********************************
                                                                               
 F1=Help    F3=Exit    F7=Backward    F8=Forward    F10=Actions    F12=Cancel

Select the required HDB from the list to display the Run Extract panel.

Figure 2. Run Summary HDB Extract

                     Run SUMMARY HDB Extract - CICSP1H               
Command ===> ___________________________________________________________
                                                                    
Specify Extract request options then press Enter to continue submit. 
                                                                    
----- Report Interval ------  HDB contains data                         
     YYYY/MM/DD  HH:MM:SS.TH  in the range:                               
From 2004/12/15  ___________  2004/11/17 05:17    Extract Recap:          
To   2004/12/16  ___________  2005/01/17 21:31    DDname . . . HXTS0001  
                                                                          
Output Data Set:                                                          
 Data Set Name . . HDB.EXTRACT___________________________________        
 Disposition . . . 1  1. OLD   2. MOD   (If cataloged)                    
                                                                          
Extract Format:                         Enter "/" to select option        
 Form  . . . . . .  ________ +          /  Include Field Labels           
 Delimiter . . . . ;                    _  Numeric Fields in Float format 
                                                                          
Processing Options:                     Enter "/" to select option        
 Time Interval . . 01:00:00  (hh:mm:ss)  /  Edit JCL before submit         
                                                                          
 F1=Help     F3=Exit     F4=Prompt   F6=Resize  F12=Cancel

The options are:

Report Interval

Specify the reporting time range. You can specify an explicit date, such as 2004/12/15, or a relative date to indicate today (0), yesterday (-1), two days ago (-2), and so on. Adjacent is the time range of data contained in this HDB. If you specify a Report Interval, then it must be within this range otherwise the extract request will fail.

Extract Recap DDname

The DDname for the Recap report which prints at the end of extract processing to provide processing statistics. The DDname is mandatory.

CICS® PA assigns a default DDname HXTS0001.

This option generates the OUTPUT(ddname) operand.

Output Data Set

The name of the data set where the extract records are written. When specifying the data set name, standard TSO conventions apply.

If CICS PA is to create the data set at run time, the default allocation attributes specified on the Reporting Allocation Settings panel are used in generating the JCL. If the data set is already cataloged, then CICS PA will use DISP=OLD or DISP=MOD according to your request to overwrite or append to the existing data set.

CICS PA generates the DDNAME(ddname) operand and assigns a default DDname HDBX0001.

Disposition

This option applies if the extract data set you specified is already cataloged.

Select option 1 - OLD to overwrite the data set contents with the new extract data.

Select option 2 - MOD to append the new extract data.

Report Form

Specify a Report Form to tailor the format of the extract records. If you do not specify a Form, CICS PA will write all the fields in the HDB in order.

Delimiter

Specify the field delimiter to be used to separate each data field in the extract data set. The default is a semicolon and generates the DELIMIT(';') operand.

Include Field Labels

Select this option to indicate that the first record to be written to the extract data set is to be a field labels record. This is the default and generates the LABELS operand.

Leave blank if you do not want a field labels record written to the extract data set. This generates the NOLABELS operand.

Numeric Fields in Float format

Select this option if you want CICS PA to write numeric fields to the extract data set in S390 FLOAT format. This generates the FLOAT operand. Specify FLOAT format if you plan to import the extract into a DB2® table. When the DB2 Load Utility is used, it will interpret all numerical fields reliably and consistently in FLOAT format.

If you do not select this option, the numeric fields are written in a mixture of integer, real and exponential using character digits. This is the default and is suitable when importing the extract data into a PC spreadsheet tool. This generates the NOFLOAT operand.

Time Interval

Specify an optional Time Interval when extracting Summary HDBs.

Data in a Summary HDB is already summarized by the interval that was used to load the data. This is the value specified in the HDB or, if Time Interval was not specified in the HDB, the value defined in the Template.

You can further summarize the data by specifying a multiple of the interval that was used to load the data. Specify a value in the range 00:00:01 (1 second) to 24:00:00 (24 hours). For example, specify 00:15:00 if you want to summarize transaction activity over 15 minute intervals. If you are reviewing many days worth of data then you might specify 24:00:00 (24 hours) so that you can view the daily trend. In Figure 2, the Interval has been changed to 1 hour.

Notes:

If you specify a reporting interval that is equal to or less than the interval that was used to load the data, the report or extract uses the data as-is, without further summarization.
If you do not specify a reporting interval it defaults to the interval that was used to load the data, unless that value is less than 1 minute, in which case the reporting interval is set to 1 minute.

When you have specified your Extract options, you are prompted to Press ENTER to proceed with request. This provides a last opportunity to review and change your request details.

If you selected Edit JCL before submit then the Extract HDB JCL is displayed in an edit session.

Figure 3. Edit JCL for Summary HDB Extract

EDIT       userid.SPFTEMP2.CNTL                            Columns 00001 00072 
Command ===> ________________________________________________ Scroll ===> CSR_ 
****** ***************************** Top of Data ******************************
000001 //CICSPA  JOB ,NOTIFY=&SYSUID                                           
000002 //*  CICS PA V5R4 HDB EXTRACT JCL                                       
000003 //CICSPA   EXEC PGM=CPAMAIN                                             
000004 //STEPLIB  DD DISP=SHR,DSN=CPA.V5R4M0.SCPALINK                          
000005 //CPAHDBRG DD DISP=SHR,DSN=CICSPROD.CICSPA.HDB.REPOSTRY                 
000006 //SYSPRINT DD SYSOUT=*                                                  
000007//HDBX0001 DD  DSN=userid.HDB.EXTRACT, 
000008//             DISP=(OLD) 
000009 //* Command Input                                                       
000010 //SYSIN DD *                                                            
000011 * HDB=CICSP1H                                                           
000012 * Description=Summary HDB for CICSP1H                                   
000013          CICSPA SMFSTART(2004/12/15,00:00:00.00),                       
000014                  SMFSTOP(2004/12/16,00:00:00.00)                        
000015          CICSPA NOAPPLID,                                    
000016                 LINECNT(60),                                 
000017                 FORMAT(':','/'),                             
000018                 PRECISION(4),                                
000019             HDB(DDNAME(HDBX0001),EXTRACT(CICSP1H),           
000020                 OUTPUT(HXTS0001),LABELS,DELIMIT(';'),NOFLOAT,
000021                 INTERVAL(01:00:00))                          
000022 /*                                                                      
000023 //* HDB Container Data Sets. HDB Report processing does not require     
000024 //* these data sets to be included in the JCL as they are dynamically   
000025 //* allocated when required. They are included:                         
000026 //* 1) for your reference                                               
000027 //* 2) to ensure that all required data sets are cataloged              
000028 //* 3) to allow DFHSM to recall required data sets up front             
000029 //HDB00001 DD DISP=SHR,DSN=userid.CICSP1H.D03219.T092846.HDB            
****** **************************** Bottom of Data ****************************

The HDB container data sets are listed at the end of the JCL. They are not required here because the CICS PA batch utility will dynamically allocate the data sets when they are required. CICS PA adds the data sets into the JCL primarily for the purpose of DFHSM recall, if required. It is more efficient to recall data sets in the JCL (where job initiation can recall migrated data sets en masse) rather than one at a time when dynamically allocated.

The command deck specifies operands to extract records from HDB CICSP1H, write them to the extract data set with DDname HDBX0001, and write the Recap report output to the DDname HXTS0001:

HDB(DDNAME(HDBX0001),EXTRACT(CICSP1H),OUTPUT(HXTS0001),...)

Enter SUBmit in the command line to submit the job to run the report.

Successful completion of the Extract request will generate an HDB Summary Extract Recap report.

Figure 4. HDB Summary Extract Recap report

 V5R4M0                                                 CICS Performance Analyzer                                                  
                                                       Historical Database Summary                            
HXTS0001 Printed at 12:34:56 09/15/2021     Data from 15:00:00 12/15/2004 to 00:00:00 12/16/2004     Page       1
                                                                                                                                   
                                                                                                                                   
HDBX0001 Extract has completed successfully                                                                                        
         Data Set Name . . . . userid.HDB.EXTRACT                                                                                 
         Record count  . . . .         788

The extract data set contains records like those in the following example.

Figure 5. HDB Summary Extract record format

Start Date;Start Time;MVS;APPLID;Tran;#Tasks;Response Time Avg;Dispatch Time Avg;User CPU Time Avg;Suspend Time
2004/12/15 15:00:00;MV2C    ;IYK3ZAC1;CSHQ    ;       1;55155.62;   .2103;   .0212;55155.41;   .0331;   .0001; 
2004/12/15 15:00:00;MV2C    ;IYK3ZAC1;CSNC    ;       1;55159.06;   .3379;   .0041;55158.72;   .0356;   .0001; 
2004/12/15 15:00:00;MV2C    ;IYK3ZAC1;CSNE    ;       1;55153.97;   .0881;   .0060;55153.88;   .0042;   .0001; 
2004/12/15 18:00:00;MV2C    ;IYK2ZFV1;CEX2    ;       1;50237.83;   .5030;   .2717;50237.33;   .1800;   .0001; 
2004/12/15 18:00:00;MV2C    ;IYK2ZFV1;CSHQ    ;       1;50234.95;   .3105;   .0190;50234.64;   .5761;   .0001; 
2004/12/15 18:00:00;MV2C    ;IYK2ZFV1;CSNC    ;       1;50393.54;   .4259;   .0058;50393.12;   .0026;   .0001; 
2004/12/15 18:00:00;MV2C    ;IYK2ZFV1;CSNE    ;       1;50389.87;   .1321;   .0177;50389.74;   .0074;   .0001; 
2004/12/15 18:00:00;MV2C    ;IYK2ZFV2;CEX2    ;       1;50241.24;   .2630;   .1828;50240.98;   .2255;   .0001;