I would never require a database update to complete on a remote system
before allowing a local database update to happen. If the remote system
is unavailable for any reason, your local system is as good as down. To
use remote journaling and allow the journal entries to queue up is much
better for performance, reliability, and data integrity. Queuing to a
server job will also work if you don't wait for the feedback, but you
lose the integrity at that time. Time to step back and look at what you
are trying to accomplish, the business requirements, and the entire
network of system in between.
Chris Bipes
Director of Information Services
CrossCheck, Inc.
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Monday, September 17, 2007 4:46 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: Trigger or Stored Procedure?
Since the action must be triggered on change requests to data in a
column on the TABLE, the trigger seems the desirable approach. To limit
directly by the database, the changes to a specific column, the trigger
would be create by CREATE TRIGGER FOR UPDATE OF. That trigger program
can defer directly to a non-SQL program by an SQL CALL to a program
which has been registered as an external stored procedure on the System
i. However AFaIK a non-SQL trigger added by ADDPFTRG will have the same
CONNECT restriction according to:
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=/d
bp/rbafotrgncmd.htm
This seems confirmed by the following book; refer to "You cannot perform
DRDA access in a trigger program." at:
http://www.redbooks.ibm.com/abstracts/sg246503.html?Open
Thus I believe the trigger must defer the connect and further work,
to a separate process that initiates [and maintains] the DRDA connection
with the DB2 LUW. In *that* connection, to then do _either_ a CALL of a
stored procedure on the remote database or perform the INSERT/UPDATE
directly -- in response to the message enqueued with the request
details. A trigger program [or a program it calls] can add a request to
a queue that is being monitored by a server job, then optionally await
feedback from the background request - accounting for trigger timeout
concerns.
If transaction integrity is required, that second link implies a DDM
connection may be possible for the insert/update activity on the target
system, using two-phase commit.
As an Amazon Associate we earn from qualifying purchases.