DB2 LUW for Database Administrators

Course Objectives
The aim of this Course is to provide the delegate with the necessary skills required to function as a DBA within a LUW environment. On completion of this Course the student will be able to:

• use the DB2 for Windows GUI Toolset
• define / maintain DB2 objects using DDL statements CREATE, ALTER and DROP
• manipulate data using DML statements SELECT, INSERT, UPDATE and DELETE statements
• use the Command Line Processor
• define Referential Integrity constraints and Table Check constraints
• resolve integrity violations
• install Stored Procedures
• install User Defined Functions
• understand the application development process
• bind plans and packages
• resolve security issues using DCL statements GRANT and REVOKE
• understand and resolve locking issues
• understand and run all DB2 Utilities
• perform database backup and recovery tasks
• monitor database / application performance
• perform database replication tasks

Course Details

The UDB Environment
GETTING STARTED WITH UDB
The UDB Environment
Accessing DB2
UDB Product Family
UDB Interactive Tools
Setting up Initial Security
Windows NT Setup - Creating a new Administrator / Group
What is an Instance?
Setting up Instances
Configuring Instances
Implementing Changes
Attaching to an Instance
Client Configuration Assistant (CCA)
DATA DEFINITION LANGUAGE
The Structure of UDB Objects
Definition of DB2 Objects - DDL
Database Definition
Database Creation using the GUI
Database Definition Tasks
Database Placement
Database Connectivity
Catalog Tables
Database Configuration
Database Directories
Tablespace Organisation
Table Placement
Data Placement Considerations
Containers, Pages and Extents
SMS Tablespaces
DMS Tablespaces
DMS Minimum Space Requirements
DMS Maximum Space
Creating a Tablespace using the GUI
Displaying Tablespace Information
Altering a Tablespace
Dropping a Tablespace
SMS vs DMS Considerations
Create Bufferpool
Page and Row Organisation
Table Definition
Table / Column Names
Copying Table Definitions
Table Authority
Data Types
Null Values
User Defined Default Values
Lob Data Options
Lob Data Manipulation
User Defined Distinct Types
Data Capture
The Alter Table Statement
The Rename Table Statement
Listing Table / Tablespace Information
Schema Definition
Indexes
Index Organisation - The B Tree Index
Index Definition
Index Clustering
Index Design Considerations
Views Definition
Read Only Views
View Restrictions
Views - Check Options
Creating a View of Two Tables
Alias Definition
Overview of Triggers
Trigger Definition
The Drop Statements
COMMAND LINE PROCESSOR
CLP Syntax
On-Line Help
Interactive / Non-Interactive Modes
CLP Option Flag
CLP Termination
DATA MANIPULATION LANGUAGE
SQL - Structured Query Language
SQL Features
SQL Query Results
DB2 Environments
The Select Statement
The Where Clause
Special Operators
Not Operand
In Operand
Like Operand
Between Operand
User Keyword
Statements using Nulls
SQL Built-In Functions
Using ''Distinct''
Group By Clause
Group By Rollup
The Grouping Function
Group By Cube
Group By Grouping Sets
Having Clause
Order By Clause
Fetch First Clause
The Values Statement
The Update Statement
The Delete Statement
The Insert Statement
Column Functions (Scalar Functions)
Working with Date, Time and Timestamp Columns
DB2 Special Date/Time Registers
Current Date
Current Time
Current Timestamp
DB2 Join
Inner Joins
Outer Joins
Outer Join - Where Clause
Nested Table Expression
SQL Union / Intersect / Except
Subqueries
Subqueries using In
Exists
The ''All'' Subquery
The ''Any'' or ''Some'' Subquery
REFERENTIAL INTEGRITY
What is Referential Integrity?
The Primary Key
The Foreign Key
Referential Constraint Rules
Constraint Names
More Complex Referential Structures
Check Pending / Set Constraints
Running Set Constraints via the GUI
TABLE CHECK CONSTRAINTS
Overview of Check Constraints
Constraint Syntax
Allowable Constraints
When Are Constraints Enforced
Check Pending
Running Set Constraints via the GUI
Catalog Information
MOVING DATA
Import / Export Utilities
Import Utility Syntax
Import Authorities
Export Utility Syntax
Export Authorities
Load Utility
Load Utility Features
Load Examples
Load Utility Syntax
Recovering from Load Failure / Load Pending
Load Utility and Referential / Check Constraints:
Load Authorities
Load / Import Differences
Tablespace Pending States
Backup Pending
Check Pending / Set Constraints
Running Set Constraints via the GUI
Replication - Data Propagator
BACKUP AND RECOVERY
Data Recovery Overview
Methods of Recovery
Logging Overview
Circular Logging
Archival Logging
Log File Information
Recovery History File
The Backup Utility
Backup File Names
Backup Examples
The Restore and Rollforward Utilities
Rollforward Utility / Rollforward Pending
Rollforward Considerations
Recovery Examples
Logging / Backup / Recovery Summary
The Quiesce Utility
DATABASE CONNECTIVITY
Type 1 / Type 2 Connectivity
The Connect Statement
Connect Reset
Disconnect
Release
APPLICATION PROGRAMMING
DB2 Environments
Database Engine Access
Development Cycle for Embedded SQL
SQL Statement Format - C
SQL Statements used in Application Programs
SQL Include
SQL Communication Area
Host Variable Declaration
Retrieving Data into Host Variables
Retrieving a ''Set'' of Columns
Cursors
Declare Cursor
Open Cursor
Fetch a Row
Row Update
Row Deletion
Close Cursor
With Hold Option
Fetch First Clause
The Optimize Statement
Handling Nulls
Sample C Program
Handling Large Objects
Declaring Lob Variables
Example using Lob Variables
Using Lob Locators
Example using Lob Locators
Lob File References
Example using File References
Lob Limitations
Retrieving Current Date
Table Names in Application Programs
SQL Error Codes
The SQLCA
Error Handling - The Whenever Statement
Precompiling a Program
Binding a Program
Levels of Optimisation
Identifying a Collection within a Program
Call Level Interface (CLI)
Open Database Connectivity (ODBC)
Importing Data into Microsoft Access
Java Database Connectivity (JDBC)
USER DEFINED FUNCTIONS
Functions Overview
Function Path
Writing an External Function
Step 1 - Write the Function
Step 2 - Compile the Function
Step 3 - Define the Function to UDB
Step 4 - Test the Function
STORED PROCEDURES
Overview
Advantages of Stored Procedures
The SQL Call Statement
Executing a Procedure
Writing a Stored Procedure - The Server Side
The CASESRV.SQC Example
The CASESRV.SQC Listing
Calling a Stored Procedure - The Client Side
The CASECLI.SQC Example
Implementing a Stored Procedure
The Create Procedure Statement
Stored Procedure Limitations
LOCKING
Implications of Concurrent Processing
Database Manager Locks
Objects of Locks
Lock Modes
Lock Example
Lock Compatibility
Isolation Levels
Lock Escalation
The Lock Table Statement
Commit Points
Lock Wait and Deadlocks
The QUIESCE Utility
MONITORING
Error Logging
Database Monitoring
Snapshot Monitoring
Alerts
Monitoring Commands
Turning Monitoring Switches On
Event Monitoring
The Create Event Monitor Command
DB2 Trace Facility
Explain
Explain Facility Tools
The DB2 Explain Function
Explain Tables
The DB2EXPLN Applet
The Visual Explain Tool
SECURITY
Authorities and Privileges
Instance / System Authorities
Database Authorities
Database Authority Summary
Schema Privileges
Table Privileges
View Privileges
Index Privileges
Package Privileges
The Grant Statement
The Control Privilege
The Revoke Statement
Catalog Information
Granting using the GUI
Privileges Required for Programming
Grant Examples
Revoke Examples
APPLICATION PERFORMANCE
The DB2 Optimizer
Default Catalog Statistics
Levels of Optimisation
Operational Utilities
Rebinding
The RUNSTATS Utility
The REORGCHK Utility
The REORG Utility
Other Application Performance Issues
Data Sorts
Concurrent Application Tuning
Asynchronous Page Cleaner
Blocking Data
UDB TOOLS
The Control Centre
The Command Centre
Writing Scripts
Saving a Script
The Access Plan Panel
The Script Centre
The Journal
The Information Centre
The Alert Centre
Tool Settings
DATA REPLICATION
Overview
Replication Tools
Steps Required to set up a Replication Environment
Defining a Replication Source
Defining a Replication Subscription
Configuring the Capture Program
Configuring the Apply Program
End-User Authentication File Creation
Starting the Capture Program
Starting the Apply Program
Stopping the Capture and Apply Programs
Troubleshooting
Course Environment The lectures apply to DB2 UDB running in a Linux, Unix or Windows environment.
Course Format: The course contains many practical exercises to ensure familiarity with the product. Initially students create Tables and Indexes, and then proceed to develop Java or C programs accessing the data held on the DB2 Tables. On completion of this course students will be ready to start the development of application programs accessing DB2 data. The comprehensive Student Guide supplied is fully indexed serving as a useful reference tool long after the course has finished. Delegates will also be able to access a free help-line with technical questions relating to topics covered on the course.