IBM Software Group

1
IBM Software Group
IBM Technical Exchange Colorado DB2 User Group
Kevin Harrison Consulting IT Architect DB2 for
z/OS Technical Support Data Management West
Region kharrison_at_us.ibm.com
2
General Info

• DB2 V8
• Where are we in the Colorado Area???
• Just Announced! First Class Savings from IBM
• Your clients training dollars will go further in
  2007 with 500 savings on public software classes
  scheduled in the first quarter in the U.S. What
  better time for your clients to attend software
  training when their business goals are set and
  their IT needs are immediate? Clients can enroll
  starting December 1 through March 28, 2007, in
  any eligible IBM publicly scheduled software
  classes in the U.S. and receive 500 off the
  tuition. At enrollment, they must reference
  priority code 6N2KJO4W to receive their discount.
  
• All public IBM software classes listed in the IBM
  Training Catalog in the US (software category)
  are included in this offer, excluding Lotus,
  Microsoft, Tivoli and Linux. All classes must
  commence on or after January 1st and complete by
  March 30, 2007, to take advantage of this offer.

3
Maintenance DB2 z/OS

• Maintenance level
• If You are on V8 you should be 0609 ( sweet
  spot)
• Most general query regression PTFs are in 0606
  0607
• However there were a large number of specialized
  query regression PTFs in 0611
• Relative to
• Data skew
• Sort intensive
• Optimizer costing algorithm changes

4
Visual Explain

• Visual Explain for DB2 for z/OS has no official
  book. However, the following resources are
  available
• Visual Explain has integrated online help. You
  can download Visual Explain and the integrated
  help fromhttp//www.ibm.com/software/data/db2/zos
  /osc/ve/index.html
• Some presentations about Visual Explain function
  are available- Major Optimization Enhancements
  in DB2 z/OS Version 8- DB2 for z/OS Visual
  Explain Overview - DB2 for z/OS Visual Explain
  Query Graph Details
• The Version 8 Everything Redbook, SG24-6079,
  contains about 15 pages on Version 8 Visual
  Explain (which also works with DB2 V7). See
  section 10.19, page 823 or Acrobat page 853
  http//www.redbooks.ibm.com/abstracts/SG246079.htm
  l
• The Version 8 Performance Topics Redbook,
  SG24-6465, contains about 15 pages on Visual
  Explain. See section 3.15, page 110, Acrobat page
  140 http//www.redbooks.ibm.com/abstracts/SG24646
  5.htmlCheck the Visual Explain Web page to get
  more details and information about what has
  changed when Visual Explain is periodically
  updated http//www.ibm.com/software/data/db2/zos/
  osc/ve/index.html

5
Service Level Determination

• Problem
• I need to determine the service level of a
  specific DB2 module so that IBM Software
  Support can diagnose my problem. How do I find
  the service level of a specific DB2 module in the
  z/OS, OS/390, or MVS/ESA environments?  
• Solution
• To determine the service level of a specific DB2
  module, use one of the following methods
• Use the MVS software installation and
  maintenance tool, SMP/E, to query the SMP/E
  libraries for the installation date and PTF or
  APAR level for a specific DB2 module.
• Run the DB2 DIAGNOSE utility with the DISPLAY
  MEPL option, which is described in the DB2
  Utility Guide and Reference.The output from this
  utility lists each module, the most recent PTF or
  APAR that was applied to the module, and the date
  on which the module was compiled for the PTF or
  APAR.

6
Private Protocol Determining if plans or
packages have a remote location dependency

• Sample REXX programs analyze the DB2 subsystem
  catalog to see if any plans or packages that were
  bound with DBPROTOCOL(PRIVATE) have a remote
  location dependency. If they do, the tool
  generates commands to convert those plans or
  packages to use DRDA protocol. Also, the programs
  analyze any three-part name aliases in the
  catalog to determine if corresponding two-part
  name aliases must be created at the indicated
  remote locations.
• The REXX program DB2TP2D7 can be run only against
  DB2 Version 7 subsystems or DB2 Version 8
  subsystems whose catalogs are at the Version 8
  compatibility mode (CM) level.
• The REXX program DB2TP2D8 can be run only against
  DB2 Version 8 subsystems whose catalogs are at
  the V8 new-function mode (NFM) level.
• Document information
• Product categories  Software  Data Information
  Management  Databases  DB2 Universal Database for
  z/OS  Reference  7008509  

7
Guarding against Access Path Regressions

