Audience
This course will be of interest to anyone needing an understanding of the design, implementation, support and maintenance of DB2 databases up to DB2 10.
Prerequisites
A fundamental understanding of the DB2 relational database management system is assumed, as is an understanding of data processing in general. Familiarity with the z/OS operating systems and TSO/ISPF would also be of benefit.
Duration
5 days. Hands on.
Course Objectives
The materials in the course reflect DB2 10, but can cover earlier releases on request. This course provides attendees with a comprehensive introduction to designing and administering DB2 databases. The course covers the complete spectrum from initial data model, through logical and physical database design, data security, and ongoing administration to ensure that the data remains complete, consistent, and in its most efficient state.
On completion delegates will appreciate:
- Data modelling techniques.
- The process of normalisation of data.
- Mapping data models to DB2 objects.
- Choosing the correct data types, and physical design characteristics.
- Data integrity issues.
- Page layouts and storage estimations.
- Indexing, locking strategy and other performance implications.
- Use of utilities and service aids in ongoing maintenance.
- Security and auditing considerations.
Course Content
THE SYSTEM ARCHITECTURE
THE BIG PICTURE
DBAS (DATA BASE ADDRESS SPACE)
IRLM
DDF
BUFFER MANAGER
EDM Pool
DATABASES
SORT
WORKING STORAGE
Data transfer
Page
Prefetch/Parallelism
Parallelism
Storage
Log Manager
DB2 Traces
DSNZPARMS
DDL
SQL Limits prior Version 8
SQL Limits
DB2 Objects
Stogroups
Database
Tablespace
Simple/Segmented
Partitioned
Parameters
Universal
Tables
Temporal Tables
Numeric Data Types
Character Data Types
Date/Time Data Types
Large Objects
Rowid
Identity Column
Sequence Column
UIDT
Biginit
Binary
Decfloat
XML
Attribute Qualifiers
Referential Integrity
Views
Materialized Query Tables
Online Schema
Indexes
Versioning
The CATALOG and DIRECTORY
DB2 System Catalog
DB2 System Catalog History Tables
Catalog Relationship
Obtaining the index names
DB2 directory
LOCKING STRATEGY
Introduction
What resources can be locked
Different Locks available
Duration of a lock
Commit/Rollback
Savepoints
Locking Notes
Locking Problems and Detection
INDEXES
Benefits of indexing
Candidates for indexing/not for indexing
Page
Record Identifier
Creating indexes
Index guide lines
RUNSTATS/REORG
Access Strategy
Index/Stage 1/Stage 2 Table
Access Types
Multiple table Access Path
The Optimizer
EXPLAIN
Querying the PLAN_TABLE
Sortn/Sortc
Explain DB 10
Statement Cost Estimation
Reoptimization/Rebind
VISUAL EXPLAIN
Optimization Service Centre
Volatile
DB2 Administration Tools
DB2 Performance Analyzer
Hash Access
Include Non-Unique Columns Within a Unique Index
DB2 10 Index Changes
ADVANCED SQL
The importance of Predicates
General guidelines for efficient SQL
Using Predicates
OPTIMIZE FOR n ROWS
FOR FETCH/READ ONLY
WITH clause
Table joins
Introduction
Joins on equality
Subselects/Subqueries
Correlated Subselects
Unions
Intersect/Except
Inner joins
Left joins
Right joins
Full joins
Joining adding a WHERE clause
Nested Tables
CTE(Common Table Expressions)
COALESCE
CASE
UPDATE
UPDATE Using A Subselect
UPDATE Features
DELETE
INSERT
MERGE
UTILITIES
Who Can RunDB2 Utilities
Invoking DB2 Utilities
DB2 Administration Tool
DB2I
LISTDEF/TEMPLATE
Using DSNUPROC
General Utility Flow
SYSUTILX Directory Tablespace
LOAD Utility
DSNTIAUL Utility
UNLOAD Utility
RUNSTATS
REORG
CHECK
COPY
MERGECOPY
QUIESCE
RECOVERY
MODIFY
DB2 SECURITY
Security Overview
First Level of Security
Connection Security
Internal Security 1
Other Security
Security Strategy
Internal Security
DB2 Security Mechanism
DB2 Security Tables
DB2 Security Terms
Authorisation ID
Privilege
Resource
SQL
SQL Grant and Revoke Statements
Cascading Revoke
Package, Plan and Collection Privileges
Database, Table and View Privileges
Other Object Privileges
System Privileges
Example 1 Application Development
Example 2 Bind
Example 3 Program Execution
Insufficient Authority
DB2 Catalog Security Tables
Common Tables Columns
Catalog Tables
Auditing Tables
Audit Trace
RACF/ACF2/TOP SECRET Security Overview
What is RACF/ACF2/TOP SECRET
Identify and Verify Users
Checking Authorizations
Recording and Reporting
Terminology
Users and Groups
Resources and Classes
Profiles
User Profile
Resource Profile
Discrete and Generic Profiles
Creating Generic Profiles
Maintaining RACF Security
Multi Level Security
Distributed Data Considerations
SECADM
APPLICATION PROGRAMMING FEATURES AFFECTING DBA’s
Fetch/Read Only
Optimize For
Nulls
Temporary Tables
Rowset/multi row fetch/update/insert
Reordered Row Format
Implicitly Hidden Columns
Truncate Table
Temporal Tables
Program Development
TRIGGERS / STORED PROCEDURES
TRIGGERS
Types of Trigger
Requirements
Syntax
Instead of Triggers
STORED PROCEDURES
Types of Stored Procedures
An SQL Procedure
An External Procedure
The Catalog
Writing an External Procedure
Calling the Stored Procedure
The SQL Procedure Language
Stored Procedure Builder
LOGS
The Active Log
The Archive Log 2
The Boot Strap Dataset
The Log Buffer
DB2 zParms
Log Avoidance
DDL Operations
Reorg and Load Utilities
SQL Operation
Mass Delete
Reorder Row Format
Log Stand Alone Utilities
Tools for Handling Logs
XML
XML Layout
Data Modelling
Database Creation
Tablespace Creation
Table creation
Catalog Information
Creating a full Text Index
Comparing XML and Relational Indexes
Index Data Types
Xpath Expressions
Create Index
Using Explain
Define Lean Indexes
DB2 Catalog
DB2 Commands
zPARMS
Query Language
XML Functions
XMLPARSE
XMLQUERY
XMLEXISTS
XMLTABLE
Views
MQT
XML With Utilities
XML Performance Notes
XML Version 10