Descripiton

This course teaches database administrators to perform basic database administrative tasks using DB2 LUW.

Topics covered include creating database objects (such as databases, tablespaces, tables, views, indexes tables, indexes and views) loading / unloading data and DB2 recovery techniques.

Students will learn how to implement 'archive logging' and also how to recover a database to a specific point in time using these archived logs.

Prerequisites

Familiarity with the host environment is required. Prior exposure to DB2 or another relational database would be advantageous but not essential.

Audience

Database Administrators

Objectives

The aim of this course is to provide the delegate with the necessary skills required to function as a DBA within an LUW environment. After completing this course, participants should be able to:

• use the DB2 Toolset / IBM Data Studio
• use the Command Window and Command Line Processor
• understand, create and configure INSTANCEs
• make database configuration changes
• create INSTANCES and DATABASES
• define / maintain essential database objects using statements CREATE, ALTER and DROP
• understand different type of TABLESPACE available - Automatic, DMS and SMS
• Unload data using EXPORT
• Load data using LOAD, IMPORT and INGEST
• define partitioning databases and tables
• understand the difference between circular logging and archive logging
• run IMPORT, EXPORT and LOAD statements
• run BACKUP and RESTORE commands
• recover to a point in time
• perform BACKUP, RESTORE, RECOVER and ROLLFORWARD commands
• use other data movement tools such as DB2MOVE, DB2RELOCATEDB, ADMIN_COPY_SCHEMA, ADMIN_MOVE_TABLE and the TRANSPORT option of restore

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