1 / 36

DB2 Production Virtualisation With Profiles

DB2 Production Virtualisation With Profiles. Baltimore/Washington DB2 Users Group December 11, 2012 Jim Dee, 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

jeb
Download Presentation

DB2 Production Virtualisation With Profiles

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. DB2 Production VirtualisationWith Profiles • Baltimore/Washington DB2 Users Group • December 11, 2012 • Jim Dee, BMC Software

  2. 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

  3. 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)

  4. 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

  5. ESTIMATE performance • We want to ESTIMATE • How expensive an application, program or SQL statement will be • How long it will run for • Does this new/changed situation still look OK

  6. 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 • New versions of the application • Tuning recomendations

  7. 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 bufferpool configurations • Identical sort pool, RID pool, statement cache, EDMPOOL • Complete this list at your leisure • Same maintenance level of DB2 • And everything else?

  8. What else is needed • For ESTIMATING (and COMPARING): • Same versions of programs, packages, SQL • UNLESS we are validating new versions, of course

  9. What does our clone need to be a true model of production • For MEASURING: • Everything that was needed for ESTIMATINGplus.... • ALL of the data • or at least a representative sized sample • Some sort of capture/replay technology

  10. 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

  11. Things we’d like to virtualize • Number of cpus • Power of cpus • zIIP and zAAP configurations • Anything else?

  12. 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 mostly them for performance gains

  13. Virtualization difficulties • So how DO you make one LPAR look like another? • Your clone DB2 is highly unlikely to be running on the same physical kit 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

  14. Virtualization help from IBM/DB2 • Some time ago IBM introduced the concept of virtual indexes • Entries in DSN_VIRTUAL_INDEXES could define index changes to be taken into account during Explain • Creation of new indexes • Dropping of existing indexes • These entries can be enabled or disabled allowing quite involved “what if” analyses to be carried out • Pity you can’t run “virtual Runstats” though • The stats are key to index choices

  15. 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......

  16. Virtualization help from IBM/DB2 • Enter PM26475 (for DB2 9) • And PM26973 (for DB2 10) • These are “let’s fool DB2” apars allowing virtualization of some of the environmental aspects • Sort pool, RID pool, all bufferpools • Cpu speed and number of cpus • Can all be specified as virtual values • Affecting EXPLAIN only and NOT bind

  17. 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

  18. Virtualization in dsnzparm • Two new parameters are added to the DSN6SPRM macro • SIMULATED_CPU_CPEED • “the microseconds of task or service request block (SRB) execution time per service unit for the CPU being simulated” • SIMULATED_CPU_COUNT • “the number the of local CPUs being simulated” • These parameters are on-line changeable with –SET SYSPARM

  19. Virtualization using PROFILES • A new area that has not been taken up by many sites are the new monitoring profile possibilities • Monitoring profiles are INCREDIBLY powerful • Can allow surgical overrides of system parameters at STATEMENT level

  20. Virtualization using PROFILES • In our case, PROFILES are used to create virtual environments • We 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 etcetc • And –START the profile • With –START PROFILE

  21. Step by Step (1) • First you need to create a DSN_PROFILE_TABLE • See current doc • Then you need to add a row for your profile • This is keyed on PROFILE_ID • Which is “INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY NOT NULL” • So you can’t choose your profile id!

  22. Step by Step (2) • Then you need to create a DSN_PROFILE_ATTRIBUTES_TABLE • See current doc • Profile attributes are • Keyed on PROFILE_ID • And require entries in KEYWORDS plus ATTRIBUTE1/ATTRIBUTE2/ATTRIBUTE3 • Depending on what you are modelling

  23. Step by Step (3) • Now create both DSN_PROFILE_HISTORY and DSN_PROFILE_ATTRIBUTES_HISTORY • These will eventually contain an audit trail of activated profiles

  24. Step by Step (4) • Now –START the profile • Note – you just say-START PROFILE • Profileid is NOT mentioned • DB2 starts ALL profiles • Check DSN_PROFILE_HISTORY to find out whether YOUR profileid was started

  25. Step by Step (5) • Look for REJECTED - DUPLICATED SCOPE SPECIFIED REJECTED - INVALID SCOPE SPECIFIED REJECTED - NO VALID RECORD FOUND IN ATTRIBUTE TABLE  • orACCEPTED 

  26. 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

  27. 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 YOURprofileid • But what else have you changed…..?

  28. Some problems with profiles • Did you notice that we don’t start A 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

  29. 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....

  30. Some other problems • Well, the APAR 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)) AS CPU_COUNT, HEX(SUBSTR(IBM_SERVICE_DATA,69,4)) AS CPU_SPEED, HEX(SUBSTR(IBM_SERVICE_DATA,13,4)) AS RIDPOOL, HEX(SUBSTR(IBM_SERVICE_DATA,9,4)) AS SORT_POOL FROM PLAN_TABLE WHERE QUERYNO=6475;

  31. Some other problems • BUT, it seems that CPU_COUNT is only populated when a query actually chooses a parallel access path

  32. 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 nnn”(quoting the relevant profile id)

  33. 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

  34. What’s still missing • It’s still not possible to simulate zIIPs or zAAPs • BUT it is perhaps harsh to complain • What we have been provided with is MUCH more useful than we had before

  35. Questions??

More Related