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.