DB2 UDB ESE V8 non-DPF Performance Guide for High Performance OLTP and BI

198 DB2 UDB ESE V8 Performance Guide for High Performance OLTP and BI

deadlocks can be minimized. However, lock waits and timeouts may still

occur.

11.Tune the database configuration parameters LOCKLIST, MAXLOCKS and

DLCHKTIME to minimize lock escalations and optimize deadlock detection

cycles as described in 3.4.6, “Locking considerations” on page 260.

12.OLTP environments are high throughput and transactional in nature, and

should therefore use row level locking with CS isolation level unless there is a

specific reason for a more restrictive lock. They should also commit more

frequently to reduce the duration a lock is held.

13.BI environments are low throughput and predominantly read only in nature,

and either table level locks or row locks with CS or UR isolation level would be

appropriate.

3.4 System environment considerations

While application design is critical, there are a number of system considerations

that can significantly impact overall system performance. System tuning is

typically performed by DBAs in conjunction with operating system administrators.

The following system performance-related considerations are discussed here:

򐂰 I/O placement considerations

򐂰 Log considerations

򐂰 Monitor switch settings

򐂰 Connection considerations

򐂰 Buffer pool considerations

򐂰 Locking considerations

򐂰 Package cache considerations

򐂰 Catalog cache considerations

򐂰 Sort considerations

򐂰 Other memory considerations

򐂰 Miscellaneous considerations

3.4.1 I/O placement considerations

Proper disk subsystem placement of critical DB2 objects is essential to reducing

I/O times and ensuring high availability of these objects.

Figure 3-17 on page 199 describes the default directory structure for a database.

Figure 3-18 on page 200 highlights the main DB2 objects.

Chapter 3. Application design and system performance considerations 199

Figure 3-17 Default database directory structure

The drive or directory specified on the CREATE DATABASE command

NODE0000

SQL00001

DB2EVENT

SQLT0000.0

SQLT0001.0

SQLT0002.0

The name of the DB2 instance

The partition number of the database,

0 for a non-partitioned database

The database ID, starts at 1, increases for

all subsequent databases

The default event directory for the database

The catalog table space, SYSCATSPACE

The temporary table space, TEMPSPACE1

The user table space, USERSPACE1

DB2 Instance Name

SQLOGDIR

The default log directory for the database

The name of the DB2 instance

Drive / Directory

Drive / Directory

The drive or directory specified on the CREATE DATABASE command

DB2 Instance Name

The name of the DB2 instance

DB2 Instance Name

200 DB2 UDB ESE V8 Performance Guide for High Performance OLTP and BI

Figure 3-18 DB2 objects placement

Figure 3-18 highlights the four categories of DB2 objects as follows:

򐂰 DB2 logs.

򐂰 Regular table spaces, which include:

– User data and indexes in the default USERSPACE1 table space or in one or

more user-defined table spaces.

– System catalogs that store DB2 metadata in the SYSCATSPACE catalog table

space.

򐂰 Large table space (previously called Long table space) that stores LOBs,

Long Varchar columns and indexes.

򐂰 Temporary table space that consists of two types of table spaces:

– User temporary table spaces that store declared global temporary tables.

DB

Large (Long)

Tablespace

Regular

Tablespace

Logs

Userspace1

(User tablespace)

Temporary

Tablespace

Syscatspace

(Catalog tablespace)

Disk

Instance

User tables

Indexes

User temporary

declared global

temporary tables

System temporary

Sorts

Joins

Index creation

Reorg

LOBs

Long

Varchar

Indexes

DB

Disk

Disk(s)

Disk(s)

Disk(s)Disk(s)

Container(s)

Container(s)

Container(s)

Container(s)