How to adjust the performance of DB2 Databases

How to adjust the DB2 database
Sharing performance and practical skills. If you have read the relevant materials, it may be helpful for you to learn more.

1. SQL Cost Analysis
In many cases, a simple SQL statement may make DB2 in an embarrassing state. Adjusting parameters does not solve this problem. Since it is difficult for DBA to change the status quo of these spam SQL statements, the following situations are left for DBA:
(1). change or add Indexes
(2). Change Clustering
(3). Change catalog statistics.
Note: cost of an SQL statement = resource cost of each execution * number of executions.
Contents
Previously, the challenge for DBAs was to find statements with high cost and try their best to reduce the cost. You can use the DB2 explain tool or
DB2 udb SQL event monitor data to analyze the cost of SQL statements. Especially for SQL event monitor data analysis, but this requires
It takes a lot of energy and time.
Generally, the DBA process is as follows:
(1). Create an SQL event monitor, write to file:
$> DB2 "create event monitor sqlcost for statements write ..."
(2). Activate the event monitor (Be sure ample free disk space is available ):
$> DB2 "set event monitor sqlcost state = 1"
(3). Let the application run.
(4). Deactivate the event monitor:
$> DB2 "set event monitor sqlcost state = 0"
(5). Use the DB2-supplied db2evmon tool to format the raw SQL event monitor data (hundreds of megabytes of free disk space may be required depending on SQL throughput rates ):

$> Db2evmon-DB dbname-EVM sqlcost
> Sqltrace.txt
(6). Browse through the formatted file scanning for unusually large cost numbers, a time-consuming process:
$> More sqltrace.txt
(7 ). undertake a more complete analysis of the formatted file that attempts to identify unique statements (independent of literal values), each unique statement's frequency (how many times it occurred ), and the aggregate of its total CPU, sort, and other resource costs. such a thorough analysis cocould take a week or more on just a 30-minute sample of application SQL activity.

To find the corresponding SQL statement as quickly as possible, we can consider the following methods:
For 4th tip: calculate the number of rows retrieved from a table for each transaction. If the value is very high, you can find the corresponding statement.
Needle
For the first tip: Calculate
Asynchronous read Percentage and physical I/O read rates. If a tablespace has
High asynchronous read Percentage and higher than average physical I/O read rates, it is possible that this
Tablesapce contains table scan. You can find the corresponding table in tablespace from the catalog (if
There is only one table on tablespace, so it is easy to locate), and then find the relevant table from SQL event monitor. This can also narrow down the scope.
Observe DB2 explain information to find suspicious places. Sometimes statements that are frequently executed and cost-effective can occupy system resources!
Many times, we can make full use of the tools! This saves time and effort.
Staying in tune
It should be noted that performance optimization should not only eliminate those good SQL statements, but also ensure a reasonable physical architecture to ensure high-performance results and memory allocation in the pool and heap, i/O are evenly distributed between disks.
2. Buffer Pool Optimization
Contents
Generally, the system memory can reach 2 GB, 4 GB, and 8 GB, but the default ibmdefabp bp of DB2 is only 16 Mb. In this case, you can create
Buffer Pool to syscatspace catalog tablespace, a buffer pool
For tempspace tablespace, at least two bp_rand and bp_seq. Random Access tablespaces should have one
Buffer Pool to cope with random objectives, which is bp_rand. sequential access
Tablespaces (with asynchronous prefetch I/O) should establish a buffer pool
Sequential objectives, bp_seq. Other buffer pools can also be created, depending on the application. For example, you can create a large enough
Buffer Pool to store data frequently accessed by hot spots. Sometimes you need to create a single buffer pool for a large table.
A small buffer pool will lead to a large number of unnecessary physical I/O. A large biffer pool may generate system paging, increasing unnecessary CPU management memory overhead.
Buffer Pool
The buffer pool size of a system should be "appropriate "! It is appropriate when diminishing return is reached. If not
Automatic tools should test the Buffer Pool performance in a systematic manner, such as the hit rate, I/O times, and physical I/O read ratio until the appropriate state is reached. Of course, applications are changing, so they are optimal.
The status is not non-edge and should be evaluated regularly.
3. tablespace Analysis
Tablespace snapshot has great value for understanding which data is accessed and how it is accessed.
DB2 "Get snapshot for tablespaces on dbname"
Note the following for each tablespace:
What is the average read time (MS )?
What is the average write time (MS )?
What percentage of the physical I/O is asynchronous (prefetched) vs. synchronous (random )?
What are the buffer pool hit ratios for each tablespace?
How many physical pages are being read each minute?
How many physical and logical pages are being read for each transaction?
For all tablespaces, note:
Which tablespaces have the slowest read and write times? Why?
Containers on slow disks? Are container sizes unequal?
Attributes, asynchronous versus synchronous access, consistent with expectations?
Randomly Read tables shoshould have Randomly Read tablespaces, meaning high synchronous read percentages, usually higher buffer pool hit ratios, and lower physical I/O rates.

