A Quick Reference for Tuning DB2 Universal Database EEE

This article presents techniques for tuning DB2 Extended Enterprise Edition (EEE), including memory usage, buffer pools, and tuning tools. Perl scripts are provided to help you collect and analyze the DB2 snapshot data and to provide hints to improve database performance.

<script language="javascript" type="text/javascript"> capture_referrer(); </script>

© 2002 International Business Machines Corporation. All rights reserved.

Introduction

This paper presents techniques for DB2® Universal Database^TM Extended Enterprise Edition (EEE) performance tuning. The topics described here include the basics of DB2 EEE, information about DB2 memory use, tuning guidelines, and more. Perl scripts are provided to help you collect and analyze the DB2 snapshot data and to provide hints to improve database performance. The examples provided in this paper are mainly focused on the Windows® platform.

Back to top

What is DB2 EEE?

The Extended Enterprise Edition from the DB2 product family supports data partitioning across clusters of massively parallel computers. A partitioned database can maintain very large amounts of data and can open opportunities for new applications. You can install DB2 EEE with multiple partitions on a single system (logical nodes), or you can distribute it on multiple systems as physical nodes. The database manager on each node manages part of the database. DB2 EEE architecture can improve the performance of decision support and online transaction processing systems.

Shared-nothing architecture DB2 EEE works as a shared-nothing architecture on the Windows®, UNIX® and Linux operating systems. Figure 1 illustrates the DB2 EEE shared-nothing architecture.

Figure 1. DB2 EEE partitions on shared-nothing hardware

The portion of the database that consists of its own data, indexes, configuration files and transaction logs is referred to as a database partition.

DB2 EEE's shared-nothing architecture allows database partitions to be assigned to one or more processors. The database partition does not share any data, but communicates via messages.

Parallelism DB2 EEE supports two types of parallelism: inter-partition and intra-partition.

With inter-partition parallelism, for any given query, DB2 first identifies the partitions where the data resides. Then, the node from which the query originates (the coordinator node) coordinates with the other partition nodes to find all qualifying rows. Each node works on only a part of the data.

With intra-partition parallelism, the given query is divided into a series of operators such as scanning, joining and sorting but all the work for those operators is done concurrently in the same partition using different processes.

DB2 EEE process/thread model per partition A DB2 EEE instance on Windows starts as a service when the db2start command is issued. Starting an instance starts db2syscs.exe for every partition on the node. These processes communicate via messages using IPC or TCP/IP. Each application is assigned an agent that works on behalf of the application.

Activating a database starts the logger for logging activity and the deadlock detector for detecting the deadlocks. DB2's license manager starts as a different process and monitors the license usage.

Back to top

How DB2 uses memory

DB2 memory gets allocated at different stages. Figure 2 illustrates DB2 EEE memory allocation.

Global control block Memory space required for the database manager to run. This memory is allocated when the database manager starts. Fast Communication Manager (FCM) provides the communication support for DB2 EEE. FCM buffers are allocated from this memory area.

Database global memory Memory required for a database to run. This memory is allocated when the database gets activated. The number of memory segments is limited by the numdb (number of databases) configuration parameter. The total size of database global memory is determined by the database configuration parameters shown in Table 1:

Table 1. Database configuration parameters that affect database global memory size

Parameter	Description
buffpage	Impacts the buffer pools whose size is set to -1
pckcachesz	Package cache size
util_heap_sz	Utility heap size
dbheap	Database heap
locklist	Maximum storage for locks

Application global memory Application global memory is the memory required for the application to run. This memory is allocated when the first agent to receive a request from the application requests a connection. This memory is controlled by the database configuration parameter app_ctl_heap_sz. Application global memory is shared between all agents working for the same application.

Agent private memory Each agent has its own private memory. The lower of the configuration parameters maxappls and maxagents limits the number of memory segments:

Table 2. The lower of maxappls and maxagents limits memory segments

Parameter	Description
maxappls	The total number of maximum applications for all active databases.
maxagents	The maximum number of agents.

