DB2 Server for VSE. Operations Guide / Handbooks (2004-2007)

You can determine whether unexpected delays are caused by locking; monitor how

the database manager is using lock request blocks; and determine the lock request

blocks required to preprocess a single application.

One of the values displayed by SHOW LOCK MATRIX is called MAX USED BY

LUW. It is the maximum number of lock request blocks used by any one

application during a logical unit of work. (When any LUW exceeds NLRBU and

the escalation process occurs, MAX USED BY LUW is set to zero.)

In addition you can look for SQLCODE -912 (SQLSTATE 57028), or SQLCODE -915

(SQLSTATE 57029). These indicate rollbacks that occur because of, insufficient lock

request blocks for the database manager, or insufficient lock request blocks for a

user application, respectively.

Deadlock

The database manager performs deadlock detection prior to placing any agent into

a lock wait. A deadlock occurs when agent A holds resource X and agent B wants

resource X while holding resource Y, which agent A wants. There is an impasse,

which the system removes by rolling back the youngest LUW. For example,

consider two users, LAWRENCE and VERONICA:

1. LAWRENCE selects rows from the EMPLOYEE table, placing a SHARE (S) lock

on the table.

2. VERONICA also selects from the same table, also placing a SHARE (S) lock on

it.

3. LAWRENCE tries to UPDATE the employee table, but cannot because he is

placed in a lock wait. The EXCLUSIVE (X) lock he needs before he can update

the table is incompatible with VERONICA’s SHARE (S) lock.

4. VERONICA also tries to UPDATE the same table, but cannot. The EXCLUSIVE

(X) lock she needs before she can update the table is incompatible with

LAWRENCE’s SHARE (S) lock. However, before she is placed in a lock wait,

the database manager detects a potential deadlock.

5. The database manager rolls back VERONICA’s logical unit of work because it

is younger than LAWRENCE’s LUW.

6. LAWRENCE receives the lock he needs because VERONICA loses her SHARE

(S) lock when her LUW ends, and VERONICA receives the following message:

UPDATE SQLDBA.EMPLOYEE SET SALARY=60000 WHERE LASTNAME=’HAAS’

ARI7955I THE SYSTEM ENDED YOUR QUERY RESULT TO PROCESS YOUR COMMAND.

ARI0503E AN SQL ERROR HAS OCCURRED.

THE CURRENT LOGICAL UNIT OF WORK HAS BEEN

ROLLED BACK DUE TO A DEADLOCK. IT WAS WAITING

FOR A PAGE LOCK IN DBSPACE = 17

HELD BY USER LAWRENCE.

ARI0505I SQLCODE = -911 ROWCOUNT = 0

ARI0504I SQLERRP: ARIXRSS SQLERRD1: -110 SQLERRD2: -99

ARI0502I FOLLOWING SQL WARNING CONDITIONS ENCOUNTERED:

NULLWHERE NOLUW

ARI7021E THE APPLICATION SERVER HAS ISSUED A ROLLBACK

STATEMENT. ALL WORK ENTERED FOR PROCESSING SINCE

THE LAST COMMIT STATEMENT WAS ROLLED BACK.

YOU MAY HAVE TO REENTER SOME STATEMENTS.

While the database manager does not allow deadlocks to occur, the more potential

deadlock situations that you create the more resources are required to avoid them.

Chapter 4. Configuring the Application Server and Requester

101

Note: The time required to detect potential deadlocks increases exponentially

(power of two) with the number of real agent structures in your database

manager. For example, it takes 100 times longer to process deadlocks when

NCUSERS=20 than it does when NCUSERS=2.

Tuning Parameters

Application Design: Look for two applications that access the same data in the

opposite order. If you can, switch the order of access for one application so they

both use the same order.

Reschedule Applications: If you find that two applications often create deadlocks,

try to reschedule them to run at different times of the day.

Reduce Lock Contention: The other way to reduce potential deadlocks is to simply

reduce the number and duration of locks that your database manager needs to use,

refer to “Locking Contention” on page 94.

Reduce Lock Escalation: Escalation can also cause deadlocks. For example,

suppose two users are updating tables in a dbspace. When the lock size is

escalated to a dbspace level, both users can be locked out, with each waiting for

the other to complete a logical unit of work. Refer to “Lock Escalation” on page 99.

Performance Indicator

To determine if deadlocks are a problem, look for users receiving SQLCODE -911

(SQLSTATE 40001, rollback due to deadlock).

Note: This message may also be received during preprocessing, as the locks are

required then as well.

To test the frequency of deadlocks, use the COUNTER operator command and

specify the DEADLOCK counter. It displays the number of deadlocks detected,

each of which causes a rollback.

Recovery

Logical Units of Work

When a user or an application program has made a change or a group of related

changes to the database, and if the application in question completed successfully,

the user or program issues an SQL COMMIT WORK statement to the application

server, to commit these changes to the database. If the application did not complete

successfully, the user instead issues an SQL ROLLBACK WORK statement, which

undoes all the changes made up to the point of the error since the last COMMIT

WORK statement, or since the start of the program or session.

A group of SQL statements is called a logical unit of work (LUW). An LUW can be

as small as one statement, or as large as an entire application execution (or ISQL

session). All SQL statements are executed within an LUW. If no LUW exists when

a statement is issued, then the database manager creates one implicitly.

CMS Work Units (VM)

Users working on a VM operating system can take advantage of CMS work units,

which allow them to maintain more than one logical unit of work (LUW) at a time.

With separate CMS work units, application programs can be independent of one

another. For example, a user can run a program, and in the middle of an LUW,

102

Performance Tuning Handbook

have that program call a second program which runs in a separate CMS work unit.

When work is committed in the second program, it does not affect the active LUW

in the first program.

Note: CMS work units require extra processing overhead, so should only be used

when necessary. If an application does not need this support, set the

WORKUNIT option of the SQLINIT command to NO.

Checkpoints

A checkpoint is an internal operation where the database manager writes modified

data and status information to DASD, and writes a summary status record to the

log.

What occurs during the Checkpoint Process?

When the database manager takes a checkpoint:

v It writes the contents of the local and directory buffer pools to DASD.

v It frees all shadow pages. (Whenever it “modifies” a page in a storage pool, it

creates a new page in the same pool, and keeps the original as a shadow page.

Refer to “Shadow Pages” on page 63).

v If LOGMODE=Y (no archive), the database manager clears space in the log up to

the beginning of the oldest LUW still active when the checkpoint is taken.

v It updates the directory pages to account for released shadow pages and

updated page allocation maps.

When do Checkpoints Occur?

A checkpoint is scheduled when:

v The number of log pages specified by the CHKINTVL initialization parameter

have been written to the log, refer to “Choosing the Checkpoint Interval” on

page 104.

v During rollback, the total number of free pages in a storage pool is less than or

equal to 10. (This does not apply when LOGMODE= N.)

v A COMMIT WORK is processed in single user mode with no logging

(LOGMODE=N)

v The percentage of free pages in a storage pool reaches the minimum specified by

the SOSLEVEL initialization parameter, refer to “Short on Storage Cushion” on

page 59. (This does not apply when LOGMODE=N.)

v A DROP DBSPACE is issued.

v Soft recovery processing is complete during startup.

v An archive (both before and after) is performed.

v Before switching to an inactive log and after the switch has completed

successfully. (This only applies when alternate logging is enabled.)

v A shutdown is issued, either in multiple user mode (MUM) or single user mode

(SUM).

v A log-full condition occurs, refer to “Log Cushion and Automatically Initiated

Archives” on page 107.

v An LUW that updates data in a nonrecoverable storage pool ends.

Performance Implications

A checkpoint has two performance implications:

v It performs a high amount of I/O to DASD. It writes all the modified buffer

pages and data space pages back to DASD, and updates the directory disk.

v It holds up processing. User agents must wait until the checkpoint is finished

before they can proceed.

Chapter 4. Configuring the Application Server and Requester

103

Choosing the Checkpoint Interval

To control the duration between checkpoints, use the CHKINTVL initialization

parameter. This parameter specifies how many log pages the database manager

will fill before it takes its next checkpoint.

Setting the Time Between Checkpoints: The time between checkpoints depends on

the number of modifications you make to the database. If logging is turned on, the

database manager writes to the log every time you perform an insert, update, or

delete. The more modifications you make, the faster you will reach a checkpoint. If

you only perform queries, the database manager may never perform a checkpoint.

We recommend that you adjust the CHKINTVL parameter so that the database

manager takes a checkpoint every 10 to 15 minutes. Should you experience a

system failure, it should take you no longer than 10 to 15 minutes to restart the

database manager once you have recovered your system. If you adjust CHKINTVL

so that checkpoints occur less frequently, for example every four hours, it may take

up to or more than four hours to restart your database.

Many installations find that the optimum CHKINTVL setting is between 50 and

300. Installations with large, randomly modified databases are in the lower end of

that range, and installations with small databases tend to be in the upper end of

that range. Large databases having a relatively low frequency of random

modifications also tend to be in the upper end of that range.

If you set the CHKINTVL parameter too low, you minimize the risk of filling the

log or storage pools. However, while each checkpoint is faster, you increase the

overall number of checkpoints.

If you set it too high, you lower the overhead associated with checkpoint

processing. However, consider the following adverse affects:

v It may take longer to recover from a system failure.

v You risk filling the log and storage pools if you are running with LOGMODE=Y.

This consideration does not apply if you are doing archiving (LOGMODE=A or

L), because in that situation log space is reclaimed only when the database or

log is archived.

v You may see an increase in the time required to complete a single checkpoint.

However, unless your standard workload includes a significant amount of

random data modifications over a relatively large area (more than 100MB), you

probably will not notice significant delays. The effect is unimportant if the

database is small or if there is very little random data modification activity. Bulk

sequential data modifications also do not generally cause problems.

v You will probably require more storage to support additional shadow pages.

Whenever the database manager modifies a page in a storage pool, it creates a

new page in the same pool, and keeps the original as a shadow page. Therefore

the longer the period between checkpoints, the more modified pages will

accumulate in your storage pools. 25% free storage in each storage pool is

generally sufficient, refer to “Short on Storage Cushion” on page 59.

Forcing Checkpoints: You can avoid checkpoint processing during peak periods by

manually forcing them to occur when required. For example, consider a bank that

processes a large number of transactions when its customers are on break for

lunch, between 11:00AM and 1:00PM. A checkpoint could lock tellers out of the

database for several minutes frustrating both the tellers and the bank’s customers.

104

Performance Tuning Handbook

To avoid a checkpoint, the checkpoint interval is set very high. Just before the

lunch rush, an empty dbspace, created for the purpose, is dropped to force a

checkpoint. After the rush, the dbspace is recreated and dropped again to force

another checkpoint and ensure that the lunch time work is saved to DASD.

If you plan to use this method to control when checkpoints occur, create a plan

that specifically indicates when each is to occur and make sure that it is followed.

Not performing any checkpoints can cause more performance problems in the long

run than you will avoid in the short run.

DB2 VM Data Spaces Support: The DB2 Server DSS feature can make checkpoint

processing faster by limiting the number of modified pages in main and expanded

storage. When the number of modified pages in a data space exceeds an

initialization parameter called the save interval (SAVEINTV), the database manager

directs the operating system to save all the modified pages in that data space to

DASD. Unlike the save that occurs during checkpoint, the database manager can

continue to service users while this is being done. For more information on the

save interval, refer to Chapter 6, “Data Spaces Support for VM/ESA,” on page 157.

Logging and Archiving

