kfogarty.com - Database Consultants

Here are some general rules of thumb that can be used in a DB2 environment:

• Never use SELECT *:
  As a general rule, a query should never ask DB2 for anything more than is required to satisfy the desired task. For maximum flexibility and efficiency, each query should access only the columns needed for the function that will be performed.

• Singleton SELECT versus the cursor:
  To return a single row, an application program can use a cursor or a singleton SELECT. A cursor requires an OPEN, FETCH, and CLOSE to retrieve one row, whereas a singleton SELECT requires only SELECT… INTO. Usually, the singleton SELECT outperforms the cursor.

  When the selected row must be updated after it is retrieved, however, using a cursor with the FOR UPDATE OF clause is recommended over a singleton SELECT. The FOR UPDATE OF clause ensures the integrity of the data in the row because it causes DB2 to hold an exclusive lock on the page containing the row to be updated. The singleton select provides no such locking.

• Use FOR FETCH ONLY:
  When a SELECT statement will be used only for retrieval, use the FOR FETCH ONLY clause.
• Avoid using DISTINCT:

  The DISTINCT verb removes duplicate rows form an answer set. If duplicates will not cause a problem, do not code distinct, because it adds overhead by invoking a sort to remove the duplicates.

• Limit the data selected:
  Return the minimum number of columns and rows needed by your application program by making efficient use of the WHERE (SQL predicate) clause. It is almost always more efficient to allow DB2 to use the WHERE clause to limit the data returned.
• Code predicates on indexed columns:

  DB2 usually performs more efficiently when it can satisfy a request using an existing index rather than no index. Design all SQL statements to take advantage of indexes.

• Multicolumn indexes:
  If a table has only multicolumn indexes, try to specify the high-level column in the WHERE clause of your query. This results in an index scan with at least one matching column.
• Use ORDER BY only when sequence is important:
  Code the ORDER BY clause when the sequence of rows being returned is important. Order only those columns that are absolutely necessary in order to improve efficiency.

  Use equivalent data types: Use the same data types and lengths when comparing column values to host variables or literals. This eliminates the need for data conversion and allows for the use of an index. For example, comparing a column defined as CHAR(6) to a field which is CHAR(4) or CHAR(7) will cause data conversion and should be avoided at all costs. The easiest way to ensure datatype consistency is to use the DCLGEN fields whenever possible.

• Use BETWEEN rather than <= and >= :
  BETWEEN allows the optimizer to select a more efficient access path.
• Use IN instead of LIKE:
  If you know that only a certain number of occurrences exist, using IN with the specific list is more efficient than using LIKE. The functionality of LIKE can be imitated using a range of values. For example, if you want to retrieve all employees with a last name starting with “M,” use BETWEEN ‘maaaaaaaaaaaaaaa’ and ‘mzzzzzzzzzzzzzzz’ instead of LIKE ‘m%’
• Avoid using NOT (except with EXISTS):
  Not should only be used as an alternative to very complex predicates.