Program stack information via SQL

The guys at RZKH applied the latest Technology Refresh PTFs over the weekend, which means I can start playing with the new things. I am starting with the new table function STACK_INFO.

STACK_INFO allows me to use a Select statement to retrieve a row for each entry in the call stack for a specific thread in a job. It supports all types of call stacks: OPM, ILE, Java, PASE, and even the LIC stack. It gives me that same information I could get using the Display Job command, DSPJOB, or the Retrieve Call Stack API, QWVRCSTK. To be able to use this table function my profile must have *JOBCTL special authority, and *SERVICE to get the LIC stack entries.

This table function has two "parameters":

1. JOB_NAME:  Qualified name of the job, * for the current job, or if JOB_NAME is not given the current job is assumed.
2. THREAD_ID:  ALL information from all the job's threads is returned. INITIAL information from the initial thread is returned. Default is INITIAL.

For example:

SELECT * FROM TABLE(QSYS2.STACK_INFO('999999/USER/JOBNAME','ALL')) A

I am returning all the available rows for all threads within the job 999999/JOBNAME/USER. Like with all other table functions I have to have a character after the table parameter, otherwise the statement will error. You will find a list of all the available columns in the KnowledgeCenter's page for this table function, you will find a link to that page at the bottom of this post.

My examples are going to be very simple. My user profile does not have *SERVICE authority, therefore, I cannot retrieve the LIC information. And I have no Java in my call stack.

You cannot get much simpler than my first example. I signed on, typed STRSQL and pressed enter, then entered the following Select statement.

01 SELECT THREAD_ID AS THREAD, 02 ORDINAL_POSITION AS POS, 03 ENTRY_TYPE AS TYPE, 04 PROGRAM_NAME AS PROGRAM, 05 PROGRAM_LIBRARY_NAME AS P_LIBRARY, 06 CAST(STATEMENT_IDENTIFIERS AS CHAR(15)) AS STMT_ID, 07 REQUEST_LEVEL AS REQ_LVL, 08 CONTROL_BOUNDARY AS BOUNDARY, 09 PROGRAM_ASP_NAME AS ASP_NAME, 10 PROGRAM_ASP_NUMBER AS ASP_NBR, 11 MODULE_NAME AS MODULE, 12 MODULE_LIBRARY_NAME AS M_LIBRARY, 13 CAST(PROCEDURE_NAME AS CHAR(25)) AS PROCEDURE, 14 ACTIVATION_GROUP_NUMBER AS ACTGRP_NBR, 15 ACTIVATION_GROUP_NAME AS ACTGRP_NME, 16 MI_INSTRUCTION_NUMBER AS MI_NBR 17 FROM TABLE(QSYS2.STACK_INFO()) A

Lines 1 – 16: I have given the columns short names to make it easier to show each column's data. The columns I chose were:

• THREAD_ID:  Thread's identifier
• ORDINAL_POSITION:  1 is the first invocation entry, and the highest number is the most recent
• ENTRY_TYPE:  Type of stack entry
• PROGRAM_NAME:  Program name
• PROGRAM_LIBRARY_NAME:  Library the program is in
• STATEMENT_IDENTIFIERS:  High level language statement identifier
• REQUEST_LEVEL:  Level of the request processing program. If it null the program has not received a request message or only incomplete information is available
• CONTROL_BOUNDARY:  Control boundary is defined as any ILE call stack entry where the preceding entry is for an ILE program or procedure in a different activation group
• PROGRAM_ASP_NAME:  Name of the ASP. *SYSBAS shows that the program is located in the system ASP
• PROGRAM_ASP_NUMBER:  Number of the ASP containing the program. 1 shows that the program is in the system ASP
• MODULE_NAME:  Name of the module containing the procedure
• MODULE_LIBRARY_NAME:  Program that the module is in
• PROCEDURE_NAME:  Name of the procedure in the module
• ACTIVATION_GROUP_NUMBER:  Number of the activation group the program or procedure is running in
• ACTIVATION_GROUP_NAME:  Name of the activation group. *DFTACTGRP means that the activation group does not have a name. *NEW the activation group does not have a name and it was created when the program was called.
• MI_INSTRUCTION_NUMBER:  The current machine instruction number in the program. Will be null if this is not an OPM program

Line 17: As I used no "parameters" this is for the current job, and for the initial thread.

My results are:

THREAD POS TYPE PROGRAM P_LIBRARY STMT_ID REQ_LVL BOUNDARY 70 20 ILE QDBSSUDF2 QSYS 0000003287 - NO 70 19 ILE QDBSSUDF2 QSYS 0000002827 - NO 70 18 ILE QDBSSUDF2 QSYS 0000002734 - NO 70 17 ILE QQQSVRTN QSYS 0000016115 - NO 70 16 ILE QQQSVRTN QSYS 0000012324 - NO 70 15 ILE QQQSVRTN QSYS 0000012048 - NO 70 14 ILE QQQSVRTN QSYS 0000011762 - NO 70 13 ILE QQQOOOUPCL QSYS 0000003083 - NO 70 12 ILE QQQOOOUPCL QSYS 0000000001 - NO 70 11 ILE QDBGETMQO QSYS 0000002942 - YES 70 10 OPM QQURB QSYS - - NO 70 9 OPM QQURA QSYS - - NO 70 8 OPM QQUDA QSYS - - NO 70 7 OPM QSQISE QSQL - - NO 70 6 OPM QSQIMAIN QSQL - - NO 70 5 OPM QUICMD QSYS - 3 NO 70 4 OPM QUIMGFLW QSYS - 2 NO 70 3 OPM QUIMNDRV QSYS - 1 NO 70 2 OPM QUICMENU QSYS - - NO 70 1 OPM QCMD QSYS - - YES ASP_NAME ASP_NBR MODULE M_LIBRARY PROCEDURE *SYSBAS 1 QSQSTKINFO QBUILDSS1 RETRIEVE_CALL_STACK *SYSBAS 1 QSQSTKINFO QBUILDSS1 OPEN_CALL *SYSBAS 1 QSQSTKINFO QBUILDSS1 QSQSTKINFO *SYSBAS 1 QQINVUDF QBUILDSS1 CALLSYSTEMSTATEUDF *SYSBAS 1 QQINVUDF QBUILDSS1 UDTFOPENCALL *SYSBAS 1 QQINVUDF QBUILDSS1 IMPLEMENTUDTFINSQE *SYSBAS 1 QQINVUDF QBUILDSS1 QQINVOKEUDF *SYSBAS 1 QQQOOOCALL QBUILDSS1 DBOPUPCALLUDF *SYSBAS 1 QQQOOOUPCL QBUILDSS1 DbopUDTFCall__FP11DbopUDFParm *SYSBAS 1 QDBGETMQO QBUILDSS1 QDBGETMQO *SYSBAS 1 - - - *SYSBAS 1 - - - *SYSBAS 1 - - - *SYSBAS 1 - - - *SYSBAS 1 - - - *SYSBAS 1 - - - *SYSBAS 1 - - - *SYSBAS 1 - - - *SYSBAS 1 - - - *SYSBAS 1 - - - ACTGRP_NBR ACTGRP_NME MI_NBR 1 *DFTACTGRP - 1 *DFTACTGRP - 1 *DFTACTGRP - 1 *DFTACTGRP - 1 *DFTACTGRP - 1 *DFTACTGRP - 1 *DFTACTGRP - 1 *DFTACTGRP - 1 *DFTACTGRP - 1 *DFTACTGRP - - - 1,655 - - 158 - - 1,018 - - 1,868 - - 1,498 - - 1,391 - - 1,239 - - 1,566 - - 193 - - 1,305

How does this compare to what I can see of the call stack when I use option 11, "Display call stack", when I use the Display Job, DSPJOB, command?

Display Call Stack Type Program Statement Procedure QCMD QSYS /0519 QUICMENU QSYS /00C1 1 QUIMNDRV QSYS /061E 2 QUIMGFLW QSYS /04D7 3 QUICMD QSYS /056F QSQIMAIN QSQL /05DA QSQISE QSQL /074C QQUDA QSYS /03FA QQURA QSYS /009E QQURB QSYS /050E QWSGET QSYS /0667 QT3REQIO QSYS /0256 ------Activation Group------ Control Type Program Name Number Boundary QCMD QSYS *DFTACTGRP 0000000000000001 Yes QUICMENU QSYS *DFTACTGRP 0000000000000001 No 1 QUIMNDRV QSYS *DFTACTGRP 0000000000000001 No 2 QUIMGFLW QSYS *DFTACTGRP 0000000000000001 No 3 QUICMD QSYS *DFTACTGRP 0000000000000001 No QSQIMAIN QSQL *DFTACTGRP 0000000000000001 No QSQISE QSQL *DFTACTGRP 0000000000000001 No QQUDA QSYS *DFTACTGRP 0000000000000001 No QQURA QSYS *DFTACTGRP 0000000000000001 No QQURB QSYS *DFTACTGRP 0000000000000001 No QWSGET QSYS *DFTACTGRP 0000000000000001 No QT3REQIO QSYS *DFTACTGRP 0000000000000001 No Module or Statement Type Program Expanded Type Identifiers QCMD QSYS QUICMENU QSYS 1 QUIMNDRV QSYS 2 QUIMGFLW QSYS 3 QUICMD QSYS QSQIMAIN QSQL QSQISE QSQL QQUDA QSYS QQURA QSYS QQURB QSYS QWSGET QSYS QT3REQIO QSYS

