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
 
  1. Single Table Access


  2.         Course Introduction
    1. DB2 Programming review
    2. Create tables, views and indexes.
    3. DB2 Referential Integrity
    4. Indicator Variables
    5. Dynamic SQL
    6. Nested Table Expressions
    7. The CASE expression and column functions and scalar functions
DAY 2
 
  1. Multiple Table Access


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

DAY 3
 
  1. Additional Advanced Programming Considerations


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

  2. Performance Considerations


    1. Performance Advanced DB2 Programming Considerations
    2. EXPLAIN Statement
    3. 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


Wintrac Inc.
16523 SW McGwire Ct.
Beaverton OR 97007
Wintrac, Inc. All rights reserved.                                                                               Site Map   |   Terms of Use   |   Privacy Policy