Example DB2 Stored Procedures for DbExecute step in Work flow

//Example DB2 Stored Procedures for DbExecute step in Work flow

DbExecute system function: Use the DbExecute system function to run a stored procedure in a specified database.

The datatypes must match the types specified in the stored procedure and can be of the following types:

Designer Param Type	Oracle Param Type	SQL Server Param Type	DB2 Param Type
String	varchar	varchar	varchar
Integer	number	int	int
Boolean	number	bit	number
Float	number	float	float
Time	date	datetime	timestamp

DB2 Stored Procedure:

A stored procedure is unusable until it is defined. Use the CREATE PROCEDURE command to do this. You can either use the DB2 command prompt or place the command in a program and compile and run it. If you use the DB2 command prompt, you first connect to the DB2 LUW Server where the stored procedure will be executed. 

Syntax:

CREATE [OR REPLACE] PROCEDURE [qualifier.]<procedure_name>
([<argument_name> IN | OUT | IN OUT <datatype>
                                    [DEFAULT <default>],...])
{IS | AS}
  [<variable_name <datatype> [DEFAULT <default>];],...
BEGIN
  <procedure_body>
[EXCEPTION
  <exception_statements>]
END;

Example1: Simple data Insertion into DOC_SCHEEMA.USER_DETAILS table

Note: The order of Parameters and  arguments in the db2 stored procedure must be same.

CREATE PROCEDURE DOC_SCHEEMA.USER_DATA
(
INOUT P_NAME VARCHAR(64),
INOUT P_FIRST_NAME VARCHAR(64),
INOUT P_NUMBER INTEGER,
INOUT P_DATE_OF_JOINING TIMESTAMP,
INOUT P_CASEID INTEGER
)
    LANGUAGE SQL
BEGIN
 INSERT INTO DOC_SCHEEMA.USER_DETAILS(NAME, FIRST_NAME, NUMBER1, DATE_OF_JOINING, CASEID)  VALUES(P_NAME, P_FIRST_NAME, P_NUMBER, P_DATE_OF_JOINING, P_CASEID);
END
  

Example 2: updating status and number1  into DOC_SCHEEMA.CASE_DETAILS table

CREATE PROCEDURE DOC_SCHEEMA.STATUS_UPDATE
(
INOUT P_CASEID INTEGER,
INOUT P_STATUS VARCHAR(64),
INOUT P_NUMBER INTEGER
)
    LANGUAGE SQL
BEGIN
    UPDATE DOC_SCHEEMA.CASE_DETAILS set STATUS = P_STATUS, NUMBER1 = P_NUMBER WHERE CASEID=P_CASEID;
END


Example 3: Update Workflow properties from different db2 tables.

updating Workflow properties name,email_ID, location and phone number based on region and location from different db2 database tables.

CREATE PROCEDURE DOC_SCHEEMA.UPDATE_WORKFLOW_PROPERTIES
(
INOUT P_COUNTRY VARCHAR(64),
INOUT P_REGION VARCHAR(64),
OUT P_NAME VARCHAR(64),
OUT P_EMAIL_ID VARCHAR(64),
OUT P_LOCATION VARCHAR(64),
OUT P_PHONE_NO VARCHAR(64)
)

LANGUAGE SQL
BEGIN

FOR myCursor AS SELECT NAME,EMAIL_ID FROM DOC_SCHEEMA.USER_DETAILS WHERE COUNTRY = P_COUNTRY AND REGION = P_REGION

DO
--updating work flow properties: name and email_id into
SET P_NAME = myCursor.NAME;
SET P_EMAIL_ID = myCursor.EMAIL_ID;

END FOR;
--updating work flow properties: location and phone number

SET P_LOCATION =(SELECT LOCATION FROM DOC_SCHEEMA.LOCATION_DETAILS WHERE COUNTRY = P_COUNTRY AND REGION = P_REGION);
SET P_PHONE_NO =(SELECT PHONE_NO FROM DOC_SCHEEMA.USER_DETAILS WHERE COUNTRY = P_COUNTRY AND REGION = P_REGION);
END