The maximum size is determined by various configuration parameters shown in Table 3.

Table 3. Configuration parameters that affect maximum private memory

Parameter	Description
agent_stack_sz	Agent stack size
udf_mem_sz	UDF shared memory set size
applheapsz	Application heap size
sortheap	Sort heap size
stmtheap	Statement heap size
stat_heap_sz	Statistics heap size
query_heap_sz	Query heap size
drda_heap_sz	DRDA heap size

Agent/application shared memory This memory is shared between the agents that are working for the same application.

Agent shared memory is affected by the database and database manager configuration parameters shown in Table 4.

Table 4. Configuration parameters that affect agent shared memory

Parameter	Description
aslheapsz	Application support layer heap size
rqrioblk	Client I/O block size

Agent private memory is affected by the database configuration parameters shown in Table 5.

Table 5. Configuration parameters that affect application shared memory.

Parameter	Description
applheapsz	Application heap size
agent_stack_sz	Agent stack size
stat_heap_sz	Statistics heap size
udf_mem_sz	UDF memory
sortheap	Sort heap size
rqrioblk	Query heap size
query_heap_sz	Client I/O block
drda_heap_sz	DRDA heap size

Figure 2 illustrates how DB2 uses the memory. Refer to Making DB2 Use All the Memory for more information.

Figure 2. DB2 memory usage

Back to top

Creating the database

In DB2 EEE, issuing a simple command from the DB2 Command Line Processor (CLP) prompt creates the database. The following example creates a database named mydb.

CREATE DATABASE mydb

After the above statement is executed, the database mydb appears in the instance directory.

Tablespaces

A tablespace is a logical layer between the database and the container objects that actually hold the data. DB2 EEE supports two types of tablespaces: system-managed space (SMS) and database-managed space (DMS). In SMS, the operating system's file manager allocates and manages the storage space. The containers used in SMS must be directories. In DMS, the database manager controls the storage space. The containers used in DMS must be a pre-allocated file or physical device, such as a hard disk drive. DBAs have the option to choose the type of the tablespaces required for the catalog, and for temporary and user tablespaces.

If you use the defaults when creating a database, the following system-managed tablespaces are created:

• System - To store catalog information in system tables. The catalog contains information about the definitions of the database objects (for example, tables, views, indexes, and packages), and security information about the type of access that users have to these objects.
• Temporary - To store the system temporary tables created during database processing.
• User - To store the database objects like tables and indexes created by a user.

The following example illustrates how database administrators can create a database using both DMS and SMS tablespaces. The following example creates the database named tested with a system-managed catalog tablespace and database-managed temporary and user tablespaces.

CREATE DATABASE tested CATALOG TABLESPACE PAGESIZE 4096 MANAGED BY DATABASE USING (FILE 'C:/DATA/CAT1.DAT' 3000, FILE 'D:/DATA/CAT1.DAT' 3000) EXTENTSIZE 12 PREFETCHSIZE 16 TEMPORARY TABLESPACE MANAGED BY SYSTEM USING ('C:/DATA/TEMP1' , 'D:/DATA/TEMP2') USER TABLESPACE MANAGED BY DATABASE USING ('//./PhysicalDrive1' 4096, '//./PhysicalDrive2' 4096)

PAGES: Rows of table data are organized into blocks called pages. DB2 supports 4 KB, 8 KB, 16 KB and 32 KB page sizes. You can choose different page sizes for the different tablespaces based on the type of the application that is used. By default DB2 uses the 4 KB pages.

PAGESIZE: A 4 KB-page size is specified for the catalog tablespace.

EXTENT: A set of pages grouped into allocation units called extents. In the above example, EXTENTSIZE defines 12 4 KB pages to be grouped together for an extent.

PREFETCH: It is a method that allows DB2 to read the data in advance to prevent wait times while the data is being retrieved.

PREFETCHSIZE: Defines the number of pages that can be prefetched.

The command LIST TABLESPACES SHOW DETAILS displays the above information and more, including the total pages, usable pages, used pages, free pages, and so on.

