DB08 - DB2 SQL Workshop - 2 Days

Course Description

This training course teaches the delegate all SQL statements required to read, manipulate and join DB2 tables. It is aimed at those who have little or no previous SQL experience.

Pre-requisites

A working knowledge of the z/OS ISPF host environment is advantageous but not essential.

Objectives

The aim of this course is to provide the delegate with the necessary skills to perform simple and complex queries using DB2I / SPUFI.

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

  • understand how tables are defined
  • describe the data types available when defining DB2 columns
  • describe the importance of an Index for certain queries
  • use DB2I and SPUFI
  • write SELECT, UPDATE, DELETE and INSERT SQL statements
  • join tables together
  • use inner joins and outer joins
  • write non-correlated and correlated subqueries
  • use DB2 functions and the CASE statement

Environment

Development will be performed using:

  • IBM Mainframe
  • 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

INTRODUCTION TO DB2
The Relational Model
Data Representation
The DB2 Environment
Data Definition Language
The DB2 Table
Column Types
INTEGER
SMALLINT
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 Merge
Select from Update
Select from Delete
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
Intersect and Except
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

Course Format

The course contains many practical exercises to ensure familiarity with the product. Students write many queries to read, join and manipulate DB2 data.

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