O'Reilly logo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

IBM DB2 12 for z/OS Technical Overview

Book Description

IBM® DB2® 12 for z/OS® delivers key innovations that increase availability, reliability, scalability, and security for your business-critical information. In addition, DB2 12 for z/OS offers performance and functional improvements for both transactional and analytical workloads and makes installation and migration simpler and faster. DB2 12 for z/OS also allows you to develop applications for the cloud and mobile devices by providing self-provisioning, multitenancy, and self-managing capabilities in an agile development environment.
DB2 12 for z/OS is also the first version of DB2 built for continuous delivery.

This IBM Redbooks® publication introduces the enhancements made available with DB2 12 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 12.

Table of Contents

  1. Front cover
  2. Notices
    1. Trademarks
  3. Preface
    1. Authors
    2. Now you can become a published author, too!
    3. Comments welcome
    4. Stay connected to IBM Redbooks
  4. Part 1 Overview
  5. Chapter 1. DB2 12 for z/OS at a glance
    1. 1.1 Subsystem
    2. 1.2 Application functions
    3. 1.3 Operations and performance
  6. Chapter 2. Continuous delivery
    1. 2.1 Function level
      1. 2.1.1 Star function level
    2. 2.2 Catalog level
    3. 2.3 Code level
    4. 2.4 Activating a function level
    5. 2.5 DISPLAY GROUP command
    6. 2.6 Catalog changes
      1. 2.6.1 The SYSIBM.SYSLEVELUPDATES table
    7. 2.7 Application compatibility (APPLCOMPAT)
      1. 2.7.1 Managing applications through function level changes
      2. 2.7.2 Data Definition Language (DDL) statements sensitive to APPLCOMPAT
    8. 2.8 SQL processing option SQLLEVEL
    9. 2.9 New built-in global variables
      1. 2.9.1 PRODUCTID_EXT
      2. 2.9.2 CATALOG_LEVEL
      3. 2.9.3 DEFAULT_SQLLEVEL
    10. 2.10 DB2 supplied stored procedures
      1. 2.10.1 ADMIN_COMMAND_DB2
      2. 2.10.2 GET_CONFIG
    11. 2.11 Resource access control facility exit
    12. 2.12 Instrumentation
  7. Part 2 Subsystem
  8. Chapter 3. Scalability
    1. 3.1 Range-partitioned table spaces changes
      1. 3.1.1 PBR RPN table space characteristics
      2. 3.1.2 PBR RPN partitioned index characteristics
      3. 3.1.3 PBR RPN non-partitioned index characteristics
      4. 3.1.4 PBR RPN considerations
    2. 3.2 DB2 internal latch contention relief
    3. 3.3 Buffer pool simulation
    4. 3.4 Support for sizes greater than 4 GB of active log data sets
  9. Chapter 4. Availability
    1. 4.1 Improved availability for pending definition changes
      1. 4.1.1 Altering index compression attribute
      2. 4.1.2 Altering column
    2. 4.2 Catalog availability improvements
      1. 4.2.1 Handling dynamic SQL statement
      2. 4.2.2 Single phase catalog migration
    3. 4.3 Removal of point-in-time recovery restrictions for PBG table spaces
    4. 4.4 PBR RPN DSSIZE increase
    5. 4.5 Insert partition
    6. 4.6 REORG enhancements for PBGs, FlashCopy and LOBs
      1. 4.6.1 Partition-by-growth (PBG)
      2. 4.6.2 FlashCopy
      3. 4.6.3 Large object (LOB)
    7. 4.7 LOAD RESUME YES BACKOUT YES option
    8. 4.8 Faster point-in-time recovery
      1. 4.8.1 Single object by defaulting to the PARALLEL(1) option
      2. 4.8.2 SCOPE UPDATED keyword
    9. 4.9 TRANSFER OWNERSHIP SQL statement
    10. 4.10 Auto-retry of GRECP and LPL recovery
  10. Chapter 5. Data sharing
    1. 5.1 DISPLAY GROUP command
    2. 5.2 XA support for global transactions
    3. 5.3 Peer recovery
    4. 5.4 Automatic retry of GRECP and LPL recovery
    5. 5.5 Improved lock avoidance checking
    6. 5.6 Asynchronous lock duplexing
  11. Part 3 Application functions
  12. Chapter 6. SQL
    1. 6.1 Introduction
    2. 6.2 Additional support for triggers
      1. 6.2.1 Basic triggers
      2. 6.2.2 Advanced triggers
      3. 6.2.3 Differences between basic triggers and advanced triggers
      4. 6.2.4 Maintaining trigger activation order
    3. 6.3 Pagination support
      1. 6.3.1 Returning a subset of rows
      2. 6.3.2 Data-dependent pagination support
      3. 6.3.3 Numeric-based pagination
    4. 6.4 Additional support for arrays
      1. 6.4.1 Arrays as global variables
      2. 6.4.2 Associative array support on ARRAY_AGG aggregate function
      3. 6.4.3 Optional ORDER BY clause on ARRAY_AGG aggregate function
    5. 6.5 MERGE statement enhancements
      1. 6.5.1 Additional source value support
      2. 6.5.2 Additional data modification support
      3. 6.5.3 Additional matching condition option
      4. 6.5.4 Additional predicates on matching conditions support
      5. 6.5.5 Atomicity
      6. 6.5.6 Enhanced MERGE statement example
    6. 6.6 New built-in functions
      1. 6.6.1 Aggregate functions for statistics
      2. 6.6.2 Scalar functions for hashing
      3. 6.6.3 GENERATE_UNIQUE_BINARY scalar function
      4. 6.6.4 VARCHAR_BIT_FORMAT scalar function enhancement
    7. 6.7 Enhanced built-in function support
      1. 6.7.1 TIMESTAMP scalar function enhancement
      2. 6.7.2 XMLMODIFY scalar function enhancement
  13. Chapter 7. Application enablement
    1. 7.1 Ensuring application compatibility
    2. 7.2 Temporal table enhancements
      1. 7.2.1 Enhanced application periods
      2. 7.2.2 Referential constraints for temporal tables
      3. 7.2.3 Temporal logical transactions
      4. 7.2.4 Auditing capabilities using temporal tables
  14. Chapter 8. Connectivity and administration routines
    1. 8.1 Maintaining session data on the target server
    2. 8.2 Preserving prepared dynamic statements after a ROLLBACK
    3. 8.3 DRDA fast load
    4. 8.4 Profile monitoring for remote threads and connections
      1. 8.4.1 Automatic start of profiles during subsystem start
      2. 8.4.2 Support for global variables
      3. 8.4.3 Support for wildcarding
      4. 8.4.4 Idle thread enhancement
    5. 8.5 Stored procedures supplied by DB2
  15. Part 4 Operations and performance
  16. Chapter 9. Administrator function
    1. 9.1 Dynamic plan stability
      1. 9.1.1 Stabilization into and loading from catalog tables
      2. 9.1.2 Stabilization method
      3. 9.1.3 Catalog tables
      4. 9.1.4 Calculating the EDM statement cache hit ratio
      5. 9.1.5 Invalidation of stabilized dynamic statements
      6. 9.1.6 EXPLAIN changes
      7. 9.1.7 The FREE STABILIZED DYNAMIC QUERY subcommand
      8. 9.1.8 Monitor for stabilization
      9. 9.1.9 DSNZPARM and installation panel
    2. 9.2 Resource limit facility for static SQL
      1. 9.2.1 Reactive governing static SQL
      2. 9.2.2 Use cases
      3. 9.2.3 RLF DSNZPARMs and installation panels
    3. 9.3 Column level deferred alter (pending alter column)
      1. 9.3.1 Utility
      2. 9.3.2 ALTER INDEX
    4. 9.4 Insert partition
      1. 9.4.1 ALTER ADD PARTITION
      2. 9.4.2 Utilities affected
      3. 9.4.3 Catalog changes
  17. Chapter 10. Security
    1. 10.1 Installation or migration without requiring SYSADM
    2. 10.2 UNLOAD privilege
      1. 10.2.1 Enforcing new privilege
      2. 10.2.2 Using DB2 security facility
      3. 10.2.3 Using Resource Access Control Facility (RACF)
    3. 10.3 Object ownership transfer
      1. 10.3.1 Supported objects
      2. 10.3.2 New owner
      3. 10.3.3 Revoking privileges of current owner
  18. Chapter 11. Utilities
    1. 11.1 Backup and recovery enhancements
      1. 11.1.1 Sequential image copy enhancements
      2. 11.1.2 Copy support for FASTREPLICATION
      3. 11.1.3 Alternate copy pools for system-level backups
      4. 11.1.4 FLASHCOPY_PPRCP keyword option
      5. 11.1.5 Point-in-time recovery enhancements
      6. 11.1.6 MODIFY RECOVERY enhancements
    2. 11.2 RUNSTATS enhancements
      1. 11.2.1 Specifying FREQVAL without the COUNT n keywords
      2. 11.2.2 USE PROFILE support for inline statistics
      3. 11.2.3 INVALIDATECACHE option
      4. 11.2.4 RUNSTATS TABLESPACE LIST INDEX improvements
      5. 11.2.5 New keyword REGISTER for RUNSTATS utility
    3. 11.3 REORG enhancements
      1. 11.3.1 Improved FlashCopy management
      2. 11.3.2 Preventing COPY-pending on a LOB table space during REORG of PBG
      3. 11.3.3 Improved partition-level PBG REORGs
      4. 11.3.4 REORG option for empty PBG partitions deletion
      5. 11.3.5 Support for the COMPRESSRATIO catalog column
      6. 11.3.6 Display claimers information on each REORG drain failure
      7. 11.3.7 Additional REORG enhancements
    4. 11.4 LOAD and UNLOAD enhancements
      1. 11.4.1 LOAD enhancements
      2. 11.4.2 UNLOAD enhancements
  19. Chapter 12. Installation and migration
    1. 12.1 Prerequisites for DB2 12
      1. 12.1.1 Data sharing
      2. 12.1.2 Processor requirements
      3. 12.1.3 Software requirements
      4. 12.1.4 DB2 Connect prerequisites
      5. 12.1.5 Programming language requirements, minimum levels
      6. 12.1.6 Minimum configuration (IEASYSxx)
    2. 12.2 Single-phase migration and function level
      1. 12.2.1 Fallback SPE
      2. 12.2.2 EARLY code
      3. 12.2.3 Pre-migration checkout
      4. 12.2.4 Creating DSNZPARM and DECP modules
      5. 12.2.5 Creating and verifying routines supplied by DB2
      6. 12.2.6 REBIND at each new release
      7. 12.2.7 Activating new function level
      8. 12.2.8 Deprecated in earlier releases and removed in DB2 12
    3. 12.3 Installing a new DB2 12 system
      1. 12.3.1 Defines DB2 to z/OS
    4. 12.4 Subsystem parameters
      1. 12.4.1 New subsystem parameters
      2. 12.4.2 Removed subsystem parameters
      3. 12.4.3 Install Parameters Default Changes
      4. 12.4.4 Deprecated system parameters
    5. 12.5 Installation or migration without requiring SYSADM
    6. 12.6 Installation with z/OS Management Facility (z/OSMF)
      1. 12.6.1 How to use DB2 installation CLIST and panels to generate z/OSMF artifacts
      2. 12.6.2 Feeding the generated artifacts to z/OSMF
    7. 12.7 Temporal catalog
      1. 12.7.1 System-period data versioning for two RTS catalog tables
      2. 12.7.2 Real-time statistics externalization during migration
  20. Chapter 13. Performance
    1. 13.1 Performance expectations
    2. 13.2 In-memory buffer pool
    3. 13.3 In-memory index optimization
    4. 13.4 Improved insert performance for non-clustered data
      1. 13.4.1 DDL clause on CREATE TABLESPACE and ALTER TABLESPACE
      2. 13.4.2 SYSIBM.SYSTABLESPACE new column: INSERTALG
      3. 13.4.3 ZPARM: DEFAULT_INSERT_ALGORITHM
    5. 13.5 Query performance enhancements
      1. 13.5.1 UNION ALL and Outer Join enhancements
      2. 13.5.2 Sort improvements
      3. 13.5.3 Predicate optimization
      4. 13.5.4 Execution time adaptive index
  21. Part 5 Appendixes
  22. Appendix A. Information about IFCID changes
    1. IFCID header changes
    2. New IFCIDs
    3. Application compatibility IFCID changes
    4. Dynamic SQL plan stability IFCID changes
    5. Fast INSERT IFCID changes
    6. Lift partition limits IFCID changes limits
    7. Large object (LOB) compression IFCID changes
    8. Transfer ownership IFCID changes
    9. UNLOAD privilege for UNLOAD utility IFCID changes
    10. Additional changed IFCIDs
  23. Appendix B. Additional material
    1. Locating the web material
    2. Downloading and extracting the web material
  24. Back cover