Skip to main content

Course description: DB2 9 for z/OS Database Administration Workshop Part 2

Interested in bringing a class to you? Onsite training

Overview

List of course details in a data table
Course code CV840 Skill level Intermediate
Duration 5.0 days Delivery type Classroom
(Hands-on labs)
Course type Public or Private on-site    
Public price USD $3,250.00 plus tax  

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.

View this course in other countries


Training Paths that reference this course are:

Back to top

Special note

IBM Education Advantage Program Eligibility:

  • Yes - IBM Education Pack - online account

Back to top

Audience

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.

Back to top

Prerequisites

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

Back to top

Skills taught

Upon completion of the course, the student should be able to:

  • Execute program preparation steps including resolving common problems, defining collections and packages, and versioning of packages
  • Alter table characteristics such as data types of columns and identify the impact of making such changes
  • Alter indexes to add columns or to change the varying length or clustering characteristics of the index, and identify the impact of making such changes
  • React to the "Advisory Reorg" Database Exception Table state
  • Make appropriate decisions to successfully handle table space versions
  • Use parallel index build with the LOAD, REORG and REBUILD INDEX utilities
  • Load partitions in parallel
  • Determine the appropriate LOAD options to use while considering concurrency and performance requirements
  • Choose appropriate options with REORG and UNLOAD to achieve optimal performance and availability
  • Given the usage of the data, determine an appropriate option for dealing with the need for adding partitions or rebalancing data partitions
  • Provide appropriate LISTDEF, TEMPLATE, and OPTIONS utility control statements for use in DB2 utilities
  • Determine the applicability and implement user-defined distinct types, user-defined functions, stored procedures, triggers, and large objects

Back to top

Course outline

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

Triggers

  • 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

Agenda

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

Back to top

My IBM

We're here to help

Easy ways to get the answers you need.

or call us at
Call 1-800-426-8322

Open M-F 9AM-7PM ET.

Student center

Find the right course

Offers and more