DB2 Performance Tuning -

Taking Snapshots -
Determining if a database parameter needs to be tuned will usually require examining statistics
from a database snapshot. In order to take a snap shot you will need to make sure the different
monitors are on before hand. The general procedure to get a snapshot is to do the following -
Verify what monitors are on -
db2 - get monitor switch
Turn on monitors
db2 - update monitor switches using bufferpool on
db2 - update monitor switches using lock on
db2 - update monitor switches using sort on
db2 - update monitor switches using statement on
db2 - update monitor switches using table on
db2 - update monitor switches using timestamp on
db2 - update monitor switches using !"# on
or - update dbm cfg using dft$mon$bufpool on dft$mon$lock on dft$mon$sort on
dft$mon$stmt on dft$mon$table on dft$mon$uow on health$mon on%
db2 - get monitor switches
&lear the monitors -
db2 - reset monitor all
-- run the application in question --
&ollect the 'napshot -
There are a number of snapshots you can take -
'napshot Type &ommand
(ocks 'napshot db2 get snapshot for locks on bgdb)
Database *anager
'napshot
db2 get snapshot for dbm
Database 'napshot db2 get snapshot for database on bgdb)
Tablespace 'napshot db2 get snapshot for tablespaces on bgdb)
+ufferpool 'napshot db2 get snapshot for bufferpools on bgdb)
,pplications db2 get snapshot for applications on bgdb)
Dynamic '-( db2 get snapshot for dynamic sql on bgdb)
Table 'napshot Db2 get snapshot for tables on bgdb)
.or performance Issues we usually only care about the dbm/ db/ and bufferpool snapshots -
db2 - get snapshot for all databases 0 1tmp1snap.out
db2 - get snapshot for dbm 00 1tmp1snap.out
db2 - get snapshot for bufferpools 00 1tmp1snap.out
db2 - reset monitor all
db2 - terminate
DB2 Parameters
Buffer Pools -
Recommendations -
The bufferpools main function is to improe database performance since it is the area of
memory used by the database to read in and modify pages. 'o it is usually beneficial to
make the bufferpools as large as possible without causing system stability issues. +y default
we set +23 systems to hae a bufferpool si4e of 5.62+ which should be sufficient for most
systems/ howeer if your system has the memory you can experiment with increasing this.
Changing the Parameter -
7ou can either update the bufferpool si4e directly in syscat.bufferpools/ or by setting the
alue of 83,29' to -5 and then by modifying the +!..3,29 alue
db2 connect to D+$8,*9
db2 select : from syscat.bufferpools
db2 alter bufferpool I+*D9.,!(T+3 si4e -5
db2 connect reset
db2 update db cfg for dbname using +!..3,29 bigger$alue
db2 terminate
Investigative Steps -
To determine if the buffer pool si4e is big enough/ you will need to examine the bufferpool
hit ratio to see if the database is pulling too many pages from disk rather then memory. It is
indicates the percentage of time that the database manager did not need to load a page from
disk in order to serice a page request because the page was already in the buffer pool. The
greater the buffer pool hit ratio/ the lower the olume of disk I1". To calculate this you will
need to look at a few alues from the bufferpool snapshot. &ollect that snapshot while
taxing the system by running -
db2 - update monitor switches using bufferpool on
db2 - get monitor switches
db2 - reset monitor all
-- run +23 ;obs --
db2 - get snapshot for bufferpools 00 1tmp1snap.out
db2 - reset monitor all
db2 - terminate
&alculate the bufferpool hit ratio by examining <logical reads= and <physical reads= as
follows -
> 5 - >
> buffer pool data physical reads ? buffer pool index physical reads @
1
> buffer pool data logical reads ? buffer pool index logical reads @
@
@ : 5))A
The hit ratio should be BCA or more. The higher the better.
,part from simply increase the buff pool si4e/ another way to improe performance is to
create multiple bufferpools for frequently accessed large tables.
Numer of !gents " #!$!%&NTS' #!$(C))RD!%&NTS'
N*#(INIT!%&NTS + -
Recommendations -
+y default we like to enable the connection concentrator and connection pooling by setting
the alue of max_connections greater than the alue of max_coordagents. This helps to
reduce the load on oerall system resources. Typically you will need to increase the
maxagents based on how many concurrent blocks you plan to boot. .or reference a system
booting D)) blocks at a time typically will need a maxagents setting of 5C)).
Changing the Parameter -
db2 - update dbm cfg using *,E,298T' Falue0
db2 - update dbm cfg using 8!*$3""(,298T' Falue0
db2 - update dbm cfg using 8!*$I8IT,298T' Falue0
db2 - terminate
max_coordagents is usually set to maxagents - num_initagents . 7ou can set it to a real
alue like aboe/ or set it to the default of maxagents num_initagents ia -
db2 - update dbm cfg using *,E$&""GD,298T' -5
Investigative Steps -
(ook at the snapshot for the database manager > db2 - get snapshot for database manager @.
If you see that <,gents waiting for a token= or <,gents stolen from another application= is
not equal to ) then you may need to increase maxagents to allow more agents to be
aailable to the db manager.
7ou can also look at the <High water mark= alues for connections/ agents/ coordinating
agents/ etc. The output will look similar to this -
High water mark for connections = 1205
Application connects = 5409
Secondary connects total = 3
Applications connected crrently = 31
Appls! e"ecting in d# manager crrently = 0
Agents associated with applications = 3
$a"imm agents associated with applications= 1194
$a"imm coordinating agents = 1194
!sing the connection concentrator you can get a situation where the database appears to
hang. If thereIs a new incoming request/ but all agents are occupied sering a transaction/
the incoming transaction will appear to hang. This is because no agent is aailable to serice
your request. #hen you see situations/ such as user requests that appear to hang your
max_coordagents setting might not be high enough. This is typically controlled by
max_agents/ so increasing maxagents will increase max_coordagents.
,og Buffer Si-e . ,)%B*/S0
Recommendations -
This is the amount of db shared memory to use as a buffer for log records before writing
these records to disk. (og records are written to disk when a transaction commits/ the log
buffer is full/ or due to a db manager eent
Increase if seeing high read actiity on the disks that house the logs/ or high disk utili4ation
in general > iostat will show this @. Increase dbheap too since the log buffer area uses space
controlled by the dbheap parameter
+y default we generally set ("2+!.'J to 52K.
Changing the Parameter -
The (og buffer is in D kb pages
db2 - update database cfg for D+$8,*9 using ("2+!.'J 2C6
db2 - terminate
Investigative Steps -
(ook at the db snapshot/ and examine the (og pages read ersus the (og pages written.
Ideally the read pages should be ) with numerous writes. If there are a bunch of reads you
will need to increase the +uffer.
!pplication 1eap Si-e . !PP1&!PS0
Recommendations -
This is the amount of memory needed to process a request gien to a agent from an
application. +y default we set this to 2C)).
Changing the Parameter -
db2 - update db cfg for D+$8,*9 using applheaps4 2C6
Investigative Steps -
If the db2diag.log shows an error from an app complaining of not haing enough heap
storage then increase this parameter.
Sort 1eap Si-e and Sort 1eap Threshold " S)RT1&!P 2
S1&!PT1R&S+ -
Recommendations -
&ertain memory settings of db2 can now be automatically maintained by the database
manager. #e recommend setting both sortheap and sheapthres to ,!T"*,TI&. This
allows db2 to modify the alues as needed.
Changing the Parameter -
db2 - update db cfg for D+$8,*9 using '"GTH9,3 Falue0
db2 - update dbm cfg using 'H9,3THG9' Falue0
db2 - terminate
a alue of ,!T"*,TI& aboe will make db2 maintain both heaps.
Investigative Steps -
db2 - monitor switches using sort on
db2 - get snapshot for database on D+8,*9
&alculate the number of sorts per transaction and the A of sorts that oerflow the memory
that was aailable to them -
Sorts%er&ransaction =
'&otal Sorts(
)
'*ommit statements attempted + ,oll#ack statements attempted(
%ercentSort-.erflow = 'Sort o.erflows / 100 ( ) '&otal sorts(
If 'orts3erTransaction 0 C there might be too many sorts per transaction. If
3ercent'ort"erflow is 0 LA there may be serious large sorts occurring. Increasing
'"GTH9,3 might help the symptom/ but correct indexes will sole the problem.
,ocks ",)C3,IST'#!$,)C3S' and ,)C3TI#&)*T +
Gecommendations -
The locklist is the mount of memory allocated to the list of locks and maxlocks is the
percentage of that list that an application must hold before the database throws up a lock
escalation. It also escalates locks when the locklist becomes full. ,gain/ in D+2 B these
alues can be automatically controlled by the database manager which we suggest. #e
generally leae locktimeout at -5 leaing lock timeout detection turned off. That means
applications will wait for a lock until they hae it granted or until a deadlock occurs
&hanging the 3arameter -
db2 - update db cfg for db$name using ("&M(I'T Falue0
db2 - update db cfg for db$name using *,E("&M' Falue0
db2 - update db cfg for db$name using ("&MTI*9"!T Falue0
db2 - terminate
Inestigatie 'teps -
(ook at the lock entries from the db snapshot. If the <(ock (ist memory in use >+ytes@=
exceeds C)A of the defined locklist si4e/ then increase the number of DM+ pages in the
locklist. (ock escalations/ timeouts and deadlocks will indicate potential system or
application problems. The locking problems normally indicate concurrency problems in a
application running on the serer.
#!$/I,)P -
If this alue is small/ db2 spends extra cpu time closing down files/ being a good citi4en and
giing oer resources to other operating system processes. If the number of files closed is
greater then 4ero in a db snapshot/ then you can incrementally increase this alue to stop db2
from closing files.
C!T!,)%C!C1&(S0
This parameter helps db2 to shorten '-( statement plan preparation times. If the db/
tablespace/ tables/ indexes/ and iews/ are all in the cache db2 can learn about the plan faster.
7ou should strie for a high package hit ratio of BCA or better. , db snapshot will show the
ratio ia - 5)) - >> &atalog cache inserts E 5))@ 1 &atalog cache lookups @ . ,lso increase this if
the &atalog cache oerflow/ and cache heap full is greater then 4ero. D+H9,3 and
&,T,("2&,&H9$'J should be increased in tandem.
#INC)##IT
Helps to group i1o to db2 logs together. .irst determine how many transactions per second the
db is performing/ and diide this by 5) - &ommits ? Gollbacks > Transactions @ 1 5) N
*I8&"**IT.
In a D# db *I8&"**IT should be set to 5.
INTR!(P!R!,,&,
"(T3 databases should hae I8TG,$3,G,((9( set to 8"/ but D# databases should set it to
79' to enable &3! parallelism/ and hae D.T$D92G99 set to ,87 or -5.
#!$(4*&R5D&%R&&
"(T3 databases should set this to 5/ and D# databases should set this equal to the number of
&3!s on the system/ to preent a user from setting their current degree to a alue to high.
/C#(N*#(B*//&RS
.&* *anages communications between parallel agents. , shortage of buffers will cause .&*
resource shortage messages in the db2diag.log file. , db snapshot will show existing usage
.ree .&* buffers low water mark 1 .ree .&* buffers : 5)) N 3ercentage of .&* +uffers
!sed
.ree .&* message anchors low water mark1.ree .&* message anchors : 5)) N 3ercentage
of .&* *essage ,nchors !sed .ree .&* connection entries low water mark 1 .ree .&*
connection entries : 5))
N 3ercentage of .&* &onnection 9ntries !sed
.ree .&* request blocks low water mark 1 .ree .&* request blocks : 5)) N
3ercentage of .&* Gequest +locks !sed
If any alues are less then 5CA youIll need to increase the initial allocation until all low water
marks are greater then 5CA.
D/T(4*&R5)PT -
Defines how long db2 will take analy4ing its sql execution plans. The smaller the alue the less
time db2 spends. 'etting this to 5 is ok in "(T3 dbs but in D# dbs because of the complex sql/
setting this to O or B to spend a little more time on the access plans can be desired.
C1N%P%S(T1&RS1 -
Defines the threshold 1 percent of dirty pages needed in the buffer pools before the
8!*$I"$&(9,89G' begin to write changed pages out to disk. The default of 6) is good for
D# dbs/ but "(T3 dbIs might want to lower this to C) or D). If the alue is too high/ when the
db finally does write out pages/ it can be oerwhelming to some apps/ so doing it fewer times at
smaller olumes can help reduce this. 8o one sets it below L) though.
N*#(I)(C,&!N&RS -
This alue deals with asynchronous writes/ and effects the asynchronous write percentage/
which should be B)A or higher. .ormula N > >,synchronous pool data page writes ?
,synchronous pool index page
writes @ x 5)) @ 1 > +uffer pool data writes ? +uffer pool index writes @
2enerally setting this to the P of &3!s should be sufficient
N*#(I)(S&R6&RS -
!sed to prefetch data into db2Is buffer pools. To set this alue/ add up the number of physical
disks on the db2 serer/ and use that number.
R!ID Disk and Parallelism -
In a D# enironment using regular '&'I or ID9 disk dries/ tablespaces should hae multiple
containers on different disks. .or G,ID deices where seeral disks appear as one to the
operating system/ be sure to do the followingQ
5. db2set D+2$'TGI39D$&"8T,I89G'N79' >do this before creating tablespaces
or before a redirected restore@
2. db2set D+2$3,G,((9($I"N: >or use TablespaceID numbers for tablespaces
residing on the G,ID deices R for example D+2$3,G,((9($I"ND/C/6/O/K/5)/52/5L @
L. ,lter the tablespace 3G9.9T&H'IJ9 for each tablespace residing on G,ID deices
such that the 3G9.9T&H'IJ9 is a multiple of the 9ET98T'IJ9. *ost companies use a
multiple of three to fie. .our is my faorite.
3roper container placement and alignment of the extent with the raid stripe si4e can reduce I1"
times by C) percent. Gemember/ I1" is still the slowest component in the transaction mix.
)ther DB2 Performance Tools -
DB2PD -
db2pd -osinfo - will display information about the system being run on
db2pd -db dbname -pages - This will show what is using the bufferpools/ you can pass
the bufferpool id to ;ust look at that ob;ect. 8ext you can run db2pd with -tcbstats to
correlate "b;IDIs to the table names using the bufferpools
db2pd -db dbname -tcbstats - will show tablenames and their "b;IDIs
db2pd -db dbname -logs S shows the status of the log archier and how fast logs are
filling up
db2pd -db dbname -logs -repeat 6) 5) - runs db2pd eery 6) seconds a total of 5) times
db2pd -db dbname -applications - This will list out all apps currently running in the db.
3art of the info might show apps with a status of (ock-wait. To iew the lock the app is
waiting on run -
db2pd -db dbname -locks wait - this might show output similar to -
(ocksQ
TranHdl (ockname Type *ode 'ts
6 )L))55)))6)),)),))))))))C2 Gow ..E #
2 )L))55)))6)),)),))))))))C2 Gow ..E 2
which shows a row lock .
db2pd -db dbname -locks showlocks wait - dumps out the ob;ect information like -
(ocksQ
TranHdl Type *ode 'ts
6 Gow ..E # TbspaceID L TableID 5O 3age 2O2) 'lot 6
2 Gow ..E 2 TbspaceID L TableID 5O 3age 2O2) 'lot 6
db2pd -db dbname -tcbstats S will show the table name from the tableid
7ou can see what is under contention with db2dart S db2dart dbhname 1dd. It will ask
for the table$id/ tablespace$id/ and the page to dump
Bufferpools -
db2pd -db dbname -bufferpools
Here are the columns it will produce -
: Id - +ufferpool id
: 8ame - +ufferpool name
: 3age'4 - 3age si4e for this bufferpool
: 3,-8um3gs - 8umber of pages in this bufferpool
: +,-8um3gs - 8umber of pages in the block based portion of this bufferpool
: 8umTbsp - 8umber of tablespaces that are using this bufferpool
: &urrent'4 - &urrent si4e >in pages@ of this bufferpool
: 3ost,lter - If you hae altered the si4e of the bufferpool and D+2 is currently
shrinking or growing in si4e/ this is the post alter si4e.
: Dat(Gds - 8umber of logical data page reads for this bufferpool
: Dat3Gds - 8umber of physical data page reads for this bufferpool
: HitGatio - Hit ratio for data pages gien the aboe logical and physical reads
: Idx(Gds - 8umber of logical index page reads for this bufferpool
: Idx3Gds - 8umber of physical index page reads for this bufferpool
: HitGatio - Hit ratio for index pages gien the aboe logical and physical reads
: Data#rts - 8umber of data pages written out from this bufferpool
: Idx#rts - 8umber of index pages written out for this bufferpool
: DirGds - 8umber of direct reads
: ,sDatGds - 8umber of asynchronous data page reads
: !nGd3.etch - 8umber of pages prefetched into the bufferpool but neer read by an
agent.
db2pd -db dbname -tcbstat Ftabspaceid tabid0 i.e. Db2pd -db bgdb) -tcbstat 2 6K will
show stats for a specific table. 7ou can query ;ust by tabspace id if desired.
Db2pd -eerything - will dump all options the db2pd command has
DB2 Callout Script " d2cos + -
db2 B has a new feature/ db2cos > db2 call out script @/ which is executed any time the
instance traps/ panics/ gets a seg fault or an exception causing the dbm to stop executing.
7ou can put any script in the db2cos script/ to dump info. +y default it runs db2pd. 'o
you can hae it run db2pd -eerything to dump a wealth of information.
There is a default db2cos script in 1opt1ibm1db21VB.51bin that you can oerride with one
in the instances sqllib directory
db2pdcfg can help you determine when to run db2cos -
db2pdcfg -catch sqlcode/reason$code action - sqlcode is the code you want to catch
and reason$code is optional. +y default action is db2cos/ but you can set it to something
else.
db2pdcfg -catch -B55/2 - catches deadlocks
db2pdcfg -catch -B55/6K - catches lock timeouts
db2pdcfg -catch -2KB - catches eerything when a tablespace full condition hit
DB2 !dmin 6ie7s -
'how messages in the notify log in the last 2D hours of a certain seerity -
S010*& &2$0S&A$%3 S45S&,'$S6313400( AS $S6
7,-$ S8S25$A9$!%91-6$S6S:1AS&24H-4,S
;H0,0 $S6S0<0,2&8 2= '>*>3>0>(
-,90, 58 &2$0S&A$% 90S*
'elect messages beyond 2D hours -
S010*& &2$0S&A$%3 S45S&,'$S6313400( AS $S6
7,-$ &A510 '%9:60&:1-6:$S6S' *4,,0=& &2$0S&A$% ? 3 9A8S(( AS %9
-,90, 58 &2$0S&A$% 90S*
the following query shows the aerage time taken >in minutes@ and the maximum time
taken for full backups
S010*& A<6'&2$0S&A$%9277'43*HA,'&2$0S&A$%'0=9:&2$0(
? &2$0S&A$%'S&A,&:&2$0(((( AS A<6:5&2$03
$A@'&2$0S&A$%9277'43*HA,'&2$0S&A$%'0=9:&2$0(
? &2$0S&A$%'S&A,&:&2$0(((( AS $A@:5&2$0
7,-$ S8S25$A9$!95:H2S&-,8
;H0,0 -%0,A&2-= = >5>
A=9 -%0,A&2-=&8%0 = >7>
"r when was that last time the T+239V98T("2 table was reorgani4ed
S010*& S&A,&:&2$0
7,-$ S8S25$A9$!95:H2S&-,8
;H0,0 &A5=A$0 = >&56%0<0=&1-6>
A=9 -%0,A&2-= = >6>
"r any operation in the history file that failed
S010*& S&A,&:&2$03 SA1*-903 S45S&,'*$9:&0@&31350(
7,-$ S8S25$A9$!95:H2S&-,8
;H0,0 SA1*-90 B 0
Here is an example query that shows you any statements that hae been executing for
more than one minute along with the authid/ the status of the application and the first
few characters of the statement text.
S010*& 01A%S09:&2$0:$2=3S45S&,'A4&H2931310( AS A4&H:293 A60=&:293
A%%1:S&A&4S3S45S&,'S&$&:&0@&31320( AS SA1:&0@&
7,-$ S8S25$A9$!1-=6:,4==2=6:SA1
;H0,0 01A%S09:&2$0:$2= C 0
-,90, 58 01A%S09:&2$0:$2= 90S*
To find who is waiting on locks and who is holding those locks being waited on -
S010*& S$A112=&'A60=&:29( AS ;A2&2=6:293
S45S&,'A%%1:=A$03 1310( AS ;A2&2=6:A%%3
S45S&,'A4&H2931310( AS ;A2&2=6:4S0,3
S$A112=&'A60=&:29:H-192=6:1D( AS H-190,:293
1-*D:$-90 AS H0193
1-*D:-5E0*&:&8%0 AS &8%03
1-*D:$-90:,0A40S&09 AS ,0A40S&
7,-$ S8S25$A9$!1-*D;A2&S
7ou can drill further using other snapshot iews to see who is the holderQ
S010*& S45S&,'A%%1:=A$03 1310( AS H-192=6:A%%3
S45S&,'%,2$A,8:A4&H:2931310( AS H-192=6:4S0,3
S45S&,'*120=&:==A$031320( AS H-192=6:*120=&3
A%%1:S&A&4S
7,-$ S8S25$A9$!S=A%A%%1:2=7-
;H0,0 A60=&:29 = F31
'how the si4e of all tables in the +23'7'D+ schema/ displayed in M+.
S010*& S45S&,'&A5S*H0$A31310( AS S*H0$A3
S45S&,'&A5=A$031315( AS &A5=A$03
2=&'9A&A:-5E0*&:%:S2G0( AS -5E:SG:D53
2=&'2=90@:-5E0*&:%:S2G0( AS 2=@:SG:D53
2=&'@$1:-5E0*&:%:S2G0( AS @$1:SG:D5
7,-$ S8S25$A9$!A9$2=&A52=7-
;H0,0 &A5S*H0$A=>56%S8S95>
-,90, 58 3 90S*
Display the sum of all ob;ects by schema
S010*& S45S&,'&A5S*H0$A31310( AS S*H0$A3
S4$'9A&A:-5E0*&:%:S2G0( AS -5E:SG:D53
S4$'2=90@:-5E0*&:%:S2G0( AS 2=@:SG:D53
S4$'@$1:-5E0*&:%:S2G0( AS @$1:SG:D5
7,-$ S8S25$A9$!A9$2=&A52=7-
6,-4% 58 &A5S*H0$A
-,90, 58 2 90S*
To iew the db2 registry ariables that are set -
S010*& S45S&,',06:<A,:=A$031320( AS =A$03
95%A,&2&2-==4$3
S45S&,',06:<A,:<A14031320( AS <A140
7,-$ S8S25$A9$!,06:<A,2A510S
;H0,0 ,06:<A,:=A$0 = >952:;-,D1-A9>
9isplay the most freHently e"ected statement
S010*& S45S&,'S&$&:&0@&31350(3 =4$:0@0*4&2-=S
7,-$ S8S25$A9$!&-%:98=A$2*:SA1
-,90, 58 =4$:0@0*4&2-=S 90S*
70&*H 72,S& ,-; -=18
"r the statement with the highest aerage execution timeQ
S010*& S45S&,'S&$&:&0@&31350(3 A<0,A60:0@0*4&2-=:&2$0:S
7,-$ S8S25$A9$!&-%:98=A$2*:SA1
-,90, 58 A<0,A60:0@0*4&2-=:&2$0:S 90S*
70&*H 72,S& ,-; -=18