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.