Log

A log is a file maintained on DASD that records all the changes to the database.

Each time a DML statement (for example, INSERT, DELETE, UPDATE) is

processed by the database manager, the old and new values are written in the log.

If any changes to the database must be undone or redone, you can use the log to

restore the data to its proper state.

Archive

An archive is a copy of data in your database at the time the archive was made.

You can archive an entire database, a portion of the database, or even the log.

Typically, you use archives to recover from a DASD failure.

You can create three different archives:

Selective Archive

Is a copy of individual tables or even dbspaces. You can create a selective

archive using either the DBS utility or IBM DB2 for VM Control Center

(refer to page 9).

Database Archive

Is a copy of the entire database at a specific time. A database archive

includes the database directory and all dbextents, but excludes the log.

Log Archive

Is a copy of the current log on either tape or disk.

Note: By using the DB2 for VSE & VM Data Restore Feature, you can also

create incremental archives and restore individual storage pools.

Alternate Logging

A database must have at least one log. Optionally, you can define a second log to

be used once the first log is full. This can prevent log archives from occurring at

unscheduled times.

For information on the alternate logging option, see the DB2 Server for VM System

Administration or the DB2 Server for VSE System Administration manuals.

Chapter 4. Configuring the Application Server and Requester

105

Dual Logs

Dual logging protects the database in case of a DASD failure on the log. With

single logging, any I/O error on the log minidisk causes the database manager to

end. With dual logging, database updates are recorded in the active log and its

dual copy. If alternate logging is enabled, a dual copy of the alternate log is also

maintained. This reduces the risk of losing the log, as an unrecoverable error is

unlikely to occur on both logs at the same time.

Note: To ensure that you really have true dual log protection, each log file or

minidisk must reside on a separate DASD volume. For information on the

dual logging option, see the DB2 Server for VM System Administration or the

DB2 Server for VSE System Administration manuals.

Choosing a Logmode

You can choose from four different log mode values:

LOGMODE=Y

A log records all changes to the database. These are stored in the log until

a checkpoint saves the changes to DASD. It is totally your responsibility to

schedule archives of the database, because the database manager never

initiates any for you. If the application server or your operating system

abends, you can recover up to and including the last complete LUW. If you

suffer a database DASD failure, you can recover from your last archive.

This value is the default.

LOGMODE=A

A log records all changes to the database. These are stored in the log until

a database archive occurs. This ensures that you can recover from either an

abend or a DASD failure up to and including the last complete LUW. The

database manager automatically initiates a database archive when the log

is nearly full. While you can wait for this to occur, it is more efficient to

perform regular archives yourself, refer to “Log Cushion and

Automatically Initiated Archives” on page 107.

LOGMODE=L

A log records all changes to the database. These are stored in the log until

a log archive occurs. This lets you recover using the last database archive

plus subsequent log archives. You can recover up to and including the last

complete LUW.

Note: Before you can use LOGMODE=L, you must create a database

archive.

If alternate logging is not enabled, the database manager automatically

initiates a log archive when it is nearly full. When alternate logging is

enabled, an attempt is made to switch to the inactive log. While you can

wait for a log archive or alternate log switch to occur, it is more efficient to

perform regular archives yourself, refer to “Log Cushion and

Automatically Initiated Archives” on page 107.

LOGMODE=N

Indicates that nothing is recorded in the log. This option is not

recommended for normal operation and it is only available in single user

mode.

When you choose a log mode, decide how much protection you want, and the

amount of time you can spend in recovering data.

106

Performance Tuning Handbook

If you are running in single user mode (SUM) and you do not need to protect

your data from either system or DASD failures, specify LOGMODE=N. The

application server will run faster because it will not require the extra time to create

archives or maintain a log.

If you do not need to protect your data from DASD failures, specify

LOGMODE=Y. The application server will run faster because it will not require

the extra time to create archives and you can maintain a smaller log.

LOGMODE=Y cannot protect you from DASD failures because the contents of the

log are only saved until the next checkpoint. After the checkpoint, the current

contents of the log can be overwritten by new changes. If several checkpoints have

occurred since your last database archive, you cannot use the contents of the log to

recover.

If you must have the ability to recover from DASD failures, choose either mode

A or L. With LOGMODE=A an archive of the entire database is created

periodically, so you can restore your entire database or individual storage pools by

using the latest database archive along with the contents of the current log. With

LOGMODE=L, archives are also taken but you can create archives of the database

less frequently than with mode A, because you have log archives as well. If a

DASD failure occurs, you can restore the entire database or individual storage

pools by using the latest database archive, the sequence of log archives that follow

it, and the contents of the current log. If you are doing a log archive for the first

time you will be prompted to do a database archive first. You will not be

prompted again. You must schedule any subsequent archives yourself.

To decide between LOGMODE=A or L, consider:

v How important it is to recover quickly after a DASD failure. You recover more

quickly with LOGMODE=A.

v How much time you can devote to taking archives. Because the log is usually

smaller than the database, log archives require less time to create than database

archives. You can create both archives when you stop the application server, or

while users are still accessing data in the database. If you create a database

archive when users are accessing data in the database, they must wait longer for

the application server to process their requests.

When you choose a log mode, use it whenever you start the system. Do not

change the log mode without thought and planning. If you must do so, you may

have to carry out additional procedures. For more information, see the DB2 Server

for VM System Administration or the DB2 Server for VSE System Administration

manuals.

Tuning Parameters

Log Cushion and Automatically Initiated Archives: The SLOGCUSH

initialization parameter defines when automatically initiated log-full processing

begins. It is expressed in terms of a percentage of the log. When the log fills to the

SLOGCUSH value, the database manager aborts the oldest active logical units of

work until enough log space is freed to bring the percentage of the log below the

SLOGCUSH level.

The ARCHPCT initialization parameter defines when automatically initiated

archives will occur or, when alternate logging is enabled, when we attempt to

switch to the inactive log disk. It is also expressed in terms of a percentage of the

log. When alternate logging is not enabled and the log fills to the ARCHPCT value,

Chapter 4. Configuring the Application Server and Requester

107

the database manager forces either a log or database archive depending on

whether it is running in LOGMODE A or L.

Ideally, you should never reach SLOGCUSH or ARCHPCT. Log-full processing and

automatically initiated archives or switching to the inactive log reduce

performance, and often occur during peak workloads, so avoid them by:

v Ensuring that your log is large enough.

v Trying to maintain enough free log space through regular log or database

archives (LOGMODE=A or L) and through regular checkpoints (LOGMODE=Y).

Note: Checkpoints only free space in the log when you run your application

server with LOGMODE=Y.

v COMMITing WORK frequently to avoid long running LUWs.

v Running very long LUWs in single user mode without logging (LOGMODE=N).

Since performing an archive impacts performance less than log-full processing

avoid the latter by:

v Setting SLOGCUSH > ARCHPCT.

v Ensure that there is a comfortable difference between SLOGCUSH and

ARCHPCT. (If the SLOGCUSH percentage is reached during an online archive

operation, all SQL processing is suspended until the archive operation is

complete.)

v Setting SLOGCUSH high enough to avoid log—full processing but not too high

to risk completely filling the log.

v Enable archiving (LOGMODE=A or L). SLOGCUSH has no effect if archiving is

disabled.

Using VSAM Buffers (VSE): In VSE, you can use the BUFND startup parameter

for the directory, data, and log disks. By using VSAM-controlled buffers and

sequential processing, VSAM is able to read multiple records with a single I/O

request. The number of records read together will depend on the number of

buffers available to VSAM, which are specified when the Access Method Control

Block (ACB) is created for the directory, data, and log disks. A second set of ACBs

is required to avoid interfering with normal I/O processing during an online

archival.

The default number of ACB buffers in DB2 Server for VSE is 40. To change this

value, use the BUFND parameter in the DLBL statement of the JCL for the

directory, data, and log disks.

Performance Indicator (SHOW LOG)

You can use the SHOW LOG operator command to determine if archiving has

been enabled and what percentage of the active log is full. Also, if archiving is

enabled, it displays the percentage of the log remaining before ARCHPCT is

reached. If archiving is disabled, it displays the percentage of the log remaining

before SLOGCUSH is reached.

Communications

DRDA Performance Considerations (VM)

This section discusses how to use the PROTOCOL parameter and different block

sizes in a DRDA protocol environment to obtain maximum performance from the

108

Performance Tuning Handbook

database manager. For information on setting up this type of environment, refer to

the DB2 Server for VM System Administrationor the DB2 Server for VSE System

Administration manuals.

PROTOCOL Performance Considerations

The PROTOCOL parameter specifies the types of protocols that the application

server can process and the types of protocol under which the application requester

runs.

On the application server, the PROTOCOL parameter is specified in the SQLSTART

EXEC. The PROTOCOL parameter has two options on the application server,

SQLDS and AUTO. When PROTOCOL=SQLDS is specified, the DB2 Server for VM

application server allows access from DB2 Server for VM application requesters

only. (The application requesters and application servers can be in either a local or

remote environment.) This is the default option. When PROTOCOL=AUTO is

specified, the DB2 Server for VM application server allows access from DB2 Server

for VM application requesters and non-DB2 Server for VM application requesters.

On the application requester, the PROTOCOL parameter is specified in the

SQLINIT EXEC. The PROTOCOL parameter has three options on the application

requester, SQLDS, AUTO and DRDA. When PROTOCOL(SQLDS) is specified, the

DB2 Server for VM application requester cannot connect to a non-DB2 Server for

VM application server. This is the default option. When PROTOCOL(AUTO) or

PROTOCOL(DRDA) is specified, the DB2 Server for VM application requester can

connect to DB2 Server for VM application servers and non-DB2 Server for VM

application servers.

When a connection is made between the application requester and the application

server, the combination specified by these parameters determines the protocol to be

used (either SQLDS protocol or DRDA protocol).

Table 5 shows the protocol used between the application requester and the

application server.

Table 5. Protocol Used Between the application requester and the application server

Application Server

Non-DB2 Server

DB2 Server for VM (SQLSTART)

Application Requester

for VM

(including DB2

SQLDS

AUTO

Server for VSE)

SQLDS

Not Allowed

DB2 Server for

AUTO

SQLDS

DRDA

VM (SQLINIT)

DRDA

Not Allowed

DRDA

Non-DB2 Server for VM

Not Allowed

DRDA

Not Applicable

When the DB2 Server for VM application server is started with

PROTOCOL=AUTO, DRDA “handshaking” occurs (unless the application

requester is a DB2 Server for VM application requester that has been initialized

with PROTOCOL(SQLDS)). Handshaking is an identification exchange between the

application server and the application requester. During this handshaking

sequence, information is exchanged between the application requester and the

application server. This exchange includes CCSID information and generation of an

LU 6.2 LUWID.

Chapter 4. Configuring the Application Server and Requester

109

For more information on handshaking, see the discussion on accessing a remote

relational database manager in the Distributed Relational Database Architecture

Reference manual.

The PROTOCOL parameters used also affect CCSID conversion. If either the

application requester or application server specifies SQLDS for the PROTOCOL

parameter, the application requester default CCSIDs are ignored, and the

application server CCSIDs are assumed.

Application requester CCSIDs are used when:

v Both the application server and the application requester specify the AUTO

option, or

v The application server is started with PROTOCOL=AUTO option, and the

application requester specifies PROTOCOL(DRDA) on the SQLINIT EXEC.

When communication is between a DB2 Server for VM application server and a

DB2 Server for VM application requester, the AUTO option yields the same