• The recommended process for migrating plans or
  packages where the access path is critical is
  described below. Any of the three processes will
  provide better capability to fallback to the
  Version 7 access paths in case any degradations
  occur on Version 8. These processes are general
  to any DB2 version migration, and are not
  specific to Version 8. It is anticipated that the
  majority of access paths will improve in Version
  8, or at least be equivalent to Version 7.The
  goal is to at least maintain a copy of the
  Version 7 access paths in the PLAN_TABLE, a
  collection containing packages bound in Version
  7, and a collection of packages bound in Version
  8. A Version 7 collection is needed, in addition
  to the Version 8 collection, to allow fallback to
  the prior collection, and the PLAN_TABLE output
  is required if you plan to use OPTHINTS or simply
  to compare Version 7 with Version 8 access
  paths.Option A1. Bind the affected
  plans/packages to a new Version 8 collection
  (using any collection name you wish), specifying
  EXPLAIN(YES) so that you can compare them with
  your current Version 7 access paths, that are in
  existence in the PLAN_TABLE.2. In the package
  list, place the Version 8 collection before the
  Version 7 collection.3. If you run into an
  access path problem, free that package from the
  Version 8 collection, allowing the next execution
  to pickup the Version 7 collection. This provides
  an immediate solution to the performance problem,
  and allows more time to analyze the access path
  regression.Option BThis approach is similar
  to Option A, though that it uses a proactive,
  rather than reactive approach to identify access
  path regressions. Keep two Version 8 collections,
  and only move these into the Version 8 collection
  that occurs first in the package list once you
  are certain that the query performance in Version
  8 is the same or better than that in Version
  7.Option CIf you do not have your existing
  Version 7 access paths saved in the PLAN_TABLE,
  then another alternative is to BIND (to a
  separate collection) or REBIND (the existing
  collection) in Version 7 with EXPLAIN(YES), so
  that Version 7 access paths are known. Note
  There is no guarantee that a new Version 7
  bind/rebind will choose the exact same path as
  the current production access path. A new bind
  (or rebind) in Version 8 should use the proactive
  (option B) or reactive (option A) approach to
  ensure that a fallback to the Version 7
  collection is possible in the package list.In
  general, OPTHINTs should only be used when DB2
  does not choose the desired access path after all
  relevant statistics are made available via
  RUNSTATS. However, it is sometimes necessary to
  migrate a Version 7 hint to Version 8. In this
  situation, the user should be sure to have these
  Version 8 OPTHINT APARs appliedPK02481PK07750
  PK10015These fixes cover various OPTHINTs
  problems, some of which deal specifically with
  migrating hints from Version 7 to Version 8.

8
Volatile Usage

• An alternate use for volatile tables is to simply
  favor index access whenever possible on a basic
  table. In this situation, multiple indexes on the
  table would be acceptable.Regardless of how the
  volatile table is used, there are certain
  optimization techniques that are disabled (such
  as list prefetch and multi-index access) for
  tables declared as VOLATILE.  

9
zIIP Redirect Software Maintenance

• DB2 V8 zIIP support
• PK18454 DRDA
• PK19920, PK27712, PK30087 Utilities
• PK19921, PK27578 Parallel Queries
• II14219 Info Apar with additional information
• Support for zIIP related instrumentation changes
  in IFCIDs 3,147,148,231,239 for zIIP usage
  reporting, monitoring and projection
• PK25395 IBM Tivoli OMEGAMON XE for DB2
  Performance Expert on z/OS
• PK29966 DB2 Performance Expert V210
• PK29967 DB2 Performance Monitor V810
• zIIP support maintenance info for z/OS,
  SDSF,RMF,WLM,BCP
• http//www.ibm.com/systems/z/ziip/gettingstarted/p
  rereqs.html
• RETAIN search keyword zIIP/K for zIIP related
  Apar/PTF information.

10
SYS1.PARMLIB(IEAOPTxx) Parameters

• PROJECTCPU YES / NO
• Set to YES for projecting zIIP redirect when
  zIIP is not installed
• Shown under APPL IIPCP in the RMF Workload
  Activity Report
• Shown under IIPCP CPU in the Omegamon DB2PE
  Accounting Report
• ZIIPAWMT 12000 (default value 12 ms)
• Use the default value
• Alternate Weight Management time spent waking
  up idle zIIP to
• compete for work or request for help from CP

11
z9 Display CPU information with zIIP

• D MCPU
• IEE174I 10.37.03 DISPLAY
• PROCESSOR STATUS
• ID CPU SERIAL
• 00 02B29E2094
• 01 02B29E2094
• 02 A 02B29E2094
• 03 I 02B29E2094
• CPC ND 002094.S28.IBM.02.00000004B29E
• CPC SI 2094.724.IBM.02.000000000004B29E
• CPC ID 00
• CPC NAME SYSS01
• LP NAME STLABH2 LP ID 2
• CSS ID 0
• MIF ID 2
• ONLINE - OFFLINE . DOES NOT EXIST W
  WLM-MANAGED
