Transcript Slide 1

IBM Software Group | DB2 Information Management Software
IBM Software Group
Performance Prediction for DB2 Upgrades to DB2 z/OS V9.
New England DB2 User's group
September 17, 2009
Hussaina Husain
Certified Consulting I/T Specialist
IBM East Region zSeries DB2 Tools Technical Sales
1
IBM Software Group | DB2 Information Management Software
Some of the Challenges . . .
• Version 8 started the requirement to REBIND.
• Required rebind for packages from version 2.3
or older.
• Uses an internal flag from the skeleton cursor
table.
• Version 9 continues with rebind required for
version 3.1 .
• New optimizer delivered with V8 and DB2 9
– Rebind highly recommended
2
IBM Software Group | DB2 Information Management Software
More reasons to Rebind for DB2 9 migration
– In CM mode to take advantage:
• DISTINCT and GROUP By enhancements to obtain a sort
avoidance
• Dynamic prefetch functionality
• Global query optimization functionality
• Enhanced page-range screening functionality
• Generalized sparse indexes and in-memory data caching
• Dynamic index ANDing for a star join query
• To exploit 64-bit addressing for CT and PT
– Some plans and packages become invalidated during the
enabling-new-function and New Function mode
3
IBM Software Group | DB2 Information Management Software
Causes of DB2 Access Path Change
• BINDS:
Application Maintenance and Development
Test to Production Migrations
• REBINDS:
Updated Catalog Statistics
New Release/Version of DB2
DB2 Optimizer Maintenance
Table Structure and Index Changes
System Changes (ZPARMS settings)
 New Access Paths can be favorable or not
4
IBM Software Group | DB2 Information Management Software
Planning for the release.
• Understanding how applications currently execute will help isolate the true
new version introduced problems via access path regression analysis
Identify access path changes prior to mass rebind
Analyze and tune any undesirable access path changes prior to rebinds
• Establish a full complete performance baseline
 Average and total resource consumption on system, thread and SQL level
Workload profile
5
IBM Software Group | DB2 Information Management Software
Things To Check Before Migration
•
•
•
•
•
Is the data in PLAN_TABLE complete?
Is the data in DSN_STATEMNT_TABLE complete?
How many versions are there in SYSPACKAGE and SKPT?
How many plans have DBRM’s instead of packages?
How many plans or packages have not been rebound on the current
release?
• How many programs have not been precompiled on the current
release?
– Host variable :
– New reserved words
– What happens if the source is no longer available or the application
(DBRM) is no longer available
• Check for QUERYNO clauses in SQL
6
IBM Software Group | DB2 Information Management Software
Leveraging the full tools migration solution
1
2
3
3
5
6
7
8
Assure that Plan_Tables contain data for packages and Plans while running
DB2 V8
ƒ Use EXPLAIN(YES) on BIND/REBINDS
ƒ Use DB2 Path Checker Explain commands (CKPEXPxx Programs to
Generate commands)
ƒ Copy plan_table into a “backup” plan_table
Migrate to DB2 V9 Conversion Mode
Use DB2 Path Checker TEST commands to check for Changes
Feed statements into DB2 SQL Performance Analyzer for analysis & tuning
Adjust/generate statistics and retry TEST into secondary plan table
Execute REBIND EXPLAIN(YES) if comfortable with result
Run DB2 Path Checker COMPARE command to assure expected path chosen
Repeat steps as needed before issuing Rebinds in subsequent migration
modes (Enabling, and New Function Modes)
 Run DB2 Bind Manager “Catalog Cleanup” to find packages without load
libraries
7
IBM Software Group | DB2 Information Management Software
Integration of Bind Manager, Path Checker
& SQL PA
Modify
Source
Customer activity
SQL
Changed?
AVDBND functions
YES
New
DBRM
TESTPKG
PTHCHK functions
NO
NO
Reset
DBRM
TS
Access
Path
Changed?
YES
SQLPA
Acceptable?
YES
NO
Compile
/ Link
BIND
8
IBM Software Group | DB2 Information Management Software
DB2 Bind Manager
Safely by-passes the DB2 bind process for code changes that do not
alter existing SQL structures in an application
• Automatically eliminates unnecessary binds, processing only the necessary DB2
binds
• Includes a “Catalog cleanup” feature that compares bound packages in a DB2
subsystem to one or more load libraries and generates FREE commands to remove
obsolete or unreferenced packages from the catalog
• Maintains DB2 system integrity using the DBRM Checker feature
• Helps determine whether a bind is required by comparing the consistency tokens for
a plan in a DBRMLIB with the corresponding tokens in the DB2 catalog tables
• Insures that the correct DBRMLIB is being accessed during the compile process
because DB2 Bind Manager does not connect to DB2 to check DBRM validity
• Lets you (re)build DBRMs from the catalog
Helps save CPU – Every time programmers do a bind, there is a cost associated with
it. If programmers can avoid doing binds, they can save CPU which equates to an
overall lower total cost of ownership.
9
IBM Software Group | DB2 Information Management Software
DB2 Path CHECKER
• Path CHECKER has been available for 10 years.
• Is a tool to compare access paths for SQL.
• Has a number of features that make analyzing BIND and
REBIND activity easier.
Commands available are:
• Report Access Paths after Bind (REPORT)
• Report Access Paths without a Bind (EXPLAIN)
Optionally populate the Plan Table
• Compare Access Path after Binds (COMPARE)
• Compare Access Path without Bind (TEST)
• Make Hint to preserve old Access Path
10
IBM Software Group | DB2 Information Management Software
PATH CHECKER Review
• It is designed to test and compare the access data for
large numbers of programs
• It can directly compare
 V8 to V9 plan table even though a column has been added in