performance advantages as the SQLDS option except that it has a slight overhead

when establishing a connection with the application server. Specifying the AUTO

option on the DB2 Server for VM application server has many advantages. This

option allows the application server to receive both SQLDS protocol or DRDA

protocol, from both DB2 Server for VM application requesters and non-DB2 Server

for VM application requesters. If you specify the AUTO option on the DB2 Server

for VM application requester, it makes the necessary adjustments for both DB2

Server for VM application servers and non-DB2 Server for VM application servers.

When PROTOCOL(DRDA) is specified on the application requester, DRDA

protocol is forced for connections, even if the target is a DB2 Server for VM

application server. The DRDA option is useful when you are doing prototype

testing between a DB2 Server for VM application requester and a DB2 Server for

VM application server to model problems that may occur in communications with

a non-DB2 Server for VM application server. You can also use the DRDA option to

test SQL extensions only available in a DRDA protocol environment, for example, a

larger block size.

Fetch and Insert Blocking

The database manager lets you use blocking for row insertion and row retrieval.

Blocking improves performance in multiple user mode because data is sent

between your program and the database manager in blocks of rows (rather than one

row at a time). This reduces overhead from communications between the

application server and the requester. Most applications that do multiple-row

insertions or retrievals would benefit from blocking.

Implementing Blocking

To use blocking, specify the BLOCK, SBLOCK or IBLOCK parameter when

preprocessing the program. (For extended dynamic statements, specify the BLOCK

parameter on the CREATE PROGRAM statement.) When you run the program,

blocking is automatically used for:

v Insert cursors (those that use OPEN, PUT, and CLOSE statements)

v Fetch cursors (those that use OPEN, FETCH, and CLOSE statements)

v Select cursors (those that with FOR FETCH ONLY or FOR READ ONLY clause

when SBLOCK is in effect)

When you run the program with IBLOCK, blocking is automatically used for:

110

Performance Tuning Handbook

v Normal Insert statements

It is unnecessary for programs to explicitly handle the blocks because they are

managed by the database manager. With the SQLDS protocol, 8KB blocks are used

for both fetch and insert blocking. The IBLOCK option is applicable with the

DRDA protocol. The block size for fetch blocking can be set by the one who

requests the application from 1KB to 32KB by using the QRYBLKSIZE option of

the SQLINIT EXEC (see the DB2 Server for VSE & VM Database Administration

manual).

For VSE Users

In VSE the block size for fetch blocking can be set by the application

requester from a minimum value of 512 bytes to a maximum value of

32KB - 1 byte (32767).

Note: Blocking is only useful if the block size is sufficiently large that many rows

can be blocked (that is, it must be greater than the maximum row length).

For retrievals, as many rows as the block will hold are sent to the application

requester on the first fetch (with the DRDA protocol, the first block is sent with the

OPEN statement). When the program fetches all the rows in the block, the next

fetch that it issues causes another block to be sent. The program never needs to

explicitly request a block.

For insertions, the blocks are also handled automatically. Whenever the program

issues a PUT or a normal INSERT, a row is added to the block. When another row

cannot fit into the block, the resource adapter sends the block to the database

manager.

Suppressed Blocking

Single User Mode: The database manager does not do blocking for single user

mode applications. Since the database manager and the application run in the

same partition (for VSE) or machine (for VM), there is no cross-partition/machine

communication overhead to be saved. Programs that have been preprocessed using

the BLOCK or IBLOCK parameter do not need to be re-preprocessed to run in

single user mode. There is an automatic suppression of the blocking and no

warning is sent to the program at run time. Some programs, however, process SQL

statements dynamically at run time by using the PREPARE statement. These

programs, when preprocessed with the BLOCK option, will receive a runtime

warning, if a dynamically processed statement is disqualified for blocking.

Multiple User Mode: In some instances, there is also suppressed blocking in

multiple user mode. Suppressed blocking for a cursor occurs when:

v There is not enough virtual storage to get one block.

v Two rows cannot fit into one block.

v The cursor retrieves long fields (LONG VARCHAR or LONG VARGRAPHIC or

VARCHAR(n) or VARGRAPHIC(n) where n is greater than 254, or 127,

respectively).

v The cursor contains a FOR UPDATE clause.

v The cursor is operated on by a DELETE ... WHERE CURRENT OF CURSOR

statement.

Chapter 4. Configuring the Application Server and Requester

111

v The cursor is operated on by an UPDATE ... WHERE CURRENT OF CURSOR

statement.

In all cases, a warning is sent to the program, in the SQLCA, to let it know that

blocking was suppressed, and execution continues. Notice that the database

manager suppresses blocking on a cursor level. It may be doing blocking for some

cursors in a program even though the blocking for other cursors is suppressed.

The advantages of fetch and insert blocking are not limited to user programs. DB2

Server for VSE & VM facilities take advantage of blocking as well. The DBS utility

and ISQL take advantage of blocking. Refer to the DB2 Server for VSE & VM

Database Administration, and the DB2 Server for VSE & VM Interactive SQL Guide and

Reference manuals. (ISQL use of blocking is limited to fetch blocking.)

One minor performance disadvantage to using blocking is that the database

manger uses extra virtual storage (equal to the block size) for every open cursor.

The storage is freed when the user closes the cursor or when the user ends the

logical unit of work (whichever comes first). This not only applies to user

applications, but to the DBS utility. (ISQL only has one cursor open at a time

because a user can only issue one SELECT statement at a time.)

Another minor performance disadvantage is that in not using a block worth of

data, you pay the overhead of that block. For example, only 10 rows are returned

in a block capable of holding 200 rows.

For more information on using fetch or put operations in programs with blocking,

refer to the DB2 Server for VSE & VM SQL Reference and DB2 Server for VSE & VM

Application Programming manuals.

Synchronous Communications (VM)

The SYNCHRONOUS parameter of SQLINIT EXEC determines whether

synchronous or asynchronous communication is used between the user and

database machines. Synchronous communication performs better than

asynchronous communication but has the following restrictions:

v You cannot use SQLHX or CANCEL to cancel SQL statements. The only ways to

terminate an unwanted LUW is to use the operator command FORCE, or to

re-IPL CMS.

v You cannot use the SQLQRY command to query the status of the application

that you are currently running on the DB2 Server for VSE & VM user machine.

We recommend that you use synchronous communication primarily when running

a well-tested production batch application against local application servers. Always

use the default, asynchronous communication, with interactive programs such as

ISQL.

Considerations for ISQL and Adhoc Queries

Adhoc queries or the Interactive SQL facility (ISQL) can significantly affect how

your database system performs.

112

Performance Tuning Handbook

AUTOCOMMIT

You should do all ISQL work in AUTOCOMMIT ON mode, the default. In

AUTOCOMMIT ON mode, ISQL internally issues a COMMIT WORK, thus freeing

DB2 Server for VSE & VM resources between query requests for possible use by

others.

The COMMIT WORK is done immediately after a statement completes

successfully. The only exception is for INSERT, DELETE, or UPDATE statements

that change more than one row. For these statements, ISQL will give you a chance

to rollback.

Use AUTOCOMMIT OFF mode, if you must have explicit control over committing

work. For example, if two SQL statements update data in two tables

simultaneously (as with debit and credit operations) and these updates must be

synchronized to prevent inconsistent data, use AUTOCOMMIT OFF mode. If you

are using this mode, package SQL statements into an ISQL routine so that terminal

read delays are minimized or eliminated.

You can cancel any SQL statement if it is still in progress by issuing the ISQL

CANCEL statement. (This statement causes a ROLLBACK WORK RELEASE to be

executed.) That is, you can enter CANCEL if you are prompted to clear the screen,

or prompted to enter CANCEL. You can also enter CANCEL to any ISQL message

requesting a reply.

Isolation Levels

To minimize contention on shared resources, do all adhoc query work with the

isolation level set to cursor stability (CS) unless the work being performed requires

the repeatable read (RR) isolation level to ensure consistent data.

Alternatively, if it is not important that the data you are reading has necessarily

been committed, consider setting the isolation level to uncommitted read (UR).

Adhoc users should be aware that when they are viewing a query result they may

be delaying other users especially if querying the catalog tables.

Temporary Tables

If a long series of adhoc queries is expected against certain data in a large

database, it may be best to copy that data into one or more temporary tables and

query the copy. The queries will run faster and indexes can be created without

being concerned with the effect of additional index maintenance on production

work that is updating the data.

If a series of adhoc queries is expected against data in several related tables,

consider creating a temporary table that contains the joined results of those tables.

Queries run against this temporary table will run faster and be easier to formulate.

An added benefit is that the temporary table can be created in a PRIVATE

DBSPACE where locking overhead during query execution is negligible. The

INSERT using subselect form of the SQL INSERT statement can be used to create

the copy.

Views

Instead of giving end users access to an entire table, provide them with a view on

just the portion of the table that they need. In addition to the security benefits, this

Chapter 4. Configuring the Application Server and Requester

113

is an effective strategy for reducing the amount of processor and input/output

usage that can be generated by indiscriminate querying of the data.

DBS Utility Considerations

Automatic Statistics Collection

Unless you specify otherwise, statistics are automatically collected and updated

during execution of the RELOAD, and RELOAD DBSPACE commands. If you are

performing a DATALOAD, statistics will also be automatically collected if you load

data into a single empty table with no indexes. The database manager

automatically issues an implicit UPDATE STATISTICS statement following the

DATALOAD. This can be time-consuming, if the number of active data pages in

that DBSPACE is large.

Note: This type of automatic collection only updates statistics for columns with

indexes. For multicolumn indexes it only updates the leading column. If you

want to ensure that all the columns have their statistics up to date, suppress

automatic collections and enter UPDATE ALL STATISTICS immediately

following a dataload. Refer to page 138.

Suppressing Automatic Statistics Collection

The automatic collection of statistics can be suppressed by specifying SET UPDATE

STATISTICS OFF in the DBS input file before the DATALOAD. In cases where the

database manager will not implicitly issue the UPDATE STATISTICS statement (but

rather collects statistics during the load), there is no advantage in explicitly

suppressing statistics collection. Otherwise, consider suppressing the UPDATE

STATISTICS statement if either of the following conditions apply:

v There are many DATALOADs into the same table. UPDATE STATISTICS could

be executed after the last one, or on a periodic basis.

v You know the statistics are not going to change significantly (for example, a

small amount of data is being added to a large table). In such cases, you could

postpone updating the statistics until more substantial changes have occurred.

TAPE Blocking

In the case of UNLOAD, a block size greater than 8244 bytes for tape output files

is recommended for improved performance. Specify the block size in the CMS

FILEDEF command associated with the OUTFILE statement.

Lock Escalation

When running the DBS utility in multiple user mode to load (INSERT) or unload

(SELECT) rows from a database, you may encounter lock escalation. SQL LOCK

DBSPACE or LOCK TABLE statements override the automatic locking mechanism;

they can be used to avoid deadlock conditions.

A user-issued SQL LOCK statement is useful only during multiple user mode

processing for table data in a public dbspace that is not defined with locking at the

dbspace level. A user-acquired database lock remains in effect until the end of the

logical unit of work in which it was issued.

DATALOAD and RELOAD Locking Considerations

If you insert many rows into the database with a RELOAD command or a

DATALOAD command without the COMMITCOUNT option specified, consider

using the SQL LOCK DBSPACE statement to eliminate or reduce lock escalation.

114

Performance Tuning Handbook

An exclusive lock on the dbspace where the tables being loaded are defined does

