ZOS - V8 DB2 trigger question

Ray {PBSG} Janes

ZOS - V8 DB2 trigger question
Hello all,
Can triggers be used to replicate data changes on the same LPAR, but to different DB2's?

Assume the same LPAR.
TB1 on DB2A, TB2 is identical in structure and data but on DB2B.
An insert, update or delete occurs on TB1. Can it fire triggers and insert, update or delete the same data on TB2? TB2 is on a different DB2.

I'm reading that triggers are not allowed with a three-part table name.


Thanks

Ray Janes
Mainframe Database Administration - PepsiCo
[cid:[login to unmask email]







_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-videos.html has hundreds of video presentations!
Did you miss out on attending an IDUG conference?
Many of the presentations were recorded and are available on our website!
_____________________________________________________________________

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

Robert Catterall

Re: ZOS - V8 DB2 trigger question
(in response to Ray {PBSG} Janes)
A trigger defined on a table in subsystem DB2A can't directly reference
objects in subsystem DB2B. The trigger could call a stored procedure defined
on the local subsystem (DB2A), and that stored procedure could reference
objects in DB2B; however, if the rate of data change for the table in DB2A
(the table for which you want to replicate changes to a "mirror" table in
DB2B) is high (maybe many changed per second), I'd be concerned about the
impact on throughput of defining triggers that call stored procedures that
perform remote data change operations -- since the triggered action is
synchronous with respect to the triggering SQL statement, an INSERT (for
example) targeting that table in DB2A would not complete until the stored
procedure called by the trigger had completed its action (inserting the data
into the "mirror" table on DB2B).

Do you have MQ installed (formal name: WebSphere MQ)? If so, and if the rate
of data change activity for the "source" table on DB2A is high, you might
consider having the trigger put captured data change information on a local
queue (there are built-in DB2 functions that can be used for this purpose).
That way, the trigger's done as soon as the PUT to the local queue is done.
An MQ listener process could subsequently get the message off the queue and
-- maybe by way of calling a stored procedure -- could cause the updated
information to be applied to the mirror table in DB2B.

Robert


On Mon, Dec 7, 2009 at 1:34 PM, Janes, Ray {PBSG} <[login to unmask email]>wrote:

> Hello all,
>
> Can triggers be used to replicate data changes on the same LPAR, but to
> different DB2's?
>
>
>
> Assume the same LPAR.
>
> TB1 on DB2A, TB2 is identical in structure and data but on DB2B.
>
> An insert, update or delete occurs on TB1. Can it fire triggers and
> insert, update or delete the same data on TB2? TB2 is on a different DB2.
>
>
>
> I'm reading that triggers are not allowed with a three-part table name.
>
>
>
>
>
> Thanks
>
>
>
> Ray Janes
>
> Mainframe Database Administration - PepsiCo
>
> [image: BISlogo-horizontal]
>
>
>
>
>
>
>
>
>
> ------------------------------
>
> [image: IDUG - The Worldwide DB2 User Community!] < http://www.idug.org >
>
> The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
> not already an IDUG member, please register here. < http://www.idug.org/register >
>



--
Robert Catterall
Catterall Consulting
www.catterallconsulting.com

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-videos.html has hundreds of video presentations!
Did you miss out on attending an IDUG conference?
Many of the presentations were recorded and are available on our website!
_____________________________________________________________________

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