Articles & Content

Archives

From Statement Cache to a Consolidated SQL Repository (F17)

Topic: Cross Platform DB2 for z/OS & LUW

Subtopic: 2011



New Java applications have dynamic SQL. We can harvest these statements, statistics included, from the statement cache into DSN_STATEMENT_CACHE _TABLE. At first glance we can find their most top consumers. But some delicate SQL statements are not obvious to see. We need some form of consolidation of all statements, eliminating literals and varying IN lists.This examination is possible with free tools like Data Studio and a few lines of code. The keys to consolidation are "regular expressions" and a hash code. SQLs with equal hash code can be summarized, counted and saved. And, now provided with an "eternal" statement ID, these SQLs can serve as an anchor point for more explorations, EXPLAIN - another source for consolidation -, additional statistics, index cleanup and documentation. And after all, this consolitation helps us to explore the investment and payback gap between the statements which are clear tuning candidates, and the well-behaved rest.



Click Here to Download

NOTE: These are only open to members of IDUG. If you are not a member, please CLICK HERE for more information.