Db2 Stored Procedure examples: How to Create DB2 Stored Procedures with Examples

Db2 Stored Procedure examples: How to Create DB2 Stored Procedures with Examples: I have been working on Oracle/Plsql, Sql Server for over a long period and got an opportunity to work on Db2. You get to see as many links a...

How to Create DB2 Stored Procedures/Triggers with Examples

I have been working on Oracle/Plsql, Sql Server for over a long period and got an opportunity to work on Db2. You get to see as many links and Pdf's online,but most of them do not cover procedures in DB2. I take this opportunity to share some of the simplest ones,that helps a fresher to understand how to create/execute procedures/functions.
Lets start with a simple stored procedure and understand how to create one,execute one.

I have created a sample table by name emp_tab to illustrate the examples.


Column                         Type      Type
name                           schema    name               Length   Scale Nulls
------------------------------ --------- ------------------ -------- ----- ------
EMPID                          SYSIBM    INTEGER                   4     0 No  
EMPNAME                        SYSIBM    VARCHAR                  30     0 Yes

Example 1 : Sample Stored Procedure with out parameters in it

CREATE OR REPLACE PROCEDURE PROC_SAMPLE1()
BEGIN
INSERT INTO EMP_TAB VALUES(121,'SCOTT');
END


NOTE:  Before you even try to compile the stored procedure, Please do make sure you change the default delimiter to any special character other than  semicolon ';'


Once compiled, You can go ahead run the procedure PROC_SAMPLE1() using the below command.


CALL PROC_SAMPLE1()



------------------------------------------------------------------------------
CALL PROC_SAMPLE1()

output as follows:

  Return Status = 0

------------------------------ Commands Entered --------------------------

SELECT * FROM EMP_TAB

------------------------------------------------------------------------------

output as follows:

EMPID       EMPNAME                       

----------- ------------------------------

        121 SCOTT                         

  1 record(s) selected.

SELECT * FROM EMP_TAB 

------------------------------------------------------------------------------------------------------------

Example 2: Sample Stored Procedure with input parameters in it

Now lets create a stored procedure that has parameters in it.Example 2 illustrates a stored procedure having an input parameter.

CREATE OR REPLACE PROCEDURE PRC_SAMPLE2(IN PARAMETER1 VARCHAR(30))

BEGIN

DECLARE v_PARAM VARCHAR(30);

DECLARE v_INT INTEGER;

SET v_PARAM=PARAMETER1;

SELECT MAX(EMPID) INTO v_INT FROM EMP_TAB;

INSERT INTO EMP_TAB SELECT v_INT+1,v_PARAM FROM SYSIBM.SYSDUMMY1;

END

Execute the stored proc using the below command.

CALL PRC_SAMPLE2('JAMES')

output as follows:

------------------------------------------------------------------------------

CALL PRC_SAMPLE2('JAMES')

  Return Status = 0

------------------------------ Commands Entered --------------------------

SELECT * FROM EMP_TAB/

------------------------------------------------------------------------------

SELECT * FROM EMP_TAB

EMPID       EMPNAME                       

----------- ------------------------------

        122 JAMES                         

        121 SCOTT                         

  2 record(s) selected.

------------------------------------------------------------------------------------------------------------

Example 3: Sample Stored Procedure with input and output parameters in it

lets create a stored procedure that has input as well as output parameters in it.

CREATE OR REPLACE PROCEDURE PRC_SAMPLE3(IN PARM VARCHAR(30),OUT o_PARAM VARCHAR(200))

BEGIN

DECLARE v_PARAM VARCHAR(30);

DECLARE v_INT INTEGER;

DECLARE v_check INT;

SET v_PARAM=PARM;

SELECT MAX(EMPID) INTO v_INT FROM EMP_TAB;

INSERT INTO EMP_TAB SELECT v_INT+1,v_PARAM FROM SYSIBM.SYSDUMMY1;

SELECT 1 into v_check from EMP_TAB WHERE EMPNAME=v_PARAM;

IF v_check=1 then

set o_PARAM=v_PARAM || ' RECORD LOADED INTO EMP_TAB TABLE' ;

END IF;

END

CALL PRC_SAMPLE3('BOND',?)

  Value of output parameters

  --------------------------

  Parameter Name  : O_PARAM

  Parameter Value : BOND RECORD LOADED INTO EMP_TAB TABLE


------------------------------------------------------------------------------------------------------------

Example 4: Sample Stored Procedure with Cursors

Alright.. Let's move on and create a cursor and see how it works in DB2.

Lets create another table Emp_tab2 having similar structure to Emp_tab

Column                         Type      Type

name                           schema    name               Length   Scale Nulls

------------------------------ --------- ------------------ -------- ----- ------

EMPID                          SYSIBM    INTEGER                   4     0 No    

