Transcript Title Slide: Session Title Always Title Case, Up to 100

How To Predict DB2
Performance Changes
Mainframe
MB103SN
Abstract
> The attendee will get a quick overview of Explain
evolution over time and how this feature has become the
mirror of the Optimizer
> We will look at some potential methods to exploit in order
to influence the Optimizers decisions and some issues to
have in mind where Explain doesn’t show the “real
picture”.
> The attendee will also get some ideas how Explain
information can be versioned in order to quickly find the
reason for a performance change and finally how to
predict performance before a program or SQL statement
is going through a Rebind/Bind using CA Plan Analyzer –
which also is a major concern during DB2 upgrades
2
June 10th: How to predict DB2 Performance Changes
Copyright © 2009 CA. All rights reserved.
Agenda
> Explain Evolution over the years – are you aware of all the
new stuff ?
> Optimizer resources for choosing the” correct” Access Path and
how the user can influence these decisions
> Methods to make corrective actions to application SQL and
how to predict Access Path / Performance
> Using CA Plan Analyzer to have a Proactive and a Reactive
approach in place
3

How CA Plan Analyzer can automate Explain Versioning in order
to find the reasons for Access Path change or performance
degradation / improvement

A method to compare the costs and performance
improvements/degradation prior to a potential Bind or Rebind
June 10th: How to predict DB2 Performance Changes
Copyright © 2009 CA. All rights reserved.
What is EXPLAIN
> Some very important issues when dealing with DB2
Performance
– Not only a Systems Programmer task
– Not only a DBA task
– Not only an Application Programmer / Analyst task
> It’s one common task
– Important to have DBA’s and Application folks speak the
same language
– Don’t “point fingers” and don’t direct without explaining
WHY !


Let the Application Developer understand what is
happening
Let the DBA understand the business
– Too often “bad things” are found too late
4
June 10th: How to predict DB2 Performance Changes
Copyright © 2009 CA. All rights reserved.
What is EXPLAIN
> Illustrates how DB2’s Optimizer will execute SQL, a Package or
a Plan (depending on how EXPLAIN is executed)
> Why is EXPLAIN a necessity

5
Can’t we simply look at the SQL-statement and estimate if it has
been coded “all right” (we used to)
–
What if it’s a 12 table join
–
Or the statement is 2 MB (or “just” 24 KB)
Let’s see a cool example
IDUG NA2007 alt.doc

Does performance mean anything – if a statement executes
in half a second or 5 minutes ?

Predict WHAT will happen when SQL changes or after a Package
REBIND when a reorganization and Runstats executed

The latest example – how will upgrading to DB2 V8 impact Access
Path (compare DB2 V7 Optimizer with DB2 V8)

Do you always know whether a JOIN or SUBSELECT/EXISTS provides
the best performance ?? (let’s see an example)
June 10th: How to predict DB2 Performance Changes
Copyright © 2009 CA. All rights reserved.
What is EXPLAIN
> Explain is invaluable to check how the DB2 Optimizer
decides to access the data
> The following pages illustrates that even though the
RESULT-SET is good and expected – the performance
might be very different and not desirable
SELECT * FROM DSN8810.EMP outer
WHERE
NOT EXISTS
(SELECT * FROM DSN8810.EMP inner
WHERE
outer.WORKDEPT IN
('D11','C11') );
SELECT * FROM DSN8810.EMP outer
WHERE
outer.WORKDEPT NOT IN ('D11','C11')
;
– We will get back to the EXPLAIN prerequisites in
the next section
6
June 10th: How to predict DB2 Performance Changes
Copyright © 2009 CA. All rights reserved.
What is EXPLAIN
> Two different SQL statements might give the same result
– but is performance identical ?
> One example where it pays off to utilize Explain:
– Explain these two statements – same result-set !!!!
– Any differences in Access Path ?
– What about Cost estimates ?
EXPLAIN PLAN SET QUERYNO = 810 FOR
SELECT * FROM DSN8810.EMP outer
WHERE
NOT EXISTS
(SELECT * FROM DSN8810.EMP inner
WHERE
outer.WORKDEPT IN ('D11','C11') );
EXPLAIN PLAN SET QUERYNO = 811 FOR
SELECT * FROM DSN8810.EMP outer
WHERE
outer.WORKDEPT NOT IN ('D11','C11')
;
COMMIT;
SELECT * FROM PLAN_TABLE where queryno in (810,811);
SELECT * FROM DSN_STATEMNT_TABLE where queryno in (810,811);
7
June 10th: How to predict DB2 Performance Changes
Copyright © 2009 CA. All rights reserved.
What is EXPLAIN
> EXPLAIN output (the essential part) for these two
statements
QUERYNO QBLOCKNO APPLNAME PROGNAME PLANNO METHOD CREATOR
810
2
BPASQL8
1
0 DSN8810
810
1
BPASQL8
1
0 DSN8810
811
1
BPASQL8
1
0 DSN8810
TNAME
EMP
EMP
EMP
TABNO ACCESSTYPE MATCHCOLS ACCESSCREATOR
2
I
0
DSN8810
1
R
0
1
R
0
ACCESSNAME
XEMP2
INDEXONLY
Y
N
N
~~~~~~~~~~~~~~~ from DSN_STATEMNT_TABLE ~~~~~~~~~~~~~
QUERYNO
810
811
8
APPLNAME PROGNAME PROCMS
BPASQL8
216
BPASQL8
98
June 10th: How to predict DB2 Performance Changes
Copyright © 2009 CA. All rights reserved.
PROCSU
6000
2706
What is EXPLAIN
> Execution statistics for these two statements
R11.05 >
------ DETECTOR Exception SQL Request Summary ------ 08-28-07 16:22
Command ==>
Scroll ==> PAGE
Line 1 of 2
OPID
==> RASST02
DB2 SSID ==> S81A
Total/Avg ==> T
Interval Time ==> 04:00
Interval Elapsed ==> 22:28.08
------------------------------------------------------------------------------S -View SQL stats, Q -View SQL text, E -Explain, D -View Detail
START_TIME
PLANNAME
SQL INDB2_TIME
INDB2_CPU
GETPAGE
------------ -------- ---------- ------------ ------------ ---------_ 16:19:57.066 RBPA1150
35 00:05.940991 00:00.009281
33
_ 16:21:00.559 RBPA1150
35 00:02.791862 00:00.004852
27
******************************* BOTTOM OF DATA ********************************
– Reflects what Explain described
– A clear difference in performance and access path –
even though the result-sets are identical
Use EXPLAIN as part of the development toolset !!!!!!
9
June 10th: How to predict DB2 Performance Changes
Copyright © 2009 CA. All rights reserved.
What is EXPLAIN
> Why is Performance so important
 Bad performing SQL costs $$$$
 One benchmark a few years ago illustrates:


It costs $30 to correct “bad” SQL in test
It costs $1000 to correct in production
 If response times are not optimal
–
–
–
Fewer transactions will go through the pipe
Each end user will be less productive
Other SQL-statements will suffer due to sharing of resources

Buffer Pools, I/O channels, Locking conflicts,
contention in shared pools like SORT area, RID pool, .
...
 Hardware upgrade to conform to SLA
 Potential loss of business – especially for businesses
dependent on Internet applications
10
June 10th: How to predict DB2 Performance Changes
Copyright © 2009 CA. All rights reserved.
What’s influencing the EXPLAIN output
> Some factors to consider when comparing Explain
between two different environments
– Optimizer looks at Hardware type
– Optimizer looks at number of processors
– Optimizer looks at Buffer Pools
– Costs to allocate work-files
> Host variables will be replaced by “Parameter Markers”
when doing dynamic Explain (as opposed to Explain
during Bind/Rebind)
– This could be a major problem in earlier DB2 versions
(prior to DB2 V8)
– When host variables (or column predicate) was defined
differently than the column defined in the DB2 catalog
11
June 10th: How to predict DB2 Performance Changes
Copyright © 2009 CA. All rights reserved.
What’s influencing the EXPLAIN output
> Table size (and compression)
> Column cardinality and Filter Factor
> Indexes present and the index columns

FIRSTKEYCARDF and FULLKEYCARDF and now more granular
statistics when portion of index columns referenced

Different RUNSTATS parameters to collect these statistics
> Clustering (Cluster Ratio) and number of index levels
> SQL predicates (predicate analysis)
> ORDER BY and the ability to eliminate sorts
> . . . . . . . . . . and many more details
12
June 10th: How to predict DB2 Performance Changes
Copyright © 2009 CA. All rights reserved.
EXPLAIN Pre-Requisites
> EXPLAIN tables
 creator.PLAN_TABLE (minimum to do explain)
–
Records Optimizers choice of Access Path
–
Not immediately easy to decrypt – many “codes”
 creator.DSN_STATEMNT_TABLE (optional)
–
Shows COST estimates (this is HUGE in my opinion)
 creator.DSN_FUNCTION_TABLE (optional)
–
Only used if UDF’s need to be explained
 Creator.DSN_STATEMENT_CACHE_TABLE (new in DB2 V8)
–
Used to explain the DB2 Dynamic Statement Cache
 With DB2 9 there are 10 additional tables to consider
13
June 10th: How to predict DB2 Performance Changes
Copyright © 2009 CA. All rights reserved.
PLAN_TABLE evolution
V1R1M0 Where is Explain ?
V1R2
25 columns
Sequential Prefetch
V2R1
V2R3
14
28 columns
30 columns
Packages
Parallelism
V3R1
34 columns
V4
43 columns
V5
46 columns
Re-optimization
V6
49 columns
Optimization Hints
V7
51 columns
Table types like temporary, work file
V8
58 columns
Encoding scheme - Unicode
9
59 columns
June 10th: How to predict DB2 Performance Changes
Copyright © 2009 CA. All rights reserved.
Data Sharing and more parallelism
Lots of new information to existing columns
PLAN_TABLE evolution
> Even though the PLAN_TABLE is invaluable there are
a number of issues to consider
–
The Optimizers “thoughts” are not reflected
–
Why one index was chosen over the other
–
Why one table was picked as the first accessed instead of
another in a join sequence
–
Why a predicate is considered STAGE1 or STAGE2
–
Why a predicate isn’t indexable
–
Visual Explain does help to explain some of these issues
 The most important issues when analyzing the Access
Path described in the PLAN_TABLE 
15
June 10th: How to predict DB2 Performance Changes
Copyright © 2009 CA. All rights reserved.
PLAN_TABLE evolution
> Explain only shows SELECT, DELETE, UPDATE, INSERT
> Important not to forget the issues below when doing
performance / tuning
– RI Definitions
– TRIGGERs executed as part of the SQL-statement
– UDF’s
– Table- and Column Check Constraints
> Not always a guarantee DB2 will USE the illustrated AP
– Prefetch activities can be disabled depending on BP status
– Parallelism is decided at the execution time
– RID Pool shortage (DB2 V8 changed the behavior)
16
June 10th: How to predict DB2 Performance Changes
Copyright © 2009 CA. All rights reserved.
PLAN_TABLE evolution
> The birth of DSN_STATEMNT_TABLE one of
the greatest news
– Almost as important as the PLAN_TABLE itself
– PROCMS and PROCSU can be used to compare costs
between different versions of a statement or package
– Somehow it’s easier than comparing the PLAN_TABLE
content

Numbers are nice when somehow accurate
– Don’t base all your decisions on these numbers only –
use them to compare a previous Access Path
 DB2 9 for z/OS introduced one additional column in
DSN_STATEMNT_TABLE
– TOTAL_COST - The estimated cost of the statement
(elapsed time) to compare with explain versions of the
same statement
17
June 10th: How to predict DB2 Performance Changes
Copyright © 2009 CA. All rights reserved.
PLAN_TABLE evolution
> WHAT-IF analysis
 DB2 9 provides another
new feature – the ability to
do explain with virtual
indexes
 One additional table needs
