DB2 trigger.

Renzo Razzetti

DB2 trigger.
Hello.

I'm in DB2 V8. The customer asked me whether is possible "CALL a CICS
transaction from the DB2 trigger". I will research. Maybe if you know the
answer can help me.

Thank you

RR


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Robert Catterall

Re: DB2 trigger.
(in response to Renzo Razzetti)
I believe that a trigger can only issue SQL statements. That said, a
trigger could call a stored procedure, which in turn could invoke a CICS
transaction; however, I'd be hesitant to implement that kind of a mechanism
via trigger unless the actions that would cause the trigger to fire are
low-volume and not response time-sensitive (there would be a lot of
synchronous work that would have to get done before the trigger-firing SQL
statement could complete). A possible alternative: have the trigger place a
message on an MQ queue via the MQSEND scalar function, and have that queue
set up so as to invoke a CICS transaction upon receipt of the message. That
would make execution of the CICS tran asynchronous with respect to the
trigger-firing action, and you'd have to verify that this would be
acceptable. If it were to be acceptable, this should be a pretty
low-overhead, low-latency means of having the trigger kick off a CICS tran
(completion of the trigger-firing SQL statement would only have to wait for
the placement of the message on the MQ queue).

Robert


On Mon, Jan 12, 2009 at 3:40 AM, Renzo razzetti <[login to unmask email]>wrote:

> Hello.
>
> I'm in DB2 V8. The customer asked me whether is possible "CALL a CICS
> transaction from the DB2 trigger". I will research. Maybe if you know the
> answer can help me.
>
> Thank you
>
> RR
>
>
> ------------------------------
>
> *IDUG 2009 - Australasia * 18-20 March * Melbourne, Australia* < http://idug.org/lsAU >
>
> *IDUG.org* < http://www.idug.org > was recently updated requiring members to
> use a new password. You should have gotten an e-mail with the temporary
> password assigned to your account. Please log in and update your member
> profile. If you are not already an IDUG.org member, please register here. < http://www.idug.org/component/juser/register.html >
>



--
Robert Catterall
Catterall Consulting
www.catterallconsulting.com


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Suresh Sane

Re: DB2 trigger.
(in response to Robert Catterall)

Robert,

I am in general agreement with your analysis but want to add a couple of comments/clarifications. Overhead could be huge for high-volume transactions.

(1) Synchronous processing provided by trigger/SP may be the only way of ensuring data integrity. For example, if a critical VSAM file of reference data is changed to DB2 and all programs are not migrated, it could be used as a bridging solution to keep the VSAM and DB2 in sync all the time - near-real-time may not be enough.

(2) Most popular way of invoking CICS from DB2 is via the EXCI interface but the DB2-supplied stored procedure DSNACICS works quite well too. The scenario documented in the SP redbook we created issues this from within a SP but I think it should be possible to do so directly from a trigger (instead of calling an external SP, you call DSNACICS). I have not done this but believe should be possible. If true, a trigger can really "call CICS".

Thx
Suresh



Date: Mon, 12 Jan 2009 09:39:52 -0500From: [login to unmask email]: Re: [DB2-L] DB2 trigger.To: [login to unmask email] believe that a trigger can only issue SQL statements. That said, a trigger could call a stored procedure, which in turn could invoke a CICS transaction; however, I'd be hesitant to implement that kind of a mechanism via trigger unless the actions that would cause the trigger to fire are low-volume and not response time-sensitive (there would be a lot of synchronous work that would have to get done before the trigger-firing SQL statement could complete). A possible alternative: have the trigger place a message on an MQ queue via the MQSEND scalar function, and have that queue set up so as to invoke a CICS transaction upon receipt of the message. That would make execution of the CICS tran asynchronous with respect to the trigger-firing action, and you'd have to verify that this would be acceptable. If it were to be acceptable, this should be a pretty low-overhead, low-latency means of having the trigger kick off a CICS tran (completion of the trigger-firing SQL statement would only have to wait for the placement of the message on the MQ queue).Robert
On Mon, Jan 12, 2009 at 3:40 AM, Renzo razzetti <[login to unmask email]> wrote:
Hello.I'm in DB2 V8. The customer asked me whether is possible "CALL a CICS transaction from the DB2 trigger". I will research. Maybe if you know the answer can help me. Thank youRR


