DB2 Server for VSE & VM: Interactive SQL Guide and Reference

Improving Query Performance

There are several ways you can improve data-access performance. This section describes three methods: indexing a table, updating statistics, and locking data.

Indexing a Table

The database manager uses an index to locate particular rows of a table.

Although you create these indexes, you do not use them directly. You simply enter your query and the database manager searches for, and attempts to use, an appropriate index to locate the information. The database manager finds the information whether an index exists or not, but it may find the information faster using an index.

A good table index is one that anticipates the kinds of queries to be used for the table.

For example, if you typically look for departments in the DEPARTMENT table by department name, create an index for the DEPTNAME column by typing the following SQL statement:

   create index dptnme -
   on department -
   (deptname)

You can delete an index by using the DROP INDEX statement. For example, drop the DPTNME index by typing:

   drop index dptnme

You can create a unique index when you create a table by using the CREATE TABLE statement, or you can add a unique index to an already existing table by using the ALTER TABLE statement. For more information about the UNIQUE attribute of these statements, see the DB2 Server for VSE & VM SQL Reference manual.

A unique index is also automatically created for each primary key and dropped automatically when the primary key is dropped. The primary key is dropped either by using an ALTER TABLE statement or by dropping the table or dbspace.

You cannot create a unique index on a VARCHAR or VARGRAPHIC column that has values that differ only by trailing blanks. Trailing blanks are ignored for values with these data types. Therefore, 'Adm ' is the same as 'Adm'.

If a VARCHAR or VARGRAPHIC column is not defined as unique, the trailing blanks on values do not affect the index. The order of 'Adm ' and 'Adm' is unpredictable because they only differ in trailing blanks.

Maintaining Updated Statistics

Another performance consideration concerns data statistics that are kept in the database manager catalogs. These statistics provide information about tables such as the number of rows in a particular table, or the number of different values contained in a particular column. This information is used by the database manager to determine the best method to satisfy query requests.

It may be important to keep these statistics up to date. For example, some tables are rarely updated, and their statistics change very little over the life of the table. Other tables, however, are updated frequently, and their statistics should be updated periodically.

You can update the statistics on a table by using an SQL UPDATE STATISTICS statement. For example, update the statistics on the ACTIVITY table by typing:

   update statistics -
   for table activity

Updating statistics involves a scan of both the rows and indexes of a table. This can be time-consuming for a large table; performing the update during off-peak hours is a good idea. As a general rule, try to develop a rule of thumb for determining when to update table statistics; for example, you might decide to update a table when it has changed by 20% or more. The full description of the UPDATE STATISTICS statement is shown in the DB2 Server for VSE & VM SQL Reference manual.

Locking Data

When you update or delete data in a table, the table is considered unstable because its data is changing. The database manager protects you and other users from obtaining unreliable query results by limiting access to unstable tables. It also prevents deadlocks when several users are trying to update the same data.

The database manager isolates the data by locking it. You can control the amount of data locked from other users, as well as the length of time that the lock is applied. Controlling the amount of locked data affects system performance. For example, a small amount of locked data requires less processing time than a large amount.

If you try to access an SQL object while another user is locking it, your processing is suspended until the other user has finished with the object. If you do not want to wait indefinitely, you can type the following to stop your transaction:

   cancel

The database manager then rolls back any uncommitted work, and issues messages ARI7043I and ARI7040I. You can find more information about the CANCEL command under CANCEL.

Specifying the Isolation Level

You control locks by specifying the isolation level. The isolation-level setting affects only those tables stored in public dbspaces.

The isolation level you set is related to the task you are performing. The selection, insertion, updating, and deletion of table data are all affected by the isolation level. Specifically, the isolation level determines how soon data you have read can be changed by other users.

The isolation level has three settings: repeatable read (RR), cursor stability (CS), and uncommitted read (UR).

You can use the SET ISOLATION command only when the target AS is a local application server. Otherwise, the isolation level of CS is used and the SET ISOLATION command will have no effect.

Using the Repeatable Read Setting

Use the RR setting when you are modifying data. The RR setting ensures that data is completely isolated for your use. No other user can update the data until your work has completed.

Using the Cursor Stability Setting

Use the CS setting when you are simply querying (selecting) committed data. The term cursor in this case refers to the database manager cursor that points to the data in the table that you are using. The data involved in a CS setting is unlocked as soon as possible by the database manager for other users.

Using the Uncommitted Read Setting

Use the UR setting when you are querying (selecting) either committed or uncommitted data. With this setting, data can be read without waiting for other logical units of work that are updating the data and reading data will not prevent other application processes from updating it. However, you should remember that data integrity may be compromised with this setting and that UR should only be used when it is not necessary that the data you are reading be committed.

Using the SET Command

You use the SET command to control the isolation level. The SET command format is given on page ***, and an explanation of isolation level settings is found on page ***. See Chapter 7, Creating and Using Routines for information on how to set the isolation level from a routine.
Note: Do not forget to change the isolation level back to the default isolation level when you have completed the operation for which you changed it.

Handling Lock Contention

Use an RR setting unless lock contention is a significant problem on the application server you are accessing. If lock contention is significant, use a CS setting whenever you can. The UR setting minimizes lock contention but it should only be used when it is not necessary that the data you are reading be committed.

Determining the Isolation-Level Setting

Figure 52 and Figure 53 describe isolation level settings for various tasks. The first table discusses cursor stability, and the second, repeatable read. There is no table for the uncommitted read setting because this setting is not recommended for regular use.

