Course Description

This 3-day, hands-on DB2/SQL Programming Intermediate training course presents the next level of sophistication in coding Structured Query Language (SQL) for the intermediate-level SQL programmer. The SQL set operations of UNION, INTERSECTION and DIFFERENCE are taught along with a focus on multi-table inner joins and outer joins. The SQL statements and the structure of the example DB2/COBOL program assist the participant in solving more complex business problems. Efficient coding techniques are presented to ensure the optimum use of DB2/SQL application and system resources. Hands-on workshops are used to reinforce concepts.

Pre-requisites

Attendance at our DB2/SQL Programming Introduction  training course, or equivalent experience.

Who is this course for?

This course is designed for software developers and other technical staff who need to learn more complex SQL and write sophisticated programs using IBM’s Database 2 (DB2) for z/OS.

Further Training

After some further on-the-job programming experience with DB2, we recommend students attend our
DB2/SQL Programming Advanced training course to gain higher-level skills in the usage of DB2/SQL.

Course content

Introduction
DB2 for z/OS
DB2 for z/OS Structure
Db2 Terminology
The DB2 Environments
DB2 Objects
The DB2 Catalog Tables
DB2I & SPUFI Setup
DB2I Primary Options Menu
DB2I Defaults Panel
Using SPUFI
SPUFI Defaults
SPUFI Edit Panel
SPUFI Panel after Edit
SPUFI Browse Panel
SPUFI COMMIT & ROLLBACK Panel
SQL Refresher
DDL Statement Examples
SELECT, INSERT, UPDATE, DELETE
SQL Expressions
AS Clause
SQL Column Functions
SQL Scalar Functions
SQL Dates & Times
Inner & Outer Joins
LEFT, RIGHT & FULL OUTER JOINS
Outer Joins & Nulls
Correlation Names
JOINs with Predicates
Nested JOINs
Subselects, EXISTS & UNION
Simple Subselect
Correlated Subselects
INTERSECTION & EXISTS
DIFFERENCE & NOT EXISTS
Subselects with Correlation Names
UNION Operation
FULL OUTER JOIN with UNION
CASE Expression
Data Integrity
Requirement Integrity (NOT NULL, WITH DEFAULT)
Entity Integrity (PRIMARY KEY)
Domain Integrity (CHECK Constraints)
Referential Integrity (FOREIGN KEY)
CASCADEd Operations
Embedded SQL
Required Program Areas
SQLCA
WHENEVER Statement
Singleton SELECT Program Example
Changing Data
Bind Variables
CURSOR Creation & Management
FETCH & FETCH Loops
Changing Data with a CURSOR
NULL Indicators
Scrollable CURSORs
Program Preparation
Overview
DCLGEN
DCLGEN Panel
Program Preparation Panel
Precompile
BIND PLAN
Compile, Link, Run
PLAN Information in the DB2 Catalog
Application Performance
DB2 Pages
DB2 Tablespaces, Simple, Segmented, Partitioned, LOB
DB2 Indexes & Indexspaces
COMMIT & ROLLBACK
Concurrency Problems
LOCKING Types
Lock Granularity
Isolation Levels
Lock Duration
Using EXPLAIN
The DB2 PLAN_TABLE
Selecting DATA
Analyzing DATA