Data Modeling with DB2 Training

DB2 copy

Need of Data Modeling 

  • What is data modeling and its significance?
  • What is Data Warehousing

Data Modeling

  • Conceptual Data modeling
  • Physical Data modeling
  • Logical Data modeling
  • Benefits and scope of data modeling

Multidimensional Data Modeling 

  • Basic Conceptual Architecture
  • Demonstrate fact and dimension
  • Difference between facts and dimensions

Concepts/Schema of Data Modeling

  • Dimensional Model Keys
  • Types of facts
  • Conformed dimensions
  • Star schema
  • Snow flake schema
  • Difference between Star schema and Snow flake Schema
  • Limitations of data warehousing

SCD Implementation methods 

  • Type 0 Fixed attributes
  • Type 1 Changing attributes
  • Type 2 Historical attributes

Introduction to RDBMS 

  • Evolution of RDBMS
  • 12 Rules of Codd
  • RDMS providers
  • Parts of database
  • Relational concepts – 1
  • Relational concepts – 2
  • Relational concepts – 3
  • Relational concepts – 4

RDBMS Vs Data warehouse and ETL Process 

  • OLTP vs. OLAP
  • What is ETL Process
  • Where ETL Process is used
  • Scope of ETL in Data warehouse

 SQL Parsing 

  • SQL Parsing-1
  • SQL Parsing-2
  • Compilation
  • Optimization-1
  • Optimization-2
  • Execution

Erwin-Design Layer Architecture 

  • What is ERwin Model?
  • Design Layer Architecture
  • Data Warehouse modeling
  • Creating and using ‘User Defined Domains’
  • Managing naming standards
  • Managing Data type standards

 Forward Engineering & Reverse Engineering 

  • An introduction to Forward Engineering (SQL Generation)
  • An introduction to Reverse Engineering
  • An introduction to Complete Compare

DB2 Course Contents 

DB2 Overview 

  • Introduction to DB2 for z/OS
  • An Overview of DB2 and Information management

DB2 Address Spaces  

  • System Services Address Space
  • Database Services Address Space
  • Distributed Data facility Address Space
  • IRLM Address Space
  • Stored Procedure Address Space

DB2 Concepts

DB2 data Structures

  • DB2 Tables
  • DB2 indexes
  • DB2 keys
  • DB2 views
  • DB2 schemas and schema qualifiers
  • DB2 table spaces
  • DB2 index spaces
  • DB2 storage groups
  • DB2 databases
  • DB2 Synonyms
  • DB2 Alias

DB2 system objects

  • DB2 catalog
  • DB2 directory
  • Active and archive logs
  • Bootstrap data set
  • Buffer pools
  • Data definition control support database
  • Resource limit facility database
  • Work file database

Normalization to avoid redundancy

  • First normal form
  • Second normal form
  • Third normal form
  • Fourth normal form

System Privileges and Database privileges

  • DB2 administrative authorities
  • SYSADM
  • SYSCTRL
  • DB2 administrative authority
  • DBCTRL

DB2 privileges 

  • Buffer pool privileges
  • Storage group privileges
  • Database privileges
  • Table space privileges
  • Table privileges
  • View privileges
  • Plan privileges
  • Package privileges
  • Stored procedure privileges

 DB2 commands   

 DB2 and related commands like 

  • START DATABASE (DB2)
  • STOP DATABASE (DB2)
  • DISPLAY DATABASE (DB2)
  • ACCESS DATABASE (DB2)

Catalog and Directory Tables like 

Catalog Tables:

  • SYSTABLES
  • SYSCOLUMNS
  • SYSDATABASE
  • SYSSTOGROUP
  • SYSTABLESPACE
  • SYSINDEXES etc …   in DB2 ver 9.1

Directory Tables:

  • SPT01
  • SCT02
  • DBD01
  • SYSLGRNX
  • SYSUTLIX

DB2 Utilities 

  • Introduction to the DB2 utilities
  • DB2 online utilities like
  • CHECK DATA
  • CHECK INDEX
  • COPY
  • COPYTOCOPY
  • LOAD
  • UNLOAD
  • RECOVER
  • REORG
  • REPAIR
  • MODIFY
  • MERGECOPY
  • REPARI, REPORT ECT…. All the Utilities….

DB2 stand-alone utilities like 

  • DSN1CHKR
  • DSN1COMP
  • DSN1COPY etc….

Performance Tuning 

  • Planning your performance strategy
  • Designing your system for performance
  • Programming DB2 applications for performance
  • Tuning your queries
  • Programming for concurrency
  • Working with PLAN_TABLE using EXPLAIN statement

SQL statements

Tools 

  • BMC Suite of products
  • Platinum suite of products
  • IBM suite of products
  • Omega on for performance tuning
  • JOB schedulers – CA-7, CONTROL-M