DB2 UDB for z/OS and OS/390 SQL and Application Coding
 

This 5 day course is intended for:

Lead Application Programmers, Application Programmers, Data Base Designers, Data Base Administrators and Business Analysts that require a working knowledge of SQL and SQL application coding.

Topics Covered:

• z/OS and OS/390 DB2 Environment
• Structured Query Language
• Coding Static SQL Programs
• Utilizing the EXPLAIN for tuning
• Locking and application program

Objectives:

Students will write SQL statements, run SQL statements interactively, write SQL application programs, prepare programs and execute them and work with the EXPLAIN to evaluate SQL performance.

Duration: 5 days

Delivery: Class Lecture and Hands-on Labs

Audience: 

Lead Application Programmers, Application Programmers, Data Base Designers, Data Base Administrators and Business Analysts.

Prerequisites:

Student should have a working knowledge of TSO/ISPF or be familiar with an SQL Query Tool.

Outline

1)     DB2 Concepts

DB2 Environment
Data Structures
Structured Query Language
Application Processes, Concurrency and Recovery
DB2 Interactive

2)     Basic Single Table SQL

Data Retrieval
Result tables
SELECT Statement Structure
Selecting Columns: SELECT
Selecting Rows using search conditions: WHERE
Using Functions and Expressions
Ordering the result table rows: ORDER BY
Merging intermediate results: UNION
Special Registers

3)   Working with Multiple Tables

Joining Data from multiple Tables
Inner join
Full Outer Join
Left Outer Join
Right Outer Join
Using multiple join types in a Query
Nested Table Expressions

4)   Summary Processing and

Data Modifications
Summary results: GROUP BY
Limiting Groups: HAVING
Using Subqueries
How to code subqueries
Correlated subqueries
Non-correlated subqueries
Modifying Data

5)     Static SQL in a Host Application

Coding Conventions for Embedded SQL
Generating and using table Declarations: DCLGEN
SQLCA
SQLCODE handling
Host Variables
Indicator Variables
Singleton Select's
Delete, Update, Insert

5)     Preparing your Program for Execution

DB2 Precompiler
Compile Your Program
Link Edit
Bind : Package
Bind : Plan
Output Listings
Problem Determination

7)     Cursor Processing

Declare a Cursor
Open, Fetch, Close a Cursor
Deleting, Updating using a Cursor
Cursor with Hold
Cursor for Read Only

8)     Introduction to Performance

What is the Optimizer?
Predicate Types (Indexable, Stage 1 and Stage 2)
Access Paths and how they work
Explain - the Basics

9)   Transaction Locking Overview

Four Basic aspects of Locks
Bind Options
Acquire/Release Options
Isolation Options
CURRENTDATA - Lock Avoidance Factors