DB2 Application Programming - Topical Outline

Introduction - Overview of DB2
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

DB2I 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 Expresions
   Computer Exercise Conditions: SELECT with Complex Conditions

SPUFI Defaults
Autocommit and explicit Commit and Rollback
The Sample Databases - Project Activities and Activity Assignments
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
    Computer Exercise: Subqueries and UNION

CREATE, DROP, and ALTER
Primary and Foreign Keys
UPDATE, DELETE, and INSERT
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, and INSERT in programs
Scrollable cursors
   Computer Exercise: Using Cursors and Table Modification