Error location in the SQLCA structure
When an error occurs during the compilation or execution of an SQL PL object, the SQLCA structure that is returned by the Db2® database manager contains information that helps in identifying the origin of the error.
Compile-time errors
When an error is raised during the compilation of an SQL PL object (either inlined or compiled), the sqlerrd(3) field in the returned SQLCA structure contains the line number of the error as reported by the Db2 SQL compiler.In
the following example, the procedure myproc contains an invalid statement in line
4. The statement is invalid because the data types of the source and target portions of the SET
statement do not
match.
create procedure myproc (parm1 integer)
begin
declare var1 date;
set var1 = parm1;
end @
Assuming
that this procedure is stored in file script1.db2, an attempt is made to
process this file using the command line processor (CLP) with the following
command:db2 -td@ -a -f script1.db2
Where using the -a
option causes the CLP to display the SQLCA. The result would be similar to the following
information:SQLCA Information
sqlcaid : SQLCA sqlcabc: 136 sqlcode: -408
sqlerrml: 4
sqlerrmc: VAR1
sqlerrd : (1) 0 (2) 0 (3) 4
(4) 0 (5) 0 (6) 0
...
sqlstate: 42821
The
value of 4 in the sqlerrd(3) field indicates that the error was in line 5 of the CREATE PROCEDURE
statement.The CLP was used as the client interface for this example because it provides a
simple way of showing the SQLCA structure that results from the execution of a statement. However,
when compiling SQL PL statements using the CLP, it is not necessary to display the SQLCA structure
to identify the line number of an error. The CLP automatically displays the line number when it
formats the error message. For the created procedure (myproc), an invocation of
the CLP that did not include the -a option would return the following message,
which shows that the error was in line
4:
SQL0408N A value is not compatible with the data type of its assignment
target. Target name is "VAR1". LINE NUMBER=4. SQLSTATE=42821
Runtime errors
When an error is raised during the execution of a compiled SQL PL routine or trigger, the SQLCA structure that is returned by the Db2 database manager contains both the line number of the statement that caused the error and a numeric value that uniquely identifies the SQL PL routine or trigger that contains that statement.In the following
example, a statement creates the table table1 with one integer column. A second
statement creates the procedure appdev.proc2 with two integer parameters,
parm1 and parm2, that inserts into the table the value that
results from dividing parm1 by parm2. The CALL statement with
arguments 1 and 0 results in a division-by-zero runtime
error.
create table table1 (col1 integer) @
create procedure appdev.proc2(in parm1 integer, parm2 integer)
specific appdev_proc2
begin
insert into table1 (parm1 / parm2);
end @
call appdev.proc2(1, 0) @
Assuming
that these statements are stored in file script2.db2, an attempt is made to
process this file by using the CLP with the following
command:db2 -td@ -a -f script2.db2
After executing the call, the result from
CLP would be similar to the following
output:SQLCA Information
sqlcaid : SQLCAM sqlcabc: 136 sqlcode: -801
...
sqlerrd : (1) 0 (2) 0 (3) 4
(4) 13152254 (5) 0 (6) 0
...
sqlstate: 22012
The
value of sqlcode -801 in the result corresponds to division by zero (SQLSTATE 22012). Similar to
compile-time errors, the value in the sqlerrd(3) field indicates the line number within the SQL PL
object where the error originated (line number 4 in this case). Additionally, the value in the
sqlerrd(4) field contains an integer value that uniquely identifies that SQL PL object. The
SYSPROC.GET_ROUTINE_NAME procedure can be used to map the unique identifier to the name of the
object that raised the error. This procedure takes as an input parameter the value in the sqlerrd(4)
field and returns information in five output parameters, as shown in the following
example:db2 CALL SYSPROC.GET_ROUTINE_NAME(13152254, ?, ?, ?, ?, ?)
Value of output parameters
--------------------------
Parameter Name : TYPE
Parameter Value : P
Parameter Name : SCHEMA
Parameter Value : APPDEV
Parameter Name : MODULE
Parameter Value : -
Parameter Name : NAME
Parameter Value : PROC2
Parameter Name : SPECIFIC_NAME
Parameter Value : APPDEV_PROC2
Return Status = 0
The
value 'P' for the TYPE parameter indicates that the object is a procedure. Because the object does
not belong to a module, the MODULE parameter is NULL.Note: The SQLCA structure does not contain line
number information for errors that are raised during the execution of inline SQL PL
objects.