• N NOT AVAILABLE

12
Monitoring System level zIIP redirect with zIIP
installed
RMF CPU Report for CLI DRDA Workload
C P U A C T I V I T Y

z/OS V1R7 SYSTEM
ID H2 RPT
VERSION V1R7 RMF CPU 2094 MODEL 724 H/W
MODEL S28
---CPU--- ONLINE TIME LPAR BUSY MVS
BUSY NUM TYPE PERCENTAGE TIME PERC
TIME PERC 0 CP 100.00 22.49
22.49 1 CP 100.00 21.72
21.72 CP TOTAL/AVERAGE 22.11
22.11 2 AAP 100.00 0.10
0.10 AAP AVERAGE 0.10
0.10 3 IIP
100.00 32.47 32.47 IIP AVERAGE
32.47 32.47
CP CPU
zAAP CPU
zIIP CPU
zIIP Redirect at the LPAR level 42
13
How to Activate Parallelism

• Static queries DEGREE parameter on bind
  Plan/Package
• Dynamic queries SET CURRENT DEGREE special
  register
• '1' -- DB2 will not consider parallelism for
  queries
• 'ANY' - DB2 will use parallelism for queries
  where possible
• ZPARMS
• CURRENT DEGREE in DSNTIP4 (CDSSRDEF ANY)
• Default CURRENT DEGREE for dynamic queries (no
  effect on static queries)

14
How to Activate Parallelism (cont)

• To avoid query regression
• Parallelism can be controlled with 2 ZPARMs
• PARMMDEG for the maximum degree of parallelism in
  DSNTIP4
• Ensures single query does not consume all
  parallel tasks
• Minimizes runtime regression if large number of
  resources are not available
• SPRMPTH
• Threshold to disable parallelism for short
  running queries

15
How to Monitor Parallelism
16
How to Monitor Parallelism

• Each SRB produces an accounting record (as well
  as the main TCB)
• You can tell DB2 to roll-up information into one
  accounting trace record via ZPARM
• PTASKROLYES
• IFCID 221 gives subpipe breakdown
• IFCID 222 gives rows qualified by subpipe
• IFCID 231 gives CPU/Elapsed by Parallel Task
• Always enable for long // queries!! (low
  overhead)

17
(No Transcript)
18
(No Transcript)
19
(No Transcript)
20
(No Transcript)
21
(No Transcript)
22
I/O Items to consider

• Striping becomes more important
• Larger CI Sizes
• Tables in V8
• Indexes in V9
• Impacts on storage
• New defaults for Prefetch, Preformat, and
  thresholds for CI limits
• If you are I/O bound are you using your hardware
  effectively
• PAV
• Striping ( Logs, tablespaces)
• Flashcopy operations
• MIDAWS

23
(No Transcript)
24
(No Transcript)
25
(No Transcript)
26
(No Transcript)
27
(No Transcript)
28
Insert/Update/Delete Performance Scalability

• LC19 Log latch contention relief in data sharing
• Active and archive log I/O improvement
• Archive log BSAM I/O striping
• ??Active log read buffers per Start IO increased
  from 15 to 120
• Up to 70 recovery throughput
• Index lookasidefor additional indexes
• In V8, for clustering index only in Insert, none
  for Delete
• In V9, possible for more indexes in both Insert
  and Delete
• More than 100 times reduction in Getpage possible
  for applicable indexes

29
Insert/Update/Delete Performance Scalability

• Randomized index key to avoid hot spots
• Can be beneficial for data sharing because of
  index page P-lock contention
• Trade-off between contention relief and
  additional Getpage, read/write I/O, and lock
  request
• Better for indexes resident in buffer pool
• ??Append option in Insert
• To reduce longer chain of spacemap page search as
  tablespace keeps getting bigger

30
Database buffer scalability/performance
enhancement

• ??Bigger prefetch and deferred write quantity for
  bigger buffer pool
• ??Max of 128 V8 -gt256KB V9 in SQL tablespace scan
• ??256 V8 -gt512KB V9 in utility
• ??36 MB/sec in non striped prefetch
• ??47 in 2-striped prefetch-gt more effective
  striping
• Bigger preformatting quantity and trigger ahead
• ??From 2 (V8) to 16 (V9) cylinders if gt16cyl
  allocation
