IBM DB2 Course

 

IBM DB2 Course Overview

Db2 is an information management system planned by IBM to enable you to oversee both organized and unstructured information on private and open cloud conditions. Db2 is based on a clever basic SQL for versatility and adaptability. Initially discharged by IBM in 1983 as the organization’s first industrially accessible social database, Db2 keeps on conveying development as an industry-driving stage for proficient cross platform information management. It drives high-sway information bits of knowledge, consistent business coherence and genuine business change.

The Db2 database management transformation proceeds with elite arrangements fueled by AI to empower your AI activities. The DB2 have been reached out to help object-relational features and structures like JSON and XML.

DB2 is a Relational Database Management System (RDBMS). DB2 is an integral part of IBM’s information management portfolio. It is a full-featured, high-performance database engine capable of handling large quantities of data and concurrently serving many users.

Students, who are not comfortable with programming, can choose this course for a highly paid career in the field of databases. To start their careers in the field of Database Administration on IBM DB2.

A prior basic knowledge of RDBMS would be helpful. Ability to write basic SQL statements would be a plus, although covered thoroughly into the course.

Many companies are using DB2, but it’s difficult to find DB 2 professional as there are plenty DBA professionals available. If you complete this course there are chances getting jobs in these companies IBM, Deloitte, McDonald’s, VISA, Accenture, Open Systems Technologies , Walmart , TCS, Infosys, CSC, XEROX, HP, Guardian Life, Capgemini, WIPRO.

This course covers Overview of DB2, Command Line Processor (CLP) and GUI usage, The DB2 Database Manager Instance, Creating databases and data placement, Creating database objects, The DB2 Database Manager Instance, Database Maintenance and Monitoring.

IBM DB2 Course Syllabus

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)

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

The DB2 Database Manager Instance

  • 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

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 tablespace 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

Creating database objects

  • 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

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

Backup and recovery

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

 

Database Maintenance, Monitoring

  • 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
  • log diagnostic log
  • db2trc command to obtain trace
  • db2fodc: collect diagnostic data for system hang
  • db2advis: Analyze workload

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

Security

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

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

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

Advanced Monitoring

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

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