Audience
All personnel who will be working with Db2 and require a comprehensive overview of the functions of Db2, the Db2 product family (up to the latest release of Db2) and how to utilise the DB2 environment under all platforms (Linux, UNIX, Windows and z/OS). The course is relevant to all versions of Db2 up to the latest Db2 13 release.
Prerequisites
There are no required prerequisites, but a basic understanding of relational databases is useful.
Duration
2 days. Hands on.
Course Objectives
This course is a basic introduction to Db2, IBM's Relational Database Management System. It will present to the student a comprehensive overview of the Db2 Product. Db2 as a formal DBMS (Data Base Management System) is presented in a manner that will enhance the attendees utilisation and implementation of Db2. The course includes an introduction to Db2 and how it performs as both a formal DBMS and a Relational DBMS; as well as presenting the facilities that are included in the Db2 product.
After taking this course the student should be able to understand:
- The objects that comprise a Relational Database.
- The components that make up the Db2 Product.
- The tabular format of data, and how these tables are related.
- The DB2 Environment and its associated attachments.
- The functions of the Structured Query Language (SQL).
- Execution Environments.
Course Content
DB2 OBJECT DESIGN AND DEFINITION OVERVIEW
The Relational Model / Data Modelling
Data Representation
Design Issues
The Db2 Environment
Data Definition Language
The Db2 Table
Table Characteristics
Column Types
INTEGER
SMALLINT
BIGINT
DECIMAL
CHAR
VARCHAR
DATE
DATE formats
TIME
TIMESTAMP
Null Values
Default Values
Indexes
Index Columns
Tablespace scan vs. Index access
Db2 INTERACTIVE (DB2I)
DB2I Options
SPUFI - SQL Processor Using File Input
Running Queries
SPUFI Defaults
SPUFI - Setting Autocommit to NO
DB2 Commands
DB2 Utilities
The DB2I Defaults Panel
DATA MANIPULATION LANGUAGE
SQL - Structured Query Language
DB2 Environments
SQL Features
SQL Query Results
The Select Statement
The 'As' Clause
The Where Clause
Special Operators
Not Operand
In Operand
Like Operand
Between Operand
Statements Using Nulls
Is Not Distinct From
Column Functions
Using 'Distinct'
Multiple Distinct
Group By Clause
Expressions / Functions in Group By
Having Clause
Order By Clause
Fetch First 'n' Rows Only Clause
Special Registers (Date, Time, Timestamp And User)
Current Date
Current Time
Current Timestamp
User Keyword
The Update Statement
Update with Subselect
The Delete Statement
The Insert Statement
The Mass Insert Statement
Select from Insert
Select from Insert Example
Scalar Functions
Function Examples
Date, Time And Timestamp Functions
The Case Statement
Table JoinOuter JoinsOuter Join Syntax
Joining More Than 2 Tables (using Newer Syntax)
Outer Join - Where Clause
Nested Table Expression
SQL UnionSubqueries
Subqueries Using InExists
The 'All' Subquery
The 'Any' Or 'Some' Subquery
Common Table Expressions
Common Table Expression Example
Recursive SQL
Recursive SQL Example
Recursive SQL - Controlling Depth of Recursion
APPLICATION PROGRAMMING OVERVIEW
Db2 EnvironmentsDevelopment Cycle With DB2
SQL Statement Format
Cursor Operations
SQL Statements Used in Application Programs
SQL Error Codes
Batch Execution
Plans & Packages
