Descripiton

This course teaches Systems Administrators, Database Administrators and technical personnel to perform Basic and Intermediate database administrative tasks using DB2 for Linux, Unix, Windows (LUW).

For those that want to go into more detail, we also have an Advanced DB2 Administration course.

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.

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 Linux, Unix, Windows (LUW) Databases. The course is delivered up to the latest release level of DB2. Earlier releases can be covered on request.

Objectives

The course is designed to teach delegates how to perform database administration activities on DB2 in a Linux, UNIX, Windows environment. Those activities include all aspects of creating and maintaining Db2 instances, databases, tablespaces and tables, manual or automatic maintenance, utilities, problem determination, parallelism, distributed data management and all aspects of efficiency in a Db2 LUW environment.

The course contains numerous practical exercises to consolidate the course content.

Topics

• GETTING STARTED WITH DB2 FOR LUW
• DB2 Servers
• DB2 Components
• IBM Data Studio
• IBM Data Studio Web Console

• RUNNING SQL AND COMMANDS
• Connecting To The Database
• Running SQL Scripts from IBM Data Studio
• The DB2 Command Window and Command Line Processor
• Command Line Syntax
• On-Line Help
• Interactive / Non-Interactive Modes
• Clp Option Flag
• Clp Termination
• The Update Command Options Command

• DB2 INSTANCES
• What Is An Instance?
• Setting Up Instances
• Working With Multiple Instances
• Attaching to Instances
• Connecting to Other Instances / Servers
• Configuring Instances
• Updating the Database Manager Configuration
• Database Manager Configuration Parameters
• Implementing Changes
• Db2 Administration Server

• DATABASE DEFINITION
• The Structure Of DB2 Objects
• Database Definition
• Default Tablespaces
• Database Placement - Automatic Storage Yes
• Database Placement - Automatic Storage No
• Automatic Storage Databases
• Database Creation using IBM Data Studio
• Database Definition Tasks
• Creating Restrictive Databases
• Altering a Database
• Database Connectivity - Connect vs Activate
• Catalog Tables
• Database Configuration
• Updating the Configuration
• Summary Of Database Configuration Parameters
• Automatic Database Manager / Database Configuration
• Update Database Configuration - Manual Keyword
• Database Directories
• Create Bufferpool
• Online Bufferpool Maintenance
• Useful SQL Routines

• TABLESPACE DEFINITION
• Tablespace Organisation
• Data Placement - SMS or DMS?
• Data Placement Considerations
• Containers, Storage Groups Pages And Extents
• Multiple Container Considerations
• Create Tablespace Command
• Create Tablespace Parameters
• Sms Tablespace Creation
• Dms Tablespace Creation
• Automatic Storage Tablespace Creation
• Storage Groups
• Maximum Sizes
• Dms Minimum Space Requirements
• Alter Tablespace Command
• Converting From DMS to Automatic Storage
• Altering Dms Containers
• Containers, Stripes and Stripe Sets
• Creating A Tablespace Using IBM Data Studio
• Displaying Tablespace Information
• Tablespace States
• Dropping A Tablespace
• Page And Row Organisation

• TABLE / INDEX DEFINITION
• Table Definition
• Table / Column Names
• Copying Table Definitions
• Table Authority
• Table Related Commands
• Db2 Column Types
• Null Values
• Compressing a Table
• Row Compression - Null and Default Compression
• Has Compression Been Switched On?
• Implicitly Hidden Columns
• Row Change Timestamps
• Row Change Timestamp Selection
• Row Change Timestamp Insertion
• Variable Timestamp Precision
• Variable Timestamp Precision - Current Timestamp
• Lob Data Options
• The Alter Table Statement
• The Rename Table Statement
• Column Renaming
• Column Renaming Restrictions
• Drop Column Support
• Drop Column - Restrictions
• Range Clustered Tables
• Range Clustered Tables - Considerations
• Insert Time Clustering
• Column Organized Tables
• Global Temporary Tables
• Declared Temporary Tables
• Declared Temporary Table Considerations
• Declared Temporary Tables - Comparisons
• Listing Table / Tablespace Information
• Schema Definition
• View Definition
• Creating A View Of Two Tables
• Read Only Views
• View Restrictions
• Views - Check Options
• Db2 Synonym
• Alias Definition
• Indexes
• Index Organisation - The B Tree Index
• Index Clustering
• Index Definition
• Renaming Indexes
• Improving Cluster Performance
• Improving Insert Performance
• Multidimensional Clustering
• Defining Multidimensional Clustering
• Defining Multidimensional Clustering Indexes
• Using Generated Columns with MDC
• Db2look - Statistics and DDL Extraction Tool