• ?? 27 faster Insert in one measurement
• Replace all sequential prefetch, except in
  tablespace scan, with dynamic prefetch in SQL
  calls
• ??Up to 50 faster
• ??Dynamic prefetch is more intelligent and robust
  
• Always treat pages read by dynamic and list
  prefetch as sequential instead of random
• ??Can prevent randomly accessed pages from being
  wiped out by massive concurrent prefetches by
  always honoring VPSEQT (buffers that can be used
  for prefetch)

31
(No Transcript)
32
(No Transcript)
33
(No Transcript)
34
(No Transcript)
35
(No Transcript)
36
(No Transcript)
37
DB2 9 for LUW Early adopter feedback

• Financial services business results with
  XML-base SOA
• Mortgage application process that took 4 weeks
  now takes 1 minute
• In 2006 will process over 10x the amount of
  client requests as in 2005

Proto-type results using DB2 Viper hybrid data
server
38
(No Transcript)
39
(No Transcript)
40
(No Transcript)
41
(No Transcript)
42
(No Transcript)
43
(No Transcript)
44
(No Transcript)
45
Education

• DB2 Version 8 Migration Workshops
• Still available but we are mostly assisting with
  planning and issues
• DB2 Version 8 Transition class
• CG381 non lab 3 days
• CG382 lab 4 days
• DB2 V9 Migration workshops are in development

46
DB2 Redbooks - 2Q 2004 http//www.ibm.com/redboo
ks

• Disk storage access with DB2 for z/OS (REDP-4187)
• Disk storage has changed rapidly over the past
  few years with the delivery of new functions and
  improved performance. DB2 has made many changes
  to keep pace and make use of the disk
  improvements.
• Data Integrity with DB2 for z/OS (SG24-7111)
• DB2 provides functions to guarantee integrity at
  the system level and at the application level
• DB2 UDB for z/OS Design Guidelines for High
  Performance and Availability (SG24-7134)
• Discussion of the techniques and guidelines for
  database and application design. We expect the
  best practices described in this IBM Redbook will
  help DB2 professionals design high-performance
  and high-availability applications.
• DB2 UDB for z/OS Version 8 Performance Topics
  (SG24-6465)
• February Update
• DB2 MIDAW Facility Exploitation (REDP-4210)
• The MIDAW facility was introduced in the IBM z9
  processor to improve FICON performance,
  especially when accessing IBM DB2 databases. This
  facility is a new method of gathering data into
  and scattering data from discontinuous storage
  locations during an I/O operation.
• Microsoft SQL Server to IBM DB2 UDB Conversion
  Guide (SG24-6672)

47
DB2 Redbooks - 2Q 2004 http//www.ibm.com/redboo
ks

• Introduction to the New Mainframe z/OS Basics
  (SG24-6366)
• The Role of IBM System z in the design of a
  Service Oriented Architecture (REDP-4190)
• DB2 9 Pure XML (SG24-7315)
• This IBM Redbook discusses the pureXML data
  store, hybrid database design and administration.
  It describes XML schema, industry standards, and
  how to manage schemas. This IBM Redbook also
  covers SQL/XML, XQuery, and XPath using
  easy-to-understand examples. Lastly, this IBM
  Redbook shows how to use XML technology
  efficiently in business applications.
• DB2 for z/OS Data Sharing in a Nutshell
  (SG24-7322)
• This IBM Redbook is meant to facilitate your
  journey towards data sharing by providing a
  cookbook approach to the main tasks in enabling
  data sharing and workload balancing. It does not
  have all the answers, because it is a brief
  summary of a large field of knowledge, but it
  contains the key elements and it points you in
  the right direction to get more details.
  Throughout this document we assume that your
  sysplex environment is set up and a DB2 subsystem
  exists at a currently supported level.

48
Software Announcements Links

• SW Webcasts
• http//www-306.ibm.com/software/os/zseries/events/
  
• HW Announcements z9
• http//www-03.ibm.com/systems/z/feature112905/
• http//www-03.ibm.com/systems/systemz9/z9109/
• DB2 V8 Library refresh 2/2006
• http//www-306.ibm.com/software/data/db2/zos/v8boo
  ks.html

49
Reference URLs
http//www.ibm.com/software/data/db2/zos/index.htm
l ibm.com/software/db2zos
DB2 for z/OS ibm.com/software/db2zos/db2zosv8.ht
ml V8 ibm.com/software/db2zos/support.html
Support ibm.com/software/data/db2imstools/
Tools ibm.com/developerworks/db2
Applications ftp site for papers,
presentations Unicode http//www-06.ibm.com/de
veloperworks/library/whyunicode.html