It is a common scenario in integration projects to retrieve messages from the database for message enhancements, grouping and transmission.
We encountered recently a performance issue in a big healthcare integration project. The technologies employed were IBM DB2, IBM Message Broker (MB), and IBM Transformation Extender (WTX).
The process of retrieving messages from the database was developed in ESQL using SQL queries to retrieve messages and then concatenate them in an ESQL loop, for passing to downstream process (WTX) for transformation and grouping.
The Future of Big Data
With some guidance, you can craft a data platform that is right for your organization’s needs and gets the most return from your data capital.
As the number of messages to be processed increased to the magnitude of 10s of thousands, the retrieving and concatenation step took hours to execute.
One elegant solution for this task is to use XML Queries: XML aggregate functions (XMLAGG) and other XML scalar functions in DB2, which concatenate all the messages from different DB rows into single XML message. XMLAGG function is many times faster in data retrieval compare to traditional query and concatenate.
This eliminated ESQL processing logic (SQL Query, retrieval, looping and concatenation) with simple ESQL XML Query and reduced processing time from hours to minutes.
The use of XML queries resulted processing time reduction from hours to minutes.
Sometimes we need to explore beyond integration framework for opportunities, and gain overall benefit for the process.
In general Database aggregate functions are tuned for higher performance for massive data retrivals
