Transcript Aug Executive presentation

DB2 Production Virtualisation
With Profiles
Michigan DB2 Users Group
May 21, 2014
Ken McDonald, BMC Software
Agenda
Why do we need a production clone?
What does our clone need to be a true model of production?
Things we can do in reality
Things we should virtualize
Virtualization help from IBM/DB2
Virtualization difficulties
What’s still missing?
© Copyright 7/18/2015 BMC Software, Inc
2
Why do we need a
production clone?
Either to MEASURE performance in a production-like environment
Or to ESTIMATE performance in a production-like environment
Or to COMPARE one set of access paths with another
A “production clone” is a subsystem that looks and behaves in exactly
the same way as the real production subsystem(s)
-
Databases / Authorities
Hardware
Software levels
© Copyright 7/18/2015 BMC Software, Inc
3
MEASURE performance
We want to KNOW
-
How expensive an application, program or SQL statement will be
How long it will run for
Do we have capacity
© Copyright 7/18/2015 BMC Software, Inc
4
ESTIMATE performance
We want to ESTIMATE
-
-
How expensive an application, program or SQL statement will be
How long it will run for
 EXPLAIN PLAN_TABLE has access method information
 EXPLAIN DSN_STATEMNT_TABLE has a cost estimate
Does this new/changed situation still look OK
 Can be compared to prior incarnations
© Copyright 7/18/2015 BMC Software, Inc
5
COMPARE access paths
We assume what we have in production is OK
When we change something of the environment
-
What gets better?
What gets worse?
Things like
-
New versions of DB2 or z/OS
Perhaps even maintenance
Schema changes
New versions of the application software
Tuning recommendations
Again, EXPLAIN tables have a lot of information to look at
© Copyright 7/18/2015 BMC Software, Inc
6
What does our clone need
to be a true model of production
For ESTIMATING (and COMPARING):
-
All objects defined identically to production
Same statistics as production
-
ALL of them
Identical buffer pool configurations
Identical sort pool, RID pool, statement cache, EDMPOOL
-
Complete this list at your leisure
Identical hardware configurations
-
Mainframe – CPU, memory, etc.
DASD – number of devices and same types, controllers, channel paths
Same maintenance level of DB2
-
And everything else?
Check into Terry Purcell’s (IBM) IDUG and IOD Presentations on the Optimizer
© Copyright 7/18/2015 BMC Software, Inc
7
What else is needed
For ESTIMATING (and COMPARING):
-
Same versions of programs, packages, SQL
 UNLESS we are validating new versions, of course
© Copyright 7/18/2015 BMC Software, Inc
8
What does our clone need
to be a true model of production
For MEASURING:
-
Everything that was needed for ESTIMATING
plus....
-
ALL of the data
 or at least a representative sized sample
-
Some sort of capture/replay technology
© Copyright 7/18/2015 BMC Software, Inc
9
Things we can do in reality
Copying object definitions is easy
-
Set PRI/SEC quantities to low value
Or (better) add DEFINE NO
Copying object stats is also easy (tools help!)
Setting the DB2 configurations MIGHT be possible
-
Use the “same” dsnzparm etc as production?
Hopefully our maintenance levels are close together
The same (or new) programs/SQL as appropriate
© Copyright 7/18/2015 BMC Software, Inc
10
Things we’d like to virtualize
Number of cpus
Power of cpus
zIIP and zAAP configurations
DASD configurations
Anything else?
© Copyright 7/18/2015 BMC Software, Inc
11
Things that don’t matter (much)
With DB2 it’s sometimes hard to know just what IS important
-
And what isn’t
DB2 probably uses more z/OS features than any other z/OS software
-
And it’s using them mostly for performance gains
Check into Terry Purcell’s (IBM) IDUG and IOD Presentations on the Optimizer
© Copyright 7/18/2015 BMC Software, Inc
12
Virtualization difficulties
So how DO you make one LPAR look like another?
Your clone DB2 is highly unlikely to be running in the same physical
environment as your production subsystem
So we really need some way of “fooling” the clone DB2 into behaving
as if the environment WAS the same as production
© Copyright 7/18/2015 BMC Software, Inc
13
Virtualization help from IBM/DB2
IBM introduced the concept of virtual indexes
-
Introduced in DB2 9
Retrofitted to DB2 8 in APAR PK46687 (Aug 2008)
Usage blog from Dec 2009 by Troy Coleman in the notes
Entries in DSN_VIRTUAL_INDEXES could define index changes to be
taken into account during Explain
-
Creation of new indexes
Dropping of existing indexes
DSN_VIRTUAL_INDEXES does have some STATS columns that can be
populated.
-
But, how do you determine the values? Can’t execute RUNSTATS…
These entries can be enabled or disabled allowing quite involved
“what if” analyses to be carried out
© Copyright 7/18/2015 BMC Software, Inc
14
Virtualization help from IBM/DB2
This is not really virtualisation of another environment
More of support for “what if” tuning hypotheses
And there was something fundamental missing......
© Copyright 7/18/2015 BMC Software, Inc
15
Virtualization help from IBM/DB2
Enter PM26475 - PRODUCTION MODELLING FUNCTION (for DB2 9)
And PM26973 - PRODUCTION MODELLING FUNCTION (for DB2 10)
October 2011
Both Had PTFs for DB2 8, 9, and 10 (confused me…)
Was for support of SYSPROC.ADMIN_INFO_SYSPARM displaying this data in a
mixed Data Sharing system.
These are “let’s fool DB2” APARs allowing virtualization of some of the
environmental aspects
-
Sort pool, RID pool, all buffer pools (via PROFILEs)
CPU speed and number of CPUs (via DSNZPARM)
Can all be specified as virtual values
-
Affecting EXPLAIN only and NOT bind
 Which, makes sense.
