Target Audience
Administrators familiar with Db2 who wish to augment their skill set in administering Db2 13 for z/OS database environment.
Course Objectives
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 Content
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
Course 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