Q
Problem solve Get help with specific problems with your technologies, process and projects.

Trigger basics in DB2 and OS/390

I recently read your article here and I have a question.

TableA is a high transaction table. TableB is essentially a copy of TableA with indexes for reporting. I want to create an insert/update trigger on TableA to replicate the change to TableB.

I?m worried about performance. Mainly, will TableA be locked up until the commit on TableB completes? Or, will DB2 spawn another process to handle TableB?

Maybe you could also direct me to information on this problem? Thank you very much for your time.
The triggered action becomes part of the unit of work of the SQL statement that triggered the action. So, in your case, the process required to replicate the change to TableB will be part of the UOW that initially makes the change to TableA. In an environment with high transaction rates this scenario is not likely to perform very well for you.

How much of a delay can you incur between the change happening to TableA and it being replicated to TableB? If there can be a delay then I would recommend that you look into getting some type of replication or ETL tool so you can set up an asynchronous process having the modification to TableB get queued and then made in the background so that it does not impact the performance of the transactions changing TableA.

Here are some online resources for further reading:

TRIGGERS
DB2 Triggers
Object-Relational Programming with DB2
Are you Trigger Happy?
Fun with Triggers

REPLICATION
DB2 Replication
Replication Guide and Reference
Data Replication
Product Solutions

Editor's note: Do you agree with this expert's response? If you have more to share, post it in one of our .VO7aaqqaAFk.0@/search390>discussion forums.

This was last published in September 2004

Dig Deeper on IBM DB2 management

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchAWS

SearchContentManagement

SearchOracle

SearchSAP

SearchSQLServer

Close