• PARTITIONING
• Introduction and Terminology
• The DB2Nodes.cfg File
• Configuring DB2Nodes.cfg on Linux and Unix Machines
• Configuring DB2Nodes.cfg on Windows Machines
• Example Configurations
• Creating Parallel Databases
• Database Partition Groups
• Creating a Database Partition Group
• Listing Nodes and Database Partition Groups
• Creating Partitioned Tablespaces
• Creating Partitioned Tables
• Hash Partitioning
• Partitioning Maps
• The Redistribute Database Partition Group Command
• Using Backup and Restore
• Table Partitioning
• Benefits Of Table Partitioning
• Table Partitioning Syntax
• Partitioning at Automated Intervals
• Adding Partitions to a Partitioned Table
• Attaching and Detaching Partitions
• Converting Non-Partitioned Tables to Partitioned
• Using Database and Table Partitioning Together
• Enabling Parallelism

• MOVING DATA
• Overview of Data Movement Utilities
• Import / Export Utilities
• Export Utility
• Export Examples
• Generating the Export Command
• Export Authorities
• Import Utility
• Import Examples
• Import Authorities
• Import / Load Settings for the 'Modified' By Parameter
• Load Utility
• Load Examples
• Recovering From Load Failure / Load Pending
• Load Restart Example
• Load Utility Considerations
• Load from a Cursor
• Online Table Load
• Load - Lock With Force Parameter
• The Load Query Command - Table States
• Dealing with Backup Pending after LOAD
• The Load Wizard
• Load / Import Differences
• Ingest Utility
• Ingest Examples
• Restarting Ingest
• DB2Move Command
• DB2Move Examples
• DB2RelocateDb Command
• DB2RelocateDb Examples
• The Admin_Copy_Schema Procedure
• The Admin_Move_Table Procedure
• Tablespace Pending States
• The Quiesce Command
• Quiesce Examples
• Utility Progress Monitoring
• The Admin_Cmd Procedure

• BACKUP AND RECOVERY
• Data Recovery Overview
• Methods Of Recovery
• Logging Overview
• Circular Logging
• Archive Logging
• Log File Database Configuration Settings
• Log File Information
• Logging Considerations
• Recovery History File
• Removal of History Records
• Automated Removal of History Records
• Single / Dual Logging
• The Backup Utility
• Backup Examples
• Backup Considerations
• Backup File Names
• Automatic Backup
• The Restore Utility
• Restore Examples
• Restore Considerations
• Database Restore Using Redirect
• Rollforward Utility / Rollforward Pending
• The Rollforward Utility
• Rollforward Considerations
• Restore / Rollforward Examples
• Tablespace Minimum Recovery Time
• The Recover Utility
• Recover Example
• Logging / Backup / Recovery Summary
• The Admin_Cmd Procedure
• Execute the REBUILD option of RESTORE
• Execute the TRANSPORT option of RESTORE

• APPLICATION PROGRAMMING ADMINISTRATION
• Db2 Environments
• Development Cycle For Embedded Sql in C
• SQLJ Programming - Development Cycle
• Precompiling A C Program
• Binding A C Program
• Levels Of Optimisation

• LOCKING
• Implications Of Concurrent Processing
• Database Manager Locks
• Objects Of Locks
• Isolation Levels
• Lock Modes
• Lock Compatibility
• Lock Example
• Change in Cursor Stability Behaviour
• Selecting Data with Update / Exclusive Locks
• The Lock Table Statement
• Lock Escalation
• Lock Wait And Deadlocks
• Commit Points
• Savepoints
• Savepoints - Considerations and Restrictions
• Optimistic Locking using Row Change Timestamps
• Optimistic Locking Example
• Row Change Timestamp Manipulation
• The Quiesce Utility

