DB2 9 for z/OS Database Administration Workshop Part 2 (CV840)

This course is the classroom delivered version of 3V840.

This course is a continuation of course CV830 and is designed to teach you how to perform additional database administration tasks. CV840 also covers several of the topics introduced in CV830 in much more depth.

Skills Gained

Program preparation and use of packages

  • Go through the program preparation steps and execute programs in the DB2 environment using the Time Sharing Option (TSO) attachment facility
  • Resolve some of the most commonly occurring problems, for example SQL error -805
  • Understand the importance of collections and packages
  • Understand how to access different tables and views at execution time without changing the program
  • Handle mirror tables
  • Understand package versioning

Online schema changes

  • Change data types and lengths of columns
  • Alter or add an index to have true VARCHAR columns
  • Unbundle partitioning and clustering
  • Relate implications of renaming a column in a table or renaming an index

LOAD and REBUILD INDEX performance and availability

  • Build indexes in parallel during LOAD and REBUILD INDEX
  • Establish inline statistics during LOAD and REBUILD INDEX
  • Load partitions of a partitioned table space in parallel
  • Use the availability feature for LOAD RESUME YES: SHRLEVEL CHANGE
  • Use the availability feature for CHECK DATA: SHRLEVEL CHANGE
  • Use the availability feature for REBUILD INDEX: SHRLEVEL CHANGE
  • Use online LOAD REPLACE functionality provided by clone tables

REORG and UNLOAD performance and availability

  • Apply performance features for REORG
  • Build indexes in parallel during REORG
  • Use the availability features for REORG: Read-only online REORG: SHRLEVEL REFERENCE, and Full online REORG: SHRLEVEL CHANGE
  • Establish inline statistics during REORG
  • Use the performance and availability features for UNLOAD

Partition management

  • Add partitions
  • Rotate partitions
  • Rebalance partitions

Generic utility jobs

  • Provide reusable list definitions for use by DB2 utilities
  • Use list definitions in utility control statements and understand how they expand
  • Describe the concept and working of templates replacing utility Dataset Definition (DD) statements
  • Use templates in utility jobs to simplify Job Control Language (JCL)
  • Use templates together with list definitions
  • Use the OPTIONS utility control statement to control execution parameters for utility jobs

UDTs, UDFs, and stored procedures

  • Identify the need for, define, and use schemas
  • Use schemas in the PATH bind option
  • Identify the need for, define, and use user-defined distinct types
  • Differentiate between external scalar functions, external table functions, SQL scalar functions, and sourced functions
  • Determine which user-defined function or stored procedure will be invoked given the invocation statement and existing functions or stored procedures
  • Identify privileges associated with schemas, user-defined distinct types, and stored procedures
  • Differentiate between the capabilities of user-defined functions and stored procedures
  • Create a user-defined function
  • Create a stored procedure
  • Explain SQL PL versioning and deployment


  • Identify benefits of triggers
  • List characteristics of and functions that can be performed by BEFORE, AFTER and INSTEAD OF triggers
  • Specify trigger granularity
  • Create a trigger
  • Use transition variables and transition tables within triggers
  • Identify privileges associated with trigger definition
  • Describe the trigger package

Large objects

  • Identify the data types of large objects
  • Describe auxiliary table
  • Explain the relationship between the base table and the auxiliary table
  • Define large objects
  • Identify the use of ROWIDs with large objects
  • List alternative ways to process large objects within applications
  • Evaluate locking considerations when processing large objects
  • Identify considerations when loading tables with large objects

Performance-related topics

  • Relate why performance is important
  • Implement tables in a performance-optimal way


Day 1

  • Welcome
  • Unit 1 - Transition from CV830 to CV840
  • Unit 2 - Program Preparation and Use of Packages
  • Unit 2 - Exercise
  • Unit 3 - Online Schema Changes
  • Unit 3 - Exercise

Day 2

  • Unit 4 - LOAD and REBUILD INDEX Performance and Availability
  • Unit 4 - Exercise
  • Day 3
  • Unit 5 - REORG and UNLOAD Performance and Availability
  • Unit 5 - Exercise
  • Unit 6 - Partition Management

Day 4

  • Unit 6 - Exercise
  • Unit 7 - Generic Utility Jobs
  • Unit 7 - Exercise
  • Unit 8 - UDTs, UDFs, and Stored Procedures

Day 5

  • Unit 8 - Exercise
  • Unit 9 - Triggers
  • Unit 9 - Exercise
  • Unit 10 - Large Objects
  • Unit 10 - Exercise
  • Unit 11 - Performance-Related Topics

Who Can Benefit

This is an intermediate course for z/OS database administrators who have the basic skills required to administer a DB2 database and want to extend their skills.


You should have attended course DB2 9 for z/OS Database Administration Workshop Part 1 (CV830), or have attained a similar level of experience.

Sales: 1-888-422-9786
Send Email
Request This Class!
5 Days
Instructor Led
Tuition (USD)

Search Other Training Options

Dont Miss Anything

We're more than a training reseller. We can schedule our award winning trainers to teach this course on your terms.

Schedule your course:

Red Hat
Oracle Corporation
IBM Corporation
Microsoft Corporation
General Training
Symantec Corporation