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
Präsenztraining
