DB2 9 for z/OS Database Administration Workshop Part 1

Course Description

This course is the classroom delivered version of 3V830.

This course provides you with instruction on how to physically implement a logical database design in DB2. The course includes instruction on DB2 data management, DB2 catalog tables, the bind process, database utilities such as LOAD and REORG, and security considerations.

Note: This course material is at the DB2 9 for z/OS level.

Skills Gained

Introduction

• Describe what DB2 for z/OS is at an overview level
• Understand the fundamental differences between static and dynamic SQL programs
• Define the major responsibilities of any application program
• Define the major responsibilities of the DB2 system
• Understand the basic principles of DB2 Recovery
• Discuss the basic roles people will play in a DB2 shop

Setting up a DB2 database

• Describe the DB2 objects that make up a DB2 database
• Select parameters for these DB2 database objects so that they are implemented with the most appropriate attributes
• Create storage groups, databases, table spaces, tables, views, synonyms, aliases, materialized query tables, and indexes
• Alter the attributes of DB2 database objects as requirements change over time
• Describe how data is stored in a DB2 database

Implementing unique and referential requirements

• Describe the concepts of PRIMARY KEY, UNIQUE KEY, and FOREIGN KEY
• Implement these keys in DB2
• Implement REFERENTIAL CONSTRAINTS on new or existing tables with the appropriate DELETE rules
• Discuss identity columns and sequence objects

Getting data into and out of DB2

• Describe how DB2 utilities may be executed, monitored, terminated, and restarted
• Run the DB2 LOAD utility and make full use of its many features
• Run the CHECK DATA utility to check DB2 table space data for any violations of referential integrity constraints or table check constraints
• Run the DB2 UNLOAD utility, and make full use of its many features

Keeping your DB2 data in good shape

• Describe and interpret the statistics collected by RUNSTATS and real-time statistics
• Run RUNSTATS with an appropriately chosen set of options
• Describe why it is necessary to periodically REORG your table spaces and indexes
• Run REORG with an appropriately chosen set of options

Application data recovery basics

• Make DB2 user data available again following media failures.

Program preparation / bind

• Describe the main steps needed to prepare a program with embedded static SQL to access DB2.

Security

• Describe DB2 global security
• Describe the various DB2 authids and how they are established
• Understand how DB2 security controls access to data
• Describe and implement DB2 privileges and authorities appropriately
• Describe how ownership is established in DB2
• Describe DB2 authorization checking
• Describe and implement security for plans and packages
• Describe multilevel security
• Describe the advantages and considerations for using the RACF / DB2 external security module

Serialization

• Describe concurrency
• Explain the aspects of transaction locks
• Tell when locks are acquired and released
• Describe S and X locks
• Explain isolation levels
• Explain LOCKSIZE

Agenda

Day 1

• Welcome
• Introduction
• Unit 2 (topic 1) - Overview of setting up a DB2 database
• Unit 2 (topic 2) - Storage groups, databases, and table spaces
• Lab 1
• Lab 1 review
• Unit 2 (topic 3) - Tables
• Lab 2
• Lab 2 review

Day 2

• Unit 2 (topic 5) - Views, synonyms, and aliases
• Lab 3
• Lab 3 review
• Unit 2 (topic 6) - Materialized Query Tables
• Unit 2 (topic 7) - Indexes
• Lab 4
• Lab 4 review
• Unit 2 (topic 8) - Additional information

Day 3 Unit 3 - Referential integrity

• Lab 5
• Lab 5 review
• Unit 3 - Identity columns and sequences
• Unit 4 - Getting data into and out of DB2
• Lab 6

Day 4

• Lab 6 review
• Unit 5 - Keeping your DB2 data in good shape
• Lab 7
• Lab 7 review
• Unit 6 - Application data recovery basics
• Lab 8
• Lab 8 review

Day 5

• Unit 7 - Program preparation
• Unit 8 - Security
• Lab 9
• Lab 9 review
• Unit 9 - Serialization
• Lab 10
• Lab 10 review

Who Can Benefit

This is an intermediate course for z/OS database administrators who need to acquire the basic skills required to administer a DB2 database.

Prerequisites

Before taking this course, you should be able to:

• Design a relational table
• Describe the SQL data manipulation statements to access and change the contents of DB2 tables

You should also take DB2 Family Fundamentals (CE03) and SQL Workshop (CE12), or have equivalent experience.