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.
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 4 of the CREATE PROCEDURE statement.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.
create table table1 (col1 integer) @
create procedure appdev.proc2(in parm1 integer, in parm2 integer)
specific appdev_proc2
begin
insert into table1 values (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.