After you find out a tablespace ID from the LIST TABLESPACES command, you can find more information about the containers by issuing the command LIST TABLESPACE CONTAINERS FOR 2 SHOW DETAIL. The 2 is the ID of the tablespace. This command lists the containers for the specified tablespace. For every container it displays ID, name, type, total pages, useable pages, and whether the container is currently accessible or not.

With DB2, you can put index, data in separate tablespaces. Using different hard disk drives for the containers reduces the I/O contention. The following example creates the table employee. Regular data will be placed in tablespace TBS1 and index data will be placed in TBS2.

CREATE TABLE employee( E_NO INT NOT NULL, E_NAME CHAR(20) NOT NULL ) IN TBS1 INDEX IN TBS2

Back to top

Creating the buffer pools

DB2 uses buffer pools to cache the table and index data as they are being read or written to the hard disk drive. When a tablespace is created, it is assigned to the default buffer pool IBMDEFAULTBP. The DBA can map the buffer pool to the tablespace when creating or altering the tablespace. For every different page size tablespace, there should be a matching page size buffer pool.

CREATE BUFFERPOOL bp1 SIZE 4000 PAGESIZE 4096

The above SQL statement creates a buffer pool bp1 with 4000 4KB pages (4000*4K = 16000K).

Existing tablespace can now be altered to map to buffer pool bp1. The following example maps the emp tablespace to buffer pool bp1:

ALTER TABLESPACE emp BUFFERPOOL bp1

Buffer pools are allocated as a number of shared memory segments on the database server. Database records are read and updated in the buffer pool area of memory. All buffer pools are allocated when the database is activated. As an application requests data out of the database for a specific table or index, pages containing that data are transferred to the associated buffer pool from the hard disk. Pages are not written back to hard disk until one of the following occurs:

• All the applications are disconnected from the database
• A new page needs to be read into the buffer pool
• A page cleaner is available and is activated by the database manager

The buffer pool should be large enough to keep the required data in memory so that hard disk drive activity can be reduced. To take advantage of the increased buffer pool, you must rebind the applications for the optimizer to consider the buffer pool size when deciding its access strategy.

Back to top

Configuring the database instance

A DB2 EEE instance or the instance manager resides on the catalog node. The instance owning machine (that is, node 0) owns the shared directory where this information is stored. Other database-partitioned servers that are added to an instance are said to be participating in the instance.

Physical nodes You can configure physical nodes by installing the instance-owning database-partitioned server on one machine, and then installing a database-partitioned server on each of the other machines that participates in the partitioned database system.

Logical nodes Having more than one database-partitioned server on the same machine is known as a multiple logical nodes (MLN) configuration. MLN takes advantage of SMP architecture. Use the db2ncrt command to add database-partitioned server nodes (a logical node) on your instance to create an MLN configuration.

The following example adds a new database-partitioned server to the instance BENCH on the instance owning machine node1. The command sets this new node to node1 using logical port 1.

db2ncrt /n:1 /u:BENCH/db2inst1,bmdb2 /I:BENCH /m:node1 /p:1 /h:node1

Nodegroups A named subset of one or more database partitions is called a nodegroup. The subset that consists of more than one database partition is known as a multi-partition nodegroup. Multi-partition nodegroups can exist only within the database partitions that belong to same database.

The following example creates the nodegroup allnodes on all the database partitions:

CREATE NODEGROUP allnodes ON ALL NODES

This example creates the nodegroup snodes only on nodes 0 to 3 and and node 5:

CREATE NODEGROUP snodes ON NODE (0 TO 3, 5)

After you create nodegroups, you must associate them with tablespaces to specify the nodes where the data is going to be partitioned.

CREATE TABLESPACE org IN NODEGROUP s_nodes MANAGED BY DATABASE USING (DEVICE '//./PhysicalDrive1' 2000) ON NODE(0) USING (DEVICE '//./PhysicalDrive1' 2000) ON NODE(1) USING (DEVICE '//./PhysicalDrive1' 2000) ON NODE(5)

