DB2 11 for z/OS Technical Overview

DB2 11 for z/OS Technical Overview

Released December 2013
Publisher(s): IBM Redbooks
ISBN: 9780738439051

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

IBM® DB2® Version 11.1 for z/OS® (DB2 11 for z/OS or just DB2 11 throughout this book) is the fifteenth release of DB2 for IBM MVS™. It brings performance and synergy with the IBM System z® hardware and opportunities to drive business value in the following areas.

DB2 11 can provide unmatched reliability, availability, and scalability
- Improved data sharing performance and efficiency
- Less downtime by removing growth limitations
- Simplified management, improved autonomics, and reduced planned outages
DB2 11 can save money and save time
- Aggressive CPU reduction goals
- Additional utilities performance and CPU improvements
- Save time and resources with new autonomic and application development capabilities
DB2 11 provides simpler, faster migration
- SQL compatibility, divorce system migration from application migration
- Access path stability improvements
- Better application performance with SQL and XML enhancements
DB2 11 includes enhanced business analytics]
- Faster, more efficient performance for query workloads
- Accelerator enhancements
- More efficient inline database scoring enables predictive analytics

The DB2 11 environment is available either for new installations of DB2 or for migrations from DB2 10 for z/OS subsystems only.
This IBM Redbooks® publication introduces the enhancements made available with DB2 11 for z/OS. The contents help database administrators to understand the new functions and performance enhancements, to plan for ways to use the key new capabilities, and to justify the investment in installing or migrating to DB2 11.

