CICS IA HELPS YOU TO COLLECT AND VISUALIZE THE DB2 RESOURCE (RESNAME - RESOURCE NAME) USED IN CICS
CICS IA collects information about multiple resources used in different CICS applications. This blog elaborates on how CICS IA collects and shows a specific resource from DB2 resources. The resource name is the resource used inside the source program. This resource name is viewed under the RESNAME field of the CIU_DB2_DATA table.
Note:
CIU_DB2_DATA – Name of the table which collects DB2 resources during CICS IA data collection.
Below are the steps to collect DB2 resources and display the name of the DB2 resource:
A. Create a DB2 table to check RESNAME values using SPUFI.
B. DCLGEN for the newly created table
C. Writing COBOL code to fetch data from CICS IA collected data and compilation.
D. Restart the CICS region.
E. Define and install resources in the CICS region.
F. CICS IA collector configuration
G. Job submission in ISPF
H. Client-side verification of data in the view
A. Create a DB2 table to check RESNAME values using SPUFI
1. Go to ISPF settings.
2. Enter 14 in the command line and press Enter.
3. Enter DB or 5 in the command line and press Enter
4. Provide subsystem id in the SSID field (this can vary as per LPAR configuration).
5. Go to the options field.
6. Verify the latest version of DB2 (in our sample, V12).
7. Select DC as it has DB2I V12 (this can vary as per LPAR configuration).
8. Enter 1 in the command and press Enter for SPUFI.
9. Enter the DATA SET NAMEs in the input and output fields within the single quote.
10. The rest of the fields should be ‘YES’.
11. Press Enter 2 times.
12. You will be redirected to the member inside the PDS file which is mentioned in the input dsname (IATEST2.COPY.PDS(DBDATA)).
13. Enter the SQL query to create the table, as shown below:
|
CREATE TABLE TABLEA1 (EMPNO INT NOT NULL,
NAMEA VARCHAR(20) NOT NULL, COMMENT CHAR(25) NOT NULL, PRIMARY KEY (EMPNO)
);
CREATE INDEX TABA_INDEX ON TABLEA1(EMPNO);
INSERT INTO TABLEA1(EMPNO, NAMEA, COMMENT) VALUES (101,'SWARNA','GOOD');
|
14. Enter SAVE in the command line and press ENTER to save.
15. Press F3 and then press Enter for output verification.
16. The output will be displayed with SQL code.
B. DCLGEN for the newly created table
1. Inside the same DB2I page, go back to “DB2I PRIMARY OPTION MENU”.
2. Enter 2 in the command line and press Enter for DCLGEN.
3. Enter the source table name (from the table created using SQL query in SPUFI)
4. Enter the table owner (user ID)
5. Enter destination DATA SET NAME (this is the output dataset where the DCLGEN will be stored)
6. Enter Command “ADD” or “REPLACE” in the ACTION field, if data needs to be added or replaced into an existing member.
The below images show the DCLGEN generated code:
C. Writing COBOL code to fetch data from CICS IA collected data and compilation
IDENTIFICATION DIVISION.
PROGRAM-ID. SDPGMDB3.
DATA DIVISION.
WORKING-STORAGE SECTION.
01 WS-MSG-O.
05 MSGDATA PIC X(40).
05 MSGLEN PIC S9(4) COMP.
EXEC SQL
INCLUDE SQLCA
END-EXEC.
EXEC SQL
INCLUDE TABLEA1
END-EXEC.
EXEC SQL BEGIN DECLARE SECTION
END-EXEC.
01 DCLTABLEA1.
05 W-EMPNO PIC S9(9) USAGE COMP.
01 DCLTABLEB1.
05 W-EMP1NO PIC S9(9) USAGE COMP.
EXEC SQL
END DECLARE SECTION
END-EXEC.
PROCEDURE DIVISION.
MOVE '40' TO MSGLEN.
MOVE 'ENTERED BEFORE STAR' TO MSGDATA.
EXEC CICS SEND TEXT FROM(MSGDATA)
LENGTH(MSGLEN)
END-EXEC.
EXEC SQL
SELECT EMPNO INTO :DCLTABLEA1
FROM TABLEA1 WHERE TABLEA1.EMPNO = :W-EMPNO
END-EXEC.
MOVE 'FIRST SQL EXECUTED' TO MSGDATA.
EXEC CICS SEND TEXT FROM(MSGDATA)
LENGTH(MSGLEN)
END-EXEC.
MOVE 'AFTER START CODED' TO MSGDATA.
EXEC CICS SEND TEXT FROM(MSGDATA)
LENGTH(MSGLEN)
END-EXEC.
EXEC SQL
SELECT NAMEA INTO :DCLTABLEA1
FROM TABLEA1 WHERE TABLEA1.EMPNO = :W-EMPNO
END-EXEC.
MOVE 'SECOND SQL FINISHED' TO MSGDATA.
EXEC CICS SEND TEXT FROM(MSGDATA)
LENGTH(MSGLEN)
END-EXEC.
STOP RUN.
|
About the COBOL code
1. Displays all SQL messages.
2. Creates 2 tables.
3. Fetch and display the DB2 resources from the table.
Compilation job (CICS DB2)
|
//CICSDB2 JOB (544187,C322,C322),'IATEST2',NOTIFY=&SYSUID,
// MSGCLASS=A,CLASS=A,MSGLEVEL=(1,1),REGION=0M
//JOBPROC JCLLIB ORDER=SYS1.PROCLIB.CTC1.USER
//CICSSRC EXEC CICSSRC,
// DBRMLIB=IATEST2.NEWLOAD.PDS,
// DB2COPY=IATEST2.RESNAME.PDS,
// COPYLIB=IATEST2.COPY.PDS,
// LOADLIB=IATEST2.LOAD.PDSE,
// SRCELIB=IATEST2.RESNAME.PDS,
// MEM=SDPGMDB3
//DB2COB.SYSIN DD DSN=&SRCELIB(&MEM),DISP=SHR
//LKED.SYSLIB DD DSN=CICS.TS55.CICS.SDFHLOAD,DISP=SHR
// DD DSN=CEE.SCEELKED,DISP=SHR
// DD DSN=DB2.VC10.SDSNLOAD,DISP=SHR
// DD DSN=IATEST2.LOAD.PDSE,DISP=SHR
//LKED.SYSIN DD *
INCLUDE SYSLIB(DSNCLI) NAME SDPGMDB3(R)
//BIND EXEC PGM=IKJEFT01, COND=(4,LT), REGION=4096K
//STEPLIB DD DISP=SHR, DSN=DB2.VC10.SDSNLOAD
//DBRMLIB DD DISP=SHR, DSN=IATEST2.NEWLOAD.PDS(SDPGMDB3)
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTSIN DD * DSN SYSTEM (DCC1)
BIND MEMBER (SDPGMDB3) - PACKAGE (SYSPACKSTMT) - LIBRARY ('IATEST2.NEWLOAD.PDS') - ACTION (REP) -
ISOLATION (CS) - VALIDATE (BIND)- RELEASE (COMMIT) - OWNER (IATEST2) - QUALIFIER (IATEST2)
END
/*
|
D. Restart the CICS region
1. Enter the load module/load library name inside SYS1.PROCLIB.* system library in the respective CICS region.
Note: The above compilation job’s output is stored in the load module.
Here, the load module name is IATEST2.LOAD.PDSE, which is mentioned in the compilation job.
This load module name should be included in this system.
2. Enter SD; ST (SPOOL) in the command line and press Enter.
3. Enter “/” in the command line and press Enter to launch the System Command Extension.
4. To stop the region, use the command P Regionname
5. To start the region, use the command S Regionname
E. Define and Install resources in the CICS region
Launch the CICS region and follow the below steps:
1. Define and install the transaction CEDA DEF TRANS(SDB3)
a. Enter the group name (give a new name or use the existing one)
b. Enter the program name (compiled program) and press Enter
c. CEDA I TRANS(SDB3)
2. Define and install the program CEDA DEF PROG(SDPGMDB3)
a. Enter the group name (give a new name or use the existing one)
b. Enter the program name (compiled program) and press Enter.
c. CEDA I PROG(SDPGMDB3)
3. Load the installed program in CICS CECI LOAD PROG(SDPGMDB3)
4. Displays the resources allocated in the group.
5. CEDA DIS GROUP(GROUPAB) - DISPLAY GROUP NAME
6. DB2 entry in the CICS region for the defined transaction (The reason is EXEC SQL command is included in the COBOL program)
a. Run the CICS command - CEMT I DB2TRAN
b. Press F8 to verify whether the transaction is present or not.
c. Place the cursor under the plus icon and press Enter.
d. Enter the transaction name ‘SDB3’ and press Enter.
e. Now, define and install are completed.
f. Run the transaction and verify whether the transaction is working fine or not.
Note:
i. SDB3 – Transaction name
ii. SDPGMDB3 – Program name
iii. GROUPAB – Group name
F. CICS IA collector configuration
1. Run the transaction CINT.
2. Select option 2 and press ENTER.
3. Select option 4 and press ENTER.
4. Enter option I in the ‘Data to Collect’ for Interdependency data collection and press F3
5. Select option 5 and press ENTER .
6. Enter Y in ‘Collect DB2 Resources’ and ‘Collect resource name’
7. Press F3
8. Select option 1 and press ENTER.
9. Start the collector by giving option 1 and Press F3 2 times.
10. Enter the defined transaction SDB3 and click Enter.
11. Press F3 to clear the page.
12. Run the CINT transaction.
13. Give option 1 and stop the collector
G. Job submission in ISPF
1. Login to TSO and go to ISPF settings.
2. Enter 3.4 in the command line and Enter “CICSIAD.ATR.<<REGIONNAME>>.SCIUSAMP.CICS” (CICS IA libraries).
3. Go to the CIUUPDB job (the member which is located inside the library).
4. Enter the Collection ID and submit the job.
H. Client-side verification of data in the view
The following three methods are used to verify the output on the client side
Method 1
1. Launch the z/os explorer.
2. Open the ‘Database administrator’ perspective and make a DB2 connection.
3. Open a new SQL script and run the below SQL command to verify the output.
“SELECT COLLECTION_ID, APPLID, TRANSID, PROGRAM, RESTYPE, RESNAME, FUNCTION
FROM IA55DB1.CIU_DB2_DATA WHERE COLLECTION_ID=’COLLECTIONID’;”
4. The RESNAME column shows the resource name (TABLEA1) collected using CICS IA.
Method 2
1. Launch the z/os explorer.
2. Open the ‘Database Administrator’ perspective and make a DB2 connection.
3. Navigate to ‘Administration Explorer’ and expand ‘All Databases’ until the database location QXPCTCDCC displays.
4. Double-click on ‘Tables’.
5. All tables residing under this database appear.
6. Go to your respective schema.
7. Right-click on the table and explore Data.
8. Click on Browse Data.
9. It will list data inside the schema.
10. Check your last run collection id and verify the RESNAME column which shows the collected resource name.
Method 3
1. Go to ISPF settings.
2. Enter 14 in the command line and press Enter.
3. Enter DB or 5 in the command line and press Enter.
4. Provide subsystem id in SSID field (this can vary as per LPAR configuration)
5. Go to the option field.
6. Verify the latest version of DB2 (in our sample, V12)
7. Choose DC because it has DB2I V12 (this can vary as per LPAR configuration)
8. Enter 1 in the command and press ENTER for SPUFI.
9. Enter the data set names in the input and output fields within the single quote.
10. The rest of the fields should be ‘YES’.
11. Press Enter 2 times.
12. You will be redirected to the member inside the PDS file which is mentioned in the input dsname (IATEST2.COPY.PDS(DBDATA1)).
13. Enter the below SQL query inside the file.
“SELECT COLLECTION_ID, APPLID, TRANSID, PROGRAM, RESTYPE, RESNAME, FUNCTION
FROM IA55DB1.CIU_DB2_DATA WHERE COLLECTION_ID=’COLLECTIONID’;”
IA55DB1 - SCHEMA NAME and CIU_DB2_DATA - CICS DB2 table
14. To run the query inside the file. Press F3 and then press Enter.
15. You can see the below output.
Author
Swarna G
SENIOR SOFTWARE ENGINEER - CICS IA