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:
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.
Figure 466 demonstrates how you can use the TRACE statement to monitor how an SPL function executes.
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 Figure 466 generates. Next to each traced statement is an explanation of its contents.