DB2 9 for z/OS Planning and Experiences

1
DB2 9 for z/OS Planning and Experiences

• Jim Brogan
• IBM DB2 Advisor
• jambrog_at_us.ibm.com

2
Disclaimer and Trademarks
Information contained in this material has not
been submitted to any formal IBM review and is
distributed on "as is" basis without any warranty
either expressed or implied. Measurements data
have been obtained in laboratory environment.
Information in this presentation about IBM's
future plans reflect current thinking and is
subject to change at IBM's business discretion.
You should not rely on such information to make
business plans. The use of this information is
a customer responsibility. IBM MAY HAVE PATENTS
OR PENDING PATENT APPLICATIONS COVERING SUBJECT
MATTER IN THIS DOCUMENT. THE FURNISHING OF THIS
DOCUMENT DOES NOT IMPLY GIVING LICENSE TO THESE
PATENTS. TRADEMARKS THE FOLLOWING TERMS ARE
TRADEMARKS OR REGISTERED TRADEMARKS OF THE IBM
CORPORATION IN THE UNITED STATES AND/OR OTHER
COUNTRIES AIX, AS/400, DATABASE 2, DB2,
e-business logo, Enterprise Storage Server,
ESCON, FICON, OS/390, OS/400, ES/9000, MVS/ESA,
Netfinity, RISC, RISC SYSTEM/6000, iSeries,
pSeries, xSeries, SYSTEM/390, IBM, Lotus, NOTES,
WebSphere, z/Architecture, z/OS, zSeries, System
z, pureXML The FOLLOWING TERMS ARE TRADEMARKS OR
REGISTERED TRADEMARKS OF THE MICROSOFT
CORPORATION IN THE UNITED STATES AND/OR OTHER
COUNTRIES MICROSOFT, WINDOWS, WINDOWS NT, ODBC,
WINDOWS 95 For additional information see
ibm.com/legal/copytrade.phtml

1
1
3
V8/V9 OverviewWorldwide Experience in the Field
4
DB2 z/OS Announce / End Of Service

• No skip release
• No GA date announced for DB2 z/OS Vx (next)
• No date announced for DB2 z/OS V8 EOS
• wont occur till after Vx GAd

5
DB2 z/OS Availability Summary
June 2008
6
DB2 Connect and DB2 z/OS v9

• MINIMUM requirements for DB2 Connect to work with
  DB2 z/OS V9.
• V8 FP13, V8.2 FP 6, V9 FP1
• The more current the FIXPACs the better.
• DB2 UDB LUW V8 products are OUT OF SUPPORT
• would need to purchase extended support

7
DB2 for z/OS Adoption

• gt85 WW Customers are Current
• DB2 V8 Majority have Migrated
• 100 of Top 100 gt99 of Top 200
• V7 End of Service June 30, 2008
• V8 Withdrawal from Marketing
• Announced Dec. 2, 2008
• Effective Sept. 8, 2009
• DB2 9 Climbing Sharply
• About 1/3 of Top 200 customers
• 15 of TOTAL EAST
• 15 of TOTAL NE/UNY (vast majority of DB2 Data
  Sharing Customers)

8
Beneficial Activities

• DB2 z/OS V9 Migration Planning Workshop
• When ready to ORDER
• OPEN PMR for Upgrade/Migration
• When your ready to BEGIN
• Stay CURRENT on MAINT

9
Maintenance

• Sound maintenance strategy is essential for all
  customers
• Recommended to exploit CST/RSU process
• Apply 2 to 3 preventative service drops annually
• Exploit Enhanced HOLDDATA to be vigilant on
  HIPERs and PEs
• No one-size-fits-all strategy
• Review installation guide and the material
  supplied to ensure that RSU only service is
  installed
• Can enforce installing RSU only service by
  adding the SOURCEID (RSU) option in the supplied
  APPLY and ACCEPT jobs
• Note '' will pull ALL RSUs off of a particular
  tape

10
Important CONSIDERATIONs

• WLM Buffer Pool Management
• Maybe NOT yet
• RRF
• caution if data Compressed
• Plan Stability
• SPT01 (64GB limit)
• (8) 3390 mod 9s, (64) 3390 mod 1s
• zPARM
• BIND/Rebind
• Converged TEMP Space
• PERFORMANCE

11
DSNTIJPM(9)

• JP9 shipped with V8 APAR PK31841
• Checks for
• Check for V8 Sample Database
• V8 job DSNTEJ1
• Report of user-defined indexes on the DB2 Catalog
  that reside on user-managed storage
• On table spaces that will be converted during
  ENFM
• DSNTIJEN needs modification for their shadow
  datasets
• DB2 Managed Stored Procedures (SPAS)
• Convert to WLM established stored procedures
  before migrating
• Plans Packages bound prior to V4 that need to
  be rebound
• They will automatically rebind if ABIND YES or
  COEXIST
• If ABIND NO, a -908 will be received at execute
  time
• Incomplete table space, table, and columns
• Optimization Service Center table format changes
  required before migration.

12
Migrating to DB2 9

• Complete pre-migration checks against DB2 V8
  (DSNTIJP9)
• This will be the same as DSNTIJPM delivered with
  DB2 9
• Check / correct incompatibilities
• The BSDS needs to be expanded to V8 format
  (DSNJCNVB)
• If not done before migrating to V9, DSNTIJUZ will
  convert the BSDS(s)
• Must be on DB2 for z/OS V8 New Function Mode
• Reestablish V8 IVP to test DB2 9 before NFM
• Assess ISV Requirements
• Tools and applications
• Some vendors may add instructions for migration
  and / or require maintenance
• Assess the training requirements for your
  organization
• Establish a project team and project plan

13
Migrating to DB2 9

• Develop conversion and coexistence goals
• How did your V8 test plans work?
• Reuse and improve upon your experiences
• Establish performance baselines
• Migration occurs in three familiar phases
• Conversion Mode (CM)
• Enable New Function Mode (ENFM)
• New Function Mode (NFM)
• With more flexibility to move between modes

14
DB2 9 for z/OS Migration Modes

• Catalog Modes Illustrated
• V8 to DB2 9 NFM