not appreciably increase lock contention and reduces the likelihood of deadlock

with another user.

Note: An exclusive lock on a table being loaded does not prevent lock escalation

and is not recommended.

You can also avoid lock escalation during multiple user mode DATALOAD

processing by issuing a SET AUTOCOMMIT ON command before the DATALOAD

command and specifying a sufficiently low COMMITCOUNT value in the

DATALOAD INFILE subcommand. Use of DATALOAD COMMITCOUNT

processing reduces the likelihood of the locking required by DATALOAD

processing delaying other users accessing the table being loaded or other tables in

the same dbspace where the table being defined resides. If the target table is in a

dbspace defined with ROW level locking, a COMMITCOUNT value of

approximately 200 should be sufficiently low. If the dbspace is defined with PAGE

locking, the COMMITCOUNT value can be higher (1000, for example) and lock

escalation is still avoided. Do not arbitrarily set the COMMITCOUNT value too

low because frequent commit points increase DATALOAD run time.

SELECT, DATAUNLOAD, and UNLOAD Locking Considerations

If you are running with an isolation level setting of repeatable read (the default

processing mode) and you know that a particular SELECT, DATAUNLOAD, or

UNLOAD operation is going to access many rows from one or more tables in the

database, lock escalation then normally occurs. You should consider acquiring a

SHARE lock on the table(s) being accessed. If all the tables being accessed reside in

the same dbspace, you should consider acquiring a SHARE lock on the dbspace

being accessed. This action can reduce lock contention and the likelihood that a

SELECT, DATAUNLOAD, or UNLOAD causes a deadlock with another user. Other

users can modify other tables in the same dbspace where the table being accessed

resides.

UNLOAD and RELOAD PACKAGE Considerations

To obtain the best performance when using the UNLOAD PACKAGE command

and the RELOAD PACKAGE command, consider doing the following:

v Unload or reload large numbers of packages in your system’s off-peak usage

time or in single user mode.

v If you are unloading or reloading packages in multiple user mode, use blocking

(by ensuring that the DBS utility was initialized with the BLOCK option).

These actions improve performance by preventing interruptions by other users.

PROGRAM is a synonym for PACKAGE. Therefore, UNLOAD or RELOAD

PROGRAM, and UNLOAD or RELOAD PACKAGE are equivalent commands.

When unloading or reloading a modifiable package, an exclusive lock is held on

the catalog table SYSACCESS. This may cause a performance deterioration for

other users wanting to run the exclusively locked package.

Chapter 4. Configuring the Application Server and Requester

115

Chapter

5. Improving Data Access Performance

User applications can access data without being dependent on how the data is

stored or on the types of access paths available to locate the data. The optimizer

determines an efficient access path to the data. This capability makes data more

readily available for use by many diverse applications; however, you can

experience a wide range of performance characteristics for the variety of possible

application requests.

Ideally, the user of a relational database need not be concerned with how data is

accessed. This is probably true for end users who write SQL queries quickly for

one-time or occasional use. However, for those who plan transaction programs that

may be executed thousands of times a day, some knowledge about the database

manager and how it chooses among various access paths and evaluation sequences

can enable them to significantly improve performance.

You can directly influence the access path to data in several ways (the first five are

discussed in this chapter):

v Creating or dropping indexes

v Maintaining up-to-date statistical information on your database

v Changing the number of tables in a dbspace

v Updating the catalog statistics used to estimate access costs

v Rewriting a query in a more efficient form

v Reorganizing data, refer to “Reorganizing Data” on page 70

v Reorganizing indexes, refer to “Index Fragmentation” on page 73.

Access Paths and Indexes

To evaluate a query, the database manager determines an access plan that consists

of a set of access paths (one for each table listed in the query) and other actions (for

example, a sort). Five types of access paths are described here:

v Dbspace scans

v Nonselective index scans

v Selective index scans

v Index-only access scans

v Unique index with key matching predicate(s).

For each method, a model query is given that refers to a generic table T, with

columns C1, C2, C3, and so on.

Notes:

1. The examples below use SELECT * because they are modeling arbitrary queries.

For actual queries, the use of SELECT * is not recommended: all queries should

only select the columns that are required in the answer set, in order to reduce

the cost of processing the query, and to provide additional access path

opportunities.

2. Considerations regarding the number of data pages read may not apply to

tables with long fields.

117

Dbspace Scans

Assuming that T has no indexes, the model query is:

SELECT * FROM T WHERE C1 = 42

Because a page can contain rows from any table in a dbspace, the database

manager must read every active data page in the dbspace to locate every row of T

and to determine whether its value of C1 matches the given value. If there are

other tables besides T in the dbspace, they will have to be read as well. If the

fraction of the dbspace occupied by T is small, then most of the pages read will

contain few or no rows from T.

It is a good idea to make a dbspace scan as inexpensive as possible. This can be

accomplished by having one table in a dbspace and reorganizing its rows so that

there are none that overflow from their original page onto another page. For

information, see “Reorganizing a Single Table” on page 71. Another factor is the

amount of free space left on each page. For information, see the DB2 Server for VSE

& VM Database Administration manual.

Index Scans

For an index scan, the model query is:

SELECT * FROM T WHERE C2 = 42

An index scan improves performance by enabling the database manager to avoid

the following:

v Reading all of the active data pages in the dbspace.

v Reading data pages that do not contain desired rows.

v Sorting the result.

An index scan performs better than a dbspace scan in many situations. However, it

has the following drawbacks:

v If VARCHAR or VARGRAPHIC columns are selected, or if not all columns

referenced are in the index, then the index scan must read the index pages as

well as the data pages. (A dbspace scan reads only data pages.)

v If the index is not clustered (even if the index is a clustering index), some data

pages may be read more than once. Refer to “Clustered Indexes” on page 66.

There are two types of index scans: nonselective and selective. An index scan on T

is selective if C2 is the first column of the index key. All other index scans are

nonselective.

Nonselective Index Scans

If T has an index on C1, the database manager can use the index to pick out only

those pages that contain rows of table T.

Be aware that if T is the only table in its dbspace, this method may be no better

than a dbspace scan. It is only more efficient in those cases where only a portion of

the pages in the dbspace contain rows from table T, the result needs to be sorted

on the index key, or index sargs can be applied to the index keys.

Selective Index Scans

If T has an index on C2, the database manager will be able to use the index to pick

out only those rows from table T where C2 = 42. That is, the only pages that will

be accessed are index leaf pages that contain keys where C2 = 42, and non-leaf

pages that must be traversed to navigate to these leaf pages.

118

Performance Tuning Handbook

A selective index scan is generally the most efficient access path. This is true even

in the case where T is the only table in the dbspace, if only a portion of the data

pages contain rows where C2 = 42. (If all or nearly all pages contained rows

where C2 = 42, then a dbspace scan would likely be more efficient).

Index-Only Access Scans

Although in general the database manager has to read data pages for a table to

evaluate a query, there are cases where all the columns referenced are present in

the index and the predicates do not require the data page. If these conditions are

met, then only index pages will be read. This is called index-only access, and is

possible for both selective and nonselective index scans. The model query is:

SELECT C2, C3 FROM T WHERE C2 = 50

(It is assumed here that an index exists on columns C2 and C3.)

Clustering Index

There is no advantage to using a clustered index with index-only access, because

clustered indexes are only valuable when the database manager uses an index to

access data pages.

Examples of Index only Access

The following are examples of queries that use index-only access. It is assumed

that a multicolumn index exists on columns C1, C2, C3, and C4.

SELECT COUNT(*) FROM T WHERE C2 = 5

The database manager scans the entire index looking for C2=5, but no data

pages are read.

SELECT C2 FROM T

The database manager scans the entire index, but no data pages are read.

SELECT MIN(C1) FROM T

The database manager does not read the entire index; just a single value.

SELECT MAX(C1) FROM T

For MAX column functions, C1 must be defined as NOT NULL so that a single

value is read rather than the entire index.

SELECT C1 FROM T WHERE C1 = 42 AND C4 = 100

The database manager reads only the index entries where C1 = 42 and then

scans for C4=100, but no data pages are read.

Index-only access is not possible when a VARCHAR or VARGRAPHIC column

appears in the SELECT list or in a residual predicate.

Creating Indexes

In some cases, it may be reasonable to create an index that includes data just to

improve the performance of certain common queries. For example, the sample

ACTIVITY table identifies each activity by an activity number (ACTNO). It also

contains a 6-character activity keyword (ACTKWD). If the table were often used to

decode activity numbers by retrieving the corresponding keywords, it might be

useful to have an index on both columns. The model query would be:

SELECT ACTKWD FROM ACTIVITY WHERE ACTNO = 42

Another case where index-only access is beneficial is a table with very long rows,

where the portion of the row retrieved is small compared to the size of the row. If

Chapter 5. Improving Data Access Performance

119

a query needs only three or four relatively short columns of that data, an index on

those columns might be worthwhile merely to avoid the cost of scanning all data

pages and extracting the useful data.

Unique Index with Key Matching Predicate(s)

The model query is:

SELECT * FROM T WHERE C1 = 42

Here, access is most direct if there is a unique index on column C1. In this case,

the database manager reads only as much of the index as needed to locate one

entry, and then at most one data page. Furthermore, instead of using a scan, it uses

a more efficient operation to return a single row. Refer to “Key-matching

Predicates” on page 123.

Indexes for Sorting

The primary use of indexes is to provide selective access to data, but they are also

used to sort data in a specified way. Consider this query:

SELECT * FROM EMPLOYEE

WHERE WORKDEPT LIKE ’A%’

ORDER BY EMPNO

The database manager can access the rows needed through an index on

WORKDEPT, but then it would have to sort all of those rows by EMPNO. It might

estimate that it would be more efficient to access all rows in order by an index on

EMPNO, then check the value of WORKDEPT in each one, but eliminate the sort.

The database manager can use indexes for ORDER BY and GROUP BY, but not

always for SELECT DISTINCT. It can avoid a sort for SELECT DISTINCT if a

unique index is used, or if there is a GROUP BY list that is a subset of the SELECT

list.

Note: If an application program contains a SELECT DISTINCT statement that is

preprocessed using a unique index, the preprocessor records that the

package has a dependency on the unique index. If the unique index ever

becomes invalid, the entire package will be invalidated and it will be

dynamically repreprocessed the next time it is executed.

Recommendations for Indexes

The nature and purpose of your data will determine what indexes you should

create, but the following very general guidelines may be of some help:

v If you delete rows from or update the primary key on parent tables, define

indexes on foreign keys.

v Define primary keys or unique constraints wherever they apply. The database

manager automatically defines unique indexes for these.

v Use indexes to speed up the most frequent queries to tables with more than 15

data pages; and for tables with more than 10 pages that are primarily accessed

for reading only.

v Create indexes on fixed-length rather than varying length columns.

v Create indexes to include columns frequently queried to allow for index-only

access.

v Whenever possible, create unique indexes. If you cannot create a unique index

on a single column, create a unique multicolumn index. For example if you want

120

Performance Tuning Handbook

to sort the EMPLOYEE table by the JOB column, create an index on JOB and

EMPNO (JOB,EMPNO). The index will still sort by JOB, but it can also be a

UNIQUE index.

v In a multicolumn index, place the “most” unique column first.

Disadvantages of Indexes

The above descriptions of the various types of access paths should suggest to you

that indexes can reduce access time significantly. But before you begin creating

them, carefully consider their costs:

v They require storage space.

v It takes time to create and maintain them.

v There is overhead associated with keeping them synchronized with the tables

