DB2 for LUW Training - UD03 - DB2 for LUW Database Administration Workshop

UD03 - DB2 for LUW Database Administration Workshop
Duration	5 Days (customisation)
Availability	Retrieving... (Click to check course price and availability)

Course Description

This course provides advanced DB2 for LUW training, and covers all of the key tasks normally performed by a Data Base Administrator.

Pre-requisites

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

Objectives

The aim of this course is to provide the delegate with the necessary skills required to function as a DBA within a LUW environment.

On completion of this course the student will be able to:

use the DB2 Toolset
define / maintain DB2 objects using DDL statements CREATE, ALTER and DROP
define partitioning databases and tables
use IBM Data Studio and the Data Studio Web Console
run IMPORT, EXPORT and LOAD statements
perform BACKUP, RESTORE, RECOVER and ROLLFORWARD commands
understand the concepts of High Availability Disaster Recovery (HADR)
define Referential Integrity constraints and Table Check constraints
resolve integrity violations
understand the application development process
bind plans and packages
set up database security
understand and resolve locking issues
monitor database activity
investigate system, application and SQL performance issues
perform database replication tasks

Environment

The lectures apply to DB2 running in a Linux, Unix or Windows environment.

Customisation

For on-site courses (i.e. at your premises), we are more than happy to tailor the course agenda to suit your exact requirements. In many cases, we are able to build your in-house standards and naming conventions into the delivered course.

Course Details

GETTING STARTED WITH DB2 for LUW: The LUW Environment; Db2 Administration Tools
DB2 INSTANCES: What Is An Instance?; Setting Up Instances; Configuring Instances; Updating the Database Manager Configuration; Database Manager Configuration Parameters; Implementing Changes; Working With Multiple Instances
DATABASE DEFINITION: The Structure Of DB2 Objects; Database Definition; Default Tablespaces; 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; Block Based Bufferpools - Prefetch Enhancements
TABLESPACE DEFINITION: Tablespace Organisation; Data Placement - SMS or DMS?; Data Placement Considerations; Containers, Pages And Extents; Maximum Sizes; Create Tablespace Command; Create Tablespace Parameters; Sms Tablespace Examples; Dms Tablespaces; Automatic Storage Tablespaces; Dms Minimum Space Requirements; Alter Tablespace Command; 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; Db2 Column Types; Null Values; Compressing a Table; Row Compression - Null and Default Compression; Has Compression Been Switched On?; Lob Data Options; The Alter Table Statement; The Rename Table Statement; 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; Alias Definition; Indexes; Index Organisation - The B Tree Index; Index Clustering; Index Definition; Renaming Indexes; Multidimensional Clustering; Defining Multidimensional Clustering; Defining Multidimensional Clustering Indexes; Using Generated Columns with MDC; Db2look
PARTITIONING: Introduction and Terminology; The DB2Nodes.cfg File; 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
RUNNING SQL AND COMMANDS: Connecting To The Database; Using IBM Data Studio to run SQL Scripts; The DB2 Command Window and Command Line Processor; Command Line Syntax; On-Line Help; Interactive / Non-Interactive Modes; Clp Option Flag; Clp Termination
MOVING DATA: Overview of Data Movement Utilities; Import / Export Utilities; Import Utility; Import Authorities; Export Utility; Export Authorities; Load Utility; Load Utility Considerations; Load Examples; Load from a Cursor; Online Table Load; Load - Lock With Force Parameter; The Load Query Command - Table States; Recovering From Load Failure / Load Pending; Load Restart Example; The Load Wizard; Load / Import Differences; DB2Move Command; DB2Move Examples; Tablespace Pending States; Dealing with Backup Pending after LOAD; The Quiesce Utility; Quiesce Enhancement; Utility Progress Monitoring; The Admin_Cmd Procedure
BACKUP AND RECOVERY: Data Recovery Overview; Methods Of Recovery; Logging Overview; Circular Logging; Archival Logging; 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 Considerations; Database Restore Using Redirect; Rollforward Utility / Rollforward Pending; The Rollforward Utility; Rollforward Considerations; Restore / Rollforward Examples; The Recover Utility; Logging / Backup / Recovery Summary; The Admin_Cmd Procedure; High Availability Disaster Recovery; Configuring a database to use HADR; Connecting to the standby database; Executing HADR Commands
APPLICATION PROGRAMMING: Db2 Environments; Development Cycle For Embedded Sql in C; SQLJ Programming - Development Cycle; Precompiling A C Program; Binding A C Program; Levels Of Optimisation; Stored Procedure Review; An SQL Procedure Example; Selecting Data - Singleton Selects; Selecting Data - Cursor Operations; Building and Rebinding Stored Procedures
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 IBM Data Studio; Informational Constraints
MATERIALIZED QUERY TABLES: What Are Materialized Query Tables?; MQT Features; Creating an MQT; Create MQT Example; Altering an MQT; Alter MQT Example; MQT Fullselect Features / Restrictions; Refresh Table; Populating User Maintained MQTs; Automatic Query Rewrite using MQT; Enabling Automatic Query Rewrite; Enabling Automatic Query Rewrite - DDL Options; Enabling Automatic Query Rewrite - Special Registers; AQR - Using Both Registers; Enabling Automatic Query Rewrite - System Properties; Enabling Automatic Query Rewrite - Query Properties; Registering Existing Tables as MQT; Maintenance of MQTs using Staging Tables