IDUG 2009 - Australasia * 18-20 March * Melbourne, Australia
IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register here. -- Robert CatterallCatterall Consultingwww.catterallconsulting.com


IDUG 2009 - Australasia * 18-20 March * Melbourne, Australia
IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register here.
_________________________________________________________________
Windows Live™ Hotmail®: Chat. Store. Share. Do more with mail.
http://windowslive.com/howitworks?ocid=TXT_TAGLM_WL_t1_hm_justgotbetter_howitworks_012009

______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Renzo Razzetti

Re: DB2 trigger.
(in response to Suresh Sane)
Suresh

Could you please tell me the number of your redbook ?

Thank you

On Tue, Jan 13, 2009 at 3:17 AM, Suresh Sane <[login to unmask email]> wrote:

> Robert,
>
> I am in general agreement with your analysis but want to add a couple of
> comments/clarifications. Overhead could be huge for high-volume
> transactions.
>
> (1) Synchronous processing provided by trigger/SP may be the only way of
> ensuring data integrity. For example, if a critical VSAM file of reference
> data is changed to DB2 and all programs are not migrated, it could be used
> as a bridging solution to keep the VSAM and DB2 in sync all the time -
> near-real-time may not be enough.
>
> (2) Most popular way of invoking CICS from DB2 is via the EXCI interface
> but the DB2-supplied stored procedure DSNACICS works quite well too. The
> scenario documented in the SP redbook we created issues this from within a
> SP but I think it should be possible to do so directly from a trigger
> (instead of calling an external SP, you call DSNACICS). I have not done this
> but believe should be possible. If true, a trigger can really "call CICS".
>
> Thx
> Suresh
> ------------------------------
>
> Date: Mon, 12 Jan 2009 09:39:52 -0500
> From: [login to unmask email]
> Subject: Re: [DB2-L] DB2 trigger.
> To: [login to unmask email]
>
>
> I believe that a trigger can only issue SQL statements. That said, a
> trigger could call a stored procedure, which in turn could invoke a CICS
> transaction; however, I'd be hesitant to implement that kind of a mechanism
> via trigger unless the actions that would cause the trigger to fire are
> low-volume and not response time-sensitive (there would be a lot of
> synchronous work that would have to get done before the trigger-firing SQL
> statement could complete). A possible alternative: have the trigger place a
> message on an MQ queue via the MQSEND scalar function, and have that queue
> set up so as to invoke a CICS transaction upon receipt of the message. That
> would make execution of the CICS tran asynchronous with respect to the
> trigger-firing action, and you'd have to verify that this would be
> acceptable. If it were to be acceptable, this should be a pretty
> low-overhead, low-latency means of having the trigger kick off a CICS tran
> (completion of the trigger-firing SQL statement would only have to wait for
> the placement of the message on the MQ queue).
>
> Robert
>
>
>
> On Mon, Jan 12, 2009 at 3:40 AM, Renzo razzetti <[login to unmask email]>wrote:
>
> Hello.
>
> I'm in DB2 V8. The customer asked me whether is possible "CALL a CICS
> transaction from the DB2 trigger". I will research. Maybe if you know the
> answer can help me.
>
> Thank you
>
> RR
>
>
> ------------------------------
>
> *IDUG 2009 - Australasia * 18-20 March * Melbourne, Australia* < http://idug.org/lsAU >
> *IDUG.org* < http://www.idug.org/ > was recently updated requiring members
> to use a new password. You should have gotten an e-mail with the temporary
> password assigned to your account. Please log in and update your member
> profile. If you are not already an IDUG.org member, please register here. < http://www.idug.org/component/juser/register.html >
>
>
>
>
> --
> Robert Catterall
> Catterall Consulting
> www.catterallconsulting.com
>
> ------------------------------
>
> *IDUG 2009 - Australasia * 18-20 March * Melbourne, Australia* < http://idug.org/lsAU >
> *IDUG.org* < http://www.idug.org/ > was recently updated requiring members
> to use a new password. You should have gotten an e-mail with the temporary
> password assigned to your account. Please log in and update your member
> profile. If you are not already an IDUG.org member, please register here. < http://www.idug.org/component/juser/register.html >
>
> ------------------------------
> Windows Live™ Hotmail(R): Chat. Store. Share. Do more with mail. See how it
> works. < http://windowslive.com/howitworks?ocid=TXT_TAGLM_WL_t1_hm_justgotbetter_howitworks_012009 >
>
> ------------------------------
>
> *IDUG 2009 - Australasia * 18-20 March * Melbourne, Australia* < http://idug.org/lsAU >
>
> *IDUG.org* < http://www.idug.org > was recently updated requiring members to
> use a new password. You should have gotten an e-mail with the temporary
> password assigned to your account. Please log in and update your member
> profile. If you are not already an IDUG.org member, please register here. < http://www.idug.org/component/juser/register.html >
>

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Mike Hutchinson

