At the end of this course, the participant will:

• have a clear overview of the very versatile tasks of a DBA in Db2;
• have a thorough technical insight into the physical and logical structure of the different objects in Db2;
• have learned to translate a logical data model into a physical database in test/acceptance/production environments;
• have practical skills to define and prepare procedures for the management of the databases.

PART 1: Database Implementation

• Overview of the versatile tasks of a DBA.
• Physical design and implementation of Db2 objects:
  • Db2 object definition: decision rules
  • Possibilities for the implementation of a logical and physical design:
    • normalisation, datatypes, referential integrity (primary/foreign keys);
    • check constraints, triggers, sequence objects and identity columns;
    • XML, LOBs, views, aliases, ...;
    • index and tablespace design;
    • partitioning;
    • "time travel" tables
  • schema evolution: possibilities of ALTER (esp. for tablespaces and tables); pending changes
  • implementing a test environment: DDL • test data • statistics
  • preparing for migration
  • prepare the database for performance analysis (but see also course Db2 for z/OS SQL performance)
• Access control on Db2 objects:
  • scenarios for efficient security management and auditing requirements
  • details regarding the different possibilities: owner-based, role-based, multilevel-based, or object-based; trusted context; "separate security" possibilities (incl. row permissions and column masks)
  • RACF-based access control
  • plans, packages, and bind & rebind options
• The Catalog and the Directory:
  • contents • usage • management
  • effective use of the real-time statistics (RTS)
  • manipulation of a.o. optimizer statistics

PART 2: Database maintenance & integrity control

• Data Utilities:
  • Using utilities: DB2I & JCL • listdef and templates • exec sql • tablespace sets • terminate and restart
  • online reorg • load & unload • check data & check index • rebuild index • runstats • statistics profiles • catmaint
• Related aspects:
  • dynamic allocation of datasets (with or without SMS)
  • (re)partitioning of tables; moving data to other volumes
  • the use of data compression
  • possibilities of and potential problems with foreign keys

PART 3: Database recovery

• Internal physical structure of Db2 data objects:
  • tablespaces and indexspaces, partitions; page sets and fan sets; VSAM; BSDS
  • using DSN1PRNT and DSN1COPY
• Db2 backup and recovery utilities & service aids:
  • using utilities & service aids to backup, recover & verify integrity of data
  • inspect and copy the contents of Db2 datasets or selective pages
  • point-in-time recovery and disaster recovery: best practices

Theory with ample room for practical exercises.

Delivered as a live, interactive training – available in-person or online, or in a hybrid format. Training can be implemented in English, Dutch, or French.

Sufficient practical experience with Db2 (see course Db2 for z/OS fundamentals course).

New database administrators on Db2, and other Db2 users occupied with (part of) the tasks of a DBA.

Database administrators of Db2 who want a refresh, specifically regarding new possibilities in versions 12 or 13.