they index. It takes more time for the database manager to update a table that

uses an index or insert new data into it.

v They may increase locking contention.

v They increase the time required for recovery.

Placing Tables into Dbspaces

Each large table should be placed in its own dbspace, so that rows from other

tables do not have to be examined during a dbspace scan. Another advantage is

that if you later wish to eliminate that table, you can do so with a DROP

DBSPACE statement, which will run very fast because the data, index, and header

pages do not have to be examined.

Very small tables may be grouped together in the same dbspace, because relatively

few additional pages have to be read during a dbspace scan. However, avoid page

level locking in this situation.

Dbspace scans are done during CREATE INDEX, DROP TABLE, and UPDATE

STATISTICS processing, and may be used to satisfy other SQL requests, depending

on index availability.

By default, locking takes place at the page-level. This is usually the best trade-off

between concurrency and locking overhead. You should consider locking at the

row-level when many applications access one small part of the database. The tables

there could be put in their own dbspaces, for which you would request row-level

locking (using an ACQUIRE DBSPACE or ALTER DBSPACE statement).

When you request row-level locking for a dbspace, key-level locking is also done

for indexes in that dbspace. Key-level locking on indexes reduces contention, but

increases overhead.

Organizing Referential Structures

Because referential operations (update of a primary or foreign key, deletion of a

parent row, or insertion of a foreign key) involve access to more than one table,

when organizing a referential structure you should carefully consider the

implications of concurrency. (The issues discussed here are equally applicable to

any set of related tables.)

Referential operations require access to multiple tables, and possibly to multiple

rows of dependent tables. This characteristic increases the possibility of deadlock

situations. When the primary key of a parent table is modified (DELETE,

Chapter 5. Improving Data Access Performance

121

UPDATE), all dependent rows are accessed (and possibly also modified).

Conversely, when a foreign key in a dependent table is modified (INSERT,

UPDATE), the parent table is accessed. If two such operations run concurrently in

different logical units of work (LUWs), a deadlock situation could result, which

would trigger the automatic rollback of the later LUW.

Note: A similar potential deadlock situation would be encountered whenever

logically related data is concurrently accessed in opposing ways.

Similarly, when multiple users access referential structures, lock contention

increases (because the users are accessing the same tables, and the number of rows

accessed can be quite large). This contention may reduce concurrency.

A user who understands the nature of referential operations can minimize their

effect on concurrency, by reducing the chances of multiple users performing

logically unrelated operations contending for locks. (Contention cannot be avoided

if users are performing logically conflicting operations.) Consider the following

ways to improve concurrency:

v Do not put tables from different referential structures in the same dbspace. In

general always try to place only one table in each dbspace.

v Create an index on a foreign key. (Whenever possible this should be a unique

index that exactly matches the columns of the foreign key. If necessary make the

index unique by creating a multicolumn index.) This provides the most selective

access path possible to dependent rows whose foreign key has a particular

value. Operations on a parent row can use this index to scan the dependent

table, thus avoiding the need for a dbspace scan or a nonselective index scan.

You should use discretion in creating such indexes, especially on tables with

several foreign keys. For example, you may consider creating them only on the

most often referenced foreign keys. These indexes will be of particular use in

reducing deadlock situations in environments where parent tables and

dependent tables are being modified concurrently, and will also provide faster

execution of all referential operations against parent rows.

Predicate Processing

Search conditions contain predicates joined with AND, OR, and NOT. A predicate

is a search condition in a WHERE or HAVING clause of an SQL statement.

Examples include C1 = 10, C2 BETWEEN 10 AND 20, EXISTS(subquery), and C4 NOT

LIKE ’A%’. Only those rows that satisfy a predicate are returned.

Predicates are resolved in one of two categories: residual or sargable. (Sargable is a

term derived from the words “search argument”.) Sargable predicates are applied

at the Database Storage Subsystem (DBSS) level; residual predicates are applied at

the Relational Data System (RDS) level.

Figure 14 on page 123 shows the hierarchy of predicates.

122

Performance Tuning Handbook

Predicate

sargable

residual

index page

data page

key-matching

index page

predicate

sarg

data page

sarg

Figure 14. Predicate Hierarchy

Sargable predicates are further divided into two categories: those that use the

index, and those that do not. The former are called either key-matching predicates

or index page sargs. The latter are called data page sargs.

A key-matching predicate, which is applied directly to the index key, is created

when the columns referenced in the predicate form an initial substring of an index

on the table.

An index page sarg is resolved using the index page, but is not used to search the

index key. It is created when the columns referenced in the predicate are not an

initial substring of an index, but are contained in the index.

A data page sarg does not use the index, and always requires the data pages be

read. It is created when the columns referenced in the predicate are not contained

in the index.

Column Attributes

The next sections deal with predicates in the WHERE clause. For these predicates,

it is important that the data types and CCSIDs of any columns and literals match

whenever possible. That is, numeric values should use the same representation,

including the same precision and scale for DECIMAL values. Character and

graphic values should have the same length. Columns and literals should use the

same CCSID, refer to “Impact of CCSIDs on Sargability” on page 131. Adhering to

this rule will always give the database manager the greatest flexibility in choosing

an efficient access path. All the examples assume that this rule has been followed.

For more information, refer to note number one on page 127.

Key-matching Predicates

Before a predicate can be considered key-matching it must be in the correct form

and a suitable index must be available.

Form of Key-matching Predicates

Some types of predicates can match index entries; other types cannot. For example,

if the EMPLOYEE table has an index on the column SEX, it matches the predicate

in this query:

SELECT * FROM EMPLOYEE WHERE SEX = ’M’

Chapter 5. Improving Data Access Performance

123

On the other hand, the same index does not match the predicate in this query:

SELECT * FROM EMPLOYEE WHERE SEX < > ’F’

We call a predicate key-matching if it can match the entries in a suitable index.

Table 6 on page 126 shows which predicate types are key-matching.

If a predicate fails to match the index, it may still be applied to the index, but not

used to search it.

Only one predicate per column can be chosen as the key, however, other predicates

on that column are eligible to be a sargable predicate. For predicates that are joined

with AND, one per column is chosen as the key. The one with the best filter factor

establishes the path and the other is turned into a sargable predicate. For example,

consider a table with three columns C1, C2, and C3. A multi-column index is

created (C3, C2, C1) and the following WHERE clause is used in a SELECT

statement:

...WHERE C1>1 AND

C1<2 AND

C2=2 AND

C3=3

Only the first, third, and fourth predicates are chosen as key-matching predicates

(C1>1, C2=2, C3=3). The second predicate (C1<2) is not chosen as key-matching

but it is sargable.

For maximum efficiency, use key-matching predicates and create suitable

indexes. The database manager may not always use an index to apply a

key-matching predicate—other factors may intervene. But the first step in reducing

the processing cost of a query is to use key-matching predicates where possible

and then create suitable indexes.

In general, when you create an multi-column index, put the column with the most

distinct values first, and continue in order to the least distinct values.

One exception to this rule is the case where the index provides a necessary

ordering of the data. With this query:

SELECT * FROM EMPLOYEE

WHERE EDLEVEL > 14 AND JOB = ’CLERK’

ORDER BY EDLEVEL

an index on EDLEVEL,JOB enables the database manager to access data in the

order required by the ORDER BY clause, thus saving a sort at the end. This may

be enough to justify scanning index entries for rows that are rejected.

Suitable Index for Key-matching Predicate

For a simple predicate, an index is fully matched if the column in the predicate is

the first column of the index. For example, the predicate C1=10 matches an index

on columns C1, C2, C3, as well as an index on column C1 alone. If there are

additional predicates on columns C2 and C3, they may also be evaluated through

the multicolumn index.

For a search condition where all the predicates are joined by an AND, it is enough

if the index includes the set of columns as an initial substring. For example, an

index on columns C1, C3, C4, and C6 is fully matched by the search condition

124

Performance Tuning Handbook

C1=10 AND C4='A' AND C3=7 AND C6=9, as long as all but the last column are

matched with equality predicates. The last predicate can be either an equality or a

range predicate.

The same index is not fully matched by the search condition C1=10 AND C4='A'

AND C6=9, because the set of columns in that search condition (C1,C4,C6) is not

an initial substring of C1, C3, C4, C6. However, the database manager can use the

index for the parts of the search condition that do form an initial substring; in the

example, it can apply the predicate C1=10 through the index. In addition, it can

still use the index to evaluate the predicates on C4 and C6, so that data pages do

not need to be accessed.

Similarly, such an index is not fully matched by the search condition C1=10 AND

C3=7 AND C4>'A' AND C6=9, because the predicate C4>'A' is not an equality

predicate. An index can only be matched up to and including the first non-equality

predicate. Thus, the database manager can apply the predicates C1=10 AND C3=7

AND C4>'A' as key-matching predicates to the index. Again, the predicate on C6

can be evaluated as an index sarg so that data pages do not need to be accessed.

Hence, the order of the index columns is important; it should take into account the

kinds of queries used. For example, suppose the Spiffy Computer department

intends to query its employee table regularly with predicates such as EDLEVEL >

14 AND JOB = 'CLERK'. With an index on EDLEVEL,JOB, the database manager

finds the first index entry with EDLEVEL greater than 14 and scans the remainder

of the index from there upward. But with an index on JOB,EDLEVEL, it scans only

the entries for clerks having EDLEVEL > 14, giving a shorter access path.

Note: If you created an index in order to improve the performance of an SQL

statement, you should probably check that the database manager actually

uses the index for that statement. To find out what access and processing

methods it has chosen, use the EXPLAIN statement (see “Using Explanation

Tables to Evaluate Performance” on page 141).

Sargable and Residual Predicates

Rows that are retrieved go through two stages of processing. Predicates can be

applied at the first stage are called sargable predicates; those that cannot be applied

until the second stage are called residual predicates. Predicates in the HAVING

clause are always residual. Resolution of predicates and the predicate hierarchy are

detailed in “Predicate Processing” on page 122. Table 6 on page 126 shows which

predicates are sargable and which are not.

There is a definite performance advantage in using sargable predicates: they

require fewer CPU instructions than do residual predicates, because they eliminate

rows that would otherwise be passed from first to second stage processing. Thus,

whenever possible, avoid a residual predicate by rewriting your SQL statement.

Example

Table T contains 1000 rows, and column C6 contains the integers from 1 to 1000.

Consider this query:

SELECT * FROM T WHERE INTEGER(C6/7) = 2

Because the column in the predicate is involved in an arithmetic expression, the

predicate is residual. The first stage must access 1000 rows and pass them all back

to the second stage. If you instead write the predicate as WHERE C6 BETWEEN 14

AND 20, then only seven rows are passed back to the second stage. Furthermore,

Chapter 5. Improving Data Access Performance

125

the predicate C6 BETWEEN 14 AND 20 is key-matching. If there is an index on C6,

the first stage need only access seven rows.

Sargable predicates are better than residual predicates, but a suitable index is

better still. Avoiding the processing cost of a residual predicate won’t help you

much if you have to access ten million rows without an index. (You can use

EXPLAIN to tell whether a sargable predicate exists for a particular column, refer

to the REFERENCE EXPLAIN table in the DB2 Server for VSE & VM SQL Reference

manual.)

Join Predicates

In general, any predicate involving more than one table is a join predicate. In the

database manager, a condition of the form T1.C1=T2.C2 (the equijoin) is handled

specially by the optimizer. For information on joins, see “Methods of Joining Two

or More Tables” on page 133.

Search Conditions and Their Processing Characteristics

