G562: Advanced DB2 Application Programming

TTFlogo2.gif (2585 bytes)

Advanced DB2 Application Programming - Topical Outline

The Sample Data Base
Review of Relational Concepts: Select, Project, Join
Access Strategies in DB2
    Index usage
    EXPLAIN
    PLAN_TABLE and DSN_STATEMNT_TABLE
    Computer Exercise: EXPLAIN and PLAN_TABLE

Advanced SQL - 1
    CASE expressions
    Aggregated queries: GROUP BY and HAVING
    Nested Table Expressions
    Common Table Expressions
    Computer Exercise: Aggregation and Nested Table Expressions

Advanced SQL - 2
    Joins: Inner, Left, Right, and Full
    Joins and Table Expressions
    Joins and Aggregates
    Join Access Strategies
    Computer Exercise: Joins

Advanced SQL – 3
    Subqueries
        Scalar Fullselects
        Subquery performance considerations
    UNION and UNION ALL
Views
    Computer Exercise: Subqueries, UNION, and Views

Application Plans and Packages – 1
    Collections, Versions, and Mirror tables
    Package maintenance; Remote packages
    Packages and security; BINDAGENT
    BIND PACKAGE and BIND PLAN
    Computer Exercise: Using Plans and Packages

Application Plans and Packages – 2
    DB2I BIND panels
    Package related catalog tables
Distributed Data Facility
    System-Directed Access
    Application-Directed Access
    Computer Exercise: Distributed Access

DB2 Utilities
    LOAD
    UNLOAD
DSNTEP2 / DSNTIAD
DSNTIAUL
    Computer Exercise: Populating Tables with Utilities

Advanced Data Types
Defining BLOBs, CLOBs, and DBCLOBs
    ROWIDs
    LOB table spaces
    Auxiliary tables and indexes
User-defined data types
LOBs and the LOAD utility
    Computer Exercise: Working with LOBs

Using LOBs in programs
    LOB host variables
    LOB locators
    Scalar functions for working with LOBs
    Computer Exercise: A program that uses LOBs

DB2 Stored Procedures – 1
    Stored Procedure Address Spaces
    Defining Stored Procedures
    Writing Stored Procedures
        Parameters
        Null Values
    Invoking Stored Procedures
    Computer Exercise: Writing, preparing, and executing a stored procedure

DB2 Stored Procedures – 2
    Returning result sets
    Result sets from the caller’s perspective
    Testing stored procedures
    Computer Exercise: Stored Procedures with result sets

User-defined functions
    Sourced functions
    External scalar functions
    External table functions
    SQL scalar functions
    Writing external functions
    Invoking user-defined functions
    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
        TRANSLATE
        Program preparation
        SQL statements
        Tracing
    Recoverable Resource Services Attachment Facility
        IDENTIFY, SIGNON, CREATE THREAD, TERMINATE THREAD, TERMINATE IDENTIFY
        Program preparation
        SQL statements
    Computer Exercise: Alternative Attachment Facilities

The ODBC (call level) interface
    Connections and handles
    Preparing SQL statements
    Binding parameter markers
    Executing SQL statements
    Working with result sets
    Calling stored procedures
    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
    Computer Exercise: DB2 data access from a REXX program

Programming Considerations
    Locking in DB2
    Cursors in online systems
    “Smart” COMMIT and restart strategies

Course Objectives
Course description
Home page