Overview

This course introduces advanced database administration skills, including program preparation and the use of packages, online schema changes, partition management, and stored procedures. Performance and availability features of utilities, including LOAD, REBUILD INDEX, REORG, and UNLOAD are covered. 

This course does not cover distributed data processing or data sharing.

Audience

Administrators familiar with Db2 who wish to augment their skill set in administering Db2 13 for z/OS database environment.

Prerequisites

Before taking this course, you should have: 

• Familiarity with the z/OS operating system, including TSO, ISPF, and SDSF
• Familiarity with SQL DML (SELECT, INSERT, UPDATE, and MERGE, as well as DDL statements (CREATE, ALTER, DROP)
• Basic skills administering a Db2 13 for z/OS database environment, including use of basic utilities, serialization, and basic database security 

Objective

Unit 1: Program Preparation and the Use of Packages - Part 1

•    Describe the significance of the Db2 catalog and directory in the program preparation process and how they interact with Db2 applications

•    Explain the role and components of DCLGEN in program preparation

•    Describe the precompilation process

•    Demonstrate the BIND process

•    Identify common execution time errors in Db2 applications and explain how to troubleshoot and resolve them

•    Demonstrate CREATE, REPLACE, and REBIND packages in Db2 

Unit 2: Online Schema Changes - Part 1

•    Explain the different types of valid and invalid data type changes for Db2 tables

•    Describe the impact of altering a column’s data type and steps to mitigate performance degradation

•    Explain the concept of schema versioning in Db2 and the limitations on the number of active versions

•    Demonstrate how to modify indexes by adding columns and describe the restrictions and states *AREO* and RDBP) resulting from them

•    Use REORG and MODIFY RECOVERY to minimize the active versions in Db2 tables and indexes 

Unit 3: Online Schema Changes - Part 2

•    Explain how to view pending schema changes and the role SYSIBM.SYSPENDINGDDL catalog table plays in tracking these changes

•    Explain how to drop columns from a table, the necessary conditions, and the impact on table availability

•    List the steps in converting a classic-partitioned table space to a partition-by-range (PBR) table space

•    Explain the restrictions and considerations of pending changes on Db2 operations, including CREATE INDEX, CREATE TABLE, and DROP TABLE 

Unit 4: Partition Management - Part 1

•    Explain table-controlled partitioning and how to add new partitions using the ALTER TABLE statement

•    Define the ROTATE PARTITION statement and explain its syntax, including the RESET option

•    Execute the ALTER TABLE ROTATE n TO LAST statement and explain how to determine the physical and logical partition numbers

•    Interpret the output of the DISPLAY DATABASE command after partition rotations

•    Perform a ROTATE PARTITION operation and describe the necessary conditions and constraints to ensure data integrity and performance 

Unit 5: Partition Management - Part 2

•    Modify partition boundary keys using the ALTER TABLE … ALTER PARTITION … ENDING AT statement

•    Explain rebalancing partitions in a table-controlled partitioning and how to do it

•    Execute a REORG TABLESPACE … REBALANCE operation 

Unit 6: Stored Procedures - Part 1

•    Describe the purpose and benefits of stored procedures in reducing network traffic and facilitating versatile client/server environments

•    Differentiate between external, native SQL and external SQL stored procedures and their execution environments

•    Create a stored procedure using the CREATE PROCEDURE statement

•    Use the ALTER PROCEDURE and DROP PROCEDURE statements

•    Manage stored procedure execution using the START, STOP, and DISPLAY PROCEDURE commands 

Unit 7: LOAD and REBUILD INDEX

•    Describe the process of running a LOAD utility without parallel index build

•    Explain how to use parallel index build during the LOAD utility and the role of SORTKEYS

•    Perform the REBUILD INDEX utility and explain the SCOPE PENDING option

•    Utilize dynamic allocation for sort work data sets during the REBUILD INDEX process 

Unit 8: REORG

•    Describe the basic flow of the REORG TABLESPACESHRLEVEL NONE process

•    Explain the REORG process with the NOSYSREC option

•    Execute a REORG operation with SHRLEVEL CHANGE, including the LOG phase and management of changes

•    Utilize the SCOPE PENDING option in the REORG TABLESPACE utility

•    Implement the FORCE option in the REORG utility to handle blocking claimers and ensure successful completion 

Unit 9: UNLOAD

•    Describe the purpose and capabilities of the UNLOAD utility

•    Construct an UNLOAD utility control statement to unload data from a specific table space or table using the HEADER, SAMPLE, and LIMIT options

•    Execute the UNLOAD utility in parallel mode for partitioned table spaces

•    Unload data from partitioned and non-partitioned tables spaces into a single data set or multiple data sets 

(Optional) Unit 10: Program Preparation and the Use of Packages - Part 2

•    Demonstrate table mirroring and implement the BIND PACKAGE command to create packages with different qualifiers for identical table structures

•    Diagnose and resolve issues by setting the CURRENT PACKAGESET special register

•    Create and manage multiple versions of a package using the VERSION option in the BIND PACKAGE command

•    Configure plan management settings using the PLANMGMT option in the REBIND PACKAGE command

•    Implement the APRETAINDUP and APCOMPARE options in the REBIND PACKAGE command 

(Optional) Unit 11: Stored Procedures - Part 2

•    Create a native SQL stored procedure using the CREATE PROCEDURE statement

•    Use the VERSION keyword in the CREATE PROCEDURE statement to develop multiple versions of a native SQL stored procedure

•    Implement a versioning strategy for SQL stored procedures

•    Deploy SQL stored procedures across different environments using the BIND PACKAGE command with the DEPLOY option 

Course Outline

Unit 1: Program Preparation and the Use of Packages - Part 1

Unit 2: Online Schema Changes - Part 1

Unit 3: Online Schema Changes - Part 2

Unit 4: Partition Management - Part 1

Unit 5: Partition Management - Part 2

Unit 6: Stored Procedures - Part 1

Unit 7: LOAD and REBUILD INDEX

Unit 8: REORG

Unit 9: UNLOAD

(Optional) Unit 10: Program Preparation and the Use of Packages - Part 2

(Optional) Unit 11: Stored Procedures - Part 2