DB2 & IMS

Since SQL issues and resource constraint issues are common, they’re natural areas for the new DBA to begin tuning.

Addressing application issues requires a more broad knowledge of application behaviors, access paths, batch windows, DB2 configuration parameters, and how all those tie together. There’s a learning curve, but the necessary information should be documented—somewhere. Typical symptoms include complaints about high-usage resources, application delays due to deadlocks and timeouts, and missed service levels.

Object issues deal with the enterprise data model and how database objects (usually table space partitions and indexes) are created and configured. This requires the DBA to do a competent analysis of several areas.

Systems issues can be extremely complex and require the DBA to have in-depth knowledge of DB2, other z/OS subsystems, hardware and software; this work isn’t suitable for a new DBA.

Let’s look at each of these categories in more detail, summarizing the areas for analysis, the tools you will use, the biggest productivity gains, and recommended first steps. You can then decide which strategy (or strategies) is best for implementation in your environment.

SQL Tuning

SQL tuning is probably the easiest tuning approach. It doesn’t require lots of DB2 systems expertise and uses minimal tools. You can get by with simple SQL statements executed from Query Management Facility (QMF) or SQL Processor Using File Input (SPUFI). In DB2 Version 8, you can use the Explain facility on either a single SQL statement in the dynamic SQL cache or on the entire cache, with access path information going into a DB2 table for later analysis. See the Application Programming Guide and the SQL Reference for more information.

What you analyze: access paths, object statistics.

Using what tools: Basic Explain, or perhaps a software tool that provides a user-friendly front-end to explain information. You also may run DB2 catalog management reports or queries, and even have access to a DB2 object statistics display or monitoring tool. One common example is the IBM Optimization Service Center.

Where to look for the largest productivity gains: For SQL coding, user training provides the best return on investment. Provide developers with clear, consistent SQL coding standards, coding examples, typical ways of accessing common tables, and descriptions of indexes and their uses for query performance. If possible, give them access to the Explain facility or the Optimization Service Center so they can analyze access path information themselves.

6 Pages