Technical Blog Post
Abstract
75 ways to demystify DB2 #42: Techtip : Wondering how to determine the status of the section of a package in DB2?
Body
Do you know how to get the status of the section of a package in DB2, here's an easy command to retrieve this information by querying the system catalogs:
DB2 on zos:
Verify the status of the section from DB2 Client against DB2 zos by issuing the below statement: (Normally this is handy when SQ0525N bind error occurs)
db2 "select contoken, SEQNO, STMTNO , SECTNO, STATUS FROM SYSIBM.SYSPACKSTMT WHERE COLLID = 'NULLID' AND NAME = 'SQLUAK20'"
C
Compiled - statement was bound successfully using defaults for input variables during access path selection.
L
Bad - the statement has some allowable error. The bind continues but the statement cannot be executed.
For example:
If you want to know the status of section 34 for package = "xxx.NULLID.SYSSTAT" consistency token = X"5359534C564C3031", here's how you do it:
- From DB2 client, connect to DB2 zos remotely using: db2 connect to <dbname> user <username> using <password>
- Once you are connected to DB2 zos, issue the below statement:
db2 select contoken, SEQNO, STMTNO , SECTNO, STATUS FROM SYSIBM.SYSPACKSTMT WHERE COLLID = 'NULLID' AND NAME = 'SYSSTAT'
CONTOKEN SEQNO STMTNO SECTNO STATUS x'5359534C564C3031' 34 281 34 C<--------------------------- status shows compiled =================
DB2 on Linux, Unix and Windows:
- From DB2 Client (if remote) or DB2 Server, connect to database
- Then issue the below statement:
db2 "select PLNAME , PLCREATOR, SECTNO, VALID FROM sysibm.SYSSECTION where PLNAME = 'SQLUAK20' AND PLCREATOR = 'NULLID'"
Check if column VALID is Y/N.
For example:
If you want to know the status of the section for package = "NULLID.SQLUAK20", here's how you do it:
C:\Program Files\IBM\SQLLIB\java\jdk\bin>db2 "select PLNAME , PLCREATOR, SECTNO VALID FROM sysibm.SYSSECTION where PLNAME = 'SQLUAK20' AND PLCREATOR = 'NULLID'"
PLNAME PLCREATOR SECTNO VALID
--------------------------------------------------------------------------------------------------------------------------
SQLUAK20 NULLID 5 Y
SQLUAK20 NULLID 6 Y
SQLUAK20 NULLID 8 Y
SQLUAK20 NULLID 9 Y
SQLUAK20 NULLID 10 Y
SQLUAK20 NULLID 11 Y
6 record(s) selected.
References:
Thanks for reading!
Please leave a comment below if you have any questions or suggestions.
UID
ibm11141030