Retrieve The Call Stack In DB2 For i

Stack Entry	Pgm No	PgmNam	PgmLib	Statement	Procedure
1	1	PROCEDURE3	QGPL	65	main
2	1	PROCEDURE3	QGPL		_C_pep
3	2	PROCEDURE2	QGPL	14	main
4	2	PROCEDURE2	QGPL		_C_pep
5	3	PROCEDURE1	QGPL	14	main
6	3	PROCEDURE1	QGPL		_C_pep

Each time an ILE C program starts there are two call stack entries: main and _C_pep (program entry point). You can eliminate the unnecessary rows by filtering out the _C_pep value in the WHERE clause.

A few notes on the output columns from this table function: The “stack entry” column is a sequential counter for uniquely identifying each row. The “program number” column is a counter that increments each time a new program is encountered on the call stack. Because each program can have multiple procedures in the call stack, this value indicates when a new program boundary is encountered. The “Request Level” column (not shown) is returned from the QWVRCSTK API, but doesn’t seem to convey much information.

With as little or much detail as needed, the complete ILE/OPM call stack is available to your SQL application. This information can be very useful in an error handler. If, for example, a stored procedure receives an error, this call stack could be formatted as text, XML, or HTML, stuffed in a LONG VARCHAR or CLOB and saved to a table or IFS file for later review.

Procedure4 shown below shows an error handler that wraps the call stack into a simple text string formatted with carriage return/line feed characters (X’0D25′ in US EBCDIC).

CREATE PROCEDURE PROCEDURE4
SET OPTION COMMIT=*NONE
BEGIN
	DECLARE @CALLSTACK VARCHAR(6144) NOT NULL DEFAULT 'CS:';

	DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
	BEGIN
    	DECLARE @SQL_STATE CHAR(5) NOT NULL DEFAULT '00000';
    	DECLARE @MSG_TEXT  VARCHAR(70);

    	GET STACKED DIAGNOSTICS EXCEPTION 1
           	@MSG_TEXT=MESSAGE_TEXT;

    	SET @CALLSTACK='Error '||@MSG_TEXT||X'0D25';

     -- When an error occurs, get the call stack
    	FOR DATA AS CALLSTACK_NO_IBM CURSOR FOR
    	SELECT STACKENTRY,PGMNAM,STATEMENT
      	FROM TABLE(QGPL.GETCALLSTACK('Y')) CS
     	WHERE PROCEDURE<>'_C_pep'
    	ORDER BY STACKENTRY
    	DO
        	SET @CALLSTACK=@CALLSTACK ||
            	CAST(STACKENTRY AS VARCHAR(10)) ||
            	' ' || PGMNAM || ' stmt: ' ||
            	STATEMENT || X'0D25';
    	END FOR;

     -- Do something with the text data
    	RETURN;
	END;

	-- Erroneous code encountered
	SIGNAL SQLSTATE '38001'
	SET MESSAGE_TEXT='I Goofed';
END

The formatted call stack text will look like this:

Error I Goofed
1 PROCEDURE4 stmt: 0000000081
2 PROCEDURE4 stmt: 0000000041
4 PROCEDURE5 stmt: 0000000014

Who Am I? Retrieving The Current Program Name

Let’s take another example of what can be gleaned from the call stack. Programatically getting the name of the current procedure (without hardcoding it) is useful when recording audit information about what code changed a table’s row.

SQL Server, for instance, has a metadata function called @@PROCID that will return the unique identifier of an SQL module (procedure, trigger, function). DB2 for i has no such analog (although in limited cases GET DIAGNOSTICS can return the ROUTINE_NAME). The CallStackEntry scalar function can give DB2 the same functionality as @@PROCID and more.

The inputs to the CallStackEntry function are:

• RelativeStackEntry–The call stack entry relative to the current program. When set to one, the function will return the name of the current program and/or procedure. When set to two, it will return the prior entry in the call stack, and so on. If the requested relative stack entry is unavailable in the current call stack, the function will return an empty string.
• UsePgmLevel–This Y/N flag is used to determine whether or not the call stack entries will be reckoned at the program level or the procedure level. When ‘Y’ is requested, only distinct program names are included in the call stack. When ‘N’ is passed, each procedure name is also included in the call stack for consideration. This setting affects the output of the function (see below).
• IgnoreSysLibs–Ignore the IBM system libraries QSYS, QPDA, and QSQL when building the call stack.

