Z/OS DB2V8: Getting RC Back to Trigger from SP

Ray Gaston

Z/OS DB2V8: Getting RC Back to Trigger from SP
Dear Listers:



We have an AFTER UPDATE trigger calling a COBOL Stored Procedure.
What/how/can we get sqlcode/sqlstate information back to the trigger
from the SP?

While we are not new to the use of triggers, this is a first at calling
a SP from a trigger for us. The calling worked fine. We are now testing
various "error scenarios".



* If we stop the SP, the process waits(hangs) for the installation
default time of 180 minutes then returns. How can the trigger capture
identify this?

* If we anything else were to go wrong in the SP we'd like the trigger
to be able to identify this also "so it can return that info" to the
environment that called it.

* Not sure if SIGNAL is the way to go.



Any suggestions, sample trigger syntax code, direction would be deeply
appreciated.



Regards - Ray Gaston

Coned DBA


_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Daniel Luksetich

Re: Z/OS DB2V8: Getting RC Back to Trigger from SP
(in response to Ray Gaston)
Using parameter style SQL is the way to throw a SQLSTATE back to the
caller. In this case it's the trigger. Then the trigger handles a failed
statement and throws that back to the invoker. The invoker will get a
generic SQLCODE, but the SQLSTATE can be customized in the stored procedure
for your needs. The SQL message text can also be set by your SP to send
back an encoded message. It could also write to SYSOUT with a timestamp and
the details corresponding to the encoded message.

That's a pretty effective technique.

I have used SIGNAL in triggers, but not in stored procedures. I'm guessing
that using it in your stored procedure should be pretty much the same as
using parameter style SQL. So, I think that's a good idea. Make sure you
establish some in-house standard SQLSTATEs.

Dan Luksetich

On Fri, 17 Dec 2010 10:54:04 -0500, "Gaston, Raymond"
<[login to unmask email]>
wrote:
> Dear Listers:
>
>
>
> We have an AFTER UPDATE trigger calling a COBOL Stored Procedure.
> What/how/can we get sqlcode/sqlstate information back to the trigger
> from the SP?
>
> While we are not new to the use of triggers, this is a first at calling
> a SP from a trigger for us. The calling worked fine. We are now testing
> various "error scenarios".
>
>
>
> * If we stop the SP, the process waits(hangs) for the installation
> default time of 180 minutes then returns. How can the trigger capture
> identify this?
>
> * If we anything else were to go wrong in the SP we'd like the trigger
> to be able to identify this also "so it can return that info" to the
> environment that called it.
>
> * Not sure if SIGNAL is the way to go.
>
>
>
> Any suggestions, sample trigger syntax code, direction would be deeply
> appreciated.
>
>
>
> Regards - Ray Gaston
>
> Coned DBA
>
>
> _____________________________________________________________________
> * IDUG North America * Anaheim, California * May 2-6 2011 *
> http://IDUG.ORG/NA *
> * Your only source for independent, unbiased, and trusted DB2
> information. *
> ** The most DB2 technical sessions of any conference
> ** Access IBM experts and developers
> _____________________________________________________________________
>
> If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
is
> the home of IDUG's Listserv

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Ray Gaston

Re: Z/OS DB2V8: Getting RC Back to Trigger from SP
(in response to Daniel Luksetich)
Dan,

Thanks for the reply. Below is our trigger. Are you saying to add a second parameter to our CALL below (i.e. SQLSTATE) to be returned from the SP.


CREATE TRIGGER TEST.TXXX100A
AFTER UPDATE OF CDE_STAT
ON TEST.WRKSTAT
REFERENCING OLD AS O
NEW AS N
FOR EACH ROW MODE DB2SQL
WHEN (O.CDE_STAT <> N.CDE_STAT)
BEGIN ATOMIC
CALL TEST.YYY123S(O.CDE_WR, SQLSTATE);
END#



-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Dan Luksetich
Sent: Friday, December 17, 2010 11:52 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Z/OS DB2V8: Getting RC Back to Trigger from SP

