Categories

There are currently no items in your shopping cart.

User Panel

Forgot your password?.

DB2 LUW – Database Administration & Certification Workshop

Video Introducing this tutorial

Introduction :
About Me - Your Instructor
Welcome to the course !
Introduction to DB2
DB2 Versions & Editions
DB2 Editions - Differentiation
Quiz Time
6 questions


Installing DB2 9.7 on Windows OS :
Understanding system requirements
Preparing machine for installation
Downloading DB2 9.7 from IBM's website
Installing DB2 9.7 on Windows
Installing DB2 9.7 using RESPONSE FILE


DB2 Architecture :
Section Overview
DB2 Enviroment - The Big Picture
DB2 Environment - Instances
DB2 Environment - Databases
Process Model
Memory Model
Storage Model
Quiz Time
10 questions


Exploring DB2 Tools :
First Steps - creating our first database
Control Center
Command Window
Command Line Processor (CLP)
CLP - Running Scripts in DB2
CLP - Obtaining Help Information
Command Line Processor Plus (CLP Plus)
Command Editor
Configuration Assistant - Connecting to Remote Databases
Journal
Task Center
Activity Monitor
Event Analyser
Health Center
DB2 Data Server Clients
Other Tools
Quiz Time
9 questions


Working with DB2 Database Objects - Part 1 - Tables & Views :
Section Overview
Definitions & Classification of Tables
Create Table statements
Tables with Identity Columns
Unique Constraints
Referential Integrity Constraints - Part I
Referential Integrity Constraints - Part II
Check Contraints
Alter & Drop Table statements
Materialized Query Tables (MQTs)
Quiz Time
20 questions


Working with DB2 Database Objects - Part 2 - Views :
Views - Introduction & Create View statement
DML operations on Views
Creating view(s) from multiple tables
Creating Views with Local Check Option
Creating views with Cascaded Check Options
More on Views
Quiz Time
5 questions


Working with DB2 Database Objects - Part 3 - Other Objects :
Aliases
Indexes - Concept
Indexes - Demo
Schemas - Concept
Schemas - Demo
Triggers - Concept
Triggers - Demo
Sequences - Concept
Sequences - Demo
Buffer Pools - Concept & Demo
Tablespaces - Concept
Tablespaces - Demo
Quiz Time
12 questions
Exercise 1 - Working with Database Objects
12 pages


DATA Types in DB2 :
Data Types - Classification
Numeric data types
String data types
CLOB & DBCLOB data types
BLOB - Inserting Images in Tables
Date, Time & Timestamp - Part 1
Date, Time & Timestamp - Part 2
XML data type


Querying DB2 Data - SELECT Statement & Clauses :
SELECT statement - clauses
Retrieving Specific Columns
ORDER BY clause
GROUP BY clause
GROUP BY ... HAVING clause
FETCH FIRST n ROWS ONLY clause
DISTINCT clause
Working with NULL values
Sub-Queries or Inner Queries
Exercise 2 - Working with Sub-Queries or Inner-Queries
7 pages


Limiting Data - WHERE clause :
Comparison Operators
AND / OR predicate
BETWEEN & NOT BETWEEN predicate
IN & NOT IN predicate
IS NULL and IS NOT NULL
LIKE & NOT LIKE predicate - Pattern Search
Exercise 3 - Writing simple SELECT queries
12 pages


Some Useful Functions & Operators in DB2 :
COUNT & COUNT(*)
ARITHMETIC Operators
SCALAR or ROW functions
VECTOR or COLUMN functions
COALESCE function - Arithmetic with NULL Values
SUBSTR - Substring of Strings
CONCAT operator
ROW_NUMBER() OVER() and ROWNUM function
Exercise 4 - Working with Scalar Functions & Arithmetic
12 pages
Exercise 5 - Working with Vector or Column Functions
8 pages


The UNION, INTERSECT, EXCEPT Operators :
What are UNION, INTERSECT & EXCEPT clauses?
UNION & UNION ALL
INTERSECT & INTERSECT ALL
EXCEPT & EXCEPT ALL


Types of JOINS in DB2 :
INNER Join
LEFT OUTER Join
RIGHT OUTER Join
FULL OUTER Join
CARTESIAN PRODUCT
Exercise 6 - Retrieving Data from Multiple Tables (INNER JOINS)
12 pages
Exercise 7 - Working with OUTER joins
8 pages


Maintaining DB2 Data - DML Statements :
INSERT statements
UPDATE statements
DELETE statements
TRUNCATE statement
Exercise 8 - Maintaining Data Using Inserts , Updates and Deletes
7 pages