© Copyright 7/18/2015 BMC Software, Inc
16
Virtualization in DB2 9 and 10
So this virtualisation helps with ESTIMATING
But not MEASURING
-
A simulated CPU cannot run at the speed that it’s simulating!
Neither can DB2 be expected to run parallel tasks on virtual CPUs
© Copyright 7/18/2015 BMC Software, Inc
17
Virtualization in DSNZPARM
Two new parameters are added to the DSN6SPRM macro
-
-
SIMULATED_CPU_SPEED
 “the microseconds of task or service request block (SRB) execution time
per service unit for the CPU being simulated”
 Valid values are OFF and numbers 1 to 2,147,483,647.
SIMULATED_CPU_COUNT
 “the number of local CPUs being simulated”
 Valid values are OFF and numbers 1 to 255.
DEFAULT value of OFF for both *recommended* for production. Use
only for virtualization.
These parameters are on-line changeable with
–SET SYSPARM
© Copyright 7/18/2015 BMC Software, Inc
18
Virtualization using PROFILES
A new area that has not been taken up by many sites is the new
monitoring profile possibilities
Monitoring profiles are INCREDIBLY powerful
Can allow surgical overrides of system parameters at STATEMENT
level
© Copyright 7/18/2015 BMC Software, Inc
19
Virtualization using PROFILES
In our case, PROFILES are used to create virtual environments
Documented In Info Center under
DB2 10 for z/OS > Managing DB2 performance > Managing performance
> Testing DB2 performance
Modeling a production environment on a test subsystem
Create a new profile in DSN_PROFILE_TABLE
We then add profile attributes in DSN_PROFILE_ATTRIBUTES
-
SORT_POOL_SIZE, MAX_RID_BLOCKS, BP0, BP8K2, etc., etc.
And –START the profile
-
With –START PROFILE
© Copyright 7/18/2015 BMC Software, Inc
20
Step by Step (1)
First you need to create a DSN_PROFILE_TABLE
-
Created by install job DSNTIJSG (also in DSNSAMP)
 SYSIBM.DSN_PROFILE_TABLE
 SYSIBM.DSN_PROFILE_HISTORY
 SYSIBM.DSN_PROFILE_ATTRIBUTES
 SYSIBM.DSN_PROFILE_ATTRIBUTES_HISTORY
 SYSIBM.DSN_PROFILE_TABLE_IX_ALL
 SYSIBM.DSN_PROFILE_TABLE_IX2_ALL
 SYSIBM.DSN_PROFILE_ATTRIBUTES_IX_ALL
Then you need to add a row for your profile
-
This is keyed on PROFILEID
PROFILEID is an IDENTITY column GENERATE BY DEFAULT
Manual shows an INSERT of just the PROFILEID to create the row
-
Usage of AUTHID, PLANNAME, COLLID, PKGNAME, etc. are geared towards
MONITORING versus MODELING.
 Notes have Info Center path to both usages of the PROFILE tables
 Monitoring is more complex than Modelling
© Copyright 7/18/2015 BMC Software, Inc
21
Step by Step (2)
Then you add rows in DSN_PROFILE_ATTRIBUTES_TABLE
Profile attributes are
-
-
Keyed on PROFILEID
And require entries in KEYWORDS plus
ATTRIBUTE1/ATTRIBUTE2/ATTRIBUTE3
 SORT_POOL_SIZE, MAX_RID_BLOCKS, BP0, BP8K2, etc., etc.
