To Register

For more information or to Register,
Click Here.

Follow Me

DB2 Embedded SQL Programming

OVERVIEW 

This course provides training for skills needed to code DB2 embedded SQL inCOBOL application programs. Topics include how to prepare DB2 programs, access DB2 databases using SQL, retrieve multiple rows of output using cursors and handle commit processing through COBOL programs.

DURATION - 2-3 Days

OBJECTIVES

  • Retrieve from and update DB2 tables using SQL data manipulation verbs.
  • Identify the differences between a standard COBOL program and one that accesses DB2 tables.
  • Embed SQL code in COBOL programs as well as code the related application structures needed for accessing DB2 tables from an application program.
  • Prepare, execute and test batch programs that access and update DB2 data efficiently and maintain integrity.
  • Employ CURSOR processing in application programs.
  • Describe and use commit-point processing and identify when and how to use commit points effectively. 

INTENDED AUDIENCE

This course is designed for people who will be coding and/or maintaining application programs using DB2, primarily application developers who code COBOL programs.

PREREQUISITES 

Experience with or training in Relational Database (Introduction to Relational Database course) and SQL Programming (SQL Fundamentals course) is needed for this class.  Some experience with COBOL is also preferred.

FORMAT/MATERIALS 

This course includes a combination of lecture, discussion and numerous computer labs.  Students will receive an extensive reference manual, which will be helpful on the job as well as in class.  There is an optional textbook available for future reference

CONTENT 

I.      Overview of Embedded SQL

  1. Syntax
  2. Checking SQLCODE
  3. Program Preparation               

II.     Application Programming - Single Row Processing

A.      Application Processing/Structure

  1. Features and Processing Modes
  2. COBOL Program Structure
  3. Embedding SQL
  4. SQLCA
  5. DECLARE TABLE
  6. DCLGEN
  7. Using Host Variables
  8. Using Host Structures
  9. Working with Nulls and Indicator Variables
  10. Sample Program Review

B.      Program Preparation and Execution

  1. Review Concepts/Facilities Involved
  2. DB2I Utilities or JCL
  3. Precompile
  4. DBRM
  5. Bind
  6. Application Packages and Plans
  7. Program Execution

C.      Locking/Integrity Issues

  1. Lock Sizes and Modes
  2. Types of Locks - Exclusive vs. Shared
  3. Bind Parameters and Locking - Repeatable Read vs. Cursor Stability
  4. Programming Workshop

III.    Application Programming - Multiple Row Processing

A.      Cursor Processing

  1. Concepts/Usage Steps
  2. DECLARE CURSOR
  3. OPEN CURSOR
  4. FETCH CURSOR
  5. Updating Within a Cursor
  6. CLOSE CURSOR

B.      Commit Processing

  1. Data Integrity
  2. COMMIT
  3. Unit of Recovery
  4. Committing with Cursors 

C.      Cursor Efficiency

  1. FOR UPDATE Clause
  2. OPTIMIZE FOR Clause
  3. Sample Program Review
  4. DB2 Application Program Checklist
  5. Restart Capabilities and Design

IV.    Stored Procedures

A.      Concepts

B.      Called Procedure

C.      Calling Program

D.     DB2 Catalog

V.     Appendix

  1. SQL Language Statement Summary
  2. SQLCODE Summary