After you successfully create and run an SPL routine,
you can encounter logic errors. If the routine has logic errors, use
the TRACE statement to help find them. You can trace the values of
the following items:
Variables
Arguments
Return values
SQL error codes
ISAM error
codes
To generate a listing of traced values, first use the
SQL statement SET DEBUG FILE to name the file that is to contain the
traced output. When you create the SPL routine, include a TRACE statement.
The
following methods specify the form of TRACE output.
Statement
Action
TRACE ON
Traces all statements except SQL statements. Prints the contents
of variables before they are used. Traces routine calls and returned
values.
TRACE PROCEDURE
Traces only the routine calls and returned values.
TRACE expression
Prints a literal or an expression. If necessary, the value of
the expression is calculated before it is sent to the file.
The following figure demonstrates how you can
use the TRACE statement to monitor how an SPL function executes. Figure 1: The TRACE
statement.
CREATE FUNCTION read_many (lastname CHAR(15))
RETURNING CHAR(15), CHAR(15), CHAR(20), CHAR(15),
CHAR(2), CHAR(5);
DEFINE p_lname,p_fname, p_city CHAR(15);
DEFINE p_add CHAR(20);
DEFINE p_state CHAR(2);
DEFINE p_zip CHAR(5);
DEFINE lcount, i INT;
LET lcount = 1;
TRACE ON; -- Trace every expression from here on
TRACE 'Foreach starts'; -- Trace statement with a literal
FOREACH
SELECT fname, lname, address1, city, state, zipcode
INTO p_fname, p_lname, p_add, p_city, p_state, p_zip
FROM customer
WHERE lname = lastname
RETURN p_fname, p_lname, p_add, p_city, p_state, p_zip
WITH RESUME;
LET lcount = lcount + 1; -- count of returned addresses
END FOREACH
TRACE 'Loop starts'; -- Another literal
FOR i IN (1 TO 5)
BEGIN
RETURN i , i+1, i*i, i/i, i-1,i WITH RESUME;
END
END FOR;
END FUNCTION;
With the TRACE ON statement, each time you execute the
traced routine, entries are added to the file you specified in the
SET DEBUG FILE statement. To see the debug entries, view the output
file with any text editor.
The following list contains some
of the output that the function in previous example generates. Next
to each traced statement is an explanation of its contents.
Statement
Action
TRACE ON
Echoes TRACE ON statement.
TRACE Foreach starts
Traces expression, in this case, the literal string Foreach starts.
start select cursor
Provides notification that a cursor is opened to handle a FOREACH
loop.
select cursor iteration
Provides notification of the start of each iteration of the select
cursor.
expression: (+lcount, 1)
Evaluates the encountered expression, (lcount+1), to 2.