Debugging an SQL routine

By specifying SET OPTION DBGVIEW = *SOURCE in the CREATE PROCEDURE, CREATE FUNCTION, or CREATE TRIGGER statement, you can debug the generated program or service program at the SQL statement level.

You can also specify DBGVIEW(*SOURCE) as a parameter on a RUNSQLSTM command and it will apply to all routines within the RUNSQLSTM.

The source view will be created by the system from your original routine body into source file QSQDSRC in the routine library. If the library cannot be determined, QSQDSRC is created in QTEMP. The source view is not saved with the program or service program. It will be broken into lines that correspond to places you can stop in debug. The text, including parameter and variable names, will be folded to uppercase. Delimited names and character literals are not folded to uppercase.

SQL routines can be debugged using either the SQL Object Processor Root view or the ILE C view. The debugger can be used to evaluate both SQL variables and C variables in the generated program or service program. The preferred approach is to debug using the SQL Object Processor Root view, but the ILE C view can be used as necessary to obtain additional execution information.

SQL Object Processor Root View

This view uses the routine source in QSQDSRC. To evaluate an SQL variable, add the prefix '%%' to the variable name to indicate the name represents an SQL variable. Any SQL variable must be prefixed with '%%' when it is referenced in a debugger command. SQL variable names are not case sensitive. Both undelimited and delimited identifiers can be referenced. When an SQL variable name is specified, the debugger will determine which C variable represents the SQL variable and then display the name and contents of the corresponding C variable. When an SQL parameter or variable which has an indicator is referenced, the debugger will return 'NULL' if the indicator is set to indicate the null value.

There is no need to qualify a variable name unless there is ambiguity. In that case, the variable should be qualified by the label of its containing compound SQL block to avoid the ambiguity. C variables can be referenced while debugging in the SQL Object Processor view. A variable name without the prefix '%%' is considered to be a C variable.

ILE C listing view

This view uses the generated C source for the SQL routine.

All variables and parameters are generated as part of a structure. The structure name must be used when evaluating a variable in debug. Variables are qualified by the current label name. Parameters are qualified by the procedure or function name. Transition variables in a trigger are qualified by the appropriate correlation name. It is highly recommended that you specify a label name for each compound statement or FOR statement. If you don't specify one, the system will generate one for you. This will make it nearly impossible to evaluate variables. Remember that all variables and parameters must be evaluated as an uppercase name. You can also evaluate the name of the structure. This will show you all the variables within the structure. If a variable or parameter is nullable, the indicator for that variable or parameter immediately follows it in the structure.

Because SQL routines are generated in C, there are some rules for C that affect SQL source debug. Delimited names that are specified in the SQL routine body cannot be specified in C. Names are generated for these names, which again makes it difficult to debug or evaluate them. In order to evaluate the contents of any character variable, specify an * prior to the name of the variable.

Since the system generates indicators for most variable and parameter names, there is no way to check directly to see if a variable has the SQL null value. Evaluating a variable will always show a value, even if the indicator is set to indicate the null value.

SQL variables cannot be directly referenced (using the '%%' prefix) while debugging in the ILE C view.

In order to determine if a handler is getting called, set a breakpoint on the first statement within the handler. Variables that are declared in a compound statement or FOR statement within the handler can be evaluated.

SQL array debug

The SQL array data type, which is only supported in SQL procedures, has special debugging rules since the array variables do not have permanent storage in the generated C program. Unlike C arrays, SQL arrays are 1 based. To display the fifth element of the array, specify array index 5 on the EVAL command (EVAL TSTARR.ABC[5]). Specifying an array variable name without specifying an array index is not supported; this will result in a variable not found error. To see a range of values in the array, an EVAL command with array index ranges can be used. For example, to see the first 25 elements in an array, EVAL TSTARR.ABC[1..25]. The EVAL command will indicate when an array variable has the NULL value. An expression containing a reference to an array variable that is NULL evaluates to NULL. The numeric array types can be used in expressions; the other types cannot. The non-prefixed string types will be displayed as a string, not a pointer.

Not all debug commands support the SQL array type. They cannot be used on the left side of an assignment (EVAL TSTARR.ABC[5] = 3). They cannot be used as the target of the '&' address operator (EVAL &TSTARR.ABC[3]). Arrays cannot be used with the WATCH debug command, either. Array variables will not be displayed by the EVAL %LOCALVARS command because they are not in the debug symbol table.