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
Duration
2 Days.
Course Objectives
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.
Course Content
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