Course Description
This 3-day, hands-on DB2/SQL Programming Advanced training course presents advanced coding and application programming techniques for Structured Query Language (SQL) in the DB2/SQL for z/OS environment. All types of SQL operations and statements are analyzed and ways of improving application efficiency are discussed. Efficient coding techniques are presented that ensures the optimum use of DB2 application and system resources, including new features of the latest DB2 subsystem. Hands-on workshops are used to reinforce concepts.
Pre-requisites
Attendance at our DB2/SQL Programming Intermediate training course, or equivalent experience.
Who is this course for?
This course is designed for technical staff who need to learn advanced SQL statements and utilize new and advanced features of 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 Intermediate training course to gain higher-level skills in the usage of DB2/SQL.
Course content
Introduction
DB2 for z/OS Structure
DB2 Terminology
The DB2 Environments
DB2 Objects
The DB2 Catalog Tables
SQL Refresher
DB2 Datatypes
SELECT, INSERT, UPDATE, DELETE
SQL Expressions
AS Clause
SQL Column Functions
SQL Scalar Functions
Date Calculations
LEFT, RIGHT & FULL OUTER JOINS
Simple Subselect
INTERSECTION & EXISTS
DIFFERENCE & NOT EXISTS
Subselects with Correlation Names
UNION Operation
Complex SQL
Nested Table Expressions
Relational Product & Percentages
Relational Division
Returning a Portion of a ResultSet
Common Table Expressions
Recursive Queries
Embedded SQL
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
Packages
DB2 Packages
User-Defined Functions
Sourced Functions
Triggers
BEFORE TRIGGERs
AFTER TRIGGERs
INSTEAD OF TRIGGERs
CREATE TRIGGER Statement
SIGNAL SQLSTATE
SET Transition-Variable
VALUES
Remote Stored Procedures
Creating an RSP
CREATE PROCEDURE (External) Statement
CALL Statement
ASSOCIATE LOCATORS
ALLOCATE CURSOR
DECLARE CURSOR
Using NULL & DB2SQL Parameters