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