IBM DB2 DBA ONLINE TRAINING COURSE CONTENT

OVERVIEW OF DB2 ON LINUX, UNIX & WINDOWS

• DB2 family products overview
• DB2 LUW product editions
• DB2 product features & functions
• DB2 client connectivity options

COMMAND LINE PROCESSOR (CLP) AND GUI USAGE

• Introduction to DB2 CLP environment
• Non interactive mode
• Interactive mode
• db2 command line option
• Client connectivity from Data Studio

THE DB2 DATABASE MANAGER INSTANCE

• About DB2 Instance
• overview of Database Manager Instance
• DB2 instance creation pre-requisites
• Create & Drop an instance
• Starting & Stopping an instance
• list & modify DBM configuration
• db2set command
• db2 history command
• DB2 registry & environment variables

CREATING DATABASES & DATA PLACEMENT

• Database creation pre-requisites
• Database storage requirements
• Table spaces overview
• Overview of Storage management
• Default Table spaces
• CREATE DATABASE command
• List DB configuration parameters
• Modifying DB configuration parameters
• Database directories & files introduction
• System catalog tables & views
• Overview of Containers, extents, pages
• CREATE TABLESPACE syntax
• Overview of Storage groups
• CREATE STOGROUP syntax
• Assigning a tablespace to a storage group
• db2pd command Introduction
• ALTER TABLESPACE, ALTER STOGROUP
• DB2 LIST DB DIRECTORY
• DB2 LIST NODE DIRECTORY
• Activate & deactivate a database

CREATING DATABASE OBJECTS

• DB2 object hierarchy
• CREATE SCHEMA
• CREATE TABLE, VIEW
• CREATE ALIAS, INDEX, TRIGGER
• Temporary table’s creation
• Temporal tables Creation
• Table partitioning
• Referential Integrity
• Keys, Constraints
• db2look CLI command
• Authorization & Authentication

MOVING DATA

• IMPORT versus LOAD
• Offline versus online load
• LOAD QUERY TABLE
• DB2 LIST UTILITIES
• Pending states
• EXPORT command
• db2move cli command
• ADMIN_MOV_TABLE procedure

BACKUP & RECOVERY

• Transactional logging
• Configure database logs
• Backup utility options
• Recovery utility options
• ROLLFORWARD
• DB2 RECOVER DATABASE
• ONLINE versus OFFLINE backup

DATABASE MAINTENANCE, MONITORING & PROBLEM DETERMINATION

• RUNSTATS utility
• REORGCHK utility
• REORG Utility
• DB2 List commands
• db2pd – Monitory & troubleshoot DB2
• MON_GET_TABLE function
• CREATE EVENT MONITOR
• DB2 Optimizer
• DB2 EXPLAIN
• log diagnostic log
• db2trc command to obtain trace
• db2fodc: collect diagnostic data for system hang
• db2advis: Analyze workload

LOCKING & CONCURRENCY

• Importance of locking
• Table lock modes
• Row lock modes
• Lock mode compatibility
• Isolation levels
• LOCK TABLE statement
• Lock escalation, wait, & timeout
• Deadlocks & detection

SECURITY

• Authentication versus Authorization
• SYSADM, SYSCTRL, SYSMAINT and SYSMON
• DBADM, SECADM and ACCESSCTRL
• DB2 security privilege hierarchies
• Grant & Revoke privileges

LOG MANAGEMENT

• Database seeds
• Log sequence numbers
• db2ckbkp -l command
• logfilsiz & logbufsz
• Logprimary & Logsecond
• db2pd to check logging activity
• Log Archive processing
• Log retrieval processing
• MAX_LOG configuration parameter

PERFORMANCE TUNING

• Analyze SQL statements
• Creating access plan graph
• db2exfmt tool
• db2expln tool
• Using the Indexes for performance
• Index usage statistics using db2pd
• MON_GET_INDEX
• MON_CONNECTION_SUMMARY view
• Clustering indexes
• db2advis command

ADVANCED MONITORING

• GET SNAPSHOT command
• In-memory metrics
• Monitoring table functions
• Administrative views, functions
• SQL Monitoring performance
• SQL Monitor heath

HIGH AVAILABILITY WITH DB2 DATABASE PRODUCTS

• DB2High Availability options in
• DR options in DB2
• High Availability Disaster Recovery
• High Availability through Log Shipping
• Log mirroring
• High Availability through suspended I/O & online split mirror support