STACK_INFO table function

The STACK_INFO table function returns one row for each entry in the call stack for either a specific thread or for every thread of the specified job. It returns information similar to what can be accessed through the Display Job (DSPJOB) CL command and the Retrieve Call Stack (QWVRCSTK) API.

The schema is QSYS2.

Authorization: The authorization ID of the statement must have *JOBCTL special authority or must be the same user profile that is running the specified job-name. If the authorization ID has *SERVICE special authority, the returned call stack information will include Licensed Internal Code (LIC) stack entries.

job-name

The qualified job name to return stack information for. Can contain the following special value:

*: The current job name is used.

If job-name is not specified, the default is *.

thread-id

A numeric expression indicating the thread identifier to return information for. Can contain one of the following special values:

ALL: Information for all the threads in the job is returned.
INITIAL: Information for the initial thread of the job is returned.

If thread-id is not specified:

If job-name is *, the default is the value of the QSYS2.THREAD_ID global variable.
Otherwise, the default is INITIAL.

The result of the function is a table containing multiple rows with the format shown in the following table. All the columns are nullable.

Table 1. STACK_INFO table function
Column Name	Data Type	Description
THREAD_ID	BIGINT	The identifier for the specific thread.
THREAD_TYPE	VARCHAR(6)	Specifies how the thread was initiated. SYSTEM The thread was initiated by the operating system. USER The thread was initiated by a user process. Contains the null value unless `ALL` was specified for the thread-id input parameter.
ORDINAL_POSITION	INTEGER	A unique number for each row corresponding to a thread where 1 is the first invocation entry for this thread and the highest number is the most recent invocation entry for this thread.
ENTRY_TYPE	VARCHAR(4)	The type of stack entry. ILE This entry returns ILE program information. The columns specific to JAVA, PASE, and LIC contain the null value. JAVA This entry returns JAVA information. The columns specific to ILE and OPM, PASE, and LIC contain the null value. LIC This entry returns Licensed Internal Code (LIC) information. The columns specific to ILE and OPM, JAVA, and PASE contain the null value. OPM This entry returns OPM program information. The columns specific to JAVA, PASE, and LIC contain the null value. PASE This entry returns PASE information. The columns specific to ILE and OPM, JAVA, and LIC contain the null value.
––– ILE and OPM information ––––––––
PROGRAM_NAME	VARCHAR(10)	The name of the program or service program. Contains the null value if the program name is not available.
PROGRAM_LIBRARY_NAME	VARCHAR(10)	The name of the library in which the program is located. Contains the null value if the program is not located in a library or if the program library name is not available.
STATEMENT_IDENTIFIERS	VARCHAR(109)	The high-level language statement identifier. If this column contains the character representation of a number, the number is right-adjusted and padded on the left with zeros (for example, '0000000246'). If the call stack entry is for an integrated language environment (ILE) procedure, more than one statement identifier may exist. If more than one statement identifier is returned, each identifier will be ten characters long with a single blank between them. Up to ten identifiers will be returned. Returns the null value if a statement identifier cannot be determined.
REQUEST_LEVEL	INTEGER	The level of the request-processing program or procedure. Contains the null value if the program or procedure has not received a request message or incomplete information is available.
CONTROL_BOUNDARY	VARCHAR(3)	Whether a control boundary exists for a program or procedure. A control boundary is defined as any ILE call stack entry for which the immediately preceding call stack entry is for an ILE procedure or program object in a different activation group. NO No control boundary is active. YES A control boundary is active. Contains the null value if information is not available or incomplete information is available.
PROGRAM_ASP_NAME	VARCHAR(10)	The name of the auxiliary storage pool (ASP) device in which the program is located. Can contain the following special value: *SYSBAS The program is located in the system ASP or a basic user ASP Contains the null value if the name of the ASP cannot be determined.
PROGRAM_ASP_NUMBER	INTEGER	The numeric identifier of the ASP containing the program. 1 The program is in the system ASP. 2-32 The program is in a basic user ASP. 33-255 The program is in an independent ASP. Contains the null value if the ASP device cannot be determined.
MODULE_NAME	VARCHAR(10)	The module containing the integrated language environment (ILE) procedure. Contains the null value if this is not an ILE program or if the module name is not available.
MODULE_LIBRARY_NAME	VARCHAR(10)	The name of the library in which the module is located. Contains the null value if this is not an ILE program or if the module library name is not available.
PROCEDURE_NAME	VARCHAR(4096)	The name of the procedure at this level of the call stack. Returns the null value if this is not an ILE program or if the procedure name cannot be determined.
ACTIVATION_GROUP_NUMBER	DECIMAL(20,0)	The number of the activation group within which the program or procedure is running. This is an internal number that uniquely identifies the activation group within the job. Contains the null value if this is not an ILE program or incomplete information is available.
ACTIVATION_GROUP_NAME	VARCHAR(10)	The name of the activation group within which the program or procedure is running. Can contain the following special values: DFTACTGRP The activation group does not have a specific name. The activation group is one of the default activation groups for the system. NEW The activation group does not have a specific name. The activation group was created when the program was called. Contains the null value if this is not an ILE program or incomplete information is available.
MI_INSTRUCTION_NUMBER	INTEGER	The current machine instruction number in the program. Contains the null value if this is not an OPM program.
––– JAVA information ––––––––
JAVA_LINE_NUMBER	INTEGER	The line number where the invocation was interrupted. Contains the null value if no line number can be determined.
JAVA_BYTE_CODE_OFFSET	INTEGER	The offset in bytes from the beginning of the Java method byte codes to the resume point for the invocation. Contains the null value if no Java byte code offset can be determined.
JAVA_METHOD_TYPE	VARCHAR(9)	The type of Java method. DE The method is a direct execution Java method. The Java method has been precompiled by the Java Transformer. GLUE The invocation is a Java Virtual Machine glue frame used either to perform a call from the JVM to a Java method or perform a call to a Java native method. INTERPRET The method is an interpreted Java method. The Java method is being interpreted by the Java Interpreter. JIT The method is a JIT compiled Java method. The Java method has been compiled by the Java Just In Time Compiler. MMI The method is a MMI interpreted Java method. The Java method is being interpreted by the Mixed Mode Java Interpreter. Contains the null value if there is no information.
JAVA_CLASS_NAME	DBCLOB(64000) CCSID 1200	The name of the Java class at this level of the call stack. Returns the null value if the class name cannot be determined.
JAVA_METHOD_NAME	DBCLOB(64000) CCSID 1200	The name of the Java method at this level of the call stack. Returns the null value if the method name cannot be determined.
JAVA_METHOD_SIGNATURE	DBCLOB(64000) CCSID 1200	The signature of the Java method at this level of the call stack. Returns the null value if the signature cannot be determined.
JAVA_FILE_NAME	DBCLOB(64000) CCSID 1200	The name of the Java file and directory that provides the location of where the Java class was loaded at this level of the call stack. If the Java class was loaded from a .jar or .zip file, then the location will be the path to and the name of the .jar or .zip file. If the class was loaded from a .class file, then the location will be the directory from which the class was loaded. Returns the null value if the file name cannot be determined.
JAVA_SOURCE_FILE_NAME	DBCLOB(64000) CCSID 1200	The name of the Java source file at this level of the call stack. Returns the null value if the source file name cannot be determined.
––– PASE information –––––––
PASE_LINE_NUMBER	BIGINT	The line number where the invocation was interrupted. Contains the null value if no line number can be determined.
PASE_INSTRUCTION_ADDRESS	DECIMAL(20,0)	The IBM PASE for i memory address for the instruction that will run when execution resumes for the invocation.
PASE_INSTRUCTION_OFFSET	BIGINT	The offset in bytes from the beginning of the start of the procedure to the instruction that is either the suspend point for the invocation or the resume point for the invocation.
PASE_KERNEL_CODE	VARCHAR(3)	Whether the invocation is running IBM PASE for i kernel code. NO The current invocation is not IBM PASE for i kernel code. YES The current invocation is IBM PASE for i kernel code. Contains the null value if there is no information.
PASE_BIT_CODE	INTEGER	Whether the invocation is running 32-bit or 64-bit IBM PASE for i code. 32 The invocation is running 32-bit IBM PASE for i code. 64 The invocation is running 64-bit IBM PASE for i code. Contains the null value if `PASE_KERNEL_CODE` is `YES`.
PASE_ALTERNATE_RESUME_POINT	VARCHAR(3)	Whether the current entry is a second entry for a given invocation. This flag is only used when the system can not reliably determine which of two possible resume points will be used when an invocation resumes execution. NO The current invocation does not have an alternate resume point. YES The current invocation has an alternate resume point. Contains the null value if there is no information.
PASE_PROCEDURE_NAME	DBCLOB(4000) CCSID 1200	The name of the procedure at this level of the call stack. Returns the null value if the procedure name cannot be determined.
PASE_LOAD_MODULE_NAME	DBCLOB(1000) CCSID 1200	The name of the load module at this level of the call stack. Returns the null value if the load module name cannot be determined.
PASE_LOAD_MODULE_PATH	DBCLOB(4000) CCSID 1200	The path to the load module at this level of the call stack. Returns the null value if the load module path cannot be determined.
PASE_SOURCE_PATH_AND_FILE	DBCLOB(1000) CCSID 1200	The path and name for the source file used to create the procedure. Returns the null value if the path and name for the source file cannot be determined.
––– LIC information –––––––
LIC_INSTRUCTION_OFFSET	BIGINT	The offset in bytes from the beginning of the start of the procedure to the instruction that is either the suspend point for the invocation or the resume point for the invocation.
LIC_PROCEDURE_NAME	VARCHAR(4096)	The name of the procedure at this level of the call stack. Returns the null value if the procedure name cannot be determined.
LIC_LOAD_MODULE_NAME	VARCHAR(64)	The name of the load module at this level of the call stack. Returns the null value if the load module name cannot be determined.

Example

Find out whether ILE program MYPGM is on the stack for the current thread.

SELECT * FROM TABLE(QSYS2.STACK_INFO('*')) A 
  WHERE PROGRAM_NAME = 'MYPGM';

Create a table that contains the stack for all of the threads in a specific job.

CREATE TABLE STACK_DUMP AS (
  SELECT * FROM TABLE(QSYS2.STACK_INFO('358788/QLIWISVR/ADMIN1', 'ALL')) AS X
  ) WITH DATA;