IBM CV881G - Db2 13 for z/OS Advanced Database Administration

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

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

•   ...

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 

Audience

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