to be created for the
Explain feature.
 The VIRTUAL index will
show up in the PLAN_TABLE
if it can be used
18
June 10th: How to predict DB2 Performance Changes
Copyright © 2009 CA. All rights reserved.
CREATE TABLE
RASST02.DSN_VIRTUAL_INDEXES
( TBCREATOR VARCHAR ( 128 )
, TBNAME VARCHAR ( 128 )
, IXCREATOR VARCHAR ( 128 )
, IXNAME VARCHAR ( 128 )
, ENABLE CHAR ( 1 )
, MODE CHAR ( 1 )
, UNIQUERULE CHAR ( 1 )
, COLCOUNT SMALLINT
, CLUSTERING CHAR ( 1 )
, NLEAF INTEGER
, NLEVELS SMALLINT
, INDEXTYPE CHAR ( 1 )
, PGSIZE SMALLINT
, FIRSTKEYCARDF FLOAT
, FULLKEYCARDF FLOAT
, CLUSTERRATIOF FLOAT
, "PADDED" CHAR ( 1 )
, COLNO1 SMALLINT
, ORDERING1 CHAR ( 1 )
, COLNO2 SMALLINT
, ORDERING2 CHAR ( 1 )
, . . . .
, . . . .
, COLNO64 SMALLINT
, ORDERING64 CHAR ( 1 ) )
Help or Fool the Optimizer
> The Optimizer not always perfect - what are the
options when a “less optimal” Access Path chosen
 Used to be because of insufficient information due to
“lack of features in Runstats” - or the Optimizer being a
“toddler” still learning
 Nowadays - bad decisions mostly due to:
– Insufficient information in the catalog
– Catalog statistics doesn’t reflect the reality
– The table data is skewed – not evenly distributed (highly
uneven frequency)
19
June 10th: How to predict DB2 Performance Changes
Copyright © 2009 CA. All rights reserved.
Help or Fool the Optimizer
> What did we do in the “good old days”
–
We had no OPTHINT
–
No FETCH ONLY and no FETCH FIRST
–
No report option for Runstats (live or die)
–
Only cardinality for FIRSTKEYCARD(f) and FULLKEYCARD(f)

A challenge when 4 columns in index and MC=2 with low cardinality
for first column

Update catalog changing NLEVELS of indexes

Update catalog changing the Clusterratio

Add strange predicates (e.g. +0 or *1) to SQL statement making
it less understandable for the next person

Adding “additional duplicate” predicates
Just to name a few – let’s see the goodies !
20
June 10th: How to predict DB2 Performance Changes
Copyright © 2009 CA. All rights reserved.
Help or Fool the Optimizer
> VOLATILE – a better way to force index access
compared to manually manipulating Catalog statistics
– Update NLEVELS, CARDF etc.
– Remember Prefetch is disabled
> REOPT(xxxx)
 where cardinality is really skewed and the costs
associated with re-optimization is less than the benefits
from different Access Path.
–
NONE, ONCE, ALWAYS, AUTO (DB2 9 - ZPARM parameter)
> OPTIMIZE FOR xx ROWS
> FETCH FIRST xx ROWS ONLY
> Rearrange tables in the FROM clause still might help

21
Predicates can still be “invalidated” by adding 0=1
June 10th: How to predict DB2 Performance Changes
Copyright © 2009 CA. All rights reserved.
Help or Fool the Optimizer
> OPTHINT – not the most intelligent implementation, but
still better than manually updating the catalog and keep
track of changes
 Identify QUERYNO from PLAN_TABLE
(preferable to use QUERYNO yyy in SQL statement)
 Update PLAN_TABLE for QUERYNO with “access path” and
specify OPTHINT name
 Either specify OPTHINT in Rebind or use SET CURRENT
OPTIMIZATION HINT=‘hint name’
 Execute EXPLAIN and study HINT_USED in PLAN_TABLE
– SQL+394  Hint used
– SQL+395  something is wrong
22
June 10th: How to predict DB2 Performance Changes
Copyright © 2009 CA. All rights reserved.
Help or Fool the Optimizer
> Runstats the first choice – and easiest method
 Especially since Optimizer changes all the time
> Exploit the many great keywords and parameters
in Runstats
RUNSTATS TABLESPACE db.ts
TABLE(tbcr.tbnm)
INDEX(all)
KEYCARD
COLGROUP(col1,col2,col3) COLGROUP(col3,col1)
FREQVAL(30 / most / least / both)
– Column combinations used in multi-column indexes
– Are most frequent / least frequent values used

23
Also needed for REOPT
June 10th: How to predict DB2 Performance Changes
Copyright © 2009 CA. All rights reserved.
Who stole my favorite scan
> All the previous mentioned issues lead to the essence of this
presentation: Explain versioning
> Finding the best proactive approach

How much can be done ahead of time

Static SQL is a lot easier to deal with compared to dynamic SQL
from the Wild Wild World of Java and other sources
> Finding the best reactive approach

No matter how proactive we try to be, something will
eventually fall between the cracks
–

Sometimes we are being pleasantly surprised by performance
improvements
–
24
What changed and when ?
What happened – and why ?
June 10th: How to predict DB2 Performance Changes
Copyright © 2009 CA. All rights reserved.
Explain Versioning
> REACTIVE <> PROACTIVE Explain

Reactive explain is just as important as the proactive

Why is versioning interesting – isn’t the most current
PLAN_TABLE content sufficient ?

More interesting to see the current “real” Access Path

We really need a feature to show what the current Access Path is
(explain what’s in SPT01)
> Proactive Explain – many ways to accomplish this

25
Goal is to predict performance prior to production
–
Import production catalog statistics into test
–
Or Bind into separate collection on Production
June 10th: How to predict DB2 Performance Changes
Copyright © 2009 CA. All rights reserved.
Automate Explain Versioning using CA Plan Analyzer
> Consider to make information available for everyone
> Consider to have Developers use the same concept
–
–
–
–
They learn what to do and not to do
They can see the impact
Less burden for the DBA’s
Better performance is the ultimate goal
> CA Plan Analyzer can help you with all these aspects
– Create Profiles reflecting the Users and Purposes




