Make sure your tools are up to the job

We had a customer who had a COGNOS 8/BI development environment running against DB2 AIX V9.5.  They were having issues with extremely long elapsed times for COGNOS-generated queries (over 30 minutes!).  Temp space was being exceeded and users were increasing it but still experiencing the same problem.  The users checked COGNOS which seemed fine and they also ran DB2 Index Advisor and created additional indexes but to no avail.

So, Triton stepped in.  We ran some analysis using a tool which we find extremely useful – Brother Eagle, from our partners DBI Software.  The analysis showed a very high query execution cost.  SQL being generated by COGNOS was captured, and found to be a Cartesian join of two tables, each with more than 25M rows in.  New indexes were not being used.

The solution was to re-work the Cognos report definitions to add some missing database relationships. This caused COGNOS to generate correct JOIN predicates .  The reports were re-run to successful completion in under 2 minutes and the developers were educated to look at both COGNOS and “native” SQL.

The Moral

Many applications (such as BI, ERP and Java frameworks) are generating their own SQL – it can be difficult to know exactly what’s getting thrown at DB2.

Correct tooling (and just as importantly, the skills to interpret them) is essential 

Find out more about DB2 tools here http://www.triton.co.uk/DBISoftware 

Share
This entry was posted in DB2, DB2 Monitoring, DB2 Support, DB2 Tools, Remote DBA, Techie Tips and tagged , , , , . Bookmark the permalink.

One Response to Make sure your tools are up to the job

  1. Pingback: Tweets that mention Make sure your DB2 tools are up to the job | Triton Consulting -- Topsy.com

Comments are closed.