Figure 52. Guidelines for Using Cursor Stability

Activity Description
Browsing a query result When you are simply displaying data, the isolation level cursor stability is sufficient. At this time, do not plan to update the table or to print a formal report.
Preparing sample reports While you are preparing a draft of a report, which you are using to check format and general content, use isolation level cursor stability.
Selecting or printing read only data Read-only data resides in tables that are subject to controlled maintenance (insert, update, or delete). That is, any maintenance is done on a known periodic basis (for example, tables that are only updated overnight).
Browsing HELP information ISQL HELP text information is read-only data, and isolation level cursor stability is sufficient.
Using SQL data definition operations Read and update access to a catalog table during the performance of data definition statements (CREATE, ACQUIRE, GRANT) is always done with an isolation level setting of repeatable read. You do not have to set the isolation level to protect your definitions.
Using EXPLAIN Using the EXPLAIN statement to access a catalog table is always performed with isolation level repeatable read, regardless of your isolation level setting.
Accessing data in private dbspaces Accessing private dbspaces is effectively isolation level repeatable read, because locking is only performed at the dbspace level. You do not need to adjust your setting for isolation level.
Accessing data in public dbspaces with dbspace level locking For public dbspaces with dbspace locking, access is always effectively isolation level repeatable read, because locking is only performed at the dbspace level. You need not change your isolation level setting.
Working with stored queries Stored queries are always stored and recalled with an isolation level repeatable read.
You control the isolation level used for starting a stored query. Therefore, you must set the isolation level to the desired value before starting the stored query.
Invoking routines Retrieving from a routine is performed with an isolation level repeatable read.
You control the isolation level used for running a routine. You can change the isolation level setting before running the routine or within the routine (as many times as needed).
Using ISQL commands For ISQL commands that access the application server, the isolation level used is repeatable read.
For the ISQL commands RUN, START, and HELP, you can control the setting of the isolation level.
Using the INSERT statement and the INPUT command Using INSERT statements with values and INPUT commands are not affected by the isolation level setting because no data is read from the application server.
Using INSERT statements with subselect are affected by the isolation level setting because of the SELECT clause. You must follow the guidelines for selecting data given above when you use INSERT through subselect statements.

Activity	Description
Browsing a query result	When you are simply displaying data, the isolation level cursor stability is sufficient. At this time, do not plan to update the table or to print a formal report.
Preparing sample reports	While you are preparing a draft of a report, which you are using to check format and general content, use isolation level cursor stability.
Selecting or printing read only data	Read-only data resides in tables that are subject to controlled maintenance (insert, update, or delete). That is, any maintenance is done on a known periodic basis (for example, tables that are only updated overnight).
Browsing HELP information	ISQL HELP text information is read-only data, and isolation level cursor stability is sufficient.
Using SQL data definition operations	Read and update access to a catalog table during the performance of data definition statements (CREATE, ACQUIRE, GRANT) is always done with an isolation level setting of repeatable read. You do not have to set the isolation level to protect your definitions.
Using EXPLAIN	Using the EXPLAIN statement to access a catalog table is always performed with isolation level repeatable read, regardless of your isolation level setting.
Accessing data in private dbspaces	Accessing private dbspaces is effectively isolation level repeatable read, because locking is only performed at the dbspace level. You do not need to adjust your setting for isolation level.
Accessing data in public dbspaces with dbspace level locking	For public dbspaces with dbspace locking, access is always effectively isolation level repeatable read, because locking is only performed at the dbspace level. You need not change your isolation level setting.
Working with stored queries	Stored queries are always stored and recalled with an isolation level repeatable read. You control the isolation level used for starting a stored query. Therefore, you must set the isolation level to the desired value before starting the stored query.
Invoking routines	Retrieving from a routine is performed with an isolation level repeatable read. You control the isolation level used for running a routine. You can change the isolation level setting before running the routine or within the routine (as many times as needed).
Using ISQL commands	For ISQL commands that access the application server, the isolation level used is repeatable read. For the ISQL commands RUN, START, and HELP, you can control the setting of the isolation level.
Using the INSERT statement and the INPUT command	Using INSERT statements with values and INPUT commands are not affected by the isolation level setting because no data is read from the application server. Using INSERT statements with subselect are affected by the isolation level setting because of the SELECT clause. You must follow the guidelines for selecting data given above when you use INSERT through subselect statements.

Figure 53. Guidelines for Using Repeatable Read

Activity Description
Using DELETE, INSERT, or UPDATE from a display If you type a DELETE, INSERT, or UPDATE statement based on a SELECT statement result, do not set the isolation level to repeatable read before you type the SELECT statement. It is then impossible for the displayed data to change before you have typed the DELETE, INSERT, or UPDATE statement.
Note: In addition, set AUTOCOMMIT OFF so the SELECT and data change statements are contained in the same LUW.

You need not set AUTOCOMMIT OFF and isolation level repeatable read if any of the following is true:

Data selected is read-only.
You are the only person authorized to modify the selected data.
You have other ways of ensuring the data selected does not change.
A command that changes the contents of the table is valid even if the selected data does change.

Using DELETE or UPDATE statements Use an isolation level repeatable read when you delete or update data unless:

You are the only person authorized to modify the data.
You have other ways of ensuring the data selected does not change.

Preparing formal reports To prevent data from changing, set the isolation level to repeatable read when you prepare a formal report unless:

Data selected is read-only.
You are the only person authorized to modify the selected data.
You have other ways of ensuring the data selected does not change.

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]