Re: DB2 trigger.
(in response to Renzo Razzetti)
DB2 for z/OS Stored Procedures: Through the CALL and Beyond
SG24-7083-00
discusses both EXCI and DSNACICS


________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Renzo razzetti
Sent: Monday, January 12, 2009 9:00 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 trigger.


Suresh

Could you please tell me the number of your redbook ?

Thank you


On Tue, Jan 13, 2009 at 3:17 AM, Suresh Sane <[login to unmask email]>
wrote:


Robert,

I am in general agreement with your analysis but want to add a
couple of comments/clarifications. Overhead could be huge for
high-volume transactions.

(1) Synchronous processing provided by trigger/SP may be the
only way of ensuring data integrity. For example, if a critical VSAM
file of reference data is changed to DB2 and all programs are not
migrated, it could be used as a bridging solution to keep the VSAM and
DB2 in sync all the time - near-real-time may not be enough.

(2) Most popular way of invoking CICS from DB2 is via the EXCI
interface but the DB2-supplied stored procedure DSNACICS works quite
well too. The scenario documented in the SP redbook we created issues
this from within a SP but I think it should be possible to do so
directly from a trigger (instead of calling an external SP, you call
DSNACICS). I have not done this but believe should be possible. If
true, a trigger can really "call CICS".

Thx
Suresh

________________________________


Date: Mon, 12 Jan 2009 09:39:52 -0500
From: [login to unmask email]
Subject: Re: [DB2-L] DB2 trigger.
To: [login to unmask email]


I believe that a trigger can only issue SQL statements. That
said, a trigger could call a stored procedure, which in turn could
invoke a CICS transaction; however, I'd be hesitant to implement that
kind of a mechanism via trigger unless the actions that would cause the
trigger to fire are low-volume and not response time-sensitive (there
would be a lot of synchronous work that would have to get done before
the trigger-firing SQL statement could complete). A possible
alternative: have the trigger place a message on an MQ queue via the
MQSEND scalar function, and have that queue set up so as to invoke a
CICS transaction upon receipt of the message. That would make execution
of the CICS tran asynchronous with respect to the trigger-firing action,
and you'd have to verify that this would be acceptable. If it were to
be acceptable, this should be a pretty low-overhead, low-latency means
of having the trigger kick off a CICS tran (completion of the
trigger-firing SQL statement would only have to wait for the placement
of the message on the MQ queue).

Robert




On Mon, Jan 12, 2009 at 3:40 AM, Renzo razzetti
<[login to unmask email]> wrote:


Hello.

I'm in DB2 V8. The customer asked me whether is possible
"CALL a CICS transaction from the DB2 trigger". I will research. Maybe
if you know the answer can help me.

Thank you

RR



________________________________


IDUG 2009 - Australasia * 18-20 March * Melbourne,
Australia < http://idug.org/lsAU >