Table of contents

  1. Front cover
  2. Figures
  3. Tables
  4. Examples
  5. Notices
    1. Trademarks
  6. Preface
    1. Authors
    2. Now you can become a published author, too!
    3. Comments welcome
    4. Stay connected to IBM Redbooks publications
  7. Chapter 1. DB2 11 for z/OS at a glance
    1. 1.1 Subsystem
    2. 1.2 Application functions
    3. 1.3 Operations and performance
  8. Part 1 Subsystem
  9. Chapter 2. Synergy with System z
    1. 2.1 Synergy with IBM zEnterprise System
      1. 2.1.1 Faster CPU speed
      2. 2.1.2 More system capacity
      3. 2.1.3 zEC12 hardware features
    2. 2.2 Synergy with IBM System z and z/OS
      1. 2.2.1 AUTOSIZE options VPSIZEMIN and VPSIZEMAX
      2. 2.2.2 1 MB page frames for DB2 execution code
      3. 2.2.3 Improved performance of batch updates in data sharing
      4. 2.2.4 Improved usability and consistency for security administration
      5. 2.2.5 Log writing
    3. 2.3 Using zIIP speciality processors
    4. 2.4 Reduced need for REORG
    5. 2.5 DFSMS storage tiers
      1. 2.5.1 Use cases for storage tiers
      2. 2.5.2 Setup and invocation of storage tiers
      3. 2.5.3 Use cases for DB2
    6. 2.6 Additional System z enhancements
      1. 2.6.1 Enhancing DB2 BACKUP SYSTEM solution
      2. 2.6.2 z/OS DFSMS VSAM RLS for z/OS catalog support
      3. 2.6.3 DDF Synchronous Receive support
      4. 2.6.4 zEnterprise Data Compression
  10. Chapter 3. Scalability
    1. 3.1 Extended RBA and LRSN
      1. 3.1.1 Reaching the end of the basic RBA
      2. 3.1.2 The new 10 byte RBA and LRSN
      3. 3.1.3 Considerations before converting to extended format
      4. 3.1.4 Steps for enabling the extended RBA/LRSN format
      5. 3.1.5 Converting the BSDS
      6. 3.1.6 Converting DB2 catalog and directory
      7. 3.1.7 Converting data from 6 byte to 10 byte RBA/LRSN or vice versa
      8. 3.1.8 Additional considerations regarding utilities
    2. 3.2 NOT LOGGED for declared global temporary tables
      1. 3.2.1 Syntax extension
      2. 3.2.2 Undo processing for NOT LOGGED DGTTs
      3. 3.2.3 Thread reuse
      4. 3.2.4 Sample scenarios
    3. 3.3 More open data sets (DSMAX)
    4. 3.4 PBG mapping tables to lift the 64 GB limit
      1. 3.4.1 Autonomic creation of the mapping table
      2. 3.4.2 Mapping tables up to 16 TB
  11. Chapter 4. Availability
    1. 4.1 Online schema changes and enhanced recovery options
      1. 4.1.1 Scope of enhancements for online schema changes in DB2 11
      2. 4.1.2 How it works
      3. 4.1.3 Effect of MODIFY RECOVERY
      4. 4.1.4 Considerations for LOBs
      5. 4.1.5 Restrictions for the window between PIT recovery and REORG
      6. 4.1.6 More restrictions for PIT recovery after materializing REORG
      7. 4.1.7 Determine if a table space is eligible for PIT recovery prior to REORG
    2. 4.2 Automatic recovery of indexes from GRECP or LPL status
      1. 4.2.1 RESTORE SYSTEM after two-pass LPL/GRECP recovery has occurred
      2. 4.2.2 RECOVER INDEX after two-pass LPL/GRECP recovery has occurred
    3. 4.3 Improved availability when altering limit keys
      1. 4.3.1 Considerations for tables containing LOBs
      2. 4.3.2 LOAD REPLACE
      3. 4.3.3 RECOVER
    4. 4.4 Work file database enhancements
      1. 4.4.1 WFSTGUSE_AGENT_THRESHOLD subsystem parameter
      2. 4.4.2 WFSTGUSE_SYSTEM_THRESHOLD subsystem parameter
      3. 4.4.3 Systems programmer response to DSNI052I/DSNI053I
    5. 4.5 Governing of parallel processing of utilities
    6. 4.6 Compression dictionary availability for CDC tables
    7. 4.7 DROP column support
      1. 4.7.1 Changes to the catalog as a result of dropping a column
      2. 4.7.2 Undo a DROP COLUMN
      3. 4.7.3 Impact of DROP COLUMN on utilities
      4. 4.7.4 Impact of DROP COLUMN on applications
      5. 4.7.5 Restrictions for DROP COLUMN
    8. 4.8 Defer define object enhancements
    9. 4.9 Allow BIND, REBIND, and DDL to break-in persistent threads
    10. 4.10 Idle thread break-in
      1. 4.10.1 Improvements for DDF threads
      2. 4.10.2 Improvements for non-DDF threads
  12. Chapter 5. Data sharing
    1. 5.1 Group buffer pool write-around protocol
    2. 5.2 Improved castout processing
      1. 5.2.1 Reduced wait time for I/O completion
      2. 5.2.2 Reduced notify message size sent to castout owners
      3. 5.2.3 More granular class castout threshold
    3. 5.3 Improved DELETE_NAME performance
    4. 5.4 Restart light with CASTOUT option
    5. 5.5 Locking enhancements
      1. 5.5.1 Conditional propagation of child Update locks to the coupling facility
      2. 5.5.2 Improved performance in handling lock waiters
      3. 5.5.3 Increase in maximum number of CF lock table entries
      4. 5.5.4 Throttle batched unlock requests
      5. 5.5.5 Improved IRLM resource hash table algorithm
    6. 5.6 Index availability and performance
      1. 5.6.1 Avoid placing indexes in RBDP state during group restart
      2. 5.6.2 Reduce synchronous log writes during index structure modifications
    7. 5.7 Group buffer pool write performance
    8. 5.8 Automatic LPL recovery at end of restart
    9. 5.9 Log record sequence number spin avoidance
  13. Part 2 Application functions
  14. Chapter 6. SQL
    1. 6.1 Introduction
    2. 6.2 Global variables
      1. 6.2.1 DDL and catalog information
      2. 6.2.2 Qualifying global variables
      3. 6.2.3 Global variable’s scope
      4. 6.2.4 Global variable’s naming resolution
    3. 6.3 Array data type
      1. 6.3.1 Ordinary arrays
      2. 6.3.2 Associative arrays
      3. 6.3.3 ARRAY_EXISTS predicate
    4. 6.4 Aliases and public aliases for SEQUENCES
      1. 6.4.1 Private ALIAS for a SEQUENCE
      2. 6.4.2 Public ALIAS for a SEQUENCE
      3. 6.4.3 Dropping an alias for sequence
      4. 6.4.4 Security considerations
      5. 6.4.5 Considerations regarding application compatibility setting
    5. 6.5 New built-in functions
      1. 6.5.1 ARRAY_AGG
      2. 6.5.2 ARRAY_DELETE
      3. 6.5.3 ARRAY_FIRST
      4. 6.5.4 ARRAY_LAST
      5. 6.5.5 ARRAY_NEXT
      6. 6.5.6 ARRAY_PRIOR
      7. 6.5.7 CARDINALITY
      8. 6.5.8 MAX_CARDINALITY
      9. 6.5.9 TRIM_ARRAY
      10. 6.5.10 UNNEST (table function)
      11. 6.5.11 Arrays in MERGE statement
    6. 6.6 SET CURRENT APPLICATION COMPATIBILITY
    7. 6.7 Temporal special registers
      1. 6.7.1 Scope of session-level special registers
      2. 6.7.2 SYSTIMESENSITIVE and BUSTIMESENSITIVE
    8. 6.8 Temporal support on VIEWs
    9. 6.9 DGTT
    10. 6.10 CUBE, ROLLUP and GROUPING SETS
      1. 6.10.1 GROUPING SETS
      2. 6.10.2 ROLLUP
      3. 6.10.3 CUBE
      4. 6.10.4 Grand total
      5. 6.10.5 Grouping expression
    11. 6.11 ALTER TABLE DROP COLUMN
    12. 6.12 LIKE_BLANK_INSIGNIFICANT DSNZPARM
  15. Chapter 7. Application enablement
    1. 7.1 Ensuring application compatibility
    2. 7.2 Transparent archiving of temporal data
      1. 7.2.1 Controls of archive transparency
      2. 7.2.2 Sample code for enabling archive transparency
      3. 7.2.3 Inserting rows into archive enabled table
      4. 7.2.4 Deleting rows from an archive enabled table
      5. 7.2.5 Querying archive enabled table
      6. 7.2.6 Using a dynamic transaction with archive transparency
      7. 7.2.7 Static application scenario
      8. 7.2.8 DISABLE ARCHIVE
      9. 7.2.9 Analytics Accelerator - HPSS considerations
    3. 7.3 Providing support for big data
      1. 7.3.1 Enhancing big data analytics with Apache Hadoop
      2. 7.3.2 Example HDFS_READ with a generic table UDF
      3. 7.3.3 Example JAQL_SUBMIT
    4. 7.4 Using the scoring adapter to add predictive analytics to OLTP applications
    5. 7.5 Using JavaScript Object Notation with IBM DB2
    6. 7.6 Suppressing null indexes
  16. Chapter 8. XML
    1. 8.1 XQuery support
      1. 8.1.1 FLWOR expressions
      2. 8.1.2 XQuery constructors
      3. 8.1.3 Conditional expressions
      4. 8.1.4 Built-in functions
      5. 8.1.5 XQuery prolog
    2. 8.2 XML performance enhancements in DB2 10 and DB2 11
      1. 8.2.1 Eliminate hotspots during XML insert
      2. 8.2.2 Validate binary XML
      3. 8.2.3 Avoid revalidation during LOAD
      4. 8.2.4 Partial revalidation
      5. 8.2.5 XMLTABLE performance improvements
    3. 8.3 XQuery FLWOR expressions performance enhancements
    4. 8.4 XMLTABLE performance enhancements in DB2 11
      1. 8.4.1 Date/Time predicate pushdown
      2. 8.4.2 Optimize index key range for varchar predicates
      3. 8.4.3 Pushdown of column casting into XPath
  17. Chapter 9. Connectivity and administration routines
    1. 9.1 Client information enhancements
      1. 9.1.1 Expansion of the length of some Client information fields
      2. 9.1.2 Introduction the new client information field Client Correlation Token
      3. 9.1.3 Introduction of a new built-in session global variable
      4. 9.1.4 Using the client information fields
    2. 9.2 Cancel thread and cancel SQL statement improvements
      1. 9.2.1 Changes in Cancel DDF thread
      2. 9.2.2 Changes in SQL statement interruption processing
    3. 9.3 Continuous block fetching
    4. 9.4 Support for global variables
    5. 9.5 Local stored procedure execution improvement
    6. 9.6 Multi-threaded Java stored procedure environment
    7. 9.7 ADMIN_COMMAND_MVS stored procedure
    8. 9.8 Drivers, clients, and connectivity requirements
  18. Part 3 Operations and performance
  19. Chapter 10. Security
    1. 10.1 Enhancements for exit authorization checking
      1. 10.1.1 Use owner privileges for authorization
      2. 10.1.2 Refresh DB2 cache entries when RACF permissions change
    2. 10.2 Enhancements to program authorization
    3. 10.3 Column masking enhancements
      1. 10.3.1 Remove column access control restrictions for GROUP BY
      2. 10.3.2 Correct implementation of aggregate function with DISTINCT
      3. 10.3.3 Column access control for UNION
  20. Chapter 11. Utilities
    1. 11.1 Online REORG enhancements
      1. 11.1.1 Improve performance of partition-level REORG with non partitioned secondary indexes
      2. 11.1.2 SWITCH phase impact reduction
      3. 11.1.3 Physically delete empty partition-by-growth partitions
      4. 11.1.4 Automated REORG mapping table management
      5. 11.1.5 REORG without SORTing data
      6. 11.1.6 Partition-level inline image copy
      7. 11.1.7 Improved REORG LISTDEF processing
      8. 11.1.8 REBALANCE enhancements
      9. 11.1.9 REORG of LOB enhancements
      10. 11.1.10 Improved REORG serviceability
      11. 11.1.11 REORG change of defaults to match preferred practices
    2. 11.2 Enhanced statistics
      1. 11.2.1 RUNSTATS RESET ACCESSPATH
      2. 11.2.2 RUNSTATS USE PROFILE usability for LISTDEF
    3. 11.3 Backup and recovery enhancements
      1. 11.3.1 SYSLGRNX recording for catalog and directory table
      2. 11.3.2 VCAT name translation for RESTORE SYSTEM
      3. 11.3.3 Remove the incompatibility of REORG and COPY
      4. 11.3.4 Removal of many point-in-time recovery restrictions
    4. 11.4 LOAD and UNLOAD enhancements
      1. 11.4.1 LOAD SHRLEVEL NONE with PARALLEL option
      2. 11.4.2 LOAD SHRLEVEL CHANGE with PARALLEL option
      3. 11.4.3 Addition of crossloader support for XML
      4. 11.4.4 More offload to zIIP with NPSIs
    5. 11.5 Compression dictionaries for Change Data Capture
    6. 11.6 General enhancements
      1. 11.6.1 DISPLAY UTILITY additional output
      2. 11.6.2 Improved TEMPLATE for extended format data sets
      3. 11.6.3 DSN1COPY
      4. 11.6.4 REPAIR utility
      5. 11.6.5 Command to externalize RTS statistics
      6. 11.6.6 DSNACCOX
    7. 11.7 Deprecated functions
  21. Chapter 12. Installation and migration
    1. 12.1 Currency of versions and migration paths
    2. 12.2 Prerequisites for DB2 11
      1. 12.2.1 Processors
      2. 12.2.2 Auxiliary storage
      3. 12.2.3 Operational requirements
      4. 12.2.4 Optional program requirements
    3. 12.3 DB2 11 installation changes and considerations
      1. 12.3.1 More support of naming standards in install and IVP jobs
      2. 12.3.2 No more EDM calculations
      3. 12.3.3 Modified installation jobs
      4. 12.3.4 New installation job DSNTIJCB
      5. 12.3.5 Miscellaneous
    4. 12.4 Considerations for migrating to DB2 11
      1. 12.4.1 Premigration considerations
      2. 12.4.2 DB2 11 CM
      3. 12.4.3 DB2 11 ENFM and NFM
    5. 12.5 Subsystem parameters
      1. 12.5.1 New system parameters
      2. 12.5.2 Changed defaults for existing system parameters
      3. 12.5.3 Removed system parameters
      4. 12.5.4 Deprecated system parameters
    6. 12.6 Release incompatibilities
      1. 12.6.1 Application and SQL release incompatibilities
      2. 12.6.2 Utility release incompatibilities
      3. 12.6.3 Command release incompatibilities
      4. 12.6.4 Storage release incompatibilities
      5. 12.6.5 Functions that are deprecated
      6. 12.6.6 Functions that are no longer supported
    7. 12.7 Controlling application compatibility
      1. 12.7.1 Example of DB2 10 application compatibility
      2. 12.7.2 Overview of application compatibility in DB2 11
  22. Chapter 13. Performance
    1. 13.1 Performance expectations
    2. 13.2 System level performance
      1. 13.2.1 Internal optimization
      2. 13.2.2 Logging
      3. 13.2.3 Synergy with System z
      4. 13.2.4 Buffer management
      5. 13.2.5 Data sharing
    3. 13.3 Reduced need for REORG
      1. 13.3.1 Asynchronous removal of pseudo-deleted indexes
      2. 13.3.2 Indirect reference avoidance
    4. 13.4 More opportunities for RELEASE(DEALLOCATE)
    5. 13.5 Optimizer enhancements
      1. 13.5.1 Identification of critical statistics for improved query performance
  23. Part 4 Appendixes
  24. Appendix A. Information about IFCID changes
    1. A.1 New IFCIDs
    2. A.2 Aggregate accounting overview and purpose
    3. A.3 IFCID 53 and 58 enhancements overview
    4. A.4 Accounting trace enhancements overview
    5. A.5 IRLM Storage Accounting enhancement
    6. A.6 Stored procedure monitoring overview and purpose
    7. A.7 Other accounting changes
  25. Appendix B. Summary of relevant maintenance
    1. B.1 DB2 APARs
    2. B.2 z/OS APARs
    3. B.3 OMEGAMON PE APARs
  26. Related publications
    1. IBM Redbooks publications
    2. Other publications
    3. Online resources
    4. Help from IBM
  27. Back cover

Product information

  • Title: DB2 11 for z/OS Technical Overview
  • Author(s):
  • Release date: December 2013
  • Publisher(s): IBM Redbooks
  • ISBN: 9780738439051