DB2 UDB Training - UD02 - DB2 UDB for Programmers - z/OS to LUW Transition

UD02 - DB2 UDB for Programmers - z/OS to LUW Transition
Duration	3 Days
Availability	Check course price and availability

Target Audience

This training course is aimed at the DB2 Applications Programmer, already familiar with DB2 running in a z/OS environment, who requires an in-depth knowledge of the key tasks involved when developing DB2 programs on a Windows platform.

Pre-requisites

A strong programming knowledge and usage of DB2 for z/OS is required. In addition, the delegate should have experience in developing C or Java programs in a Linux, Unix or Windows environment.

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 Environment

The lectures apply to DB2 UDB running in a Linux, Unix or Windows environment.

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 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.