Wednesday, November 1, 2017

Program stack information via SQL

stack_info to get call stock information using 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_IDALL 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.

11 comments:

  1. Love the new functions cant wait to get to that level of OS so I can use all that IBM POWER

    ReplyDelete
  2. This is a really good option. If there is new function for file opens with their respective RRN's then that would be really wonderfully for all the guys working with LCKS/MSGW analysis.

    ReplyDelete
  3. Great post once again! Thanks.

    One question: Maybe I am missing something here, but the final SQL is on QTEMP, right?
    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(QSYS2.STACK_INFO()) A
    08 ORDER BY ORDINAL_POSITION

    JJJ

    ReplyDelete
    Replies
    1. I am beginning to think my example was confusing. The reason the last SQL statement was using QTEMP is that I had created an output file in the statement before with the name STACK_INFO. I am going to change the example code to remove the confusion,

      Delete
  4. Thanks again.

    This gets me the "current" program in the stack for the even I need.

    SELECT PROGRAM_NAME AS PROGRAM
    FROM TABLE(QSYS2.STACK_INFO()) A WHERE
    PROGRAM_LIBRARY_NAME not like 'Q%'
    and ORDINAL_POSITION < (
    SELECT Min(ORDINAL_POSITION) FROM TABLE(QSYS2.STACK_INFO()) A WHERE
    PROGRAM_NAME in (
    'AA9999',
    'BB_XXX',
    'BB_YYY'))
    fetch first row only

    ReplyDelete
    Replies
    1. Thanks - This worked great in getting the Program Name within a Trigger!

      Delete
  5. Hey, it seems it's USER/JOBNAME not JOBNAME/USER.
    SELECT * FROM TABLE(QSYS2.STACK_INFO('999999/USER/JOBNAME','ALL')) A

    ReplyDelete
  6. Anonymous is right. It is job number/user/job name.

    ReplyDelete
  7. thanks Simon, stack_info() would surely be handy

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.