13.1 Sample Stored Procedure

Creating and Executing a Sample Stored Procedure in Aqua Data Studio 7.0 (DB2 UDB)

Create Table	Insert Values	Create Stored Procedure	Execute

Execute Bind	Call SP - Input Parameter	Execute for Results

Create table DDL

CREATE TABLE "DB2ADMIN"."AQUA_CUSTOMER" (
    "F_NME"     VARCHAR(25),
    "L_NME"     VARCHAR(25),
    "TITLE"     VARCHAR(10),
    "ADDR_1"    VARCHAR(25),
    "ADDR_2"    VARCHAR(25),
    "CITY"      VARCHAR(25),
    "ZIP"       VARCHAR(25)
    )

Insert Statements

INSERT INTO DB2ADMIN.AQUA_CUSTOMER(F_NME, L_NME, TITLE, ADDR_1, ADDR_2, CITY, ZIP)
    VALUES('James', 'Smith', 'Manager', '1970 Napa Ct.', '', 'Bothell', '98011')
GO
INSERT INTO DB2ADMIN.AQUA_CUSTOMER(F_NME, L_NME, TITLE, ADDR_1, ADDR_2, CITY, ZIP)
    VALUES('Andrew', 'Smith', 'CTO', '1970 Napa Ct.', '', 'Bothell', '98011')
GO
INSERT INTO DB2ADMIN.AQUA_CUSTOMER(F_NME, L_NME, TITLE, ADDR_1, ADDR_2, CITY, ZIP)
    VALUES('Drake', 'Smith', 'CTO', '1970 Napa Ct.', '', 'Bothell', '98011')
GO
INSERT INTO DB2ADMIN.AQUA_CUSTOMER(F_NME, L_NME, TITLE, ADDR_1, ADDR_2, CITY, ZIP)
    VALUES('Micheal', 'Smith', 'CTO', '1970 Napa Ct.', '', 'Bothell', '98011')
GO
INSERT INTO DB2ADMIN.AQUA_CUSTOMER(F_NME, L_NME, TITLE, ADDR_1, ADDR_2, CITY, ZIP)
    VALUES('Drew', 'Smith', 'CTO', '1970 Napa Ct.', '', 'Bothell', '98011')
GO
INSERT INTO DB2ADMIN.AQUA_CUSTOMER(F_NME, L_NME, TITLE, ADDR_1, ADDR_2, CITY, ZIP)
    VALUES('Barry', 'Hike', 'CEO', '1970 Napa Ct.', '', 'Bothell', '98011')
GO

SAMPLE DB2 UDB STORED PROCEDURE

This stored procedure when executed will return all rows from the table DB2ADMIN.AQUA_CUSTOMER based on the condition that Aqua_Code parameter is equal to the specified TITLE column in the table. For example, if I want the stored procedure to return all rows from the table with TITLE=CTO, I specify the input parameter AQUA_CODE as 'CTO'. If its all the Managers I want, just need to specify input parameter AQUA_CODE as 'MANAGER'.

A cursor is declared which loops through each row in the Table.

CREATE PROCEDURE DB2ADMIN.SAMPLE_SP_1 ( IN Aqua_Code CHARACTER(10) )
    DYNAMIC RESULT SETS 1
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN
    -- Declare cursor
    DECLARE cursor1 CURSOR WITH RETURN FOR
        SELECT F_NME,L_NME,TITLE,CITY,ZIP FROM DB2ADMIN.AQUA_CUSTOMER
        WHERE TITLE = Aqua_Code;
    -- Cursor left open for client application
    OPEN cursor1;
END P1
GO

Executing DB2 UDB STORED PROCEDURE

Connect to  the Database Server and Open a Query Analyzer. Type the following and Execute to see the UDF result

CALL DB2ADMIN.SAMPLE_SP_1 ('Manager')
GO