G560 DB2 Application Programming

DB2 Application Programming - Topical Outline

Introduction - Overview of DB2

Computer Exercise: Course Setup

The Sample Databases - Employees and Departments

Relational Operations: Select, Project, Join

Overview of DB2 SQL - the SELECT statement: WHERE clause

Basic SPUFI usage

Computer Exercise: A First Exploration of SQL

DB2 System Components and Control Flow

SPUFI Processing and menu options

The Sample Databases - Projects and Activities

SELECT DISTINCT

SELECT with multiple conditions

Computed values, NULL, and LIKE in SELECT statements

CASE Expressions

Computer Exercise: SELECT with Complex Conditions

SPUFI Defaults

Autocommit and explicit Commit and Rollback

The Sample Databases - Project Activities and Activity Assignments

FETCH FIRST “n” ROWS ONLY

Built-in Column Functions

Aggregation: GROUP BY and HAVING

Result set sequencing: ORDER BY

Computer Exercise: Data Aggregates

Special Registers

Dates, Times, and Timestamps

Date Operations

Built-in Scalar Functions

Computer Exercise: Special Registers and Scalar Functions

Joins

Name Specification

Joins with Aggregates

Outer Joins

Classic join syntax

Computer Exercise: Joins

Subqueries

Outer Joins

UNION

EXCEPT

INTERSECT

Computer Exercise: Subqueries and UNION

CREATE, DROP, and ALTER

Primary and Foreign Keys

UPDATE, DELETE, and INSERT

MERGE

Defining and Using Views

Computer Exercise: Creating and Changing Tables

DB2 Objects

DB2 Catalog Tables: SYSTABLES, SYSCOLUMNS, SYSDBRM, SYSPLAN,

SYSINDEXES, SYSKEYS, SYSPLANAUTH, SYSTABAUTH,

SYSPLANDEP, SYSVIEWS

Computer Exercise: Querying the Catalog Tables

Application Development Control Flow

DCLGEN

Computer Exercise: DCLGEN

SQL in Application Programs

Indicator Variables and Error Handling

Computer Exercise: Coding a DB2 Program

BIND/REBIND/FREE

Authorizing Users

Executing a Program

Common Problems

Computer Exercise: Running a DB2 Program

Cursors in application programs

UPDATE, DELETE, INSERT, and MERGE in programs

Insensitive and Sensitive Static Scrollable cursors

Dynamic Scrollable cursors

Computer Exercise: Using Cursors and Table Modification

SQL SET statement

The rest of DB2I

Batch Processing

Locks and Locking

EXPLAIN, Tuning and Performance

Odds and Ends

Optional Computer Exercise: Commands, EXPLAIN, More Programming

G560 / 5 Days

Materials version: V4.x

Course Objectives
Course Description
Home page