This Db2 for z/OS News from the Lab blog entry was originally published on 2018-11-02.

By Jae Lee and Paul McWilliams.

In Db2 11 and 12, APARs PI44721 and introduce tracing procedures for writing trace information from native SQL procedures. Application developers can use these procedures to debug native SQL routines, by writing SQL statements to write and retrieve the trace information. These new procedures are especially useful for debugging SQL routines running on production systems.

APAR PI44721 introduces the following sample procedures in the DSN8 schema:

• DSN8.DISABLE
• DSN8.ENABLE
• DSN8.GET_LINE
• DSN8.GET_LINES
• DSN8.NEW_LINE
• DSN8.PUT
• DSN8.PUT_LINE

APAR PI93887 introduces support in Db2 for z/OS for invoking functions defined with MODIFIES SQL DATA from a subselect. This makes it possible for the trace procedures to be called from a function which is invoked from subselect. Before APAR PI93887, Db2 for z/OS issues SQLCODE -740 for this situation.

Let's look at how to invoke the new trace procedures from a compiled SQL scalar function to obtain trace data. In this example, we trace the function REVERSE which returns the text of an input string in reverse order. You can also find this example function in CREATE FUNCTION (compiled SQL scalar).

CREATE FUNCTION REVERSE(INSTR VARCHAR(4000))
 RETURNS VARCHAR(4000)
 DETERMINISTIC
 NO EXTERNAL ACTION
 CONTAINS SQL
  BEGIN
   DECLARE REVSTR, RESTSTR VARCHAR(4000) DEFAULT '';
   DECLARE LEN INT;
   
   IF INSTR IS NULL THEN
   RETURN NULL;
   END IF;


   SET (RESTSTR, LEN) = (INSTR, LENGTH(INSTR));


   WHILE LEN > 0 DO
    SET (REVSTR, RESTSTR, LEN) =
                (SUBSTR(RESTSTR, 1, 1) CONCAT REVSTR,
                 SUBSTR(RESTSTR, 2, LEN - 1),
                 LEN - 1);
  END WHILE;


  RETURN REVSTR;


 END

For native SQL routines, you can use ALTER PROCEDURE or ALTER FUNCTION statement with the ADD VERSION clause to create a new version that invokes trace procedures in the routine body. When the problem occurs in the function, you can activate a version that invokes the trace procedures and execute to obtain the trace information. The ALTER PROCEDURE or ALTER FUNCTION statement must also specify the MODIFIES SQL DATA clause because trace procedures write trace information to SQL objects, namely declared global temp tables.

ALTER FUNCTION REVERSE(INSTR VARCHAR(4000))
 ADD VERSION V2 (INSTR VARCHAR(4000))
 RETURNS VARCHAR(4000)
 DETERMINISTIC
 NO EXTERNAL ACTION
 MODIFIES SQL DATA

To initiate tracing, you must invoke the DSN8.ENABLE procedure. This procedure creates the necessary objects for writing the trace data, including global temporary tables and global variables.

   CALL DSN8.ENABLE(NULL);

You can have two options for writing the trace information. You can use the DSN8.PUT procedure to the message buffer or you can use the DSN8.PUT_LINE procedure to write a complete line, including the end of line character, to the message buffer. Both procedures take one argument which is trace data to be written in the message buffer. You can invoke the DSN8.NEW_LINE procedure to add an end of line character to the message buffer.

   CALL DSN8.PUT('START OF FUNCTION');  
   CALL DSN8.NEW_LINE();  


   CALL DSN8.PUT_LINE('BEFORE LOOP STARTS - ' ||        
                                 'CONTENT OF INPUT: ' || RESTSTR);  

After the trace is written, you have two options for retrieving the data: DSN8.GET_LINE and DSN8.GET_LINES.

The DSN8.GET_LINE procedure takes two output arguments: the first output argument is the MSGBUFFER SQL variable, a VARGRAPHIC value that trace information. The second argument is an INTEGER value that indicates whether the trace is returned or not. The second argument returns 0 if trace is returned, and it returns 1 if no trace is returned.

  WHILE STATUS = 0
   DO
    CALL DSN8.GET_LINE(MSGBUFFER, STATUS);
         IF STATUS = 0 THEN
      INSERT INTO ADMF001.TRACE_TABLE VALUES (MSGBUFFER);   
         END IF;
    END WHILE;

