A Bag of Tricks

Understanding the ins and outs of DB2 performance can be an overwhelming task. DB2 tuning options are numerous and constantly changing. Even the number of SQL tuning options is staggering. The differences in efficiency can be substantial. For example, coding a query as a join instead of as a correlated subquery sometimes results in a query that performs better. The same query, however, might result in degraded performance. Plus, to make matters worse , a new version or release of DB2 can cause completely different results.

NOTE

Although a new release rarely causes the results set of a query to change, the performance results can change dramatically. And sometimes even the query's answer can change if, for example, a default changes. IBM works diligently to make sure this does not happen, though.

The release level of DB2 is not the only factor that can cause performance problems. Changes to the z/OS or OS/390 operating system, the DB2 database environment, the application code, or the application database can cause performance fluctuations. The following is a sample list of system changes that can affect DB2 query performance:

• Enterprisewide changes

  Distributing data

  Moving data from site to site

  Replicating and propagating data

  Downsizing, upsizing, and rightsizing

  Integrating legacy applications to the web

  Changing to a new hardware environment

  Adding more users

• z/OS and OS/390 system-level changes

  Modifying DB2 dispatching priorities

  Modifying CICS, IMS/TM, or TSO dispatching priorities

  Modifying network parameters (TCP/IP, SNA, and so on)

  Implementing (or modifying) Workload Manager

  Installing a new release of OS/390 or z/OS

  Installing a new release of CICS, IMS/TM, or TSO

  Implementing parallel sysplex

  Modifying TSO parameters

  Adding or removing memory

  Installing additional hardware that consumes memory

  Increasing system throughput

• DB2 system-level changes

  Installing a new DB2 version or release

  Applying maintenance to the DB2 software

  Changing DSNZPARMs

  Modifying IRLM parameters

  Modifying buffer pool sizes or parameters

  Incurring DB2 growth, causing the DB2 Catalog to grow without resizing or reorganizing

  Ensuring proper placement of the active log data sets

  Implementing data sharing

• Application-level changes

  Increasing the application workload

  Adding rows to a table

  Deleting rows from a table

  Increasing the volume of inserts , causing unclustered data or data set extents

  Increasing the volume of updates to indexed columns

  Updating variable character columns or compressed rows, possibly causing storage space to expand and additional I/O to be incurred

  Changing the distribution of data values in the table

  Updating RUNSTATS information (see Chapters 1 and 35 for more information on RUNSTATS )

  Not running RUNSTATS at all

  Rebinding application packages and plans

  Implementing or changing stored procedures or user -defined functions

  Enabling parallel processing

• Database-level changes

  Adding or removing indexes

  Changing the clustering index

  Altering a table to add a column

  Changing partitioning for a table space

  Adding or removing triggers from a table

  Reorganizing table spaces and indexes

  Compressing data

  Inserting data causing the table space to grow or to add an extent

  Moving physical data sets for table spaces or indexes to different volumes

Luckily, you can prepare yourself to deal with performance problems by understanding the dynamic nature of DB2 performance features and keeping abreast of SQL tricks of the trade. Use caution when implementing these tips and tricks, though, because the cardinal rule of relational database development always applies ”what is this cardinal rule?

NOTE

The cardinal rule of RDBMS development is "It depends!" Most DBAs and SQL experts resist giving a straight or simple answer to a general question because there is no simple and standard implementation that exists. Every situation is different, and every organization is unique in some way.

Don't be discouraged when you ask the local expert which statement will perform better, and the answer is "It depends." The expert is just doing his or her job. The secret to optimizing DB2 performance is being able to answer the follow-up question to "It depends" ”and that is "What does it depend on?"

The key to effective SQL performance tuning is to document each SQL change along with the reason for the change. Follow up by monitoring the effectiveness of every change to your SQL statements before moving them into a production environment. Over time, trends will emerge that will help to clarify which types of SQL formulations perform best.

This chapter is divided into six major sections. In the first section, you learn SQL guidelines for simple SQL statements. The second section covers guidelines for complex SQL statements such as joins, subqueries, table expressions, and unions. Common table expressions and recursion are introduced in section three; these features are new to DB2 V8 and they require some effort to master. Section four introduces the concept of nulls. Section five deals with querying date, time, and timestamp values. The sixth section provides guidelines for the efficient use of the INSERT , DELETE , and UPDATE statements.