About This Book
Who Should Use This Book
How to Use This Book
Conventions
Related Publications
DB2 Application Development Concepts
About This Book
Who Should Use This Book
How to Use This Book
Conventions
Related Publications
Prerequisites for Programming
DB2 Application Coding Overview
Declaring and Initializing Variables
Connecting to the Database Server
Coding Transactions
Ending the Program
Implicitly Ending a Transaction
Application Pseudocode Framework
Designing an Application For DB2
Access to Data
Data Value Control
Data Relationship Control
Application Logic at the Server
The IBM DB2 Universal Database Project Add-In for Microsoft Visual C++
Supported SQL Statements
Authorization Considerations
Dynamic SQL
Static SQL
Using APIs
Example
Database Manager APIs Used in Embedded SQL or DB2 CLI Programs
Setting Up the Testing Environment
Creating a Test Database
Creating Test Tables
Generating Test Data
Running, Testing and Debugging Your Programs
Prototyping Your SQL Statements
Embedding SQL Statements in a Host Language
Creating and Preparing the Source Files
Creating Packages for Embedded SQL
Precompiling
Compiling and Linking
Binding
Advantages of Deferred Binding
DB2 Bind File Description Utility - db2bfd
Application, Bind File, and Package Relationships
Timestamps
Rebinding
Characteristics and Reasons for Using Static SQL
Advantages of Static SQL
Example: Static SQL Program
How the Static Program Works
C Example: STATIC.SQC
Java Example: Static.sqlj
COBOL Example: STATIC.SQB
Coding SQL Statements to Retrieve and Manipulate Data
Retrieving Data
Using Host Variables
Declaration Generator - db2dclgn
Using Indicator Variables
Data Types
Using an Indicator Variable in the STATIC program
Selecting Multiple Rows Using a Cursor
Declaring and Using the Cursor
Cursors and Unit of Work Considerations
Example: Cursor Program
Updating and Deleting Retrieved Data
Updating Retrieved Data
Deleting Retrieved Data
Types of Cursors
Example: OPENFTCH Program
Advanced Scrolling Techniques
Scrolling Through Data that has Already Been Retrieved
Keeping a Copy of the Data
Retrieving the Data a Second Time
Establishing a Position at the End of a Table
Updating Previously Retrieved Data
Example: UPDAT Program
Diagnostic Handling and the SQLCA Structure
Return Codes
SQLCODE and SQLSTATE
Token Truncation in SQLCA Structure
Handling Errors using the WHENEVER Statement
Exception, Signal, Interrupt Handler Considerations
Exit List Routine Considerations
Using GET ERROR MESSAGE in Example Programs
Why Use Dynamic SQL?
Dynamic SQL Support Statements
Comparing Dynamic SQL with Static SQL
Using PREPARE, DESCRIBE, FETCH and the SQLDA
Declaring and Using Cursors
Example: Dynamic SQL Program
Declaring the SQLDA
Preparing the Statement Using the Minimum SQLDA Structure
Allocating an SQLDA with Sufficient SQLVAR Entries
Describing the SELECT Statement
Acquiring Storage to Hold a Row
Processing the Cursor
Allocating an SQLDA Structure
Passing Data Using an SQLDA Structure
Processing Interactive SQL Statements
Saving SQL Requests from End Users
Example: ADHOC Program
Variable Input to Dynamic SQL
Using Parameter Markers
Example: VARINP Program
The DB2 Call Level Interface (CLI)
Comparing Embedded SQL and DB2 CLI
Advantages of Using DB2 CLI
Deciding on Embedded SQL or DB2 CLI
Generated Columns
Identity Columns
Declared Temporary Tables
Controlling Transactions with Savepoints
Savepoint Restrictions
Savepoints and Data Definition Language (DDL)
Savepoints and Buffered Inserts
Using Savepoints with Cursor Blocking
Savepoints and XA Compliant Transaction Managers
Stored Procedure Overview
Advantages of Stored Procedures
Writing Stored Procedures
Client Application
Stored Procedures on the Server
Writing OLE Automation Stored Procedures
Example OUT Parameter Stored Procedure
Code Page Considerations
C++ Consideration
Graphic Host Variable Considerations
Multisite Update Consideration
NOT FENCED Stored Procedures
Returning Result Sets from Stored Procedures
Example: Returning a Result Set from a Stored Procedure
Resolving Problems
Comparison of SQL Procedures and External Procedures
Valid SQL Procedure Body Statements
Issuing CREATE PROCEDURE Statements
Handling Conditions in SQL Procedures
Declaring Condition Handlers
SIGNAL and RESIGNAL Statements
SQLCODE and SQLSTATE Variables in SQL Procedures
Using Dynamic SQL in SQL Procedures
Nested SQL Procedures
Passing Parameters Between Nested SQL Procedures
Returning Result Sets From Nested SQL Procedures
Restrictions on Nested SQL Procedures
Returning Result Sets From SQL Procedures
Returning Result Sets to Caller or Client
Receiving Result Sets as a Caller
Debugging SQL Procedures
Displaying Error Messages for SQL Procedures
Debugging SQL Procedures Using Intermediate Files
Examples of SQL Procedures
What is Stored Procedure Builder?
Advantages of Using Stored Procedure Builder
Creating New Stored Procedures
Working with Existing Stored Procedures
Creating Stored Procedure Builder Projects
Debugging Stored Procedures
Why Use the DB2 Object Extensions?
Object-Relational Features of DB2
Why Use Distinct Types?
Defining a Distinct Type
Resolving Unqualified Distinct Types
Examples of Using CREATE DISTINCT TYPE
Example: Money
Example: Job Application
Defining Tables with Distinct Types
Example: Sales
Example: Application Forms
Manipulating Distinct Types
Examples of Manipulating Distinct Types
Example: Comparisons Between Distinct Types and Constants
Example: Casting Between Distinct Types
Example: Comparisons Involving Distinct Types
Example: Sourced UDFs Involving Distinct Types
Example: Assignments Involving Distinct Types
Example: Assignments in Dynamic SQL
Example: Assignments Involving Different Distinct Types
Example: Use of Distinct Types in UNION
Structured Types Overview
Creating a Structured Type Hierarchy
Storing Objects in Typed Tables
Storing Objects in Columns
Additional Properties of Structured Types
Using Structured Types in Typed Tables
Creating a Typed Table
Populating a Typed Table
Using Reference Types
Comparing Reference Types
Creating a Typed View
Dropping a User-Defined Type (UDT) or Type Mapping
Altering or Dropping a View
Querying a Typed Table
Queries that Dereference References
Additional Query Specification Techniques
Additional Hints and Tips
Creating and Using Structured Types as Column Types
Inserting Structured Type Instances into a Column
Defining Tables with Structured Type Columns
Defining Types with Structured Type Attributes
Inserting Rows that Contain Structured Type Values
Retrieving and Modifying Structured Type Values
Associating Transforms with a Type
Where Transform Groups Must Be Specified
Creating the Mapping to the Host Language Program: Transform Functions
Working with Structured Type Host Variables
What are LOBs?
Understanding Large Object Data Types (BLOB, CLOB, DBCLOB)
Understanding Large Object Locators
Example: Using a Locator to Work With a CLOB Value
How the Sample LOBLOC Program Works
C Sample: LOBLOC.SQC
COBOL Sample: LOBLOC.SQB
Example: Deferring the Evaluation of a LOB Expression
How the Sample LOBEVAL Program Works
C Sample: LOBEVAL.SQC
COBOL Sample: LOBEVAL.SQB
Indicator Variables and LOB Locators
LOB File Reference Variables
Example: Extracting a Document To a File
How the Sample LOBFILE Program Works
C Sample: LOBFILE.SQC
COBOL Sample: LOBFILE.SQB
Example: Inserting Data Into a CLOB Column
What are Functions and Methods?
Why Use Functions and Methods?
UDF And Method Concepts
Implementing Functions and Methods
Writing Functions and Methods
Registering Functions and Methods
Examples of Registering UDFs and Methods
Example: Exponentiation
Example: String Search
Example: BLOB String Search
Example: String Search over UDT
Example: External Function with UDT Parameter
Example: AVG over a UDT
Example: Counting
Example: Counting with an OLE Automation Object
Example: Table Function Returning Document IDs
Using Functions and Methods
Referring to Functions
Examples of Function Invocations
Using Parameter Markers in Functions
Using Qualified Function Reference
Using Unqualified Function Reference
Summary of Function References
Description
Interface between DB2 and a UDF
The Arguments Passed from DB2 to a UDF
Summary of UDF Argument Use
How the SQL Data Types are Passed to a UDF
Writing Scratchpads on 32-bit and 64-bit Platforms
The UDF Include File: sqludf.h
Creating and Using Java User-Defined Functions
Coding a Java UDF
Changing How a Java UDF Runs
Table Function Execution Model for Java
Writing OLE Automation UDFs
Creating and Registering OLE Automation UDFs
Object Instance and Scratchpad Considerations
How the SQL Data Types are Passed to an OLE Automation UDF
Implementing OLE Automation UDFs in BASIC and C++
OLE DB Table Functions
Creating an OLE DB Table Function
Fully Qualified Rowset Names
Defining a Server Name for an OLE DB Provider
Defining a User Mapping
Supported OLE DB Data Types
Scratchpad Considerations
Table Function Considerations
Table Function Error Processing
Scalar Function Error Processing
Using LOB Locators as UDF Parameters or Results
Scenarios for Using LOB Locators
Other Coding Considerations
Hints and Tips
UDF Restrictions and Caveats
Examples of UDF Code
Example: Integer Divide Operator
Example: Fold the CLOB, Find the Vowel
Example: Counter
Example: Weather Table Function
Example: Function using LOB locators
Example: Counter OLE Automation UDF in BASIC
Example: Counter OLE Automation UDF in C++
Debugging your UDF
Why Use Triggers?
Benefits of Triggers
Overview of a Trigger
Trigger Event
Set of Affected Rows
Trigger Granularity
Trigger Activation Time
Transition Variables
Transition Tables
Triggered Action
Triggered Action Condition
Triggered SQL Statements
Functions Within SQL Triggered Statement
Trigger Cascading
Interactions with Referential Constraints
Ordering of Multiple Triggers
Synergy Between Triggers, Constraints, UDTs, UDFs, and LOBs
Extracting Information
Preventing Operations on Tables
Defining Business Rules
Defining Actions
DB2 Programming Considerations
National Language Support Considerations
Collating Sequence Overview
Deriving Code Page Values
Deriving Locales in Application Programs
National Language Support Application Development
DBCS Character Sets
Extended UNIX Code (EUC) Character Sets
Running CLI/ODBC/JDBC/SQLJ Programs in a DBCS Environment
Japanese and Traditional Chinese EUC and UCS-2 Code Set Considerations
Considerations for Multisite Updates
Remote Unit of Work
Multisite Update
Accessing Host or AS/400 Servers
Multiple Thread Database Access
Recommendations for Using Multiple Threads
Multithreaded UNIX Applications Working with Code Page and Country Code
Potential Pitfalls when Using Multiple Threads
Concurrent Transactions
Potential Pitfalls when Using Concurrent Transactions
X/Open XA Interface Programming Considerations
Application Linkage
Working with Large Volumes of Data Across a Network
Improving Performance
Using FOR READ ONLY Cursors
Using Directed DSS and Local Bypass
Using Buffered Inserts
Example: Extracting Large Volume of Data (largevol.c)
Creating a Test Environment
Error-Handling Considerations
Severe Errors
Merged Multiple SQLCA Structures
Identifying the Partition that Returned the Error
Debugging
Diagnosing a Looping or Suspended application
Introduction to DB2 Federated Systems
Accessing Data Source Tables and Views
Working with Nicknames
Using Isolation Levels to Maintain Data Integrity
Working with Data Type Mappings
How DB2 Determines What Data Types to Define Locally
Default Data Type Mappings
How You Can Override Default Type Mappings and Create New Ones
Using Distributed Requests to Query Data Sources
Coding Distributed Requests
Using Server Options to Facilitate Optimization
Invoking Data Source Functions
Enabling DB2 to Invoke Data Source Functions
Reducing the Overhead of Invoking a Function
Specifying Function Names in the CREATE FUNCTION MAPPING Statement
Discontinuing Function Mappings
Using Pass-Through to Query Data Sources Directly
SQL Processing in Pass-Through Sessions
Considerations and Restrictions
Programming Considerations for C and C++
Language Restrictions for C and C++
Trigraph Sequences for C and C++
C++ Type Decoration Consideration
Input and Output Files for C and C++
Include Files for C and C++
Including Files in C and C++
Embedding SQL Statements in C and C++
Host Variables in C and C++
Naming Host Variables in C and C++
Declaring Host Variables in C and C++
Indicator Variables in C and C++
Graphic Host Variable Declarations in C or C++
LOB Data Declarations in C or C++
LOB Locator Declarations in C or C++
File Reference Declarations in C or C++
Initializing Host Variables in C and C++
C Macro Expansion
Host Structure Support in C and C++
Indicator Tables in C and C++
Null-terminated Strings in C and C++
Pointer Data Types in C and C++
Using Class Data Members as Host Variables in C and C++
Using Qualification and Member Operators in C and C++
Handling Graphic Host Variables in C and C++
Japanese or Traditional Chinese EUC, and UCS-2 Considerations in C and C++
Supported SQL Data Types in C and C++
FOR BIT DATA in C and C++
SQLSTATE and SQLCODE Variables in C and C++
Programming Considerations for Java
Comparison of SQLJ to JDBC
Advantages of Java over Other Languages
SQL Security in Java
Source and Output Files for Java
Java Class Libraries
Java Packages
Supported SQL Data Types in Java
SQLSTATE and SQLCODE Values in Java
Trace Facilities in Java
Creating Java Applications and Applets
JDBC Programming
How the DB2Appl Program Works
Distributing a JDBC Application
Distributing and Running a JDBC Applet
JDBC 2.0
SQLJ Programming
DB2 SQLJ Support
Embedding SQL Statements in Java
Host Variables in Java
Calls to Stored Procedures and Functions in SQLJ
Compiling and Running SQLJ Programs
SQLJ Translator Options
Stored Procedures and UDFs in Java
Where to Put Java Classes
Updating Java Classes for Routines
Debugging Stored Procedures in Java
Java Stored Procedures and UDFs
Using LOBs and Graphical Objects With JDBC 1.22
JDBC and SQLJ Interoperability
Session Sharing
Connection Resource Management in Java
Programming Considerations for Perl
Perl Restrictions
Connecting to a Database Using Perl
Fetching Results in Perl
Parameter Markers in Perl
SQLSTATE and SQLCODE Variables in Perl
Perl DB2 Application Example
Programming Considerations for COBOL
Language Restrictions in COBOL
Input and Output Files for COBOL
Include Files for COBOL
Embedding SQL Statements in COBOL
Host Variables in COBOL
Naming Host Variables in COBOL
Declaring Host Variables
Indicator Variables in COBOL
LOB Declarations in COBOL
LOB Locator Declarations in COBOL
File Reference Declarations in COBOL
Host Structure Support in COBOL
Indicator Tables in COBOL
Using REDEFINES in COBOL Group Data Items
Using BINARY/COMP-4 COBOL Data Types
Supported SQL Data Types in COBOL
FOR BIT DATA in COBOL
SQLSTATE and SQLCODE Variables in COBOL
Japanese or Traditional Chinese EUC, and UCS-2 Considerations for COBOL
Object Oriented COBOL
Programming Considerations for FORTRAN
Language Restrictions in FORTRAN
Call by Reference in FORTRAN
Debugging and Comment Lines in FORTRAN
Precompiling Considerations for FORTRAN
Input and Output Files for FORTRAN
Include Files for FORTRAN
Including Files in FORTRAN
Embedding SQL Statements in FORTRAN
Host Variables in FORTRAN
Naming Host Variables in FORTRAN
Declaring Host Variables
Indicator Variables in FORTRAN
LOB Declarations in FORTRAN
LOB Locator Declarations in FORTRAN
File Reference Declarations in FORTRAN
Supported SQL Data Types in FORTRAN
SQLSTATE and SQLCODE Variables in FORTRAN
Considerations for Multi-byte Character Sets in FORTRAN
Japanese or Traditional Chinese EUC, and UCS-2 Considerations for FORTRAN
Programming Considerations for REXX
Language Restrictions for REXX
Registering SQLEXEC, SQLDBS and SQLDB2 in REXX
Embedding SQL Statements in REXX
Host Variables in REXX
Naming Host Variables in REXX
Referencing Host Variables in REXX
Indicator Variables in REXX
Predefined REXX Variables
LOB Host Variables in REXX
LOB Locator Declarations in REXX
LOB File Reference Declarations in REXX
Clearing LOB Host Variables in REXX
Supported SQL Data Types in REXX
Using Cursors in REXX
Execution Requirements for REXX
Bind Files for REXX
API Syntax for REXX
REXX Stored Procedures
Calling Stored Procedures in REXX
Japanese or Traditional Chinese EUC Considerations for REXX
DB2 API Non-Embedded SQL Samples
DB2 API Embedded SQL Samples
Embedded SQL Samples With No DB2 APIs
User-Defined Function Samples
DB2 Call Level Interface Samples
Java Samples
SQL Procedure Samples
ADO, RDO, and MTS Samples
Object Linking and Embedding Samples
Command Line Processor Samples
Log Management User Exit Samples
DB2DARI Stored Procedures
Using the SQLDA in a Client Application
Using Host Variables in a DB2DARI Client
Using the SQLDA in a Stored Procedure
Summary of Data Structure Usage
Input/Output SQLDA and SQLCA Structures
Return Values for DB2DARI Stored Procedures
DB2GENERAL UDFs and Stored Procedures
Supported SQL Data Types
Classes for Java Stored Procedures and UDFs
NOT FENCED Stored Procedures
Example Input-SQLDA Programs
How the Example Input-SQLDA Client Application Works
C Example: V5SPCLI.SQC
How the Example Input-SQLDA Stored Procedure Works
C Example: V5SPSRV.SQC
Using Data Definition Language (DDL)
Using Data Manipulation Language (DML)
Numeric Data Types
Mixed-Byte Data
Long Fields
Large Object (LOB) Data Type
User Defined Types (UDTs)
ROWID Data Type
64-bit Integer (BIGINT) data type
Using Data Control Language (DCL)
Connecting and Disconnecting
Precompiling
Blocking
Package Attributes
C Null-terminated Strings
Standalone SQLCODE and SQLSTATE
Defining a Sort Order
Managing Referential Integrity
Locking
Differences in SQLCODEs and SQLSTATEs
Using System Catalogs
Numeric Conversion Overflows on Retrieval Assignments
Isolation Levels
Stored Procedures
Stored Procedure Builder
NOT ATOMIC Compound SQL
Multisite Update with DB2 Connect
Host or AS/400 Server SQL Statements Supported by DB2 Connect
Host or AS/400 Server SQL Statements Rejected by DB2 Connect
DB2 PDF Files and Printed Books
DB2 Information
Printing the PDF Books
Ordering the Printed Books
DB2 Online Documentation
Accessing Online Help
Viewing Information Online
Using DB2 Wizards
Setting Up a Document Server
Searching Information Online
Trademarks