Jan Vandensande

1
DB2 UDB for z/OS
Version 8
Performance expectations
Jan Vandensande janv_at_jeeves.be
2

• New version migration
• Typical lt5 cpu increase

• DB2 V8 cpu up to 10
• Most customers 5 - 10

• What do you get for it ?

3
DB2 V8 at a glance

• 64 bit - VSCR
• Unicode
• Online schema evolution
• More online zparms
• Index enhancements
• Data encryption
• Row level security
• Multi-row select, insert
• Dynamic scrollable cursors
• MQT

• Backup and restore system
• Automatic space management
• 2 MB SQL statements
• CI size gt 4KB
• 4096 partitions
• Migration changes
• Utility enhancements
• SQL enhancements
• and more

4

• Major performance impact items
• DB2 Catalog
• CPU growth
• I/O time
• Storage
• Compatibility mode
• New function mode

5

• DB2 Catalog
• Support for long names
• Easier to port applications from other platforms
• Conversion to Unicode
• UTF-8 and UTF-16 used by DB2
• Impact on space and order by
• 0 - 10 space growth
• Reorg catalog tablespaces before migration and
  increase allocation
• Drop user defined indexes and recreate as not
  padded
• Run modify utility
• Dont forget the catalog consistency queries

6

• CPU
• New version migration
• Typical lt5 cpu increase
• DB2 V8 cpu 0 - 10
• 64-bit mode instructions
• Longer and variable length names
• Unicode
• Longer and more complex SQL

7

• CPU unicode conversion
• Conversion of SQL statements and derived metadata
  to Unicode
• Minor Conversion
• Fast
• Conversion between EBCDIC SBCS and UTF-8
• Major Conversion
• Slower
• Call to Unicode Conversion Services for z/OS
• Used if minor conversion can not be done
• Always used for ASCII/EBCDIC-gtUTF-16

8

• CPU conversion performance

9

• CPU conversion performance

10

• CPU conversion performance

11

• Less CPU regression if under DB2 V7
• Hiperpools and bpools in data spaces
• Simplified buffer pool management above the bar
• SMF 89 record collection
• V7 generates these records after every SQL
  statement while V8 generates these records on
  each commit. In some environments, this has the
  potential of saving up to 15 in CPU consumed by
  DB2 V8
• Heavily use of DSNTIAUL and DSNTEP4
• exploit multi-row operation. This is applicable
  to NFM only.
• DRDA workload
• automatically exploits multi-row operation. This
  is applicable to CM as well as NFM.
• I/O intensive workload
• I/O intensive workloads tend to benefit from long
  term page fix option and 180 CI limit removal.
  Sequential processing with larger CIs can also
  benefit.
• IRLM PCYES
• Default in V8
• Lock avoidance enhancements

12

• Tuning for CPU in V8
• Rebind all packages/plans
• Long term page fix buffer pools
• Up to 8 CPU savings
• Only if enough real storage to back bpools for
  100
• Multi-row FETCH and INSERT
• Requires change to local application
• Review DSNZPARMS as EDMBFIT, CONTSTOR, MINSTOR
• Used to manage virtual storage
• Increase size of buffer pools

13

• Tuning for CPU in V8
• Long term page fix
• DB2 V8 introduces a new ALTER BUFFERPOOL
  parameter called PGFIX. This parameter allows you
  to fix the buffer pages once in memory and keep
  them fixed in real storage. This avoids the
  processing time that DB2 needs to fix and free
  pages each time there is an I/O.

14

• Tuning for CPU in V8 multi row fetch

15

• Tuning for CPU in V8 multi row fetch

16
Accounting class 2 CPU time increase
Do not just take the accounting class 2 CPU times
to compare V7 to V8 performance. You must also
factor in any CPU time reduction in the DB2
address spaces from statistics reports.
17
Accounting class 2 CPU time increase but

• Less DBM1 time from
• Long term page fix
• 180 CI limit removal
• z/OS 1.4 CF batching
• Larger CI size
• Less MSTR time from
• Default immediate write change from NO(Phase2)
• to Phase1(MSTR srb shifted to acctg TCB time)
• Less IRLM time from
• Reduced global and false contention for
• pageset/partition locks when resource release
• commit (Protocol level 2)

18

• I/O time
• No change in I/O times for 4 KB pages.
• Significant sequential I/O time improvement for
  table spaces with gt 4 KB page sizes in NFM new
  CI sizes
• Limit of 180 CIs per I/O for list prefetch and
  castout I/O requests removed.
• Compression, with the dictionaries now above the
  bar, is even more widely applicable, and can help
  in reducing I/O.

only good news !
19
I/O time larger CI
45 throughput non-EF 70 throughput EF
20