Using parameter style SQL is the way to throw a SQLSTATE back to the
caller. In this case it's the trigger. Then the trigger handles a failed
statement and throws that back to the invoker. The invoker will get a
generic SQLCODE, but the SQLSTATE can be customized in the stored procedure
for your needs. The SQL message text can also be set by your SP to send
back an encoded message. It could also write to SYSOUT with a timestamp and
the details corresponding to the encoded message.

That's a pretty effective technique.

I have used SIGNAL in triggers, but not in stored procedures. I'm guessing
that using it in your stored procedure should be pretty much the same as
using parameter style SQL. So, I think that's a good idea. Make sure you
establish some in-house standard SQLSTATEs.

Dan Luksetich

On Fri, 17 Dec 2010 10:54:04 -0500, "Gaston, Raymond"
<[login to unmask email]>
wrote:
> Dear Listers:
>
>
>
> We have an AFTER UPDATE trigger calling a COBOL Stored Procedure.
> What/how/can we get sqlcode/sqlstate information back to the trigger
> from the SP?
>
> While we are not new to the use of triggers, this is a first at calling
> a SP from a trigger for us. The calling worked fine. We are now testing
> various "error scenarios".
>
>
>
> * If we stop the SP, the process waits(hangs) for the installation
> default time of 180 minutes then returns. How can the trigger capture
> identify this?
>
> * If we anything else were to go wrong in the SP we'd like the trigger
> to be able to identify this also "so it can return that info" to the
> environment that called it.
>
> * Not sure if SIGNAL is the way to go.
>
>
>
> Any suggestions, sample trigger syntax code, direction would be deeply
> appreciated.
>
>
>
> Regards - Ray Gaston
>
> Coned DBA
>
>
> _____________________________________________________________________
> * IDUG North America * Anaheim, California * May 2-6 2011 *
> http://IDUG.ORG/NA *
> * Your only source for independent, unbiased, and trusted DB2
> information. *
> ** The most DB2 technical sessions of any conference
> ** Access IBM experts and developers
> _____________________________________________________________________
>
> If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
is
> the home of IDUG's Listserv

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Daniel Luksetich

Re: Z/OS DB2V8: Getting RC Back to Trigger from SP
(in response to Ray Gaston)
That is one technique. However, if you define the COBOL stored procedure
with PARAMETER STYLE SQL then you can simply set the SQLSTATE you desire in
the stored procedure. Then the CALL from the trigger fails with that
SQLSTATE. I think you can get the same result with SIGNAL, but I have not
tried it. I have used parameter style SQL before and it works just fine.

Give it a try just make sure you get the parameters seet up right in the
SP. It is all well documented in the programming guide.

Cheers,
Dan

