DB2 SQL Application Programming

This course focuses on using efficient SQL on all platforms and using appropriate host languages in the business applications environment.

Application programmers and designers seeking a solid grounding in SQL and how it is used interactively and embedded in host language program using IBM's DB2 database under both z/OS and LUW platforms.  The course is delivered at the latest release level, previous releases can be covered on request.

Delegates should have some knowledge of a relevant supported application language (eg. COBOL, Java, C++ etc...). Please inform us of the language and platform in use when booking the course.

The aim of this course is to provide the programmer, unfamiliar with Db2, with the necessary skills required to define, access and manipulate D2bdata, either via an application program or using SPUFI. The course contains many practical exercises to ensure familiarity with the product. Initially students create Tables and Indexes, and then proceed to develop COBOL or PL/I programs accessing the data held on the DB2 Tables. By the end of the week, students will be ready to start the development of application programs accessing DB2 data.

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
• use both permanent and temporary tables
• use the Db2I toolset, including SPUFI
• code SQL statements to read and manipulate DB2 data
• develop, prepare and execute Db2 programs
• bind packages and plans
• use non-scrollable and scrollable cursors
• use multi-row INSERT and FETCH processing
• describe the locking process used by Db2
• define and use Referential Integrity
• use Identity Columns and Sequences
• produce EXPLAIN output using a PLAN_TABLE
• use the LOAD utility to populate tables

INTRODUCTION TO Db2
The Relational Model
Data Representation
The DB2 Environment
The DB2 Table
Accessing The Data
Interfaces to DB2
SQL Structure
Embedded SQL
DB2 Data Relationships
Access Path Selection
DB2 Table Structure
DB2 Data Types
DB2 Catalog
DB2 Interactive
SPUFI
Database Design / Data Analysis
Overview Of Normalisation
First Normal Form
Order Form Example
Second Normal Form
Third Normal Form
Check 3nf

DATA DEFINITION LANGUAGE
The Structure Of DB2 Objects
Definition Of DB2 Objects - DDL
Database Definition
Tablespace / Page Organisation
Page Sizes
Tablespace Layout
Types Of Tablespace
Tablespace Definition
Table Definition
Table Names
Copying Table Definitions
Rename Table
Data Partitioning
DB2 Column Types
Null Values
User Defined Default Values
Global Temporary Tables
Declared Temporary Tables
Declared Temporary Table Considerations
Declared Temporary Tables - Comparisons
DB2 Views
Read Only Views
Views - With Check Option
Creating A View Of Two Tables
DB2 Synonym
DB2 Alias
The Alter Statements
DB2 Indexes
Index Organisation - The B Tree Index
Index Clustering
Non-Unique Indexes
Index Definition
Partitioning Indexes
Index Design Considerations
The Drop Statement

DB2 INTERACTIVE (DB2I)
DB2I Options
SPUFI - SQL Processor Using File Input
Running Queries
SPUFI Defaults
SPUFI - Setting Autocommit to NO
DB2 Commands
DB2 Utilities
The DB2I Defaults Panel

UTILITIES
Introduction
Utility Overview
Running Utilities
The LOAD Utility
The Load Utility Syntax
Online Load Resume
Online Load Considerations