error handling in sql storedProcedures for db2 OS390 v7

Michael Shields

error handling in sql storedProcedures for db2 OS390 v7
I have a stored procedure which is called by a trigger.
The stored procedure is responsible for inserting some data into a table.
If that insert is gets a duplicate key on insert I want to return successfully to the trigger (without throwing the -813 back up to the trigger which goes back to my java application).

I read a redbook that came out in 2001 for iseries that talks of error handling which sounded perfect, but of course that didn't work for me.
I need to replace the "declare continue handler" with a solution for OS390.
Here's what I had if it helps to understand my issue:

CREATE PROCEDURE schema.spname (IN ORD_NUM CHAR(17))
LANGUAGE SQL
EXTERNAL NAME spname
MODIFIES SQL DATA
COLLID CDR_DNS
WLM ENVIRONMENT DB39WL01
STAY RESIDENT NO
COMMIT ON RETURN NO

BEGIN ATOMIC
DECLARE DUPLICATE_RECORD CONDITION FOR SQLSTATE '23505';
DECLARE AT_END INT DEFAULT 0;

INSERT INTO schema.table (col1, col2, col3)
VALUES ('CONSTANT','CONSTANT2',ORD_NUM);

DECLARE CONTINUE HANDLER FOR DUPLICATE_RECORD
SET AT_END = 1;

END


What are my options?

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Suresh Sane

Re: error handling in sql storedProcedures for db2 OS390 v7
(in response to Michael Shields)
Michael,

A new redbook is on its way that will have 2 chapters dealing with these
types of issues - trigger/sp & error handling. We can discuss the specifics
offline, if you like but here are points of general interest:

1. You don't have to abend the SP. You can write the -803 (you mean -803
right, not -813?) to some common area (dataset, db2 table etc). Passing it
back to trigger is possible but the trigger cannot take any action on it -
not with SPs.
2. Handlers you mention are in V8 (using them now as I develop the
samples), they should help.
3. Today's technology - I ssugest you replace the SP with a UDF. Very
similar logic but the return code can be acted on by a trigger. Invoke it
with a VALUES, instead of CALL.

Hope this helps, and holler if we need to discuss offline.

Thanks,
Suresh


>From: Shields Michael <[login to unmask email]>
>Reply-To: DB2 Database Discussion list at IDUG <[login to unmask email]>
>To: [login to unmask email]
>Subject: error handling in sql storedProcedures for db2 OS390 v7
>Date: Tue, 2 Dec 2003 16:37:47 -0600
>
>I have a stored procedure which is called by a trigger.
>The stored procedure is responsible for inserting some data into a table.
>If that insert is gets a duplicate key on insert I want to return
>successfully to the trigger (without throwing the -813 back up to the
>trigger which goes back to my java application).
>
>I read a redbook that came out in 2001 for iseries that talks of error
>handling which sounded perfect, but of course that didn't work for me.
>I need to replace the "declare continue handler" with a solution for OS390.
>Here's what I had if it helps to understand my issue:
>
>CREATE PROCEDURE schema.spname (IN ORD_NUM CHAR(17))
>LANGUAGE SQL
>EXTERNAL NAME spname
>MODIFIES SQL DATA
>COLLID CDR_DNS
>WLM ENVIRONMENT DB39WL01
>STAY RESIDENT NO
>COMMIT ON RETURN NO
>
>BEGIN ATOMIC
>DECLARE DUPLICATE_RECORD CONDITION FOR SQLSTATE '23505';
>DECLARE AT_END INT DEFAULT 0;
>
>INSERT INTO schema.table (col1, col2, col3)
>VALUES ('CONSTANT','CONSTANT2',ORD_NUM);
>
>DECLARE CONTINUE HANDLER FOR DUPLICATE_RECORD
>SET AT_END = 1;
>
>END
>
>
>What are my options?
>
>---------------------------------------------------------------------------------
>Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
>page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
>"Join or Leave the list". If you will be out of the office, send the SET
>DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins
>can be reached at [login to unmask email] Find out the latest on
>IDUG conferences at http://conferences.idug.org/index.cfm

_________________________________________________________________
Tired of slow downloads and busy signals? Get a high-speed Internet
connection! Comparison-shop your local high-speed providers here.
https://broadband.msn.com

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Aurora Dellanno

Re: error handling in sql storedProcedures for db2 OS390 v7
(in response to Suresh Sane)
Hi Michael,