EMPNAME                        SYSIBM    VARCHAR                  30     0 Yes   

CREATE OR REPLACE PROCEDURE PRC_SAMPLE4()

BEGIN

DECLARE v_NAME VARCHAR(30);

DECLARE v_id INTEGER;

FOR V1 AS

CSR1 CURSOR FOR SELECT EMPID,EMPNAME FROM EMP_TAB

DO

SET V_ID=EMPID;

SET V_NAME=EMPNAME;

INSERT INTO EMP_TAB2 VALUES(V_ID,V_NAME);

END FOR;

END

Output as follows: 

CALL PRC_SAMPLE4()

  Return Status = 0

------------------------------ Commands Entered --------------------------

select * from emp_Tab2/

-----------------------------------------------------------------------------

select * from emp_Tab2

EMPID       EMPNAME                       

----------- ------------------------------

        121 SCOTT                         

        123 BOND                          

        122 JAMES                         

  3 record(s) selected.

------------------------------------------------------------------------------------------------------------

Example 5: Exception Handling example

Now lets do something more interesting and handle exceptions in our procedures.What's exception? Well, there are lots of links that provides you an answer to that question . It holds the same in any programming language or database.

Lets try to create a procedure that handles exception while performing any DML statement.

CREATE OR REPLACE PROCEDURE EXCEPTION_HANDLING(V_EMP_ID   INTEGER,V_EMP_NAME VARCHAR(30),OUT O_V_OUT   VARCHAR(50))
BEGIN
   DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
   DECLARE EXIT HANDLER FOR SQLEXCEPTION
      SET O_V_OUT = SQLSTATE;
   IF V_EMP_ID = '0' THEN
      SIGNAL SQLSTATE '88888'
    SET MESSAGE_TEXT ='INCORRECT EMPLOYEE_ID';
   END IF;
   INSERT INTO EMP_TAB VALUES (V_EMP_ID, V_EMP_NAME);
END

The above simple procedure allows you to enter any number as employee id except 0. when entered,  it would pass a user defined  error code(in this example '88888') you defined to an output variable that can be used in the called envirnoment for validations.

lets assume the table has no records. 

INSERT INTO EMP_TAB
WITH CTE_DB2(PRM) AS
( VALUES
        '123 BOND'
        ,'122 JAMES'
)SELECT SUBSTR(PRM,1,3),SUBSTR(PRM,5,5) FROM CTE_DB2

Now lets execute the procedure to see how it works.

CALL  EXCEPTION_HANDLING(1,'ROCKY',?)

  Value of output parameters
  --------------------------
  Parameter Name  : O_V_OUT
  Parameter Value : -

  Return Status = 0

CALL  EXCEPTION_HANDLING(0,'ZERO',?)

Value of output parameters

  --------------------------

  Parameter Name  : O_V_OUT

  Parameter Value : 88888

  Return Status = 0

SELECT * FROM EMP_TAB

EMPID       EMPNAME                       

----------- ------------------------------

        122 JAMES                         

        123 BOND                          

          1 ROCKY                         

                     

  3 record(s) selected.
----------------------------

If you notice, the record with an employee_id '0' didnt get inserted into the table EMP_TAB.

hope this helps to have an understanding on how to create and run procedures in DB2.

------------------------------------------------------------------------------------------------------------

Example 6: Trigger Handling example

CREATE OR REPLACE TRIGGER TRIGGER_EXAMPLE
AFTER INSERT ON EMPLOYEE
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
DECLARE V SMALLINT DEFAULT 0;
DECLARE VV SMALLINT DEFAULT 0;
FOR INC AS SELECT COUNT(*) AS COUNT_TEST FROM ORG_EMP_SIZE
DO
SET V= COUNT_TEST;
END FOR;
FOR INC AS SELECT COUNT(*) AS EMP_COUNT FROM EMPLOYEE
DO
SET VV= EMP_COUNT;
END FOR;
IF V=0
THEN
INSERT INTO ORG_EMP_SIZE SELECT COUNT(*) FROM EMPLOYEE;
ELSE
UPDATE ORG_EMP_SIZE SET TOTCOUNT=VV;
END IF;
END

The above trigger is such a simple trigger used to keep track of the number of employees in an organistaion. Ofcourse it's a Trigger that gets enabled after insertion of a record.


---------------------------------------------------------------------------------------------------------

         Example 7: Create Temp Tables

We all know that creating temp tables in sql server no matter whether a global/local is so easy. Lets see how the same can be aquired in DB2.


-> DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP_TABLE (DEPTID INT)
    ON COMMIT PRESERVE ROWS NOT LOGGED


 -> INSERT INTO SESSION.TEMP_TABLE VALUES(10),(20)


-> SELECT * FROM SESSION.TEMP_TABLE


DEPTID  
-----------
         10
         20

  2 record(s) selected.