Generating input data for spreadsheets
With the Spreadsheet Input-Data Generator utility you can create input data for spreadsheets to ease DB2 performance analysis.
- Accounting and Statistics File performance data
- Accounting and Statistics converted Save-File performance data
- Record Trace File data of IFCID 172, 196, and 365
It creates a comma-separated value (CSV) data set using input provided by parts of the Performance Database (PDB) in the RKO2SAMP / TKO2SAMP library and also your input from field selection lists. This CSV data can then be transferred to workstations and imported into spreadsheets to improve DB2 performance analysis using graphical representations or pivot tables.
Input parameters
The Spreadsheet Input-Data Generator mainly relies on the sample CREATE, LOAD, and meta data parts delivered for the Performance Database in the RKO2SAMP / TKO2SAMP library. These parts describe the record layout of the sequential load file input data, the positions of fields within the record, data type, length, WHEN, NULLIF, and DEFAULTIF values. Based on this information and the following input parameters, the fields of the sequential load file can be transformed into a CSV record.
The load module FPEPCSV is linked in library RKANMOD / TKANMOD. The following samples are provided in the RKANSAM / TKANSAM library:
- FPEPCSVJ
- The sample JCL to invoke the Spreadsheet Input-Data Generator utility.
- FPEPCSVP
- Invokes the Spreadsheet Input-Data Generator utility with a job
procedure. The following sample jobs are provided:
- FPEPCSVA
- Invokes the Spreadsheet Input-Data Generator utility and creates multiple Accounting Save spreadsheets with a job procedure.
- FPEPCSVB
- Invokes the Spreadsheet Input-Data Generator utility and creates multiple Accounting File spreadsheets with a job procedure.
- FPEPCSVN
- Invokes the Spreadsheet Input-Data Generator utility and creates multiple Record Trace spreadsheets with a job procedure.
- FPEPCSVS
- Invokes the Spreadsheet Input-Data Generator utility and creates multiple Statistics spreadsheets with a job procedure.
- FPEPCSVW
- Invokes the Spreadsheet Input-Data Generator utility and creates multiple System Parameter spreadsheets with a job procedure.
...
//* parm = type persec hdrrow hdrpdcol delim decsep sampdir
//FPEPCSV EXEC PGM=FPEPCSV,
// PARM='SBUF N Y N , . <db2ompe>.TKO2SAMP'
...- TYPE
- Specifies the type of records that are put into the CSV data set. For each supported TYPE, a sample master selection list is provided that contains all fields in the sequential load file records that have a column in the associated Performance database (PDB) table.
- The RKO2SAMP / TKO2SAMP part type consists of 4
characters, it can be:
- AFBU
- Accounting File data, category BUFFER POOL
- AFDF
- Accounting File data, category DDF
- AFGE
- Accounting File data, category GENERAL
- AFGP
- Accounting File data, category GROUP BUFFER POOL
- AFPK
- Accounting File data, category PACKAGE
- AFXC
- Accounting File data, category ACCELERATOR
- ASBU
- Accounting Save-File data, category BUFFER POOL
- ASDF
- Accounting Save-File data, category DDF
- ASGE
- Accounting Save-File data, category GENERAL
- ASGP
- Accounting Save-File data, category GROUP BUFFER POOL
- ASPK
- Accounting Save-File data, category PACKAGE
- ASRF
- Accounting Save-File data, category RLF
- ASXC
- Accounting Save-File data, category ACCELERATOR
- NFDL
- Record trace data, category DEADLOCK
- NFTI
- Record trace data, category TIMEOUT
- NFRL
- Record trace data, category REMOTE LOCATION
- SACC
- Statistics data, category AGGREGATED ACCOUNTING
- SBUF
- Statistics data, category BUFFER POOL
- SDDF
- Statistics data, category DDF
- SGBP
- Statistics data, category GROUP BUFFER POOL
- SGEN
- Statistics data, category GENERAL
- SSET
- Statistics data, category BUFFER POOL DATA SET
SSIM
- Statistics data, category SIMULATED BUFFER POOL
- SSTG
- Statistics data, category STORAGE GENERAL
- SXCL
- Statistics data, category ACCELERATOR
- W106
- System Parameter data, category IFCID 106
- W201
- System Parameter data, category IFCID 201
- W202
- System Parameter data, category IFCID 202
- W230
- System Parameter data, category IFCID 230
- W256
- System Parameter data, category IFCID 256
- WSFP
- System Parameter data, category DDF Facility
- PERSEC
- Specifies whether average values are calculated. It can be Y (for
YES) or N (for NO). This option is only valid for Statistics records
where the interval column INTERVAL_ELAPSED can be
used to derive per-second (PERSEC) values. Note: Averages are only calculated for Statistics fields which are accumulated by DB2 and have a field TYPE of AGGR defined in a Statistics field selection list (FPEPS*).
- HDRROW
- Specifies whether a header row is added to the CSV output. It
can be:
- F (for FORCED)
- Indicates that the header row is written in any case.
- Y (for YES)
- Indicates that the header row is written only if input data is available.
- N (for NO)
- Indicates that the header row is not written.
- HDRPDCOL
- Specifies whether the Performance Database (PDB) column name or
the report label in the field selection list is used as header row.
It can be Y (for YES) or N (for NO).
It is ignored if HDRROW=N.
If HDRPDCOL=N, the REPORT LABEL (V) entries are chosen as CSV column names. You can modify these entries according to your requirements.
- DELIM
- Specifies the CSV delimiter to be used in the output. It can be a comma (,) or a semicolon (;).
- DECSEP
- Specifies which decimal separator is used in the output. It can
be a dot (.) or a comma (,):
- Dot (.)
- If DELIM is a comma (,), DECSEP must be a dot (.) and the CSV output is suited for workstation settings with dot as decimal separator.
- Comma (,)
- If DELIM is a semicolon (;), DECSEP must be a comma (,) and the CSV output is suited for workstation settings with comma as decimal separator.
- SAMPDIR
- Specifies the sample RKO2SAMP / TKO2SAMP library to be used.
The following DD names must be specified in FPEPCSVJ:
...
//STEPLIB DD DSN=<db2ompe>.TKANMOD,DISP=SHR
//SYSPRINT DD SYSOUT=*
//FLDSEL DD DSN=<user_hlq>.FPECSVU(FPEPSBUF),DISP=OLD
//INPUT DD DSN=<user_hlq>.STFILE,DISP=OLD
//OUTPUT DD DSN=<user_hlq>.SCSVSBUF,DISP=(NEW,CATLG,DELETE),
// UNIT=SYSDA,SPACE=<CYL,<150,150),RLSE),
// DCB=<RECFM=VB,LRECL=32756,BLKSIZE=32760)
...Where:
- FLDSEL
- Specifies the field selection lists you want to use.
For each supported TYPE, a sample master selection list is provided in the library that contains all fields in the sequential load file records that have a column in the associated Performance database (PDB) table.
The master field selection lists for all supported data sets of Accounting and Statistics File, converted Save-File, and Record Trace File are provided in the RKANSAMF / TKANSAMF library. The name of a field list FPEPtype consists of the component code FPEP and the record type described in input parameter TYPE. For more information refer to Input parameters. For example, FPEPSFBU is the sample field selection list for the Spreadsheet Input-Data Generator utility and Statistics File data, category BUFFER POOL.
- INPUT
- Specifies the data set of Accounting or Statistics File, converted Save-File, or Record Trace File.
- OUTPUT
- Specifies the CSV data set.
Input and output data sets
- A field selection list (such as FPEPSGEN) specifies which fields from the sequential load file input data set are transformed to the CSV output.
- For each supported TYPE, a sample master field selection list is provided that contains all fields in the sequential load file records that have a column in the associated Performance database (PDB) table.
- The first 13 rows contain the copyright statement. Comments must start with a forward slash followed by an asterisk (/*) and end with an asterisk followed by a slash (*/). You can delete or update these comments.
- The next two header rows and the first three columns are fixed (=F). You must not change them.
- The fourth and fifth columns are variable (=V). You can modify them.
/***Start of Specifications********************************************/
/* */
/* MODULE-NAME = FPEPSGEN */
/* DESCRIPTIVE-NAME = Field selection list for Spreadsheet Utility */
/* */
/* COPYRIGHT : IBM OMEGAMON XE for DB2 PE on z/OS V5 */
/* Licensed Material - Property of IBM */
/* 5655-W37 (C) Copyright IBM Corp. 1993, 2015 */
/* */
/* FUNCTION = Sample field selection list for Spreadsheet Utility */
/* and Statistics data, category GENERAL. */
/* */
/***End of Specifications**********************************************/
FIELD(F) PDB COLUMN NAME(F) TYPE (F) FORMAT(V) REPORT LABEL (V)
-------- ------------------ -------- --------- -------------------------------
DB2PMREL DB2PM_REL SNAP DB2PM_REL
DB2REL DB2_REL SNAP DB2_REL
QWHSSID MVS_ID SNAP MVS_ID
QWHSLOCN LOCAL_LOCATION SNAP LOCAL_LOCATION
QWHADSGN GROUP_NAME SNAP GROUP_NAME
QWHSSSID SUBSYSTEM_ID SNAP SUBSYSTEM_ID
QWHAMEMN MEMBER_NAME SNAP DATA SHARING MEMBER
SDBEGINT INTERVAL_TSTAMP SNAP INTERVAL_TSTAMP
SDELTIME INTERVAL_ELAPSED AGGR INTERVAL ELAPSED
SDBEGREC BEGIN_REC_TSTAMP SNAP INTERVAL START
SDENDREC END_REC_TSTAMP SNAP INTERVAL END
QXSELECT SELECT AGGR SELECT
QXINSRT INSERT AGGR INSERT
QXUPDTE UPDATE AGGR UPDATE
QXMERGE MERGE AGGR MERGE
QXDELET DELETE AGGR DELETE
QXPREP PREPARE AGGR PREPARE
QXDESC DESCRIBE AGGR DESCRIBE
QXDSCRTB DESCRIBE_TABLE AGGR DESCRIBE TABLE
QXOPEN OPEN AGGR OPEN
QXFETCH FETCH AGGR FETCH
QXCLOSE CLOSE AGGR CLOSE
...The record layout of a field selection list contains the following headers and columns:
- FIELD (F)
- The FIELD header and its column is fixed (=F). You must not change it.
- PDB COLUMN NAME (F)
- The PDB COLUMN NAME (Performance Database) header and its column is fixed (=F). You must not change it.
- TYPE (F)
- The TYPE header and its column is fixed (=F).
You must not change it. The following values are possible:
- AGGR
- Aggregated value by DB2
- HWM
- High-water mark
- SNAP
- Snapshot value
- FORMAT (V)
- The FORMAT header and its column is variable (=V).
You can modify it.This column specifies special formatting routines. The following formats are supported:
- MB
- Divides input value (bytes expected) by 1024*1024.
- If MB is specified in the master field selection list, then (MB) is shown as a suffix of the report label.
- P2MB
- Formats number of pages (4K-pages expected) as MB value.
- If P2MB is specified in the master field selection list, then (MB) is shown as a suffix of the report label.
- HEX
- Shows the hexadecimal value.
- S2T
- Transforms a store-clock value to a readable time format.
- BP2C
- Transforms buffer pool identifiers (small integers) in the BP_ID
columns of Accounting and Statistics spreadsheets to a character representation,
such as:
0 → BP0 100 → BP8K0 - GBP2C
- Transforms group buffer pool identifiers (small integers) in the
GBP_ID columns of Accounting and Statistics spreadsheets to a character
representation, such as:
0 → GBP0 100 → GBP8K0
- REPORT LABEL (V)
- The REPORT LABEL header and its column is variable (=V).
You can modify it.Note:
- If HDRPDCOL=N, the REPORT LABEL entries are chosen as CSV column names. You can modify these entries according to your requirements.
- The sample field selection lists contain report labels which are derived from the labels in Accounting and Statistics reports. Some report labels refer to the Performance database (PDB) column name.
Organization . . . : PO
Record format . . . : FB
Record length . . . : 132The Spreadsheet Input-Data Generator expects field selection lists under DD name FLDSEL in a partitioned data set with the above attributes of the RKANSAMF / TKANSAMF library. This means that you can use the master field selection lists of RKANSAMF / TKANSAMF as input if you do not want to customize the lists. Otherwise, you should copy the sample lists to a partitioned data set with the above FB132 attributes and tailor them to your needs.
The sequential load file denotes the File or converted Save-File data set that is input for the Spreadsheet Input-Data Generator. The records of the input data set which satisfies the WHEN condition of the TYPE-related LOAD statement are read, the fields specified in the selection list are retrieved from this input record, converted, and added to the CSV record according to the PERSEC and format options.
The Spreadsheet Input-Data Generator expects the input data set under DD name INPUT.
The TKO2SAMP directory is delivered with OMEGAMON® XE for DB2® PE. It contains the PDB parts which are needed for the CSV creation (fields, positions, data types, and so on). The Spreadsheet Input-Data Generator expects the original product library as input. A run-time environment contains the parts in directory RKO2SAMP. The Spreadsheet Input-Data Generator expects the directory name as the 7-th input parameter after the decimal separator.
Organization . . . : PS
Record format . . . : VB
Record length . . . : 32756
Block size . . . . : 32760- Checks the syntactical correctness of RKO2SAMP / TKO2SAMP members. If you use the original product library, the syntactical correctness is ensured. The Spreadsheet Input-Data Generator detects if members are changed or if they use the wrong syntax (such as unknown SQL syntax).
- Supports 1000 columns in one CSV row. For example, the Statistics File/Save General records currently contain about 600 fields.
Sample procedures and JCL
The Spreadsheet Input-Data Generator is invoked using PROCs and JCLs as shown in the following samples delivered with the RKANSAM / TKANSAM library. The following example creates a spreadsheet for Statistics data, category BUFFER POOL (SBUF) based on the master selection list <user_hlq>.FPECSVU(FPEPSBUF).
Use FPEPCSVJ to invoke the Spreadsheet Input-Data Generator utility.
//*Start of Specifications*********************************************
//* *
//* MODULE NAME : FPEPCSVJ *
//* *
//* DESCRIPTION : Spreadsheet Utility sample job *
//* *
//* COPYRIGHT : IBM OMEGAMON XE for DB2 PE on z/OS V5 *
//* Licensed Material - Property of IBM *
//* 5655-W37 (C) Copyright IBM Corp. 1993, 2015 *
//* *
//* FUNCTION : Invoke spreadsheet utility *
//* *
//* Notes = *
//* 1. Add a valid job card *
//* 2. Change the prefix of the OMEGAMON XE for DB2 PE *
//* library <db2ompe> *
//* 3. Change the prefix of the user libraries <user_hlq> *
//* 4. DDnames represent the following files... *
//* FLDSEL User-modified field selection list *
//* INPUT Accounting / statistics FILE or *
//* converted SAVE-FILE data set *
//* OUTPUT Spreadsheet CSV data set *
//* *
//* Verify the DD parameters (allocation, quantities, etc.) *
//* *
//*End of Specifications***********************************************
//* parm = type persec hdrrow hdrpdcol delim decsep sampdir
//FPEPCSV EXEC PGM=FPEPCSV,
// PARM='SBUF N Y N , . <db2ompe>.TKO2SAMP'
//STEPLIB DD DSN=<db2ompe>.TKANMOD,DISP=SHR
//SYSPRINT DD SYSOUT=*
//FLDSEL DD DSN=<user_hlq>.FPECSVU(FPEPSBUF),DISP=OLD
//INPUT DD DSN=<user_hlq>.STFILE,DISP=OLD
//OUTPUT DD DSN=<user_hlq>.SCSVSBUF,DISP=(NEW,CATLG,DELETE),
// UNIT=SYSDA,SPACE=(CYL,(150,150),RLSE),
// DCB=(RECFM=VB,LRECL=32756,BLKSIZE=32760) Multiple invocations of the OMEGAMON XE for DB2 PE Spreadsheet Input-Data Generator can be organized using job procedures with additional parameters for the input or output data sets. The &OUTDISP parameter specifies whether an existing CSV data set is overwritten or whether CSV records are appended to the end.
Use FPEPCSVP to invoke the Spreadsheet Input-Data Generator utility with a job procedure.
//*Start of Specifications*********************************************
//* *
//* MODULE NAME : FPEPCSVP *
//* *
//* DESCRIPTION : Spreadsheet Utility sample job procedure *
//* *
//* COPYRIGHT : IBM OMEGAMON XE for DB2 PE on z/OS V5 *
//* Licensed Material - Property of IBM *
//* 5655-W37 (C) Copyright IBM Corp. 1993, 2015 *
//* *
//* FUNCTION : Invoke spreadsheet utility with job procedure *
//* *
//* Notes = *
//* 1. Change the prefix of the OMEGAMON XE for DB2 PE load *
//* library <db2ompe> *
//* 2. DDnames represent the following files... *
//* FLDSEL User-modified field selection list *
//* INPUT Accounting / statistics FILE or *
//* converted SAVE-FILE data set *
//* OUTPUT Spreadsheet CSV data set *
//* *
//* Verify the DD parameters (allocation, quantities, etc.) *
//* *
//*End of Specifications***********************************************
//CSVPROC PROC TYPE=DUMMY,PERSEC=DUMMY,HDRROW=DUMMY,HDRPDCOL=DUMMY,
// DELIM=DUMMY,DECSEP=DUMMY,SAMPDIR=DUMMY,
// FLDSELDS=DUMMY,INPUTDS=DUMMY,OUTPUTDS=DUMMY,OUTDISP=DUMMY
// EXEC PGM=FPEPCSV,
// PARM='&TYPE &PERSEC &HDRROW &HDRPDCOL &DELIM &DECSEP &SAMPDIR'
//STEPLIB DD DSN=<db2ompe>.TKANMOD,DISP=SHR
//SYSPRINT DD SYSOUT=*
//FLDSEL DD DSN=&FLDSELDS,DISP=OLD
//INPUT DD DSN=&INPUTDS,DISP=OLD
//OUTPUT DD DSN=&OUTPUTDS,DISP=&OUTDISP
// PEND
Use FPEPCSVS to invoke the Spreadsheet Input-Data Generator utility and create multiple Statistics spreadsheets with a job procedure.
//*Start of Specifications*********************************************
//* *
//* MODULE NAME : FPEPCSVS *
//* *
//* DESCRIPTION : Spreadsheet Utility sample job for creation of *
//* STATISTICS FILE and SAVE spreadsheets *
//* *
//* COPYRIGHT : IBM OMEGAMON XE for DB2 PE on z/OS V5 *
//* Licensed Material - Property of IBM *
//* 5655-W37 (C) Copyright IBM Corp. 1993, 2015 *
//* *
//* FUNCTION : Invoke spreadsheet utility and create multiple *
//* STATISTICS spreadsheets with a job procedure *
//* *
//* Notes = *
//* 1. Add a valid job card *
//* 2. Change the prefix of the OMEGAMON XE for DB2 PE *
//* library <db2ompe> *
//* 3. Change the prefix of the user libraries <user_hlq> *
//* 4. DDnames represent the following files... *
//* LIBS Library of job procedure FPEPCSVP *
//* *
//*End of Specifications***********************************************
//*-----------------------------------------------------------------
//LIBS JCLLIB ORDER=(<user_hlq>.FPEJCL)
//*-----------------------------------------------------------------
//* STATISTICS -----------------------------------------------------
// EXEC PROC=FPEPCSVP,TYPE=SACC,PERSEC=N,HDRROW=Y,HDRPDCOL=N,
// DELIM=',',DECSEP='.',
// SAMPDIR=<db2ompe>.TKO2SAMP,
// FLDSELDS=<user_hlq>.FPECSVU(FPEPSACC),
// INPUTDS=<user_hlq>.STLOAD,
// OUTPUTDS=<user_hlq>.SCSVSACC,
// OUTDISP=OLD
// EXEC PROC=FPEPCSVP,TYPE=SBUF,PERSEC=N,HDRROW=Y,HDRPDCOL=N,
// DELIM=',',DECSEP='.',
// SAMPDIR=<db2ompe>.TKO2SAMP,
// FLDSELDS=<user_hlq>.FPECSVU(FPEPSBUF),
// INPUTDS=<user_hlq>.STLOAD,
// OUTPUTDS=<user_hlq>.SCSVSBUF,
// OUTDISP=OLD
// EXEC PROC=FPEPCSVP,TYPE=SDDF,PERSEC=N,HDRROW=Y,HDRPDCOL=N,
// DELIM=',',DECSEP='.',
// SAMPDIR=<db2ompe>.TKO2SAMP,
// FLDSELDS=<user_hlq>.FPECSVU(FPEPSDDF),
// INPUTDS=<user_hlq>.STLOAD,
// OUTPUTDS=<user_hlq>.SCSVSDDF,
// OUTDISP=OLD
// EXEC PROC=FPEPCSVP,TYPE=SGBP,PERSEC=N,HDRROW=Y,HDRPDCOL=N,
// DELIM=',',DECSEP='.',
// SAMPDIR=<db2ompe>.TKO2SAMP,
// FLDSELDS=<user_hlq>.FPECSVU(FPEPSGBP),
// INPUTDS=<user_hlq>.STLOAD,
// OUTPUTDS=<user_hlq>.SCSVSGBP,
// OUTDISP=OLD
// EXEC PROC=FPEPCSVP,TYPE=SGEN,PERSEC=N,HDRROW=Y,HDRPDCOL=N,
// DELIM=',',DECSEP='.',
// SAMPDIR=<db2ompe>.TKO2SAMP,
// FLDSELDS=<user_hlq>.FPECSVU(FPEPSGEN),
// INPUTDS=<user_hlq>.STLOAD,
// OUTPUTDS=<user_hlq>.SCSVSGEN,
// OUTDISP=OLD
// EXEC PROC=FPEPCSVP,TYPE=SSET,PERSEC=N,HDRROW=Y,HDRPDCOL=N,
// DELIM=',',DECSEP='.',
// SAMPDIR=<db2ompe>.TKO2SAMP,
// FLDSELDS=<user_hlq>.FPECSVU(FPEPSSET),
// INPUTDS=<user_hlq>.STLOAD,
// OUTPUTDS=<user_hlq>.SCSVSSET,
// OUTDISP=OLD
// EXEC PROC=FPEPCSVP,TYPE=SSTG,PERSEC=N,HDRROW=Y,HDRPDCOL=N,
// DELIM=',',DECSEP='.',
// SAMPDIR=<db2ompe>.TKO2SAMP,
// FLDSELDS=<user_hlq>.FPECSVU(FPEPSSTG),
// INPUTDS=<user_hlq>.STLOAD,
// OUTPUTDS=<user_hlq>.SCSVSSTG,
// OUTDISP=OLD
// EXEC PROC=FPEPCSVP,TYPE=SXCL,PERSEC=N,HDRROW=Y,HDRPDCOL=N,
// DELIM=',',DECSEP='.',
// SAMPDIR=<db2ompe>.TKO2SAMP,
// FLDSELDS=<user_hlq>.FPECSVU(FPEPSXCL),
// INPUTDS=<user_hlq>.STLOAD,
// OUTPUTDS=<user_hlq>.SCSVSXCL,
// OUTDISP=OLD
// EXEC PROC=FPEPCSVP,TYPE=SSIM,PERSEC=N,HDRROW=Y,HDRPDCOL=N,
// DELIM=',',DECSEP='.',
// SAMPDIR=<db2ompe>.TKO2SAMP,
// FLDSELDS=<user_hlq>.FPECSVU(FPEPSSIM),
// INPUTDS=<user_hlq>.STLOAD,
// OUTPUTDS=<user_hlq>.SCSVSSIM,
// OUTDISP=OLD
You can now import the generated CSV data into your spreadsheet tools at the workstation and use graphical representations or pivot tables for performance analysis.