This content made possible by our sponsor. It is not written by or reflect the views of MSP TechMedia or IBM Systems Magazine.

Doug Mack

Doug Mack

Analytics Consultant, Db2 for i

IBM Db2 for Web Query

Doug Mack currently leads the Db2 for i Analytics team for IBM’s Systems Lab Services technology enablement organization.

As an advocate for Business Intelligence on IBM i for many years, I have had a lot of conversations with IBM i customers and business partners about query processing. One subject that comes up often relates to the frustration with not being able to control query processing.
I recently polled our Lab Services consultants who are kept busy conducting systems or database level performance assessments to ask what they notice in their studies. They indicated that seeing query workloads of upwards of 50% of overall system resources is not atypical. Ouch.
Runaway queries can be painful. They often happen because of an inability to control how the query is written, combined with little or no proactive tuning to optimize execution. Free form query tools like green screen Query/400, SQL generators, or even Excel accessing Db2 for i data through an ODBC interface are classic examples of this ad-hocracy! Many times customized data extract programs are written to alleviate the ad-hocracy but that often has its own set of issues such as data being replicated all over the place, complicating data security and integrity, not to mention the impact of large extraction jobs.
When IBM with its technology partner Information Builders designed Db2 Web Query for i, ad-hocracy was dealt with head-on, through capabilities such as:
  • The metadata layer
  • Guided ad-hoc reports
  • Pushing the work to batch
  • Isolating the query workload
A Metadata layer is generally leveraged to shield the complexity of the underlying data source to report authors but can also be used to minimize or eliminate performance surprises. For example, predefining join conditions forces data access so you don’t end up with an end user mistakenly submitting a cartesian join of multiple million record files!
Guided ad-hoc forms front end OPTIMIZED queries, providing flexibility for end users in changing selection criteria while forcing the execution of a tuned query. 
If there is not a hard and fast requirement for real time data, but “near” real time, active reports and dashboards can be scheduled to run off hours (or in batch mode anytime). With this technology, end users can still dynamically work with the data, but completely offline from the server.
Or if you want to completely isolate query workloads from production systems to get out of ad-hocracy, Db2 Web Query offers DataMigrator ETL to automate data replication and transformations to move, and untangle data into an isolated reporting repository. DataMigrator can get you out of the customized extract business, freeing up more CPU cycles and making end users happier. 
Reduce your stress with a controlled ad-hoc managed query approach!

    Additional Products