The function’s output is a VARCHAR(160), but the value it returns is one of two formats depending on how UsePgmLevel was populated:

• UsePgmLevel=Y: The function returns a CHAR(20) value of the system library name (fixed length) and the system program name (fixed length). Example: QGPL MY_PGM
• UsePgmLevel=N: The function returns a qualified library and program name with the procedure name included in the following format: LIB/PGM.procedure. Example: QGPL/MY_PGM.main.

So how is the function used? Say there’s a need to have a trigger record the table, time, user, and program of a row insert in the following AUDIT_TEST table:

CREATE TABLE QGPL.AUDIT_TEST (
TABLE_NAME CHAR(10) NOT NULL,
USER CHAR(10) NOT NULL,
CHANGE_STAMP TIMESTAMP NOT NULL,
CHANGE_PGM   CHAR(20));

To make things interesting, several application tables will need this audit table populated and the trigger code should be consistent, if possible. Because of code sharing, of particular interest is the need for the trigger to dynamically get values for the TABLE_NAME (base table) and CHANGE_PGM columns. The CHANGE_PGM is not the trigger itself, but rather the name of the SQL procedure or HLL program that executed an INSERT.

Here is one of the many “fancy” application tables that need auditing:

CREATE TABLE QGPL.MY_DATA (
DUMMY VARCHAR(256));

The following trigger will record the information in the audit table after data is inserted into table MY_DATA:

CREATE OR REPLACE TRIGGER QGPL.TRIGGER_MY_DATA
AFTER INSERT ON QGPL.MY_DATA 
FOR EACH STATEMENT
MODE DB2SQL                          	 
SET OPTION COMMIT=*NONE, USRPRF=*OWNER
BEGIN
	DECLARE @CALLER  CHAR(20);
	DECLARE @TRIGGER CHAR(20);
	DECLARE @LONG_TABLE VARCHAR(257);
	DECLARE @TABLE      VARCHAR(10);

	/* Get name of trigger - CHAR(20)  */
	SET @TRIGGER=QGPL.CALLSTACKENTRY(1,'Y','Y');
	/* Get name of base table from trigger catalog  */
	SELECT EVENT_OBJECT_SCHEMA || '.' || EVENT_OBJECT_TABLE,
       	SYSTEM_EVENT_OBJECT_TABLE
  	INTO @LONG_TABLE, @TABLE
  	FROM QSYS2.SYSTRIGGERS
 	/* Get trigger name based on system program name */
 	WHERE TRIGGER_PROGRAM_LIBRARY=LEFT(@TRIGGER,10)
   	AND TRIGGER_PROGRAM_NAME=RIGHT(@TRIGGER,10);

	/* Get name of caller - CHAR(20)  */
	SET @CALLER=QGPL.CALLSTACKENTRY(2,'Y','Y');

	INSERT INTO QGPL.AUDIT_TEST
	VALUES(@TABLE,USER,CURRENT_TIMESTAMP, @CALLER);
END;

The trigger uses the CALLSTACKENTRY function with a call stack level of 1 to dynamically get its own name:

SET @TRIGGER=QGPL.CALLSTACKENTRY(1,'Y','Y');

Also, the trigger uses the CALLSTACKENTRY function with a call stack level of 2 to get the name of the prior non-IBM program on the call stack. This will either be blank (for dynamic SQL) or the program name of a procedure that ran an INSERT statement:

SET @TRIGGER=QGPL.CALLSTACKENTRY(2,'Y','Y');

Now, to validate that it works, create a stored procedure to insert data into table MY_DATA:

CREATE OR REPLACE PROCEDURE QGPL.INSERT_MY_DATA
SET OPTION COMMIT=*NONE
BEGIN
	INSERT INTO QGPL.MY_DATA VALUES('Hello');
END

Invoke the procedure:

CALL QGPL.INSERT_MY_DATA;

Finally, query the audit table:

SELECT * FROM QGPL.AUDIT_TEST;

The query of the audit table will show you the following results: