What problems exist in DB2 performance tuning and how to solve them?

This article mainly describes the problem of DB2 Performance Tuning. We mainly answer the problems in DB2 performance tuning in a one-Question-and-one manner, the following is a description of the DB2 Performance Tuning problem. I hope it will help you in your future studies.

Hardware environment: AIX memory 8 GB) EMC140G) Hard Drive size 140 GB)

Data Structure table structures can be divided into four types ):

Category 1:-Business tables with a maximum data volume of about million)

Category 2: The primary table has about 10 million data records. The primary table is a tdBase table and a sub-table of this table)

Category 3: there are 10 index table index tables that are obtained by splitting entName in the tdBase table. There are only 4 columns, and the maximum data volume is about)

Category 3: about 100 system tables

Now the database structure has been designed. It was previously in MS and is now ready to be migrated to db2. Please give us some suggestions. Mainly query performance, the most important is the cable

Query the association between the cited table and the primary table.

How Should tablespace be allocated? What is the size of the buffer pool? How are containers distributed? How can I configure the database to achieve maximum concurrency? Active memory management

Do you still need to specify it by yourself? Please give me some suggestions. Thank you.

Best Answer: mdkii

This problem is complicated.

Performance Tuning is divided into several layers:

Storage layer,

1. It is recommended that your lun be reasonably divided. For raid, the Best raid group contains more disks.

2. It is recommended that a container only contain a complete raid group. Do not allow multiple containers to compete for a raid group.

3. Use a separate disk or raid group for data logs.

Operating System:

1. Control your file cache. Do not make too many file cache occupy your memory, resulting in frequent pagesp in and pagesp out,

This seriously affects your performance.

2. Ensure that sufficient aioserver is configured.

Database layer:

1. stmm is recommended for version 9 unless you are a very experienced DB2 engineer.

2. Putting a table into a tablespace by classification does not affect performance. However, if there are many indexes, we recommend that you use a separate tablespace and bufferpool for the index.

3. PREFETCHSIZE should be equal to extentsize * The number of containers.

4. enable active page clearing

5. Start tablespace parallel IO

6. For smp machines, enable intra-partition parallelism.

7. If possible, perform a partition on a large table. For complex dimension queries, you can create a fact table as an MDC table.

SQL:

1. We recommend that you follow the db2advis suggestions to create an appropriate index for SQL statements that affect the complexity.

2. Use runstats regularly to ensure that your statistics are up-to-date.

3. Use db2exfmt and other tools to analyze the execution plan for SQL statements that still have performance problems, and try to avoid the words Residual Predicates in the query plan.

After the system is running,

Use the AIX system tools such as topas, vmstat, and iostat to check whether your system is running normally.

Use Database snapshots and other tools to view the buffer hit rate, sort overflow, and lock wait of the database.

Make sure that the database is running properly.