IBM Books

Application Development Guide

Table of Contents

DB2 Application Development Concepts

  • Getting Started with DB2 Application Development
  • About This Book
  • Who Should Use This Book
  • How to Use This Book
  • Conventions
  • Related Publications

  • Coding a DB2 Application
  • 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
  • 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 in Applications

  • Embedded SQL Overview
  • 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

  • Writing Static SQL Programs
  • Characteristics and Reasons for Using Static SQL
  • Advantages of Static SQL
  • Example: Static SQL Program
  • Coding SQL Statements to Retrieve and Manipulate Data
  • Using Host Variables
  • Using Indicator Variables
  • 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

  • Writing Dynamic SQL 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

    • Stored Procedures

  • Stored Procedures
  • Stored Procedure Overview
  • Advantages of Stored Procedures
  • Writing Stored Procedures
  • Client Application
  • Stored Procedure on the Server
  • Example OUT Stored Procedure
  • Code Page Considerations
  • C++ Consideration
  • Graphic Host Variable Considerations
  • Multisite Update Consideration
  • Building the Stored Procedure
  • Client Application
  • Stored Procedure
  • NOT FENCED Stored Procedures
  • Returning Result Sets from Stored Procedures
  • Resolving Problems
  • Overview of Stored Procedure Builder
  • What is SPB?
  • Advantages of Using SPB

    • Object-Relational Programming

  • Using the Object-Relational Capabilities
  • Why Use the DB2 Object Extensions?
  • DB2 Approach to Supporting Objects
  • Combining UDTs, UDFs, and LOBs
  • Examples of Complex Applications
  • Example: Defining the UDT and UDFs
  • Example: Exploiting LOB Function to Populate the Database
  • Example: Exploiting UDFs to Query Instances of UDTs

  • Using Large Objects (LOBs)
  • 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
  • FORTRAN Sample: LOBLOC.SQF
  • 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

  • User-Defined Functions (UDFs)
  • What are UDFs?
  • Why Use UDFs?
  • UDF Concepts
  • Implementing UDFs
  • Writing UDFs
  • Registering UDFs
  • Examples of Registering UDFs
  • 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 UDFs
  • Referring to Functions
  • Examples of Function Invocations
  • Using Parameter Markers in Functions
  • Using Qualified Function Reference
  • Using Unqualified Function Reference
  • Summary of Function References

  • User-defined Distinct Types (UDTs)
  • Why Use UDTs?
  • Defining a UDT
  • Resolving Unqualified UDTs
  • Examples of Using CREATE DISTINCT TYPE
  • Example: Money
  • Example: Job Application
  • Defining Tables with UDTs
  • Example: Sales
  • Example: Application Forms
  • Manipulating UDTs
  • Examples of Manipulating UDTs
  • Example: Comparisons Between UDTs and Constants
  • Example: Casting Between UDTs
  • Example: Comparisons Involving UDTs
  • Example: Sourced UDFs Involving UDTs
  • Example: Assignments Involving UDTs
  • Example: Assignments in Dynamic SQL
  • Example: Assignments Involving Different UDTs
  • Example: Use of UDTs in UNION

  • Writing User-Defined Functions (UDFs)
  • 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
  • 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

  • Using Triggers in an Active DBMS
  • Why Use Triggers?
  • Benefits and Value 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

  • Programming in Complex Environments
  • National Language Support Considerations
  • Collating Sequences
  • Deriving Code Page Values
  • Deriving Locales in Application Programs
  • Programming Considerations
  • Unicode/UCS-2 and UTF-8 Support in DB2 UDB
  • Running CLI/ODBC/JDBC/SQLJ Programs in a DBCS Environment
  • Japanese and Traditional Chinese EUC and UCS-2 Code Set Considerations
  • Mixed EUC and Double-Byte Client and Database Considerations
  • Considerations for Traditional Chinese Users
  • Developing Japanese or Traditional Chinese EUC Applications
  • Developing for Mixed Code Set Environments
  • Applications Connected to a Unicode (UCS-2) Database
  • 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

  • Programming Considerations in a Partitioned Environment
  • 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

  • Writing Programs for DB2 Federated Systems
  • 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

    • Language Considerations

  • Programming in C and C++
  • Programming Considerations
  • Language Restrictions
  • Trigraph Sequences
  • C++ Type Decoration Consideration
  • Input and Output Files
  • Include Files
  • Including Files
  • Embedding SQL Statements
  • Host Variables
  • Naming Host Variables
  • Declaring Host Variables
  • Indicator Variables
  • 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
  • C Macro Expansion
  • Host Structure Support
  • Indicator Tables
  • Null-terminated Strings
  • Pointer Data Types
  • Using Class Data Members as Host Variables
  • Using Qualification and Member Operators
  • Handling Graphic Host Variables
  • Japanese or Traditional Chinese EUC, and UCS-2 Considerations
  • Supported SQL Data Types
  • FOR BIT DATA
  • SQLSTATE and SQLCODE Variables

  • Programming in Java
  • Programming Considerations
  • Comparison of SQLJ to JDBC
  • Advantages Over Other Languages
  • SQL Security
  • Source and Output Files
  • Java Class Libraries
  • Java Packages
  • Supported SQL Data Types
  • SQLSTATE and SQLCODE Values
  • DB2 Trace Facilities
  • 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
  • Host Variables
  • Calls to Stored Procedures and Functions
  • Compiling and Running SQLJ Programs
  • SQLJ Precompile Options
  • Stored Procedures and UDFs
  • Where to Put Java Classes
  • Debugging Stored Procedures
  • Java Stored Procedures and UDFs
  • Using LOBs and Graphical Objects With JDBC 1.22
  • JDBC and SQLJ Interoperability
  • Session Sharing
  • Connection Resource Management

  • Programming in COBOL
  • Programming Considerations
  • Language Restrictions
  • Input and Output Files
  • Include Files
  • Embedding SQL Statements
  • Host Variables
  • Naming Host Variables
  • Declaring Host Variables
  • Indicator Variables
  • LOB Declarations in COBOL
  • LOB Locator Declarations in COBOL
  • File Reference Declarations in COBOL
  • Host Structure Support
  • Indicator Tables
  • Using REDEFINES in COBOL Group Data Items
  • Using BINARY/COMP-4 COBOL Data Types
  • Supported SQL Data Types
  • FOR BIT DATA
  • SQLSTATE and SQLCODE Variables
  • Japanese or Traditional Chinese EUC, and UCS-2 Considerations
  • Object Oriented COBOL

  • Programming in FORTRAN
  • Programming Considerations
  • Language Restrictions
  • Call by Reference
  • Debugging and Comment Lines
  • Precompiling Considerations
  • Input and Output Files
  • Include Files
  • Including Files
  • Embedding SQL Statements
  • Host Variables
  • Naming Host Variables
  • Declaring Host Variables
  • Indicator Variables
  • LOB Declarations in FORTRAN
  • LOB Locator Declarations in FORTRAN
  • File Reference Declarations in FORTRAN
  • Supported SQL Data Types
  • SQLSTATE and SQLCODE Variables
  • Considerations for Multi-byte Character Sets
  • Japanese or Traditional Chinese EUC, and UCS-2 Considerations

  • Programming in REXX
  • Programming Considerations
  • Language Restrictions
  • Registering SQLEXEC, SQLDBS and SQLDB2
  • Embedding SQL Statements
  • Host Variables
  • Naming Host Variables
  • Referencing Host Variables
  • 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
  • Supported SQL Data Types
  • Using Cursors in REXX
  • Execution Requirements for REXX
  • Bind Files for REXX
  • API Syntax
  • REXX Stored Procedures
  • Calling Stored Procedures
  • Japanese or Traditional Chinese EUC Considerations

    • Appendixes

  • Appendix A. Supported SQL Statements (DB2 Universal Database)

  • Appendix B. Sample Programs
  • 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
  • ADO, RDO, and MTS Samples
  • Object Linking and Embedding Samples
  • Command Line Processor Samples
  • Log Management User Exit Samples

  • Appendix C. DB2DARI and DB2GENERAL Stored Procedures and UDFs
  • 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
  • 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
  • FORTRAN UNIX Example: INPCLI.SQF
  • How the Example Input-SQLDA Stored Procedure Works
  • REXX OS/2 Example: INPSRV.CMD

  • Appendix D. Programming in a Host or AS/400 Environment
  • 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
  • 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

  • Appendix E. Country Code and Code Page Support

  • Appendix F. Simulating EBCDIC Binary Collation

  • Appendix G. How the DB2 Library Is Structured
  • Completing Tasks with SmartGuides
  • Accessing Online Help
  • DB2 Information - Hardcopy and Online
  • Viewing Online Information
  • Accessing Information with the Information Center
  • Setting Up a Document Server
  • Searching Online Information
  • Printing the PostScript Books
  • Ordering the Printed Books

  • Appendix H. Notices
  • Trademarks
  • Trademarks of Other Companies

  • Appendix I. Contacting IBM

  • Index

    • [ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

    [ DB2 List of Books | Search the DB2 Books ]