Short or long Access Path description ?
Filter Factors and Predicate analysis ?
Object tree and object statistics ?
Which Expert Rules and guidelines depends on knowledge !
– Users simply PICK the profile they need based on
experience, knowledge, task etc.
26
June 10th: How to predict DB2 Performance Changes
Copyright © 2009 CA. All rights reserved.
Create Profile(s)
> Create as many Explain Profiles as needed to reflect the
user profiles and skill level
r11.5
---------------- PPA Explain Options --------------- 07-30-08
COMMAND ===>
16:02
Save Historic data as strategy
PROFILE
===> IDUGVER1
Description
===> DBA PROD EXPL+COMP
Creator
===> RASST02
Share Option
===> U
(U,Y, or N)
----------------------------------------------------------------- RASST02
Database Options
===> y
Historical Database Options
Primary AUTHID
===>
Secondary AUTHID
===>
Update SQL Qualifiers ===>
(Override Schemas & SQL/View Qualifiers)
Rule Set SSID
===> s81a
(Subsystem where Rule Sets are stored)
PLAN_TABLE Option
===> R
(C - Commit, R - Rollback)
Explain Type
===> F
(C - Current, F - Future)
Plan Explain Option
===> b
(D - DBRM, P - Package, B - Both)
Non-Catalog Isolation ===> CS
Optimization Hint
===>
Process Views (Y, N)
===> Y
Parallelism Degree ===>
Target SSID
===> s81a
Target Rule Set
===> @DEFAULT
Save Host Variables
===> Y
Save Reports
===> y
Uppercase Output
===> n
Access Path Filters ===> N
Reports
===> u
Search Conditions
===> N
Catalog Statistics
===> N
Search Filters
===> N
Tie PERFORMANCE Data
===> N
27
June 10th: How to predict DB2 Performance Changes
Copyright © 2009 CA. All rights reserved.
Create Profile(s)
> Specify which reports to generate and whether these
should be Short or Long (knowledge level)
r11.5 -------------- PPA Explain Reports --------------- 07-30-08
COMMAND ===>
Summary Reports:
Summary ===> n
Cost
===> n
Report Formatting
Compare Options
Cost Filtering
Perf Total/Avg
Suppress Reports
28
Stmt Level Reports:
Access
===> s
Predicate
===> L
Dependency ===> S
Sqlrule
===> s
Physrule
===> N
Predrule
===> N
Performance ===> N
16:13
Group Level Reports:
Planrule
===> N
RI
===> N
Tree
===> N
Statistics ===> y
Compare
===> Y
Options:
===> y
===>
===> T
===> N
(Ignore Stmt Level reports if rules not fired)
June 10th: How to predict DB2 Performance Changes
Copyright © 2009 CA. All rights reserved.
Create Profile(s)
> Specify what should be included in the compare report – SQL
differences, Access Path changes, . . . .
r11.5
------------ PPA Compare Report Options ------------ 07-30-08
Report Inclusion Options
Change Level
===> A
Paired stmts
===> B
Comparison Options
Compare Creator ===> N
Formatting Options
SQL/Access Path ===> A
SQL version
Access version
Host Variables
===> E
===> E
===> C
Suppress Report ===> Y
Old HVersion
Options
29
===>
===> Y
16:15
B - Changed/Unchanged
E - Either changed
B - Paired/Unpaired
S - SQL changed
A - Access changed
P - Paired
U - Unpaired
Cost Margin (ms) => 08
(su) => 05
B
C
E
B
B
N
Y
-
Show SQL and Access
S - Show
Show the change only
A - Show
Show once if equal
O - Show
Show both versions
N - Show
Show Changed/Unchanged C - Show
Do not show
No SQL and/or Access changes
N
Y – Save
June 10th: How to predict DB2 Performance Changes
Copyright © 2009 CA. All rights reserved.
N – Use
(tm) =>
SQL only
Access only
OLD version
NEW version
Change Only
- Show report
Execute Explain using Profile
> Online creation of a strategy is used to explain a package /
program in this case – but input can be anything
– Program name used as strategy name 
easy to identify and administer
r11.5
----------------- PPA Quick Explain ---------------- 07-30-08
COMMAND ===>
16:28
Strategy ===> R%
Creator ===> RASST02
Type ===> E
----------------------------------------------------------------- RASST02
Location ===> LOCAL
DB2 SSID ===> S81A
Version ===> V8R1M0
STRATEGY
CMD /VERSION DESCRIPTION
CREATOR
c rqatsl
_________________________ RASST02
_ RMA@TCON
RASST02
_
V00001 VERSION AUTO CREATED
RASST02
_
V00002 VERSION AUTO CREATED
RASST02
_
V00003 VERSION AUTO CREATED
RASST02
_
V00004 VERSION AUTO CREATED
RASST02
_ RMA@TCO8
RASST02
_
V00001 VERSION AUTO CREATED
RASST02
_
V00002 VERSION AUTO CREATED
RASST02
******************************** BOTTOM OF DATA
T D S +----- LAST UPDATE -----+
P B O USER
DATE
TIME
E
U <=== STRATEGY CREATION
E
U CALPE05 2005-05-15 10.05
C M U RASST02 2005-04-28 18.17
C M U RASST02 2005-04-28 18.20
C M U RASST02 2005-04-28 18.21
C M U RASST02 2005-06-13 09.09
E
U RASST02 2005-04-28 18.04
C M U RASST02 2005-04-28 18.03
C M U RASST02 2005-04-28 18.13
*******************************
Batch automation to follow
30
June 10th: How to predict DB2 Performance Changes
Copyright © 2009 CA. All rights reserved.
Execute Explain using Profile
r11.5
--------- PPA Explain Strategy Data Editor --------- 07-30-08
17:17
Strategy
: RQATSL
Type
===> E
Profile ===> IDUGVER1
Creator
: RASST02
Share Option ===> U
Version
:
Description ===>
----------------------------------------------------------------- RASST02
----TARGET--- O
C TY SSID RULE SET P LOCATION
SSID PLAN/COLLID
DBRM/PKG STMT
c D s81a ________ _ LOCAL___________ s81a __________________ ________ ________
******************************** BOTTOM OF DATA *******************************
CMDS : C - Create, E - Edit, D - Delete, R - Repeat, O - Options
TYPES: A,AK,AO,AQ,AP - Autobuild(Plan,Package,Object,QMFQuery,PRFQuery)
C,CK,CO,CQ,CP - Catalog(Plan,Package,Object,QMFQuery,PRFQuery)
E,EK - EQF(Plan,Pkg), S - SQL, F - File, P - PRF, Q - QMF, D - DBRMLIB
r11.5
> Specify what
the input to
Explain is – in
this case
DBRMLIB is
specified
> Wildcarding
supported too
31
------- PPA Explain Strategy - DBRMLIB Source
Strategy
: RQATSL
Type
: E
Creator
: RASST02
Share Option
: U
Version
:
Description
:
--------------------------------------------------------Member Selection List ===> n
(Y or N)
Enter Specifications for your DBRMLIB Dataset.
ISPF LIBRARY:
PROJECT ===>
GROUP
===>
TYPE
===>
MEMBER ===>
June 10th: How to predict DB2 Performance Changes
Copyright © 2009 CA. All rights reserved.
RASST02
R115
DBRMLIB
RQATSL
Specify the DBRMLIB
Member(s) to include
(Blank, pattern, or member name)
Execute Explain using PPA Profile
> Once Explain command is executed for strategy – simply
specify the PROFILE needed
> The profile also holds the Expert System Rules
r11.5
-------- PPA Explain Strategy - Version Info ------- 07-30-08
17:28
PP327I: Explain Profile options will be used for Explain.
Strategy
: RQATSL
Type
: E
Profile ===> IDUGVER1
Creator
: RASST02
Share Option
: U
Version
:
Description
:
---------------------------------------------------------------------- RASST02
Version Information:
Identifier
Description
===>
===>
(Leave blank for system generated ID)
Options:
32
Replace existing version
===> N
(Y,N)
Update Explain Options
===> N
(Y,N)
June 10th: How to predict DB2 Performance Changes
Copyright © 2009 CA. All rights reserved.
Easier to specify a profile
than selecting the reports
and options.
Execute Explain using Profile
> Once the explain is executed, the first Version is generated