DSNTIJTC
DSNTIJEN
DSNTIJNF
DB2 for z/OS V8 NFM
Install Fallback SPE cycle DB2 (all members if
data sharing) BSDS reformatted ( V8 DSNJCNVB) Run
DSNTIJP9 Resolve inconsistencies
incompatibilities
Can fallback to V8 Data sharing coexistence
support Most new function unavailable Running DB2
9 code Regression testing RUNSTATS / REBIND
Primary catalog migration phase Online REORGs of
SYSOBJ SYSPKAGE Start Load of RTS Most new
function unavailable
Most new features available REORG table spaces
containing tables with variable length columns to
use RRF
15
Some CM New Features

• Catalog Modes Illustrated
• V8 to DB2 9 NFM

DSNTIJTC
DSNTIJEN
DSNTIJNF
DB2 for z/OS V8 NFM
Install Fallback SPE cycle DB2 (all members if
data sharing) BSDS reformatted ( V8 DSNJCNVB) Run
DSNTIJP9 Resolve inconsistencies
incompatibilities
Can fallback to V8 Data sharing coexistence
support Most new function unavailable Running DB2
9 code Regression testing RUNSTAT / REBIND
Primary catalog migration phase Online REORGs of
SYSOBJ SYSPKAGE Start Load of RTS Most new
function unavailable
Most new features available REORG table spaces
containing tables with variable length columns to
use RRF

• Additional 64 bit improvements
• Rebind to gain these benefits with static Plans
  / Packages
• Asymmetric index page splits
• New access paths available with rebind
• More archive logging buffers
• DB2 9 Utilities (except online utility support
  for large format input data sets RECOVER to PIT
  with consistency)
• Data sharing improvements (except for log
  contention relief)
• Rebinding can also help to identify
  incompatibilities (like new reserved words)
• No new SQL features

16
DB2 9 for z/OS Migration Modes

• Catalog Modes Illustrated
• Convert / Revert Mode Options
• The star modes (CM, ENFM) help explain why
  some new function may appear before its expected
  time ( like a Universal Table Space in CM )

DB2 for z/OS V8 NFM
DSNTIJCS
Revert
Revert
DSNTIJCS
DSNTIJES
DSNTIJCS
17
V9 Modes An Overview
CM Compatibility Mode - This is the mode DB2 is
in when V9 is started for the first time from V8.
It will still be in CM when migration job
DSNTIJTC has completed. No new function can be
executed in CM. Data sharing systems can have V8
and V9 members in this mode. DB2 can only migrate
to CM from V8 NFM. ENFM Enabling New Function
Mode - This mode is entered when CATENFM START is
executed (the first step of job DSNTIJEN). DB2
remains in this mode until all the enabling
functions are completed. Data sharing systems can
only have V9 members in this mode. NFM New
Function Mode - This mode is entered when CATENFM
COMPLETE is executed (the only step of job
DSNTIJNF). This mode indicates that all catalog
changes are complete and new function can be
used. ENFM This is the same as ENFM but the
indicates that at one time DB2 was at NFM.
Objects that were created when the system was at
NFM can still be accessed but no new objects can
be created. When the system is in ENFM it can
not fallback to V8 or coexist with a V8
system. CM This is the same as CM but the
indicates that at one time DB2 was at a higher
level. Objects that were created at the higher
level can still be accessed. When DB2 is in CM
it can not fallback to V8 or coexist with a V8
system.
18
DB2 9 Catalog

• New Catalog Table Spaces for
• Real-Time Statistics
• New page size for SYSOBJ
• XML
• Trusted Context
• Extended Index definitions

19
Catalog Table Spaces
DB2 for z/OS V8
DB2 9 for z/OS
TABLESPACE PAGESIZE --------------------- SYS
COPY 4 SYSDBAUT 4 SYSDDF
4 SYSEBCDC 4 SYSGPAUT
4 SYSGROUP 4 SYSJAUXA
4 SYSJAUXB 4 SYSJAVA
4 SYSPKAGE 4 SYSPLAN
4 SYSRTSTS 4 SYSSEQ
4 SYSSEQ2 4 SYSUSER
4 SYSDBASE 8 SYSGRTNS
8 SYSHIST 8 SYSPLUXA
8 SYSSTR 8 SYSVIEWS
8 SYSXML 8 SYSCONTX
16 SYSOBJ 16 SYSROLES
16 SYSSTATS 16 SYSTARG
16 SYSALTER 32
TABLESPACE PAGESIZE ---------------------- SY
SCOPY 4 SYSDBAUT 4 SYSDDF
4 SYSEBCDC 4 SYSGPAUT
4 SYSGROUP 4 SYSJAUXA
4 SYSJAUXB 4 SYSJAVA
4 SYSPKAGE 4 SYSPLAN
4 SYSSEQ 4 SYSSEQ2
4 SYSUSER 4 SYSDBASE
8 SYSGRTNS 8 SYSHIST
8 SYSOBJ 8 SYSSTR
8 SYSVIEWS 8 SYSSTATS
16 SYSALTER 32
New TS for Real-Time Stats
Auxiliary Table to hold TEXT from Routines
New page size
XML Trusted Context
Extended Index Definitions
22 Tablespaces
28 Tablespaces
20
DB2 9 CPU Performance

• The target for DB2 9 CPU performance is to be
  roughly equivalent or marginally better relative
  to V8
• Mileage will vary
• Customers running DB2 9 on old hardware
  (z800/z900) will likely see CPU regression -
  maybe 10
• Data sharing customers running on DB2 9 (NFM) may
  see significant savings from reduced LC19
  contention and less spin to get unique LRSN

21
Utilities Performance Improvements

• Parallelism for REORG V9
• 10-40 elapsed time improvement
• Parallel log apply for REORG V9
• Parallelism for CHECK INDEX V9
• Up to 30 improvement in elapsed time, 5 CPU
  degradation
• Utility CPU time reduction V9
• 5-20 for RECOVER, REBUILD, REORG
• 5-30 for LOAD
• 20-60 for CHECK INDEX
• 35 for LOAD partition
• 15 for COPY
• 30-50 for RUNSTATS INDEX
• 40-50 for REORG INDEX
• Up to 70 for LOAD REPLACE of single partition

UTL
22
WLM assisted buffer pool management

• DB2 registers the BPOOL with WLM.
• DB2 provides sizing information to WLM.
• DB2 communicates to WLM each time allied agents
  encounter delays due to read I/O.
• DB2 periodically reports BPOOL size and random
  read hit ratios to WLM.