On Fri, 17 Dec 2010 12:16:21 -0500, "Gaston, Raymond"
<[login to unmask email]>
wrote:
> Dan,
>
> Thanks for the reply. Below is our trigger. Are you saying to add a
second
> parameter to our CALL below (i.e. SQLSTATE) to be returned from the SP.
>
>
> CREATE TRIGGER TEST.TXXX100A
> AFTER UPDATE OF CDE_STAT
> ON TEST.WRKSTAT
> REFERENCING OLD AS O
> NEW AS N
> FOR EACH ROW MODE DB2SQL
> WHEN (O.CDE_STAT <> N.CDE_STAT)
> BEGIN ATOMIC
> CALL TEST.YYY123S(O.CDE_WR, SQLSTATE);
> END#
>
>
>
> -----Original Message-----
> From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Dan Luksetich
> Sent: Friday, December 17, 2010 11:52 AM
> To: [login to unmask email]
> Subject: Re: [DB2-L] Z/OS DB2V8: Getting RC Back to Trigger from SP
>
> Using parameter style SQL is the way to throw a SQLSTATE back to the
> caller. In this case it's the trigger. Then the trigger handles a failed
> statement and throws that back to the invoker. The invoker will get a
> generic SQLCODE, but the SQLSTATE can be customized in the stored
procedure
> for your needs. The SQL message text can also be set by your SP to send
> back an encoded message. It could also write to SYSOUT with a timestamp
and
> the details corresponding to the encoded message.
>
> That's a pretty effective technique.
>
> I have used SIGNAL in triggers, but not in stored procedures. I'm
guessing
> that using it in your stored procedure should be pretty much the same as
> using parameter style SQL. So, I think that's a good idea. Make sure you
> establish some in-house standard SQLSTATEs.
>
> Dan Luksetich
>
> On Fri, 17 Dec 2010 10:54:04 -0500, "Gaston, Raymond"
> <[login to unmask email]>
> wrote:
>> Dear Listers:
>>
>>
>>
>> We have an AFTER UPDATE trigger calling a COBOL Stored Procedure.
>> What/how/can we get sqlcode/sqlstate information back to the trigger
>> from the SP?
>>
>> While we are not new to the use of triggers, this is a first at calling
>> a SP from a trigger for us. The calling worked fine. We are now testing
>> various "error scenarios".
>>
>>
>>
>> * If we stop the SP, the process waits(hangs) for the installation
>> default time of 180 minutes then returns. How can the trigger capture
>> identify this?
>>
>> * If we anything else were to go wrong in the SP we'd like the trigger
>> to be able to identify this also "so it can return that info" to the
>> environment that called it.
>>
>> * Not sure if SIGNAL is the way to go.
>>
>>
>>
>> Any suggestions, sample trigger syntax code, direction would be deeply
>> appreciated.
>>
>>
>>
>> Regards - Ray Gaston
>>
>> Coned DBA
>>
>>
>> _____________________________________________________________________
>> * IDUG North America * Anaheim, California * May 2-6 2011 *
>> http://IDUG.ORG/NA *
>> * Your only source for independent, unbiased, and trusted DB2
>> information. *
>> ** The most DB2 technical sessions of any conference
>> ** Access IBM experts and developers
>> _____________________________________________________________________
>>
>> If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
> is
>> the home of IDUG's Listserv
>
> _____________________________________________________________________
> * IDUG North America * Anaheim, California * May 2-6 2011 *
> http://IDUG.ORG/NA *
> * Your only source for independent, unbiased, and trusted DB2
> information. *
> ** The most DB2 technical sessions of any conference
> ** Access IBM experts and developers
> _____________________________________________________________________
>
> If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
is
> the home of IDUG's Listserv
>
> _____________________________________________________________________
> * IDUG North America * Anaheim, California * May 2-6 2011 *
> http://IDUG.ORG/NA *
> * Your only source for independent, unbiased, and trusted DB2
> information. *
> ** The most DB2 technical sessions of any conference
> ** Access IBM experts and developers
> _____________________________________________________________________
>
> If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
is
> the home of IDUG's Listserv

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Ray Gaston

Re: Z/OS DB2V8: Getting RC Back to Trigger from SP
(in response to Daniel Luksetich)
Dan...

Excellent! Will try "defining the COBOL store procedure with PARAMETER STYLE SQL" as suggested. We have it defined as "PARAMETER STYLE GENERAL". Thanks again for the your valued input and direction.

Regards - Ray


-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Dan Luksetich
Sent: Friday, December 17, 2010 2:02 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Z/OS DB2V8: Getting RC Back to Trigger from SP

That is one technique. However, if you define the COBOL stored procedure
with PARAMETER STYLE SQL then you can simply set the SQLSTATE you desire in
the stored procedure. Then the CALL from the trigger fails with that
SQLSTATE. I think you can get the same result with SIGNAL, but I have not
tried it. I have used parameter style SQL before and it works just fine.

Give it a try just make sure you get the parameters seet up right in the
SP. It is all well documented in the programming guide.

Cheers,
Dan

