Advanced DB2 Application Programming

Course Number:  607-TF
Recommended Duration:  5 days

Intended Audience
Experienced DB2 programmers who need to construct complex, efficient queries in both online and batch environments, who need to understand data access strategies in DB2, and who need to be able to access distributed data bases.

Course Overview
This course presents more sophisticated SQL techniques, goes deeper into understanding the implications of design choices (index types, query styles, packages and plans, normalization), loading tables, stored procedures, distributed data access, and dynamic SQL statements.

Objectives
Students who complete this course will be able to build efficient queries in DB2, using multiple tables.  They will also be able to discern the underlying access strategy for a DB2 query, be able to define, load, and maintain DB2 tables.  In addition, they will understand the use of packages, be able to utilize the distributed data base features of DB2, and be able to code programs that use the Call Attachment facility, Recoverable Resource Manager Attachment Facility, dynamic SQL, and stored procedures.

Prerequisites
Experience designing and coding application programs containing embedded SQL in PL/I, COBOL, C, or Assembler in one or more of the application environments in which DB2 is used: CICS, IMS/DB/DC, TSO, or batch.

Topics

• Relational access concepts: EXPLAIN, plan tables, and access strategies

• SQL statements for aggregatrion, joins, outer joins, subqueries, and nested / common table expressions

• Join access strategies - nested loop, merge scan, and hybrid

• Packages: Remote units of work and distributed units of work; DRDA

• LOAD and UNLOAD utilities

• Working with LOBs

• Stored procedures

• User-defined functions

• Call Attachment and Recoverable Resource Services Attachment Facilities

• Dynamic SQL

• ODBC Interface

• Accessing DB2 from REXX

• Locking in DB2