• just as though an ALTER BUFFERPOOL VPSIZE
• command had been issued.
• DB2 V9 restricts the total adjustment to /- 25
  the size of the buffer pool at DB2 startup
• if a buffer pool size is changed and later DB2 is
  shut down and subsequently brought up, the last
  used buffer pool size is remembered across DB2
  restarts !!!!!!

23
WLM assisted buffer pool management
ALTER BUFFERPOOL AUTOSIZE option
DBM1
WLM
Data Collection
DB2 Periodic Report Buffer Pool Sizes Hit Ratio
for Random Reads
BP0
BP1
1 Plots size and hit ratio over time. 2
Projects effects of changing the size
BP7
BP2
Bufferpool Adjustment - 25
24
REORDERED ROW FORMAT

• in DB2 9 new function mode (NFM)
• REORG or LOAD REPLACE, changes the row format
  from basic row format (BRF) to reordered row
  format (RRF)
• NO EDITPROC or VALIDPROC
• more efficient compression dictionary IF you
  rebuild the dictionary AFTER converting over to
  reordered row format.
• REORG and LOAD jobs with KEEPDICTIONARY specified
  
• the introduction of APAR  PK41156 that makes a
  change to REORG and LOAD REPLACE so they ignore
  KEEPDICTIONARY for that one time run when the
  rows are reordered and allows for a rebuild of
  the dictionary regardless of the KEEPDICTIONARY
  setting.
• APAR also introduces a new keyword APAR also
  introduces a new keyword HONOR_KEEPDICTIONARY and
  it defaults to NO

25
Reordered Row Format (RRF)

• Automatic repositioning of variable length
  columns to end of row
• Length attributes replaced with indicators
  positioned after fixed length columns
• Any table space created in DB2 9 NFM
• To Convert
• REORG or LOAD REPLACE a table space or partition
• ADD PARTITION
• No EDITPROCs or VALIDPROCs
• EDITPROCs may need to be updated if implemented
  for specific columns
• Byte RFMTTYPE passed to indicate fixed length,
  basic, or reordered format
• Consider this impact on tables encrypted via an
  EDITPROC
• DSN1COPY impact during the transition period
  across environments
• PIT RECOVER will set the table space to the row
  format of the PIT
• Catalog / Directory remains in Basic Row Format
  (BRF)

DSN
26
Reordered Row Format (RRF)

• Logging Considerations
• Variable length rows that DO NOT change length
• Logging is from first byte of first changed
  column to last byte of last changed column
• RRF should not negatively impact logging in this
  case
• For variable length rows changing length
  compressed rows
• Logging is from first changed byte until the end
  of the row
• One consideration may be where variable length
  columns are placed at the end of the row in V8
  AND where the length changes
• Now the logging will be from the indicators
  (offsets).

DSN
27
RRF
28
Varchar Performance Improvement

• Old tuning recommendation for rows with many
  columns with any varchar present
• V9 DB2 internally executes this recommendation
  and more
• 2 times or more improvement observed when many
  rows with many varchars are scanned and/or
  fetched using many predicates
• No difference if no varchar , Under 5
  improvement for a typical online transaction
• Reorg with rebuild compression dictionary if
  varchar columns when migrating to V9

29
Access Path Stability

• New function of DB2 9 (PK52523)
• Protects customers against access path
  regression
• Allows for a safe way to REBIND (fall back)
• Available even in DB2 9 (CM) as it can benefit
  migration and fallback
• Strongly recommended (SPT01)
• Make sure that the pre-conditioning APAR for
  Plan Stability (PK52522) is
• applied on all V8 (NFM) systems
• What is the problem?
• REBINDs can cause access path changes
• Most of the time, this improves query
  performance
• But when it doesnt
• No easy way to undo the REBIND
• Can lead to a lot of grief to our customers
  and to IBM

30
Access Path Stability

• Delivered with APAR PK52523 (V9)
• Preconditioning APAR PK52522 (V8 / V9)
• For fallback to V8 and coexistence with V8 / V9)
  V8 toleration of multiple package copies
• Also causes DB2 to delete old copies of PLANMGMT
  packages invalidated due to database changes
• REBIND PACKAGE... PLANMGMT(OFF BASIC
  EXTENDED)
• Or REBIND TRIGGER PACKAGE
• ZParm PLANMGMT is online updateable
• Options
• OFF (default)
• Do not use plan stability
• Package continues to have one active copy
• BASIC
• Package has one active copy and one old
  (previous) copy
• EXTENDED
• One active and two old / preserved package copies
• The preserved copies are the previous and
  original copies

OPT
31
Access Path Stability

• REBIND PACKAGE...SWITCH(PREVIOUS ORIGINAL)
• SWITCH(PREVIOUS) toggles previous and current
  copies
• SWITCH(ORIGINAL) previous deleted
  current-gtprevious original cloned to current
• FREE PACKAGE...PLANMGMTSCOPE(ALL INACTIVE)
• ALL is the default and frees all copies
• INACTIVE frees all old copies
• SYSPACKAGE reflects the current copy
• Other package related tables reflect dependencies
  of all packages
• DTYPE column of SYSPACKDEP overloaded to indicate
  Previous or Original
• To keep one V8 package
• REBIND with PLANMGMT(BASIC) once
• Subsequent REBINDs with PLANMGMT(OFF)
• REBIND SWITCH(PREVIOUS) can be used to use the
  original V8 package

OPT
32
Access Path Stability

• Before falling back to V8
• REBIND...SWITCH to the V8 package before fallback
• The V8 preconditioning maintenance will tolerate
  additional copies
• Restrictions
• No Native SQL Procedure support today
• No support for DBRMs bound into Plans.
• Impacts
• Requires additional SPT01 space
• Double for packages with BASIC
• Triple for those with EXTENDED
• REBIND can take 10 40 additional CPU
• Access Path Stability is not sticky. Except for
  the ZParm, it the chosen level must be specified
  on the REBIND.

OPT
33

• Optimization Service Center
• Identify Problem Query
• Tune Problem Query
• Monitor Capture Query Workload
• Tune Query Workload

34
Converged TEMP Space

• Single source for all temporary space in DB2
• Workfile (work files and Created Global Temporary
  Tables)
• Temp DB (Static Scrollable Cursors and Declared
  Global Temporary Tables)
