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:

SQL0525N: http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.messages.sql.doc/doc/msql00525n.html

Thanks for reading!

Please leave a comment below if you have any questions or suggestions.