r11.5
Lets change the DBRM and re-execute the explain this time simply using the control cards in a batch job
---------- PPA Explain Strategy Services
---------- 07-31-08
11:37
LINE 1 OF 11 >
Strategy ===> R%
Creator ===> RASST02
Type ===> E
----------------------------------------------------------------- RASST02
Location ===> LOCAL
DB2 SSID ===> S81A
Version ===> V8R1M0
STRATEGY
CMD /VERSION DESCRIPTION
CREATOR
_ ________ _________________________ RASST02
_ RMA@TCON
RASST02
_
V00001 VERSION AUTO CREATED
RASST02
_
V00002 VERSION AUTO CREATED
RASST02
_
V00003 VERSION AUTO CREATED
RASST02
_
V00004 VERSION AUTO CREATED
RASST02
_ RMA@TCO8
RASST02
_
V00001 VERSION AUTO CREATED
RASST02
_
V00002 VERSION AUTO CREATED
RASST02
_ RQATSL
RASST02
_
V00001 VERSION AUTO CREATED
RASST02
******************************** BOTTOM OF DATA
33
June 10th: How to predict DB2 Performance Changes
Copyright © 2009 CA. All rights reserved.
T D S +----- LAST UPDATE -----+
P B O USER
DATE
TIME
E
U <=== STRATEGY CREATION
E
U CALPE05 2005-05-15 10.05
C M U RASST02 2005-04-28 18.17
C M U RASST02 2005-04-28 18.20
C M U RASST02 2005-04-28 18.21
C M U RASST02 2005-06-13 09.09
E
U RASST02 2005-04-28 18.04
C M U RASST02 2005-04-28 18.03
C M U RASST02 2005-04-28 18.13
E
U RASST02 2008-07-30 17.28
C S U RASST02 2008-07-30 17.48
*******************************
Execute Explain using Profile
> Batch JCL step to either create a new explain version or create a new
strategy (if DBRM is new)