• Virtual Storage
• 64 bit virtual storage
• Several critical storage pools above 2GB bar
• Larger pools may result in better performance
• Thread related storage stays below the bar
• Thread footprint increase 30 - 70
• Only IRLM and DBM1 address spaces exploit 64-bit
  addressing

Eliminate virtual storage constraint problems !
21
DBM1 virtual storage No data spaces No hiperpools
22

• Virtual Storage
• DB2 V8 requires more virtual storage to
  accommodate
• Bigger modules, control blocks, and working
  storage
• Higher default and maximum buffer pool size, RID
  pool size, sort pool size, EDM pool size
• More and larger user threads
• More deferred write engines and castout engines
  (300 -gt 600 maximum)
• More parallelism enabled
• Locks moved above the 2 GB bar

23

• Real storage
• Increased demand for virtual storage increases
  the demand for real storage
• 1-20 more real storage needed
• Ensure the buffer pools are backed up by
  sufficient real storage
• Monitor paging and UIC

24

• Compatibility mode
• No new function available that prevents fallback
  to V7
• 4 CPU increase seen for IRWW workload
• REBIND re-enable fast path for column
  processing

Customers typically stay in CM mode for a period
of several weeks to a few months !
25

• Compatibility mode
• 64-bit addressing for DBM1 and IRLM address
  spaces
• Increased number of deferred write, castout and
  global buffer pool write engines
• EDM pool changes
• Fast log apply default
• Multi-row operations with DRDA
• IRLM locks in private storage
• Unicode parser
• Most of the optimizer enhancements

26

• Compatibility mode
• Lock avoidance in singleton SELECT with CURRENT
  DATA YES
• 180 CI limit removal for list prefetch and
  castout
• Ability to page fix buffer pools for long term
• SMF 89 improvement
• Data sharing IMMEDIATE WRITE default change
• All the enhancements provided by utilities,
  except for BACKUP and RECOVER SYSTEM

27

• New Function mode
• Enable new function mode
• Catalog conversion
• Long name support
• Unicode
• Not padded indexes
• Larger page size
• No new functions available
• No fallback to V7 or V8 CM
• ENFM is a group wide event
• No rebind necessary if done in CM
• Bind for new V8 functions

28

• Migration V7 to V8 CM

29

• Migration V8 CM to V8 NFM

30

• IRLM
• V2.2 required for DB2 V8
• Also shipped with IMS V9
• 64 bit, locks above the bar
• Average locksize up to 540 bytes
• More locks - max 100.000.000 locks
• NUMLKTS, NUMLKUS
• PCYES is default
• Locks in private storage
• ECSA freed up in V8
• IRLM request CPU increase up to 10
• Locking takes 2of DB2 cpu time
• 0.2 overall increase is probably not noticeable.

31

• Data Sharing
• Protocol level 2
• Prereq z/OS 1.4 and CFCC Level 12
• Protocol Level 2 remaps parent IX L-locks from
  XES-X to XES-S locks
• Avoid the cost of global contention processing
  whenever possible
• CF batching
• z/OS 1.4 and CFCC Level 12
• Allow multiple pages to be registered/written to
  the CF with a single operation
• Read multiple pages from the GBP for castout

32

• Major availability enhancements
• System level point in time recovery
• Automated space management
• Online schema
• Index changes

33

• System level point in time recovery
• Prior to DB2 V8 you could make an external copy
  of your production system by delimiting the back
  up activity with the -SET LOG SUSPEND and -SET
  LOG RESUME commands.
• DB2s update activity and logging, while you were
  making the external copy of your DB2 system, was
  suspended

34

• System level point in time recovery
• DB2 V8 introduces the new BACKUP SYSTEM and
  RESTORE SYSTEM utilities.
• This provides an easier and less disruptive way
  to make fast volume-level backups of an entire
  DB2 subsystem or data sharing group with minimal
  disruption
• Recover a subsystem or data sharing group to any
  point-in-time, regardless of whether or not you
  have uncommitted units of work.
• At DB2 restart, the uncommitted URs are resolved
  during the forward and backward recovery phase.

35

• System level point in time recovery
• Prerequisites
• z/OS V1R5 or above
• Disk control units that support ESS FlashCopy
  (FlashCopy V2 is recommended)
• SMS copy pools that are defined by using the DB2
  naming convention
• Defined SMS copy pool backup storage groups
• Concurrency and compatibility
• BACKUP SYSTEM can run concurrently with any other
  utility
• Must wait for the following DB2 events to
  complete before the copy can begin
• Extending data sets
• Writing 32 KB pages if CISZE different from page
  size
• Writing close page set control log records
• Creating/deleting/renaming data sets

36
BACKUP SYSTEM Util. Measurements

• System level point in time recovery

37
BACKUP SYSTEM Util. Measurements

• System level point in time recovery

38