Let me make this a bit more like "real life". I have a CL program that calls a RPG program. In the RPG program I have some SQL that will create a table of the stack information.

My CL program, EG005, just calls the RPG program. I have included the source sequence numbers, you will see why in a while.

0001.00 PGM 0002.00 0003.00 CALL PGM(EG005A) 0004.00 0005.00 ENDPGM

The RPG program is simple too.

0001.00 **free 0002.00 ctl-opt option(*nodebugio:*srcstmt:*nounref) 0003.00 exec sql SET OPTION COMMIT = *NONE ; 0004.00 0005.00 exec sql CREATE TABLE QTEMP.OUTPUT AS ( 0006.00 SELECT ORDINAL_POSITION, 0007.00 ENTRY_TYPE, 0008.00 STATEMENT_IDENTIFIERS, 0009.00 PROGRAM_NAME, 0010.00 MODULE_NAME, 0011.00 PROCEDURE_NAME 0012.00 FROM TABLE(QSYS2.STACK_INFO('*', 'ALL')) A) 0013.00 WITH DATA ; 0014.00 0015.00 *inlr = *on ;

Line 1: My RPG is now always totally free.

Line 2: My favorite control options. It is important to notice that I am using the *SRCSTMT option, when the program is compiled it will use the source sequence numbers as the statement identifiers.

Line 3: I do not want to have a journal or use commitment control so I use the SET OPTION to disable it.

Lines 5 - 13: I am just going to Select a few columns into my table, and I want the information for my current job and all of its threads. If you want more information on using CREATE TABLE like this to create a file see the post Creating a SQL table "on the fly".

Now I can just use a Select statement to retrieve the results from my table.

01 SELECT ORDINAL_POSITION AS POS, 02 ENTRY_TYPE AS TYPE, 03 CAST(STATEMENT_IDENTIFIERS AS CHAR(10)) AS STMT_NBR, 04 PROGRAM_NAME AS PROGRAM, 05 MODULE_NAME AS MODULE, 06 CAST(PROCEDURE_NAME AS CHAR(20)) AS PROCEDURE 07 FROM TABLE(QTEMP.OUTPUT()) A 08 ORDER BY ORDINAL_POSITION

My results show the source sequence number from the CL program, see line 10, and from the RPG program, see line 12. Both of these numbers match the source line numbers.

POS TYPE STMT_NBR PROGRAM MODULE PROCEDURE 1 OPM - QCMD - - 2 OPM - QUICMENU - - 3 OPM - QUIMNDRV - - 4 OPM - QUIMGFLW - - 5 OPM - QUICMD - - 6 OPM - QUOCPP - - 7 OPM - QUOMAIN - - 8 OPM - QUOCMD - - 9 ILE - EG005 EG005 _CL_PEP 10 ILE 0000000300 EG005 EG005 EG005 11 ILE - EG005A EG005A _QRNP_PEP_EG005A 12 ILE 0000001300 EG005A EG005A EG005A 13 ILE 0000018581 QSQROUTE QSQROUTE QSQROUTE 14 ILE 0000038514 QSQCRTT QSQCRTT QSQCRTT 15 ILE 0000012275 QSQROUTX QSQROUTX QSQROUTX 16 ILE 0000012772 QSQRUN3 QSQINS SQL_Insert 17 ILE 0000002942 QDBGETMQO QDBGETMQO QDBGETMQO 18 ILE 0000000001 QQQOOOUPCL QQQOOOUPCL DbopUDTFCall__FP11Db 19 ILE 0000003083 QQQOOOUPCL QQQOOOCALL DBOPUPCALLUDF 20 ILE 0000011762 QQQSVRTN QQINVUDF QQINVOKEUDF 21 ILE 0000012048 QQQSVRTN QQINVUDF IMPLEMENTUDTFINSQE 22 ILE 0000012324 QQQSVRTN QQINVUDF UDTFOPENCALL 23 ILE 0000016115 QQQSVRTN QQINVUDF CALLSYSTEMSTATEUDF 24 ILE 0000002734 QDBSSUDF2 QSQSTKINFO QSQSTKINFO 25 ILE 0000002827 QDBSSUDF2 QSQSTKINFO OPEN_CALL 26 ILE 0000003287 QDBSSUDF2 QSQSTKINFO RETRIEVE_CALL_STACK

If the latest Db2 of i TR PTF has been loaded to your IBM i I am sure you can come up with more complex examples for yourselves.

 

You can learn more about the STACK_INFO table function from the IBM website here.

 

This article was written for IBM i 7.3 TR3 and 7.2 TR7.