Home About Courses Schedule Services Webinars Contact Search

Db2 for z/OS Extended Features and Complex Object Administration


Duration: 3 Days

Method: Instructor Led, Hands-on Workshops

Price: $1920.00

Course Code: DB3020


Db2 Application and System Database Administrators who will be designing and managing Db2 Databases.


Beyond the base table, Db2 supports the creation and management of a wide variety of special purpose object types that can promote the performance and usability of your base tables. These extended features of Db2 are vitally important to the success of your Database design and implementation. Use of the extended features will directly impact the administration of your database. We cover the Creation, usage and management of these “Extended Features” and complex objects.


Upon successful completion of this course, the student will be able to:

  • Understand the Extended Features available and make informed choices
  • Implement Performance related objects
  • Implement Usability related objects
  • Create and manage user defined functions and complex data types
  • Manage stored procedures
  • Create and manage temporal, clone and hash tables
  • Update Recovery procedures to include extended table types
  • Implement profiles and extended security objects


Db2 for z/OS Database Administration (DB3010) or equivalent knowledge and prerequisites which includes Introduction to Db2 & SQL coding basics (DB1000) and TSO/ISPF, MVS JCL (MF1010).


  • I. Introduction
    • Base Table Dependencies
    • Db2 Advanced Object Types
    • Extended Features Dependencies
    • Db2 Object Naming Conventions
    • Schema Resolution
    • Where to Start
  • II. Event Based Extensions, Triggers
    • Trigger Purposes
    • Creating Triggers
    • Trigger Packages
    • Trigger Cascading
    • Performance Considerations
  • III. Application Performance Extensions
    • Table Constraints vs. Application Code
    • Views for Usability and Performance
    • Global Temporary Tables
      • Types of GTTs
      • Declared GTTs
      • Created GTTS
      • Recommendations
    • Materialized Query Tables (MQTs)
      • Used for Performance Tuning
      • Creation
      • Implementation choices
      • Recommendations
  • IV. Extensions to the SQL Language
    • Sequence
    • User Defined Data Types
    • User Defined Functions
    • Global Variables
    • Stored Procedures
    • Recommendations
  • V. Extended Table Types
    • Clone Table
    • Temporal Tables
    • Archive Enabled Tables
    • Hash Tables
    • Recommendations
  • VI. LOBs and XML Data Types
    • LOBs
    • XML
    • Creation and Management
    • Recovery Considerations
  • VII. Profiles
    • Monitoring Connections & Threads
    • Setting Special Registers
    • Setting Subsystem Parameters for SQL
    • Modeling Subsystem Properties
    • Set thresholds for query acceleration
  • VIII. Security Related Objects
    • Mask
    • Permissions
    • Role
    • Trusted Context