Table 6 shows the different types of search conditions, and their processing

characteristics. The following conventions are used:

v A search condition consists of one or more predicates

v Predicates are combined using the logical operators AND/OR

v NOT can be applied to either predicates or search conditions

v Expression is any expression involving arithmetic operators, concatenation, scalar

functions, or column functions

v Value is a literal or host variable

v Litexpr is any value or expression

v Anyexpr is any column, value or expression

v Char is any character string that does not begin with the ‘%’ or ‘_’ special

characters

v pattern is any character string that begins with the '%' or ‘_’ special characters

v Op is one of the operators: <, <=, >, >=, =, <>, ¬=

v Rop is one of the range operators: <, <=, >, >=

v Q is one of the quantifiers: ANY, ALL, SOME

<> represents <> or ¬=

[ ] indicates parts of the predicate that are optional.

Note: If the predicate falls in two different categories, choose the more specific

category.

Table 6. Search Conditions and Their Processing Characteristics

Search Conditions

Key-

Sargable?

Default Filter Factor

Notes

Matching?

(FF)

COL = value

Yes

1/25

1,16

COL IS NULL

Yes

1/25

COL rop value

Yes

1/3

1,16,19

COL BETWEEN value1 AND value2

Yes

1/10

1,4,16,19

COL LIKE 'char'

Yes

1/10

5,19

COL IN (value1,...)

Yes

1/25*size

1,3,6,16,17

COL <> value

Yes

24/25

1,7,16

COL IS NOT NULL

Yes

24/25

COL NOT BETWEEN value1 AND value2

9/10

7,8,19

COL NOT IN (value1,...)

Yes

1 - 1/25*size

1,7,9,16,17

COL NOT LIKE 'value'

9/10

COL LIKE 'pattern'

1/10

COL LIKE host variable

1/10

16,19

126

Performance Tuning Handbook

Table 6. Search Conditions and Their Processing Characteristics

(continued)

Search Conditions

Key-

Sargable?

Default Filter Factor

Notes

Matching?

(FF)

T1.COL = T2.COL (different tables)

Yes

1/25

1,15

T1.COL rop T2.COL (different tables)

Yes

1/3

1,15,19

T1.COL <> T2.COL (different tables)

Yes

24/25

1,15

T1.COL1 = T1.COL2 (same table)

1/25

T1.COL1 rop T1.COL2 (same table)

1/3

T1.COL1 <> T1.COL2 (same table)

24/25

COL = [Q] (uncorrelated scalar subquery)

Yes

1/25

1,10

COL rop [Q] (uncorrelated scalar subquery)

Yes

1/3

1,10,19

COL <> [Q] (uncorrelated scalar subquery)

Yes

24/25

1,10

litexpr = [Q] (uncorrelated scalar subquery)

litexpr rop [Q] (uncorrelated scalar subquery)

1/3

10,19

litexpr <> [Q] (uncorrelated scalar subquery)

COL = (subquery)

1/25

COL <> (subquery)

24/25

COL rop (subquery)

1/3

11,19

litexpr = (subquery)

litexpr <> (subquery)

anyexpr op Q (subquery)

anyexpr [NOT] IN (subquery)

[NOT] EXISTS (subquery)

COL = expression

1/25

COL <> expression

24/25

COL rop expression

1/3

12,19

anyexpr NOT BETWEEN anyexpr AND anyexpr

9/10

12,18,19

anyexpr BETWEEN anyexpr AND anyexpr

1/10

12,18,19

anyexpr <> expression

24/25

litexpr rop anyexpr

1/3

litexpr

= anyexpr

litexpr

<> anyexpr

∼0

litexpr NOT IN (value,...)

∼0

12,17

litexpr IN (value,...)

12,17

search condition AND search condition

Yes

FF1*FF2

search condition OR search condition

Yes

FF1+FF2-FF1*FF2

NOT search condition

1-FF

Notes to Table 6 on page 126: Search conditions which are listed as key-matching

or sargable are only potentially so; they may not be treated as such because of the

following factors:

1. The value must be of the same or compatible type as the column. Adhere to

this rule whenever possible. Numeric data types have the following hierarchy:

SMALLINT < INTEGER < DECIMAL < FLOAT

A value’s data type can be converted to any higher data type. For example,

INTEGER can be converted to a DECIMAL (given sufficient precision) or

FLOAT, but not to SMALLINT. Similar compatibility considerations exist for

character and graphic data lengths, as well as for the precision and scale of

decimal data.

If the data type of the column is CHAR(n) or GRAPHIC(n), the predicate that

references it is sargable if the length of that predicate value is less than or

equal to “n”. The column and the predicate should also have the same CCSID,

refer to “Impact of CCSIDs on Sargability” on page 131.

Chapter 5. Improving Data Access Performance

127

If the data type of the column is DECIMAL(m,n), it must be possible to

accommodate the number of decimal digits before and after the decimal point

in the target decimal field (P1-S1 <= P2-S2 and S1<=S2 where P is precision

and S is scale).

Even-precision DECIMAL variables are supported by the DB2 Server for VSE

& VM product for the assembler preprocessor. You can use even-precision

DECIMAL columns in tables that are referenced by Assembler programs. Host

variables in even-precision will be left as is by the preprocessor. Therefore,

when these programs access even-precision DECIMAL data, predicates

become sargable instead of RESIDUAL. Your performance may improve when

using SQL statements that use even-precision packed DECIMAL columns.

The NULL predicate must be applied to a column without the NOT NULL

attribute in order to be key-matching or sargable. Otherwise, the predicate is

residual.

If a multicolumn index exists, at most one IN predicate can be used to match

columns of the index. For example, if a table T1(C1, C2, C3) and an index C1,

C2, C3 exist, the following query will have only one key-matching predicate,

not three:

SELECT * from T1 where C1 IN (:HV1, :HV2) AND C2 IN (:HV3, :HV4) AND C3=5

If value1 and value2 are equal, then for filter factor calculation purposes the

predicate is treated as though it were the equality predicate COL = value1.

Although the LIKE predicate is a residual predicate, the database manager

takes advantage of the character argument to generate a BETWEEN predicate

which is both key-matching and sargable. This BETWEEN predicate is then

applied by the first stage either as a key-matching or a sargable predicate.

This transformation does not apply if the pattern is a host variable or the

ESCAPE clause exists. It also does not apply if the character data is mixed

data.

If there are no host variables in the list, then a BETWEEN predicate will be

generated using the lowest and highest values. This predicate is sargable, and

can be used to reduce the number of rows returned to the second stage.

Whenever possible, avoid negating a predicate using NOT. Instead, use an

equivalent form that distributes the negation. In some cases, the database

manager will perform this transformation for you. For example, the predicate

NOT COL = value is treated like COL ¬= value.

This predicate, although residual as stated, can be rewritten to eliminate the

NOT BETWEEN into COL < value1 OR COL > value2, which is sargable. (See

note 14.)

Because this predicate is residual when more than one value is used, it might

be beneficial to rewrite it as COL ¬= value1 AND COL ¬= value2 AND ...

which is sargable.

10.

An uncorrelated scalar subquery can return at most one value, and can be

evaluated before the query that contains it. This returned value is then used to

replace the subquery. The predicate is scalar only if the subquery statement

specifies a COLUMN function and the subquery does not contain a GROUP

BY clause, or if the predicate containing the subquery is not quantified.

11.

Predicates that reference correlated subqueries or subqueries that can return

more than one row are always residual.

12.

An expression makes any predicate residual. Sometimes a query can be

rewritten to avoid the presence of an expression. For example, instead of

“SALARY+200 = 20000”, write “SALARY = 198000”. The second form is

executed more efficiently.

128

Performance Tuning Handbook

13.

For this kind of search condition to be key-matching, all predicates must refer

to columns that form an initial substring of the index columns. All but the last

column must be matched with equality predicates; the last predicate can be

either an equality or a range predicate.

In search conditions containing multiple predicates on the same column, only

one predicate can be chosen as the key. The predicate providing the best

filtering establishes the path, and the others are turned into sargable

predicates.

14.

All predicates in a search condition that contains an OR remain sargable only

if all the individual predicates are sargable; otherwise, they are all treated as

residual. In other words, a single residual OR will cause all the predicates in a

search condition to be residual.

If all the predicates refer to the same column and the column is indexed, the

search condition can be rewritten using the IN predicate.

For example, instead of:

SELECT * FROM EMP_ACT

WHERE ACTNO=90 OR ACTNO=100

write:

SELECT * FROM EMP_ACT

WHERE ACTNO IN (90,100)

If different columns are referenced and the columns are indexed, then a

UNION may be a more efficient form of the query.

In the following example, the database manager will have to examine all rows

in the EMPLOYEE table to find those that satisfy the two predicates:

SELECT * FROM EMPLOYEE

WHERE JOB = ’CLERK’

OR LASTNAME = ’JONES’

The same request can be processed more efficiently if it is reformulated as the

UNION of two SELECT statements:

SELECT * FROM EMPLOYEE WHERE JOB = ’CLERK’

UNION

SELECT * FROM EMPLOYEE WHERE LASTNAME = ’JONES’

15.

A join is accomplished by first accessing the outer table and looking for rows

that satisfy all predicates on that table only. For each such row, the inner table

is then accessed to find all rows there that match that row’s join column

value. Because a specific value is being used, a join predicate of

“colname = colname” becomes “colname = value”. This is why join

predicates can give selective access to a table, if the table is the inner table.

A join predicate is only sargable if the data types of the two columns are

identical (disregarding whether the columns support NULLS). If the data

types are CHAR(n), VARCHAR(n), GRAPHIC(n), and VARGRAPHIC(n), the

lengths must match. If they are DECIMAL(m,n), precision and scale must both

match.

The database manager path selection takes this into account when it decides

which table should be accessed first.

16.

Predicates using indicator variables are sargable only if they meet the

following criteria:

v The predicate is of the form COL = :HV1:IND1 or of the form COL = ?

v COL is a nullable column

Predicates using indicator variables which do not meet the above criteria are

always residual.

Chapter 5. Improving Data Access Performance

129

17. When the IN predicate contains only one value in the list, it is converted to an

EQUAL predicate.

18. All BETWEEN and NOT BETWEEN predicates that do not have a column as

the first argument and values as the second and third arguments, are residual.

19. For the following cases the default filter factor is determined from the

COLCOUNT value in the SYSTEM.SYSCOLUMNS catalog table (refer to

“SYSTEM.SYSCOLUMNS” on page 39):

v COL rop host variable

v COL rop COL

v COL like predicate

v COL BETWEEN anyexpr AND anyexpr

Filter Factors

The objective of a predicate is to return to the user only those rows satisfying a

particular search condition. Every predicate is treated like a filter that reduces the

number of rows returned. The degree to which the predicate reduces the size of

the answer set is the filter factor (FF). The filter factor is an estimate of the

proportion of rows that remain after a predicate has “filtered out” the rows that do

not satisfy it.

The filter factor is a value between 0 and 1. If it is 1, the whole table is selected,

and the predicate has no filtering effect; if it is 0, no rows are returned.

The database manager estimates a filter factor for every predicate. If the predicate

is either too complex (contains an expression), uses disjunction (OR), uses host

variables, or if there are no statistics available for the columns it references, then a

default filter factor is used. The defaults for various predicates are shown in

Table 6 on page 126.

Sometimes the optimizer will not use the index when a predicate uses host

variables. This is because the optimizer is forced to make assumptions about the

values that are not available when the statement is being preprocessed. In such