LOCKING: Implications Of Concurrent Processing; Database Manager Locks; Objects Of Locks; Isolation Levels; Lock Modes; Lock Compatibility; Lock Example; Change in Cursor Stability Behaviour V9.7; 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
SECURITY: 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
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; Further GUI Automatic Reorg Features; The Admin_Cmd Procedure
SYSTEM PERFORMANCE: Database Performance Configuration Parameters; Self Tuning Memory Allocation; Data Sorts; Concurrent Application Tuning; Asynchronous Page Cleaner; Blocking Data; The Database Configuration Advisor
MONITORING: Error Logging; Database Monitoring; Snapshot Monitor; Turning Monitoring Switches On; Snapshot Commands; Taking a Snapshot using Sql; Summary of Activity Monitor SQL Routines; Summary of Health Snapshop SQL Routines; Summary of Snapshot Related SQL Routines; Summary of ADMIN_CMD Related SQL Routines; Summary of Configuration Related SQL Routines; Summary of Environment Related SQL Routines; Summary of WebSphere MQ Related SQL Routines; Summary of Security Related SQL Routines; Summary of Stored Procedure Related SQL Routines; Summary of Redistribution Related SQL Routines; Summary of Management Tool Related SQL Routines; Summary of Miscellaneous Tool Related SQL Routines; Examples Of SQL Functions / Views; Event Monitors; The Create Event Monitor Command; Event Monitor Example; Activating Monitors; Formatting File Monitor Output; Event Monitors - Writing to SQL tables; The Activity Monitor; Health Monitoring; Health Indicator Configuration; Recommendation Advisor; Diagnosis of Lock Timeout; Lock Timeout Report File Example; DB2PD Command
SQL PERFORMANCE AND TUNING: SQL Explain Tools; Explain Tables; The Db2 Explain Bind Option; The Db2expln Tool; The DynExpln Tool; Interpreting DB2Expln and Dynexpln Output; The Db2advis Tool - Index Advisor; The Design Advisor; The Visual Explain Tool; The Explain Operator Details Window; Visual Explain Operators; Visual Explain - The Table Statistics Window; Visual Explain - The Column Statistics Window; The Index Statistics Window; The Explainable Statements Window; 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
DB2 TOOLS: IBM Data Studio; IBM Data Studio Web Console
Data Replication: Overview; Replication Tools; Steps Required To Set Up A Replication Environment; Defining A Replication Source; Defining A Replication Subscription; Configuring The Capture Program; Configuring The Apply Program; End-User Authentication File Creation; Starting The Capture Program; Starting The Apply Program; Stopping The Capture And Apply Programs; Troubleshooting
DB2 SIZE LIMITS: Naming Limits; Numeric Limits; String Limits; Xml Limits; Size Limits; Tablespace Limits
DB2 V9 NEW FEATURES: General; Backup, Recovery and High Availability; Performance and Tuning; Security; XML; Application Development; DDL; No Longer Available; Deprecated Features
DB2 V10 NEW FEATURES: General; Performance and Tuning; DDL; Security; XML; Application Development; No Longer Available; Deprecated Features

Course Format

The course includes many practical sessions, designing, implementing, and tuning a Case Study system, using all DB2 utilities applicable to the role of the DBA. On completion of this course delegates will be ready to perform the daily tasks associated with a DBA role on a Windows platform.

The comprehensive Student Guide supplied is fully indexed serving as a useful reference tool long after the course has finished. Delegates will also be able to access a free help-line with technical questions relating to topics covered on the course.

CASE Training Tel: 01275 859666