Db2 for z/OS Utilities in Practice

Db2 for z/OS Utilities in Practice

by Craig Friske, Hendrik Mynhardt
Released September 2018
Publisher(s): IBM Redbooks
ISBN: 9780738456942

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

As IBM® continues to enhance the functionality, performance, and availability of IBM Db2®, the utilities have made significant strides towards self-management.

IBM Db2 for z/OS utilities is leading the trend towards autonomics. During the last couple of versions of Db2 for z/OS, and through the maintenance stream, new features and enhancements have been delivered to further improve the performance and functionality of the Db2 utilities.

The intent of this IBM Redpaper™ publication is to help Db2 Database Administrators, Db2 System Programmers, and anyone who runs Db2 for z/OS utilities implement best practices. The intent of this paper is not to replicate the Db2 for z/OS Utilities Reference Guide or the Db2 for z/OS Installation Guide.

This paper describes and informs you how to apply real-life practical preferred practices for the IBM Db2 for z/OS Utilities Suite. The paper concentrates on the enhancements provided by Db2 utilities, regardless of the version, albeit some functions and features are available only in Db2 12 for IBM z/OS®.

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. Chapter 1. Loading Db2 data
    1. 1.1 Usability functions
      1. 1.1.1 CONSTANT and CONSTANTIF
      2. 1.1.2 IGNORE keyword
      3. 1.1.3 DATE and TIMESTAMP formats
      4. 1.1.4 NUMRECS specification
    2. 1.2 Availability considerations
      1. 1.2.1 RESUME BACKOUT YES
      2. 1.2.2 REPLACE SHRLEVEL REFERENCE
      3. 1.2.3 SHRLEVEL CHANGE
      4. 1.2.4 RESUME YES copy support
    3. 1.3 Performance considerations
      1. 1.3.1 Partition parallelism: Multiple input data sets
      2. 1.3.2 SHRLEVEL NONE or REFERENCE Partition Parallelism: Single DS
      3. 1.3.3 Index parallelism
      4. 1.3.4 SHRLEVEL CHANGE Partition Parallelism: Single input data set
      5. 1.3.5 FORMAT INTERNAL
      6. 1.3.6 DRDA fast load
      7. 1.3.7 zIIP offload
  5. Chapter 2. Reorganizing Db2 data
    1. 2.1 Availability considerations
      1. 2.1.1 Batch windows
      2. 2.1.2 Switching phase during batch window
      3. 2.1.3 Switch phase while applications are running
      4. 2.1.4 The DRAIN_WAIT, RETURN, and RETRY_DELAY keywords
      5. 2.1.5 Minimizing application failures proper REORG settings
      6. 2.1.6 Recommendation for settings
      7. 2.1.7 Mapping table control (locking contention)
    2. 2.2 Performance considerations
      1. 2.2.1 Parallelism
      2. 2.2.2 SORTNPSI
      3. 2.2.3 Inline copies and flash copies
      4. 2.2.4 CPU and zIIP offload
      5. 2.2.5 DISCARDING rows during REORG
      6. 2.2.6 Sort products
    3. 2.3 Disk space usage and considerations
      1. 2.3.1 Avoiding sorting by unloading by the cluster index
      2. 2.3.2 Avoiding sorting with RECLUSTER NO
      3. 2.3.3 Smaller sorts by reorganizing subsets of partitions
    4. 2.4 Partition by growth table space considerations
      1. 2.4.1 REORG of a part range
      2. 2.4.2 Dropping partitions
    5. 2.5 Partition by range table space considerations
    6. 2.6 Recovery considerations
    7. 2.7 Materialization of pending schema changes
      1. 2.7.1 Converting to universal table spaces
    8. 2.8 Changing a partition boundary
    9. 2.9 Inserting a new partition
    10. 2.10 Altering column data types of lengths
  6. Chapter 3. Db2 backup and recovery
    1. 3.1 Using the COPY utility
      1. 3.1.1 Sequential copies
      2. 3.1.2 FlashCopy Image Copy – non-sequential
    2. 3.2 Using BACKUP SYSTEM
      1. 3.2.1 FLASHCOPY_PPRCP option
    3. 3.3 Db2 Recovery
      1. 3.3.1 RECOVER using the SCOPE keyword
      2. 3.3.2 MODIFY RECOVERY enhancements
    4. 3.4 BACKUP considerations and tips
      1. 3.4.1 COPY INDEXES
      2. 3.4.2 FlashCopy IC
      3. 3.4.3 CONCURRENT COPY
      4. 3.4.4 Split off active versus non-active objects
      5. 3.4.5 To QUIESCE or not to QUIESCE
      6. 3.4.6 OPTIONS EVENT(ITEMERROR,SKIP)
  7. Chapter 4. Statistics collection (RUNSTATS)
    1. 4.1 Functional improvements
      1. 4.1.1 Inline statistics parity
      2. 4.1.2 RESET keyword
    2. 4.2 Availability
      1. 4.2.1 INVALIDATECACHE
    3. 4.3 Performance
    4. 4.4 Usability
      1. 4.4.1 PROFILE keyword
      2. 4.4.2 PROFILE keyword and LISTDEF
  8. Chapter 5. Db2 Real Time Statistics (RTS)
    1. 5.1 RTS overview
      1. 5.1.1 How RTS is collected
      2. 5.1.2 How RTS is externalized
      3. 5.1.3 Where RTS information is stored
    2. 5.2 RTS table SYSIBM.SYSTABLESPACESTATS
      1. 5.2.1 SYSTABLESPACESTATS: Incremental statistics
      2. 5.2.2 SYSTABLESPACESTATS – Columns affected by the REORG utility
      3. 5.2.3 SYSTABLESPACESTATS – Columns affected by the LOAD utility
      4. 5.2.4 SYSTABLESPACESTATS – Columns affected by the COPY utility
      5. 5.2.5 SYSTABLESPACESTATS – columns affected by RUNSTATS utility
    3. 5.3 RTS table SYSIBM.SYSINDEXSPACESTATS
      1. 5.3.1 SYSINDEXSPACESTATS columns affected by the COPY utility
      2. 5.3.2 SYSINDEXSPACESTATS columns affected by the REORG INDEX utility
      3. 5.3.3 SYSINDEXSPACESTATS columns affected by the LOAD utility
      4. 5.3.4 SYSINDEXSPACESTATS columns affected by the REBUILD INDEX utility
      5. 5.3.5 SYSINDEXSPACESTATS columns affected by RUNSTATS
  9. Chapter 6. Repairability and REPAIR CATALOG
    1. 6.1 REPAIR CATALOG TEST
    2. 6.2 Multi-table tablespaces and version wrapping
      1. 6.2.1 Single table tablespaces
      2. 6.2.2 Multi-table tablespaces
  10. Chapter 7. Db2 DSNZPARMs for utilities
    1. 7.1 ZPARMs affecting dynamic allocation of data sets
    2. 7.2 ZPARMs affecting utility timeout operations
    3. 7.3 ZPARMs affecting SORT operations
    4. 7.4 ZPARMS affecting RUNSTATS operations
    5. 7.5 ZPARMS affecting IBM FlashCopy operations
    6. 7.6 ZPARMS affecting BACKUP and RESTORE SYSTEM
    7. 7.7 ZPARMS affecting the RECOVER utility
    8. 7.8 ZPARMS affecting the CHECK utility
    9. 7.9 ZPARMS affecting the REORG utility
    10. 7.10 ZPARM affecting object conversion
  11. Back cover

Product information

  • Title: Db2 for z/OS Utilities in Practice
  • Author(s): Craig Friske, Hendrik Mynhardt
  • Release date: September 2018
  • Publisher(s): IBM Redbooks
  • ISBN: 9780738456942