Db2 for Linux, UNIX & Windows - Database Administration

This five-day course covers all of the key tasks normally performed by a DBA working in a Db2 Database (LUW) environment.
For exclusive, one-company presentations (either at your premises or presented live, over the Internet), 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.

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


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


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



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.


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.


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.


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.


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.

© RSM Technology 2022