Depending on what you are modelling
© Copyright 7/18/2015 BMC Software, Inc
22
Step by Step (3)
The DSN_PROFILE_HISTORY and DSN_PROFILE_ATTRIBUTES_HISTORY
that were created will eventually contain an audit trail of activated
profiles
Interesting that they do not have indexes in the DSNTIJSG DDL.
© Copyright 7/18/2015 BMC Software, Inc
23
Step by Step (4)
Now –START the profile
Note – you just say
-START PROFILE
PROFILE_ID is NOT mentioned
DB2 starts ALL profiles that are PROFILE_ENABLED = ‘Y’
-
-
Both Modelling and Monitoring profiles
Would a subsystem have both?
Check STATUS column DSN_PROFILE_HISTORY to find out whether
YOUR PROFILEID was started
© Copyright 7/18/2015 BMC Software, Inc
24
Step by Step (5)
STATUS column in DSN_PROFILE_HISTORY
Look for
REJECTED - DUPLICATED SCOPE SPECIFIED 
REJECTED - INVALID SCOPE SPECIFIED 
REJECTED - NO VALID RECORD FOUND IN ATTRIBUTE TABLE 
or
ACCEPTED 
© Copyright 7/18/2015 BMC Software, Inc
25
Step by Step (5)
Because you start ALL profiles, you need to be careful of what others
are specifying
It is also possible to “restart” profiles
-
-START PROFILE when profiles are already active
Refreshed all profiles according to what is in the profile tables
Check the history table to make sure conflicts have not appeared
© Copyright 7/18/2015 BMC Software, Inc
26
Step by Step (6)
Stopping profiles is easy
-STOP PROFILE
BUT, how do you stop only YOUR PROFILEID?
-
Sorry – you can’t
You COULD change your PROFILEID to have PROFILE_ENABLED = ‘N’
-
Then issue another –START PROFILE
This should terminate YOUR PROFILEID
But what else have you or others changed?!?
© Copyright 7/18/2015 BMC Software, Inc
27
Some problems with profiles
Did you notice that we don’t start A single profile
-
We start ALL the profiles defined in DSN_PROFILE_TABLE
Where PROFILE_ENABLED = “Y”
Wouldn’t it have been useful to be able to start specific profiles?
-
Then we could have defined different profiles for different virtualizations
This also means we must be VERY careful what else we are activating
when we START our profile
© Copyright 7/18/2015 BMC Software, Inc
28
Some other problems
And you have to ask why the SIMULATED CPU SPEED and CPU COUNT
values are in DSNZPARM
-
And everything else is part of a profile?
Have you guessed yet where to get these values from?
-
You could talk to your friendly sysprog....
© Copyright 7/18/2015 BMC Software, Inc
29
Some other problems
Well, the APAR originally shows you how
SET CURRENT DEGREE='ANY‘;
EXPLAIN ALL SET QUERYNO=6475 FOR
SELECT * FROM SYSIBM.SYSDUMMY1;
SELECT
HEX(SUBSTR(IBM_SERVICE_DATA,17,2))
HEX(SUBSTR(IBM_SERVICE_DATA,69,4))
HEX(SUBSTR(IBM_SERVICE_DATA,13,4))
HEX(SUBSTR(IBM_SERVICE_DATA,9,4))
FROM PLAN_TABLE
WHERE QUERYNO=6475;
AS
AS
AS
AS
CPU_COUNT,
CPU_SPEED,
RIDPOOL,
SORT_POOL
DISPLAY BUFFERPOOL command to get their sizes
The Manual has more current information than the APAR
-
Much more complex SQL than above
© Copyright 7/18/2015 BMC Software, Inc
30
Some other problems
BUT, it seems that CPU_COUNT is only populated when a query
actually chooses a parallel access path
Maybe you will need to find an existing PLAN_TABLE row where the
PARALLELISM_MODE = ‘C’ and use its QUERYNO versus the SELECT
provided if it returns a 1 and you know there are multiple CPUs.
© Copyright 7/18/2015 BMC Software, Inc
31
Did DB2 even notice?
When you have a running PROFILE and/or DSNZPARM changes
After an EXPLAIN, look at REASON in your DSN_STATEMNT_TABLE
-
If DB2 took notice
You will see “PROFILEID nnnn”
(quoting the relevant profile id)
© Copyright 7/18/2015 BMC Software, Inc
32
Some other problems
It seems that IBM consider it likely that any ONE modelling subsystem
will only be modelling ONE production subsystem
-
If you want to model multiple targets in a single DB2, YOU will have to
manage all the attribute settings
And different DSNZPARMs as well
© Copyright 7/18/2015 BMC Software, Inc
33
What’s still missing
It’s still not possible to simulate zIIPs or zAAPs or DASD configurations
BUT it is perhaps harsh to complain
What we have been provided with is MUCH more useful than we had
before
I am not IBM nor do I have any knowledge of their futures, but I do
assume they’ve noted some of the same issues.
© Copyright 7/18/2015 BMC Software, Inc
34
Learn more at www.bmc.com
© Copyright 7/18/2015 BMC Software, Inc
35