• Merged into Workfile Database
• In CM NFM
• The workfile database is the only temporary
  database
• Supports 4K and 32K page sizes, with automatic
  selection of the appropriate page size
• Expect an increased use of the 32K temp space
• Consider sizing your 32K _at_ 50 - 100 of your 4K
  buffers
• Monitor statistics and adjust for actual usage
• Access is virtualized for small amounts of data,
  eliminating cost of work file creation (reduced
  CPU and I/O)
• At runtime, a result fitting in 1 data page does
  not create a workfile
• ORDER BY and FETCH FIRST n ROWS without index
  support
• Uses memory replacement technique if result fits
  within a 32k page
• Sort is avoided
• New ZParm for preventing workfile monopolization
  (MAXTEMPS)
• IFCID 002 343 updated to report usage and
  exceptions

VST
35
Temporary Space The DB2 V8 Picture
Installation support (DSNTIJTM) CREATE DATABASE
xxx as WORKFILE Define VSAM Dataset CREATE
TABLESPACE DSN4K01 IN xxx
No installation support CREATE DATABASE xxx as
TEMP
WORKFILE
TEMP
Declared temporary tables for SSC
Work files
Created global temporary tables
Declared global temporary tables
Only in a data sharing environment in
non-data sharing syntax is CREATE DATABASE DSNDB07
36
Temporary Space The DB2 9 Picture
Installation and migration support (REXX program
called by DSNTIJTM) CREATE DATABASE xxx as
WORKFILE DSNTWFG DB41 DB2ADM xxx 3
10 16 BP0 SYSDEFLT 1 20 16 BP32K
SYSDEFLT
Declared global temporary tables
Created global temporary tables
WORKFILE
Declared temporary tables for SSC
Work files

• Declared Global Temporary Tables and Static
  Scrollable Cursors now use the WORKFILE database
  instead of the TEMP database
• Uses DB2-managed (instead of user-managed)
  storage in SYSDEFLT storage group
• Segmented table space organisation (user-defined
  SEGSIZE or default of 16)
• 4KB and 32KB page sizes only no 8KB or 16KB

37
Planning For Converged TEMP Space

• Migration from DB2 V8
• To reclaim TEMP database storage, YOU must drop
  the TEMP database and reallocate the storage
• Recommendation Do not drop the TEMP database
  until you are sure that you will not return be
  falling back to V8, to avoid having to recreate
  it after fallback
• New installation panel for work file database
  definitions (DSNTIP9)
• In migration mode, if you specify non-zero values
• Migration job DSNTIJTM will create additional
  DB2-managed WORKFILE table spaces in the SYSDEFLT
  storage group ? new REXX program DSNTWFG
• DB2 does not take into account the existing work
  file table spaces
• Recommendation set the 'DSVCI' ZPARM to YES to
  allow DB2 to match VSAM CI size to table space
  page size
• Ensure you have 32KB WORKFILE table spaces for
  Declared Global Temporary Tables and Static
  Scrollable Cursors

38
Controlling Temporary Space Utilization

• Control of temporary space utilization at the
  agent level
• New ZPARM MAXTEMPS
• Macro DSN6SYSP, panel DSNTIP9
• If MAXTEMPS is exceeded for any given agent

SQLCODE -904, ERROR UNSUCCESSFUL EXECUTION
CAUSED BY AN UNAVAILABLE RESOURCE. REASON
00C90305, TYPE OF RESOURCE 100, AND RESOURCE NAME
'WORKFILE DATABASE' SQLSTATE 57011
39
DB2 9 for z/OS Addressing corporate data goals

• Improved IT Infrastructure for Compliance Efforts
• Trusted security context
• Database roles
• Auditing, encryption improved
• Simplify development and porting
• Many SQL improvements
• Native SQL stored procedures
• Default databases and table spaces
• Data Warehousing
• Dynamic index ANDing for star schema
• EXCEPT and INTERSECT

• Decrease Complexity and Cost
• Partition by growth
• Performance improvements
• Volume-based COPY/RECOVER
• Index compression
• Optimization Service Center
• Evolve Your Environment SOA
• Integrated pureXML
• WebSphere integration
• Continuous Availability
• Schema evolution enhancements
• Fast table replacement

40
Native SQL Procedural Language
zIIP Enabledfor DRDA

• Eliminates generated C code and compilation
• Fully integrated into the DB2 engine
• Any SQL procedure created without the FENCED or
  EXTERNAL keywords are native SQL procedures
• zIIP enabled for DRDA clients
• Extensive support for versioning
• VERSION keyword on CREATE PROCEDURE
• CURRENT ROUTINE VERSION special register
• ALTER ADD VERSION
• ALTER REPLACE VERSION
• ALTER ACTIVATE VERSION
• BIND PACKAGE with new DEPLOY keyword

SQL
41
Past Table Spaces Options

• Past table space options
• Simple
• Multi table, interleaved
• Segmented
• Multi table, no page sharing
• Good with mass deletes
• 64GB
• Partitioned
• One table per table space
• 128TB
• Doesnt have the internal space map like that of
  a segmented table space.

DSN
42
Universal Table Spaces

• Universal Table Space
• Combination of segmented with partitioning
  options
• Better space management
• Support of mass deletes / TRUNCATE
• If partitioned
• Still must be one table per table space
• Can choose Range Based partitioning (as before
  PBR)
• Can choose Partitioned By Growth (PBG)
• DROP / CREATE to migrate existing page sets
• Simple table spaces can not be created
• Default table space is now Segmented

DSN
43
Universal Table Spaces Partitioned By Growth

• Partition By Growth (PBG)
• Single-table table space, where each partition
  contains a segmented page set (allows segmented
  to increase from 64GB to 16TB or 128 TB with 32K
  pages)
• Eliminates need to define partitioning key and
  assign key ranges
• Partitions are added on demand
• A new partition is created when a given partition
  reaches DSSIZE
• See the SQL Reference for DSSIZE rules given the
  page size number of partitions
• Up to MAXPARTITIONS
• Retains benefits of Utilities and SQL parallelism
  optimizations for partitioned tables
• SEGSIZE defaults to 4 LOCKSIZE defaults to ROW

DSN
44
Universal Table Spaces

• Partition By Growth (PBG)
• CREATE TABLESPACE. MAXPARTITIONS n
• Can specify DSSIZE
• Only the first partition is created with the
  CREATE statement (if DEFINE YES)
• No USING VCAT.
• The compression dictionary is copied as new
  partitions are created.
• Also syntax to specify PGB on CREATE TABLE, when
  defaulting the DB TS.
• Considerations
• Single-table table space
• Always defines as LARGE
• Need PBR for query partition elimination
• No LOAD PART, ALTER ADD PART, or ROTATE PART
• All indexes are NPSIs

DSN
45
Universal Table Spaces

• What kind of Table Space will be created?

DSN
46
Index Changes

• INDEX on expression
• Page sizes 8K, 16K, 32K
• Improved page split
• Index compression
• Online REBUILD INDEX
• REORG without BUILD2 not just for DPSI
• Randomized index key
• Not logged index space
• XML index

47
Index Compression

• Compression of indexes for BI workloads
• Indexes are often larger than tables in BI
• Solution provides page-level compression
• Data is compressed to 4K pages on disk
• 8K, 16K or 32K pages results in 2x, 4X or 8x disk
  savings
• No compression dictionaries compression on the
  fly

48
Index Compression Differences between data and
index compression
49
Asymmetric Index Page Splits
Multiple Sequential Insert Patterns on an Index
Sequential inserts into the middle of an index
resulted in some pages with 50 free space prior
to V9
New algorithm dynamically accommodates a varying
pattern of inserts
IDX
50
Relief for Sequential Key INSERT

• New page sizes 8K, 16K, 32K for INDEX pages
• Fewer page splits for long keys
• More key values per page
• INSERT at the end of the key range used to result
  in 50 free space in each index page
• Enhanced support dynamically adapts page split
  boundary to minimize wasted space in index pages
• Index key randomization

51
Utilities Highlights

• Extensive support has been added to DB2 utilities
  for the pureXML
• CHECK, COPY, LOAD, MERGECOPY, REBUILD INDEX,
  RECOVER, REORG,
• More online utilities
• Rebuild Index SHRLEVEL CHANGE
• Reorg LOB now supports SHRLEVEL REFERENCE (space
  reclamation)
• Check data, LOB and repair locate SHRLEVEL
  CHANGE
• Check index SHRLEVEL REFERENCE supports parallel
  for gt 1 index
• Online REORG BUILD2 phase elimination
• Intra-REORG parallelism for UNLOAD, RELOAD, LOG
  phases
• Merge multiple concurrent REORGs against part of
  the same table
• If NPIs exist, multiple concurrent REORGs must be
  converted to Intra-REORG parallelism
• Otherwise the first job will run and others will
  receive DSNU180I and RC 8.
• Utility TEMPLATE switching
• MODIFY Recovery enhancements (apply PK69427 to
  avoid COPYP for some TS)
• CLONE support
• Retain keyword added to improve management of
  copies
• LAST, LOGLIMIT, GDGLIMIT

UTL
52
Utilities Highlights (cont.)

• COPY utility includes SCOPE PENDING support to
  improve usability
• The ability to recover to any point in time with
  consistency
• Uncommitted changes are backed out
• Significantly reduces (eliminates?) the need to
  run QUIESCE which can be disruptive to
  applications
• Fast log apply buffer default increased from
  100MB to 500MB for RESTORE SYSTEM
• LOGAPSTG remains _at_ 100MB
• Volume-based COPY/RECOVER
• FlashCopy technology used to capture entire
  content of disk volumes
• RECOVER modified to enable object-level recovery
  from volume FlashCopy
• Restore assumes that the object has not moved
  volumes
• Eliminates labor associated with setting up COPY
  jobs for each database / table space
• Full integration of tape into BACKUP/RESTORE
  SYSTEM utilities

UTL
53
Trusted Context Roles

• Establishes a trusted relationship between DB2
  and an external entity
• A Server or a User ID
• Once established, a provides for specialized
  privileges only available via the Trusted Context
  via a Role
• Remote IP Address, Domain Name or SERVAUTH
  security zone attributes
• Local Job or Task name attributes
• Role
• Database entity that groups privileges
• Can be assigned to a User ID
• ROLE AS OBJECT OWNER
• CREATE
• The Role will own the object
• BIND w/o OWNER
• The Role will own the Plan / Package
• Outbound Auth ID translation is not in effect for
  remote binds
• SET CURRENT SQLID will be ignored
• Trusted Context has a Default Role
• See Admin Guide, Ch.3, Implementing your
  database design

SEC
54
Database ROLEs

• ROLE is a virtual authid
• Assigned via TRUSTED CONTEXT
• Provides additional privileges only when in a
• trusted environment using existing primary
• AUTHID.
• Can optionally be the OWNER of DB2 objects

55
Trusted Security Context

• Identifies trusted DDF, RRS Attach, or DSN
  application servers
• Allows selected DB2 authids on connections
  without passwords
• ?? reduces complexity of password management
• ?? reduces need for an all-inclusive system
  authid in app servers
• ?? more visibility/auditability of which user is
  current running
• ?? enables mixed security capabilities from a
  single app server

56
Database ROLEs Examples

• Dynamic SQL access to DB2 tables using JDBC
• or CLI, but only when running on a
  specific server.
• DBA can be temporarily assigned a DBA ROLE
• for weekend production table admin work
  no
• table access at other times.
• DBA uses a ROLE for CREATE statements, so
• that the ROLE owns the objects he or she
• creates.
• Project librarian assigned a BIND ROLE only
• when running on the production code
  library
• server cant BIND from any other server.

57
DB2 9 for z/OS Innovation SQL

• Numerous new SQL capabilities
• Easier application porting
• Simplified application development

58
DB2 SQL z z/OS V7 common luw Linux, Unix
Windows V8.2
Range partitioning
z
c o m m o n
Inner and Outer Joins, Table Expressions,
Subqueries, GROUP BY, Complex Correlation, Global
Temporary Tables, CASE, 100 Built-in Functions,
Limited Fetch, Insensitive Scroll Cursors, UNION
Everywhere, MIN/MAX Single Index, Self
Referencing Updates with Subqueries, Sort
Avoidance for ORDER BY, and Row Expressions, Call
from trigger, statement isolation
Updateable UNION in Views, ORDER BY/FETCH FIRST
in subselects table expressions, GROUPING SETS,
ROLLUP, CUBE, INSTEAD OF TRIGGER, EXCEPT,
INTERSECT, 16 Built-in Functions, MERGE, Native
SQL Procedure Language, SET CURRENT ISOLATION,
BIGINT data type, file reference variables,
SELECT FROM INSERT, UPDATE, or DELETE, multi-site
join, 2M Statement Length, GROUP BY Expression,
Sequences, Scalar Fullselect, Materialized Query
Tables, Common Table Expressions, Recursive SQL,
CURRENT PACKAGE PATH, VOLATILE Tables, Star Join
Sparse Index, Qualified Column names, Multiple
DISTINCT clauses, ON COMMIT DROP, Transparent
ROWID Column, FOR READ ONLY KEEP UPDATE LOCKS,
SET CURRENT SCHEMA, Client special registers,
long SQL object names, SELECT from INSERT, MDC
l u w
59
DB2 SQL z z/OS V8 common luw Linux, Unix
Windows V8.2
Multi-row INSERT, FETCH multi-row cursor
UPDATE, Dynamic Scrollable Cursors, GET
DIAGNOSTICS, Enhanced UNICODE for SQL, join
across encoding schemes, IS NOT DISTINCT FROM,
Session variables, range partitioning
z
Inner and Outer Joins, Table Expressions,
Subqueries, GROUP BY, Complex Correlation, Global
Temporary Tables, CASE, 100 Built-in Functions
including SQL/XML, Limited Fetch, Insensitive
Scroll Cursors, UNION Everywhere, MIN/MAX Single
Index, Self Referencing Updates with Subqueries,
Sort Avoidance for ORDER BY, and Row Expressions,
2M Statement Length, GROUP BY Expression,
Sequences, Scalar Fullselect, Materialized Query
Tables, Common Table Expressions, Recursive SQL,
CURRENT PACKAGE PATH, VOLATILE Tables, Star Join
Sparse Index, Qualified Column names, Multiple
DISTINCT clauses, ON COMMIT DROP, Transparent
ROWID Column, Call from trigger, statement
isolation, FOR READ ONLY KEEP UPDATE LOCKS, SET
CURRENT SCHEMA, Client special registers, long
SQL object names, SELECT from INSERT
c o m m o n
Updateable UNION in Views, ORDER BY/FETCH FIRST
in subselects table expressions, GROUPING SETS,
ROLLUP, CUBE, INSTEAD OF TRIGGER, EXCEPT,
INTERSECT, 16 Built-in Functions, MERGE, Native
SQL Procedure Language, SET CURRENT ISOLATION,
BIGINT data type, file reference variables,
SELECT FROM UPDATE or DELETE, multi-site join,
MDC
l u w
60
DB2 SQL z z/OS V9 common luw Linux, Unix
Windows V9
Multi-row INSERT, FETCH multi-row cursor
UPDATE, Dynamic Scrollable Cursors, GET
DIAGNOSTICS, Enhanced UNICODE for SQL, join
across encoding schemes, IS NOT DISTINCT FROM,
Session variables, TRUNCATE, DECIMAL FLOAT,
VARBINARY, optimistic locking, FETCH CONTINUE,
ROLE, MERGE, SELECT from MERGE
z
c o m m o n
Inner and Outer Joins, Table Expressions,
Subqueries, GROUP BY, Complex Correlation, Global
Temporary Tables, CASE, 100 Built-in Functions
including SQL/XML, Limited Fetch, Insensitive
Scroll Cursors, UNION Everywhere, MIN/MAX Single
Index, Self Referencing Updates with Subqueries,
Sort Avoidance for ORDER BY, and Row Expressions,
2M Statement Length, GROUP BY Expression,
Sequences, Scalar Fullselect, Materialized Query
Tables, Common Table Expressions, Recursive SQL,
CURRENT PACKAGE PATH, VOLATILE Tables, Star Join
Sparse Index, Qualified Column names, Multiple
DISTINCT clauses, ON COMMIT DROP, Transparent
ROWID Column, Call from trigger, statement
isolation, FOR READ ONLY KEEP UPDATE LOCKS, SET
CURRENT SCHEMA, Client special registers, long
SQL object names, SELECT from INSERT, UPDATE or
DELETE, INSTEAD OF TRIGGER, Native SQL Procedure
Language, BIGINT, file reference variables, XML,
FETCH FIRST ORDER BY in subselect and
fullselect, caseless comparisons, INTERSECT,
EXCEPT, not logged tables, range partitioning,
compression
l u w
Updateable UNION in Views, GROUPING SETS, ROLLUP,
CUBE, 16 Built-in Functions, SET CURRENT
ISOLATION, multi-site join, MERGE, MDC, XQuery
61
SQL Productivity, DB2 family porting

• XML
• MERGE TRUNCATE
• SELECT FROM UPDATE, DELETE, MERGE
• INSTEAD OF TRIGGER
• BIGINT, VARBINARY, BINARY, DECIMAL FLOAT
• Native SQL Procedure Language
• Nested compound
• Optimistic locking

• LOB File reference variable FETCH CONTINUE
• FETCH FIRST ORDER BY in subselect and
  fullselect
• INTERSECT EXCEPT
• ROLE trusted context
• Many new built-in functions, caseless comparisons
• Index on expression
• Improved DDL consistency
• CURRENT SCHEMA

62
TRUNCATE Statement

• Allows fast delete of all rows in a given table (
  segmented, partitioned or simple)
• Very useful for nightly refresh of summary
  tables, warehouses, etc.

TRUNCATE TABLE TABLE-NAME lt DROP STORAGE
REUSE STORAGEgt lt RESTRICT WHEN DELETE
TRIGGERS IGNORE DELETE TRIGGERSgt
lt IMMEDIATEgt
63
MERGE

• Multi-row MERGE operation, using arrays
• Targets OLTP applications like SAP

MERGE INTO account AS T USING VALUES (hv_id,
hv_amt) FOR 5 ROWS AS S(id,amt) ON T.id
S.id WHEN MATCHED THEN UPDATE SET balance
T.balance S.amt WHEN NOT MATCHED THEN
INSERT (id, balance) VALUES (S.id, S.amt) NOT
ATOMIC CONTINUE ON SQLEXCEPTION
64
SQL Improvements Family Compatibility

• INSTEAD OF triggers
• SELECT FROM UPDATE
• SELECT FROM DELETE
• SELECT FROM MERGE
• BIGINT, BINARY and VARBINARY data types
• ORDER BY and FETCH FIRST in subselect

65
DDL Porting Improvements

• Automatic selection of DATABASE and TABLESPACE
  when DDL omits these keywords
• Automatic CREATE of UNIQUE index for PRIMARY KEY
• Deprecated simple table space, default to
  segmented structure, partition by growth

66
DB2 9 for z/OS Innovation Data Warehousing

• Dynamic index ANDing for star schema
• INTERSECT, EXCEPT
• Query optimization improvements
• Improved query performance
• Index compression
• Plan stability
• Optimization Service Center

67
SKIP LOCKED DATA
SQL
X
X
X
data
data
data
data
data
data
data
data

• Rows with incompatible locks by other
  transactions are skipped
• Clause available
• On SELECT INTO, PREPARE, searched UPDATE,
  searched DELETE, UNLOAD
• Effective when CS or RS is in use
• Otherwise it is ignored
• Data is locked at the row or page
• QW0018SK ROWS SKIPPED DUE TO INCOMPATIBLE LOCK
  HELD
• Reported in IFCID 018
• Logic / Scenario
• When a transaction needs to find work to do,
  regardless of order.
• Messaging applications without strict ordering
  requirements expect to be able to skip over
  records that are locked by other transactions

SQL
68
Intersect/Except/Union semantics
R1
R1
R2
R2
INTERSECT
EXCEPT (Difference)
UNION
There are some variations and restrictions
UNION ALL
69
Query Enhancements

• SQL enhancements INTERSECT, EXCEPT, cultural
  sort, caseless comparisons, FETCH FIRST in
  fullselect, OLAP specifications RANK,
  DENSE_RANK, ROW_NUMBER
• pureXML integration and text improvements
• Index improvements Index on expression, Index
  compression,
• Improved Optimization statistics Histogram
• Optimization techniques
• Cross query block optimization and REOPT(AUTO)
• Generalize sparse index in-memory data cache
  method
• Dynamic Index ANDing for Star Schema
• Analysis instrumentation Optimization Service
  Center

70
CREATE TABLE APPEND(YES)

• New APPEND option
• Maximizes performance for INSERT at end
• Avoids overhead of attempting to preserve
  clustering sequence
• CREATE or ALTER table

71
LOB Function

• SQL
• File reference variables
• FETCH CONTINUE
• Automatic object creation
• Utilities
• REORG reclaim fragmented space and improve access
  performance
• REORG share level reference (read only)
• Online CHECK LOB CHECK DATA
• Sample unload DSNTIAUL

72
LOB Performance/Scalability

• LOB lock avoidance LRSN and page latching is
  used instead for consistency checks
• New network flows for delivering LOBs
• JDBC, SQLJ, and CLI will let server determine
  whether to flow LOB values or LOCATORs based on
  size thresholds
• Significant reduction in network traffic
• Greatly reduces frequency of FREE LOCATOR
  statements

73
DDF Improvements

• 64-bit addressing by DDF
• Special shared private with xxxDBM1 to
  eliminate many data moves on SQL operations
• Prepare for elimination of PRIVATE protocol
  requester
• DB2 9 did not eliminate DDF Private Protocol
• Plan is to eliminate in DB2 91 release
• If do not convert from Private to DRDA protocol,
  will not be able to migrate to DB2 91 release
• DSNTP2DP (Private to DRDA Protocol Catalog
  Analysis Tool) REXX program which looks at the
  DB2 Catalog

74
Volume-based COPY/RECOVER

• FlashCopy technology used to capture entire
  content of disk volumes
• RECOVER modified to enable object-level recovery
  from volume FlashCopy
• Restore assumes that the object has not moved
  volumes
• Eliminates labor associated with setting up COPY
  jobs for each database / table space
• Full integration of tape into BACKUP/RESTORE
  SYSTEM utilities

75
DB2 9 Vstor Constraint Relief

• DDF address space runs in 64-bit addressing mode
• Shared 64-bit memory object avoids xmem moves
  between DBM1 and DDF and improves performance
• Constraint relief
• DBM1, the following are moved above the bar in V9
• Parse trees
• EDM fixed pools
• SKPTs / SKCTs (primarily static SQL). Also part
  of CTs/PTs
• Pageset blocks, RTS blocks
• Local SQL statement cache
• Some thread-related storage
• For installations that are constrained on DBM1
  vstor
• 200 to 300MB or more of savings expected
• Mainly from EDM related storage (static SQL) and
  dynamic statement cache (dynamic SQL)

76
DBM1 Virtual Storage below 2GB
77
DB2 9 for z/OS Innovation SOA and XML

• Integration with WebSphere
• Native XML data type, hybriddata base server

78
Optimistic Locking Support

• Built-in timestamp for each row or page
• Automatically updated by DB2
• Allows simple timestamp predicate to validate
  that row has not changed since last access
• Eliminates need for complex predicates on
  WebSphere CMP updates, improves performance

79
Contrasting the ModelsXML and Relational

• Relational
• Strength Static data
• Strict schema ensures data integrity
• High performance indexing on fixed data
• Strength Set-based data
• Multiple results returned
• Retrieving rows

• XML
• Strength Semi-structured, frequently changing
  data
• Self-describing, flexible schema
• Easily modified format
• Strength Retrieving sequences
• Documents, subdocuments, related documents

80
XML Data Needs Relational MaturityComplementing
XML Processing

• XML Data Needs Protection
• Backup and recovery features to ensure continuity
• Data is protected using database security
• Simplified XML Data Access
• Centrally store and access difficult to retrieve
  data
• SQL or XPath can be used to retrieve data
• Join XML data with its related relational data
• Search Speed
• Search documents quickly and efficiently using
  proven search optimization engine of mature
  database
• Optimize Existing Investments
• Use existing technology infrastructure and skills
  to store and manage both relational and XML

81
pureXML

• Support XML data type
• Store the XML document natively
• DDL --
• CREATE/ALTER Table with XML type column
• Implicitly create XML Auxiliary objects
  (tablespace/table/index) - one per XML column
• Index support
• Created by users
• uses XPath to determine which nodes in the XML
  document to index.
• CREATE INDEX dependentName ON
  deptTable(deptDocs)
• GENERATE KEY USING XMLPATTERN
  '/department/empl/dependent/name' ATOMIC AS
  SQL VARCHAR(20)
