DB00 - Introduction to DB2 - 2 Days

Course Description

This course provides the delegate with an overview of the DB2 development process. It is aimed at those who need to understand the concepts and capabilities of DB2 at a high level.


Pre-requisites

Knowledge of the z/OS host environment is advantageous but not essential.


Objectives

The aim of this course is to provide the delegate, unfamiliar with DB2, with an overview of its main features.

On completion of this course the student will be able to:

  • describe the DB2 environment
  • describe the process of table / data creation
  • understand the role of Data Definition statements
  • understand SQL Data Manipulation statements
  • understand the program development, preparation and bind processes


Environment

DB2 for z/OS Versions 10, 11 or 12.


Customisation

For on-site courses (i.e. at your premises), we are more than happy to tailor the course agenda to suit your exact requirements. In many cases, we are able to build your in-house standards and naming conventions into the delivered course.


Course Details

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 Join
Outer Joins
Outer Join Syntax
Joining More Than 2 Tables (using Newer Syntax)
Outer Join - Where Clause
Nested Table Expression
SQL Union
Subqueries
Subqueries Using In
Exists
The 'All' Subquery
The 'Any' Or 'Some' Subquery
Common Table Expressions (V8)
Common Table Expression Example
Recursive SQL (V8)
Recursive SQL Example
Recursive SQL - Controlling Depth of Recursion

APPLICATION PROGRAMMING OVERVIEW
DB2 Environments
Development Cycle With DB2
SQL Statement Format
Cursor Operations
SQL Statements Used in Application Programs
SQL Error Codes
Batch Execution
Plans & Packages


Course Format

DB2 can be used if available to demonstrate some of the topics discussed such as Data Manipulation using SELECT, INSERT, UPDATE and DELETE statements. On completion of this course students will be able to describe the main features of DB2 but will not have the required hands-on experience required to develop full DB2 application programs.

The comprehensive Student Guide supplied is fully indexed serving as a useful reference tool long after the course has finished. Delegates will also be able to access a free help-line with technical questions relating to topics covered on the course.


Course Enquiry