IBM DB2 LUW Training

IBM DB2 LUW Course Content :

1. Overview of DB2 on Linux, UNIX and Windows

• DB2 family products overview (LUW, Mainframe, iSeries)
• DB2 LUW product editions (Express-c to PureScale to InfoSphere)
• DB2 product features and functions
• DB2 client connectivity options (Data Studio, Data Server Client)

2. Command Line Processor (CLP) and GUI usage

• Introduction to DB2 CLP environment
• Non-interactive mode
• Interactive mode
• db2 command line options
• Client connectivity from Data Studio

3. The DB2 Database Manager Instance

4. About DB2 Instance

• Database Manager Instance overview
• DB2 instance creation pre-requisites
• Create and Drop an instance
• Starting and Stopping an instance
• list and modify DBM configuration
• db2set command
• db2 history command
• DB2 registry and environment variables

5. Product installation, extract and instance creation

6. Creating databases and data placement

• Database creation pre-requisites
• Database storage requirements
• Table spaces overview
• Storage management overview (SMS, DMS, Automatic)
• Default Table spaces
• CREATE DATABASE command
• List DB configuration parameters
• Modify DB configuration parameters
• Database directories and files introduction
• System catalog tables and views
• Containers, extents, pages overview
• CREATE TABLESPACE syntax
• Storage groups overview
• CREATE STOGROUP syntax
• Assigning a table space to a storage group
• Introduction to db2pd command
• ALTER TABLESPACE, ALTER STOGROUP
• DB2 LIST DB DIRECTORY
• DB2 LIST NODE DIRECTORY
• Activate and deactivate a database

7. Creating databases and data placement

8. Creating database objects

9. DB2 object hierarchy

• CREATE SCHEMA
• CREATE TABLE, VIEW
• CREATE ALIAS, INDEX, TRIGGER
• creation of temporary tables
• Creation of temporal tables
• Table partitioning
• Referential Integrity
• Keys, Constraints
• db2look CLI command
• Authorization & Authentication

10. Creation of table spaces, tables, databases

11. Moving data

• IMPORT versus LOAD
• Offline versus online load
• LOAD QUERY TABLE
• DB2 LIST UTILITIES
• Pending states (Load, backup, set integrity)
• EXPORT command
• db2move cli command
• ADMIN_MOV_TABLE procedure
• Ingest utility

12. Moving data using Export, Import, Load

13. Backup and recovery

• Transactional logging (Circular, Archival)
• Configure database logs
• Backup utility options
• Recovery utility options
• ROLLFORWARD
• DB2 RECOVER DATABASE
• ONLINE versus OFFLINE backup
• Backup and recovery

14. Database Maintenance, Monitoring and Problem Determination

• RUNSTATS utility
• REORGCHK utility
• REORG Utility
• DB2 List commands
• db2pd – Monitory and troubleshoot DB2
• MON_GET_TABLE function
• CREATE EVENT MONITOR
• DB2 Optimizer
• DB2 EXPLAIN
• db2diag.log diagnostic log
• db2trc command to obtain trace
• db2fodc: collect diagnostic data for system hang
• db2advis: Analyze workload

15. Using above DB2 Tools/Utilities

16. Locking and concurrency

• Importance of locking
• Table lock modes
• Row lock modes
• Lock mode compatibility
• Isolation levels
• LOCK TABLE statement
• Lock escalation, wait, and timeout
• Deadlocks and detection

17. Using db2pd to identify locks, wait locks, deadlocks

18. Security

• Authentication versus Authorization
• SYSADM, SYSCTRL, SYSMAINT, SYSMON
• DBADM, SECADM, ACCESSCTRL
• DB2 security privilege hierarchies
• Grant and Revoke privilege

19. Database Security using grant/revoke

20. Log Management

• Database seeds
• Log sequence numbers
• db2ckbkp -l command
• logfilsiz, logbufsz
• Logprimary and Logsecond
• db2pd to check logging activity
• Log Archive processing
• Log retrieval processing
• MAX_LOG configuration parameter

21. Log archive/retrieval process

22. Performance Tuning

• Analyze SQL statements
• Create an access plan graph
• db2exfmt tool
• db2expln tool
• Using Indexes for performance
• Index usage statistics using db2pd
• MON_GET_INDEX
• MON_CONNECTION_SUMMARY view
• Clustering indexes
• db2advis command

23. Explain Tools, Indexes creation

24. Advanced Monitoring

• GET SNAPSHOT command
• In-memory metrics
• Monitoring table functions
• Administrative views, functions
• Monitoring performance with SQL
• Monitor heath with SQL

25. In memory metrics, SQLS, Admin views, functions

26. High Availability with DB2 Database products

• High Availability options in DB2
• DR options in DB2
• High Availability Disaster Recovery (HADR)
• High Availability through Log Shipping
• Log mirroring
• High Availability through suspended I/O and online split mirror support

27. DB2 HADR setup (1 primary, 1 standby creation, setup, communication)