For each tablespace, note that the prefetch size is a multiple of the extent size. If necessary, you can modify the prefetch size of tablespace.
Display tablespace information: DB2 "list tablespaces show detail"
Display containers information: DB2 "list tablespace containers for n show detail"
4. Table Access
To find the row read by each query,
1) DB2 "Get snapshot for database on dbname"
The sum of commit statements attempted + rollback statements attempted
2) DB2 "Get snapshot for tables on dbname"
Zone
Returns the row read from the transaction.
Divide the number of rows read by the number of transactions (rowspertx). OLTP
Generally, every transaction reads 20 rows from a table. If a transaction can read hundreds of rows of data, a table scan may occur. You may need to check whether the index is required. Simple
Running runstats to collect information.
Sample output from "Get snapshot for tables on dbname" follows:
Snapshot timestamp = 09-25-2000 4:47:09. 970811
Database Name = dgidb
Database Path =/fs/inst1/inst1/node0000/sql00001/
Input Database alias = dgidb
Number of accessed tables = 8
Table list
Table schema = inst1
Table name = dgi_sales _ logs_tb
Table type = user
Rows written = 0
Rows read = 98857
Overflows = 0
Page reorgs = 0
If there is a high overflows, you need to re-org table. When the width of a row changes, DB2 may put a row on a different page.
5. Sort memory
OLTP should not have a large-scale sort, because sort will consume a lot of CPU, I/O and time.
The default sortheap = 256*4 k = 1 m, which is generally enough. You should know the number of sort overflows and the sort number of each transaction.
DB2 "Get snapshot for database on dbname"
View the following items:
Total sort heap allocated = 0
Total sorts = 1
Total sort time (MS) = 8
Sort overflows = 0
Active sorts = 0
Commit statements attempted = 3
Rollback statements attempted = 0
Let transactions = commit statements attempted + rollback statements
Attempted
Let sortspertx = total sorts/transactions
Let percentsortoverflows = sort overflowws * 100/total sorts
If percentsortoverflows exceeds 3%, it may indicate that there is a serious sort SQL in the application. Because a large number of overflows indicate that a large number of sort appear, which is ideal when it is zero or less than 1.
If a large number of overflows appear, the right option is to increase sortheap, but this only hides the problem. The fundamental solution is to locate SQL and reduce the sort cost by adjusting SQL, index, and clustering.
If sortspertx is greater than 5, it indicates that there are too many sort instances for each transaction. Some applications may execute a large number of small composite queries without Overflow, but there is a small period of time. But it will consume a lot of CPU. You also need to adjust SQL, index, and clustering to solve the problem.
6. Temporary tablespaces
Temporary tablespace generally has three ininers on different disks, which can achieve parallel I/O and improve the performance of sorts, hash joins, or other actions on tempspace.
DB2 "list tablespaces show detail" to view the container of the temporary tablespace:
Tablespace id = 1
Name = tempspace1
Type = system managed Space
Contents = temporary data
State = 0x0000
Detailed explanation: normal
Total pages = 1
Useable pages = 1
Used pages = 1
Free pages = not applicable
High water mark (pages) = not applicable
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 96
Number of containers = 3
Here, three containers are available, and the prefetch size is three times the extent size. For the best parallel performance, the best prefetch size is a multiple of extent size. A common multiple is the number of containers.
DB2 "list tablespace containers for 1 show detail"
You can see the definition of containers.
7. Locks
The default locktimeout =-1, that is, do not set the lock timeout, which may be a disaster in OLTP. We need to set a relatively small value, such as locktimeout = 10 or 15 seconds.
View the command:
DB2 "Get dB CFG for dbname ",
Continue to view the following information:
Lock timeout (Sec) (locktimeout) =-1
And the application staff will understand whether they can process timeout in the program. Then set:
DB2 "Update dB CFG for dbname using locktimeout 15"
You can view the number of lock wait instances in the system, the amount of memory used by lock wait time and lock list instances.
DB2 "Get snapshot for database on dbname"
View:
Locks held currently = 0
Lock waits = 0
Time Database waited on locks (MS) = 0
Lock list memory in use (bytes) = 576
Deadlocks detected = 0
Lock escalations = 0
Exclusive lock escalations = 0
Agents currently waiting on locks = 0
Lock timeouts = 0
If the memory size (bytes) of the lock list exceeds 50% of that of the locklist, you need to increase the number of locklists. The locklist is calculated by 4 K.
8. Maximum open files
Maximum number of opened files
DB2 limits the number of files opened at the same time. The database parameter "maxfilop" specifies the number of files opened concurrently. If this number is reached, DB2 will start to close and open the tablespace file, including raw device, which will reduce the SQL reflection time and CPU usage.
Run the following command to check whether a file is closed:
DB2 "Get snapshot for database on dbname"
Check "database files closed = 0"
If the value is not zero, you need to modify maxfilop,
DB2 "Update dB CFG for dbname using maxfilop N"
9. Agents
Ensure that there is sufficient agent to cope with system load.
Command: DB2 "Get snapshot for Database Manager"
This
Observe "agents waiting for a token" or
"Agents stolen from another application", if there is a value, you need to increase the agent value of DB manager, that is
Is to modify the value of maxagents and/or max_coordagents.
High water mark for agents registered = 7
High water mark for agents waiting for a token = 0
Agents registered = 7
Agents waiting for a token = 0
Idle agents = 5
Agents assigned from pool = 158
Agents created from empty pool = 7
Agents stolen from another application = 0
High water mark for coordinating agents = 7
MAX agents overflow = 0
10. Monitor Switches
Enable the monitor switch to obtain the performance information. The detailed command is as follows:
DB2 "Update monitor switches using lock on sort on bufferpool on uow on table on statement on"
View the execution plan:
Db2expln:
Db2expln-D dbname-C pkgowner-P pkgnmae-O expln. Out
Dynexpln:
Dynexpln-d eos-Q "select * From eosmenu"-g-T
Dynexpln-d eos-F query. SQL-O out.txt
Update statistics:
Runstats:
DB2 runstats on table songxn. eosmenu and indexs songxn. ix1, songxn. ix2 allow read access
DB2 runstats on table songxn. eosmenu with distribution and detailed index all
Adjust the optimization level:
(0, 1, 2, 3, 5, 7, 9 ):
Change dft_queryopt (db cfg). The default value is 5.
SQL: Set current query optimization = 3
Index:
Index creation:
Create unique index ind1 on Vicky. Staff (Dept, lastname)
Create unique index ind2 on Vicky. emplyee (empno) include (lastname, salary)
Fields in include are not included in the sorting range.
Cluster Index:
The record sequence is consistent with the index sequence.
Cretae index ind3 on Vicky. Staff (Dept) Cluster
Scan Mode:
Index scan, full index scan (index only access), relation Scan
Query index:
Select indname from syscat. indexes where tabname = 'customer'
Index recommender:
Db2advis-D dbname-I query. SQL |-s "SQL stmt"-O advis. Out
Reserve space on the data page:
Alter table Vicky. Staff pctfree 30 (30% reserved)
Load from staff. ixf of ixf modified by pagefreespace = 30 Replace into Vicky. Staff
Buffer Pool and IO
The default value is ibmdefabp bp.
Create a buffer pool:
DB2 create bufferpool bpname size 1000 pagesize 4 K
Alter tablespace tbname bufferpool bpname
When the dirty page exceeds chngpgs_thresh (%, db cfg), the buffer pool is cleared and written back.
Extended Buffer Pool:
Estore_seg_sz & num_estore_segs (db cfg)
DB2 alter bufferpool ibmdefaultbp [not] extended storage
I/O Server:
Num_ioservers (db cfg): Generally the number of disks + 2
Data Reorganization:
Reorgchk:
DB2 reorgchk update statistics on table all this statement is also used to run runstats on all tables
DB2 reorgchk current statistics on table Vicky. Staff
Table statistics:
Card: number of records
OV (erflow): number of records in Overflow
NP (AGEs): number of pages containing records
FP (AGEs): Total number of pages occupied by the table
Tsize (bytes): Table size
Index statistics:
Leaf: Number of leaf pages
Eleaf: Number of blank leaves
Ndel: Number of RID deleted
Lvls: index level
Isize: average index Length
Keys: number of different index values
Table Reorganization:
DB2 reorg table Vicky. Staff index ind1 use tempspace2 indexscan
Index Reorganization:
DB2 reorg indexes all for table Vicky. Staff _ options _
Options: allow read | write | no access, cleanup only pages | all (no re-indexing structure, only empty index pages are recycled)
Online table reorganization (V8 + ):
DB2 reorg table Vicky. Staff index Vicky. ind2 inplace allow write access
DB2 reorg table Vicky. Staff index Vicky. ind2 inplace pause | resume
DB2 list history reorg all for dbname
MQT
MQT:
Create MQT:
Create Table Vicky. mqt1
(Select name, location, salary
From Vicky. Staff, vicky.org
Where staff. Dept = org. deptnumb and salary> 20000
) Data initially deferred refresh deferred | immediate
Enable | disable Query Optimization
Refresh table Vicky. mqt1
Use MQT:
Runstats on table Vicky. mqt1
Update dB CFG for sample using dft_refresh_age any
Parallel Processing
Set parallel processing:
Update dBm CFG using intra_parallel Yes
Update dB CFG for Eos using dft_degree any
Parallel Processing limit:
Application level:
Set runtime degree for (25) to 4
Set runtime degree for all to 6
Instance level:
Update dBm CFG using max_querydegree 6
MDC table:
Create Table Vicky. Sales
(Yearandmonth char (4 ),
Region char (20 ),
Product char (2 ),
Sales bigint
) Organize by dimensions (yearandmonth, Region)
Create Table Vicky. Table1
(Col1 char (10 ),
Col2 char (10 ),
Col3 char (10 ),
Col4 integer,
Col5 decimal (10, 2 ),
) Organize by dimensions (col1, (col2, col3), col4)