V9
 V7 toV8 plan table even though the column lengths are
different and some columns have changed to VARCHAR
• It will accept and process 3 part names if the DDF
connections are available but performance is affected by
normal DDF constraints
11
IBM Software Group | DB2 Information Management Software
DB2 Path Checker Process
PLAN
TABLE
DBRMLIB
Or
DB2 Catalog
BACKUP
PLAN
TABLE
DBRM
CHECKER
History
table
CHANGES
REPORT*
EXPLAIN
REPORT*
ANLOUT*
12
IBM Software Group | DB2 Information Management Software
Use Path CHECKER to Identify Access
Path Changes without Bind/rebind
• TEST DBRM:
TEST DBRM PROGNAME AS PACKAGE COLLID.*
IN OWNERID.PLAN_TABLE
{START WITH pgm2} {BEFORE tmstp} {FROM
CATALOG}
Read a DBRM or DB2 Catalog
Identify SQL that can be process by EXPLAIN.
COMPARE the potential access path to an existing
access path.
13
IBM Software Group | DB2 Information Management Software
Path CHECKER to Identify Current Access Path
and to Create missing plan Table entries
EXPLAIN DBRM dbrmname TO PACKAGE testcoll.* in
creator.PLAN_TABLE.
• Read the DBRM or DB2 catalog
• Take explainable SQL and run EXPLAIN.
• Save the results in the target table (establishes a
baseline).
– Populate both PLAN_TABLE and
DSN_STATEMNT_TABLE
• Report on rebind issues without killing the existing
package
– OPTIONS SQLERROR
14
IBM Software Group | DB2 Information Management Software
Now Check to See What Changed after
Rebind/Bind
COMPARE (PLAN|PACKAGE) qualifier.pgm1 IN
tablename1 TO qualifier2.* IN tablename2 {START WITH
pgm2} {BEFORE tmstp | PREVIOUS | PREVIOUS version}
• COMPARE two sets of access path data from PLAN_TABLE
and DSN_STATEMNT_TABLE.
• Identify differences.
15
IBM Software Group | DB2 Information Management Software
Path Checker Options
• Options
 REPORTCHG | REPORTALL changes only or all
 MATCHSQL1| MATCHSEQUENCE | MATCHQUERYNO
 (NO)MATCHCREATOR
 (NO)SQLERROR
 (NO)CATALOGSQL report SQL statement from catalog
 (NO)REPORTCOSTGT report if path or estimated costs
(PROCSU in DSN_STATEMNT_TABLE) changes
 HISTORY TABLE creator.HIST_TABLE
 CCSID-target CCSID for conversion
 CREATE TABLES to create PLAN_TABLE and
DSN_STATEMNT_TABLE if the tables are not predefined
16
IBM Software Group | DB2 Information Management Software
Path Checker Output
• Outputs
Default Summary Report - 1 liner (SYSPRINT)
Detailed Report - optional (SYSEXPLN DD)
Summary of Access Path Changes Report (SYSCHG)
Changed SQL statements for SQLPA - (ANLOUT)
 Change Summary is inserted into HIST_TABLE
Rebind statements from TEST (RBINDOUT) for no access
path changes or cost is within CPUPCT specified boundary
 Rebind statements from TEST (PBINDOUT) for access
