DB2 Developer’s Guide: A Solutions-Oriented Approach to Learning the Foundation and Capabilities of DB2 for z/OS, Sixth Edition

DB2 Developer’s Guide: A Solutions-Oriented Approach to Learning the Foundation and Capabilities of DB2 for z/OS, Sixth Edition

Released April 2012
Publisher(s): IBM Press
ISBN: 9780132836470

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 Developer's Guide is the field's #1 go-to source for on-the-job information on programming and administering DB2 on IBM z/OS mainframes. Now, three-time IBM Information Champion Craig S. Mullins has thoroughly updated this classic for DB2 v9 and v10. Mullins fully covers new DB2 innovations including temporal database support; hashing; universal tablespaces; pureXML; performance, security and governance improvements; new data types, and much more. Using current versions of DB2 for z/OS, readers will learn how to: * Build better databases and applications for CICS, IMS, batch, CAF, and RRSAF * Write proficient, code-optimized DB2 SQL * Implement efficient dynamic and static SQL applications * Use binding and rebinding to optimize applications * Efficiently create, administer, and manage DB2 databases and applications * Design, build, and populate efficient DB2 database structures for online, batch, and data warehousing * Improve the performance of DB2 subsystems, databases, utilities, programs, and SQL stat DB2 Developer's Guide, Sixth Edition builds on the unique approach that has made previous editions so valuable. It combines: * Condensed, easy-to-read coverage of all essential topics: information otherwise scattered through dozens of documents * Detailed discussions of crucial details within each topic * Expert, field-tested implementation advice * Sensible examples

