Db2 for Linux, UNIX & Windows - Database Administration

What you will learn

On successful completion of this course you 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
• understand and resolve locking issues
• monitor database activity
• investigate system, application and SQL performance issues
• perform database replication tasks.

Who Should Attend

All those who will be working as database administrators in a Db2 Database (i.e. LUW) environment.

Prerequisites

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

Duration

5 days

Fee (per attendee)

£2250 (ex VAT)

This includes free online 24/7 access to course notes.

Hard copy course notes are available on request from rsmshop@rsm.co.uk

at £50.00 plus carriage per set.

Course Code

UD03

Contents

Getting Started with Db2 Database (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; CreatingrRestrictive 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; 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; 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 Uutilities; 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; ablespace 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; High Availability Disaster Recovery (HADR) overview; HADR limitations / restrictions; HADR setup; HADR states; HADR Takeover.

Application Programming Administration

Db2 environments; Development cycle for embedded Sql; 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.

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; Monitoring using the IBM Data Studio Web Console; Web Console Access; Web Console - Health Summary; Web Console - View Alerts; Web Console -Configuring Alerts; Web Console - View Application Connections; Web Console - Monitor Tablespaces; Web Console - Monitor Utilities; Web Console - Job Manager; Other Diagnosis Features - Diagnosis of Lock Timeout; Lock Timeout Report File Example; Other Diagnosis Features - Db2PD Command.

SQLPerformance 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.