SQLCODE -817

[db2sysc]

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 Schneider]

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" <db*****@yahoo.com> schrieb im Newsbeitrag
news:69**************************@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 Schneider]

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" <db*****@yahoo.com> schrieb im Newsbeitrag
news:69**************************@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

[db2sysc]

Thanks a lot Walter.
"Walter Schneider" <wa**************@telekom.at.nospam> wrote in message news:<41***********************@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" <db*****@yahoo.com> schrieb im Newsbeitrag
news:69**************************@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

[db2sysc]

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" <wa**************@telekom.at.nospam> wrote in message news:<41***********************@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" <db*****@yahoo.com> schrieb im Newsbeitrag
news:69**************************@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 Schneider]

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" <db*****@yahoo.com> schrieb im Newsbeitrag
news:69**************************@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" <wa**************@telekom.at.nospam> wrote in message

news:<41***********************@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" <db*****@yahoo.com> schrieb im Newsbeitrag
news:69**************************@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