Figure 3 illustrates the relationship between the database objects for a given partition.

Figure 3. DB2 object-relationship diagram for a given database partition

Back to top

Tuning guidelines

DB2 includes tools for monitoring and analyzing database performance problems.

Explain tools Explain tools are very useful for identifying performance issues. Explain tools help determine whether additional indexes are required, a query needs to be rewritten, locking strategies are appropriate, etc. Explain snapshot information can be captured in the following ways.

Enabling the EXPLAIN SNAPSHOT special register Set the special register CURRENT EXPLAIN SNAPSHOT to YES to capture the snapshot for dynamic SQL statements. You can imbed the following statement in an application program or issue it interactively. By setting the special register, an Explain snapshot will be taken for any subsequent eligible dynamic SQL statements.

SET CURRENT EXPLAIN SNAPSHOT YES

Collecting explain snapshot on SQL procedures The stored procedure needs to set the EXPLSNAP register to ALL or YES in order to collect the access plans.

db2 PREP <procedure name="name"> EXPLSNAP {YES / ALL / NO } YES = static SQL ALL = static and dynamic NO = No snapshot</procedure>

When the package is prepared with EXPLSNAP YES or ALL, you can obtain the plan for the entire package.

The db2expln tool describes the access plan selected for static SQL statements in the package stored in the system catalog tables, and the dynexpln tool describes the access plan for dynamic SQL statements.

The following example uses db2expln to retrieve the access plan for the package named neword in the database mydb, created by user myuser to a output file output.file.

db2expln -d mydb -p neword -c myuser -o output.file

Utilities that affect performance DB2 provides various utilities, such as RUNSTATS, REORG and REORGCHK, to improve database performance.

The RUNSTATS utility updates the statistics in the system catalog tables to help with the query optimization process. With these statistics information database manager could make a decision that would increase the performance of an SQL statement. Use this utility after massive changes to the data and possibly after running REORG.

REORG eliminates the fragmentation in tables and indexes and may optionally order the rows of the table according to the order of the index. Use REORG when another utility, REORGCHK , indicates that REORG is needed and when the performance degrades over time - when data inserts, updates and deletes cause the clustering or space utilization to degrade.

REORGCHK examines the data in the system tables and applies formula to determine whether to reorganize of the table and its indexes. REORGCHK can also invoke RUNSTATS before examining the statistics. Run REORGCHK periodically, or when users notice degraded performance.

Examples

The following RUNSTATS example collects all possible statistics based on the indexes:

RUNSTATS ON TABLE bench.neword WITH DISTRIBUTION AND DETAILED INDEXES ALL

The following REORG example reorganizes the customer table using the system temporary tablespace TEMPSAPCE1 as a work area to store the intermediate results.

REORG TABLE bench.customer USING TEMPSPACE1

The following REORGCHK command examines whether the REORG is required and also updates the statistics on table bench.customer:

REORGCHK UPDATE STATISTICS ON TABLE bench.customer

Minimize I/O

Reduce I/O by taking the advantage of indexes, caching, reduced logging overhead, summary tables, and reduced fetches.

Use indexes Creating proper indexes improves the performance of the query. DB2 index adviser provides assistance in the designing of indexes on tables. It is useful in the following situations:

• Finding the best indexes for a problem query.
• Finding the best indexes for a set of queries subject to resource limits that optimally applied.
• Testing out an index on a workload without having to create the index.

Creating indexes helps avoid unnecessary table scans and sorts, speeds up frequently executed queries, and ensures uniqueness.

After you create indexes, use the Explain tools to ensure that DB2 is using them.

Catalog cache, package cache, and log buffer Set the catalog cache, package cache, and log buffer size to an appropriate size to improve performance. The catalog cache is used to store table descriptor information that is used when the tables, views, or alias have been referenced in previous statements. The package cache sets the amount of database global memory to be used for caching a package's static and dynamic SQL statements. The log buffer holds log records in storage until they are written to hard disk drive. Log records are written to the hard disk drive when one of the following occurs:

• A log buffer is full
• A transaction or a group of transactions commits

It is important that the log buffer be able to hold the amount of log space used by an average transaction. If not, you will experience poor logging performance due to a high number of log writes, mainly caused by the log buffer full condition.

Here are the commands you use to change these caching parameters:

db2 update db cfg for mydb using logbufsz 4096 db2 update db cfg for mydb using catalogcache_sz 1024 db2 update db cfg for mydb using pckacchesz 4096

Improving I/O parallelism The following are guidelines for improving I/O parallelism:

• Spread the data across multiple hard disk drives to reduce I/O wait time.
• Separate data and indexes to different tablespaces to improve the performance of OLTP and OLAP applications by reducing I/O contention.

As a rule of thumb for OLTP applications, use DMS tablespaces with multiple devices. Temporary and catalog tablespaces must be placed SMS.

Placement of log files For OLTP applications, it is very important to place the log files on a separate physical device where their high activity will not negatively impact other work on the same hard disk drive.

DB2 parallel I/O When reading data from or writing data to tablespace containers, DB2 may use parallel I/O if the number of containers in the database is greater than one. However, there are situations when it would be beneficial to have parallel I/O enabled for single container tablespaces, especially striped RAID devices.

Enabling intra-partition parallelism To enable the optimizer to choose the intra-partition parallelism you must set the intra_parallel database manager configuration parameter as shown here:

db2 update dbm cfg using intra_parallel YES

In conjunction to intra_prallel one need to set the dft_degree database parameter to specify the degree of the parallelism. This value should be set to greater than 1 to maximum to the number of processors that database partition can use.

The dft_degree database configuration parameter specifies the default level of parallelism for each database. A value of 1 means no intra-parallelism. A value of -1 means the optimizer determines the degree of parallelism based on the number of processors and the type of query.

Inter-partition parallelism gets enabled automatically when the database is partitioned across the nodes.

Summary tables A summary table is a table whose definition is based on a result of a query. As such, the summary table typically contains pre-computed results based on the data existing in the table or tables that its definition is based on. If the SQL compiler determines that a dynamic query will run more efficiently against summary table than the base table, the query executes against the summary table, and you obtain the results faster than you otherwise would.

How data is partitioned A partitioned key is a column, or group of columns, used to determine the partition in which the particular row of data is stored. You define a partition key for a table on the CREATE TABLE statement. If you do not specify a partitioning key for a table in a tablespace that is divided across more than one database partition in a nodegroup, DB2 creates one by default from the first column of the primary key. If no primary key is specified, the default-partitioning key is the first non-long field column defined on the table.

CREATE TABLE customer( C_ID INTEGER NOT NULL, C_FIRST VARCHAR(20) NOT NULL, C_MIDDLE VARCHAR(20) NOT NULL) IN CUSTOMER INDEX IN CUSTOMER_IDX PARTITIONING KEY(C_ID) USING HASHING;

Tuning using database snapshot monitors

Database monitors provide large amount of the information to the DBAs about database and the application accessing the database. Different monitor group switches are listed below:

• SORT - Amount of heap used, overflows and sorts performed
• LOCK - Number of locks held, deadlocks and lock waits escalations
• TABLE - measures of activity (rows read, rows written)
• BUFFERPOOL - Physical reads, logical reads and timing information
• STATEMENT - Number of commits, rollbacks, selects and failures

You can use these switches with the following commands to operate the snapshot monitor:

GET MONITOR SWITCHES UPDATE MONITOR SWITCHES USING <switch-name> ON/ OFF RESET MONITOR ALL/ FOR DATABASE <dbname>

Tuning buffer pools The buffer hit ratio indicates the percentage of time that the database manager did not need to load a page from hard disk drive in order to service a page request; that is, the page was already in the buffer pool. The greater the buffer pool hit ratio, the lower the frequency of hard disk drive I/O. The following formula calculates the buffer pool hit ratio from the buffer pool snapshot information:

