Purpose
This course teaches you how to perform basic database administration tasks using IBM DB2 Universal Database. These tasks include creating and populating databases and implementing a logical design to support recovery requirements.
Audience
Database Administrators and other technical personnel who are involved in planning, implementing, and maintaining DB2 databases.
Objectives
After completing this course, student should be able to:
List and describe the components of DB2 Universal Database
Implement DB2 security
Perform basic administration of a DB2 database system
Define a basic DB2 recovery strategy and perform the tasks necessary to support the strategy
Day 1 : 9am-12:30pm
Overview of DB2 Universal Database
Unit Objectives
1.1 Overview of DB2 Universal Database
DB2 Products
DB2 UDB – The Scalable DB
Accessing DB2
DB2 Product Components
Connecting to DB2 UDB Database Servers
DRDA Application Requester (AR) Support
DRDA Application Server (AS) Support
Sample Scenario
Installation Launchpad
Select Products
Installation of DB2 Universal Database
Instance Administration
What’s in an Instance
DB2 Database Manager Instance
Setting DB2 Variable Values
The db2set Command
DB2 Administration Server (DAS)
Using the Administration Server
Need More Information
Unit Summary
Getting Started with DB2 UDB GUIs
Unit Objectives
Grasping the Graphical Tools
Roadmap to the GUI Tools
Configuration Assistant (CA)
Using the DB2 CA
First Steps
Command Center
SQL Assist Windows
Administration Tools
Overview of Control Center Objects
Using the Wizards
Task Center
Health Center
Journal
Tool Settings
DB2 Web Tools
Unit Summary
Day 1 : 1:30pm-5:00pm
Creating Databases and Data Placement
Unit Objectives
3.1 Create Database Overview
Database, Table Spaces and Tables
Create Database Wizard
Create Database Syntax
Completed by DB2 During Database Creation
Managing Initialization Overhead
Schema and Authorization ID
System Catalog Tables and Views
Topics
Tables Spaces
DMS versus SMS
Table Placement in DMS
Mapping Tables to Table Spaces
Containers and Table Spaces
Writing to Containers
Table Space – Container – Extent – Page
DMS Table Space – Minimum Requirements
Let’s Run Some Numbers
Default Database Configuration
Defining Table Spaces
Sample Scenario
Create Table Space
CREATE TABLESPACE Syntax (1 of 2)
CREATE TABLESPACE Syntax (2 of 2)
Creating SMS Table Spaces
Creating DMS Table Spaces
Getting Information About Table Spaces and Containers
DB2 List Tablespaces Show Details (1 of 2)
DB2 List Tablespaces Show Details (2 of 2)
DB2 List Tablespaces Containers for 5
Creating Tables within a DMS Table Space
Alter Table Space
ALTER TABLESPACE Syntax
Increasing the Size of the humanres Table Space
Choosing between SMS and DMS Table Spaces
Placement Considerations
I/O Prefetch Example
Tablespace Administration
Create Buffer Pool
CREATE BUFFERPOOL Syntax
Configuration Files and Database Directories
Database Configuration
Database Configuration Manager
Change Directory Entries
CONNECT TO Database
Quit versus Terminate versus Connect Reset
Unit Summary
Day 2 : 9am-12:30pm
Creating Objects
Unit Objectives
4.1 Physical Directories and Files
DB2 Object Hierarchy
Database Physical Directories and Files
Correlation between Tables and Tablespaces
4.2 DB2 Objects
Create Schema – GUI
Create Schema – Statement
Set Current Schema
Create Table – GUI
Create Table – Statement
Large Objects – The Need
Identity Columns and Sequences
Declared Temporary Tables
Create Index – GUI
Create Index – Statement
Create View – GUI
Create View – Statement
Create Alias – GUI
Create Alias – Statement
Referential Integrity
RI – Add Foreign Key – GUI
RI – Create Table – Statement
Unique Key Considerations
Check Constraints – The Need
Check Constraints – Definition
Add Check Constraint – GUI
Triggers – The Need
Create Trigger – GUI (1 of 2)
Create Trigger – GUI (2 of 2)
Create Trigger – Statement
For Further Study (1 of 2)
For Further Study (2 of 2)
Unit Summary
Moving Data
Unit Objectives
5.1 Export / Import Utilities
Data Exchange
Export – ARTISTS
EXPORT Command Syntax
Export Utility
Import – ARTISTS
IMPORT Command Syntax
Import Utility
Import Utility…CREATE….IN TABLESPACE
5.2 Load Utility
Four Phases of LOAD
Load – ARTISTS
LOAD Command
LOAD Scenario
Rules for Creating Exception Table
Suggested Methods for Creating Exception Tables
Offline versus Online Load
Table States
Load Pending State: Recovering From LOAD Failure
Backup Pending State – COPY Options
Check Pending Table State
Check Pending State
SET INTEGRITY Syntax
Meaningful Steps for LOAD
Differences between the IMPORT and LOAD Utilities
5.3 Replication
Available Components
DB2 Replicator Relational Architecture
Need More Information ?
Unit Summary
Day 2 : 1:30pm-5:00pm
Recovery
Unit Objectives
6.1 Introduction to Recovery
Potential Problems
Database Objects
6.2 Methods of Recovery and Logging
Methods of Recovery
Logging Introduction
Circular Logging
Archival Logging
Log File Information
Location of Log Files
Configure Database Logs – MUSICDB
Logging Configuration Parameters
Recovery History File
Utilities
Backup Utility
Backup Database – MUSIC DB
Online versus Offline
BACKUP DATABASE
The Backup Files
Restore Utility
Restore Database – MUSICBD
RESTORE DATABASE
Backup/Restore Table Space Considerations
Roll Forward Pending
Roll Forward Database – MUSICDB
ROLLFORWARD Syntax
Roll Forward – How Far?
Log File Naming
Disaster Recovery Considerations
A Few Questions to Consider
Logging/Backup Requirements Summary
Advanced Recovery Features
DB2 UDB Advanced Recovery and High Availability Workshop
Unit Summary
Day 3 : 9am-12:30pm
Monitoring/Problem Determination
Unit Objectives
DB2 Diagnostics
First Failure Data Capture (FFDC)
FFDC Structure
Interpreting the Notification Log
Database Monitor
Why Use Database Monitors?
Ways to Monitor the Database
Snapshot Monitor Overview
Database System Monitor – Who?/How?
Database System Monitor – When?
Database System Monitor – What?
Database System Monitor – CLP Commands
Database System Monitor – Snapshots
Event Monitors
Event Monitor Output
Filtering Event Monitor Output
Examine Monitoring Output
Health Monitoring
Health Center
Health Indicator Settings
Health Centre – Alerts Views
Alarm Details
How to use DB2TRC
Independent Trace Facility – db2trc
Explain
Explain Tool
EXPLAIN Facility Tools
Visual Explain – Control Center
Other Considerations
Additional Commands
List Active Databases
List Applications
Force Applications
Performance Tuning and Monitoring Workshop
Unit Summary
Day 3 :1:30pm-5:00pm
Concurrency
Unit Objectives
Locking Objectives, Strategies, and Modes
Purpose of Locking
Objects of Locks
Locking Strategies
Table Lock Modes
Row Lock Modes
Lock Mode Compatibility
Some Scenarios
Isolation Levels
Isolations Levels
Repeatable Read versus Read Stability
Summary of Isolation Levels
Other Factors Influencing Locking
Lock Table Statement
Lock Conversion
Lock Escalation
Lock Wait and Time out
Deadlock Causes and Detection
Unit Summary
Day 4 :9:00pm-12:30pm
Application Alternatives
Unit Objectives
Application Alternatives
Applications Alternatives
Database Engine Access
Performance – Dynamic versus Static SQL
Database Manager APIs
9.3 Programming Interfaces
Programming Interfaces
Embedded SQL Program Preparation
Embedded SQL Applications
Write the Program
Program Preparations Steps
Associating Load Modules and Packages
Precompiling versus Binding
Adjusting Qualifier and Owner
Precompile an Application Program
Bind and Application Program
Package Catalog Views
Application Process
Development Center and SQL PL
Call Level Interface, ODBC, and OLE DB
Call Level Interface
ODBC (Open Database Connectivity)
OLE DB Support
Java
Java Applications
DB2 Java Applets
Additional Learning Opportunities
Need More Information? (1 of 2)
Need More Information? (2 of 2)
Unit Summary
Application Performance
Unit Objectives
10.1 Application Performance
DB2 Optimizer
Optimization classes
Adjusting the QUERY OPTIMIZATION Class
Design Advisor
Implicit Rebinding
Explicit Rebinding
Sorts – Piped versus Non-piped
Sorts – Sort Passes
Concurrent Application Tuning
Asynchronous Page Cleaner
Minimizing Communication Overhead
Levels of Data Transmission
Blocking – Putting It Together
Stored Procedures
10.3 Utilities and Tools
Operational Utilities
Operation and Non Clustered Indexes
Update Statistics
Determine IF Table Reorganization Required
REORGCHK Statistics
Reorganize Table
Statistical Catalog Views
DB2 UDB Advanced Administration
Unit Summary
Day 4 :1:30pm-5:00pm
Security
Unit Objectives
Authentication versus Authorization
Access Control – Privileges
DB2 Access Control Hierarchy
Database Authority Summary
Authorities and Privileges
Controlling Use of Schemas
Protecting Resources through Programs
Explicit Privileges
Explicit Authorization
Grant Table, View Privileges Support
Individual IDs, Group IDs, and PUBLIC
Group and Use Support
Public Privilege Support for Static SQL and Views
Privileges Required for Programming
Dynamic Rules and Dynamic SQL
DB2 BIND GRANT Option
Implicit Privileges
Implicit Privileges Scenarios
Grant/Revoke Scenarios
Querying Privileges /Authorities
Query Who Has Privileges
Query Privileges Granted to Current ID
Unit Summary
Prerequisites
Before taking this course, student should be able to:
Use basic operating system functions such as utilities, file permissions, hierarchical file system, commands, and editor
State the functions of the Structured Query Language (SQL) and be able to construct DDL, DML, and authorization statements
Discuss basic relational database concepts and objects such as tables, indexes, views, and joins
These skill can be developed by taking OS Training:
AIX/6000 for Users or AIX Version 4.1.4 Basics
OS/2 for Users
Administering Microsoft Windows
SQL Workshop
DB2 Universal Database Fundamentals
This is a version-specific reference outline. Confirm the current syllabus and delivery arrangements with EIEIO.