path changes or cost exceeds CPUPCT specified boundary
17
IBM Software Group | DB2 Information Management Software
Path Checker History table
• Can keep a history table of the results of
COMPARE and TEST processing
• Captures the old and new PROCSU from
DSN_STATEMNT_TABLE so you can identify
large swings in estimated cost
18
IBM Software Group | DB2 Information Management Software
Path Checker- SYSPRINT
CKP031I EXECUTING LICENSED PATH CHECKER
V3R1
DEBUG LISTSQL
OPTIONS SQLERROR
CONNECT TO DB8G
SET CURRENT SQLID
= 'P390H'
EXPLAIN DBRM TDBRM2 TO PACKAGE TESTEXP.* IN P390I.PLAN_TABLE
ACCESS PATH FOR
2009/05/04
COLLID - TESTEXP
.TDBRM2
SQL ID-
IN QRYNO M CREATOR TNAME
TBNO AC MC CREATOR
MJN PG JN OP
294 0 P390H
SYSCOLUMNS
1 R
0
CKP225I DSN_STATEMNT_TABLE ESTIMATED COST - EST SVC UNITS
CKP228I DSN_STATEMNT_TABLE ESTIMATED COST - EST CPU
ACCESSNAME
OLD PLAN_TABLE -
RUN DATE
IO SORTUJOG LK PF FN QBNO PLNO MXSQ
N NNNNNNNN
209
108
IS S
1
1
0
19
IBM Software Group | DB2 Information Management Software
Path Checker- SYSEXPLN
20
IBM Software Group | DB2 Information Management Software
Path Checker- SYSCHG
21
IBM Software Group | DB2 Information Management Software
Path Checker- SYSPRINT -Results (Not Good)
IN
*
QRYNO M CREATOR
327 0 P390H
CKP220I
CKP221I
CKP224I
CKP227I
CKP223I
CKP226I
TNAME
SYSCOLUMNS
TBNO AC MC
1 I 2
DATA CHANGE FOR COLUMN PREFETCH
DATA CHANGE FOR COLUMN PREFETCH
DSN_STATEMNT_TABLE ESTIMATED COST
DSN_STATEMNT_TABLE ESTIMATED COST
DSN_STATEMNT_TABLE ESTIMATED COST
DSN_STATEMNT_TABLE ESTIMATED COST
CHANGE
CHANGE
CHANGE
CHANGE
CKP206I TEST COMPLETE FOR PROGRAM = TEST01
PREVIOUS
CKP203I STATEMENTS WITH SAME ACCESS PATH 1
CKP204I STATEMENTS WITH MATCHING EXPLAIN 2
-
CREATOR
P390H
WAS
IS NOW
OLD EST
OLD EST
NEW EST
NEW EST
VERSION =
VERSION =
STATEMENTS WITH DIFFERENT ACCESS PATH 1
STATEMENTS WITHOUT MATCHING EXPLAIN
0
ACCESSNAME
DSNDCX01
IO
L
SVC UNITS
CPU
SVC UNITS
CPU
1
1
146
76
2005-04-11-18.34.53.754785
2005-04-11-18.34.53.754785
FOR PROGRAM TEST01
FOR PROGRAM TEST01
22
IBM Software Group | DB2 Information Management Software
Path Checker- Sample SYSPRINT
IN
*
QRYNO M CREATOR
327 0 PUBLIC
CKP220I
CKP220I
CKP221I
CKP221I
CKP224I
CKP227I
CKP223I
CKP226I
CKP206I TEST
TNAME
TBNO AC MC
SYSCOLUMNS
1 I
CREATOR
2 PUBLIC
ACCESSNAME
DSNDCX01
DATA CHANGE FOR COLUMN MATCHCOLS
DATA CHANGE FOR COLUMN PREFETCH
DATA CHANGE FOR COLUMN MATCHCOLS
DATA CHANGE FOR COLUMN PREFETCH
DSN_STATEMNT_TABLE ESTIMATED COST
DSN_STATEMNT_TABLE ESTIMATED COST
DSN_STATEMNT_TABLE ESTIMATED COST
DSN_STATEMNT_TABLE ESTIMATED COST
COMPLETE
FOR
IO SORTUJOG LK PF FN QBNO PLNO MXSQ MJN PG JN OP
N NNNNNNNN
CHANGE
CHANGE
CHANGE
CHANGE
-
IS
WAS
WAS
IS NOW
IS NOW
OLD EST
OLD EST
NEW EST
NEW EST
1
1
0
0
S
2
SVC UNITS
CPU
SVC UNITS
CPU
38
20
1
1
PROGRAM = TEST01
VERSION =
2006-04-24-22.45.29.330423
PREVIOUS VERSION =
2007-11-09-13.47.05.581274
CKP203I STATEMENTS WITH SAME ACCESS PATH
CKP204I STATEMENTS WITH MATCHING EXPLAIN
CKP219I STATEMENTS OVER CPUPCT
0
1
2
STATEMENTS WITH DIFFERENT ACCESS PATH
STATEMENTS WITHOUT MATCHING EXPLAIN
1
0
FOR PROGRAM TEST01
FOR PROGRAM TEST01
23
IBM Software Group | DB2 Information Management Software
Path Checker OPTIONS REPORTCOSTGT
• PATH CHECKER compares the cost changes
even if the Access Path stays the same.
• To show those cost changes:
OPTIONS REPORTCOSTGT
• Will show Access Paths that did not change but
the estimated cost in
DSN_STATEMNT_TABLE changed.
24
IBM Software Group | DB2 Information Management Software
Path Checker OPTIONS CPUPCT nn
• RBINDOUT will have REBIND command for
– TEST command with no access path changes
– TEST command where the estimated PROCMS is
less than the old PROCMS * (1+CPUPCT) for every
changed ACCESS PATH
PBINDOUT will have REBIND commands for
hazardous REBIND’s
Every TEST command will generate a REBIND
25
IBM Software Group | DB2 Information Management Software
Path Checker- Another Option
• OPTIONS CPUPCT2 for when you only want to create
REBIND packages control card that will have Access
Path changes.
• Ignore packages that do not have access path changes.
• If the estimated cost for every SQL statement is less
than the percentage specified, put the REBIND in
RBINDOUT.
• If the estimated cost for one SQL statement is greater
than the percentage specified, put the REBIND in
PBINDOUT.
• This is a safe method to keep your Access Paths current
without automatic Rebind.
26
IBM Software Group | DB2 Information Management Software
More Features of Path Checker
• What if DBRM’S are Missing
– Usually safe assumption the SQL in SYSSTMT or
SYSPACKSTMT is valid.
– Optional clause for EXPLAIN and TEST commands
‘FROM CATALOG’.
• Supports use of Shadow Catalog
• Makes Existing Access Path a Hint
• Constructs Path Checker commands from
Bind/Rebind control cards
27
IBM Software Group | DB2 Information Management Software
Reasonable Method for Processing
Binds/Rebinds
•
•
•
•
Use PATH CHECKER to analyze the access path data
Use PATH CHECKER to create current access path data for old
packages
Use SQL Performance Analyzer to get the access path details,
cost and tuning advice
Use PATH CHECKER to identify safe REBIND’s
• Use PATH CHECKER to manage ongoing Bind/REBIND activity
Helps determine access path changes so you can make adjustments
before putting the application into production - thereby avoiding
performance issues which could impact the bottom line
28
IBM Software Group | DB2 Information Management Software
SQL Performance Analyzer
•
•
•
•
•
To predict cost and performance of SQL queries without
executing them
Evaluates SQL cost and performance
Gives recommendations for tuning the SQL for maximum
performance
Provides access path information…which road it is going to take
to get the data
Used with DB2 Path Checker, the tool passes the statements with
different access paths to get the cost and more detailed explain
material
Reduces the escalating costs of database queries
Improves the quality of SQL code which results in better
performance which in turn allows a higher transaction rate
and more throughput, which results in better profitability.
29
IBM Software Group | DB2 Information Management Software
What else can SQL PA do?
• Enhanced EXPLAIN report:
Catalog Statistics
Access Path Information
RI Relationships
• Key ADVICE on each SQL statement:
Warnings and Alerts
Guidelines and Recommendations
Performance Notes and Good News
• What If Analysis
SQLPA teaches users how to write better SQL
30
IBM Software Group | DB2 Information Management Software
And more… from SQLPA
• Provides a detailed execution “forecast” report:
– Breaks down SQL by time spent in DB2 components
- Wait Times and Bottlenecks, Path Lengths and I/O Types
• One line summary for each SQL statement evaluated
– Recap of costs
– Quick eye catcher for problem queries
• Acts as a GOVERNOR for Static or Dynamic SQL:
- in QMF, as a preemptive Exit
- in DRDA and IMS or CICS, via a Stored Procedure call
31
IBM Software Group | DB2 Information Management Software
Where does SQL PA run?
• In BATCH, as normal z/OS job
• In TSO, under ISPF interface
• From SPUFI, TSO/ISPF edit session
• In QMF, as a Governor Intercept
• In any DB2 application, via a Stored Procedure
call
32
IBM Software Group | DB2 Information Management Software
How much flexibility does SQLPA have?
• Users can vary the test catalog statistics, via Updates and Inserts
and the RUNSTATS utility or import statistics from another DB2
subsystem
• Users can emulate production volumes in the test catalog, and
fine tune applications while still in early design and development
stages
• Users can also vary the Parameters:
– User Parms describe the Application and DB2 scenarios
– Installation Parms describe the Configuration & Environment
33
IBM Software Group | DB2 Information Management Software
SQL PA processing task
34
IBM Software Group | DB2 Information Management Software
Process SQL in a PLAN using existing plan records
If a plan or
package was
bound with
EXPLAIN
(YES) option,
choice is given
to show
the plan
generated
at bind or
issue a
dynamic
explain
35
IBM Software Group | DB2 Information Management Software
Perform What-If on a single SQL statement (slide 1 of 2)
Use the
line
command
W to
perform the
what-if.
36
IBM Software Group | DB2 Information Management Software
DB2 SQL PERFORMANCE ANALYZER
Architecture
SQL/PA
ISPF
Edit/Browse
SQL/PA
Batch Job
QMF
DB2
Catalog
DBRM(s)
/ Seq. File /
PDS
Plan/package
QMF saved
queries
ANL4QMFxx- single
SQL statements
Governor exit
Application
Stored Procedure
Call
(Local/Remote)
QM/Path
Checker
ANLOUT
Cost
Summary
Report
Query
Limit
Report
SQL/PA
ANLPGMxx
Enhanced
Explain
Report
Generic or
Private Plan
Tables
ANLPARM
ANLCNTL
SQL/PA
PARMS
SQL/PA
SUBSYSTEM
PARMS
Detailed
Trace
Report
uses CAF or WLM
37
IBM Software Group | DB2 Information Management Software
Horizontal & vertical integration from OMPE perspective
…
OMEGAMON XE
for Mainframe
Networks /
for zNetview
Tivoli
OMEGAMON
Monitors
OMEGAMON
XE for CICS
OMEGAMON
XE for IMS
DB2
Path Checker
DB2
SQL PA
OMEGAMON XE
for DB2 PE
Visual Explain
OSC
Optimization Expert
Information Management Tools
DB2
Query Monitor
Control Center
OMEGAMON
XE for z/OS
….
=> DWL or additionally using
OMEGAMON DE (Dashboard Edition)
38
IBM Software Group | DB2 Information Management Software
Tivoli Omegamon XE for DB2 Performance Expert
(OMPE) can assist with real time and batch system
monitoring
DB2 PM/PE/BPA
OMEGAMON
In depth Reporting
DB2 Monitoring
Performance DB
Expert analysis
Buffer Pool Analysis
DB2 Connect Monitoring
Cross-zSeries monitoring
Consistent “look and feel”
Web browser, 3270 VTAM
Integrated “dashboard”
Online monitoring
The expertise of two leaders in the industry have come together!
39
IBM Software Group | DB2 Information Management Software
OMEGAMON XE For DB2 PM/PE
Identify, quantify excessive resource usage on a system, plan and package basis
Real Time Thread Analysis
Thread performance (elapsed,
CPU, getpage info)
Thread Detail (lock detail, SQL
detail, plan & package level
Triggers, Procedures, & UDFs
Real Time – DB2 subsystem
Virtual Pool & EDM Pool analysis
Pool performance
Pool snapshot detail
Locking & Logging
Application Trace Facility
Detailed performance tracing
Choice Of Interfaces (TEP XE, PE, 3270
Classic & CUA)
Object Analysis
I/O & getpage analysis
Correlate activity by object &
applications
Lock Conflicts
Near-Term Historical
Near-term history online
Historical Analysis
Batch reporting
XE Historical analysis
DB2Plex Monitoring View
View CF structures
Global lock analysis
Automation capabilities
40
IBM Software Group | DB2 Information Management Software
OMEGAMON XE For DB2 PE Provides:
• World Class Batch Reporting – In-depth problem
analysis
Performance Warehouse – (PEclient GUI)
Expert analysis (ROT and SQL Performance
queries)
DB2 Connect Monitoring (Classic, TEP, PE GUI)
More granular snapshot history via the PE GUI for
online ad-hoc problem analysis
Buffer Pool Analysis
41
IBM Software Group | DB2 Information Management Software
OMPE Reporting for trend analysis
Application A
Dist.Appl A
DB2
Application B
OPx
Application C
DB2 Connect
IFI
Dist.Appl B
Dist.Appl C
DB2 event trace processing
SMF
GTF
Batch
Historical
(Collector &)
Reporter
DB2 Load
Reports
PWH
/ PDB
 Post processing analysis (Report / Trace)
 Statistics
 Accounting
 Deep dive with performance traces
 SQL Activities
 Locking Activities
 I/O Activities
 Audit
 Record Trace
 Explain
 Exception Reports
 Trend analysis
42
IBM Software Group | DB2 Information Management Software
Broad Flexibility in Trace Collection, Reporting, saving to DBs
DB2 event trace processing
DB2 Trace collection
Processing / Reporting
Performance DB archiving
SMF
Job - Scheduler
GTF
REPORT
Historical
BatchCRD
O
P
B
u
f
f
e
r
Se
q
ISPF CRD
Se
q
PWH CRD
& SQL Act.
Se
q
Near-Term
History
Se
q
TRACE
Reporting
Programs
SAVE
DB2
Load
Utility
PDB
VSAM
FILE
PWH
VSAM
PE SERVER
VSAM
Classic
ATF
VSAM
43
IBM Software Group | DB2 Information Management Software
Expert Analysis using Rule-of-Thumb and Expert Queries to detect performance issues
Select table and column to be added to the 'Value expression'
Depending on the selected ROT
and the performance data you
may get a result matrix, select
row and column to get more
specific information
Define thresholds and recommendations
Predefined expert rules
 Database table column wizard
 Point and drop support
Analyze using
 Single rule
 Cluster of rules
 Zoom-in
44
IBM Software Group | DB2 Information Management Software
OMPE-PWH-Analysis Support Performance Queries
45
IBM Software Group | DB2 Information Management Software
OMPE-PWH-Analysis Support – Performance Queries
The result can be sorted by
clicking on any header title
The result can be saved or
shown in a browser window
46
IBM Software Group | DB2 Information Management Software
OMPE-Buffer Pool Analysis
 Collects buffer pool data
 as summary or detailed data
 continuously or in sampling mode
 in Online and Batch
 Generates various reports and displays results in
multiple formats for BP and GBP (including
graphical end-user interface)
 Provides expert knowledge and recommendations
 Recommends object placements, BP size &
thresholds
 Generates ALTER statements for the
recommendation
 Provides simulation for planned changes
 Long-term analysis to detect trends, hourly, daily,
and weekly peaks, repetitive performance pattern,
unbalanced resource usage
 Makes it easy to tune your buffer pools
Tuning
DB2 for
OS/390
z/OS
Collection &
Reporting
Tuning
Review
and
Analysis
Verification
Iterative
Simulation
Expert analysis
Tuning
Expert Analysis
and
recommended
changes
Verification
47
IBM Software Group | DB2 Information Management Software
More Control of your System and Application Performance
PE Client
ISPF Online Monitor
Classic Interface
Tivoli Enterprise Portal
48
IBM Software Group | DB2 Information Management Software
Tivoli Enterprise Portal (XE Web browser Interface)
Easy to use
Browser controls
Plug and Play components
Personalized
Views
View
Zoom
Intelligent
Linking
Splitter
controls
Persistent customized
workspaces
49
IBM Software Group | DB2 Information Management Software
OMPE-Situation Analysis
What are the details?
What is the problem?
Any expert
advice?
Any Predefined Actions?
50
IBM Software Group | DB2 Information Management Software
OMPE-A Basic Example Situation
Alert On Threads With More Than ‘n’ Getpages
Start/stop
situation
Distribution tab to specify where situation
runs.
Expert advice is customizable.
Action tab to execute command.
Specify alert criteria.
This may include one or
multiple attribute
criteria.
Specify sampling
interval
Specify severity and whether to
run at Omegamon startup
51
IBM Software Group | DB2 Information Management Software
OMPE-Use Persistence Option To Smooth Alert Spikes
With a persistence
option the
situation must me
true ‘n’ times
before the alert
fires
Click ‘Advanced’
to specify
persistence
options
User persistence to eliminate
alerts that are spikes or outliers
52
IBM Software Group | DB2 Information Management Software
OMPE-Alert Flexibility
The XE GUI
provides much
more flexibility
for alerts and
alerting
More detailed alerts
mean more meaningful &
useful alerts. May
require fewer situations
be created.
Specify multiple attributes
with And/Or logic
53
IBM Software Group | DB2 Information Management Software
OMPE-Situations – Usage And Benefits
‘Action’ To Perform Commands And Corrections
Where
command is
executed
Attribute substitution in
the command line
System command may be executed when the
situation is true
Examples of actions include:
DB2 thread kill command
Issuing messages to the console
Any valid z/OS console command
54
IBM Software Group | DB2 Information Management Software
OMPE-Storage Consumption in TEP
Product-provided situations
KD5_MVS_Extended_CSA_Warning
KD5_MVS_low_private_Warning
KD5_Storage_Cushion_Warning
KD5_DBM1_Storage_Usage_Warnin
KD5_Real_Storage_Usage_Warning
KD5_LPAR_CPU_Usage_Warning
KD5_DB2_CPU_Usage_Warning
KD5_DB2MSTR_Usage_Warning
KD5_DB2DBM1_Usage_Warning
55
IBM Software Group | DB2 Information Management Software
OMPE-DB2 Messages in TEP
Product-provided
situations
KD5_DSNT376I_Timeout_Cr
itical
KD5_DSNT375I_Deadlock_C
ritical
KD5_DSNU621I_Runstats_C
ritical
KD5_DSNU511I_Recovery_
Critical
KD5_DSNP007I_Space_Criti
cal
KD5_DSNU548I_Recovery_
Critical
KD5_DSNT500I_ResUnavail
_Warning
KD5_DSNT501I_ResUnavail
_Warning
KD5_DSNP001I_Space_Warn
ing
56
IBM Software Group | DB2 Information Management Software
Exploiting Dynamic Workspace Linking
Example – Drill Down From OMEGAMON XE For DB2
PM/PE To OMEGAMON XE For CICS
OMEGAMON XE For DB2
PM/PE CICS Connection
display
Drill down
from DB2 to
CICS detail
within the TEP
OMEGAMON XE For CICS
Region Overview detail
display
57
IBM Software Group | DB2 Information Management Software
Tivoli Enterprise Portal and OMEGAMON
The TEP With OMEGAMON Dashboard Edition
enables integrated multi-component views
Customizable
graphic overview
User-definable drill
downs for detail
Combine
information from
multiple sources
More flexible and
granular than 3270
z/OS
CICS
IMS
DB2
MQ
58
IBM Software Group | DB2 Information Management Software
Using Policies For Dynamic Performance
Management
Check if threads
creation into DB2 is
bottlenecked
Check if the problem is
impacting CICS and
response time is bad
If true issue console
commands
If so dynamically adjust
settings in DSNZPARM
59
IBM Software Group | DB2 Information Management Software
OMPE-Using Policies For Dynamic
Performance Management
Issue SET SYSPARM command
to activate new ZPARM settings
60
IBM Software Group | DB2 Information Management Software
Detection of Exceptional Situations with Take Action
In Real-time with
 Pre-defined Situations
 Audio and Video Alerting
 Manual or Automatic
Take Action
 Optional running in the
Background with Alert
via User Exit
In Batch with dedicated
Exception Reports
 Exception Profiling to
better find threshold
values which fits to the
customers workload
61
IBM Software Group | DB2 Information Management Software
Summary of functions
• Integrated cross zSeries monitoring
– OMEGAMON Classic (VTAM)
– OMEGAMON XE client GUI
(TEP)
– DB2 PM/PE ISPF and PE client GUI
• The DB2 PM/PE Batch Reports
– Statistics
– Accounting
– Subsystem Parameters
– Locking
– SQL Activity
– I/O Activity
– Utility
– Audit
– Record Trace
– Explain
• The DB2 Connect Monitoring
– OMEGAMON Classic
– TEP/XE
– PE Client
• The DB2 PM/PE Performance
Warehouse and Performance DB
• The Buffer Pool Analysis function
coming with the PE product offering
• One single Server
– Only one started task per LPAR
– One separate collector subtask
per DB2 subsystem
– Complementary functions
• Near-term history (OM)
• Short-term history (OM)
• Object analysis (OM)
• Snapshot history (PE)
• Exception processing
(PE/OM)
• PWH processing (PE)
62
IBM Software Group | DB2 Information Management Software
DB2 Query Monitor
to identify, quantify excessive resource usage on a
SQL statement level
SQL
• Low overhead SQL statement monitor
• Identify SQL requests which are consuming
excessive resources and may be preventing critical
requests from completing on schedule
• Proactively manage DB2 resources
• React quickly and effectively to DB2 problems like
inefficient SQL or inadequate object structures
• Determine which tables and indexes are actually being used
63
IBM Software Group | DB2 Information Management Software
DB2 QUERY MONITOR
 ISPF and GUI interfaces
Supports DB2 z/OS
Supports DB2 V9
• SQL Monitor
Static / Dynamic SQL
Current
Historical
• Auxiliary Functions
Exceptions
Capture negative return codes
Capture DB2 commands
64
IBM Software Group | DB2 Information Management Software
DB2 QUERY MONITOR
Low overhead SQL monitor; requires no traces
• DATA collected and
available to view
•
•
•
•
•
•
•
•
SQL metrics
DB2 object access
SQL text and host variables
DB2 commands
Negative SQLCODES
Expanded and grouped
Information about
exceptions
Buffer Pool Statistics
Delays
• History
Interval-based VSAM
Datasets for data storage
Intervals of data viewable
online
Data can be loaded into DB2
Tables
User controlled
•
•
number of intervals
retained
Interval length
65
IBM Software Group | DB2 Information Management Software
DB2 Query Monitor - User Interfaces
• ISPF – flexible / configurable – filtering data for display
• GUI
– View across enterprise from a single console
– Consolidated view of data sharing group activity
– Proactive event notification
• Provides alerts to exceptional events on a monitored DB2
subsystems
• Knowledge-based analysis examines exceptional events to
determine underlying problems
– Autonomic functionality enables DB2 Query Monitor
to execute user-configurable responses
• including e-mail notifications
• corrective actions such as console commands and batch job
submission
• WEB- View message board of alerts
66
IBM Software Group | DB2 Information Management Software
DB2 Performance Management Tools
Provide tools to predict, monitor and tune DB2 systems and applications
to obtain optimal performance and lowest cost
OMEGAMON XE
for DB2 PE
Tune and
Control
Monitor
Analyze
DB System
Applications
DB2 SQL
Network
Performance
Analyzer Predict performance
and tune query
DB2 Query
Monitor
Analyze & Predict
Access Path Changes
DB2 Path
Checker
Bind Avoidance & DBRM checker
Bind Manager
67
IBM Software Group | DB2 Information Management Software
For DB2 Tools:www.ibm.com/software/data/tools
68
IBM Software Group | DB2 Information Management Software
How old are your Packages?
SELECT BINDYEAR ,COUNT(*) AS COUNT
FROM TABLE (SELECT COLLID, NAME,
YEAR(BINDTIME) AS BINDYEAR FROM
SYSIBM.SYSPACKAGE T1)
AS SYSPACKAGE
GROUP BY BINDYEAR ;
NOTE – SELECT FROM TABLE (SELECT****
is a V8 SQL enhancement – V7 requires a VIEW
for the SELECT statement.
69
IBM Software Group | DB2 Information Management Software
Check for data in Plan_Table
SELECT SUBSTR(COLLID,1,18) AS COLLID
, SUBSTR(NAME,1,8) AS NAME
FROM SYSIBM.SYSPACKAGE A
WHERE CREATOR = 'P390H' AND VALID = 'Y'
AND OPERATIVE = 'Y' AND VERSION = ''
AND NOT EXISTS
(SELECT 1 FROM P390H.PLAN_TABLE B
WHERE A.COLLID = B.COLLID AND A.NAME =
B.PROGNAME AND B.VERSION = '' );
70
IBM Software Group | DB2 Information Management Software
Check for data in Plan_Table Version
SELECT SUBSTR(COLLID,1,18) AS COLLID
, SUBSTR(NAME,1,8) AS NAME
FROM SYSIBM.SYSPACKAGE A
WHERE CREATOR = 'P390H' AND VALID = 'Y'
AND OPERATIVE = 'Y' AND VERSION > ''
AND NOT EXISTS
(SELECT 1 FROM P390H.PLAN_TABLE B
WHERE A.COLLID = B.COLLID AND A.NAME =
B.PROGNAME AND A.VERSION = B.VERSION
AND A.BINDTIME = B.BIND_TIME );
71
IBM Software Group | DB2 Information Management Software
Check For Packages Without
DSN_STATEMNT_TABLE Data
SELECT DISTINCT 'EXPLAIN DBRM ' CONCAT RTRIM(NAME)
CONCAT ' TO PACKAGE ' CONCAT RTRIM(COLLID)
CONCAT '.* IN XIBMUSR.PLAN_TABLE '
FROM SYSIBM.SYSPACKAGE A
WHERE CREATOR = 'P390H'
AND VALID = 'Y'
AND OPERATIVE = 'Y'
AND VERSION = ''
AND NOT EXISTS
(SELECT 1 FROM P390H.DSN_STATEMNT_TABLE B
WHERE A.COLLID = B.COLLID
AND A.NAME = B.PROGNAME);
72
IBM Software Group | DB2 Information Management Software
How Many Versions Are There In
SYSPACKAGE And SKPT?
SELECT COLLID, NAME, COUNT(*)
FROM SYSIBM.SYSPACKAGE
GROUP BY COLLID, NAME
ORDER BY 3 DESC
• Reasonable numbers are 3,4 or 5 versions
depending how often the software is updated
73
IBM Software Group | DB2 Information Management Software
How Many Plans Have DBRM’s Instead
Of Packages?
SELECT PLNAME,COUNT(*)
FROM SYSIBM.SYSDBRM
GROUP BY PLNAME
ORDER BY 2 DESC
• You would like this count to be zero but
purchased products and old untouchable
applications can prevent this
74
IBM Software Group | DB2 Information Management Software
How Many Packages Have Not been
REBOUND On The Current Release?
SELECT COLLID, NAME, VERSION
FROM SYSIBM.SYSPACKAGE
WHERE BINDTIME < '2004-01-01-00.00.00.000000'
ORDER BY BINDTIME
• Pick your date carefully!
75
IBM Software Group | DB2 Information Management Software
How Many Plans Have Not Been
REBOUND On The Current Release?
SELECT NAME, BINDDATE
FROM SYSIBM.SYSPLAN A
WHERE BINDDATE < '040101'
OR BINDDATE > '900101'
AND EXISTS
(SELECT 1 FROM SYSIBM.SYSDBRM B
WHERE A.NAME = B.PLNAME)
ORDER BY BINDDATE
• Notice that BINDDATE is not a date data type nor is it year 2000
friendly
76
IBM Software Group | DB2 Information Management Software
How Many Programs Have Not Been
PRECOMPILED On The Current
Release?
SELECT COLLID, NAME, VERSION, HOSTLANG,
PCTIMESTAMP
FROM SYSIBM.SYSPACKAGE
WHERE PCTIMESTAMP < '2004-01-0100.00.00.000000'
• Pick the date carefully!
77
IBM Software Group | DB2 Information Management Software
Check For PRECOMPILE Issues
• Host variable :
• New reserved words
SELECT DISTINCT 'EXPLAIN DBRM ' CONCAT
RTRIM(NAME)
FROM SYSIBM.SYSPACKAGE A
WHERE PCTIMESTAMP < '2004-01-01-00.00.00.000000'
AND VALID = 'Y'
AND OPERATIVE = 'Y'
78
IBM Software Group | DB2 Information Management Software
Check For QUERYNO Clauses In SQL
SELECT COLLID, NAME, CONTOKEN,
QUERYNO , STMT
FROM SYSIBM.SYSPACKSTMT
WHERE STMT LIKE '%QUERYNO%'
AND SUBSTR(STMT,9,7) = 'DECLARE'
AND STMT LIKE '%CURSOR%'
79
IBM Software Group | DB2 Information Management Software
Multiple Cursors With The Same
QUERYNO Are A Problem
• It will run just fine
• PLAN_TABLE and DSN_STATEMNT_TABLE will
have the access data from multiple statements with
the same queryno
• Requires source code change and recompile to fix.
80
IBM Software Group | DB2 Information Management Software
REBIND Only Current
SELECT 'REBIND PACKAGE (' CONCAT RTRIM(COLLID)
CONCAT '.(' CONCAT RTRIM(NAME)
CONCAT '.(' CONCAT RTRIM(VERSION) CONCAT '))'
FROM SYSIBM.SYSPACKAGE A
WHERE VERSION > ' '
AND CONTOKEN =
(SELECT MAX(CONTOKEN) FROM SYSIBM.SYSPACKAGE B
WHERE A.COLLID = B.COLLID
AND A.NAME = B.NAME);
• Only rebind the most current package
81