You are previewing DB2 9 for z/OS: Database Administration Certification Study Guide.
O'Reilly logo
DB2 9 for z/OS: Database Administration Certification Study Guide

Book Description

In order to become an IBM Certified Database Administrator - DB2 9 DBA for z/OS, you must pass two exams: DB2 9 Fundamentals Exam (Exam 730), and DB2 9 Database Administrator for z/OS (Exam 732)—the primary focus focus of this book. Written by two members of the team who participated in the actual writing of the exam, this specialized study guide covers every topic that you will need to know to pass Exam 732, including database design and implementation, operation and recovery, security and auditing, performance, as well as installation and migration/upgrade. But that is only the beginning. It also covers the new features of DB2 9 for both database and application development. This comprehensive guide includes an extensive set of practice questions in each chapter that closely model the actual exam, along with an answer key with a description of why the answer is the correct one. No other source gives you this much help in passing the exam. Whether you plan to take Exam 732 or just want to master the skills needed to be an effective database administrator on z/OS systems, this is the only book you’ll need. With the DB2 9 for z/OS Database Administration Certification Study Guide, you will: • Discover the changes to DB2 9 that you’ll need to know in order to be successful when taking the exam • Learn how to effectively administer a DB2 database • Receive an explanation of every objective included on the test…by someone involved in the creation of the actual exam • Find 85 practice questions based on the actual exam’s format and approach, along with comprehensive answers to help you gain understanding Publisher’s Note: While this book covers much of the information needed to prepare for Exam 730, a far more in-depth review of topics specifically related to Exam 730 can be found in the MC Press companion book: DB2 9 Fundamentals Certification Study Guide by Roger E. Sanders.