cases, you can usually improve performance by executing the SQL statement

dynamically with fixed values.

Table 7. Filter Factors

COLCOUNT Value

Filter Factor for rop

Filter Factor for LIKE,

BETWEEN

>=100,000,000

1/10000

3/100000

>=10,000,000

1/3000

1/10000

>=1,000,000

1/1000

3/10,000

>=100,000

1/300

1/1000

>=10,000

1/100

3/1000

>=1000

1/30

1/100

>=100

1/10

3/100

<100

1/3

1/10

=-1

1/3

1/10

130

Performance Tuning Handbook

Examples of Predicate Processing

The following examples of predicates illustrate the general rules shown in Table 6

on page 126. In each case, assume that there is an index on columns C1, C2, C3, C4

of the table.

WHERE C1 = 5 AND C2 = 7

Both predicates are sargable, and both can be applied as key-matching

predicates to the index.

WHERE C1 = 5 AND C2 > 7

Both predicates are sargable, and both can be applied as key-matching

predicates to the index.

WHERE C1 > 5 AND C2 = 7

Both predicates are sargable, but only the first can be applied as a

key-matching predicate. Because all the predicates reference columns in the

index, the second predicate will be applied as an index page predicate.

WHERE C1 > 5 OR C2 = 7

Both predicates are sargable, and the combination is sargable. The OR

prevents the use of key-matching predicates. The index can not be used for

a selective index scan. However, both predicates will be applied as index

page predicates.

WHERE C1 IN (subquery) AND C2 = C1

Both predicates are residual. The index is not considered for a selective

index scan, and both predicates are evaluated residually.

WHERE C1 = 5 AND C2 = 7 AND C3+5 = 7

Only the first two predicates are sargable and can be applied as

key-matching predicates. The third predicate is residual. The index is

considered for selective access. All rows satisfying those two predicates are

passed to residual processing to evaluate the third predicate.

WHERE C1 = 5 OR C2 = 7 OR C3+5 = 7

The third predicate is residual; hence, the combination is residual. All three

predicates are evaluated residually.

WHERE C1 = 5 OR (C2 = 7 AND C3 = C4)

The third predicate is residual, so, the combination of the second and third

predicates (in parentheses) is also residual. Hence, the total combination is

residual. All predicates are evaluated residually.

WHERE (C1 > 5 OR C2 = 7) AND C3 = C4

The combination of the first two predicates is sargable, but the OR

prevents the use of key-matching predicates. The third predicate is

residual. The index is not considered for a selective index scan, but the

combined predicate (in parentheses) is sargable and will be applied as

index page predicates. All rows satisfying those two predicates are passed

to residual processing to evaluate the third predicate.

WHERE C1 > 5 AND C2 = 7 AND C5 = 8

All predicates are sargable, but only the first can be applied as a

key-matching predicate. Because the remaining predicates reference

columns in both the index and data pages, the remaining two predicates

are applied as data page sargs.

Impact of CCSIDs on Sargability

The DB2 Server for VSE & VM SQL Reference manual lists the rules used to decide

which operand will undergo Coded Character Set Identifier (CCSID) conversion in

Chapter 5. Improving Data Access Performance

131

a comparison operation. These rules will help you to maintain sargability

whenever possible. The rules were defined to ensure that a column operand will

only undergo CCSID conversion if it is absolutely necessary.

Whenever a column operand is chosen to undergo CCSID conversion, the

predicate becomes residual because CCSID conversion is performed from RDS, and

not from DBSS. In most cases, this is a necessary consequence of using the CCSID

support. In other cases, it can be avoided by understanding how the rules apply

and by changing the application or the data.

Consider the search condition: COL = value (where value is a host variable). This

search condition is normally sargable (see Table 6 on page 126). The database

manager attempts to keep this search condition sargable by always performing

CCSID conversion on the host variable operand. There is a case, however, when

the rules state that the column operand is the one that should be converted: when

the subtype of the column is SBCS and the subtype of the host variable CCSID is

mixed.

If it is possible that the host variable will contain mixed data, then the column

operand must undergo CCSID conversion and the predicate must become residual.

One way to make this predicate sargable is to set the column subtype to mixed.

This may not always be possible or desirable, but you should consider this

situation when setting the subtype of new columns.

If the host variable will never contain mixed data, then it is possible to make this

predicate sargable by changing the subtype of the host variable from mixed to

SBCS. To do this, change the default CCSID values on the application requester.

Refer to the DB2 Server for VM System Administration or the DB2 Server for VSE

System Administration manuals for information on how to change the CHARNAME

setting for an application requester. This situation may not be possible or desirable,

especially if graphic or mixed data is used elsewhere in the query.

Tuning Queries with Several Tables

The process of combining rows of one table with rows of another is called a join. It

is often possible to write a query against two or more tables either as a join or as

one or more nested SELECT clauses. The first method is usually more efficient, as

this gives the optimizer more choices during access path selection.

The following query retrieves data about all designers in departments that are

responsible for projects that are part of a major project MA2100. It will be used

here to illustrate different access methods in detail.

FROM EMPLOYEE E, PROJECT P

WHERE E.JOB = ’DESIGNER’

AND E.WORKDEPT = P.DEPTNO

AND P.MAJPROJ = ’MA2100’

In this example, the following assumptions are made:

v Each table is in its own dbspace

v Table EMPLOYEE

- Has 10 000 rows on 500 pages

- Has an index on EMPNO with 25 pages

- Has an index on WORKDEPT with 10 pages

v Table PROJECT

132

Performance Tuning Handbook

- Has 3000 rows on 60 pages

- Has an index on PROJNO with 8 pages

- Has an index on RESPEMP with 8 pages

v COLCOUNT for MAJPROJ is 100.

v COLCOUNT for WORKDEPT is 1000; for JOB, 50

Methods of Joining Two or More Tables

To join two tables in a single query, the database manager chooses the less costly

of a nested loop join and a merge scan join. The two methods are described below.

Nested Loop Join (Type 1)

In nested loop joins, the rows of one table (the “outer” table) are retrieved one by

one. Sargable and residual predicates are applied to eliminate unqualified rows.

For each qualified row of the outer table, the database manager opens a cursor on

the second table (the “inner” table), and retrieves all rows that satisfy both the join

predicate connecting the two tables and any local predicates on the inner table.

Either table can be scanned by a dbspace or index scan. The outer table is scanned

once, while the inner table is scanned as many times as the number of qualifying

rows in the outer table. Hence, the nested loop join is most efficient when the inner

table has an efficient access path, and when only a few rows of the outer table

remain after applying predicates to it. For a nested loop join:

Join cost = cost of outer table scan

+ ((Estimated number of qualifying records in outer table) x

(cost of inner table scan))

If the inner table is small enough to fit into its share of the buffer pool, the

database manager anticipates that the entire inner table will remain in buffers

throughout the operation. On this assumption, the I/O cost in the second term of

the join cost is estimated as no more than the cost of scanning the inner table once.

The nested loop join is illustrated in Figure 15.

Chapter 5. Improving Data Access Performance

133

Method: Nested Loop Join

SELECT A, B, X, Y

FROM OUTER, INNER

WHERE A=10 AND B=X

Table:

OUTER

INNER

COMPOSITE

Columns:

A B X Y

Scan the outer table.

The nested loop join

For each qualifying row

scan the inner

produces this result.

table to find all

matching rows..

Figure 15. Nested Loop Join

Merge Scan Join (Type 2)

For this method, there must be one or more predicates of the form

TABLE1.COL1 = TABLE2.COL2, where the two columns have the same data type

and length attribute. One of the predicates is chosen as the merge join predicate. The

approach is to scan both tables in the order of the merge join columns, and to

merge the result together whenever matching rows are found.

If the outer table has no efficient index on the join columns, an intermediate table

is built by sorting the outer table on the join columns, applying any local

predicates, and eliminating unused columns. The inner table is handled similarly.

The database manager then reads the first row of both ordered tables (applying

any predicates that remain). If the merge join predicate matches, the database

manager returns the combined result. It then reads the next row of the inner table,

which might match the same row of the outer table, and continues to read rows

from the inner table and return the results until the merge join predicate fails to

match. When there is no longer a match, the database manager reads the next

outer table row. If that row has the same join predicate value, the database

manager goes back and reads the matching group of records from the inner table

again. If the outer row has a new join predicate value, the database manager

searches ahead in the inner table until it finds either:

v A matching inner row, in which case the matching process is repeated.

v An inner row with a higher value than the join predicate. Then the database

manager discards the unmatched row of the outer table and searches through

the outer table until either a matching row or a row with a higher join predicate

value is found. If a matching row is found, the matching process is again

repeated. If a higher join predicate is found, the search moves back into the

inner table.

Hence, for a merge scan join,

134

Performance Tuning Handbook

Join cost = cost of outer table scan

+ cost of sorting and reading outer table (if needed)

+ cost of inner table scan

+ cost of sorting and reading inner table (if needed)

If an efficient index does exist on the join column, and this index has been used to

retrieve the rows of either table, the rows of that table are already in sequence. In

this case, no sort of the table is required.

Merge scan join is illustrated in Figure 16.

Method: Merge Scan Join

SELECT A, B, X, Y

FROM OUTER, INNER

WHERE A=10 AND B=X

Condense and sort the

outer table, or access

inner table, or access

it through an index on

column B.

column X.

Table:

OUTER

INNER

COMPOSITE

Columns:

A B X Y

Scan the outer table.

The merge scan join

For each row,

scan a group of matching

produces this result.

rows in the inner table.

Figure 16. Merge Scan Join

Choosing an Access Method

When choosing an access method for the query shown on page 134, which entails

a simple join of only two tables, four access methods must be considered: the

nested loop join and the merge scan join, each with both possible choices of the

outer and inner table. The choice will be based on a comparison of their costs.

Nested Loop, with EMPLOYEE as Outer Table

With no index on JOB, a scan occurs on all 10000 (500 pages) rows of the employee

table. For each row, it first evaluates the predicate JOB = 'DESIGNER'; an estimated

200

(1/50 x 10000) rows remain. For each of those 200 rows, a scan takes place on

all 3000 rows (60 pages) of the project table to find rows with WORKDEPT =

DEPTNO and MAJPROJ = 'MA2100'. The major costs are:

Join cost = 10 000 row scan (500 page I/Os)

+ (200 x 60 page I/Os)

= 12 500 page I/Os

Chapter 5. Improving Data Access Performance

135

Nested Loop, with PROJECT as Outer Table

With no index on MAJPROJ, the database manager must scan all 3000 rows of the

project table. For each row, it first evaluates the predicate MAJPROJ='MA2100'; an

estimated 30 (1/100 x 3000) rows remain. For each of those 30 rows, the database

manager must find all the rows in the EMPLOYEE table with WORKDEPT =

DEPTNO and JOB = 'DESIGNER'. But instead of scanning the entire employee

table, it can use the index on WORKDEPT each time. Each department has an

average of 10 employees (10 000 rows / 1000 distinct departments), so the I/O cost

is 1 index leaf page and 10 data pages for each value of WORKDEPT. The major

costs are:

Join cost = 3000 row scan (60 page I/Os)

+ (30 x 11 page I/Os)

= 390 page I/Os

Merge Scan, with EMPLOYEE as Outer Table

Here the database manager reads the project table and applies the local predicate

MAJPROJ = 'MA2100'. The remaining 30 rows are sorted and placed into a

temporary table (a write and read of 1 page). Then, the database manager reads

the employee table in department number order using the index on WORKDEPT

