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

Ray Gaston

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

Still not getting process to work. Can’t get or don’t know how, a COBOL Stored Procedure passes back to the Trigger that called it, that it experienced an error.
Can you supply some sample Cobol code the a could share with the programmers here.

Thanks – Ray Gaston
ConEdison

_____________________________________________
From: Gaston, Raymond
Sent: Friday, December 17, 2010 2:26 PM
To: IDUG DB2-L
Cc: dl - IR DBA ORANGE & ROCKLAND
Subject: RE: [DB2-L] Z/OS DB2V8: Getting RC Back to Trigger from SP


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 *
* If you are going to attend only one conference this year, this is it! *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

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)
The parameter style is documented in the DB2 application programming guide. Here is something I have as an example. You set the SQLSTATE to an appropriate code and BAM, the trigger fails.



Cheers,

Dan



Daniel L Luksetich

IBM Information Champion

IBM Certified Database Administrator - DB2 10 for z/OS

IBM Certified System Administrator - DB2 9 for z/OS

IBM Certified Solutions Expert - DB2 Universal Database V7.1 Database Administration for UNIX, Windows, and OS/2

IBM Certified Solutions Expert - DB2 UDB V7.1 Family Application Development

IBM Certified Advanced Technical Expert - DB2 Data Replication



Vice President of Global Database Operations

YL&A, Inc.

Database Performance Professionals

http://www.ylassoc.com

http://www.db2expert.com

http://www-01.ibm.com/software/data/champion/profiles/luksetich.html







CREATE PROCEDURE U1430.CBLSP1C

( IN DBNAME CHAR(8)

, IN TSNAME CHAR(8)

)

PARAMETER STYLE SQL…….



003700 LINKAGE SECTION.
003800
003900 01 INPUT-DBNAME PIC X(08).
003910 01 INPUT-TSNAME PIC X(08).
004000 01 NULL-DBNAME PIC S9(04) COMP.
004010 01 NULL-TSNAME PIC S9(04) COMP.
004100 01 OUT-SQLSTATE PIC X(05).
004200 01 IN-PROCNAME PIC X(27).
004300 01 IN-SPEC PIC X(18).
004400 01 OUT-DIAG.
004500 49 DIAG-LEN PIC S9(04) COMP.
004600 49 DIAG-TEXT PIC X(70).
004700
004800*----------------------------------------------------------------*
004900 PROCEDURE DIVISION USING INPUT-DBNAME,
004910 INPUT-TSNAME,
005000 NULL-DBNAME,
005010 NULL-TSNAME,
005100 OUT-SQLSTATE,
005200 IN-PROCNAME,
005300 IN-SPEC,
005400 OUT-DIAG.



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Gaston, Raymond
Sent: Friday, January 07, 2011 9:03 AM
To: [login to unmask email]
Subject: [SPAM] Re: Z/OS DB2V8: Getting RC Back to Trigger from SP



Dan…

Still not getting process to work. Can’t get or don’t know how, a COBOL Stored Procedure passes back to the Trigger that called it, that it experienced an error.

Can you supply some sample Cobol code the a could share with the programmers here.

Thanks – Ray Gaston

ConEdison

_____________________________________________
From: Gaston, Raymond
Sent: Friday, December 17, 2010 2:26 PM
To: IDUG DB2-L
Cc: dl - IR DBA ORANGE & ROCKLAND
Subject: RE: [DB2-L] Z/OS DB2V8: Getting RC Back to Trigger from SP

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



_____

< http://www-01.ibm.com/software/data/db2/zos/db2-10/ > Introducing IBM® DB2® 10 for z/OS

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please < http://www.idug.org/register > register here.

No virus found in this incoming message.
Checked by AVG - www.avg.com
Version: 9.0.872 / Virus Database: 271.1.1/3357 - Release Date: 01/07/11 01:34:00


_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

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