DB2 for z/OS Application Programming

DB2 for z/OS Application Programming - Advanced

Overview

First, DB2 programming is reviewed in detail.

The following areas are presented, discussed and build into programs -

DB2 Referential Integrity

Inner and Outer Joins

3, 4, 5 and 6 way joins

Scrollable Cursors

Use the expanded us of Unions ('everywhere")

Fetching 1 (or 2 or 15) Row only

Indicator Variables

Dynamic SQL

Nested Table Expressions

The CASE expression and column functions and scalar functions

Check Constraints

Create tables, views and indexes.

DB2 performance guidelines are discussed in detail. The SQL statement EXPLAIN PLAN is used to help to evaluate the efficiency of SQL statements. Advanced Programming Performance considerations are presented to "tuned" programs to perform "better" that their "untuned" counter parts.

LEARNING OBJECTIVES

As a result of this class, the attendee will be able to -

Modify a "shell" program to select a single row, multiple rows, and change rows
CREATE Tables, Indexes, Views
Discuss and program DB2's Referential Integrity
Use the CASE expressions and several SCALAR / COLUMN FUNCTIONS
Code the UNION everywhere
Code Dynamic and Static SQL
Perform DCLGENs to build table definitions and I/O areas
Handle nulls in Program using indicator variables for Select, Insert and Update
Code scrollable cursors
Program Joins for 3, 4, 5 and 6 tables
Program Inner and outer Joins
Program Nested table expressions as an alternative to external tables
Discuss overall DB2 PERFORMANCE considerations
Run the EXPLAIN PLAN SQL statement and analyze results

Audience

Experienced Data Processing personnel who need use more of the "advanced" features of SQL to access DB2 tables in COBOL programs.

Prerequisites

At least six month of DB2 COBOL Programming experience is required.

Course duration

3 days

Course outline

DAY 1

Single Table Access

Course Introduction

DB2 Programming review
Create tables, views and indexes.
DB2 Referential Integrity
Indicator Variables
Dynamic SQL
Nested Table Expressions
The CASE expression and column functions and scalar functions

DAY 2

Multiple Table Access

Nested Table Expressions
Scrollable Cursors
Unions ('everywhere")
Inner and Outer Joins
Joining more than 2 tables
Fetching 1 (or 2 or 15) Row only

DAY 3

Additional Advanced Programming Considerations

Other Topics - Update Triggers, Check Constraints, UDT, UDF

Performance Considerations

Performance Advanced DB2 Programming Considerations
EXPLAIN Statement
PLAN_TABLE and DSN_STATEMNT_TABLE

Appendices

Appendix A - IBM Sample Tables	Appendix J - DB2 for WINDOWs Overview
Appendix B - Bibliography	Appendix K - UDT / UDF
Appendix C - The Programming Workshop	Appendix L - Cast Testing
Appendix D - EXPLAIN PLAN Statement and PLAN_TABLE and DSN_STATEMNT_TABLE	Appendix M - Trigger Introduction
Appendix E - Hints to Success	Appendix N - Dynamic SQL PGM Examples
Appendix F - SQLCODES / SQLSTATES	Appendix O - Scroll CURSOR PGM & Results
Appendix G - QMF Overview	Appendix Z6- Changes Summary - DB2 V 6
Appendix H - Stored Procedures	Appendix Z7- Changes Summary - DB2 V 7
Appendix I - "Common" SQL Errors	Appendix Z8- Changes Summary - DB2 V 8