GET DIAGNOSTICS

From SQL PL Guide for DB2

The GET DIAGNOSTICS statement can be used to get information about the previously executed SQL statement. There are three different options available:

• DB2_RETURN_STATUS
• ROW_COUNT
• EXCEPTION 1

DB2_RETURN_STATUS

The option DB2_RETURN_STATUS can be used to transfer the return status of a called stored procedure to the calling stored procedure.

Example:

 create procedure myproc
 begin
   declare VAR_RETURN int default 0;

   call myproc_call;
   get diagnostics VAR_RETURN = db2_return_status;
 end!

ROW_COUNT

With the option ROW_COUNT it's possible to find out the number of rows which were affected by a previous INSERT, UPDATE, or DELETE statement (it will NOT work with a SELECT statement). When it will used after a PREPARE statment you will just get an estimation of the number of rows which will affected.

Example:

 create procedure upd_employee_sal (in old_sal integer, in new_sal integer, out no_of_rows integer)
 language sql
 begin

   update employee set salary = new_sal where salary = old_sal;
   get diagnostics no_of_rows = ROW_COUNT;

 end!

EXCEPTION 1

With this option you can retrieve information to the error or warning of the SQL statement which was just executed.

Example:

 create procedure exec_cmd (in v_stmt varchar(4096), out v_rc smallint, out v_msg varchar(4096))
 language sql
 begin
   declare SQLCODE integer;
   declare exit handler for SQLEXCEPTION
   begin
     get diagnostics exception 1 v_msg = MESSAGE_TEXT;
     set v_rc = SQLCODE;
   end;

   set v_rc=0;
   set v_msg=;

   execute immediate v_stmt;

 end!

---

Link to the DB2 Information Center: DB2 9 DB2 9.5