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.
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