To Register

For more information or to Register,
Click Here.

Follow Me

DB2/SQL Application Programming

OVERVIEW

This class provides the knowledge and skills needed to use features of DB2 and SQL in both interactive and static formats.  It also includes information on relational database concepts and facilities, referential integrity, locking, primary keys, foreign keys and data integrity, DB2 physical storage and programming considerations.  Hands on computer workshops allow students to run dynamic SQL through SPUFI or QMF, and static SQL  using COBOL application programs. There is discussion of DB2 triggers and procedures in a client server environment.

DURATION - 5 Days

OBJECTIVES

  • Briefly describe relational database concepts and how DB2 implements the relational model.
  • Define and code the DDL statements of CREATE, ALTER and DROP
  • Define and code the DML statements of SELECT, INSERT, UPDATE and DELETE
  • Use SQL SELECT verb to qualify data retrieval, such as WHERE, DISTINCT, IN, LIKE, BETWEEN, ORDER BY, GROUP BY and HAVING.
  • Use SPUFI or QMF to execute SQL interactively.
  • Code more complex queries such as Joins, Subqueries and UNIONs.
  • Code aggregated functions COUNT, MIN, MAX, AVG and SUM with and w/o GROUP BY
  • Code the date functions DAY, HOUR, CHAR and others used for date calculations.
  • Code the scalar functions SUBSTR, DECIMAL, VALUE and others for manipulating data values.
  • Understand performance considerations, and code efficient SQL statements
  • Define the EXPLAIN bind parameter and how to interpret the Plan Table
  • Embed SQL code in COBOL programs for static processing of DB2 tables
  • Prepare, execute and test batch programs that access and update DB2 data and maintain integrity.
  • Employ CURSOR processing in application programs.
  • Code commit point processing and identify when and how to use commit points effectively.
  • Describe the different locking  modes DB2 employs and the implications to concurrent processing.
  • Define the SQL statements that cause locking and unlocking  of  rows or pages  in a DB2 database.
  • Use simple dynamic SQL statements in application programs.
  • Describe major DB2 facilities and their purpose, such as program preparation and various utilities.Identify the use of stored procedures in a DB2 Client Server Environment.
  • Identify the use of triggers in a DB2 Client Server Environment.
  • Identify the new features of Release 8.0 of DB2. 

INTENDED AUDIENCE

This course is designed for people who will be coding and/or maintaining data in a DB2 database using  application programs or using SPUFI, QMF, or other interactive tools for adhoc queries.  This includes users, systems analysts or application developers.

PREREQUISITES

Working knowledge of TSO/ISPF, and previous COBOL coding experience is preferred.

FORMAT/MATERIALS

A combination of lecture, discussion and numerous computer labs are included.  Students will receive an extensive course reference manual, which will be a valuable reference on the job. Included for quick reference are the appendices for syntax, SQLCODEs, glossary as well as a complete index. The course can be split into two separate classes for non-programmers if necessary:

1.   Introduction to DB2 and SQL (3-days)

2.   DB2 Application Programming (2-days) 

   

CONTENT - DAY 1     

I.    DB2 Concepts and Facilities

A.    Relational Concepts

  1. Tables
  2. Keys - Primary and Foreign
  3. Relational Operators - Project, Select, Union, Join, etc.
  4. Referential Integrity

B.    DB2 as a Relational Database

  1. DB2 Environment - Batch, CICS and IMS
  2. DB2 Objects - Database, Table, Index, View, Synonym, Storage Group, Tablespace
  3. DB2 Catalog and Directory
  4. System Utilities, Components and Services (e.g., locking)
  5. List the features of Release 8.0

C.    Introduction to SQL

  1. Data Definition Language Overview
  2. Data Manipulation Language Overview
  3. DB2 Security Concepts
  4. Data Control Language Overview

D.    DB2 Programming Facilities

  1. Program Preparation
  2. DCL Generation (DCLGEN)
  3. Application Precompile
  4. Bind Process
  5. Packages and Plans

  

DAYS 2 and 3

II.    Beginning SQL

A.    Overview

  1. Executing SQL
  2. SQL Data Types

B.    Selecting Data

  1. SELECT - choosing rows and columns (WHERE)
  2. Creating new "columns"

C.    DB2 Interactive

  1. General Usage
  2. Using SPUFI to Execute SQL
  3. SQL Workshop

D.  SELECT - Conditional Operators

  1. AND/OR, IN, LIKE, BETWEEN, NOT
  2. SQL Workshop

E.    Additional SELECT Operators

  1. DISTINCT, ORDER BY, GROUP BY, HAVING
  2. Common Column and Scalar Functions
  3. NULL Values
  4. SQL Workshop 

 

III.    Advanced Data Manipulation

A.    Joins

  1. Simple Inner Joins
  2. Outer Joins
  3. Joining a Table to Itself

B.    Subqueries

  1. Single-valued Subqueries
  2. Multi-valued Subqueries
  3. ALL and ANY Parameters
  4. Correlated Subqueries
  5. EXISTS Parameter
  6. SQL Workshop

C.    UNIONs

  1. Coding
  2. Differences

D.    Table Updates

  1. INSERT
  2. UPDATE
  3. DELETE
  4. Locking features
  5. COMMIT and ROLLBACK
  6. SQL Workshop 

E.    SQL Performance

  1. Components that Affect SQL Performance
  2. Use of Indexes
  3. Use of EXPLAIN and Plan Table
  4. Performance Guidelines

F.    SQL Functions

  1. COUNT, MAX, MIN, AVG, SUM
  2. Scalar function SUBSTR, VALUE, etc.
  3. Date functions DAYS, CHAR, etc.
  4. Use calculations for date columns
  5. Use date functions for formatting   

 

DAYS  4 and 5 

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

V.    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. Use of CASE
  4. Use of NULLIF
  5. Use of STRIP
  6. Sample Program Review

D.    DB2 Application Program Checklist

  1. Programming Workshop

E.    Introduction to Dynamic SQL

  1. Static vs. Dynamic SQL
  2. Restrictions of Dynamic SQL
  3. EXECUTE IMMEDIATE Statement
  4. PREPARE Statement
  5. EXECUTE Statement
  6. Programming Workshop 

VI.   Version 8 features

  1. Partitioned tables
  2. SQL enhancements
    • Multi-row FETCH and INSERT
    • GET DIAGNOSTICS
    • Sequence objects
    • Scalar full select
    • Select in insert
    • Unicode fundamentals

C.    Utility Enhancements

  1. LOAD/UNLOAD
  2. REORG rebalance of partitions

D.   Index support and classifications

VII.  Appendix

  1. SQL Language Statement Summary
  2. SQLCODE Summary
  3. Glossary
  4. Index