IBM Books

Embedded SQL Programming Guide

Table of Contents

About This Book

  • Who Should Use This Book
  • How To Use This Book
  • Highlighting Conventions
  • Related Publications

    • Chapter 1. Getting Started With DB2 Application Development

  • Prerequisites for Programming
  • Coding a DB2 Application
  • Setting Up the Program
  • Declaring Variables That Interact with the Database Manager
  • Relating Host Variables to an SQL Statement
  • Handling Errors and Warnings with the SQLCA
  • Using Additional Nonexecutable Statements
  • Connecting to the Database Server
  • Coding Transactions
  • Beginning a Transaction
  • Ending a Transaction
  • Ending the Program
  • Implicitly Ending a Transaction
  • On Most Supported Operating Systems
  • On Windows 95 and Windows NT Operating Systems
  • When Using the DB2 Context APIs
  • Summary
  • Designing an Application For DB2
  • Access to Data
  • Embedded SQL
  • Call Level Interface (CLI)
  • REXX
  • Query Products
  • Data Value Control
  • Data Types
  • Table Check Constraints
  • Referential Integrity Constraints
  • Views With Check Option
  • Application Logic and Program Variable Types
  • Data Relationship Control
  • Referential Integrity Constraints
  • Triggers
  • Application Logic
  • Application Logic at the Server
  • Stored Procedures
  • User-defined Functions
  • Triggers
  • Alternatives for Coding DB2 Applications
  • Embedding SQL Statements in a Host Language
  • Interactive Programming Using REXX
  • Programming With the DB2 Call Level Interface (CLI)
  • Creating and Preparing the Source Files
  • Creating Packages for Compiled Applications
  • Precompiling
  • Compiling and Linking
  • Binding
  • Advantages of Deferred Binding
  • DB2 Bind File Dump Tool - db2bfd
  • Application, Bind File, and Package Relationships
  • Timestamps
  • Rebinding
  • Supported SQL Statements
  • Authorization Considerations
  • Dynamic SQL
  • Static SQL
  • Using APIs
  • Example
  • Database Manager APIs Used in Embedded or 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

    • Chapter 2. Writing Embedded Static Programs

  • Characteristics and Reasons for Using Static SQL
  • Advantages of Static SQL
  • Example Static SQL Program
  • How the Example Static SQL Program Works
  • C Example: STATIC.SQC
  • COBOL Example: STATIC.SQB
  • FORTRAN UNIX Example: STATIC.SQF
  • Coding SQL Statements to Retrieve and Manipulate Data
  • Retrieving Data
  • Using Host Variables
  • 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
  • Read Only Cursors
  • WITH HOLD Option
  • Example Cursor Program
  • How the Example Cursor Program Works
  • C Example: CURSOR.SQC
  • COBOL Example: CURSOR.SQB
  • FORTRAN UNIX Example: CURSOR.SQF
  • Updating and Deleting Retrieved Data
  • Updating Retrieved Data
  • Deleting Retrieved Data
  • Types of Cursors
  • Example OPENFTCH Program
  • How the Example OPENFTCH SQL Program Works
  • C Example: OPENFTCH.SQC
  • COBOL Example: OPENFTCH.SQB
  • FORTRAN Example: OPENFTCH.SQF
  • Advanced Scrolling Techniques
  • Scroll through Data that has Already Been Retrieved
  • Keeping a Copy of the Data
  • Retrieving the Data a Second Time
  • Retrieving from the Beginning
  • Retrieving from the Middle
  • Order of Rows in the Second Result Table
  • Retrieving in Reverse Order
  • Establish a Position at the End of a Table
  • Update Previously Retrieved Data
  • Example UPDAT Program
  • How the Example UPDAT Program Works
  • C Example: UPDAT.SQC
  • COBOL Example: UPDAT.SQB
  • FORTRAN UNIX Example: UPDAT.SQF
  • REXX Example: UPDAT.CMD
  • Diagnostic Handling and the SQLCA Structure
  • Return Codes
  • SQLCODE and SQLSTATE
  • Handling Errors using the WHENEVER Statement
  • Exception, Signal, Interrupt Handler Considerations
  • Exit List Routine Considerations
  • Using GET ERROR MESSAGE in Example Programs
  • C Example: UTIL.C
  • COBOL Example: CHECKERR.CBL
  • FORTRAN Example: UTIL.F
  • REXX Example: CHECKERR procedure within REXX Examples

    • Chapter 3. Writing Dynamic 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
  • How the Example Dynamic SQL Program Works
  • C Example: DYNAMIC.SQC
  • COBOL Example: DYNAMIC.SQB
  • FORTRAN UNIX Example: DYNAMIC.SQF
  • REXX Example: DYNAMIC.CMD
  • 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
  • Determining Statement Type
  • Varying-List SELECT Statement
  • Saving SQL Requests from End Users
  • Example ADHOC Program
  • How the Example ADHOC Program Works
  • C Example: ADHOC.SQC
  • Variable Input to Dynamic SQL
  • Using Parameter Markers
  • Example VARINP Program
  • How the Example VARINP SQL Program Works
  • C Example: VARINP.SQC
  • COBOL Example: VARINP.SQB
  • FORTRAN Example: VARINP.SQF
  • The DB2 Call Level Interface (CLI)
  • Comparing Embedded SQL and DB2 CLI
  • Advantages of Using DB2 CLI
  • Deciding on Embedded SQL or DB2 CLI

    • Chapter 4. Programming Considerations for Concurrency and Performance

  • Concurrency
  • Repeatable Read
  • Read Stability
  • Cursor Stability
  • Uncommitted Read
  • Choosing the Isolation Level
  • Specifying the Isolation Level
  • Transactions
  • Transaction Management
  • Transaction Logging
  • Locking
  • Attributes of Locks
  • Locks and Application Performance
  • Concurrency and Granularity
  • Lock Compatibility
  • Lock Conversion
  • Lock Escalation
  • Lock Waits and Timeouts
  • Deadlocks
  • Factors Affecting Locking
  • Application Processing
  • Access Paths
  • LOCK TABLE Statement
  • CLOSE CURSOR WITH RELEASE
  • Summary of Locking Considerations
  • Row Blocking
  • Adjusting the Optimization Class
  • How Do You Set the Optimization Class?
  • How Much Optimization is Necessary?
  • Join Strategies in a Partitioned Database
  • Broadcast Outer-Table Joins
  • Directed Outer-Table Joins
  • Directed Inner-Table and Outer-Table Joins
  • Broadcast Inner-Table Joins
  • Directed Inner-Table Joins
  • Collocated Joins
  • Table Queues

    • Chapter 5. Writing Stored Procedures

  • Why Use Stored Procedures?
  • Invoking Stored Procedures
  • Writing Stored Procedures on DB2
  • Client Application
  • SQLDA Structure
  • Allocating Host Variables
  • Running the Client Application
  • Stored Procedure
  • Data Structure Manipulation
  • Return Values
  • Code Page Considerations
  • C++ Consideration
  • Java Considerations
  • Graphic Host Variable Considerations
  • Distributed Unit of Work (DUOW) Consideration
  • Summary of Data Structure Usage
  • Building the Stored Procedure Application
  • Client Application
  • Stored Procedure
  • Resolving Problems
  • Working with Not-Fenced Stored Procedures
  • Example Output-SQLDA Programs
  • How the Example Output-SQLDA Client Application Works
  • C Example: OUTCLI.SQC
  • COBOL Example: OUTCLI.SQB
  • FORTRAN UNIX Example: OUTCLI.SQF
  • REXX Example: OUTCLI.CMD
  • How the Example Output-SQLDA Stored Procedure Works
  • C Example: OUTSRV.SQC
  • COBOL Example: OUTSRV.SQB
  • FORTRAN UNIX Example: OUTSRV.SQF
  • REXX OS/2 Example: OUTSRV.CMD
  • Example Input-SQLDA Programs
  • How the Example Input-SQLDA Client Application Works
  • C Example: INPCLI.SQC
  • COBOL Example: INPCLI.SQB
  • FORTRAN UNIX Example: INPCLI.SQF
  • REXX Example: INPCLI.CMD
  • How the Example Input-SQLDA Stored Procedure Works
  • C Example: INPSRV.SQC
  • COBOL Example: INPSRV.SQB
  • FORTRAN UNIX Example: INPSRV.SQF
  • REXX OS/2 Example: INPSRV.CMD
  • Registering Stored Procedures
  • Returning Result Sets From Stored Procedures

    • Chapter 6. Using the Object-Relational Capabilities

  • Why Use the DB2 Object Extensions?
  • DB2 Approach to Supporting Objects
  • Using Large Objects (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 (UDF)
  • 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 (UDT)
  • Why Use UDTs?
  • Defining a UDT
  • Resolving Unqualified UDTs
  • Examples of Using CREATE DISTINCT TYPE
  • Example: Money
  • Example: Resume
  • 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
  • Synergy Between UDTs, UDFs, and LOBs
  • 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
  • Example: Exploiting LOB Locators to Manipulate UDT Instances

    • Chapter 7. Writing User-Defined Functions (UDFs)

  • 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
  • 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 Automation UDFs in BASIC
  • OLE Automation UDFs in C++
  • Scratchpad Considerations
  • Table Function Considerations
  • 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++
  • Example: Mail OLE Automation Table Function in BASIC
  • Debugging your UDF

    • Chapter 8. Using the Active DBMS Capabilities

  • 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

    • Chapter 9. Programming in Complex Environments

  • National Language Support Considerations
  • Collating Sequences
  • Overview
  • Specifying a Collating Sequence
  • Deriving Code Page Values
  • Deriving Locales in Application Programs
  • How DB2 Derives Locales
  • Programming Considerations
  • Coding SQL Statements
  • Coding Remote Stored Procedures and UDFs
  • Package Name Considerations in Mixed Code Page Environments
  • Precompiling and Binding
  • Executing an Application
  • A Note of Caution
  • Conversion Between Different Code Pages
  • Japanese and Traditional-Chinese EUC Code Set Considerations
  • Mixed EUC and Double-Byte Client and Database Considerations
  • Considerations for Traditional-Chinese Users
  • Developing Japanese or Traditional-Chinese EUC Applications
  • Graphic Data Handling
  • Graphic Constants
  • Considerations for UDFs
  • Considerations for Stored Procedures
  • Considerations for DBCLOB Files
  • Collation
  • Developing for Mixed Code Set Environments
  • Unequal Code Page Situations
  • Client-Based Parameter Validation
  • Using the DESCRIBE Statement
  • Using Fixed or Variable Length Data Types
  • Character Conversion String Length Overflow
  • Considerations for Distributed Unit of Work (DUOW)
  • Remote Unit of Work
  • Distributed Unit of Work
  • When to Use DUOW
  • Coding SQL for a DUOW Application
  • Precompiling a DUOW Application
  • Specifying Configuration Parameters for a DUOW Application
  • DUOW Restrictions
  • Accessing DRDA Servers
  • Multiple Thread Database Access
  • Recommendations for Using Multiple Threads
  • Potential Pitfalls when Using Multiple Threads
  • Preventing Deadlocks for Multiple Contexts
  • Concurrent Transactions
  • Potential Pitfalls when Using Concurrent Transactions
  • Preventing Deadlocks for Concurrent Transactions
  • X/Open XA Interface Programming Considerations
  • Application Linkage
  • Working with Large Volumes of Data Across a Network

    • Chapter 10. Programming Considerations in a Partitioned Environment

  • Using Buffered Inserts
  • Considerations for Using Buffered Inserts
  • Restrictions on Using Buffered Inserts
  • Example: Extracting Large Volume of Data (largevol.c)
  • Error-Handling Considerations
  • Severe Errors
  • Merged Multiple SQLCA Structures
  • Identifying the Partition that Returned the Error
  • Debugging
  • Diagnosing a Looping or Suspended application

    • Chapter 11. Programming in C and C++

  • Programming Considerations
  • Language Restrictions
  • Macro Expansion
  • Input and Output Files
  • Including Files
  • Trigraph Sequences
  • C++ Type Decoration Consideration
  • Include 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
  • 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 Considerations
  • Supported SQL Data Types
  • FOR BIT DATA
  • SQLSTATE and SQLCODE Variables

    • Chapter 12. 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 in COBOL
  • Indicator Tables
  • Using BINARY/COMP-4 COBOL Data Types
  • Using REDEFINES in COBOL Group Data Items
  • Supported SQL Data Types
  • FOR BIT DATA
  • SQLSTATE and SQLCODE Variables
  • Japanese or Traditional-Chinese EUC Considerations
  • Object Oriented COBOL

    • Chapter 13. Programming in FORTRAN

  • Programming Considerations
  • Language Restrictions
  • Call by Reference
  • Debugging and Comment Lines
  • Including Files
  • Precompiling Considerations
  • Input and Output Files
  • Include 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 Considerations

    • Chapter 14. 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
  • Considerations on the Client
  • Considerations on the Server
  • Retrieving Precision and SCALE Values from SQLDA Decimal Fields
  • Japanese or Traditional-Chinese EUC Considerations
  • REXX Sample Programs

    • Chapter 15. Programming in Java

  • Getting Started
  • How Does It Work?
  • JDBC Applet and Application Support
  • Java UDFs and Stored Procedures
  • Creating and Running JDBC Applets and Applications
  • Distributing and Running a JDBC Applet
  • Distributing and Running a JDBC Application
  • Extensions
  • Using Graphical and Large Objects
  • Creating Java UDFs and Stored Procedures
  • Mapping Between SQL Types and Java Objects
  • Where to Put Java Classes
  • Creating and Using Java User-Defined Functions
  • Coding a Java UDF
  • Changing How a Java UDF Runs
  • Creating and Using Java Stored Procedures
  • Coding Java Stored Procedures
  • Classes for Java Stored Procedures and UDFs
  • COM.ibm.db2.app.StoredProc
  • COM.ibm.db2.app.UDF
  • COM.ibm.db2.app.Blob
  • COM.ibm.db2.app.Clob

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

    Appendix B. Sample Programs and Extra Examples

  • Installing, Building and Executing the Sample Programs

    • Appendix C. Programming in a DRDA Environment

  • Using Data Definition Language (DDL)
  • Using Data Manipulation Language (DML)
  • Numeric Data Types
  • Mixed-Byte Data
  • Long Fields
  • Large Object (LOB) 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
  • Distributed Unit of Work with DB2 Connect
  • DRDA Server SQL Statements Supported by DB2 Connect
  • DRDA Server SQL Statements Rejected by DB2 Connect

    • Appendix D. Country Code and Code Page Support

    Appendix E. Simulating EBCDIC Binary Collation

    Appendix F. How the DB2 Library Is Structured

  • SmartGuides
  • Online Help
  • DB2 Books
  • About the Information Center

    • Appendix G. Notices

  • Trademarks
  • Trademarks of Other Companies

    • Appendix H. Contacting IBM

    Index

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

    [ DB2 List of Books | Search the DB2 Books ]