IBM Data and AI

CV881G : Db2 13 for z/OS Advanced Database Administration

Course Duration

2 Days

Delivery

ILT/VILT

Overview

Overview Course Curriculum Training Schedule Exam & Certification FAQs

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.

This course does not cover distributed data processing or data sharing.

What You'll Learn

Program Preparation and the Use of Packages - Part 1
Online Schema Changes - Part 1
Online Schema Changes - Part 2
Partition Management - Part 1
Partition Management - Part 2
Stored Procedures - Part 1
LOAD and REBUILD INDEX
REORG
UNLOAD
(Optional) Unit 10: Program Preparation and the Use of Packages - Part 2
(Optional) Unit 11: Stored Procedures - Part 2

Who Should Attend

Administrators familiar with Db2 who wish to augment their skill set in administering Db2 13 for z/OS database environment.

Prerequisites

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

Learning Journey

Coming Soon...

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

Frequently Asked Questions (FAQs)

Why get IBM certified?

IBM certifications validate your skills and expertise in a wide range of technologies, including hybrid cloud, data & AI, security, IT infrastructure, and business applications.
These certifications are globally recognized and can help you advance your career, increase your earning potential, and demonstrate your commitment to professional development.
IBM-certified professionals are in high demand, making them valuable assets to any organization seeking to leverage IBM's innovative solutions.
What to expect for the examination?

IBM offers a variety of certification exams at different levels (Basic, Intermediate, and Advanced) covering various technologies and job roles.
Exams typically consist of multiple-choice questions and may include scenario-based questions that assess your ability to apply your knowledge in real-world situations.
Note: Certification requirements and policies may be updated by IBM from time to time. We apologize for any discrepancies; do get in touch with us if you have any questions.
How long is IBM certification valid for?

The validity period of IBM certifications varies. Some certifications, particularly those focused on specific product versions or technologies, may have expiration dates.
However, many IBM certifications, especially those focused on broader skills or newer technologies, do not have an expiration date.
Note: Certification requirements and policies may be updated by IBM from time to time. We apologize for any discrepancies; do get in touch with us if you have any questions.
Why take this course with Trainocate?

Here’s what sets us apart:
- Global Reach, Localized Accessibility: Benefit from our geographically diverse training hubs in 24 countries (and counting!).
- Top-Rated Instructors: Our team of subject matter experts (with high average CSAT and MTM scores) are passionate to help you accelerate your digital transformation.
- Customized Training Solutions: Choose from on-site, virtual classrooms, or self-paced learning to fit your organization and individual needs.
- Experiential Learning: Dive into interactive training with our curated lesson plans. Participate in hands-on labs, solve real-world challenges, and take on comprehensive assessments.
- Learn From The Best: With 30+ authorized training partnerships and countless awards from Microsoft, AWS, Google – you're guaranteed learning from the industry's elite.
- Your Bridge To Success: We provide up-to-date course materials, helpful exam guides, and dedicated support to validate your expertise and elevate your career.

Keep Exploring

Course Curriculum

Training Schedule

Exam & Certification

FAQs

Frequently Asked Questions

Improve yourself and your career by taking this course.

Enroll Now

More Courses By IBM

Browse All Courses

System Z

CV881G : Db2 13 for z/OS Advanced Database Administration

What You'll Learn

Who Should Attend

Prerequisites

Learning Journey

Frequently Asked Questions (FAQs)

Why get IBM certified?

What to expect for the examination?

How long is IBM certification valid for?

Why take this course with Trainocate?

Keep Exploring

Course Curriculum

Training Schedule

Exam & Certification

Frequently Asked Questions

Improve yourself and your career by taking this course.

More Courses By IBM

Advanced Parallel Sysplex Operations and Recovery Workshop

IBM Cognos Analytics: Author Reports Fundamentals v11.1.x

IBM z/OS Management Facility Implementation and Use 2.1

Ready to Take Your Business from Great to Awesome?

Request More Information