SEO

ALL:

I have created a INSERT trigger on table A in subsystem X. This AFTER
INSERT trigger will call a COBOL stored procedure, which inturn CONNECTs to
subsystem Y and inserts the same row into table B in subsystem Y.

When I test the trigger using table editor, inserts on table A in subsystem
X gets refelected on to table B on subsystem Y.

But when CICS program INSERTS into table A in subsystem X, the stored
procedure that the trigger calls, returns -817 and does not insert into
table B in subsystem Y. However the record gets inserted into table A in
subsystem X.

Any help to solve the -817 error?

TIA

Hello,

the scenario you described is in fact a multisite-update, which requires
each resource manager involved to support a protocol called "two phase
commit". The aim of this protocol is, that all resource managers either
commit or roll back changes to the data they manage to ensure data integrity
("all or nothing at all") . Two phase commit always hase one commit
coordinator (usually the initiating resource manager that must be able to
talk "two phase commit" to all the other participants of the transaction.

In your first case (Table editor), the originating Db2 that fires the
trigger becomes the commit coordinator and the update this trigger initiates
at another DB2 works fine because DB2A and DB2B understand the two pase
commit protocol.

If your transaction starts from CICS, this CICS becomes the commit
coordinator (because it is also a resource manager), but it can communicate
only with the local DB2A. Since this DB2A is not the coordinator, it is not
allowed tocoordinate updates with remote DB2B on behalf of CICS in this
case. (You are not even allowed to issue DB2A COMMITs or ROLLBACKs within a
CICS transaction - remember?).

So if your trigger should work under CICS too, the second DB2B must also be
known and connected to this CICS region - but AFAIK, this is only possible
if all the involved resource manager run under the same instance of the
operating system or within a z/OS Sysplex Cluster in conjunction with z/OS
RRM (Recoverable Resource Manager) as the "global" commit coordinator.

Cheers - Walter SCHNEIDER.


"db2sysc" <db2sysc@yahoo.com> schrieb im Newsbeitrag
news:6905d2bd.0408241001.79e7dbbb@posting.google.c om...
> ALL:
>
> I have created a INSERT trigger on table A in subsystem X. This AFTER
> INSERT trigger will call a COBOL stored procedure, which inturn CONNECTs
to
> subsystem Y and inserts the same row into table B in subsystem Y.
>
> When I test the trigger using table editor, inserts on table A in
subsystem
> X gets refelected on to table B on subsystem Y.
>
> But when CICS program INSERTS into table A in subsystem X, the stored
> procedure that the trigger calls, returns -817 and does not insert into
> table B in subsystem Y. However the record gets inserted into table A in
> subsystem X.
>
> Any help to solve the -817 error?
>
> TIA

Hello,

the scenario you described is in fact a multisite-update, which requires
each resource manager involved to support a protocol called "two phase
commit". The aim of this protocol is, that all resource managers either
commit or roll back changes to the data they manage to ensure data integrity
("all or nothing at all") . Two phase commit always hase one commit
coordinator (usually the initiating resource manager that must be able to
talk "two phase commit" to all the other participants of the transaction.

In your first case (Table editor), the originating Db2 that fires the
trigger becomes the commit coordinator and the update this trigger initiates
at another DB2 works fine because DB2A and DB2B understand the two pase
commit protocol.

If your transaction starts from CICS, this CICS becomes the commit
coordinator (because it is also a resource manager), but it can communicate
only with the local DB2A. Since this DB2A is not the coordinator, it is not
allowed tocoordinate updates with remote DB2B on behalf of CICS in this
case. (You are not even allowed to issue DB2A COMMITs or ROLLBACKs within a
CICS transaction - remember?).

So if your trigger should work under CICS too, the second DB2B must also be
known and connected to this CICS region - but AFAIK, this is only possible
if all the involved resource manager run under the same instance of the
operating system...

Cheers - Walter SCHNEIDER.

"db2sysc" <db2sysc@yahoo.com> schrieb im Newsbeitrag
news:6905d2bd.0408241001.79e7dbbb@posting.google.c om...
> ALL:
>
> I have created a INSERT trigger on table A in subsystem X. This AFTER
> INSERT trigger will call a COBOL stored procedure, which inturn CONNECTs
to
> subsystem Y and inserts the same row into table B in subsystem Y.
>
> When I test the trigger using table editor, inserts on table A in
subsystem
> X gets refelected on to table B on subsystem Y.
>
> But when CICS program INSERTS into table A in subsystem X, the stored
> procedure that the trigger calls, returns -817 and does not insert into
> table B in subsystem Y. However the record gets inserted into table A in
> subsystem X.
>
> Any help to solve the -817 error?
>
> TIA

Thanks a lot Walter.


"Walter Schneider" <walter.schneider@telekom.at.nospam> wrote in message news:<412cb669$0$30904$91cee783@newsreader02.highw ay.telekom.at>...
> Hello,
>
> the scenario you described is in fact a multisite-update, which requires
> each resource manager involved to support a protocol called "two phase
> commit". The aim of this protocol is, that all resource managers either
> commit or roll back changes to the data they manage to ensure data integrity
> ("all or nothing at all") . Two phase commit always hase one commit
> coordinator (usually the initiating resource manager that must be able to
> talk "two phase commit" to all the other participants of the transaction.
>
> In your first case (Table editor), the originating Db2 that fires the
> trigger becomes the commit coordinator and the update this trigger initiates
> at another DB2 works fine because DB2A and DB2B understand the two pase
> commit protocol.
>
> If your transaction starts from CICS, this CICS becomes the commit
> coordinator (because it is also a resource manager), but it can communicate
> only with the local DB2A. Since this DB2A is not the coordinator, it is not
> allowed tocoordinate updates with remote DB2B on behalf of CICS in this
> case. (You are not even allowed to issue DB2A COMMITs or ROLLBACKs within a
> CICS transaction - remember?).
>
> So if your trigger should work under CICS too, the second DB2B must also be
> known and connected to this CICS region - but AFAIK, this is only possible
> if all the involved resource manager run under the same instance of the
> operating system...
>
> Cheers - Walter SCHNEIDER.
>
> "db2sysc" <db2sysc@yahoo.com> schrieb im Newsbeitrag
> news:6905d2bd.0408241001.79e7dbbb@posting.google.c om...
> > ALL:
> >
> > I have created a INSERT trigger on table A in subsystem X. This AFTER
> > INSERT trigger will call a COBOL stored procedure, which inturn CONNECTs
> to
> > subsystem Y and inserts the same row into table B in subsystem Y.
> >
> > When I test the trigger using table editor, inserts on table A in
> subsystem
> > X gets refelected on to table B on subsystem Y.
> >
> > But when CICS program INSERTS into table A in subsystem X, the stored
> > procedure that the trigger calls, returns -817 and does not insert into
> > table B in subsystem Y. However the record gets inserted into table A in
> > subsystem X.
> >
> > Any help to solve the -817 error?
> >
> > TIA

Walter:

What about issuing a COMMIT in the SP? This way when I connect
Subsystem Y and perform I/U/D on Table B will be commited or
rollback?


"Walter Schneider" <walter.schneider@telekom.at.nospam> wrote in message news:<412cb669$0$30904$91cee783@newsreader02.highw ay.telekom.at>...
> Hello,
>
> the scenario you described is in fact a multisite-update, which requires
> each resource manager involved to support a protocol called "two phase
> commit". The aim of this protocol is, that all resource managers either
> commit or roll back changes to the data they manage to ensure data integrity
> ("all or nothing at all") . Two phase commit always hase one commit
> coordinator (usually the initiating resource manager that must be able to
> talk "two phase commit" to all the other participants of the transaction.
>
> In your first case (Table editor), the originating Db2 that fires the
> trigger becomes the commit coordinator and the update this trigger initiates
> at another DB2 works fine because DB2A and DB2B understand the two pase
> commit protocol.
>
> If your transaction starts from CICS, this CICS becomes the commit
> coordinator (because it is also a resource manager), but it can communicate
> only with the local DB2A. Since this DB2A is not the coordinator, it is not
> allowed tocoordinate updates with remote DB2B on behalf of CICS in this
> case. (You are not even allowed to issue DB2A COMMITs or ROLLBACKs within a
> CICS transaction - remember?).
>
> So if your trigger should work under CICS too, the second DB2B must also be
> known and connected to this CICS region - but AFAIK, this is only possible
> if all the involved resource manager run under the same instance of the
> operating system...
>
> Cheers - Walter SCHNEIDER.
>
> "db2sysc" <db2sysc@yahoo.com> schrieb im Newsbeitrag
> news:6905d2bd.0408241001.79e7dbbb@posting.google.c om...
> > ALL:
> >
> > I have created a INSERT trigger on table A in subsystem X. This AFTER
> > INSERT trigger will call a COBOL stored procedure, which inturn CONNECTs
> to
> > subsystem Y and inserts the same row into table B in subsystem Y.
> >
> > When I test the trigger using table editor, inserts on table A in
> subsystem
> > X gets refelected on to table B on subsystem Y.
> >
> > But when CICS program INSERTS into table A in subsystem X, the stored
> > procedure that the trigger calls, returns -817 and does not insert into
> > table B in subsystem Y. However the record gets inserted into table A in
> > subsystem X.
> >
> > Any help to solve the -817 error?
> >
> > TIA

Hello,

unfortunately, there is the same restriction, as documented in DB2 for z/OS
SQL Reference: :
"The COMMIT statement cannot be used in a stored procedure if the procedure
is in

the calling chain of a user-defined function or a trigger or if the caller
is using a

two-phase commit."

The only solution seems to be, that the remote DB2 will be made known to
your CICS so it can become participant in the two phase commit process.
Another option might be, that CICS and both DB2's use the z/OS Recoverable
Resource Services (RRS) if that is implemented at your site and both DB2's
are running within the same z/OS image or Sysplex Cluster. DB2 has a
dedicated language interface called "DSNRLI" for this purpose, but I don't
know if CICS has this capability too...

Cheers - Walter.

"db2sysc" <db2sysc@yahoo.com> schrieb im Newsbeitrag
news:6905d2bd.0408301333.2896cdd0@posting.google.c om...
> Walter:
>
> What about issuing a COMMIT in the SP? This way when I connect
> Subsystem Y and perform I/U/D on Table B will be commited or
> rollback?
>
>
> "Walter Schneider" <walter.schneider@telekom.at.nospam> wrote in message
news:<412cb669$0$30904$91cee783@newsreader02.highw ay.telekom.at>...
> > Hello,
> >
> > the scenario you described is in fact a multisite-update, which requires
> > each resource manager involved to support a protocol called "two phase
> > commit". The aim of this protocol is, that all resource managers either
> > commit or roll back changes to the data they manage to ensure data
integrity
> > ("all or nothing at all") . Two phase commit always hase one commit
> > coordinator (usually the initiating resource manager that must be able
to
> > talk "two phase commit" to all the other participants of the
transaction.
> >
> > In your first case (Table editor), the originating Db2 that fires the
> > trigger becomes the commit coordinator and the update this trigger
initiates
> > at another DB2 works fine because DB2A and DB2B understand the two pase
> > commit protocol.
> >
> > If your transaction starts from CICS, this CICS becomes the commit
> > coordinator (because it is also a resource manager), but it can
communicate
> > only with the local DB2A. Since this DB2A is not the coordinator, it is
not
> > allowed tocoordinate updates with remote DB2B on behalf of CICS in this
> > case. (You are not even allowed to issue DB2A COMMITs or ROLLBACKs
within a
> > CICS transaction - remember?).
> >
> > So if your trigger should work under CICS too, the second DB2B must also
be
> > known and connected to this CICS region - but AFAIK, this is only
possible
> > if all the involved resource manager run under the same instance of the
> > operating system...
> >
> > Cheers - Walter SCHNEIDER.
> >
> > "db2sysc" <db2sysc@yahoo.com> schrieb im Newsbeitrag
> > news:6905d2bd.0408241001.79e7dbbb@posting.google.c om...
> > > ALL:
> > >
> > > I have created a INSERT trigger on table A in subsystem X. This AFTER
> > > INSERT trigger will call a COBOL stored procedure, which inturn
CONNECTs
> > to
> > > subsystem Y and inserts the same row into table B in subsystem Y.
> > >
> > > When I test the trigger using table editor, inserts on table A in
> > subsystem
> > > X gets refelected on to table B on subsystem Y.
> > >
> > > But when CICS program INSERTS into table A in subsystem X, the stored
> > > procedure that the trigger calls, returns -817 and does not insert
into
> > > table B in subsystem Y. However the record gets inserted into table A
in
> > > subsystem X.
> > >
> > > Any help to solve the -817 error?
> > >
> > > TIA