Db2 11.1 Quickstart for Experienced Relational DBAs

Overview of Db2 11

• Describe the system platforms supported by the Db2 Family of products
• Compare the features available with different Db2  product editions 
• Select the appropriate Db2 client software to support application client or server systems
• Choose a method to install your Db2 product edition and plan migrations of existing Db2 servers to the latest release
• Compare cloud-based Db2 options to a locally installed Db2 server

Db2 Command Line Processor (CLP) and GUI tools

• Utilize the Db2 Command Line Processor to run Db2 commands, SQL statements and command scripts
• Invoke the CLPPlus command line processor to connect databases and to define, edit, and run statements, scripts, and commands
• List some of the ways IBM Data Server Manager can be used to support Db2 database administration and SQL execution with Db2 servers 

The Db2 database manager instance

• Specify the key features of an instance
• Create and drop a Db2 instance
• Use db2start and db2stop commands to manage a Db2 instance
• Display and set Db2 registry variables
• Describe and modify the database manager configuration

Creating databases and data placement

• Plan the initial storage allocations for a database including catalog tables and log files
• Create a new database using Db2 native encryption
• Explore the System Catalog tables and views
• Check and update Database configuration parameter settings
• Compare DMS and Automatic Storage management for table space
• Differentiate between table spaces, containers, extents, and pages
• Create and alter table spaces
• Use Db2 commands and SQL statements to display current table space statistics and status information

Creating database objects

• Create schemas, tables, views, aliases, and indices
• Review the use of temporary tables,
• Explore the use and implementation of check constraints, referential integrity and triggers
• Use BLU Acceleration, column-organized tables to improve analytics query performance
• Explain the difference between system-period temporal tables and Application-period temporal tables
• List the types of compression available for tables and indexes
• Use the db2look utility to export database structures for future use

Moving data

• Discuss using the INSERT SQL statement to populate tables 
• Explain the differences between IMPORT and LOAD processing
• Explain the EXPORT, IMPORT, and LOAD command options
• Create and use Exception Tables and Dump-Files
• Check table status using LOAD QUERY
• Describe the ANALYZE phase of LOAD command processing used for loading BLU Acceleration, column-organized tables.  
• Check the Load Pending and Set Integrity Pending status for a table
• Use the SET INTEGRITY command
• Discuss the db2move and db2look commands
• Use the ADMIN_MOVE_TABLE procedure to move a table to different table spaces
• List some of the features of the Ingest utility for continuous data ingest

Backup and recovery

• Describe the major principles and methods for backup and recovery
• State the three types of recovery used by Db2
• Explain the importance of logging for backup and recovery
• Describe how data logging takes place, including circular logging and archival logging
• Use the BACKUP, RESTORE, ROLLFORWARD and RECOVER commands
• Perform a table space backup and recovery
• Restore a database to the end of logs or to a point-in-time
• Discuss the configuration parameters and the recovery history file and use these to handle various backup and recovery scenarios
• Create an encrypted backup image to improve data security

Database maintenance, monitoring and problem determination

• Plan the use of RUNSTATS, REORGCHK and REORG utilities for maintaining database efficiency
• Configure the Db2 instance to set the location for diagnostic data and message severity levels for basic problem analysis
• Describe the methods that can be used for monitoring database and application activity including db2pd commands, Event Monitors and using SQL statements to access statistics
• Describe the function of EXPLAIN and use this facility to assist basic analysis
• Use the db2advis command to analyze a workload for potential performance improvements
• Use the db2fodc command to collect diagnostic data for a system hang

Locking and concurrency

• Explain why locking is needed
• List objects that can be locked
• Describe and discuss the various lock modes and their compatibility
• Explain four different levels of data protection
• Set isolation level and lock time out for current activity
• Explain lock conversion and escalation
• Describe the situation that causes deadlocks
• Create a LOCKING EVENT monitor to collect lock related diagnostics
• Set database configuration options to control locking event capture

Security

• Use Db2 access control mechanisms to implement security within the database
• Explain the tasks performed by the SYSADM user, the SECADM user and a DBADM user
• Compare the use of database roles to user groups for security
• Describe privileges required for binding and executing an application package
• Describe the difference between explicit privileges and implicit privileges
• List the methods for implementing encryption for database connections
• List the advantages of creating a Trusted Context for a three-tier application system

Database rebuild support

• Review the considerations of using standard Db2 database recovery options
• Explain the capabilities of the REBUILD option for the RESTORE command
• List the types of information included in each Db2 backup image and describe how it is used to support rebuilding a database
• Plan for supporting database and disaster recovery scenarios using Db2 database and table space backups using the RESTORE command with a REBUILD option
• Utilize LIST UTILITIES SHOW DETAIL to monitor progress of a RESTORE utility during database rebuilding

Db2 Database and tablespace relocation

• Explain the facility of the Db2 RESTORE command to recover table spaces to different containers
• Use the SET TABLESPACE CONTAINERS command to define new containers during a redirected restore
• Utilize the SET STOGROUP PATHS command to change the storage paths for automatic storage tablespaces in storage groups
• Plan the use of redirected restore as part of a disaster recovery
• Describe two methods that can be used to convert a DMS table space to utilize Automatic Storage
• Use the GENERATE SCRIPT option of RESTORE to set up a command script for a redirected restore operation
• Copy schemas from one database to another using the TRANSPORT option of the RESTORE utility
• Use db2relocatedb when moving or copying Db2 databases with non-Db2 utilities

Using Explain tools

• Use the IBM Data Server Manager Explain tool to review access plans
• Examine access plan detailed information using the db2exfmt reports
• Create a set of Explain tables used by Visual Explain tools and db2exfmt
• Set the Explain mode special register to capture the Explain data for analysis of dynamic SQL statements
• Use the db2expln report to analyze access plans for dynamic or static SQL statements
• Utilize the Activities Event monitor to create Explain reports that include estimated and actual row counts

Using Indexes for performance

• Describe the Indexing options that can be used to improve performance:
• Index Only Access
• Clustered Index
• Reverse Scans
• Include Columns
• Index Freespace
• Describe the Block Indexing capability for MDC tables
• Monitor index usage using the MON_GET_INDEX function and db2pd commands
• Explain how multiple indexes can be combined using Index ORing and Dynamic Bitmap Index ANDing
• Use the Design Advisor to predict performance gains from adding new indexes or using MDC tables

Advanced monitoring

• Describe the infrastructure used to support monitoring
• Configure a database to collect the activity, request and object metrics returned by the Monitoring Table functions
• Investigate current application activity that might indicate performance problems using SQL statements
• Use the Db2-provided views and functions in SQL to evaluate efficient use of database memory for locks, sorting and database buffer pools
• Check database health indicators, like log space available and table space utilization using CLP queries using Monitor functions and views