• Automated space management
• DB2 will adjust the size of the secondary extent,
  when extending the data set in such a way that
  the maximum allowable data set size is reached,
  before the maximum number of extents for that
  data set is reached, therefore avoiding out of
  space conditions.
• Today space allocation supports a maximum number
  of 255 extents per component of a VSAM data set
  for multivolume data sets and a maximum of 123
  extents for a single volume allocation.

39

• Automated space management
• New DSNZPARM MGEXTSZ enables automated space
  management
• Default PRQTY is 720KB even if TSQTY and IXQTY
  are not specified (0). 10 x more for LOB
  tablespaces
• Increasing SECQTY up to 127 extentsand a constant
  number of 559 cylinders for 32 GB and 64 GB data
  sets thereafter.
• Increasing SECQTY to 127 extents constant number
  of 127 cylinders for data sets less than 1 GB or
  1, 2, 4, 8 and 16 GB thereafter
• SECQTY, if specified, used if gt calculated

40

• Automated space management
• I/Os never span extents prefetch, deferred
  write, mass insert, utility processing will
  benefit from larger extents
• Do extents still matter ?
• Any penalty with having multiple extents goes
  down as the extent size increases. The size of
  the extents is actually more important than the
  actual number of extents. Above an extent size of
  10 cylinders independent of the number of extents
  there is no noticeable difference in performance.

41

• Automated space management

42

• Online Schema evolution
• Unload the data, extract the DDL and
  authorizations
• Drop the object.
• Create the object with the new definition and
  reestablish authorizations for the object.
• Recreate all dependent objects, such as views and
  indexes, and so forth
• Reload the data.
• Rebind plans and packages.

DB2 V8 allows many more schema changes without
having to drop and recreate the objects.
43

• Online Schema evolution
• Alter column data type
• Increase column size within a data type
• Change the data type
• Alter index
• Add a column to the end of an index
• Alter an index VARCHAR columns to be non padded
• Dynamic partition management
• Add, rotate, and rebalance partitions

44

• Online Schema evolution Alter table
• Information about new table layout is stored in
• Catalog and directory
• New system pages inside the page set
• Table space is placed in AREO (advisory
  REORG-pending)
• Accessible but some performance degradation until
  REORG
• Plans, packages and cached dynamic statements
  referring to the changed column are invalidated
• Runstats values for columns are invalidated

45

• Online Schema evolution Alter table
• Existing data remains unchanged
• On SELECT, data will be materialized to the
  latest format
• On INSERT / UPDATE, the entire row will be
  changed to latest format
• Reorg changes all rows to the latest format

46

• Online Schema evolution Alter table

Fetch performance Degradation of 21 to 23 is
measured after ALTER and before REORG After
reorganization the performance overhead remains
below 5.
47

• XML Publishing functions
• Up to DB2 V7, you could use the XML extender
  feature for manipulating XML data into and out of
  DB2.
• set of external programs which are defined to
  execute as stored procedures and user-defined
  functions
• DB2 V8 now provides seven new built-in XML
  publishing functions to help with XML document
  composition from DB2 data.

48
Composition - Extender and Publishing
30
25
20
15
10
5
0
1M doc
2M doc
3M doc
4M doc
5.8M
doc
COMP Elapsed
SQL/XML Elapsed

• The XML publishing functions outperform the XML
  composition by a factor 20 and more due to the
  fact that the XML publishing functions are built
  into the DB2 engine.

49

• References
• IBM Redbooks
• SG24-6079
• DB2 UDB for z/OS Version 8Everything You Ever
  Wanted thing to Know, ... and More
• SG24-6465
• DB2 UDB for z/OS Version 8 Performance Topics

50

Thanks for your attention Questions ?
51

• Migration process no skip release
  migration
• Version 8 compatibility mode (CM)
• This is only a transitional phase where no new
  Version 8 external functions are enabled
  Compatibility mode allows
  you to test and ensure that the new Version 8
  code is functioning correctly and your existing
  applications are working correctly under the new
  code.
• Enabling-new-function mode (ENFM)
• This is the phase where the catalog and directory
  are converted to a Version 8 new-function mode
  catalog. Once again, most of the new V8 functions
  are not available.
• New-function mode (NFM)
• DB2 enters new-function mode after all the
  migration tasks are complete. All the new
  function in DB2 Version 8 is now available.

52

• CPU Range by Workload

53

• Index enhancements
• Indexes on table-controlled partitioned tables
  can be classified as follows
• Partitioning index The columns in the index are
  the same as (and have the same collating
  sequence), or start with the columns in the
  PARTITION BY clause of the CREATE TABLE
  statement.
• Secondary index Any index in which the columns
  do not coincide with the partitioning columns of
  the table.
• Data-partitioned secondary indexes Any index
  defined with the PARTITIONED option.

54

• Index enhancements not padded