• DATA INTEGRITY
• Referential Integrity
• The Primary Key
• The Foreign Key
• Referential Integrity Rules
• Referential Integrity Constraint Names
• Self Referencing Referential Structures
• Referential Integrity Performance
• Check Constraints
• Check Constraint Syntax
• Allowable Check Constraints
• When Are Check Constraints Enforced
• Integrity Pending (aka Check Pending) State
• The Set Integrity Command
• Running Set Integrity Via The Gui
• Load Utility Considerations
• Informational Constraints

• SECURITY
• Connecting To DB2 - Authentication
• Authentication Methods
• 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 IBM Data Studio
• Privileges Required For Programming
• Grant Examples
• Revoke Examples
• Label-Based Access Control
• LBAC - Step 1 - Define The Security Label Component
• LBAC - Step 2 - Define The Security Policy
• LBAC - Step 3 - Define The Security Labels
• LBAC - Step 4 - Define The Table To Use Security Labels
• LBAC - Step 5 - Grant Security Labels To Users
• LBAC - Step 6 - Use The Table
• Label-Based Access Control - Column Protection
• LBAC Catalog Tables
• The Transfer Ownership Statement
• Introduction To Trusted Context And Roles
• Trusted Context And Roles - 3 Tier Problems
• Security Enhancement - Trusted Contexts / Roles
• Trusted Contexts
• Creating Trusted Contexts
• Roles
• Trusted Context / Role Examples
• Using A Trusted Connection In A Jdbc Application
• Creating Restrictive Databases
• Row And Column Access Control
• Scalar Functions For Row / Column Permissions
• Row Access Control - Defining Row Permissions
• Activating Row Permissions
• Column Access Control - Defining Column Masks
• Activating Column Masks
• Encryption Techniques

• APPLICATION PERFORMANCE
• The Db2 Optimizer
• Levels Of Optimisation
• Operational Utilities
• Rebinding
• The Runstats Utility
• Runstats Parameters
• Runstats - Sampling Options
• Runstats - Statistics Profiling
• Runstats - Throttling
• Runstats Profiling Examples
• Statistical Views
• Statistical View Considerations
• Automatic Statistics Collection
• Automatic Statistics Profile Generation
• Collection of Real Time Statistics
• Volatile Tables
• The Reorgchk Utility
• The Reorg Utility
• Offline / Online Table Reorg
• Index Reorg
• Automatic Table / Index Reorg
• Maintenance Window Features
• The Admin_Cmd Procedure

• SYSTEM PERFORMANCE
• Database Performance Configuration Parameters
• Self-tuning memory manager (STMM)
• Data Sorts
• Concurrent Application Tuning
• Asynchronous Page Cleaner
• Blocking Data
• The Database Configuration Advisor

• MONITORING
• Error Logging
• Database Monitoring
• Monitoring Elements
• System Monitoring Table Functions
• System Monitoring Example
• Activity Monitoring Table Functions
• Database Monitoring Table Functions
• Database Monitoring Example
• Monitoring Table Functions Summary
• Administrative Views
• Administrative View Examples
• Event Monitors
• Event Monitor Types
• The Create Event Monitor Command
• Event Monitor Example
• Activating Monitors
• Formatting File Monitor Output
• Monitor Example - Investigating Poorly Performing SQL
• Event Monitors - Writing to SQL tables
• Snapshot Monitoring - Pre DB2 9.7
• Snapshot Monitor Switches
• Snapshot Commands
• Taking a Snapshot using Sql
• Monitoring SQL Routines
• Examples Of SQL Functions / Views
• Other Diagnosis Features - Diagnosis of Lock Timeout
• Lock Timeout Report File Example
• The DB2PD Command
• The DB2TOP Command

• SQL PERFORMANCE AND TUNING
• SQL Explain Tools
• Explain Tables
• The Explain Command
• The Db2 Explain Bind Option
• The Db2expln Tool
• Interpreting DB2Expln Output
• The Db2advis Tool - Index Advisor
• The Query Tuner
• The Visual Explain Tool
• The Explain Operator Details Window
• Visual Explain Operators
• Visual Explain - The Table Statistics Window
• Visual Explain for Packages
• Access Paths - Tablespace Scan (Relational Scan)
• Non-Matching Index Scan
• Matching Index Scan
• Multiple Index Access
• Index Only Access
• Table Join Methods
• Merge Scan Join
• Nested Loop Join
• Hash Join