The DSN8.GET_LINES procedure takes two arguments. The first output argument is an ARRAY data type that is defined as VARGRAPHIC data type. It contains multiple lines from the message buffer. The second argument is and input and output argument of type INTEGER. When it is used as an input, it specifies the number of lines to be retrieved and when it is used as output, it indicates the actual number of lines that were retrieved. To retrieve the entire message, you can pass the DSN8.DBMS_ROW_COUNT_E built-in global variable as a second argument.

DECLARE LINE_ARRAY DSN8.GRPHICARR;      -- Type created by DSN8.ENABLE


      CALL DSN8.GET_LINES(LINE_ARRAY, DSN8.DBMS_ROW_COUNT_E);
   SET IX = 1;                         
     WHILE IX <= DSN8.DBMS_ROW_COUNT_E DO                  
      INSERT INTO ADMF001.TRACE_TABLE VALUES (LINE_ARRAY(IX));     
      SET IX = IX + 1;                       
   END WHILE;   

At the end, you invoke the DSN8.DISABLE procedure to indicate tracing is completed and clean up the objects that were created for tracing.

   CALL DSN8.DISABLE;   

Here is the ALTER FUNCTION statement that defines a new version of the REVERSE function. The body of this version of the function invokes the trace procedures.

ALTER FUNCTION REVERSE(INSTR VARCHAR(4000))
 ADD VERSION V2 (INSTR VARCHAR(4000))
 RETURNS VARCHAR(4000)
 DETERMINISTIC
 NO EXTERNAL ACTION
 MODIFIES SQL DATA
  BEGIN
   DECLARE REVSTR, RESTSTR VARCHAR(4000) DEFAULT '';
   DECLARE LEN INT;
   DECLARE IX     INT DEFAULT 1;            
   DECLARE NUMLINES  INT DEFAULT 0;            
   DECLARE LINE_ARRAY DSN8.GRPHICARR;  
   DECLARE MSGBUFFER VARCHAR(1000);
   DECLARE STATUS   INT DEFAULT 0;


   IF INSTR IS NULL THEN
   RETURN NULL;
   END IF; 


   CALL DSN8.ENABLE(NULL);
   CALL DSN8.PUT('START OF FUNCTION');  
   CALL DSN8.NEW_LINE();  


   SET (RESTSTR, LEN) = (INSTR, LENGTH(INSTR));  


   CALL DSN8.PUT_LINE('BEFORE LOOP STARTS - ' ||        
             'CONTENT OF INPUT: ' || RESTSTR);  


   WHILE LEN > 0 DO
    SET (REVSTR, RESTSTR, LEN) =
               (SUBSTR(RESTSTR, 1, 1) CONCAT REVSTR,
                SUBSTR(RESTSTR, 2, LEN - 1),
                LEN - 1);


    CALL DSN8.PUT('LOOP #' || IX || ' - ');


    CALL DSN8.PUT_LINE('Output: ' || REVSTR ||         
              ', Next char: ' || SUBSTR(RESTSTR,1,1) ||
              ', Input: ' || RESTSTR          
             );                    
    SET IX = IX + 1;


  END WHILE;


  CALL DSN8.PUT_LINE('END OF FUNCTION');      


  WHILE STATUS = 0
  DO
    CALL DSN8.GET_LINE(MSGBUFFER, STATUS);
     IF STATUS = 0 THEN
      INSERT INTO ADMF001.TRACE_TABLE VALUES (MSGBUFFER);   
     END IF;
  END WHILE;


  CALL DSN8.DISABLE;   


  RETURN REVSTR;
 END

Invoke a REVERSE function a query.
SELECT REVERSE(C1) FROM MYTABLE;

This example stores the trace messages into a table called TRACE_TABLE. Let's assume that 'ABCD' is the value of a VARCHAR column in a row in the MYTABLE table. When the value of the column is 'ABCD' and it is passed in as an argument for the REVERSE function, the expected result is 'DCBA'. After the execution of the REVERSE function completes with the trace procedures, TRACE_TABLE contains following rows:

  +--------------------------------------------------------
1_| START OF FUNCTION                                      
2_| BEFORE LOOP STARTS - CONTENT OF INPUT: ABCD            
3_| LOOP #1 - Output: A, Next char: B, Input: BCD          
4_| LOOP #2 - Output: BA, Next char: C, Input: CD          
5_| LOOP #3 - Output: CBA, Next char: D, Input: D          
6_| LOOP #4 - Output: DCBA, Next char:  , Input:           
7_| END OF FUNCTION                                        
  +--------------------------------------------------------

Jae Lee is a Software Developer for Db2 for z/OS and Paul McWilliams is an Information Developer for Db2 for z/OS.