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 |
|
|
