LOBs with DB2 for z/OS: Stronger and Faster

LOBs with DB2 for z/OS: Stronger and Faster

by Paolo Bruni, Patric Becker, Tim Bohlsen, Burkhard Diekmann, Dima Etkin, Davy Goethals
Released November 2006
Publisher(s): IBM Redbooks
ISBN: None

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

The requirements for a database management system (DBMS) have included support for very large and complex data objects.
DB2 UDB for OS/390 Version 6 introduced the support for large objects (LOBs): they can contain text documents, images, or movies, and can be stored directly in the DBMS with sizes up to 2 gigabytes per object and 65,536 TB for a single LOB column in a 4,096 partition table. The introduction of these new data types has implied some changes in the administration processes and programming techniques. The book Large Objects with DB2 for z/OS and OS/390, SG24-6571, introduced and described the usage of LOBs with DB2 for z/OS at Version 7 level.
Major enhancements for LOB manipulation have been introduced with DB2 UDB for z/OS Version 8 and DB2 Version 9.1 for z/OS (DB2 9 in this book). These enhancements include performance functions such as the avoidance of LOB locks and DRDA LOB flow optimization, usability functions such as file reference variables, FETCH CONTINUE, and the automatic creation of objects. DB2 utilities provide integrated support with LOAD and UNLOAD, Cross Loader, REORG, CHECK DATA, and CHECK LOB.
In this IBM Redbooks publication, we provide a totally revised description of the DB2 functions for LOB support as well as useful information about how to design and implement LOBs. We also offer examples of their use, programming considerations, and the enhanced processes used for their administration and maintenance. We also detail how SAP solutions use LOBs.
This book replaces the previous book, Large Objects with DB2 for z/OS and OS/390, SG24-6571, for DB2 Version 8 and Version 9.1.

Please note that the additional material referenced in the text is not available from IBM.