Let's Play - DB2 Detective Game ! :
Setting up the Detective Game


DB2 Server - Administering and Working with Instances :
Understanding Instances - Practically
Starting Instances - Multiple Ways
Stopping Instances - Multiple Ways
Listing and Creating Instances - db2ilist & db2icrt commands
Working with Databases under different Instances
Dropping an Instance - Considerations
Configuring Instances - Via DBM CFG file and Control Center
More on DBM CFG file


DB2 Server - Administering and Working with Databases - Part 1 :
Connecting to Databases - via ALIAS names
CONNECT vs ACTIVATE- Connecting to Databases
CONNECT RESET vs TERMINATE - Disconnecting from Databases
Reviewing DB2 Directory Hierarchy - OS File System
Directories and Connectivity - System Database Directory
Directories & Connectivity - Local Database Directory
Directories & Connectivity - Node Directory


DB2 Server - Managing Database Storage :
Introduction to Storage
Listing Tablespaces and Containers
Creating Automatic Storage tablespaces
Altering Automatic Storage Tablespace
Creating DMS tablespaces
Altering DMS Tablespace
Creating SMS tablespaces
Altering SMS Tablespace
Reclaiming Unused Storage in Tablespaces
Page, Extent and Prefetch Sizes
Dropping a Tablespace and Containers


DB2 Server - Administering and Working with Databases - Part 2 :
Creating Automatic Storage Databases
Creating Manually Managed Databases
Creating Database from Backup Image
Querying the ACTIVE databases
Listing Applications
Forcing Applications OFF
Querying Database Size - get_dbsize_info procedure
Dropping Databases
Re-creating SAMPLE database - db2sampl script
Configuring Databases - Via DB CFG file and Control Center


DB2 Security - Authentication :
DB2 Security - Introduction
DB2 Security Model
DB2 Users and Groups - Creating a new user
DB2 Users and Groups - DB2USERS
DB2 Users and Groups - DB2ADMNS
DB2 Users and Groups - ADMINISTRATORS
Authentication Types in DB2
Setting authentication type at SERVER
Setting authentication type at CLIENT


DB2 Security - Authorization :
Authorization - Authorities in DB2
Managing SYSADM Authority - via SYSADM_GROUP group
Managing SYSCTRL Authority - via SYSCTRL_GROUP group
Managing SYSMAINT Authority - via SYSMAINT_GROUP group
Grant and Revoke authorities/privileges to/from PUBLIC group
Managing DBADM Authority - via GRANT command
Managing DBADM Authority - via REVOKE command
Managing SECADM authority - via GRANT command


DB2 Security - Privileges :
Introduction to Privileges
Grant/Revoke - Tablespace Privilege
Grant/Revoke - Schema Privileges
Grant/Revoke - Tables and Views Privileges
Grant/Revoke - Index Privileges
Grant/Revoke - Sequence Privileges
Granting privileges WITH GRANT OPTION


DB2 Security - Miscellaneous :
Role Based Access Control (RBAC)
System Catalog Tables related to Security
Managing Security from Control Center
DB2 Security - 12 Best Practices


DB2 Backup & Recovery - Introduction :
Introduction to Backup and Recovery
Performing basic backup - BACKUP DATABASE command
Performing basic recovery - RESTORE DATABASE command


DB2 Backup and Recovery - Transactional Logging :
Introduction to Logging
How logging works in DB2
Log File states
Primary and Secondary Log Files
Types of Logging in DB2
Configuring Circular Logging
Configuring Archival Logging
Increasing Log file Size - LOGFILSIZ parameter
Changing Log File Path - NEWLOGPATH parameter


DB2 Backup and Recovery - Backup :
The BACKUP DATABASE command
Performing Online Vs Offline backup
Performing Full Vs Partial backup
Performing Incremental Vs Delta backup


DB2 Backup and Recovery - Recovery :
Introduction to Recovery
Performing Crash Recovery
Performing Version Recovery
Understanding ROLLFORWARD RECOVERY
Performing Roll-Forward Recovery - Point In Time Recovery
Performing Rollforward Recovery - End of Logs Recovery
RECOVER DATABASE command and Recovery History File


DB2 Server - Data Concurrency - Introduction :
Introduction to Concurrency Concepts
Understanding Transactions
Commit, AutoCommit and Rollback
Savepoints


DB2 Server - Data Concurrency - Phenomena in Multiuser Environment :
Introduction to Phenomena in Multiuser Environments
Lost Update
Uncommitted/Dirty Reads
Non-Repeatable Reads
Phantom Reads


