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.
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) 5
(4) 0 (5) 0 (6) 0
...
sqlstate: 42821
The value of 5 in the sqlerrd(3) field
indicates that the error was in line 5 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=5. SQLSTATE=42821
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 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.