DB2 UDB Stored Procedure Example
The following example shows how to create, register and invoke a database procedure using the DB2 UDB gateway.
Note: The user creating the DB2 UDB stored procedure must have create procedure permissions. The register procedure requires insert permissions on the gateway procedure catalogs. The execute procedure statement requires execute permissions for the DB2 UDB stored procedure and select permissions on the gateway procedure catalogs.
To create, register and invoke a database procedure
1. Create the DB2 UDB stored procedure from the terminal monitor. The trailing spaces after each line are required to successfully create the procedure:
DIRECT EXECUTE IMMEDIATE 'drop procedure db2udb_credit_update;';
DIRECT EXECUTE IMMEDIATE
'CREATE PROCEDURE db2udb_credit_update
(IN acc_no integer,
IN amount float )
language sql
BEGIN
UPDATE accounts
SET balance = balance + amount
WHERE account_id = acc_no ;
END';
2. Register the stored procedure as a gateway database procedure:
REMOVE PROCEDURE gwcredit_update;
REGISTER PROCEDURE gwcredit_update
(acc_no INTEGER4,
amount FLOAT8)
AS IMPORT
FROM 'db2udb_credit_update';
3. Invoke the gateway database procedure:
EXECUTE PROCEDURE gwcredit_update
(acc_no = 14567,
amount = 1678.93);