(1- ((pool_data_p_reads + pool_index_p_reads) / (pool_data_l_reads + pool_index_l_reads))) * 100

If the hit ratio is low, increasing the number of buffer pool pages may improve performance.

Tuning the sort heap The sort average elapsed time from the snapshot data can be measures as total_sort_time / total_sorts. As sort performance improves, this average will decrease. Increasing the sort heap may eliminate the merge phases, hence the improvement in sort time.

Tuning the sort heap threshold (sortheapthres) Always keep the threshold high enough to fulfill the total sort heap required by all the applications, if not application request for a piped sort will be rejected. The percentage of piped sort requests that have been accepted by the database manager may be calculated by using the following formula:

100%*piped_sorts_accepted/piped_sorts_requested

A small percentage is the indication that the performance can be improved by increasing the sortheapthres parameter.

Tuning the package cache (pckcachesz) The package cache hit ratio can be calculated using the following formula:

(1 - (Package cache inserts / package cache lookups)) * 100

A smaller hit ratio indicates that the pckcachesz parameter should be increased.

Tuning the catalog cache Catalog cache hit ratio can be calculated by using the following formula:

(1 - (Catalog cache inserts / catalog cache lookups)) * 100

A smaller hit ratio is the indication that the catalogcache_sz should be increased.

Tuning the maximum number of concurrent applications (maxappls) The database configuration parameter maxappls specifies the maximum number of concurrent applications that can be connected to a database. Increasing the value of this parameter without lowering the maxlocks parameter or increasing the locklist parameter could cause you to reach the database limit on locks (locklist) rather than the application limit and as a result cause pervasive lock escalation problems. You should ensure that there are enough agents are available (maxagents) to the applications.

Tuning the maximum number of database manager agents (maxagents) The maxagents indicates the maximum number of database manager agents available at any given time to accept application requests. The value of the maxagents parameter should be the sum of the values for maxappls in each database allowed to be accessed concurrently.

Fast Communications Manager (FCM) DB2 can use FCM to communicate between agents working on the same request. The configurable parameters for the FCM are fcm_num_anchors, fcm_num_buffers, fcm_num_connect, and fcm_num_rqb. As the number of partitions goes up, the number of connection entries (fcm_num_connect) and BQS RQB (fcm_num_rqb) will go up. As the number of request blocks goes up, the number of FCM buffers (fcm_num_buffers) goes up. As the number of subsections (that is, the complexity of the query) goes up, the number of BDS RQB (fcm_num_rqb) and message anchors (fcm_num_anchors) will go up.

In an MLN environment, set DB2_FORCE_FCM_BP to YES, which allows DB2 to create FCM buffers in a separate memory segment. When the FCM buffers are created in a separate memory segment, the communication between FCM daemons of different logical partitions on the same physical node occurs through shared memory.

Disable file system caching for Windows File system caching is performed as follows:

• For DMS file containers (and all SMS containers), the operating system may cache pages in the file system cache.
• For DMS device container tablespaces, the operating system does not cache pages in the file system cache.

When working on Windows NT®, the registry variable DB2NTNOCACHE specifies whether or not DB2 will open database files with a NOCACHE option. If DB2NTNOCACHE=ON, file system caching is eliminated. If DB2NTNOCACHE=OFF, the operating system caches DB2 files. This applies to all data except for files that contain LONG FIELDS or LOBS. Eliminating system caching allows more memory to be available to the database so that the buffer pool or sort heap can be increased. To disable file system caching, you must set the DB2NTNOCACHE registry variable to ON:

db2set DB2NTNOCACHE=ON

Eliminating system caching allows more memory to be available to the database so that the buffer pool or sort heap can be increased.

Back to top

Sample Perl scripts for tuning DB2 on Linux, UNIX, and Windows

enable_snap.pl - Enables all the DB2 snapshots (that is, BUFFERPOOLS, TABLES, LOCKS, etc.).

perl gather_stats.pl <dbname> <time_in_secs> - This script gathers all the DB2 snapshot information into a file called dbrun.snap.

