DB2 UDB Training - UD03 - DB2 UDB for Database Administrators

UD03 - DB2 UDB for Database Administrators
Duration	5 Days
Availability	Check course price and availability

Target Audience

This course provides advanced DB2 UDB 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.

Course 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 for Windows GUI Toolset
define / maintain DB2 objects using DDL statements CREATE, ALTER and DROP
use the Command Line Processor
define Referential Integrity constraints and Table Check constraints
resolve integrity violations
understand the application development process
bind plans and packages
resolve security issues using DCL statements GRANT and REVOKE
understand and resolve locking issues
understand and run all DB2 Utilities
perform database backup and recovery tasks
monitor database / application performance
perform database replication tasks

Course Environment

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

Course Details

The UDB Environment
DB2 INSTANCES: What Is An Instance?; Setting Up Instances; The Db2set Command; Configuring Instances; Updating the Database Manager Configuration; Database Manager Configuration Parameters; Implementing Changes; Automatic Database Manager / Database Configuration; Working With Multiple Instances
DATABASE DEFINITION: The Structure Of Udb Objects; Database Definition; Default Tablespaces; Database Placement; Automatic Storage Databases; Database Creation Using The Gui; Database Definition Tasks; Altering a Database; Database Connectivity - Connect vs Activate; Catalog Tables; Database Configuration; Updating the Configuration; Summary Of Database Configuration Parameters; Automatic Database Configuration; 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; Create Tablespace Command; Create Tablespace Parameters; Sms Tablespace Examples; Dms Tablespaces; Automatic Storage Tablespaces; Dms Minimum Space Requirements; Dms Maximum Space; Alter Tablespace Command; Altering Dms Containers; Containers, Stripes and Stripe Sets; Creating A Tablespace Using The Gui; 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; Null and Default Compression; Compression - Row Format; Has Compression Been Switched On?; Lob Data Options; The Alter Table Statement; Advanced GUI Alter Table Functionality; 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; Range Partitioned Tables
COMMAND LINE PROCESSOR: CLP Syntax; On-Line Help; Interactive / Non-Interactive Modes; CLP Option Flag; CLP Termination
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; Informational Constraints
MOVING DATA: Import / Export Utilities; Import Utility Syntax; Import Authorities; Export Utility Syntax; Export Authorities; Load Utility; Load Utility Features; Load Examples; Load Utility Syntax; Recovering from Load Failure / Load Pending; Load Utility and Referential / Check Constraints:; Load Authorities; Load / Import Differences; Tablespace Pending States; Backup Pending; Check Pending / Set Constraints; Running Set Constraints via the GUI; Replication - Data Propagator
BACKUP AND RECOVERY: Data Recovery Overview; Methods of Recovery; Logging Overview; Circular Logging; Archival Logging; Log File Information; Recovery History File; The Backup Utility; Backup File Names; Backup Examples; The Restore and Rollforward Utilities; Rollforward Utility / Rollforward Pending; Rollforward Considerations; Recovery Examples; Logging / Backup / Recovery Summary; The Quiesce Utility
APPLICATION PROGRAMMING OVERVIEW: Db2 Environments; Development Cycle For Embedded Sql in C; SQLJ Programming - Development Cycle; JDBC Drivers - The Universal Driver; Single Row Selects; Including External Sql into C Programs; Sql Include; Sql Communication Area; Host Variable Declaration in C Programs; Cursor Selects; Declare Cursor; Open Cursor; Fetch A Row; Row Update; Row Deletion; Close Cursor; With Hold Option; Fetch First Clause; The Optimize Statement; Precompiling A Program; Binding A Program; Levels Of Optimisation; Run Time Reoptimization - Bind Option REOPT
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; Lock Modes; Lock Example; Lock Compatibility; Isolation Levels; Lock Escalation; The Lock Table Statement; Commit Points; Lock Wait And Deadlocks; Savepoints; Savepoints - Considerations and Restrictions; The Quiesce Utility; SHARE
MONITORING: Error Logging; Database Monitoring; Snapshot Monitoring; Alerts; Monitoring Commands; Turning Monitoring Switches On; Event Monitoring; The Create Event Monitor Command; DB2 Trace Facility; Explain; Explain Facility Tools; The DB2 Explain Function; Explain Tables; The DB2EXPLN Applet; The Visual Explain Tool
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 the GUI; Privileges Required for Programming; Grant Examples; Revoke Examples
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; Automatic Statistics Collection; Automatic Statistics Profile Generation; The Reorgchk Utility; The Reorg Utility; Offline / Online Table Reorg; Index Reorg
SYSTEM PERFORMANCE: Database Performance Configuration Parameters; Data Sorts; Concurrent Application Tuning; Asynchronous Page Cleaner; Blocking Data; The Database Configuration Advisor
UDB TOOLS: The Control Center; Control Center Enhancements; Command Editor; Storage Management Tool; The Journal; The Development Center; Sql Assist Enhancements; Replication Center; Additional Wizards and Gui Tools
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

Course Format

The course contains many practical exercises to ensure familiarity with the product. Initially students create Tables and Indexes, and then proceed to develop Java or C programs accessing the data held on the DB2 Tables. On completion of this course students will be ready to start the development of application programs accessing DB2 data. 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.