DDL, IQL, BATCH and LOAD for IFCID 366 within Sysview for Db2
Article ID: 73076
Updated On:
Products
Issue/Introduction
Requirement in Sysview Performance Management Option for Db2 for z/OS (IDB2) to pull IFCID 366 records from SMF,
load them into a table, and run a query against the table to know details about the programs which currently are affected by
BIF_COMPATIBILITY parameter in ZPARM.
Requirements:
1. DDL sample to create table for IFCIC 366 reflecting SMF-IFCID366 data
2. IQL to populate data into the above table
3. JCL (PGM=NSIGHTRW), which can map the provided data from SMF with the IFCID 366 table
Below is a sample table columns:
- DB2 Member Name
- SMF Date
- Authid
- Connection Name
- End user Workstation Name
- Type Code
- Correlation ID Value
- StoreClock
- Subsystem Name
- Requestor Location Name
- Accounting Token
- Package collection
- Program name
- Plan Name for query
- 'STATIC' or 'DYNAMIC' based on QH0366TY
- FunctionCode
- Text decription of function code
- Statement number of query
- Section number
- Statement Identifier
- Timestamp for query
- Version Length
- Version name
4. LOAD statement
Resolution
1. DDL
CREATE TABLE <creator>.IFCID376 (
DATE_TIME TIMESTAMP,
SYSTEM_ID CHAR(4),
SUBSYSTEM CHAR(4),
CONNECTION CHAR(8),
PLANNAME CHAR(8),
CORR_ID CHAR(12),
UNIT_OF_WORK_ID CHAR(22),
STMT_TYPE CHAR(7),
ARC_EXTENDED_CONN_TYPE CHAR(24),
SYSIBM_CHAR_FUNC INT,
REASON CHAR(25),
STMT_ID BIGINT,
STMT_NUM INT,
SECTION_NUM SMALLINT,
ENDUSER_WORKSTATION VARCHAR(128),
LOCATION VARCHAR(128),
COLLECTION_ID VARCHAR(128),
PROGRAM_NAME VARCHAR(128),
AUTH_ID VARCHAR(128),
VERSION_NAME VARCHAR(64)
);
2. IQL
DECLARE APPL-SQL-INCOMPAT STMT-TYPE (A7) VALOF
IF STMT-TYPE-FLAG = X'8000' RESULTIS 'DYNAMIC'
ELSE RESULTIS 'STATIC '
IFEND
DECLARE APPL-SQL-INCOMPAT REASON (A25) VALOF
IF SYSIBM-CHAR-FUNC = 1 RESULTIS 'CHAR(DEC) FORMAT'
ELSEIF SYSIBM-CHAR-FUNC = 2 RESULTIS 'VARCHAR/CAST(DEC) FORMAT'
ELSEIF SYSIBM-CHAR-FUNC = 3 RESULTIS 'UNSUPPORTED TIMESTAMP'
ELSEIF SYSIBM-CHAR-FUNC = 4 RESULTIS 'DB2 10 DEFAULT SQL PATH'
ELSEIF SYSIBM-CHAR-FUNC = 5 RESULTIS 'CUBE UNQUAL UDF NAME'
ELSEIF SYSIBM-CHAR-FUNC = 6 RESULTIS 'ROLLUP UNQUAL UDF NAME'
ELSEIF SYSIBM-CHAR-FUNC = 7 RESULTIS 'INCOMPATIBLE CONVERSION'
ELSEIF SYSIBM-CHAR-FUNC = 8 RESULTIS 'MATCH CALL STATEMENT'
ELSEIF SYSIBM-CHAR-FUNC = 9 RESULTIS 'IGNORE THE TIMEZONE PART'
ELSEIF SYSIBM-CHAR-FUNC = 10 RESULTIS 'PRE-V10 LTRIM/RTRIM/STRIP'
ELSEIF SYSIBM-CHAR-FUNC = 11 RESULTIS 'SELECT INTO WITH UNION'
ELSEIF SYSIBM-CHAR-FUNC = 1101 RESULTIS 'INSERT W/O XMLDOCUMENT'
ELSEIF SYSIBM-CHAR-FUNC = 1102 RESULTIS 'XPATH EVALUATION ERROR'
ELSEIF SYSIBM-CHAR-FUNC = 1103 RESULTIS 'RLF GOVERNING'
ELSEIF SYSIBM-CHAR-FUNC = 1104 RESULTIS 'LONG CLIENT_ACCTNG SR'
ELSEIF SYSIBM-CHAR-FUNC = 1105 RESULTIS 'LONG CLIENT_APPLNAME SR'
ELSEIF SYSIBM-CHAR-FUNC = 1106 RESULTIS 'LONG CLIENT_USERID SR'
ELSEIF SYSIBM-CHAR-FUNC = 1107 RESULTIS 'LONG CLIENT_WRKSTNNAME'
ELSEIF SYSIBM-CHAR-FUNC = 1108 RESULTIS 'LONG CLIENT RLF SR VALUE'
ELSEIF SYSIBM-CHAR-FUNC = 1109 RESULTIS 'CAST(STRNG AS TIMESTAMP)'
ELSEIF SYSIBM-CHAR-FUNC = 1110 RESULTIS 'SPACE INT GREATER 32764'
ELSEIF SYSIBM-CHAR-FUNC = 1111 RESULTIS 'VARCHAR INT GREATER 32764'
ELSEIF SYSIBM-CHAR-FUNC = 1112 RESULTIS 'EMPTY XML ELEM AS <X></X>'
ELSEIF SYSIBM-CHAR-FUNC = 1201 RESULTIS '-802 RESULT OUT OF RANGE'
ELSE RESULTIS 'QW0376FN UNKNOWN TYPE'
IFEND
IFCID376: TRACE
<< APPL-SQL-INCOMPAT
EVENT-TIME (OF=DB2)
MVS-SMF-ID (OF=A4)
SUBSYS (OF=A4)
CONNECTION (OF=A8)
PLANNAME (OF=A8)
CORR-ID (OF=A12)
UNIT-OF-WORK-ID (OF=A22)
STMT-TYPE (OF=A7)
ARC-EXTENDED-CONN-TYPE (OF=A24)
SYSIBM-CHAR-FUNC (OF=B4)
REASON (OF=A25)
STMT-ID (OF=B8)
STMT-NUM (OF=B4)
SECTION-NUM (OF=B2)
*
ENDUSER-WORKSTATION-LEN (OF=B2)
ENDUSER-WORKSTATION-LONG (OF=A128)
LOCATION-LEN (OF=B2)
LOCATION-LONG (OF=A128)
COLLECTION-ID-LEN (OF=B2)
COLLECTION-ID-LONG (OF=A128)
PROGRAM-NAME-LEN (OF=B2)
PROGRAM-NAME-LONG (OF=A128)
AUTH-ID-LEN (OF=B2)
AUTH-ID-LONG (OF=A128)
VERSION-LENGTH (OF=B2)
VERSION-NAME (OF=A64)
>>
OUTFILE (IFCID376)
;
3 JCL
//* CONFIGURATION BEGIN
// SET SYSV4DB2=hlq.CDBALOAD
// SET IQL=<IQL source>
// SET LOAD=<LOAD control cards>
//*
// SET SMFIN=<input SMF>
//*
// SET SSID=ssid
// SET DB2LIB1=hlq.SDSNEXIT
// SET DB2LIB2=hlq.SDSNLOAD
//* CONFIGURATION END
//GENFLAT EXEC PGM=NSIGHTRW
//STEPLIB DD DISP=SHR,DSN=&SYSV4DB2.
//SYSPARMS DD DUMMY
//DBGPRINT DD SYSOUT=*
//DB2DDN DD DISP=SHR,DSN=&SMFIN.
//IFCID376 DD DISP=(,PASS),DSN=&&IFCID376,SPACE=(CYL,(50,50))
//DBGIN DD DISP=SHR,DSN=&IQL.
//*
//LOADFLAT EXEC PGM=DSNUTILB,PARM='&SSID.',COND=(4,LT)
//STEPLIB DD DISP=SHR,DSN=&DB2LIB1.
// DD DISP=SHR,DSN=&DB2LIB2.
//SYSPRINT DD SYSOUT=*
//UTPRINT DD SYSOUT=*
//SYSIN DD DISP=SHR,DSN=&LOAD.
//IFCID376 DD DISP=(OLD,DELETE),DSN=&&IFCID376
//
4. LOAD
LOAD DATA INDDN IFCID376 RESUME YES LOG YES
INTO TABLE <creator>. IFCID376
WHEN(1:8)='IFCID376'
( DATE_TIME TIMESTAMP EXTERNAL POSITION(77),
SYSTEM_ID CHAR(4),
SUBSYSTEM CHAR(4),
CONNECTION CHAR(8),
PLANNAME CHAR(8),
CORR_ID CHAR(12),
UNIT_OF_WORK_ID CHAR(22),
STMT_TYPE CHAR(7),
ARC_EXTENDED_CONN_TYPE CHAR(24),
SYSIBM_CHAR_FUNC INT, REASON CHAR(25),
STMT_ID BIGINT,
STMT_NUM INT,
SECTION_NUM SMALLINT,
ENDUSER_WORKSTATION VARCHAR,
LOCATION VARCHAR POSITION(365),
COLLECTION_ID VARCHAR POSITION(495),
PROGRAM_NAME VARCHAR POSITION(625),
AUTH_ID VARCHAR POSITION(755),
VERSION_NAME VARCHAR POSITION(885) )
Feedback