DB2 Server - Data Concurrency - Isolation Levels and Locking :
Introduction to Locking
Retrieving lock information using db2pd command
Introduction to Isolation Levels
Querying and Setting isolation level
Repeatable Read
Repeatable Read - Demo
Read Stability
Read Stability - Demo
Cursor Stability
Cursor Stability - Demo
Cursor Stability with Currently Committed
Cursor Stability with Currently Committed - Demo
Uncommitted Read
Uncommitted Read - Demo


DB2 Server - Data Concurrency - More on Locking :
Lock Attributes
Lock States/Modes - types of locks in DB2
Lock States/Modes - table and row level
Lock Compatibility
How locks are aquired
LOCKTIMEOUT parameter
LOCKLIST parameter
MAXLOCKS parameter
Lock Conversion
Lock Escalation
Deadlocks


Data Movement Utilities :
EXPORTing data in DEL format
EXPORTing data in IXF format
IMPORTing data from DEL format
IMPORTing data from IXF format
LOAD Utility


DB2 9 Family Fundamentals - Preparing for Certification Exam :
Exploring IBM DB2 certification paths
Tips and Tricks for clearing the certification
DB2 Certification Practice Questions & Answers


DB2 Case Studies :
Reliance cuts database total cost of ownership in half with IBM DB2
6 pages
Coca-Cola Bottling Co. Consolidated migrates from Oracle to DB2
12 pages
University of Toronto benefits from IBM DB2 with BLU Acceleration
4 pages
Sabah Credit Corporation satisfies investors With IBM DB2 and SAP
4 pages
YAZAKI Europe Limited moves faster with DB2
4 pages


Download Learning Resources for DB2 :
Download these PDFs


Bonus Section - High Availability and Disaster Recovery (HADR) :
Introduction to HADR
HADR System Requirements
HADR Setup
Configuring HADR on primary
Configuring HADR on secondary
Starting HADR on Primary and Standby
HADR Database States
HADR SYNC Modes
Switching server roles using TAKEOVER HADR command
Enabling STANDBY for read-only operations
Deactivating and Stopping HADR


DB2 10.x/11.x - Upgrade Roadmap :
Upgrade Overview
Changes and New Features in DB2 10.x/11.x
Downloading the Required Setups


Preparing the Environment :
Installing VMware Workstation
Creating a Virtual Machine - Installing Ubuntu
Some Important Points to Consider
Moving Setup Files to VM


Linux Basics for DB2 :
Overview
Exploring Ubuntu Desktop
Understanding the Linux File System
Exploring LFS via Graphic File Manager
Navigating the LFS via Terminal
Unlocking the ROOT user
User and Group Management
Creating and Removing Directories
Understanding Permissions in Linux
chmod command
Installing Applications and few Utilities on Linux
Some Important Tips


Installing DB2 10.1 and Data Studio 4.1.2 :
Preparing the setup files
Installing DB2 10.1 on Linux (Ubuntu)
Installing Data Studio 4.1.2 on Linux (Ubuntu)


Exploring DB2 on Linux :
Important Directories and Files related to DB2
The DB2 Shell
Executing DB2 commands
Installing DB2 Fixpacks
Setting DB2 Environment for Linux Users


DB2 v10.1 to DB2 v11.1 - Upgrade (Linux) :
Overview
Creating Instances and Databases on Linux
Performing Pre-Upgrade tasks
Installing DB2 v11.1 on Linux
Upgrading v10.1 instances - db2iupgrade command
Upgrading v10.1 databases - database upgrade command
Summarizing the upgrade process
Uninstalling DB2 v10.1


Exploring Data Studio :
Introduction
Perspectives and Views
Creating DB connection in Data Studio
Creating Objects in Data Studio


Adaptive Compression :
Introduction to Table Compression in DB2
Understanding Classic Row Compression
Understanding Adaptive Compression
Understanding Compression Dictionaries
Revealing Compression Settings for Tables
Enabling and Working with Classic Row Compression
Enabling and Working with Adaptive Row Compression
Disabling Compression on Tables


Reviewing DB2 Security and RCAC on Linux :
Overview
Cataloging Instances and Databases
Managing Instance and Database level authorities
Introduction to Row and Column Access Control (RCAC)
Creating and Managing Row Permissions in RCAC
Creating and Managing Column Masks in RCAC
Disabling and Deactivating RCAC rules


Storage Groups and Multi-temperature Storage :
Storage Overview
Storage Groups and Multi-temperature Storage
Reviewing Storage Information for Database
Creating Storage Groups and assigning Tablespaces
Alter, Rename and Set Default - Storage Groups