G562 Advanced DB2 Application Programming

Advanced DB2 Application Programming - Topical Outline

Introduction

The course data base

Review of relational concepts

Review of basic SQL

Computer Exercise: Course setup

Access Strategies in DB2

Index Usage

EXPLAIN

PLAN_TABLE and DSN_STATEMNT_TABLE

Computer Exercise: EXPLAIN and PLAN_TABLE

TRUNCATE

Multi-row access

GET DIAGNOSTICS

MERGE

SELECT from INSERT, UPDATE, DELETE, and MERGE

OLAP Functions

Encryption Functions

Computer Exercise: TRUNCATE, MERGE, and Multi-row Access

Joins: Inner, Left, Right, and Full

Joins and Aggregates

Joins and Table Expressions

Computer Exercise: Joins

Subqueries

UNION

EXCEPT

INTERSECT

Computer Exercise: Subqueries and UNION

Nested Table Expressions

Common Table Expressions

Declared global temporary tables

View Definition

ORDER BY and FETCH FIRST Enhancements

Computer Exercise: Advanced SQL

DB2 Processing Sequence

Indexable, Stage 1, and Stage 2 Predicates

Filter Factors

Join Access Strategies

Subquery Access Strategies

Computer Exercise: DB2 Data Access Strategies

Application Plans and Packages - 1

Collection

Version Control

Qualifier

Mirror Tables

Explaining Packages and Plans

BIND PACKAGE - Action Parameter

Package Maintenance Strategies

DROP PACKAGE and FREE PACKAGE

BINDAGENT Authority

Security Issues Related to Packages, Plans, and Collections

BIND PACKAGE Parameters

BIND PLAN Parameters

REBIND PACKAGE and REBIND PLAN

Computer Exercise: Using Plans and Packages

Application Plans and Packages - 2

ISPF DB2I Panels for Packages and Plans

Catalog Tables for Packages and Plans

Distributed Data

Distributed Data and Packages

Remote Unit of Work

Connect - Type 1

Distributed Unit of Work

Connect - Type 2

SET CONNECTION and RELEASE

System-directed access

Computer Exercise: Catalog Tables and Distributed Access

DB2 Utilities

LOAD

UNLOAD

DSNTEP2 / DSNTIAD

DSNTIAUL

Computer Exercise: Populating Tables with Utilities

Working with LOBs

Defining BLOBs, CLOBs, and DBCLOBs

ROWIDs

LOB table spaces

Auxiliary tables and indexes

LOBs and the LOAD utility

Using LOBs in programs

LOB host variables

LOB locators

FETCH CONTINUE

LOB file reference variables

LOB Catalog Tables

Computer Exercise: A program that Uses LOBs

DB2 Stored Procedures - Introduction

Stored Procedure Address Spaces

Defining Stored Procedures

CREATE / ALTER / DROP PROCEDURE

Language Environment Run-time Parameters

Writing Stored Procedures

Parameters

Null Values

Invoking Stored Procedures

Computer Exercise: Writing, Preparing, and Executing a Stored Procedure

DB2 Stored Procedures - Result Sets

From the Stored Procedure's Perspective

From the Caller's Perspective

Testing Stored Procedures

Stored Procedure Catalog Tables

Computer Exercise: Stored procedures with result sets

User-defined data types

User-defined functions

Sourced functions

External scalar functions

External table functions

SQL scalar functions

Modifying and deleting function definitions

Functions and security

Computer Exercise: User-defined Functions

Dynamic SQL - 1

Concepts

EXECUTE IMMEDIATE

PREPARE

Parameter Markers

EXECUTE

Computer Exercise: Dynamic SQL - Parameter Markers

Dynamic SQL - 2

Data Retrieval

Fixed List SELECT statements

DECLARE CURSOR

OPEN, FETCH, CLOSE

Dynamic SQL Bind Options

Special Registers and Dynamic SQL

Computer Exercise: Dynamic SQL - Fixed List SELECT

Dynamic SQL - 3

Variable List SELECT statements

SQLDA

Dynamic memory management

DESCRIBE INTO

SQLTYPE

Computer Exercise: Dynamic SQL - Variable List SELECT

Alternatives to the TSO Attachment Facility

Call Attachment Facility

CONNECT, OPEN, CLOSE, and DISCONNECT

Program preparation

SQL statements

Tracing

Recoverable Resource Services Attachment Facility

IDENTIFY, SIGNON, CREATE THREAD, TERMINATE THREAD,

TERMINATE IDENTIFY, TRANSLATE

Program preparation

SQL statements

Computer Exercise: Alternative Attachment Facilities

The Open Database Connectivity (ODBC) interface

Connections and handles

Coding ODBC Programs

Preparing SQL statements

Binding parameter markers

Executing SQL statements

Working with result sets

Calling stored procedures

Establishing the Environment for ODBC

Computer Exercise: Exploring ODBC

REXX and DB2

Connecting to DB2

The REXX SQLCA

Embedding SQL statements

Using cursors for data access

Indicator variables

Using SQLDAs

REXX Stored Procedures

Calling Stored Procedures

Writing Stored Procedures

Handling Null Values

Working with Result Sets

Computer Exercise: DB2 Data Access from a REXX program

Native SQL procedures

CREATE / ALTER PROCEDURE

Versioning

Commands for Native SQL procedures

Computer Exercise: Native SQL Stored Procedures

XML overview

XML and DB2

XML and SQL

XML in application programs

XMLMODIFY function and partial updating

Computer Exercise: Using XML in DB2

Version 10 features

Native SQL Scalar Functions

SQL Table Functions

Extended Indicator Variables

DSNULI: A Universal Interface

LOAD / UNLOAD Utilities with Spanned Records

Computer Exercise: Exploring Version 10 Features

Locking in DB2

Performance Considerations

Cursors in Online Systems

Batch Performance Guidelines

"Smart" COMMIT

Restart strategies

G562 / 10 Days

Materials version: V6.x

Course Objectives
Course Description
Home page