Table of contents

  1. Title Page
  2. Copyright Page
  3. Accolades for DB2 Developer’s Guide
  4. Dedication Page
  5. Contents at a Glance
  6. Contents
  7. Preface: A Short History of DB2 for z/OS
  8. Acknowledgments
  9. About the Author
  10. Part I. SQL Techniques, Tips, and Tricks
    1. Chapter 1. The Magic Words
      1. An Overview of SQL
      2. SQL Tools of the Trade
      3. Static SQL
      4. Dynamic SQL
      5. SQL Performance Factors
      6. Summary
    2. Chapter 2. Data Manipulation Guidelines
      1. A Bag of Tricks
      2. SQL Access Guidelines
      3. Complex SQL Guidelines
      4. Common Table Expressions and Recursion
      5. Working with Nulls
      6. Date and Time Guidelines
      7. Data Modification Guidelines
      8. Summary
    3. Chapter 3. Using DB2 Functions
      1. Aggregate Functions
      2. Scalar Functions
      3. Table Functions
      4. MQSeries Built-In Functions
      5. XML Built-In Functions
      6. The RAISE_ERROR Function
      7. The CAST Operation
      8. Built-In Function Guidelines
      9. Summary
    4. Chapter 4. Using DB2 User-Defined Functions and Data Types
      1. What Is a User-Defined Function?
      2. Types of User-Defined Functions (UDFs)
      3. What Is a User-Defined Data Type?
      4. User-Defined Data Types (UDTs) and Strong Typing
      5. Summary
    5. Chapter 5. Data Definition Guidelines
      1. An Overview of DB2 Database Objects
      2. DB2 Databases
      3. Creating and Using DB2 Table Spaces
      4. DB2 Storage and STOGROUPs
      5. Table Guidelines
      6. General Table Guidelines
      7. Normalization and Denormalization
      8. Assuring Data Integrity in DB2
      9. Referential Integrity
      10. Views, Aliases, and Synonyms
      11. Index Guidelines
      12. Naming Conventions
      13. Miscellaneous DDL Guidelines
      14. Summary
    6. Chapter 6. DB2 Indexing and Hashing Guidelines
      1. How an Index Works
      2. Creating Indexes
      3. DB2 Hashing and Hash Organized Tables
      4. Index and Hash Guidelines
      5. Summary
    7. Chapter 7. Database Change Management, Schema Evolution, and Database Definition On Demand
      1. Online Schema Changes
      2. Versioning for Online Schema Changes
      3. Summary
    8. Chapter 8. Using DB2 Triggers
      1. What Is a Trigger?
      2. Trigger Guidelines
    9. Chapter 9. Large Objects and Object/Relational Databases
      1. Defining the Term “Object/Relational”
      2. What Is a Large Object?
      3. LOB Guidelines
      4. DB2 Extenders™
    10. Chapter 10. pureXML: Using XML in DB2 for z/OS
      1. What Is XML?
      2. pureXML
      3. XML-DB2 Guidelines
      4. Summary
    11. Chapter 11. Supporting Temporal Data in DB2 for z/OS
      1. The Need for Temporal Data
      2. DB2 Temporal Support
      3. Temporal Data Guidelines
      4. Summary
    12. Chapter 12. DB2 Security, Authorization, and Auditing
      1. Authorization and Privileges
      2. Database Auditing
      3. Using External Security (for Example, RACF, ACF2, and Top Secret)
      4. Summary
  11. Part II. DB2 Application Development
    1. Chapter 13. Using DB2 in an Application Program
      1. Embedded SQL Basics
      2. Embedded SQL Guidelines
      3. Host Variables
      4. Programming with Cursors
      5. Modifying Data with Embedded SQL
      6. Application Development Guidelines
      7. Batch Programming Guidelines
      8. Online Programming Guidelines
      9. General SQL Coding Guidelines
      10. Introduction to Java
      11. Using REXX and DB2
      12. Developing Applications Using Only SQL
      13. Summary
    2. Chapter 14. Dynamic SQL Programming
      1. What Is Dynamic SQL?
      2. Dynamic SQL Versus Static SQL
      3. The Four Classes of Dynamic SQL
      4. pureQuery
      5. Making Dynamic SQL More Static and Vice Versa
      6. Dynamic SQL Guidelines
      7. Summary
    3. Chapter 15. Program Preparation
      1. Program Preparation Steps
      2. Running a DB2 Program
      3. Preparing a DB2 Program
      4. What Is a DBRM?
      5. What Is a Plan?
      6. What Is a Package?
      7. What Is a Collection?
      8. Versions
      9. Converting DBRM-Based Plans in DB2 V10
      10. Program Preparation Objects
      11. Program Preparation Guidelines
      12. Summary
    4. Chapter 16. Using DB2 Stored Procedures
      1. What Is a Stored Procedure?
      2. Implementing DB2 Stored Procedures
      3. Procedural SQL
      4. The Procedural DBA
      5. IBM Data Studio
      6. Summary
    5. Chapter 17. DB2 and the Internet
      1. The Internet Phenomenon
      2. Accessing DB2 over the Internet
      3. Finding DB2 Information Using the Internet
      4. Summary
  12. Part III. DB2 In-Depth
    1. Chapter 18. The Doors to DB2
      1. DB2 Program Execution Basics
      2. TSO (Time-Sharing Option)
      3. CICS (Customer Information Control System)
      4. IMS (Information Management System)
      5. CAF (Call Attach Facility)
      6. RRSAF (Recoverable Resource Manager Services Attach Facility)
      7. Comparison of the Environments
      8. Summary
    2. Chapter 19. Data Sharing
      1. Data Sharing Benefits
      2. Data Sharing Requirements
      3. The DB2 Coupling Facility
      4. Data Sharing Naming Conventions
      5. Data Sharing Administration
      6. Data Sharing Application Development Guidelines
      7. Data Sharing Administration Guidelines
      8. Summary
    3. Chapter 20. DB2 Behind the Scenes
      1. The Physical Storage of Data
      2. What Makes DB2 Tick
      3. Specialty Processors
      4. Summary
    4. Chapter 21. The Optimizer
      1. Physical Data Independence
      2. How the Optimizer Works
      3. Filter Factors
      4. Screening
      5. Access Path Strategies
      6. Other Operations Performed by the Optimizer
      7. Summary
    5. Chapter 22. The Table-Based Infrastructure of DB2
      1. The DB2 Catalog
      2. The DB2 Directory
      3. Summary
    6. Chapter 23. Locking DB2 Data
      1. How DB2 Manages Locking
      2. Locks Versus Latches
      3. Lock Duration
      4. Table Space Locks
      5. Table Locks
      6. Page Locks
      7. Row Locks
      8. Lock Suspensions, Timeouts, and Deadlocks
      9. Partition Independence
      10. Lock Avoidance
      11. Data Sharing Global Lock Management
      12. LOBs and Locking
      13. DB2 Locking Guidelines
      14. Other DB2 Components
      15. The Big Picture
      16. Summary
  13. Part IV. DB2 Performance Monitoring
    1. Defining DB2 Performance
    2. Types of DB2 Performance Monitoring
    3. Chapter 24. DB2 Performance Monitoring
      1. DB2 Traces
      2. Trace Destinations
      3. Using IFCIDs
      4. Tracing Guidelines
      5. Performance Monitoring and Reporting: Online and Batch
      6. Monitoring and Reporting Strategy
      7. Performance Profiles
      8. Viewing DB2 Console Messages
      9. Displaying the Status of DB2 Resources
      10. Monitoring z/OS
      11. Summary
    4. Chapter 25. Using EXPLAIN
      1. How EXPLAIN Works
      2. Access Paths and the PLAN_TABLE
      3. Cost Estimates and the DSN_STATEMNT_TABLE
      4. Function Resolution and the DSN_FUNCTION_TABLE
      5. Additional EXPLAIN Tables
      6. Explaining the Dynamic Statement Cache
      7. EXPLAIN Guidelines
      8. Additional Tools for Managing Access Paths
      9. Summary
    5. Chapter 26. The Five R’s
      1. Approaches to Rebinding
      2. A Best Practice Approach to Rebinding
      3. Summary
    6. Chapter 27. DB2 Object Monitoring Using the DB2 Catalog and RTS
      1. DB2 Catalog Queries
      2. Real Time Statistics
      3. Reviewing the Rules for an Effective Monitoring Strategy
  14. Part V. DB2 Performance Tuning
    1. Chapter 28. Tuning DB2’s Environment
      1. Tuning the z/OS Environment
      2. Tuning the Teleprocessing Environment
      3. Summary
    2. Chapter 29. Tuning DB2’s Components
      1. Tuning the DB2 Subsystem
      2. Tuning the Database Design
      3. Tuning the Application
      4. The Causes of DB2 Performance Problems
      5. Summary
    3. Chapter 30. DB2 Resource Governing
      1. The Resource Limit Facility
      2. Summary
  15. Part VI. DB2 Utilities and Commands
    1. Chapter 31. An Introduction to DB2 Utilities
      1. Generating Utility JCL
      2. Monitoring DB2 Utilities
      3. The IBM DB2 Utilities
      4. Using LISTDEF and TEMPLATE
      5. Issuing SQL Statements in DB2 Utilities
      6. Summary
    2. Chapter 32. Data Consistency Utilities
      1. The CHECK Utility
      2. The CHECK DATA Option
      3. The CHECK LOB Option
      4. The CHECK INDEX Option
      5. The REPAIR Utility
      6. The REPAIR DBD Option
      7. The REPAIR LOCATE Option
      8. The REPAIR SET Option
      9. REPAIR and Versions
      10. The REPORT Utility
      11. The DIAGNOSE Utility
      12. Summary
    3. Chapter 33. Backup and Recovery Utilities
      1. The COPY Utility
      2. The COPYTOCOPY Utility
      3. The MERGECOPY Utility
      4. The QUIESCE Utility
      5. The RECOVER Utility
      6. The REBUILD INDEX Utility
      7. The REPAIR Utility
      8. The REPORT RECOVERY Utility
      9. Backing Up and Restoring the System
      10. Summary
    4. Chapter 34. Data Movement and Organization Utilities
      1. The LOAD Utility
      2. The UNLOAD Utility
      3. The REORG Utility
      4. Summary
    5. Chapter 35. Catalog Manipulation Utilities
      1. The CATENFM Utility
      2. The CATMAINT Utility
      3. The DSNJCNVB Utility
      4. The MODIFY RECOVERY Utility
      5. The MODIFY STATISTICS Utility
      6. The RUNSTATS Utility
      7. The STOSPACE Utility
      8. Summary
    6. Chapter 36. Stand-Alone Utilities and Sample Programs
      1. The Stand-Alone Utilities
      2. DB2 Sample Programs
      3. Summary
    7. Chapter 37. DB2 Commands
      1. DB2 Environment Commands
      2. Information-Gathering Commands
      3. Administrative Commands
      4. Environment Control Commands
      5. DSN Commands
      6. IMS Commands
      7. CICS Commands
      8. TSO Commands
      9. IRLM Commands
      10. Summary
    8. Chapter 38. DB2 Utility and Command Guidelines
      1. Utility Guidelines
      2. The Pending States
      3. Summary
    9. Chapter 39. DB2 Contingency Planning
      1. What Is a Disaster?
      2. DB2 Recovery Basics
      3. Additional DB2 Disaster Recovery Technologies
      4. DB2 Environmental Considerations
      5. DB2 Contingency Planning Guidelines
      6. Summary
  16. Part VII. The Ideal DB2 Environment
    1. Chapter 40. Components of a Total DB2 Solution
      1. DB2 Tools
      2. DB2 Tools Vendors
      3. Summary
    2. Chapter 41. Organizational Issues
      1. Education
      2. Standards and Procedures
      3. Operational Support
      4. Political Issues
      5. Environmental Support
      6. Tool Requirements
      7. Summary
  17. Part VIII. Distributed DB2
    1. The Advantages of Data Distribution
    2. DB2 Data Distribution
    3. DB2 Data Warehousing
    4. Chapter 42. DRDA
      1. What Is DRDA?
      2. DRDA Functions
      3. DRDA Architectures and Standards
      4. The Five DRDA Levels
      5. Putting It All Together
      6. Summary
    5. Chapter 43. Distributed DB2
      1. Distributing Data Using DB2
      2. DB2 Support for the DRDA Levels
      3. Methods of Accessing Distributed Data
      4. Packages for Static SQL
      5. Two-Phase Commit
      6. Miscellaneous Distributed Topics
      7. Summary
    6. Chapter 44. DB2 Connect
      1. An Overview of IBM DB2 Connect
      2. Summary
    7. Chapter 45. Distribution Guidelines
      1. Distribution Behind the Scenes
      2. Block Fetch
      3. Dynamic Cursor Pre-Open
      4. Distributed Performance Problems
      5. Distributed Database Design Issues
      6. Distributed Data Placement
      7. Distributed Optimization
      8. Distributed Security Guidelines
      9. Miscellaneous Distributed Guidelines
      10. Summary
    8. Chapter 46. Data Warehousing with DB2
      1. Defining the Basic Terms
      2. Designing a Data Warehouse
      3. Populating a Data Warehouse
      4. Accessing the Data Warehouse
      5. Managing the Data Warehouse
      6. The Big Picture
      7. IBM Data Warehousing Solutions
      8. Materialized Query Tables
      9. General Data Warehouse Guidelines
      10. DB2-Specific Data Warehousing Guidelines
      11. Summary
  18. Index
  19. Common DB2 SQLCODE Values
  20. DB2 Manuals
    1. Standard Issue Manuals
    2. DB2 Redbooks

Product information

  • Title: DB2 Developer’s Guide: A Solutions-Oriented Approach to Learning the Foundation and Capabilities of DB2 for z/OS, Sixth Edition
  • Author(s):
  • Release date: April 2012
  • Publisher(s): IBM Press
  • ISBN: 9780132836470