GET DIAGNOSTICS statement
The GET DIAGNOSTICS statement can be used to get information about the previously executed SQL statement. There are three different options available:
- EXCEPTION 1
The option DB2_RETURN_STATUS can be used to transfer the return status of a called stored procedure to the calling stored procedure.
create procedure myproc begin declare VAR_RETURN int default 0; call myproc_call; get diagnostics VAR_RETURN = db2_return_status; end!
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.
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!
With this option you can retrieve information to the error or warning of the SQL statement which was just executed.
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!