(10 index pages + 500 data pages). For each row of the employee table, the

database manager first evaluates the predicate JOB = 'DESIGNER'; if the row

qualifies, it reads rows in the inner (temporary) table that match on department

number. The costs are:

Join cost = cost of outer table index scan

510 page I/Os

+ cost of accessing inner table

60 page I/Os

+ cost of sorting and reading inner

2 page I/Os

= 572 page I/Os

Merge Scan, with PROJECT as Outer Table

If the outer table is the project table, it cannot be accessed in department number

order, and thus must be sorted. Before sorting, the database manager eliminates

the rows that do not satisfy MAJPROJ = 'MA2100', leaving 30 rows. The estimated

costs are:

Join cost = cost of outer table scan

60 page I/Os

+ cost of sorting and reading outer

2 page I/Os

+ cost of accessing inner table

510 page I/Os

= 572 page I/Os

The nested loop scan with the project table as the outer table would be the best

choice here. Only this access path makes use of the employee table index on

WORKDEPT to avoid a scan of the entire table while not requiring a sort of the

project table.

The two merge scan costs are shown as identical here because CPU costs and the

presence of duplicates have been ignored. In reality, both would affect the actual

cost calculated by the database manager.

Multiple Joins

Multiple joins are performed by logically joining two tables at a time, using either

the nested loop or merge scan join method. This does not mean that each join

necessarily produces an actual intermediate table; in a number of cases, no

intermediate tables are required.

For example, a query joining tables T1, T2, and T3 may use a nested loop join to

join T1 to T2, and a merge scan join to join the logical result (T1-T2) to T3. In this

136

Performance Tuning Handbook

case, an intermediate or composite table might be created, and would appear in

PLAN_TABLE if you examined the query with EXPLAIN. In addition, the

composite table may require sorting to participate in the merge scanjoin. T3 may

also require sorting (unless an index exists over the join columns). If the query has

no ORDER BY clause, requiring sorting of the final result, then the T1-T2

composite table can be joined (using merge scan) with T3, returning each

qualifying result row as it is found.

In short, whether composite tables are formed or not depends on a number of

factors, including the SQL statement in question, the availability and type of

indexes, and the catalog statistics. Any number of access plans can produce the

correct answer, but the optimizer will choose the lowest estimated total cost

solution.

Keeping Database Statistics Current

The catalog tables hold statistical information on data stored in the database, and

the database manager uses these statistics to determine how it will access data for

each individual SQL request. If the statistics are unavailable, then default values

are used. Table 8 shows the key statistics used for access path selection, and

identifies which catalog table they are in.

Table 8. Key Catalog Statistics Used for Path Selection

Table Name

Description

Default Value Used by

Column Name

Optimizer When Catalog

Value is -1

SYSCATALOG

ROWCOUNT

Total number of rows for this table.

100

NPAGES

Number of pages in the dbspace that contain rows

of this table.

SYSDBSPACES

NTABS

Number of tables in the dbspace.

NACTIVE

Number of active pages in the dbspace.

3*NTABS

NPAGES

Number of usable pages in the dbspace.

SYSCOLUMNS

COLCOUNT

Number of distinct values in this column.

HIGH2KEY

Second highest value in this column.

LOW2KEY

Second lowest value in this column.

AVGCOLLEN

Average length of the column.

SYSINDEXES

FULLKEYCOUNT

Number of distinct values of the full key.

FIRSTKEYCOUNT

Number of distinct values of the first column

of the

key.

Equals COLCOUNT for the index column.

NLEAF

Number of leaf pages in the index.

NLEVELS

Number of levels in the index.

CLUSTERRATIO

Measure of how clustered an index is.

SYSCOLSTATS

VAL10

The value at the tenth percentile.

VAL50

The value at the fiftieth percentile.

VAL90

The value at the ninetieth percentile.

FREQ1VAL

The most frequent value in the column.

FREQ1PCT

Number of rows that contain that column value,

given as a percentage of the total number of rows.

The second most frequent value in the column.

FREQ2VAL

Number of rows that contain that column value,

FREQ2PCT

given as a percentage of the total number of rows.

Chapter 5. Improving Data Access Performance

137

Table 8. Key Catalog Statistics Used for Path Selection (continued)

Table Name

Description

Default Value Used by

Column Name

Optimizer When Catalog

Value is -1

Notes:

Default values are only assigned to the base table. The values of NPAGES and NACTIVE determine PCTPAGES.

Column statistics are used primarily for calculating filter factors. When these statistics are not available (a value

of -1), the optimizer uses a default filter factor. These default filter factors are listed in Table 6 on page 126.

It is impractical for these statistics to be maintained on every INSERT, UPDATE,

and DELETE operation; therefore, you must periodically update them in the

catalog tables with the UPDATE STATISTICS or the UPDATE ALL STATISTICS

statement.

Update your statistics whenever a table’s contents change significantly.

Suppose you enter the following UPDATE STATISTICS statement:

UPDATE STATISTICS FOR TABLE MYTABLE

The database manager updates the statistics for MYTABLE in the catalog tables.

However, the statistics are updated only for indexed columns. (For indexes having

multicolumn keys, only the first column is updated.) Similarly, whenever you

create a new index, the database manager automatically updates the statistics for

index columns.

To update the statistics for all columns in MYTABLE (even those that are not

indexed), enter the following statement:

UPDATE ALL STATISTICS FOR TABLE MYTABLE

The complete set of statistics produced by the ALL option may result in a better

access strategy being selected by the optimizer component. However, the ALL

option can greatly increase the processing time required to run the UPDATE

STATISTICS statement. UPDATE ALL STATISTICS is recommended where queries

have non-indexed columns with local predicates or queries have multi-column

indexes with local or join predicates that are not on the first column of an index.

The DB2 Server for VSE & VM SQL Reference manual describes exactly what

operations affect each statistic. The SYS0001 DBSPACE, which contains the catalog

tables, is a candidate for UPDATE ALL STATISTICS processing.

It is recommended that you schedule UPDATE STATISTICS activities during

off-peak hours.

When working with preplanned application programs, ensure that the programs

are re-preprocessed whenever the tables accessed by the application have

significantly changed (for example, a 10%-20% or more change). Before

re-preprocessing, ensure that statistics have been updated so that the optimizer is

provided with the new characteristics of the data.

The DBS utility DATALOAD and RELOAD commands automatically collect the

statistics for a table as part of the load operation; thus, it is not necessary to issue a

separate UPDATE STATISTICS statement (although see the DB2 Server for VSE &

VM Database Services Utility or the DB2 Server for VSE & VM Database Services

Utility manuals for restrictions). If you want, you can suppress this automatic

updating of statistics through the DBS Utility SET UPDATE STATISTICS command.

138

Performance Tuning Handbook

Note: Statistics are not updated when you use DATALOAD to load data into a

view.

Using Catalog Statistics

The following suggestions introduce the techniques of using the catalog statistics to

influence the choice of access methods made by the database manager. For more

information see “Tuning Queries with Several Tables” on page 132.

Modelling your Production System

If you have DBA authority, you are allowed to update the statistical values stored

in the catalog tables. See Table 8 on page 137 for a description of the columns in

question. This ability lets you create a model of your production system on a

smaller test system. You can then use the EXPLAIN statement to determine how

your production tables would be accessed for some set of SQL statements. In the

same way, you can model a future production system by making assumptions

about the size and nature of the database structure.

To create a model of your production system, the same database structure must be

in place on your test system. This means the same dbspaces, tables, indexes,

referential constraints, and so on must be defined on the test system. You can then

modify the statistics in your test system catalog tables to be identical to those in

the production system. The optimizer will choose the same paths to access your

tables on the test system as it would on the production system.

After you have a model of the production system established, you can discover

how the optimizer will react to changes in the database structure, such as adding

new indexes by updating the catalog table statistics. Using EXPLAIN will tell you

whether (and how) a new index would be used for a particular SQL statement,

and how it impacts the expected costs of executing that statement. With this

information, you can decide whether you should add the index to the production

system.

Similarly, you can now use your test system to discover how rewriting an SQL

statement into an alternate form affects the path chosen and the estimated cost for

executing the statement.

A Warning about Updating Statistics

If you supply the COLCOUNT value for an index column without running

UPDATE STATISTICS, you should also supply HIGH2KEY and LOW2KEY for the

index. If the data is not uniformly distributed you should also supply the

additional values in the SYSCOLSTATS table. These columns are defined as CHAR,

so an UPDATE statement must provide a character or hexadecimal value.

Although the columns have a length of 12, only 8 bytes of information should be

stored. Entering a character value is quite straightforward—SET LOW2KEY =

'ALAS', for instance. But to enter a numeric, date, or time value you must use the

hexadecimal value of the DB2 Server for VSE & VM internal format. To determine

the proper hexadecimal data to use for these data types, create a table with

columns of the required types and insert the values you want to use for

HIGH2KEY and LOW2KEY into it. Then display the internal format of these

values by using the HEX column function in the select list. For example:

SELECT HEX(column_name) FROM ...

Chapter 5. Improving Data Access Performance

139

Be sure to allow for a NULL indicator in keys that allow NULLS by making the

first character '00'X. If values being set are less than 8 bytes long (including the

'00'X NULL indicator byte) pad them on the right with '00'X bytes.

If the NPAGES column of SYSTEM.SYSDBSPACES is updated (to allow testing of

the access plan generation) and then an ACQUIRE DBSPACE command attempts

to acquire this dbspace, an error message may result. Updating NPAGES does not

actually change the size of a dbspace, it changes the information supplied to the

optimizer used in access plan generation.

Determining the Cost of Access Methods

The access method cost has two parts: a processing cost and an I/O cost.

Depending on the hardware environment, a query can be either CPU or I/O

bound. You may want to compare the cost characteristics of your queries to

equivalent alternatives.

Processing Cost

The database manager estimates processing cost as a result of:

v The number of rows considered

v The number of residual predicates checked for each row

v The number of rows that satisfy the residual predicates.

This estimate of the number of rows that remain after applying all the predicates is

dependent on assumptions on the distribution of data within the column. For the

first column of an index, the database manager records additional information to

help it recognize non-uniform data distributions. Otherwise, it assumes that the

data values are evenly distributed, and uses the following rules:

For a predicate of the form WHERE column = value: The number of rows is

estimated as either ROWCOUNT/COLCOUNT or ROWCOUNT*(1/COLCOUNT);

that is, the total number of rows divided by the number of distinct values in the

column. The term 1/COLCOUNT is the filter factor.

For a predicate that uses a range operator: The number of rows is estimated using

the ratio of the range encompassed in the predicate to the range of values in the

column. Thus, if LOW2KEY and HIGH2KEY are respectively 10 and 90, then the

predicate column > 70 is given a filter factor of, approximately, (90 - 70)/(90 - 10),

or 0.25. Only the first 8 bytes of the column are stored for HIGH2KEY and

LOW2KEY so it is important that columns be distinct within the first 8 bytes. (7

bytes if the column is nullable.)

I/O Cost

The I/O cost is estimated by the number of index pages, the number of data

pages, and the number of directory pages to be read.

For a dbspace scan, the number of index pages read is zero. Otherwise, it is

determined from the number of leaf pages and levels in the index (NLEAF and

NLEVELS) and the filter factors of the matching predicates.

A dbspace scan reads all data pages. The total number of pages is given by

NACTIVE.

140

Performance Tuning Handbook

DB2 Server for VSE. Operations Guide / Handbooks (2004-2007) - page 30