I saw a presentation yesterday at the UK GSE DB2 WG Meeting Kenneth
Mackinnon, Scottish Widows, regarding their handling of a similar situation
- it was a user's presentation and you might find it quite interesting once
it's published on http://www.gseukdb2.org.uk/minutes/0312ag.htm (if it's not
there, you'll find it under the "Meetings" header).

HTH.

ciao!

Aurora Emanuela Dell'Anno
Database Analyst
Data Services Group - Bank of America
tel. 66192
ext. 0208 760 6192
[login to unmask email]

* std. disclaimer * MY OPINIONS ARE MY OWN AND NOT THOSE OF MY EMPLOYER

no trees were killed in sending this message. However, a large number of
electrons were seriously inconvenienced :-)

-----Original Message-----
From: Suresh Sane [mailto:[login to unmask email]
Sent: 03 December 2003 18:29
To: [login to unmask email]
Subject: Re: error handling in sql storedProcedures for db2 OS390 v7


Michael,

A new redbook is on its way that will have 2 chapters dealing with these
types of issues - trigger/sp & error handling. We can discuss the specifics
offline, if you like but here are points of general interest:

1. You don't have to abend the SP. You can write the -803 (you mean -803
right, not -813?) to some common area (dataset, db2 table etc). Passing it
back to trigger is possible but the trigger cannot take any action on it -
not with SPs.
2. Handlers you mention are in V8 (using them now as I develop the
samples), they should help.
3. Today's technology - I ssugest you replace the SP with a UDF. Very
similar logic but the return code can be acted on by a trigger. Invoke it
with a VALUES, instead of CALL.

Hope this helps, and holler if we need to discuss offline.

Thanks,
Suresh


>From: Shields Michael <[login to unmask email]>
>Reply-To: DB2 Database Discussion list at IDUG <[login to unmask email]>
>To: [login to unmask email]
>Subject: error handling in sql storedProcedures for db2 OS390 v7
>Date: Tue, 2 Dec 2003 16:37:47 -0600
>
>I have a stored procedure which is called by a trigger.
>The stored procedure is responsible for inserting some data into a table.
>If that insert is gets a duplicate key on insert I want to return
>successfully to the trigger (without throwing the -813 back up to the
>trigger which goes back to my java application).
>
>I read a redbook that came out in 2001 for iseries that talks of error
>handling which sounded perfect, but of course that didn't work for me.
>I need to replace the "declare continue handler" with a solution for OS390.
>Here's what I had if it helps to understand my issue:
>
>CREATE PROCEDURE schema.spname (IN ORD_NUM CHAR(17))
>LANGUAGE SQL
>EXTERNAL NAME spname
>MODIFIES SQL DATA
>COLLID CDR_DNS
>WLM ENVIRONMENT DB39WL01
>STAY RESIDENT NO
>COMMIT ON RETURN NO
>
>BEGIN ATOMIC
>DECLARE DUPLICATE_RECORD CONDITION FOR SQLSTATE '23505';
>DECLARE AT_END INT DEFAULT 0;
>
>INSERT INTO schema.table (col1, col2, col3)
>VALUES ('CONSTANT','CONSTANT2',ORD_NUM);
>
>DECLARE CONTINUE HANDLER FOR DUPLICATE_RECORD
>SET AT_END = 1;
>
>END
>
>
>What are my options?
>
>---------------------------------------------------------------------------
------
>Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
>page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
>"Join or Leave the list". If you will be out of the office, send the SET
>DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins
>can be reached at [login to unmask email] Find out the latest on
>IDUG conferences at http://conferences.idug.org/index.cfm

_________________________________________________________________
Tired of slow downloads and busy signals? Get a high-speed Internet
connection! Comparison-shop your local high-speed providers here.
https://broadband.msn.com

----------------------------------------------------------------------------
-----
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". If you will be out of the office, send the SET
DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins
can be reached at [login to unmask email] Find out the latest on
IDUG conferences at http://conferences.idug.org/index.cfm

Notice to recipient:
The information in this internet e-mail and any attachments is confidential
and may be privileged. It is intended solely for the addressee. If you are
not the intended addressee please notify the sender immediately by
telephone. If you are not the intended recipient, any disclosure, copying,
distribution or any action taken or omitted to be taken in reliance on it,
is prohibited and may be unlawful.
When addressed to external clients any opinions or advice contained in this
internet e-mail are subject to the terms and conditions expressed in any
applicable governing terms of business or client engagement letter issued by
the pertinent Bank of America group entity.
If this email originates from the U.K. please note that Bank of America,
N.A., London Branch, Banc of America Securities Limited and Banc of America
Futures Incorporated are regulated by the Financial Services Authority.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm