DB2 Universal Database

Table of contents

1. Product Overview.

DB2 Universal Database - The Scalable Database
DB2 Products
DB2 Universal Database (UDB)
DB2 UDB Personal Edition
DB2 Workgroup Edition
DB2 Enterprise Edition
DB2 Universal Database Enterprise-Extended Edition
DB2 Connect
DB2 Connect Personal Edition
DB2 Connect Enterprise Edition
DB2 Developer's Edition
Licensing Scenarios
DB2 Client/Server Environment
Supported Communication Protocols
DB2 Resource Usage
Features and Functions
DB2 Universal Database Administrator's Tools
The Control Center
The SmartGuides
The Command Line Processor (CLP)
Summary
Questions
Answers.

2. Getting Started and Connected.

Product Installation
Intel Installation
UNIX Installation
DB2 Client Installation
The DB2 Environment
DB2 Profile Registry
Environment Variables
Using the Command Line Processor
DAS and DB2 Instances
DB2 Administration Server (DAS) Instance
DB2 Instances
Creating the Sample Database
Summary
Questions
Answers
Exercises.

3. Controlling Data Access.

Security
Overview of Security
Authentication
Authorization Levels
Security Considerations
DB2 Privileges (Data Control Language)
Database Object Privileges
Establishing a DB2 Client/Server Connection
Configuring a DB2 UDB Server
DB2 Directories
DB2 Discovery
Generating a Server Access Profile
Configuring DB2 Clients
Using the Client Configuration Assistant
Configuring a Connection to a DB2 Database
Instance Administration
Summary
Questions
Answers
Exercises.

4. Using SQL.

Understanding Database Objects
Data Types
DB2-Supplied Data Types
Large Objects (LOBs)
String Data Types
Numeric Data Types
Datetime Data Types
User-Defined Data Types
Code Page Considerations
Selecting the Correct Data Type
DB2 Certification Database Design and Implementation
Tables
Using SQL Data Definition Language (DDL)
Creating a DB2 Database
Views
Indexes
Data Manipulation Language (DML)
Data Retrieval (SELECT)
Selecting Columns from Multiple Tables
DB2 Functions
Data Modification (INSERT/UPDATE/DELETE)
Removing Data
View Classification
Business Rules
User-Defined Defaults
Check Constraints
Triggers
Advanced SQL
Recursive SQL
Outer Join
OLAP Features
Advanced Case Expressions
Summary
Questions
Answers
Exercises

5. Database Concurrency.

Concurrency
Concurrency Considerations
Lost Update Problem
Uncommitted Read
Non-Repeatable Read
Phantom Read Problem
Isolation Level
Uncommitted Read
Cursor Stability
Read Stability
Repeatable Read
Choosing an Isolation Level
Locking
Lock Attributes
Lock Conversion
Lock Escalation
Lock Wait Behavior
Summary
Questions
Answers
Exercises

6. Data Placement.

Containers
Table Spaces
Extents
System Managed Storage (SMS) Table Spaces
Database Managed Storage (DMS) Table Spaces
Planning a Table Space Environment
Creating Table Spaces
Performance Considerations
Buffer Pool
Extent Size
NUM_IOSERVERS
I/O Prefetch
Page Cleaners
System Catalogs in DB2
Managing Table Spaces
States of Table Spaces
Summary
Questions
Answers
Exercises.

7. Data Management.

Populating Tables
The LOAD Utility
Using The LOAD Utility
Syntax of the LOAD Command
The IMPORT/EXPORT Utility
The EXPORT Utility
The IMPORT Utility
Data Maintenance
Analyzing Data's Physical Organization: REORGCHK
Table Reorganization: REORG
RUNSTATS
The REBIND Utility
Data Maintenance Process
Modelling a Production Environment
User Maintenance
Fenced and Not Fenced Procedures and Functions
Database Recovery
Use of Log Files
Types of Logging
Backup and Restore
Restore Database SmartGuide
Summary of Backup/Restore Consideration
The QUIESCE Command
The Recovery History File
Administration Tools for Data Maintenance
The Script Center
The Journal
Data Replication
Summary
Answers
Exercises

8. Database Monitoring and Tuning.

Monitoring Methods
Obtaining Database Access Information
Explain Facilities
Query Compilation
Gathering Explain Data
Examining EXPLAIN Data
Database Monitoring
Event Monitoring
Visual Performance Monitors
Performance Monitor
DB2 Governor
Performance Issues
Configuring Database Resources
DB2 Sorting Methods
DB2 Server Resources
Query Parallelism
Performance Tuning Scenario
Diagnostics and Problem Determination
Error Messages and SQL Codes
Tracing Problems in DB2 Universal Database
Database Analysis and Repair Tool
Summary
Questions
Answers
Exercises.

9. DB2 Application Programming.

DB2 Application Development Environment
Client/Server Platform
Choosing a DB2 Programming Method
Static Embedded SQL
Dynamic Embedded SQL
Call Level Interface (CLI)
DB2 Application Programming Interfaces (APIs)
Accessing Data from an Application
Using Schemas
Using an Alias
Creating Packages
Binding Applications
Binding Utilities
Parts of a DB2 Embedded Static SQL Application
Host Variables
Indicator Variables
Using Cursors
Handling Application Termination
Interrupt Handling
Large Objects
Direct LOB Reference
Reference by LOB Locator
LOB File Reference Variables
Unique Keys and Referential Integrity
Online Analytical Processing
STAR Schemas
OLAP Indexes
Dynamic Bitmap Index ANDing
STAR Joins
OLAP SQL
Summary
Questions
Answers
Exercises

10. DB2 Advanced Application Programming.

Writing Dynamic Programs
First Look at Dynamic SQL
Dynamic SQL Phases
Types of Dynamic SQL Statements
Dynamic SQL - Callable SQL Interfaces
ODBC vs
CLI
Embedded Dynamic vs
Call Level Interface
Setting Up a CLI Development Environment
Stored Procedures
User Defined Functions
Support For Java Programming
Compound SQL
Summary
Questions
Answers
Exercises

Appendix A - Test Objectives.

DB2 UDB V5 Fundamentals (503).

Appendix B - Test Objectives.

DB2 UDB V5 Database Administration (504).

Appendix C - Test Objectives.

DB2 UDB V5 Application Development (505).

Appendix D - DB2 Tools and Utilities.

Appendix E - DB2 Example Source Code.

Index.

CD-ROM Installation.