IDUG.org < http://www.idug.org/ > was recently updated
requiring members to use a new password. You should have gotten an
e-mail with the temporary password assigned to your account. Please log
in and update your member profile. If you are not already an IDUG.org
member, please register here.
< http://www.idug.org/component/juser/register.html >





--
Robert Catterall
Catterall Consulting
www.catterallconsulting.com
< http://www.catterallconsulting.com/ >


________________________________


IDUG 2009 - Australasia * 18-20 March * Melbourne, Australia
< http://idug.org/lsAU >

IDUG.org < http://www.idug.org/ > was recently updated requiring
members to use a new password. You should have gotten an e-mail with the
temporary password assigned to your account. Please log in and update
your member profile. If you are not already an IDUG.org member, please
register here. < http://www.idug.org/component/juser/register.html >


________________________________

Windows Live(tm) Hotmail(r): Chat. Store. Share. Do more with
mail. See how it works.
<http://windowslive.com/howitworks?ocid=TXT_TAGLM_WL_t1_hm_justgotbetter
_howitworks_012009>

________________________________


IDUG 2009 - Australasia * 18-20 March * Melbourne, Australia
< http://idug.org/lsAU >

IDUG.org < http://www.idug.org > was recently updated requiring
members to use a new password. You should have gotten an e-mail with the
temporary password assigned to your account. Please log in and update
your member profile. If you are not already an IDUG.org member, please
register here. < http://www.idug.org/component/juser/register.html >



________________________________

IDUG 2009 - Europe * 5-9 October * Rome, Italy < http://idug.org/lseu >

IDUG.org < http://www.idug.org > was recently updated requiring members
to use a new password. You should have gotten an e-mail with the
temporary password assigned to your account. Please log in and update
your member profile. If you are not already an IDUG.org member, please
register here. < http://www.idug.org/component/juser/register.html >

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Peter Vanroose

Re: DB2 trigger.
(in response to Mike Hutchinson)
For DB2 v8 the redbook on Stored Procedures is SG24-7083-00.
(www.redbooks.ibm.com/abstracts/sg247083.html)
There is a new version for DB2 9: SG24-7604-00

-- Peter.

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Suresh Sane

Re: DB2 trigger.
(in response to Peter Vanroose)

Mike and Renzo,

To keep the record straight, I must point you to the later and current version SG24-7604 (March 2008) instead of the original SG24-7083 February 2006).

For personal reasons (since I was a co-author on the first), I like the oiginal, of course -:)