perl buffhitratio.pl dbrun.snap - This script provides the hit ratio for the data and index pages of every buffer pool.

$ perl buffhitratio.pl dbrun.snap BP Name Logical Rd Physical Rd Writes Async Rds Async Wrts Hit Ratio% ------------------------------------------------------------------------------ DATA 1538 133 19 117 0 91.35% INDX 3084 38 2 0 0 98.77% IBMDEFAULTBP DATA 1538 133 19 117 0 91.35% IBMDEFAULTBP INDX 3084 38 2 0 0 98.77% IBMDEFAULTBP DATA 1538 133 19 117 0 91.35% IBMDEFAULTBP INDX 3084 38 2 0 0 98.77% HINT : It is good have high hit ratio; if not increase your bufferpool size.

perl cathitratio.pl dbrun.snap - This script provides the hit ratio for the catalog cache.

$ perl cathitratio.pl dbrun.snap Cache Inserts Cache Lookups % Hit Ratio ------------------------------------------------------- 10 20 50% HINT : If hit ratio is less than increase catalog cache size

perl packhitratio.pl dbrun.snap - This script provides the hit ratio for the package cache.

$ perl packhitratio.pl dbrun.snap Package Cache Inserts Package Cache Lookups % Hit Ratio -------------------------------------------------------------- 1 244 99% HINT : Increase the catalog cache size if the hit ratio is less

perl sortthresh.pl dbrun.snap - This script provides the number of sorts serviced and number of sorts rejected.

$ perl sortthresh.pl dbrun.snap Piped sorts requested Piped sorts accepted % Serviced Num Sorts Rejected -------------------------------------------------------------------------------- 1 1 100% 0 HINT : If % Serviced is low then increase sheapthresh If Num Sorts Rejected is higher then increase sortheap or sheapthreash is too small

perl sorttime.pl dbrun.snap - This script provides the average elapsed time per sort.

$ perl sorttime.pl dbrun.snap Total Sort Time (ms) Total Sorts Average Elapsed Time per sort ----------------------------------------------------------------- 100 10 10 HINT : As the performance improves, this average will decrease

perl tbs_usage.pl <dbname> - This script provides the free and used space for all the tablespaces for a given database.

$ perl tbs_usage.pl tpcd connect to ebuis Database Connection Information Database server = DB2/LINUX 7.2.2 SQL authorization ID = EBUIS Local database alias = EBUIS Ts_Id Ts_Name Total Used Free Number_of_files ------------------------------------------------------------------ 0 SYSCATSPACE 10M 10M 0M 1 1 TEMPSPACE1 0M 0M 0M 1 2 USERSPACE1 0M 0M 0M 1 3 BENCHTEMP 1000M 0M 999M 2 4 ORDERS_DATA 1000M 95M 903M 2 5 ORDERS_INDEX 1000M 18M 981M 2 6 LINEITEM_DATA 1000M 398M 600M 2 7 LINEITEM_INDEX 1000M 138M 861M 2 8 BENCHINDX 1000M 22M 977M 2 9 BENCHDATA 1000M 94M 905M 2

perl locklist.pl dbrun.snap <dbname> - This script displays the total lock list, average lock list in use and average lock list utilization.

$ perl locklist.pl dbrun.snap ebuis Locklist (4K Page) Lock_list_in_use(4K Page) Lock list utilization -------------------------------------------------------------------------------- 100 6 6% HINT : If Lock list utilization is low then decresae the locklist If Lock list utilization is more then increase the locklist

Back to top

Downloads

Name	Size	Download method
db2_tune.ZIP	12KB	HTTP
db2_tune.tar	64KB	HTTP
readme.txt	2KB	HTTP

	Information about download methods

Resources

• IBM DB2 Universal Database Administration Guide: Planning and Implementation
• IBM DB2 Universal Database Extended Enterprise Edition for Windows Quick Beginnings
• IBM DB2 Universal Database Administration Guide: Performance Version 7
• Yongli An and Peter Shum, "DB2 Tuning Tips for OLTP Applications"