If you want to automatically propagate data from DB2 via triggers and stored procedures to a MQ, you will experience problems. MQ will not retain a connect between invocations of the stored procedure because of the change of TCB. There are two solutions:
1) Reconnect to the MQ each time you wish to send a message (costs 30 ms).
2) Triggers write to a DB2 table that can be polled by a program that puts the data onto an MQ. This results in very minimal delay (1-2 ms) in processing each record, and allows records to be transmitted on the MQ within a few seconds of the update occurring.
This tip has been tested on DB2 V6 for OS390.
Reader Feedback
Rick P. writes: Pete must have read our project plan. One of our projects intends to use triggers and stored procedures with MQ. His tip was timely. Being new to MQ, I wish he had given a bit more meat to the tip.
For More Information
- Feedback: E-mail the editor with your thoughts about this tip.
- More tips: Dozens of free DB2 tips and scripts.
- Tip contest: Have a DB2 tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
- Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
- Forums: Ask your technical DB2 questions--or help out your peers by answering them--in our active forums.
- Best Web Links: DB2 tips, tutorials, and scripts from around the Web.
This was first published in July 2002