We normally do this to pass on to MQ Series the row change based on the
column that changed so that MQ based on its rules can deliver the data to
the right message queue.
I do not know what platform you are in but you can do something to the
equivalent of a DB2 Instrumentation Facility Interface read on the logs, the
before Image will have a UB image designation in ne of the log information
and the after Image will have a UP and then in your trigger, call a stored
procedure that will compare the UB and the UP and then pass any differences
on the trigger variables any differences. Your trigger must be prepared to
handle multiple column changes because it is possible that there are more
than one changed field on a single row.
You are correct, it will get very ugly if you start comparing data pulled
from the table itself, so our approach was to get it from the DB2 logs. The
basic steps are:
1) Turn on Logging for the table you wil ldetect the column change.
2) Have an update trigger that will:
a) Call a program to do the DB2 IFI using an IFCID306 to read the
log for this particular table.
b) Read the UB and UP portion of the read result.
c) Compare every column and dump the differences in a flat file.
d) Get the after image from a flat file.
** Since we are forwarding the info to MQ, we proceeded with the following
steps bus based on what you need, you can proceed appropriate to what you
need to accomplish**
e) Format the information gathered in the correct XML format (in our
case it is XML).
f) Deposit this XML to the message queue.
g) Go back to the trigger and report that it is done, otherwise send
an error.
h) Once in the message queue, th erules we set in can forward the
xml document to the right queue or database based on the changed column
information.
If you do not want to write that program, you can use Q Replication and
doing the DB2 IFI will be handled by it, you will just have to write the
program detecting the differences on the rows of the before and after
images. The queue delivery I believe will also be handled by Q Replication.
Hope this helps.
RdR
"dataguy" <ba*********@progressive.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
I can't find it anywhere in the manual, but I have a developer that
wants to know if we can code a trigger to capture the data that has
changed in a table only if certain columns have changed. It looks like
I can do it with the when clause and check the old vs new, but that
would get very ugly with a large table. Has anyone done something like
this?
