DDL, IQL, BATCH & LOAD FOR IFCID366

Document ID : KB000073076
Last Modified Date : 14/05/2018
Show Technical Document Details
Issue:
User has the following need:
Pull IFCID366 records from SMF, load them into a table, and run query against the table to know details about the PGMs which currently are affected by BIF_COMPATIBILITY parameter in ZPAM. From the preceeding she needs:
 
1- DDL  sample to create table for IFCIC366 reflecting SMF-IFCID366 data.
2- IQL to populate data in to the above table.
3-  JCL(PGM=NSIGHTRW), which can map the provided data from SMF with the IFCID366 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-The LOAD statement
Resolution:
1-The DDL 
CREATE TABLE INSIGHT.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-The JCL 
//* CONFIGURATION BEGIN 
// SET SYSV4DB2=PTIPROD.RD190.PRD.CDBALOAD 
// SET IQL=KUCSL02.IDB2.C965692(IQL) 
// SET LOAD=KUCSL02.IDB2.C965692(LOAD) 
//* 
// SET SMFIN=TSPROD.IDB2.PTG.D11A.D170212.IFCID376.SMF 
//* 
// SET SSID=DH3G 
// SET DB2LIB1=DSNDH0G.PRIVATE.SDSNEXIT 
// SET DB2LIB2=DB2.DB2B10.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-The LOAD 
LOAD DATA INDDN IFCID376 RESUME YES LOG YES INTO TABLE INSIGHT. 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) )