Calling a DB2 Stored Procedure with Error Trapping

Data provider support for row count tracking when calling DB2 stored procedures is sparse or unreliable. To overcome this, T-Loader has a special syntax for calling DB2 stored procedures. First and foremost, the Destination Command must be provided with the correct syntax, and secondly the stored procedure must have an integer output parameter with a specific name.

How to Define the DB2 Procedure Call in Step 4 of the Job Editor

As shown in this simple job, sql definition area the call for a DB2 stored procedure is not done in the expected form:

CALL PROCEDURE_NAME ( PARAM, PARAM, ETC.);

Rather, you prefix the CALL command thus:

DB2_CALL PROCEDURE_NAME ( PARAM, PARAM, ETC., ?);

When you use the DB2_CALL keyword the engine is alerted to the fact you want to override normal provider behaviour and use of an output parameter to return the AFFECTED ROW COUNT of the last operation in the procedure.

In an actual call the following must be true:

• CALL is replaced by DB2_CALL, to inform the engine to override;
• The last parameter of the called procedure must be an OUTPUT type parameter and must be called by providing a “?” (question mark) symbol.

The Required Form of the DB2 Stored Procedure

The stored procedure in the DB2 data store being manipulated must adhere to some specific guidelines. A simple procedure that does so is this:

CREATE PROCEDURE MY_PROC ( IN P_1 INT, IN P_2 VARCHAR(128), OUT AFFECTED_ROW_COUNT INT )

P1: BEGIN 

INSERT INTO MY_TABLE (MY_ID, MY_DESCRIPTION  )
                  VALUES (P_1, P_2);
            GET DIAGNOSTICS AFFECTED_ROW_COUNT = ROW_COUNT;
         END P1

The specific requirements are that:

• the last parameter of the procedure be an output only parameter of integer type named “AFFECTED_ROW_COUNT” (OUT AFFECTED_ROW_COUNT INT); and
• the last line immediately above the closing block indicator of the procedure use the diagnostics interface to return the row count (GETDIAGNOSTICS AFFECTED_ROW_COUNT = ROW_COUNT;).

Having said that, strictly speaking, the assignment to the AFFECTED_ROW_COUNT parameter inside the procedure can be any valid assignment at all, such as a SET statement, or otherwise. The only critical aspect is the parameter name be an integer, the assignment be an integer, and the name of the parameter is explicitly AFFECTED_ROW_COUNT.