Attributes marked in red should be replaced at generation time when
integrated with Change Management process
Nothing needs to be done within PPA – executing this step is all that is
needed.
//STEP1
EXEC PGM=PTLDRIVM,REGION=4M,
//
PARM='SUFFIX=00,EP=BPLBCTL'
//STEPLIB DD DISP=SHR,DSN=SALESUP.R115SP1.LOADLIB
//PTILIB
DD DISP=SHR,DSN=SALESUP.R115SP1.LOADLIB
//PTIPARM DD DISP=SHR,DSN=SALESUP.R115SP1.PARMLIB
//SYSOUT
DD
SYSOUT=X
//UTPRINT DD
SYSOUT=X
//ABNLIGNR DD
DUMMY
SUPPRESS ABENDAID DUMPS
//BPIIPT
DD
*
.CALL EXPLAIN
.DATA
.ENDDATA
/*
//BPIOPT
DD *
.CONTROL BPID(BP-RASST02-114817-20080731)
LOGID(S81A) UNIT(SYSDA)
.LIST SYSOUT(X,,)
.OPTION NOERRORS
.RESTART OVERRIDE
.CONNECT S81A
34
June 10th: How to predict DB2 Performance Changes
Copyright © 2009 CA. All rights reserved.
+
RULESSID
ACM
STRATEGY
PLANTAB
SQLQUAL
LINES
PROCDDF
FLOATFMT
SAVEHOST
PROCVIEW
EXPLTYPE
ISOLATE
TARGET
PERFTIE
DATABASE
REPORT
=
=
=
=
=
=
=
=
=
=
=
=
=
=
=
=
(S81A)
(N,RASST02)
(S81A,RQATSL,RASST02,@AUTO)
(ROLLBACK)
(RASST02)
(55)
(N)
(SCI)
(Y)
(Y)
(FUTURE)
(CS)
(S81A(@DEFAULT))
(N)
(STRATEGY)
(ACCESS/SHORT,PREDICATE,
DEPENDENCY/SHORT,
STATISTICS,SQLRULE/SHORT,
PREDRULE,COMPARE)
STATSRPT = (TSP,TB,IX,IXP,CO)
COMPOPTS = (ABNAEE CY,,N,08,05,)
SRCDBRML = (RASST02.R115.DBRMLIB(RQATSL),)
Execute Explain using Profile
> Executing the JCL from the previous page will create
VERSION 2 of the Explain for this DBRM / Package
r11.5
---------- PPA Explain Strategy Services
---------- 07-31-08
12:50
LINE 1 OF 12 >
Strategy ===> R%
Creator ===> RASST02
Type ===> E
----------------------------------------------------------------- RASST02
Location ===> LOCAL
DB2 SSID ===> S81A
Version ===> V8R1M0
STRATEGY
CMD /VERSION DESCRIPTION
CREATOR
_ ________ _________________________ RASST02
_ RMA@TCON
RASST02
_
V00001 VERSION AUTO CREATED
RASST02
_
V00002 VERSION AUTO CREATED
RASST02
_
V00003 VERSION AUTO CREATED
RASST02
_
V00004 VERSION AUTO CREATED
RASST02
_ RMA@TCO8
RASST02
_
V00001 VERSION AUTO CREATED
RASST02
_
V00002 VERSION AUTO CREATED
RASST02
_ RQATSL
RASST02
_
V00001 VERSION AUTO CREATED
RASST02
R
V00002 VERSION AUTO CREATED
RASST02
******************************** BOTTOM OF DATA
35
June 10th: How to predict DB2 Performance Changes
Copyright © 2009 CA. All rights reserved.
T D S +----- LAST UPDATE -----+
P B O USER
DATE
TIME
E
U <=== STRATEGY CREATION
E
U CALPE05 2005-05-15 10.05
C M U RASST02 2005-04-28 18.17
C M U RASST02 2005-04-28 18.20
C M U RASST02 2005-04-28 18.21
C M U RASST02 2005-06-13 09.09
E
U RASST02 2005-04-28 18.04
C M U RASST02 2005-04-28 18.03
C M U RASST02 2005-04-28 18.13
E
U RASST02 2008-07-30 17.28
C S U RASST02 2008-07-30 17.48
C S U RASST02 2008-07-31 11.53
*******************************
View Explain Reports
> Use option “R” to view the generated reports for a specific
Explain version - then Select the reports of interest
r11.5
---------- PPA Explain - Output Selection ---------- 07-31-08
13:09
Strategy : RQATSL
Creator : RASST02
Version : V00002
Share Opt: U
Type
: C
Desc
: VERSION AUTO CREATED
---------------------------------------------------------------------- RASST02
Available Sel ( Indicate Selections with an "S" )
Y
_ INPUT
(Explain input statements)
_ SUMMARY
(Explain summary report)
Y
_ ACCESS
(Access path report)
_ COST
(Relative cost report)
Y
_ PREDICATE
(Predicate analysis report)
Y
_ DEPENDENCY (Object dependency report)
_ TREE
(Object tree report)
Y
_ STATISTICS (Catalog statistics report)
Y
_ SQLRULE
(SQL rules/recommendations)
_ PHYSRULE
(Physical rules/recommendations)
_ PLANRULE
(Plan rules/recommendations)
_ PREDRULE
(Predicate rules/recommendations)
Y
_ ERRORS
(Error messages)
Y
S COMPARE
(Compare Explain Versions report)
_ PERFORMANCE (Performance Report)
_ STATS MGR
(Statistics Manager Interface)
36
June 10th: How to predict DB2 Performance Changes
Copyright © 2009 CA. All rights reserved.
View Explain Compare Report
> The compare report selected and displayed (first access path change listed).
–
Old: Source:
DBRM:
Statement:
Changes to Access Path noted by hyphens
RQATSL
1292
New: Source:
DBRM:
Statement:
(3)
RQATSL
1292
(3)
Old Access Path Analysis:
Cost: (ms) 1
PQbkNo:
Access:
Table :
CorrNm:
Index :
MCols :
(su) 7
0
QblkNo: 1
IXDATA
TSLock: IS
SYSIBM.SYSTABLESPACE
A
SYSIBM.DSNDSX01
1
PlanNo: 1
Prefet: L
(tm) +.88415 E+02
MxOpSq: 0
QblkTy: SEL
TBTyp: TABLE
TabNo: 1
New Access Path Analysis:
Cost: (ms) 1
Diff: (ms) None
-(su) 1
-(su) 86% Decr
PQbkNo: 0
QblkNo: 1
Access: IXDATA
TSLock: IS
Table : SYSIBM.SYSTABLESPACE
CorrNm: A
Index : SYSIBM.DSNDSX01
MCols :-2
37
PlanNo: 1
Prefet:-?
June 10th: How to predict DB2 Performance Changes
Copyright © 2009 CA. All rights reserved.
(tm) +.11048 E+02
MxOpSq: 0
QblkTy: SEL
TBTyp: TABLE
TabNo: 1
This statement
will perform
better than
the previous
version
View Explain Compare Report
> . . . and the second Access Path Change
–
Old: Source:
DBRM:
Statement:
This statement will have worse performance
RQATSL
1322
New: Source:
DBRM:
Statement:
(6)
RQATSL
1325
(6)
Old Access Path Analysis:
Cost: (ms) 1
PQbkNo:
Access:
Table :
CorrNm:
Index :
MCols :
(su) 1
0
QblkNo: 1
IXDATA
TSLock: IS
SYSIBM.SYSTABLESPACE
A
SYSIBM.DSNDSX01
2
PlanNo: 1
Prefet:
(tm) +.11048 E+02
MxOpSq: 0
QblkTy: SEL
TBTyp: TABLE
TabNo: 1
New Access Path Analysis:
Cost: (ms) 1
Diff: (ms) None
-(su) 4
-(su) 300% Incr
PQbkNo: 0
QblkNo: 1
Access: IXDATA
TSLock: IS
Table : SYSIBM.SYSTABLESPACE
CorrNm: A
Index : SYSIBM.DSNDSX01
MCols :-1
38
PlanNo: 1
Prefet:
June 10th: How to predict DB2 Performance Changes
Copyright © 2009 CA. All rights reserved.
(tm) +.46428 E+02
MxOpSq: 0
QblkTy: SEL
TBTyp: TABLE
TabNo: 1
Please note the
statement number
change too where
CA Plan Analyzer
was able to
map these
View Explain Statistics Report
Object Statistics Report:
---------------- TABLEPART
PARTNO: 0
STATSTIME : 2007-02-14-15.27.27.450000
NEARINDREF : 257
FARINDREF: 273
PERCACTIVE : 48
PERCDROP : 0
CARD
: 76492
EXTENTS : 3
PQTY
: 4140
SECQTY
: 4140
----------------------------
TABLE
SYSIBM.SYSTABLESPACE
STATSTIME : 2007-02-14-15.27.27.450000
CARD
: 1401
NPAGES
: 1401
AVGROWLEN : 190
PCTPAGES : 45
INDEX
STATSTIME:
CLUSTRATO:
CLUSTERNG:
SYSIBM.DSNDSX01
2007-02-14-15.27.27.450000
+.7480E+00 1STKEYCARD: 164
YES
NLEAF
: 15
-------------- INDEXPART PARTNO: 0
STATSTIME: 2007-02-14-15.27.27.450000
FAROFFPOS: 513
NEAROFFPOS: 251
LEAFDIST : 340
LEAFFAR
: 14
PQTY
: 60
SECQTY
: 60
CARD
: 1401
EXTENTS
: 1
COLUMN
COLCARD :
STATSTIME:
HIGH2KEY :
<HEX>
39
: 0
DSNUM
SPACE
: 1
: 33120
PCTROWCOMP: 0
SPACE
: 720
FULLKYCARD: 1401
NLVL
: 2
SPACE
: 240
----------------------------
NAME
552
COLTYPE
: VARCHAR
2005-10-19-16.19.22.520206
!|.(...
5444455
AF8D543
June 10th: How to predict DB2 Performance Changes
Copyright © 2009 CA. All rights reserved.
PAGESAVE
LEAFNEAR
SPACE
DSNUM
: 0
: 240
: 1
LENGTH
NULLS
: 24
: NO
Another report generated
is the Object Statistics
Report, which does
provide invaluable
information when
comparing Access Path
changes between two
Explain versions.
In most cases,
the cause for an
Access Path change
is due to changed SQL
or changed information
in the catalog used
by the Optimizer
DB2 Explain Versioning
> Using the CA Plan Analyzer batch JCL step listed earlier,
removes most if not all the guess work:
– Old and new SQL statement listed
– Old and new Access Path described
– Old and new catalog information used by the Optimizer is
saved for further analysis when needed
> A quick and easy method to pinpoint WHEN and WHY
performance changed.
– Gives you invaluable information to “learn” too
40
June 10th: How to predict DB2 Performance Changes
Copyright © 2009 CA. All rights reserved.
DB2 Explain Versioning
(cont’d)
> Conclusion - CA Plan Analyzer Explain versioning
provides:
> A Proactive approach



41
> A Reactive approach
Verify SQL and
performance changes
ahead of program
promotion

When someone complains
about performance

Quickly identify when
program was bound
Eventually execute Explain
ahead of Bind for critical
applications

Analyze SQL text changes

Analyze Access Path
Changes

Verify if catalog statistics
have changed
Perform corrective actions
June 10th: How to predict DB2 Performance Changes
Copyright © 2009 CA. All rights reserved.
DB2 Explain Versioning
> However - this process might fall short, so here are some
challenges to consider
> What if Binds or Rebinds happen “outside” this process
To Rebind
or Not To Rebind
….
That is the QUESTION !
(cited from Roger Miller)
42
June 10th: How to predict DB2 Performance Changes
Copyright © 2009 CA. All rights reserved.
DB2 Explain Versioning
> BIND / REBIND with EXPLAIN(YES)
 DB2 will generate a NEW Access path !!!!!
 Is this a good idea ? Maybe !
warning
 What if the DB2 catalog statistics ISN’T optimal
–
No RUNSTATS executed  statistics columns in the catalog
have -1  Optimizer has NO clue about statistics for tables,
indexes, columns
 Only method to evaluate Trigger Access Path
> These might happen outside the scope / process
discussed on the previous slides – so unless these are
caught in another job, you might be fooled !!!
43
June 10th: How to predict DB2 Performance Changes
Copyright © 2009 CA. All rights reserved.
DB2 Explain Versioning
> Fallback to previous Access Path

Has not been a very easy task in the past

DB2 9 APAR=PK52523 (PTF UK31993) changed the game
(up to three copies of package access path)

REBIND PACKAGE(x) PLANMGMT(yyyyyyyy)
–
OFF : The new package access path will become the active – any
previous or original package access path remain untouched (basically
what we have had for years)
–
BASIC : The previous copy is removed, the active copy becomes the
PREVIOUS and the new becomes the ACTIVE
–
EXTENDED : The current active package becomes PREVIOUS and
new package becomes ACTIVE – original stays (or previous becomes
ORIGINAL if original doesn’t exist already)

44
Also valid for REBIND TRIGGER PACKAGE
June 10th: How to predict DB2 Performance Changes
Copyright © 2009 CA. All rights reserved.
DB2 Explain Versioning
> Fallback to previous Access Path

REBIND PACKAGE(x) SWITCH(yyyyyyyy)

PREVIOUS : Flip-Flop ACTIVE and CURRENT access paths. The
current active Package Access Path becomes the previous – and the
current previous Package Access Path becomes the active
–

A quick method to fallback to old Access Path
ORIGINAL : Flip-Flop of the ORIGINAL and the current ACTIVE
(newest) Package Access Path.
–
–
What was the CURRENT becomes the PREVIOUS
The ORIGINAL is now the ACTIVE

–

The PREVIOUS is gone
Wildcarding is supported to help rebinding multiple packages
–
–
45
It still exists as ORIGINAL too
After DB2 upgrade
Runstats and/or REBIND “mis-executed”
June 10th: How to predict DB2 Performance Changes
Copyright © 2009 CA. All rights reserved.
DB2 Access Path (SPT01) Versioning
REBIND PACKAGE(STEEN_COLL.RQATL.(CAD115_2008-01-08-20.41.45)) PLANMGMT(EXTENDED) ;
DSNT255I !DA0G DSNTBRB2 REBIND OPTIONS FOR PACKAGE =
DA0GPTIB.STEEN_COLL.RQATL.(CAD115_2008-01-08-20.41.45)
SQLERROR
NOPACKAGE
CURRENTDATA NO
DEGREE
1
DYNAMICRULES
DEFER REOPT NONE
KEEPDYNAMIC NO
IMMEDWRITE NO DBPROTOCOL DRDA
OPTHINT
ENCODING
EBCDIC(05035)
ROUNDING HALFEVEN PATH
DSNT232I !DA0G SUCCESSFUL REBIND FOR
PACKAGE = DA0GPTIB.STEEN_COLL.RQATL.(CAD115_2008-01-08-20.41.45)
REBIND PACKAGE(STEEN_COLL.RQATL.(CAD115_2008-01-08-20.41.45))
SWITCH(PREVIOUS) ;
DSNT232I !DA0G SUCCESSFUL REBIND FOR
PACKAGE = DA0GPTIB.STEEN_COLL.RQATL.(CAD115_2008-01-08-20.41.45)
REBIND PACKAGE(STEEN_COLL2.RQATL.(CAD115_2008-01-08-20.41.45))
SWITCH(PREVIOUS) ;
DSNT217I !DA0G DSNTBRB2 REBIND SWITCH FOR
PACKAGE =DA0GPTIB.STEEN_COLL2.RQATL.(CAD115_2008-01-08-20.41.45) FAILED
BECAUSE THE PREVIOUS OR ORIGINAL COPY DOES NOT EXIST.
DSNT233I !DA0G UNSUCCESSFUL REBIND FOR PACKAGE =
DA0GPTIB.STEEN_COLL2.RQATL.(CAD115_2008-01-08-20.41.45)
46
June 10th: How to predict DB2 Performance Changes
Copyright © 2009 CA. All rights reserved.
DB2 Explain Versioning
> Where does DB2 keep the new Access Path versions

I found no documentation, so my “Sherlock Holmes” toolset (CA
Log Analyzer) uncovered the following:
–
Three REBINDs executed of one package using EXTENDED 
resulted in 103 updates to SPTR
–
A REBIND with SWITCH(PREVIOUS)  resulted in 309 updates to
SPTR
–
Seems like all this information is stored in SPTR
(the table name for SPT – Skeleton Package table)

Make sure your SPT01 has enough space to accommodate
multiple Access Paths

FREE using PLNMGMTSCOPE(xxxxx)
– INACTIVE  original and previous removed
– ALL  all packages removed
47
June 10th: How to predict DB2 Performance Changes
Copyright © 2009 CA. All rights reserved.
(be careful since default)
Summary
> Explain more important as ever
> Influencing the Optimizer
> Proactive performance approach
> Reactive performance approach
> Explain versioning
48
June 10th: How to predict DB2 Performance Changes
Copyright © 2009 CA. All rights reserved.
Terms of This Presentation
This presentation was based on current information and resource allocations as of November
16, 2008 and is subject to change or withdrawal by CA at any time without notice.
Notwithstanding anything in this presentation to the contrary, this presentation shall not serve
to (i) affect the rights and/or obligations of CA or its licensees under any existing or future
written license agreement or services agreement relating to any CA software product; or (ii)
amend any product documentation or specifications for any CA software product. The
development, release and timing of any features or functionality described in this presentation
remain at CA’s sole discretion. Notwithstanding anything in this presentation to the contrary,
upon the general availability of any future CA product release referenced in this presentation,
CA will make such release available (i) for sale to new licensees of such product; and (ii) to
existing licensees of such product on a when and if-available basis as part of CA maintenance
and support, and in the form of a regularly scheduled major product release. Such releases
may be made available to current licensees of such product who are current subscribers to CA
maintenance and support on a when and if-available basis. In the event of a conflict between
the terms of this paragraph and any other information contained in this presentation, the terms
of this paragraph shall govern.
49
June 10th: How to predict DB2 Performance Changes
rights reserved.
Copyright © 2009 CA. All
For Informational Purposes Only
Certain information in this presentation may outline CA’s general product direction. All
information in this presentation is for your informational purposes only and may not be
incorporated into any contract. CA assumes no responsibility for the accuracy or completeness
of the information. To the extent permitted by applicable law, CA provides this document “as
is” without warranty of any kind, including without limitation, any implied warranties or
merchantability, fitness for a particular purpose, or non-infringement. In no event will CA be
liable for any loss or damage, direct or indirect, from the use of this document, including,
without limitation, lost profits, lost investment, business interruption, goodwill, or lost data,
even if CA is expressly advised of the possibility of such damages.
50
June 10th: How to predict DB2 Performance Changes
rights reserved.
Copyright © 2009 CA. All
Resources and Q&A
> Database Management White paper by Phillip Howard

http://www.ca.com/us/products/collateral.aspx?cid=196889
> Business Value of Intelligent Data Access by Susan Larsen
http://www.ca.com/us/whitepapers/collateral.aspx?cid=201547

Access Path Flash Download

http://www.ca.com/us/demos/collateral.aspx?cid=185950
> Questions?
51
June 10th: How to predict DB2 Performance Changes
Copyright © 2009 CA. All rights reserved.