DB2 for Programmers z/OS to LUW Transition

Course Objectives
The aim of this Course is to provide the programmer, already familiar with DB2 on a z/OS platform, with the necessary skills required to define, access and manipulate DB2 data in a Windows environment. On completion of this Course the student will be able to:

• set up a DB2 test environment, using correctly defined tables, views, indexes, synonyms and aliases
• code SQL statements to read and manipulate DB2 data
• develop, prepare and execute DB2 programs
• bind packages
• use non-scrollable and scrollable cursors
• describe the locking process used by DB2
• use the DB2 for Windows GUI Toolset
• use the Command Line Processor
• code and install Stored Procedures
• code and install User Defined Functions
• populate tables using DB2 Utilities
• monitor application performance

Course Details
GETTING STARTED WITH UDB
The UDB Environment
Accessing DB2
UDB Product Family
UDB Interactive Tools
Setting up Initial Security
Windows NT Setup - Creating a new Administrator / Group
What is an Instance?
Setting up Instances
Configuring Instances
Implementing Changes
Attaching to an Instance
Client Configuration Assistant (CCA)
DATA DEFINITION LANGUAGE
The Structure of UDB Objects
Definition of DB2 Objects - DDL
Database Definition
Database Creation using the GUI
Database Definition Tasks
Database Placement
Database Connectivity
Catalog Tables
Database Configuration
Database Directories
Tablespace Organisation
Table Placement
Data Placement Considerations
Containers, Pages and Extents
SMS Tablespaces
DMS Tablespaces
DMS Minimum Space Requirements
DMS Maximum Space
Creating a Tablespace using the GUI
Displaying Tablespace Information
Altering a Tablespace
Dropping a Tablespace
SMS vs DMS Considerations
Create Bufferpool
Page and Row Organisation
Table Definition
Table / Column Names
Copying Table Definitions
Table Authority
Data Types
Null Values
User Defined Default Values
Lob Data Options
Lob Data Manipulation
User Defined Distinct Types
Data Capture
The Alter Table Statement
The Rename Table Statement
Listing Table / Tablespace Information
Schema Definition
Indexes
Index Organisation - The B Tree Index
Index Definition
Index Clustering
Index Design Considerations
Views Definition
Read Only Views
View Restrictions
Views - Check Options
Creating a View of Two Tables
Alias Definition
Overview of Triggers
Trigger Definition
The Drop Statements
COMMAND LINE PROCESSOR
CLP Syntax
On-Line Help
Interactive / Non-Interactive Modes
CLP Option Flag
CLP Termination
DATA MANIPULATION LANGUAGE
SQL - Structured Query Language
SQL Features
SQL Query Results
DB2 Environments
The Select Statement
The Where Clause
Special Operators
Not Operand
In Operand
Like Operand
Between Operand
User Keyword
Statements using Nulls
SQL Built-In Functions
Using 'Distinct'
Group By Clause
Group By Rollup
The Grouping Function
Group By Cube
Group By Grouping Sets
Having Clause
Order By Clause
Fetch First Clause
The Values Statement
The Update Statement
The Delete Statement
The Insert Statement
Column Functions (Scalar Functions)
Working with Date, Time and Timestamp Columns
DB2 Special Date/Time Registers
Current Date
Current Time
Current Timestamp
DB2 Join
Inner Joins
Outer Joins
Outer Join - Where Clause
Nested Table Expression
SQL Union / Intersect / Except
Subqueries
Subqueries using In
Exists
The 'All' Subquery
The 'Any' or 'Some' Subquery
MOVING DATA
Import / Export Utilities
Import Utility Syntax
Import Authorities
Export Utility Syntax
Export Authorities
Load Utility
Load Utility Features
Load Examples
Load Utility Syntax
Recovering from Load Failure / Load Pending
Load Utility and Referential / Check Constraints:
Load Authorities
Load / Import Differences
Tablespace Pending States
Backup Pending
Check Pending / Set Constraints
Running Set Constraints via the GUI
Replication - Data Propagator
DATABASE CONNECTIVITY
Type 1 / Type 2 Connectivity
The Connect Statement
Connect Reset
Disconnect
Release
APPLICATION PROGRAMMING
DB2 Environments
Database Engine Access
Development Cycle for Embedded SQL
SQL Statement Format - C
SQL Statements used in Application Programs
SQL Include
SQL Communication Area
Host Variable Declaration
Retrieving Data into Host Variables
Retrieving a 'Set' of Columns
Cursors
Declare Cursor
Open Cursor
Fetch a Row
Row Update
Row Deletion
Close Cursor
With Hold Option
Fetch First Clause
The Optimize Statement
Handling Nulls
Sample C Program
Handling Large Objects
Declaring Lob Variables
Example using Lob Variables
Using Lob Locators
Example using Lob Locators
Lob File References
Example using File References
Lob Limitations
Java Development
Dynamic SQL - What is JDBC?
What are JDBC Drivers?
JDBC Driver Types
JDBC SQL Statements
JDBC Statements
The JDBC API
Running SQL Statements
JDBC Statements - Update Example
Processing Result Sets
JDBC Prepared Statements
Handling NULL Values
Handling SQL Errors
Handling SQL Warnings
Transaction Control
Transaction Example
Isolation Levels
SQLJ Programming - Development Cycle
SQLJ Support
Importing an Sqlj Program into VisualAge for Java
Single Row Selects
Host Variable Declaration
Cursor Selects
Sqlj Cursor Technique 1 - JDBC Result Set
Sqlj Cursor Technique 2 - Sqlj Result Set
Positioned Updates using Cursors
Positioned Update Restrictions
Fetch First Clause
The Optimize Statement
Handling Nulls
Table Names In Application Programs
Sql Error Codes
Precompiling a Program
Binding a Package
Levels of Optimisation
Identifying a Collection Within a Program
USER DEFINED FUNCTIONS
Functions Overview
Function Path
Writing An External Function
Writing a C Function
Compiling C Functions
Writing a Java Function
Function Definition
Function Installation
Testing the Function
STORED PROCEDURES
Overview
Advantages of Stored Procedures
The SQL CALL Statement
Executing a Procedure
Writing a C Stored Procedure
The Casesrv.Sqc Example
The Casesrv.Sqc Listing
Writing a Java Stored Procedure using JDBC
Calling a Stored Procedure
Writing a C Client
Calling a Java Client using JDBC
Building a C Stored Procedure
Implementing a Stored Procedure
AllowingPackage Access
The Create Procedure Statement
QUERY RESULTS SETS
Query Results Sets
Objects From Which You Can Return Result Sets
Requirements for Query Result Sets
New Embedded SQL Statements
Query Results Sets Example
Declare Cursor With Return
Definition of Result-Set-Locator Variables
Associate Locators
The Allocate Cursor Statement
Describe Cursor
Describe Procedure
Using Global Temporary Tables
Returning Result Sets in Java Jdbc
LOCKING
Implications of Concurrent Processing
Database Manager Locks
Objects of Locks
Lock Modes
Lock Example
Lock Compatibility
Isolation Levels
Lock Escalation
The Lock Table Statement
Commit Points
Lock Wait and Deadlocks
The QUIESCE Utility
APPLICATION PERFORMANCE
The DB2 Optimizer
Default Catalog Statistics
Levels of Optimisation
Operational Utilities
Rebinding
The Runstats Utility
The Reorgchk Utility
The Reorg Utility
Other Application Performance Issues
Data Sorts
Concurrent Application Tuning
Asynchronous Page Cleaner
Blocking Data
SQL PERFORMANCE
Tablespace Scan
Non-Matching Index Scan
Matching Index Scan
Multiple Index Access
Index Only Access
Indexable Predicates
Use of And / Or
Table Join Methods
Merge Scan Join
Nested Loop Join
Hash Join
UDB TOOLS
The Control Centre
The Command Centre
Writing Scripts
Saving a Script
The Access Plan Panel
The Script Centre
The Journal
The Information Centre
The Alert Centre
Tool Settings
Course Environment The lectures apply to DB2 UDB running in a Linux, Unix or Windows environment.

Course Format: The course contains many practical exercises to ensure familiarity with the product. Initially students create Tables and Indexes, and then proceed to develop Java or C programs accessing the data held on the DB2 Tables. On completion of this course students will be ready to start the development of application programs accessing 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.