It seems that I hit a nerve when I was talking about DB2 tools. It seems many shops are upset at the rising costs of third party tools. IBM’s responses of some free tools that have limited capabilities are also getting limited enthusiasm.
What has been interesting is there really seems to be support for OSC. Tuning queries with this tool is easy and the setup is quite easy also. Sheryl Larsen commented that it is only a few steps to set it up. After getting OSC downloaded and installed it is as easy as clicking on the “Add Subsystem” menu to connect to the correct host name or TCP/IP address of your DB2 system. After that step, creating the DB2 Explains of your workload is simple through the OSC interface. Getting the pictures of the access path is great for understanding the functionality of the SQL in your environment and improving DB2 performance.
Other comments centered on the fact that their shops do not allow Administrator rights to download and install OSC. This is more common especially in Fortune 500 companies as workstation support can be an issue. So to get the information out of the Dynamic Statement Cache there are a few steps that need to be done if you cannot use OSC.
First, make sure all your DB2 Explain tables are built. Go to the IBM DB2 V9 for z/OS manuals and start directly within the SQL Reference (http://publib.boulder.ibm.com/epubs/pdf/dsnsqk18.pdf on page 1306 of the PDF) to get the first DDL for the PLAN_TABLE and the other EXPLAIN tables. You need the DB2 EXPLAIN tables (PLAN_TABLE, DSN_STATEMNT_TABLE and DSN_STATEMENT_CACHE_TABLE) to receive the cache information. Security and authorization for creating these Explain tables and DB2 commands also may be issues so work with your DBAs to get all ready.
Next, start the accounting and DB2 performance traces within your environment that include IFCIDs 316 and 318, which can be done easily through the simple DB2 command below. Be careful as these do cost a little bit of overhead but most shops already have the accounting traces with the various classes 1,2,3,7,8 running so it is probably will go unnoticed.
-START TRACE (PERFM) CLASS (30) DEST(GTF) IFCID(316,318)
Then let the traces run long enough to cache all the desired SQL statements and then issue the
EXPLAIN STMTCACHE ALL;
COMMIT;
The COMMIT materializes the SQL statements within your tables and your SQL cache will be captured within your DSN_STATEMENT_CACHE_TABLE. Use simple SQL to query the DSN_STATEMENT_CACHE_TABLE and see the all the great SQL statement DB2 performance information.
Also remember to turn off the traces.
-STOP TRACE (PERFM) CLASS (30) DEST(GTF)
Even if you cannot install OSC you can still get the information out of the Dynamic Statement Cache quickly and easily. Remember tools or no tools, the facilities to tune your environment are always available within all the platforms of DB2 and have fun improving your SQL and DB2 performance.
Leave a Reply