G562: 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.
×

Comments are closed.