• INSERT/UPDATE/DELETE
• INSERT with VALUES and SUBSELECT
• No Subdocument update

82
pureXML -- Query

• Enhanced V8 XML Constructors (XML Publishing
  Functions)
• SQL/XML Functions and Predicates
• XMLParse - Convert a XML text to XML value
• XMLSerialize - Converts XML to character type
• XMLQuery - executes an XPath expression against
  an XML value.
• SELECT XMLQUERY ( '//itemUSPrice price '
  PASSING PO.POrder,
• T.price AS price) FROM PurchaseOrders PO, T
• XMLCast - Cast XML to other types or other types
  to XML
• XMLExists - a predicate, which returns TRUE if
  the XPath expression evaluates to a non-empty
  sequence
• SELECT PO.pid FROM PurchaseOrders PO, T
• WHERE XMLEXISTS( '//itemUSPrice
  price ' PASSING PO.POrder, T.price AS
  price)

83
pureXML

• XPATH supported features from XPath 2.0
• Utility Support
• LOAD/UNLOAD, CHECK DATA/INDEX, COPY, REBUILD,
  RECOVER, REORG, etc.
• XML Schema Support
• XSR XML Schema Repository
• Tables to store XML schemas
• Stored procedures to register XML schemas
• DSN_XMLVALIDATE() SQL/XML function
• Test XML values for validity against XML schema
• Obtain default values and schema normalized
  values from XML schema
• XML decomposition using annotated XML schema

84
API Support

• XML type is supported in
• Java (JDBC, SQLJ), ODBC,
• C/C, COBOL, PL/I, Assembly
• .NET
• Applications use
• XML as CLOB(n)
• XML as DBCLOB(n)
• XML as BLOB(n)
• All character or binary string types are
  supported
• XMLParse and XMLSerialize apply (implicitly or
  explicitly)

85
When To Use XML?

• Sparsely populated data
• Frequent DDL changes
• Short term complex data
• Complex snapshot data
• Relatively static data that is not frequently
  updated
• Data which is not frequently referenced on WHERE
  predicates and not frequently updated

Tedious normalization and frustrated changes of
schema are an indicator for using native XML.
86
Example1 Auto Insurance Policy Variations

• Each vehicle has many different features, and
  insured may choose different policy variations
• New features may come up each model year, and new
  policy variations can come up too.
• Its hard to design a set of columns to cover all
  possible features and variations
• Some of the features and variations need to be
  searched upon
• Solution use XML column

87
Example2 Customer Statements

• Customer statements get generated in XML format
• XML file is used to print/mail to customer
• XML documents are tagged with keywords
• XML document can be stored natively in DB2
• Keywords are searched to respond to customer
  inquiries
• Able to easily recreate the original document
  sent to customers (no transformations needed)
• Instead of side table and CLOB, use XML and
  indexing on the tagged keywords
• Benefit flexible, high performance

88
DB2 9 Summary of pureXML Support

• XML as a native data type
• Pure XML storage and indexing
• SQL/XML and XPath support
• Integration with traditional relational data
• XML Schema Repository
• Schema validation
• Application Support (Java, C/C, .NET, PHP,
  COBOL, PL/1 etc.)
• Visual Tooling, Control Center Enhancements
• DB2 Utilities Load, Unload, Reorg, etc.
• and more

DB2
9
Secure and Resilient Infrastructure for a New
Breed of Agile Applications
89
DB2 9 for z/OS Innovation Continuous Availability

• Online schema evolution
• More online utilities
• Data sharing enhancements

90
Schema Evolution Database Definition On Demand

• Fast replacement of one table with another
• Rename column and index
• Rename SCHEMA and VCAT
• Table space that can add partitions, for growth
• Improve ability to rebuild an index online
• Online reorganization with no BUILD2 phase
• Modify early code without requiring an IPL
• Alter table space and index logging
• Create alter STOGROUP SMS constructs

91
CLONE Tables

• Allows fast replacing production data without
  renames and rebinds
• A capability to support online load replace
• ALTER TABLE to create a Clone Table
• All indexes are also cloned
• Table and Index data are not copied
• Base and Clone tables share the same table space
  and index names
• Underlying data sets are differentiated by a data
  set instance number

92
CLONE Tables

• A clone table can only be created
• On a single table in a table space (partitioned
  or non-partitioned)
• No RI or Trigger on the base table
• Use insert or load to populate clone tables
• Utilities (except RUNSTATS) can operate on clone
  tables with a new CLONE keyword

93
Partition by Growth

• New partitioning scheme
• Single table tablespace, where each partition
  contains a segmented pageset (allows segmented to
  increase from 64GB to 16TB or 128 TB with 32K
  pages)
• Eliminates need to define partitioning key and
  assign key ranges
• A new partition is created when a given partition
  reaches DSSIZE (defaults to 64G)
• Retains benefits of Utilities and SQL parallelism
  optimizations for partitioned tables

94
DB2 9 Utilities

• Support for all new functions in DB2 Version 9
  for z/OS product (universal table spaces, XML,
  not logged, etc.)
• More online utilities
• Rebuild Index SHRLEVEL CHANGE
• Great for building new non-unique indexes
• Reorg enhancements
• Reorg LOB now supports SHRLEVEL REFERENCE
• LOB space reclamation
• Partition-level capabilities (not available with
  REBALANCE)
• Partition parallelism (UNLOAD/RELOAD) in a single
  utility statement
• Elimination of the BUILD2 phase outage
• Recover to consistent PIT without need for a
  quiesce

95
DB2 9 Utilities

• More online utilities
• Check data, LOB and repair locate SHRLEVEL
  CHANGE
• Check index SHRLEVEL REFERENCE supports parallel
  for gt 1 index
• Load replace (shrlevel change) with CLONE TABLE
  function
• Always perform CHECKPAGE on the COPY utility
• Prior to V9, CHECKPAGE was optional, with about
  5 CPU overhead, and if a broken page was
  encountered (DSNU441I for space maps or DSNU518I
  for others, both RC8), then copy-pending was set
• Now, COPY always performs these checks (with
  reduced overall CPU!) and no longer sets
  copy-pending, so. Check those RCs!
• A new SYSCOPY record type is written if a broken
  page is detected to force a full image next since
  dirty bits may have already been flipped off in
  the space map pages