UD02 - DB2 - z/OS to LUW Transition for Developers - 3 Days

Course Description

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, Linux or Unix 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.

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 IBM Data Studio and the Data Studio Web Console
  • use the Command Line Processor
  • code and install Stored Procedures
  • code and install User Defined Functions
  • populate tables using DB2 Utilities
  • monitor application performance

Environment

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

Customisation

For on-site courses (i.e. at your premises), we are more than happy to tailor the course agenda to suit your exact requirements. In many cases, we are able to build your in-house standards and naming conventions into the delivered course.

Course Details

DB2 INSTANCES
What Is An Instance?
Setting Up Instances

DATABASE DEFINITION
The Structure Of DB2 Objects
Database Definition
Default Tablespaces
Database Placement
Automatic Storage Databases
Database Creation Using IBM Data Studio
Database Definition Tasks
Altering a Database
Database Connectivity - Connect vs Activate
Catalog Tables

TABLESPACE DEFINITION
Tablespace Organisation
Data Placement - SMS or DMS?
Data Placement Considerations
Containers, Pages And Extents
Create Tablespace Command
Create Tablespace Parameters
Sms Tablespace Examples
Dms Tablespaces
Automatic Storage Tablespaces
Dms Minimum Space Requirements
Dms Maximum Space

TABLE / INDEX DEFINITION
Table Definition
Table / Column Names
Copying Table Definitions
Table Authority
Db2 Column Types
Null Values
Null and Default Compression
Compression - Row Format
Has Compression Been Switched On?
Lob Data Options
The Alter Table Statement
Advanced GUI Alter Table Functionality
The Rename Table Statement
Global Temporary Tables
Declared Temporary Tables
Declared Temporary Table Considerations
Declared Temporary Tables - Comparisons
Listing Table / Tablespace Information
Schema Definition
View Definition
Creating A View Of Two Tables
Read Only Views
View Restrictions
Views - Check Options
Alias Definition
Indexes
Index Organisation - The B Tree Index
Index Clustering
Index Definition

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

APPLICATION PROGRAMMING
Db2 Environments
Development Cycle For Embedded Sql in C
SQLJ Programming - Development Cycle
JDBC Drivers - The Universal Driver
Single Row Selects
Including External Sql into C Programs
Sql Include
Sql Communication Area
Host Variable Declaration in C Programs
Cursor Selects
Declare Cursor
Open Cursor
Fetch A Row
Row Update
Row Deletion
Close Cursor
With Hold Option
Fetch First Clause
The Optimize Statement
Precompiling A Program
Binding A Program
Levels Of Optimisation
Run Time Reoptimization - Bind Option REOPT

MATERIALIZED QUERY TABLES
What Are Materialized Query Tables?
MQT Features
Creating an MQT
Create MQT Example
Altering an MQT
Alter MQT Example
MQT Fullselect Features / Restrictions
Refresh Table
Populating User Maintained MQTs
Automatic Query Rewrite using MQT
Enabling Automatic Query Rewrite
Enabling Automatic Query Rewrite - DDL Options
Enabling Automatic Query Rewrite - Special Registers
AQR - Using Both Registers
Enabling Automatic Query Rewrite - System Properties
Enabling Automatic Query Rewrite - Query Properties
Registering Existing Tables as MQT
Maintenance of MQTs using Staging Tables

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
Savepoints
Savepoints - Considerations and Restrictions
The Quiesce Utility
SHARE

DATA INTEGRITY
Referential Integrity
The Primary Key
The Foreign Key
Referential Integrity Rules
Referential Integrity Constraint Names
Self Referencing Referential Structures
Referential Integrity Performance
Check Constraints
Check Constraint Syntax
Allowable Check Constraints
When Are Check Constraints Enforced
Integrity Pending (aka Check Pending) State
The Set Integrity Command
Running Set Integrity Via IBM Data Studio
Informational Constraints

APPLICATION PERFORMANCE
The Db2 Optimizer
Levels Of Optimisation
Operational Utilities
Rebinding
The Runstats Utility
Runstats Parameters
Runstats - Sampling Options
Runstats - Statistics Profiling
Runstats - Throttling
Runstats Profiling Examples
Automatic Statistics Collection
Automatic Statistics Profile Generation
The Reorgchk Utility
The Reorg Utility
Offline / Online Table Reorg
Index Reorg

DB2 TOOLS
IBM Data Studio
IBM Data Studio Web Console

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.

Course Enquiry