Table of Contents

  1. Copyright
  2. Preface
    1. IBM DB2 Version 9.1 z/OS Certified Database Administrator
      1. 730 Exam Objectives
        1. Section 1: Planning (14%)
        2. Section 2: Security (11%)
        3. Section 3: Working with Databases and Database Objects (17%)
        4. Section 4: Working with DB2 Data Using SQL (23.5%)
        5. Section 5: Working with DB2 Tables, Views, and Indexes (23.5%)
        6. Section 6: Data Concurrency (11%)
      2. 732 Exam Objectives
        1. Section 1: Database Design and Implementation (26%)
        2. Section 2: Operation and Recovery (28%)
        3. Section 3: Security and Auditing (10%)
        4. Section 4: Performance (31%)
        5. Section 5: Installation and Migration/Upgrade (5%)
  3. 1. DB2 Product Fundamentals
    1. DB2 and the On Demand Business
    2. The DB2 Product Family
      1. DB2 for z/OS
      2. DB2 for i5/OS
      3. DB2 9 for Linux, UNIX, and Windows
        1. DB2 Express Edition
        2. DB2 Express-C Edition
        3. DB2 Workgroup Edition
        4. DB2 Enterprise Edition
        5. DB2 Everyplace Edition
        6. DB2 Personal Edition
        7. DB2 Enterprise Developer Edition
      4. DB2 Data Warehouse Edition
    3. DB2 Middleware and Connectivity
      1. DB2 Clients
        1. DB2 Runtime Client
        2. DB2 Client
      2. DB2 Connect
    4. DB2 Application Development
      1. DB2 Developer Workbench
      2. DB2 Extenders
      3. Query Management Facility
    5. DB2 Administration
      1. DB2 Client
      2. Visual Explain
      3. Optimization Service Center
      4. DB2 Control Center
        1. Health Center
        2. Design Advisor
        3. Replication Center
        4. Journal
        5. Task Center
        6. Activity Monitor
        7. Command Editor
    6. Summary
    7. Additional Resources
    8. Practice Questions
    9. Answers
  4. 2. Environment
    1. z/OS
    2. DB2 for z/OS
      1. Address Spaces
        1. Internal Resource Lock Manager Address Space
        2. System Services Address Space
        3. Database Services Address Space
        4. Distributed Data Facility Services Address Space
        5. Stored Procedure Address Space
        6. Allied Address Spaces
      2. Address Space Priority
      3. Attachments
        1. Customer Information Control System
        2. Information Management System
        3. Time Sharing Option
        4. Call Attachment Facility
        5. Recoverable Resource Services Attachment Facility
    3. DB2 and Security
    4. Parallel Sysplex Support
    5. Storage Management Subsystem
    6. DB2 Interfaces
      1. DB2 Interactive
      2. SQL Processing Using File Input
    7. Installation, Migration, and Conversion
      1. High-Level Overview
      2. DB2 9 Migration and Conversion
    8. DSNZPARMs
      1. Online DSNZPARMs
    9. Commands
      1. DSN Commands
      2. DB2 Commands
    10. DB2 Utilities
      1. Executing the Utilities
        1. DB2I
        2. DSNU CLIST Command
        3. Control Center
        4. DSNUTILS
        5. DSNUTILU
        6. Utility Templates
        7. Displaying Utilities
    11. Catalog and Directory
      1. DB2 Catalog
        1. Catalog Consistency Queries
      2. DB2 Directory
    12. Distributed Data
      1. DRDA
      2. Communications Protocols
        1. TCP/IP
        2. SNA
        3. VTAM
      3. Communications Database
    13. Subsystem Pools
      1. Buffer Pools
        1. Creating Buffer Pools
      2. EDM Pool
      3. RID Pool
      4. Sort Pool
    14. Summary
    15. Additional Resources
    16. Practice Questions
    17. Answers
  5. 3. Access and Security
    1. DB2 Subsystem Access
      1. Authorization Control with Exit Routines
      2. Local DB2 Access
      3. Remote Access
      4. IMS and CICS Security
      5. Kerberos Security
      6. Secure Sockets Layer Support
      7. Protection Against Denial-of-Service Attacks
    2. Data-Set Protection
    3. DB2 Object Access
      1. Authorization IDs
        1. Primary Authorization ID
        2. Secondary Authorization ID
        3. Role
        4. Current SQL ID
      2. Trusted Contexts
      3. Trusted Connections
      4. Roles
      5. Defining Trusted Contexts
      6. Performing Tasks on Behalf of Others
      7. Explicit Privileges
        1. GRANTing and REVOKEing Privileges
        2. Related and Inherited Privileges
      8. Authorities
        1. GRANTing and REVOKEing Authorities
        2. WITH GRANT OPTION
      9. Ownership
        1. Unqualified Objects
        2. Qualified Objects
        3. Objects Within a Trusted Context
        4. Privileges of Ownership by Object
        5. Plan or Package Ownership
        6. Unqualified Names
    4. Trusted Context
      1. Plan Execution Authorization
      2. Catalog Table Information for Object Access
      3. Controlling Access with Views
    5. Multilevel Security
      1. Security Function DB2_SECURE_VAR
    6. Auditing
      1. Audit Trace
        1. Trace Details
    7. Auditing Specific IDs or Roles
    8. Starting/Stopping the Trace
    9. Auditing a Table
    10. Summary
    11. Additional Resources
    12. Practice Questions
    13. Answers
  6. 4. Database Objects
    1. Understanding Data Structures
      1. Tables
      2. Aliases
      3. Synonyms
      4. Views
      5. Indexes
        1. Keys
      6. Table Spaces
      7. Index Spaces
      8. Databases
      9. Storage Groups
      10. Materialized Query Tables
      11. Clone Tables
      12. Sequence Objects
      13. Schemas
    2. Managing Database Objects
      1. The CREATE Statement
      2. The DECLARE Statement
      3. The DROP Statement
      4. The ALTER Statement
    3. Data Types
      1. DB2-Supplied Data Types
        1. Numeric Data Types
          1. Small Integer (SMALLINT)
          2. Integer (INTEGER)
          3. Big Integer (BIGINT)
          4. Decimal (DECIMAL/NUMERIC)
          5. Single-Precision Floating Point (REAL)
          6. Double-Precision Floating Point (DOUBLE/FLOAT)
          7. Decimal Floating Point (DECFLOAT)
        2. String Data Types
          1. Fixed-Length Character String (CHAR)
          2. Varying-Length Character String (VARCHAR)
          3. Character Large Object (CLOB)
          4. Double-Byte Character Strings (GRAPHIC)
          5. Double-Byte Character Large Objects (DBCLOB)
          6. Fixed-Length Binary Strings (BINARY)
          7. Varying-Length Binary Strings (VARBINARY)
          8. Binary Large Object (BLOB)
          9. Large Object Considerations
          10. String Encoding Schemes
        3. Date and Time Data Types
          1. Date String (DATE)
          2. Time String (TIME)
          3. Timestamp String (TIMESTAMP)
        4. ROWID
        5. XML
      2. User-Defined Data Types
      3. Null Values
        1. NOT NULL WITH DEFAULT
      4. Unicode Support
      5. Choosing the Correct Data Type
      6. Identity Columns
      7. Row Change Timestamp
    4. Tables
      1. Unique Constraints
      2. Referential Constraint
        1. INSERT Rules
        2. DELETE Rules
        3. UPDATE Rules
      3. Check Constraints
        1. Adding Check Constraints
        2. Modifying Check Constraints
      4. Creating Tables
      5. Auxiliary Tables
      6. Copying a Table Definition
      7. Modifying a Table
      8. Removing a Table
    5. Sequence Objects
      1. Creating Sequence Objects
      2. Using Sequence Objects
      3. Modifying Sequence Objects
      4. Removing Sequence Objects
    6. Table Spaces
      1. Simple Table Space
      2. Segmented Table Space
      3. Partitioned Table Space
        1. Adding Partitions
        2. Rotating Partitions
        3. Table-Controlled Partitioning
      4. Universal Table Spaces
      5. LOB Table Spaces
        1. XML Table Spaces
      6. Creating Table Spaces
        1. Page Sizes
        2. DSSIZE
        3. Free Space
        4. Allocation
        5. Compression
        6. Type of Table Space Created
        7. Modifying Table Spaces
      7. Removing Table Spaces
    7. Views
      1. Views with Check Option
      2. Nested View Definitions
      3. Read-Only Views
      4. Modifying a View
      5. Removing a View
    8. Materialized Query Tables
      1. Defining MQTs
        1. MQT Options
      2. Changing MQTs
    9. Clone Tables
    10. Indexes
      1. Deferring Physical Definition
      2. Index Compression
      3. Clustering Index
      4. Partitioning Index
      5. Unique Index and Non-Unique Index
      6. Unique Where Not Null Index
      7. Null Values and Indexes
      8. Expressions and Indexes
      9. Non-Partitioning Secondary Indexes
      10. Data Partitioned Secondary Indexes
        1. Drawbacks of DPSIs
      11. LOB Indexes
      12. XML Indexes
      13. General Indexing Guidelines
      14. Modifying an Index
      15. Removing an Index
      16. Renaming an Index
    11. Databases
      1. Creating a Database
      2. Modifying a Database
      3. Removing a Database
    12. Storage Groups
      1. Creating a Storage Group
      2. Modifying a Storage Group
      3. Removing a Storage Group
    13. Database Design and Implementation
      1. Logical Design
        1. Entities
        2. Relationships
        3. Attributes
        4. Normalization
          1. Normal Forms
        5. Anomalies
      2. Physical Design
        1. Transforming Logical to Physical
    14. Sample Implementation
      1. DB2 Sample Database Table Descriptions
        1. Defining Tables and Columns
        2. Keys
          1. Defining Primary Keys
          2. Defining Unique Keys
          3. Defining Foreign Keys
    15. Summary
    16. Additional Resources
    17. Practice Questions
    18. Answers
  7. 5. Retrieving and Manipulating Database Objects
    1. Data Retrieval
      1. The Major Components of a SELECT Statement
      2. Retrieving the Entire Table
      3. Projecting Columns from a Table
      4. Changing the Order of the Columns
      5. Restricting Rows from a Table
      6. Restricting Rows Using Multiple Conditions
      7. Selecting Columns from Multiple Tables
        1. Cartesian Products
        2. Joins
      8. Using Correlation Names
      9. Sorting Your Output
      10. Derived Columns
        1. Naming Derived/Calculated Columns
      11. Functions
        1. Scalar Functions
        2. Column Functions
      12. Grouping Values
      13. Restricting the Use of Sets of Data
      14. Eliminating Duplicates
      15. Searching for String Patterns
      16. Searching for Data in Ranges
      17. Searching for Null Values
      18. Searching for Negative Conditions
      19. Searching for a Set of Values
      20. Advanced Selection Functionality
    2. Data Modification
      1. Inserting Data Records
        1. Inserting Data into Specific Columns
        2. Inserting a Set of Values
        3. Inserting Large Amounts of Data
      2. Updating Data Records
        1. Updating Large Amounts of Data
      3. Merging Data
      4. Removing Data
        1. The DELETE Statement
        2. Deleting All the Rows in a Table
      5. View Classification
        1. Read-Only Views
        2. Non-Read-Only Views
    3. Summary
    4. Additional Resources
    5. Practice Questions
    6. Answers
  8. 6. Advanced SQL Coding
    1. Subqueries
    2. Unions
      1. UNION ALL in a View
    3. Excepts
    4. Intersects
    5. Nested Table Expressions
    6. Common Table Expressions
    7. Data Change Tables
    8. Joins
      1. Inner Joins
      2. Outer Joins
        1. Left Outer Join
        2. Right Outer Join
        3. Full Outer Join
        4. Combining Outer Joins
      3. Joins vs. Denormalization
    9. ORDER BY and FETCH FIRST in Subselects and Fullselects
    10. CASE Expressions
      1. Using CASE Expressions in Functions
    11. Row Expressions
    12. XPath and XQuery
      1. XPath Exressions
    13. Predicates and Filtering
    14. Summary
    15. Additional Resources
    16. Practice Questions
    17. Answers
  9. 7. Maintaining Data
    1. Data Movement
      1. Load Utility
        1. LOAD Phases
        2. Loading Data
        3. Add To or Replace
        4. Logging
        5. Loading Ordered Rows
        6. Loading Partitions
        7. Delimited Load
        8. Concurrent Access
        9. Referential Integrity
          1. ENFORCE NO
          2. Correcting Violations
        10. Loading ROWID Columns
        11. Free Space
        12. Inline Copies
        13. Inline Statistics
        14. SORTKEYS
        15. PREFORMAT
        16. Parallel Index Builds
        17. LOAD Parallelism for Partitioned Table Spaces
        18. Cursors
      2. Unloading Data
        1. DSNTIAUL
        2. UNLOAD Utility
        3. Delimited Unload
      3. Using REORG to Remove Data
    2. Data Maintenance
      1. Analyzing Data’s Physical Organization
      2. REORG Utility
      3. Reorganizing Table Spaces
        1. Segmented Table Space
        2. Partitioned Table Space
        3. LOB Table Space
      4. REORG Options
        1. SHRLEVEL
        2. SORTDATA
        3. Parallel Index Build
        4. Logging
      5. Online Reorganization
        1. Read-Only OLR
        2. FASTSWITCH
        3. Read/Write OLR
          1. Mapping Table
          2. Controlling Log Iterations
          3. Controlling Long Log Situations
          4. REORG DEADLINE
      6. Inline Statistics During REORG
      7. REORG DISCARD and REORG UNLOAD
      8. Reorganizing Indexes
      9. Triggering Reorganizations
        1. Catalog Queries
        2. REORG Triggers
        3. Table ALTERs
        4. Reorganizing Table Spaces
        5. Reorganizing LOB Table Spaces
        6. Reorganizing Indexes
        7. DBA Analysis for REORG Determination
      10. Reorganizing the Catalog and Directory
      11. Rebalancing Partitions
    3. Data Statistics
      1. RUNSTATS Utility
        1. Reporting and Performing Catalog Updates
        2. Access During RUNSTATS Execution
        3. Sampling
        4. Key Correlation Statistics
        5. Frequency Distribution Statistics
        6. Histogram Statistics
        7. RUNSTATS on the DB2 Catalog
        8. Inline Statistics
        9. SQL Cache Invalidation Using RUNSTATS
        10. Historical Statistics
      2. STOSPACE Utility
    4. Real Time Statistics
      1. Statistics Collection
      2. Externalizing and Using Realtime Statistics
    5. Data Maintenance Process
      1. Rebinds
      2. Modeling a Production Environment
      3. Other Data Maintenance Utilities
        1. CHECK Utilities
          1. CHECK DATA
          2. CHECK INDEX
          3. CHECK LOB
      4. MODIFY
        1. Modify Recovery
        2. Modify Statistics
      5. Repair
    6. Diagnose Utility
    7. Standalone Utilities
      1. DSNJLOGF (Preformat Active Log)
      2. DSNJU003 (Change Log Inventory)
      3. DSNJU004 (Print Log Map)
      4. DSN1CHKR
      5. DSN1COMP
      6. DSN1COPY
      7. DSN1LOGP
      8. DSN1PRNT
      9. DSN1SDMP (IFC Selective Dump)
    8. Displaying Utilities
    9. Resolving Restrictive and Advisory States
    10. Summary
    11. Additional Resources
    12. Practice Questions
    13. Answers
  10. 8. Recovery and Restart
    1. Database Recovery Concepts
      1. Unit of Work
    2. Logging
      1. Log Data Sets
      2. BSDS
      3. SYSIBM.SYSLGRNX
        1. Removing Rows from SYSLGRNX
    3. Image Copies
      1. Frequency of Image Copies
      2. Dual Image Copies
      3. COPYTOCOPY
      4. Tape vs. Disk Image Copies
      5. Full and Incremental Image Copies
      6. Copy of Partitions or Data Sets
        1. Multiple Copy Data Sets
      7. CHANGELIMIT
      8. Access During Image Copy Process
        1. Utility Mode
        2. SHRLEVEL
      9. Inline Copies
      10. DFSMS Concurrent Copy
      11. MERGECOPY Utility
      12. REPORT Utility
      13. Index Copy
      14. Copying the Catalog and Directory
    4. Establishing a Point of Consistency
    5. Recovery Concepts
      1. Table Space Recovery
      2. Index Recovery
      3. Other Object Recovery
      4. Multiple Object Recovery
      5. Recovery Using Database Copy Pool
      6. Recovery Involving NOT LOGGED Tables
        1. Recovering NOT LOGGED LOB or XML Table Spaces
      7. Fallback Recovery
      8. REPORT RECOVERY
      9. Using Fast Log Apply
      10. Point-in-Time Recovery
        1. Pending Conditions
      11. CHECK DATA
        1. Exception Tables
      12. Recover Pending
    6. System-Level Backup and Recovery
      1. Object-Level Recoveries with System-Level Backups
    7. Backup and Recovery of the DB2 Catalog and Directory
    8. Disaster Recovery
      1. Preparation
      2. Items Needed for Disaster Recovery
        1. Image Copies
        2. Archive Logs
        3. ICF
        4. DB2 Libraries
        5. Minimizing Data Loss
        6. Taking the Table Spaces Offline
    9. LOB Recovery
      1. Logging
      2. Point-in-Time Recovery
      3. AUXW Status
    10. Tracker Site Recovery
    11. DB2 Restart
      1. Stopping DB2
      2. Restarting DB2
        1. Conditional Restart
        2. Restart Implication with NOT LOGGED Tables
      3. Set Log Suspend/Resume
      4. Checkpoint Interval
      5. Minimizing Outages
      6. Postponed Units of Recovery
      7. Viewing Threads Affected by a Failure
        1. RESET INDOUBT
    12. Data-Sharing Recovery
    13. Summary
    14. Additional Resources
    15. Practice Questions
    16. Answers
  11. 9. Data Sharing
    1. Data Sharing Benefits
    2. Data Sharing Components
      1. Coupling Facility
        1. Coupling Facility Structures
          1. Shared Communications Area
          2. Lock Structure
          3. Group Buffer Pools
        2. Structures and Policies
          1. Coupling Facility Resource Management
          2. Sysplex Failure Management
          3. Automatic Restart Manager
      2. Links
      3. Sysplex Timer
      4. XES
      5. XCF
    3. Shared Data
      1. To Share or Not to Share
    4. Maintaining Data Integrity
      1. Locking
        1. Local Locks
        2. Global Locks
          1. P-Locks
          2. L-Locks
        3. Modify Locks
        4. Retained Locks
      2. Lock Contention
      3. Group Buffer Pools
        1. Sizing GBPs
        2. Castout
    5. Performance
    6. Processing Costs
    7. Movement to Data Sharing
      1. Application Analysis
      2. Current Environment Evaluation
      3. Migration Issues
        1. Catalog Merging
        2. Naming Conventions
    8. Workload Management and Affinity Processing
    9. Distributed Processing
    10. Sysplex Query Parallelism
    11. Recovery Considerations
      1. Logs and Recovery
        1. Recovery Scenarios
          1. DASD Failure
          2. DB2 Failure
          3. Coupling Facility Failure
          4. Structure Failures
          5. Structure Duplexing
    12. Summary
    13. Additional Resources
    14. Practice Questions
    15. Answers
  12. 10. Using SQL in an Application Program
    1. Delimiting SQL in a Program
    2. Declaring Table and View Definitions
      1. Host Variables and Host Structures
        1. Using Host Variables
        2. Using Host Structures
        3. DCLGEN
        4. Retrieving a Single Row of Data
        5. Retrieving Multiple Rows of Data
        6. Inserting and Updating Data
        7. Searching Data
    3. SQL Execution Validation
      1. SQLCODE and SQLSTATE
        1. SQLCODE
        2. SQLSTATE
        3. GET DIAGNOSTICS
    4. Using Cursors
      1. DECLARE Cursor
        1. Updating a Column
      2. OPENing the Cursor
    5. Executing SQL Statements
      1. FETCH Statements
        1. Positioned UPDATE
        2. Positioned DELETE
      2. CLOSEing the Cursor
      3. Cursor WITH HOLD
      4. Types of Cursors
        1. Scrollable and Non-Scrollable
        2. Using a Non-Scrollable Cursor
        3. Using a Scrollable Cursor
          1. Scrollable Cursor Fetching
      5. Dynamic Scrollable Cursors
    6. Using Dynamic SQL
      1. Coding Dynamic SQL in Applications
    7. Summary
    8. Additional Resources
    9. Practice Questions
    10. Answers
  13. 11. Binding an Application Program
    1. Precompile and Bind
      1. Precompile
      2. Binding
        1. Plans and Packages
          1. Plan-to-Package Ratio
        2. Advantages of Packages
        3. Collections
        4. Versioning
      3. Binding and Rebinding
        1. Binding or Rebinding a Package or Plan in Use
        2. Options for Binding and Rebinding
        3. Preliminary Steps
        4. Invalidations
        5. Rebinding a Package
        6. Package Lists
        7. Automatic Rebinding
      4. Migration Testing with Plans and Packages
      5. Removing a Plan or Package
    2. Plan or Package Ownership
      1. Unqualified Objects
    3. Plan Execution Authorization
    4. Summary
    5. Additional Resources
    6. Practice Questions
    7. Answers
  14. 12. Application Program Features
    1. Application Program Features
      1. Commit, Rollback, and Savepoint
        1. Commit and Rollback
        2. Unit of Work
        3. Unit of Recovery
        4. Rolling Back Work
      2. Savepoints
        1. Establishing a Savepoint
        2. Restoring to a Savepoint
        3. Releasing a Savepoint
        4. Savepoints in a Distributed Environment
      3. Global Transactions
      4. Global Temporary Tables
        1. Created Temporary Tables
          1. Creating a Temporary Table
            1. Determining How Often CTTs Are Materialized
        2. Declared Temporary Tables
          1. Usage Considerations
          2. Commit Options for DTTs
    2. Fetch for Limited Rows
      1. FETCH FIRST and OPTIMIZE FOR
      2. Using FETCH FIRST for Existence Checking
    3. Multi-Row Operations
      1. Multi-Row FETCH
        1. Multi-Row FETCH WITH DDF
      2. Multi-Row Insert
    4. Identity Columns
      1. INSERTs and UPDATEs
        1. Obtaining Generated Identity Columns Values
    5. Sequence Objects
      1. Programming with Sequence Objects
      2. NEXT VALUE (NEXTVAL)
        1. NEXTVAL and Cursors
      3. PREVIOUS VALUE (PREVVAL)
        1. PREVVAL and Cursors
      4. Populating Referential Relationships
      5. Comparing Identity Columns and Sequence Objects
      6. Merging
      7. Optimistic Locking
    6. Summary
    7. Additional Resources
    8. Practice Questions
    9. Answers
  15. 13. Stored Procedures
    1. Stored Procedures
    2. Benefits
    3. Writing Stored Procedures
      1. Language Environment
      2. Parameters
        1. Parameter Style
        2. DBINFO
      3. Result Sets
        1. Dynamically Identifying Result Sets
      4. Unit of Work
        1. COMMIT ON RETURN
        2. Issuing COMMITs
        3. Nesting
    4. Defining Stored Procedures
    5. Removing Stored Procedures
      1. Schema Qualification
    6. Execution Environments
      1. Workload Manager
        1. Stored Procedures and WLM
        2. Program Type
        3. Managing WLM Environments
        4. Obtaining Diagnostic Information
    7. SQL Procedure Language
      1. Developing SQL Stored Procedures
      2. External SQL Stored Procedures
    8. DB2 Developer Workbench
    9. Summary
    10. Additional Resources
    11. Practice Questions
    12. Answers
  16. 14. Accessing Distributed Data
    1. Distributed Data
      1. DRDA
      2. DB2 Private Protocol
    2. Communications Protocols
      1. System Network Architecture
      2. Virtual Terminal Access Method
      3. TCP/IP
    3. Communications Database
      1. Gathering Configuration Information
    4. Communicating with a Data-Sharing Group
      1. Member-Specific Routing
    5. Coding Methods for Distributed Data
      1. Three-Part Names
        1. Three-Part Names and Multiple Servers
        2. Accessing Declared Temporary Tables
      2. Using Explicit CONNECT Statements
        1. Releasing Connections
        2. DISCONNECT Bind Parameter
      3. Preparing Programs for DRDA Access
        1. Precompiler Options
        2. BIND PACKAGE Options
        3. BIND PLAN Options
      4. Coordinating Updates
        1. CICS and IMS
        2. TSO and Batch
        3. Without Two-Phase Commit
    6. Programming Considerations
    7. Application Design Options
      1. Stored Procedures
    8. Remote Query Performance
      1. OPTIMIZE FOR n ROWS
      2. Inactive Threads
      3. CMTSTAT
      4. Displaying Distributed Threads
      5. Distributed Application Tuning Guidelines
      6. Connection Pooling
        1. Configuration Settings
    9. Summary
    10. Additional Resources
    11. Practice Questions
    12. Answers
  17. 15. Advanced Functionality
    1. Triggers
      1. Trigger Uses
      2. Trigger Activation
      3. Creating Triggers
      4. After Trigger
      5. Before Trigger
      6. INSTEAD OF Triggers
      7. Row and Statement Triggers
        1. Row Triggers
        2. Statement Triggers
      8. Transition Variables and Tables
        1. Transition Variables
        2. Transition Tables
      9. Allowable Combinations
      10. Trigger Packages
      11. Trigger Invalidations
        1. SIGNAL SQLSTATE
        2. RAISE_ERROR
        3. Forcing a Rollback
      12. Performance Issues
      13. Monitoring Triggers
      14. Catalog Information
      15. Triggers vs. Table-Check Constraints
      16. Triggers and Declarative RI
      17. Performing Actions Outside a Database
      18. Triggers and UDFs
      19. Triggers and Stored Procedures
      20. Dropping Triggers
    2. Object-Relational Extensions
      1. Schemas
        1. Schema Names
        2. Schema Privileges
        3. PATH Bind Option
        4. CURRENT PATH Special Register
        5. CURRENT SCHEMA Special Register
      2. User-Defined Data Types
        1. Defining UDTs
        2. Casting
        3. Built-In Functions for UDTs
        4. Privileges
        5. Catalog Information
    3. User-Defined Functions
      1. External UDFs
      2. Sourced UDFs
      3. SQL Scalar UDFs
      4. Table Functions
      5. Invoking User-Defined Functions
      6. Polymorphism and UDFs
      7. External UDF Execution
      8. Monitoring and Controlling UDFs
      9. UDF Statistics
      10. Cost Information
      11. Catalog Information
    4. Large Objects
      1. LOB Data Types
      2. LOB Implementation
      3. INSERTing and LOADing LOBs
      4. LOBs and UDTs
    5. Extenders
      1. LOB and Extender Usage
      2. Enabling Extenders
      3. Text Extenders
        1. Indexing Text Extenders
        2. Frequency of Index Updates
        3. Catalog View for Text Extenders
      4. Image, Audio, and Video Extenders
      5. XML Extender
    6. Summary
    7. Additional Resources
    8. Practice Questions
    9. Answers
  18. 16. Locking and Concurrency
    1. Locking Data
      1. Lock Attributes
      2. Lock Sizes
        1. Table Space and Table Locks
        2. Partition or Universal Table Space Locking
        3. Page Locks
        4. Row Locks
        5. LOB Locks
        6. XML Locks
      3. LOCK TABLE Statement
      4. SKIP LOCKED DATA
      5. Lock Modes
        1. Table or Table Space Locks
        2. Page or Row Locks
      6. Lock Durations
        1. Table, Table Space, and Partition Lock Durations
        2. Page or Row Lock Durations
          1. Cursor Stability
          2. Read Stability
          3. Repeatable Read
          4. Uncommitted Read
          5. Share Lock Duration
          6. Update Lock Duration
          7. Exclusive Lock Duration
    2. Avoiding Locks
      1. Determining Whether Lock Avoidance Is Used
    3. System Parameters
    4. Claims and Drains
      1. Claims
      2. Drains
    5. Locking Issues and Problems
      1. Timeouts and Deadlocks
        1. Coding Retry Logic for Locking Errors
    6. Lock Promotion and Escalation
      1. Lock Escalation
      2. Lock Promotion
    7. Database and Application Design for Concurrency
      1. Database Design
      2. Application Design
    8. Lock Monitoring
      1. Explain
      2. Accounting and Statistics Reports
      3. Displaying Locks
      4. Traces
    9. Summary
    10. Additional Resources
    11. Practice Questions
    12. Answers
  19. 17. Performance Monitoring and Tuning
    1. Access Paths and Optimization
      1. Explain
        1. Gathering Explain Data
        2. Examining Explain Data
    2. Access Path Evaluation
      1. Index Access
        1. Index Access (ACCESSTYPE is I, I1, N, MX, or DX)
        2. Multiple Index Access (ACCESSTYPE = M)
        3. Number of Matching Index (MATCHCOLS = n)
        4. Index-Only Access (INDEXONLY = Y)
      2. Table Access
        1. Table Space Scan (ACCESSTYPE = R, PREFETCH = S)
        2. Prefetching (PREFETCH = D, L, S, or Blank)
        3. Limited Partition Scanning (PAGE_RANGE = Y)
        4. SORT (SORTN_xxx and SORTC_yyy)
        5. Nested Loop Join (METHOD = 1)
        6. Nested Loop Join with Sparse Index (METHOD = 1, PRIMARY_ACCESSTYPE = T)
        7. Merge Scan Join (METHOD = 2)
        8. Hybrid Join (METHOD = 4)
        9. Star Join (METHOD = 0, 1; JOIN TYPE = S)
        10. Parallelism Usage (PARALLELISM_MODE = I, C, or X)
        11. Interpreting Access for Subqueries
      3. Optional Tables Populated by EXPLAIN
        1. DSN_STATEMNT_TABLE
        2. DSN_FUNCTION_TABLE
        3. DSN_STATEMENT_CACHE_TABLE
        4. DSN_PREDICAT_TABLE
        5. DSN_STRUCT_TABLE
        6. DSN_PGROUP_TABLE
        7. DSN_PTASK_TABLE
        8. DSN_FILTER_TABLE
        9. DSN_DETCOST_TABLE
        10. DSN_SORT_TABLE
        11. DSN_SORTKEY_TABLE
        12. DSN_PGRANGE_TABLE
        13. DSN_VIEWREF_TABLE
        14. DSN_QUERY_TABLE
      4. Explain Table Querying and the Optimization Service Center
    3. Guidelines for Using Explain Output
      1. Verifying Index Use
      2. Checking Application Access
    4. Access Path Optimization Hints
    5. Catalog Statistics
      1. Filter Factors and Catalog Statistics
      2. Histogram Statistics
      3. Statistics for Partitioned Table Spaces
      4. Using Statistics to Model a Production Environment
    6. Predicate Types
      1. Indexable Predicates
      2. Stage 1 Predicates
      3. Stage 2 Predicates
    7. Designing Indexes and SQL for Performance
      1. Indexes for Efficient Access
        1. Matching Index Scan (MATCHCOLS > 0)
        2. Index Screening
        3. Non-Matching Index Scan (ACCESSTYPE = I and MATCHCOLS = 0)
        4. IN-List Index Scan (ACCESSTYPE = N)
        5. Multiple Index Access (ACCESSTYPE is M, MX, MI, MU, DX, DI, or DU)
        6. One-Fetch Access (ACCESSTYPE = I1)
        7. Index-Only Access (INDEXONLY = Y)
        8. Equal Unique Index (MATCHCOLS = Number of Index Columns)
      2. Using Indexes to Help Avoid Sorts
    8. Dynamic SQL
    9. Runtime Reoptimization
      1. Runtime Reoptimization for Static SQL
      2. Runtime Reoptimization for Dynamic SQL
        1. BIND Parameters for Runtime Reoptimization of Dynamic SQL
        2. Setting Dynamic SQL Reoptimization at the Subsystem Level
    10. Query Parallelism
      1. I/O and CPU Parallelism (PARALLELISM_MODE = I or C)
      2. Queries Best-Suited for Parallelism
        1. Sysplex Query Parallelism (PARALLELISM_MODE = X)
    11. Elements of Performance
      1. Tuning Guidelines
      2. Performance Improvement Process
      3. How Much Can You Tune a System?
      4. A Less Formal Approach
    12. Database Monitoring
      1. Online and Batch Performance Monitors
      2. Resource Limit Facility
    13. Tracing Problems in DB2
      1. DB2 Traces
        1. Statistics
        2. Accounting
        3. Performance
        4. Audit
        5. Monitor
      2. Invoking Traces
        1. Trace Classes
      3. IFCIDs
      4. Continuous Performance Monitoring
      5. Periodic Performance Monitoring
      6. Detailed Performance Monitoring
      7. Exception Performance Monitoring
    14. Using DISPLAY Commands
    15. Buffer Pools
      1. Pages
      2. Virtual Buffer Pools
      3. Buffer Pool Queue Management
      4. I/O Requests and Externalization
      5. Checkpoints and Page Externalization
        1. Sizing
        2. Sequential vs. Random Processing
        3. Writes
        4. Parallelism
        5. Stealing Method
        6. Page Fixing
      6. Internal Thresholds
        1. Sequential Prefetch Threshold
        2. Data Manager Threshold
        3. Immediate Write Threshold
      7. Virtual Pool Design Strategies
      8. Tuning with the –DISPLAY BUFFER POOL Command
      9. RID Pool
        1. Sizing
        2. Statistics to Monitor
          1. RIDs Over the RDS Limit
          2. RIDS Over the DM Limit
          3. Insufficient Pool Size
      10. The Sort Pool
        1. Sort Pool Size
      11. The EDM Pool
        1. EDM Pool Size
        2. Efficiency
        3. Dynamic SQL Caching
    16. Logging
      1. Log Reads
      2. Log Writes
    17. Summary
    18. Additional Resources
    19. Practice Questions
    20. Answers
  20. A. DB2 Sample Database
  21. B. Sample Exam Questions
    1. Practice Questions
  22. C. Sample Exam Answers
  23. D. References