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

•    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