Objectives

At the end of this course, the participant will:

• have a clear overview of the 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.

Main topics

• 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 design: primary keys, foreign keys, check constraints, triggers, sequence objects, identity columns, distinct types, XML, LOBs, views and MQTs
• 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 to DB2 objects:
• scenarios for efficient security management and auditing requirements
• details regarding the different possibiliities: owner-, role-, multilevel-, or object-based; trusted context
• plans, packages, and bind options0
• The Catalog and the Directory:
• contents • usage • management
• effective use of the real-time 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 • runstats & stospace
• Related aspects:
• dynamic allocation of datasets (with or without SMS)
• (re)partitioning of tables; moving data to other volumes
• 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
• 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 data spaces or selective pages
• point-in-time recovery and disaster recovery: best practices