Data Integrity with DB2 for z/OS

Data Integrity with DB2 for z/OS

by Paolo Bruni, John Iczkovits, Rama Naidoo, Fabricio Pimentel, Suresh Sane
Released July 2006
Publisher(s): IBM Redbooks
ISBN: 9780738495545

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

DB2 provides functions to guarantee integrity at the system level and at the application level. From the system point of view, DB2's integration with zSeries and disk storage architecture is the cornerstone for data integrity. Logging functionality and COPY and RECOVER utilities are the building blocks for bringing the table space back to a current or consistent status in case of hardware or software failures or when application events need to be rerun.
From the application point of view, DB2 supports locking and commit at the transaction level, and general data integrity (at entity and semantic level), and a set of referential constraint rules for each parent/dependent table relationship. The tables linked by referential integrity are recognized during the execution of the QUIESCE utility. Other logical relations across tables, necessary to support business rules, are implemented via constraints, triggers, user defined functions, and user defined tables. Informational constraints also exist, they are not enforced by the database manager, they are used to improve query performance. In this IBM Redbooks publication, we briefly describe the integration of DB2 for z/OS with System z architecture, we then explore the data integrity options and utilize the standard recovery functions for application-related issues.

Table of contents

  1. Figures
  2. Examples
  3. Tables
  4. Notices
    1. Trademarks
  5. Preface
    1. The team that wrote this redbook
    2. Become a published author
    3. Comments welcome
  6. Chapter 1: Introduction
    1. Host platform
      1. z/OS and OS/390 system integrity
      2. System-level security
      3. Transaction-level security
      4. zSeries cryptography
      5. System z integrity features
      6. The zIIP
      7. VSAM share options
      8. Data sharing integrity
      9. Global resource serialization
      10. DB2 controls
      11. Auditing your DB2 applications
      12. Other security enhancements
      13. DB2 column level encryption
      14. IBM Data Encryption for IMS and DB2 Databases
    2. Information integrity
    3. DB2 and data integrity
      1. Entity integrity
      2. Semantic integrity
      3. Referential integrity
      4. Domain integrity
    4. Example of integrity needed across applications
      1. Customer names and addresses across applications
  7. Chapter 2: Semantic integrity
    1. Constraints
      1. Data constraint
      2. NOT NULL constraints
      3. Unique constraint
      4. Check constraints
    2. Distinct types
      1. Why distinct types
      2. Creating a distinct type
      3. Generated cast functions
      4. Comparing distinct types
      5. Assigning a distinct type
      6. Invoking routines with distinct types
      7. Errors with comparisons across distinct types
      8. Summary and usage recommendations
  8. Chapter 3: Referential integrity
    1. Referential constraints
    2. RI in the relational model
      1. RI concepts
      2. RI rules and options
    3. RI in DB2
      1. Additional DB2 terminology
      2. Data definitions for RI (1/2)
      3. Data definitions for RI (2/2)
      4. Plan, package, and trigger considerations
      5. Maintaining RI when using data encryption
      6. Informational referential constraint
    4. Functional implications
      1. DELETE rule for self-referencing tables
      2. DELETE with RESTRICT
      3. DELETE with SET NULL
      4. Cycles should not cause a table to be delete-connected to itself
      5. Table delete-connections through multiple paths
      6. INSERT
      7. UPDATE
      8. DELETE
    5. Summary of design recommendations
      1. Primary key
      2. Foreign keys
      3. Circumventing DML restrictions
    6. Code and look-up tables
      1. Code table alternatives
    7. DB2 versus application RI
    8. REPORT utility
    9. CHECK utility
      1. CHECK DATA
      2. CHECK INDEX
    10. LOAD utility
      1. Loading tables involved in cycles
    11. Performance
    12. Migrating applications to RI
      1. Planning considerations
      2. Application implementation considerations
    13. DB2 catalog information and queries
      1. DB2 catalog extensions
      2. Sample catalog queries
      3. Constraints and multilevel security
  9. Chapter 4: Triggers
    1. Why use triggers for data integrity
    2. Trigger terminology
    3. Extending triggers with UDFs and stored procedures
      1. Data validation
      2. Data propagation
    4. Invoking UDFs and stored procedures
      1. Using the VALUES statement
      2. Using the SELECT statement
      3. Using the CALL statement
    5. Passing parameters to UDFs and stored procedures
      1. Using transition variables
      2. Using transition tables
    6. Raising error conditions
    7. Handling errors during execution
    8. Auditing versus mass replication
    9. Impact of LOAD utility
    10. DB2-enforced RI versus triggers
    11. Execution sequence of multiple triggers
    12. Trigger cascading
      1. Triggers at the same level
      2. Triggers at different levels
    13. Interactions among triggers and other integrity checks
    14. Creating triggers to obtain consistent results
      1. Effect of an uncorrelated subquery
      2. Effect of row processing order
      3. Effect of set update with row triggers
    15. Common business scenarios
      1. Data validation
      2. Complex data validation with a UDF
      3. Maintaining redundant data
      4. Complex redundant data maintenance with a stored procedure
      5. Bidirectional data maintenance
      6. Maintaining summary data
      7. Maintaining existence flags
      8. Enforcing multiple parent RI
      9. Enforcing “Empty-nest-last-child-gone” rule
      10. Generating alerts
      11. Writing an MQ message
      12. Auditing
  10. Chapter 5: Other integrity features
    1. Data structure validation
      1. DSN1COPY with CHECK option
      2. DSN1PRNT with FORMAT option
      3. COPY with CHECKPAGE option
    2. Insert within select
      1. Generated values example
      2. Multiple-row inserts example
      3. Trigger example
    3. Atomic versus not atomic on multi-row insert and update
    4. Sequence objects
      1. Generated values may have gaps
      2. Generated values may not be in strict sequential order
    5. Informational RI
      1. What is informational RI
      2. Impact on utilities
      3. Impact on MQT usage
      4. Usage recommendations
    6. Locking
      1. Data sharing implications
      2. Locking protocol level 2
  11. Chapter 6: Recovery
    1. DB2 attachment facilities
    2. DB2 commit process
    3. Unit of recovery
      1. Commit processing for TSO applications
      2. Commit processing for CICS, IMS, or RRSAF applications
      3. Consistency across multiple DBMSs
    4. Unit of work
      1. Commit
      2. Commit frequency
      3. Cursors WITH HOLD
      4. Savepoints
      5. More on read only COMMIT
    5. Data integrity
      1. Concurrent update
      2. Last update column
      3. Work-in-progress tables
      4. Restricting other applications’ data access
      5. Applications to switch between isolation levels
      6. Error handling control table
      7. Restart using sequential input and output files
      8. Restart using DB2 tables for input and output files
    6. Scrollable cursors
      1. Static scrollable cursors
      2. Dynamic scrollable cursors
      3. FETCHing options for scrollable cursors
      4. Updating using scrollable cursors
      5. Change of underlying data for scrollable cursors
      6. Using multi-row FETCH with scrollable cursors
      7. SQLCODEs for scrollable cursors
      8. Summary on scrollable cursors
    7. DB2 subsystem restart after abend
    8. Recovery of objects in error
      1. LPL recovery
      2. CHECK-pending
      3. Write Error Page Range recovery
      4. COPY utility
    9. Application recovery process
      1. Rolling back work
    10. Preparing to recover to a point of consistency
      1. Copying the data
      2. Recovery of the data to the previous point of consistency (1/2)
      3. Recovery of the data to the previous point of consistency (2/2)
      4. Restore data to previous point in time
      5. New utilities in DB2 V8 for online backup and point in time recovery
  12. Related publications
    1. IBM Redbooks
    2. Other publications
    3. Online resources
    4. How to get IBM Redbooks
    5. Help from IBM
  13. Abbreviations and acronyms
  14. Index (1/3)
  15. Index (2/3)
  16. Index (3/3)
  17. Back cover

Product information

  • Title: Data Integrity with DB2 for z/OS
  • Author(s): Paolo Bruni, John Iczkovits, Rama Naidoo, Fabricio Pimentel, Suresh Sane
  • Release date: July 2006
  • Publisher(s): IBM Redbooks
  • ISBN: 9780738495545