Table of contents

  1. Figures
  2. Tables
  3. Examples
  4. Notices
    1. Trademarks
  5. Summary of changes
    1. November 2006, First Edition
      1. November 2007, First Update
  6. Preface
    1. The team that wrote this IBM Redbooks publication
    2. Become a published author
    3. Comments welcome
  7. Chapter 1: Introduction
    1. Object-relational in DB2 for z/OS
    2. Changes with DB2 9
    3. DB2 for z/OS and large objects
    4. The IBM Redbooks publication contents
    5. Pointers to LOB functions after DB2 Version 6
  8. Chapter 2: Large objects with DB2
    1. Introduction to LOB data types
    2. The LOB table spaces
      1. Single LOB column table space
      2. Multiple LOB column table space
      3. Partitioned LOB table space
      4. The full LOB implementation structure
    3. LOB locators
      1. Purpose of LOB locators
      2. Different types of LOB locators
    4. LOB file reference variables
      1. DB2-generated file reference variable constructs
      2. Language support for LOB file reference variables
      3. File local/client support
  9. Chapter 3: Creating LOBs
    1. Alternatives in defining LOBs
      1. Example of automatic creation of objects
      2. Using CURRENT RULES STD
      3. Manual creation of objects (1/2)
      4. Manual creation of objects (2/2)
      5. Adding a LOB column to an existing table
    2. Defining ROWIDs
      1. Creating the ROWID column
    3. LOBs and LOG activity
      1. LOGGED and NOT LOGGED attributes
      2. Logging for all LOB sizes
    4. Additional considerations for creating LOB objects
      1. Data conversion
      2. Buffer pools and LOB table spaces
      3. Locking with LOBs
      4. Buffer pool and page size considerations
      5. DSSIZE for LOB table spaces
      6. GBPCACHE parameter
      7. Impact on cursors fetching LOB values
    5. LOBs are different DB2 objects
    6. Physical layout of LOBs
  10. Chapter 4: Using LOBs
    1. Language considerations
      1. LOB host variables, locators, and file reference variables
      2. Use of a double or triple SQLDA in dynamic SQL
      3. Working with LOBs in JDBC and SQLJ applications
      4. Specific SQL support for LOBs
      5. Functions such as XML2CLOB
      6. Stored procedures
    2. LOB locators
      1. Getting to know LOB locators
      2. Examples of using locators
    3. DRDA LOB flow optimization
      1. DB2 Universal Java Driver
    4. Feeding a LOB column
      1. Loading a LOB column using LOAD or the cross loader
      2. Inserting LOBs using the host application (1/2)
      3. Inserting LOBs using the host application (2/2)
      4. DB2 for Linux, UNIX and Windows import
    5. Locking
      1. Locking for LOBs with DB2 V8 (1/2)
      2. Locking for LOBs with DB2 V8 (2/2)
      3. Locking for LOBs with DB2 9 (1/2)
      4. Locking for LOBs with DB2 9 (2/2)
    6. Unloading LOBs
      1. Unloading a LOB using an application
      2. Using FETCH CONTINUE (1/2)
      3. Using FETCH CONTINUE (2/2)
      4. Finding the nth occurrence of a string
    7. Updating LOBs
      1. Deleting a specific part of a LOB
      2. Updating a specific part of a LOB
      3. Updating the entire LOB value
    8. General best practices
  11. Chapter 5: SAP usage of LOBs
    1. Overview of SAP usage of LOBs
      1. Some history of SAP LOB usage
      2. Basic architecture
      3. Connectivity
      4. Why use LOBs
      5. SAP usage of LOBs in terms of number and size
    2. ABAP and Dynpro source and Load
    3. Programming techniques for the ABAP stack
      1. Basic locator access
      2. CLI Streaming Interface
    4. Optimization techniques and query rewrite
      1. Local LOB buffer
      2. Retrieve length and maximal data with locator
      3. Optimizing the free locator statement
      4. Comparison of different techniques using SGEN
      5. Chaining
    5. Programming techniques with JDBC
    6. Data Dictionary considerations
      1. ABAP stack
      2. Java stack
      3. DSNZPARMs for DB2 V8
      4. ROWID
    7. Unicode
    8. Some points of SAP LOB usage with CCMS
    9. Portability aspects
    10. Monitoring and tracing
    11. Database interface layer profile parameters
    12. Performance measurements
      1. Locks and SELECT
      2. Locks and INSERT
      3. UPDATE improvement
  12. Chapter 6: Utilities with LOBs
    1. UNLOAD (1/2)
    2. UNLOAD (2/2)
    3. DSNTIAUL
    4. LOAD
      1. Loading LOB data as normal data columns
      2. Loading LOB data using file reference variables
      3. Using the cross loader
      4. Impact of logging
    5. COPY
    6. COPYTOCOPY
    7. QUIESCE
    8. REPORT
    9. RUNSTATS
    10. REORG (1/3)
    11. REORG (2/3)
    12. REORG (3/3)
    13. RECOVER and REBUILD
    14. CHECK DATA
    15. CHECK LOB
    16. CHECK INDEX
    17. REPAIR
    18. DSN1COPY and DSN1PRNT
  13. Chapter 7: Data administration with LOBs
    1. LOBs in the DB2 catalog
      1. Catalog definitions for LOBs
      2. LOBs defined in DB2 catalog
      3. Real Time Statistics
    2. Recovery strategies and considerations
      1. LOGGED base table space with LOGGED LOB table space
      2. LOGGED base table space with NOT LOGGED LOB table space
      3. NOT LOGGED base table space with NOT LOGGED LOB table space
      4. LOBs and SYSTEM RECOVERY
      5. Conclusions on recovery of LOB data
    3. Altering tables containing LOB columns
  14. Chapter 8: Performance with LOBs
    1. LOB materialization
      1. The different cases of materialization
      2. Materialization avoidance techniques
    2. Virtual storage management for LOBs
      1. DB2 subsystem parameters for LOBs
    3. Buffer pools and group buffer pools
      1. Virtual buffer pools
      2. Considerations for a data sharing environment
    4. Logging with LOBs
    5. Accessing LOBs
      1. Reading LOBs
      2. Writing LOBs
    6. Comparing SQL accounting profiles
    7. Important I/O aspects
    8. IFCID enhancements for LOBs
    9. DRDA LOB flow optimization performance
    10. LOB recommendations for performance
  15. Appendix A: Additional material
    1. Locating the Web material
    2. Using the Web material
      1. System requirements for downloading the Web material
      2. How to use the Web material
  16. Related publications
    1. IBM Redbooks
    2. Other publications
    3. Online resources
    4. How to get IBM Redbooks
    5. Help from IBM
  17. Abbreviations and acronyms
  18. Index (1/2)
  19. Index (2/2)
  20. Back cover

Product information

  • Title: LOBs with DB2 for z/OS: Stronger and Faster
  • Author(s): Paolo Bruni, Patric Becker, Tim Bohlsen, Burkhard Diekmann, Dima Etkin, Davy Goethals
  • Release date: November 2006
  • Publisher(s): IBM Redbooks
  • ISBN: None