DB2 for LUW (Linux, UNIX & Windows) Administration

Audience

This course is for Systems Administrators, Database Administrators and technical personnel who are involved in all stages of designing, planning, implementation and maintaining DB2 for LUW Databases. The course is delivered up to DB2 9.7

Prerequisites

Delegates are expected to have relevant operating system experience (i.e. Linux, UNIX or Windows). Knowledge of relational database theory is not a pre-requisite, nor is previous experience of using DB2.

Duration

4 days. Hands on.

Course Objectives

The course is designed to teach delegates how to perform database administration activities on DB2 in a Linux, UNIX or Windows environment. Those activities include all aspects of manual or automatic maintenance, utilities, problem determination, parallelism, distributed data management, remote administration, MDT, Health Center, federated databases and the DB2 Governor. The course contains numerous exercises to consolidate the course content.

Course Content

Introduction to DB2
SQL Compatibility Feature
Versions
System architecture
Terms/Terminology
Instance/Database
Tablespace
Regular/System temporary/User temporary/Long
Tables
Table partitioning
Views
Merge
Materialization
MQT
Columns
Nulls
Referential Integrity
DB2 System Catalog
Buffer Pools Including Scan Sharing
Alias
Attributes
Numeric / Character
Date/Time
ROWID
IDENTITY/ SEQUENCE
UDT
LOB’s
UNICODE
XML

Data Definition Language
CONNECT
DATABASE
TABLESPACE
TABLE
ALTER
TRUNCATE Table
Generate DDL
Set Integrity

SELECT (single table)
*/ALL/COLUMNS/DISTICT/CONCATENATOR/literals/ORDER BY
FETCH FIRST
Efficiency aspects
Predicates
= (efficiency aspects)
IN (efficiency aspects)
BETWEEN (efficiency aspects)
LIKE (efficiency aspects)
GROUP
AND/OR
Built in function
MAX/MIN/SUM/COUNT/AVG/COUNT_BIG
Scalar Functions
Examples and use of some of them e.g. SUBSTR/VALUE etc
Complex SQL
Table joins
EQ JOIN
SUBSELECT
CORRELATED SUBSELECT
UNION

Left/Right/Full Joins
In Line Tables
CASE
Remainder of DML statements
UPDATE/DELETE/INSERT/MERGE
CTE’s
Practical exercise

Embedded SQL
Static and Dynamic SQL
Cursors
Development cycle
Precompile
DBRMs
BIND
PLANS/PACKAGES
Execution of executable program
Practical exercise

LOCKING STRATEGY
Introduction
What resources can be locked?
The different locks available
Duration of a lock
Commit/rollback
Locking notes

INDEXING
Physical data management
Tablespace Object
Page
Record identifier
Indexes Type 1(removed in v 9.7) and Index Type 2
Index Compression
Partitioned Indexes
RUNSTATS/REORG
MDC
Range Clustered Tables
Statistical Views
Access strategy
Multi table access path
EXPLAIN
OPTIMIZATION SERVICE CENTER

Extra Facilities
OPTIMIZE FOR
HANDLING NULLS
TEMPORARY TABLES
Remote and Distributed Environments

Logs
Types of Logging
Configuring Database
Logging Information
DB2DIAG
Manual Reading of the Log
Snapshot & Event Monitor
TRACE DB2TRC
REORGCHK
Output Table Stats
Output Index Stats

Security
Levels Of Security
GRANT/REVOKE
Security levels
Table check constraints
TRIGGERS
STORED PROCEDURES
Utilities
BACKUP / RESTORE / LOAD
IMPORT/EXPORT

DB2 LUW Tools overview
Design Advisor
Index Advisor
Activity Monitor
Memory Visualization
Health Center (Data Studio Administrative Console)
Remote Administration
Replication Center
Task Center
Event Analyzer
Recommendation Adviser