On Fri, 17 Dec 2010 12:16:21 -0500, "Gaston, Raymond"
<[login to unmask email]>
wrote:
> Dan,
>
> Thanks for the reply. Below is our trigger. Are you saying to add a
second
> parameter to our CALL below (i.e. SQLSTATE) to be returned from the SP.
>
>
> CREATE TRIGGER TEST.TXXX100A
> AFTER UPDATE OF CDE_STAT
> ON TEST.WRKSTAT
> REFERENCING OLD AS O
> NEW AS N
> FOR EACH ROW MODE DB2SQL
> WHEN (O.CDE_STAT <> N.CDE_STAT)
> BEGIN ATOMIC
> CALL TEST.YYY123S(O.CDE_WR, SQLSTATE);
> END#
>
>
>
> -----Original Message-----
> From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Dan Luksetich
> Sent: Friday, December 17, 2010 11:52 AM
> To: [login to unmask email]
> Subject: Re: [DB2-L] Z/OS DB2V8: Getting RC Back to Trigger from SP
>
> Using parameter style SQL is the way to throw a SQLSTATE back to the
> caller. In this case it's the trigger. Then the trigger handles a failed
> statement and throws that back to the invoker. The invoker will get a
> generic SQLCODE, but the SQLSTATE can be customized in the stored
procedure
> for your needs. The SQL message text can also be set by your SP to send
> back an encoded message. It could also write to SYSOUT with a timestamp
and
> the details corresponding to the encoded message.
>
> That's a pretty effective technique.
>
> I have used SIGNAL in triggers, but not in stored procedures. I'm
guessing
> that using it in your stored procedure should be pretty much the same as
> using parameter style SQL. So, I think that's a good idea. Make sure you
> establish some in-house standard SQLSTATEs.
>
> Dan Luksetich
>
> On Fri, 17 Dec 2010 10:54:04 -0500, "Gaston, Raymond"
> <[login to unmask email]>
> wrote:
>> Dear Listers:
>>
>>
>>
>> We have an AFTER UPDATE trigger calling a COBOL Stored Procedure.
>> What/how/can we get sqlcode/sqlstate information back to the trigger
>> from the SP?
>>
>> While we are not new to the use of triggers, this is a first at calling
>> a SP from a trigger for us. The calling worked fine. We are now testing
>> various "error scenarios".
>>
>>
>>
>> * If we stop the SP, the process waits(hangs) for the installation
>> default time of 180 minutes then returns. How can the trigger capture
>> identify this?
>>
>> * If we anything else were to go wrong in the SP we'd like the trigger
>> to be able to identify this also "so it can return that info" to the
>> environment that called it.
>>
>> * Not sure if SIGNAL is the way to go.
>>
>>
>>
>> Any suggestions, sample trigger syntax code, direction would be deeply
>> appreciated.
>>
>>
>>
>> Regards - Ray Gaston
>>
>> Coned DBA
>>
>>
>> _____________________________________________________________________
>> * IDUG North America * Anaheim, California * May 2-6 2011 *
>> http://IDUG.ORG/NA *
>> * Your only source for independent, unbiased, and trusted DB2
>> information. *
>> ** The most DB2 technical sessions of any conference
>> ** Access IBM experts and developers
>> _____________________________________________________________________
>>
>> If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
> is
>> the home of IDUG's Listserv
>
> _____________________________________________________________________
> * IDUG North America * Anaheim, California * May 2-6 2011 *
> http://IDUG.ORG/NA *
> * Your only source for independent, unbiased, and trusted DB2
> information. *
> ** The most DB2 technical sessions of any conference
> ** Access IBM experts and developers
> _____________________________________________________________________
>
> If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
is
> the home of IDUG's Listserv
>
> _____________________________________________________________________
> * IDUG North America * Anaheim, California * May 2-6 2011 *
> http://IDUG.ORG/NA *
> * Your only source for independent, unbiased, and trusted DB2
> information. *
> ** The most DB2 technical sessions of any conference
> ** Access IBM experts and developers
> _____________________________________________________________________
>
> If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
is
> the home of IDUG's Listserv

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv