RE: Trigger or Stored Procedure?

["Chris Bipes" <chris.bipes@xxxxxxxxxxxxxxx>]

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.