Know Db2

Db2 for z/OS SQL and Application Coding

Db2 SQL and Application Coding is a 5-day hands-on course, takes the student from an introduction to Db2, through learning SQL, working with SQL interactively and writing static SQL programs. Working with the EXPLAIN statement they will become familiar with tuning techniques used in Db2 for z/OS.

Topics Covered:

Duration: 5 days Hands-on labs

Audience: Lead Application Programmers, Application Programmers, Data Base Designers, Data Base Administrators and Business Analysts that require a working knowledge of SQL and SQL application coding.

Prerequisites: Students should have a working knowledge of TSO/ISPF or be familiar with an SQL Query Tool and a programming language.

Objectives: Students will write SQL statements, run SQL statements interactively, write SQL application programs, prepare programs and execute them and work with the EXPLAIN to evaluate SQL performance.

Db2 for z/OS SQL and Application Coding Class Outline:

Db2 Concepts

  • Db2 Environment
  • Data Structures
  • Structured Query Language
  • Application Processes
  • Concurrency and Recovery
  • Db2 Interactive
  • Hands-on Labs
  •  

Basic Single Table SQL

  • Data Retrieval
  • Result tables
  • SELECT Statement Structure
  • Selecting Columns : SELECT
  • Selecting Rows
  • Search conditions: WHERE
  • Using Functions and Expressions
  • Ordering the result table rows: ORDER BY
  • Merging intermediate results: UNION
  • Special Registers
  • Hands-on Labs
  •  

Working with Multiple Tables

  • Joining Data from Multiple Tables
  • Inner join
  • Full Outer Join
  • Left Outer Join
  • Right Outer Join
  • Using multiple Join types in a Query
  • Nested Table Expressions
  • Hands-on Labs
  •  

Summary Processing and Data Modifications

  • Summary results: GROUP BY
  • Limiting Groups: HAVING
  • Using Subqueries
  • How to code Subqueries
  • Correlated Subqueries
  • Non-correlated Subqueries
  • Modifying Data
  • Hands-on Labs
  •  

Static SQL in a Host Application

  • Coding Conventions for Embedded SQL
  • Generating and using DCLGEN
  • SQLCA
  • SQLCODE handling
  • Host Variables
  • Indicator Variables
  • Singleton Select’s
  • Delete, Update, Insert
  • Hands-on Lab
  •  

Preparing your Program for Execution

  • Db2 Precompiler
  • Compile Your Program
  • Link Edit
  • Bind : Package
  • Bind : Plan
  • Output Listings
  • Problem Determination
  • Hands-on Lab
  •  

Cursor Processing

  • Declare a Cursor
  • Open, Fetch, Close a Cursor
  • Deleting, Updating using a Cursor
  • Cursor with Hold
  • Cursor for Read Only
  • Hands-on Lab
  •  

Introduction to Performance

  • What is the Optimizer?
  • Predicates (Indexable, Stage 1 and Stage 2)
  • Access Paths and how they work
  • Explain - the Basics
  • Hands-on Lab
  •  

Transaction Locking Overview

  • Four Basic aspects of Locks
  • Bind Options Isolation
  • Options
  • CURRENTDATA - Lock Avoidance Factors
  • Hands-on Lab
  • Class Review
  •  
  • © Computer Business International, Inc.

 

 

Would you like to schedule or customize this class?
Please call our toll free number 1.866.224.4968 or email us at info@cbi4you.com.