Thx
Suresh
> Date: Tue, 13 Jan 2009 09:07:42 -0500> From: [login to unmask email]> Subject: Re: [DB2-L] DB2 trigger.> To: [login to unmask email]> > DB2 for z/OS Stored Procedures: Through the CALL and Beyond> SG24-7083-00> discusses both EXCI and DSNACICS> > > ________________________________> > From: DB2 Data Base Discussion List [mailto:[login to unmask email] On> Behalf Of Renzo razzetti> Sent: Monday, January 12, 2009 9:00 PM> To: [login to unmask email]> Subject: Re: [DB2-L] DB2 trigger.> > > Suresh> > Could you please tell me the number of your redbook ?> > Thank you> > > On Tue, Jan 13, 2009 at 3:17 AM, Suresh Sane <[login to unmask email]>> wrote:> > > Robert,> > I am in general agreement with your analysis but want to add a> couple of comments/clarifications. Overhead could be huge for> high-volume transactions.> > (1) Synchronous processing provided by trigger/SP may be the> only way of ensuring data integrity. For example, if a critical VSAM> file of reference data is changed to DB2 and all programs are not> migrated, it could be used as a bridging solution to keep the VSAM and> DB2 in sync all the time - near-real-time may not be enough.> > (2) Most popular way of invoking CICS from DB2 is via the EXCI> interface but the DB2-supplied stored procedure DSNACICS works quite> well too. The scenario documented in the SP redbook we created issues> this from within a SP but I think it should be possible to do so> directly from a trigger (instead of calling an external SP, you call> DSNACICS). I have not done this but believe should be possible. If> true, a trigger can really "call CICS".> > Thx> Suresh> > ________________________________> > > Date: Mon, 12 Jan 2009 09:39:52 -0500> From: [login to unmask email]> Subject: Re: [DB2-L] DB2 trigger.> To: [login to unmask email] > > > I believe that a trigger can only issue SQL statements. That> said, a trigger could call a stored procedure, which in turn could> invoke a CICS transaction; however, I'd be hesitant to implement that> kind of a mechanism via trigger unless the actions that would cause the> trigger to fire are low-volume and not response time-sensitive (there> would be a lot of synchronous work that would have to get done before> the trigger-firing SQL statement could complete). A possible> alternative: have the trigger place a message on an MQ queue via the> MQSEND scalar function, and have that queue set up so as to invoke a> CICS transaction upon receipt of the message. That would make execution> of the CICS tran asynchronous with respect to the trigger-firing action,> and you'd have to verify that this would be acceptable. If it were to> be acceptable, this should be a pretty low-overhead, low-latency means> of having the trigger kick off a CICS tran (completion of the> trigger-firing SQL statement would only have to wait for the placement> of the message on the MQ queue).> > Robert> > > > > On Mon, Jan 12, 2009 at 3:40 AM, Renzo razzetti> <[login to unmask email]> wrote:> > > Hello.> > I'm in DB2 V8. The customer asked me whether is possible> "CALL a CICS transaction from the DB2 trigger". I will research. Maybe> if you know the answer can help me. > > Thank you> > RR> > > > ________________________________> > > IDUG 2009 - Australasia * 18-20 March * Melbourne,> Australia <http://idug.org/lsAU> > > IDUG.org <http://www.idug.org/> was recently updated> requiring members to use a new password. You should have gotten an> e-mail with the temporary password assigned to your account. Please log> in and update your member profile. If you are not already an IDUG.org> member, please register here.> <http://www.idug.org/component/juser/register.html> > > > > > > -- > Robert Catterall> Catterall Consulting> www.catterallconsulting.com> <http://www.catterallconsulting.com/> > > > ________________________________> > > IDUG 2009 - Australasia * 18-20 March * Melbourne, Australia> <http://idug.org/lsAU> > > IDUG.org <http://www.idug.org/> was recently updated requiring> members to use a new password. You should have gotten an e-mail with the> temporary password assigned to your account. Please log in and update> your member profile. If you are not already an IDUG.org member, please> register here. <http://www.idug.org/component/juser/register.html> > > > ________________________________> > Windows Live(tm) Hotmail(r): Chat. Store. Share. Do more with> mail. See how it works.> <http://windowslive.com/howitworks?ocid=TXT_TAGLM_WL_t1_hm_justgotbetter> _howitworks_012009> > > ________________________________> > > IDUG 2009 - Australasia * 18-20 March * Melbourne, Australia> <http://idug.org/lsAU> > > IDUG.org <http://www.idug.org> was recently updated requiring> members to use a new password. You should have gotten an e-mail with the> temporary password assigned to your account. Please log in and update> your member profile. If you are not already an IDUG.org member, please> register here. <http://www.idug.org/component/juser/register.html> > > > > ________________________________> > IDUG 2009 - Europe * 5-9 October * Rome, Italy <http://idug.org/lseu> > > IDUG.org <http://www.idug.org> was recently updated requiring members> to use a new password. You should have gotten an e-mail with the> temporary password assigned to your account. Please log in and update> your member profile. If you are not already an IDUG.org member, please> register here. < http://www.idug.org/component/juser/register.html > > > ______________________________________________________________________> > * IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/Events *> ______________________________________________________________________> > > > > IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html
_________________________________________________________________
Windows Live™ Hotmail®: Chat. Store. Share. Do more with mail.
http://windowslive.com/explore?ocid=TXT_TAGLM_WL_t1_hm_justgotbetter_explore_012009
______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html