Who Should Use this book
How this Book is Structured
About This Book
Who Should Use this book
How this Book is Structured
Chapter 1. Introduction to CLI
DB2 CLI Background Information
Differences Between DB2 CLI and Embedded SQL
Comparing Embedded SQL and DB2 CLI
Advantages of Using DB2 CLI
Deciding on Embedded SQL or DB2 CLI
Supported Environments
Other Information Sources
Chapter 2. Writing a DB2 CLI Application
Initialization and Termination
Handles
Connecting to One or More Data Sources
Initialization and Connection Example
Transaction Processing
Diagnostics
Data Types and Data Conversion
Working with String Arguments
Querying Environment and Data Source Information
Chapter 3. Using Advanced Features
Environment, Connection, and Statement Attributes
Writing Multi-Threaded Applications
When to Use Multiple Threads
Programming Tips
Multisite Updates (Two Phase Commit)
DB2 as Transaction Monitor
Microsoft Transaction Server (MTS) as Transaction Monitor
Process-based XA-Compliant Transaction Program Monitor (XA TP)
Host and AS/400 Database Servers
Querying System Catalog Information
Input Arguments on Catalog Functions
Catalog Functions Example
Scrollable Cursors
Static, Read-Only Cursor
Keyset-Driven Cursor
Deciding on Which Cursor Type to Use
Specifying the Rowset Returned from the Result Set
Typical Scrollable Cursors Application
Using Bookmarks with Scrollable Cursors
Sending/Retrieving Long Data in Pieces
Specifying Parameter Values at Execute Time
Fetching Data in Pieces
Piecewise Input and Retrieval Example
Using Arrays to Input Parameter Values
Column-Wise Array Insert
Row-Wise Array Insert
Retrieving Diagnostic Information
Parameter Binding Offsets
Array Input Example
Retrieving a Result Set into an Array
Returning Array Data for Column-Wise Bound Data
Returning Array Data for Row-Wise Bound Data
Column Binding Offsets
Column-Wise, Row-Wise Binding Example
Using Descriptors
Descriptor Types
Values Stored in a Descriptor
Allocating and Freeing Descriptors
Getting, Setting, and Copying Descriptor Fields
Descriptor Sample
Using Compound SQL
ATOMIC and NOT ATOMIC Compound SQL
Compound SQL Error Handling
Compound SQL Example
Using Large Objects
LOB Examples
Using LOBs in ODBC Applications
Using User Defined Types (UDT)
User Defined Type Example
Using Stored Procedures
Calling Stored Procedures
Registering Stored Procedures
Handling Stored Procedure Arguments (SQLDA)
Returning Result Sets from Stored Procedures
Writing a Stored Procedure in CLI
Stored Procedure Example
Mixing Embedded SQL and DB2 CLI
Mixed Embedded SQL and DB2 CLI Example
Asynchronous Execution of CLI
Typical Asynchronous Application
Sample Asynchronous Application
Using Vendor Escape Clauses
Escape Clause Syntax
ODBC Date, Time, Timestamp Data
ODBC Outer Join Syntax
LIKE Predicate Escape Clauses
Stored Procedure Call Syntax
ODBC Scalar Functions
Chapter 4. Configuring CLI/ODBC and Running Sample Applications
Setting up the DB2 CLI Runtime Environment
Running CLI/ODBC Programs
Platform Specific Details for CLI/ODBC Access
Detailed Configuration Information
Application Development Environments
Compiling a Sample Application
Compile and Link Options
DB2 CLI/ODBC Configuration Keyword Listing
Configuration Keywords
Chapter 5. DB2 CLI Functions
DB2 CLI Function Summary
SQLAllocConnect - Allocate Connection Handle
SQLAllocEnv - Allocate Environment Handle
SQLAllocHandle - Allocate Handle
SQLAllocStmt - Allocate a Statement Handle
SQLBindCol - Bind a Column to an Application Variable or LOB Locator
SQLBindFileToCol - Bind LOB File Reference to LOB Column
SQLBindFileToParam - Bind LOB File Reference to LOB Parameter
SQLBindParameter - Bind A Parameter Marker to a Buffer or LOB Locator
SQLBrowseConnect - Get Required Attributes to Connect to Data source
SQLBuildDataLink - Build DATALINK Value
SQLBulkOperations - Add, Update, Delete or Fetch a Set of Rows
SQLCancel - Cancel Statement
SQLCloseCursor - Close Cursor and Discard Pending Results
SQLColAttribute - Return a Column Attribute
SQLColAttributes - Get Column Attributes
SQLColumnPrivileges - Get Privileges Associated With The Columns of A Table
SQLColumns - Get Column Information for a Table
SQLConnect - Connect to a Data Source
SQLCopyDesc - Copy Descriptor Information Between Handles
SQLDataSources - Get List of Data Sources
SQLDescribeCol - Return a Set of Attributes for a Column
SQLDescribeParam - Return Description of a Parameter Marker
SQLDisconnect - Disconnect from a Data Source
SQLDriverConnect - (Expanded) Connect to a Data Source
SQLEndTran - End Transactions of a Connection
SQLError - Retrieve Error Information
SQLExecDirect - Execute a Statement Directly
SQLExecute - Execute a Statement
SQLExtendedBind - Bind an Array of Columns
SQLExtendedFetch - Extended Fetch (Fetch Array of Rows)
SQLExtendedPrepare - Prepare a Statement and Set Statement Attributes
SQLFetch - Fetch Next Row
SQLFetchScroll - Fetch Rowset and Return Data for All Bound Columns
SQLForeignKeys - Get the List of Foreign Key Columns
SQLFreeConnect - Free Connection Handle
SQLFreeEnv - Free Environment Handle
SQLFreeHandle - Free Handle Resources
SQLFreeStmt - Free (or Reset) a Statement Handle
SQLGetConnectAttr - Get Current Attribute Setting
SQLGetConnectOption - Return Current Setting of A Connect Option
SQLGetCursorName - Get Cursor Name
SQLGetData - Get Data From a Column
SQLGetDataLinkAttr - Get DataLink Attribute Value
SQLGetDescField - Get Single Field Settings of Descriptor Record
SQLGetDescRec - Get Multiple Field Settings of Descriptor Record
SQLGetDiagField - Get a Field of Diagnostic Data
SQLGetDiagRec - Get Multiple Fields Settings of Diagnostic Record
SQLGetEnvAttr - Retrieve Current Environment Attribute Value
SQLGetFunctions - Get Functions
SQLGetInfo - Get General Information
SQLGetLength - Retrieve Length of A String Value
SQLGetPosition - Return Starting Position of String
SQLGetSQLCA - Get SQLCA Data Structure
SQLGetStmtAttr - Get Current Setting of a Statement Attribute
SQLGetStmtOption - Return Current Setting of A Statement Option
SQLGetSubString - Retrieve Portion of A String Value
SQLGetTypeInfo - Get Data Type Information
SQLMoreResults - Determine If There Are More Result Sets
SQLNativeSql - Get Native SQL Text
SQLNumParams - Get Number of Parameters in A SQL Statement
SQLNumResultCols - Get Number of Result Columns
SQLParamData - Get Next Parameter For Which A Data Value Is Needed
SQLParamOptions - Specify an Input Array for a Parameter
SQLPrepare - Prepare a Statement
SQLPrimaryKeys - Get Primary Key Columns of A Table
SQLProcedureColumns - Get Input/Output Parameter Information for A Procedure
SQLProcedures - Get List of Procedure Names
SQLPutData - Passing Data Value for A Parameter
SQLRowCount - Get Row Count
SQLSetColAttributes - Set Column Attributes
SQLSetConnectAttr - Set Connection Attributes
SQLSetConnection - Set Connection Handle
SQLSetConnectOption - Set Connection Option
SQLSetCursorName - Set Cursor Name
SQLSetDescField - Set a Single Field of a Descriptor Record
SQLSetDescRec - Set Multiple Descriptor Fields for a Column or Parameter Data
SQLSetEnvAttr - Set Environment Attribute
SQLSetParam - Bind A Parameter Marker to a Buffer or LOB Locator
SQLSetPos - Set the Cursor Position in a Rowset
SQLSetStmtAttr - Set Options Related to a Statement
SQLSetStmtOption - Set Statement Option
SQLSpecialColumns - Get Special (Row Identifier) Columns
SQLStatistics - Get Index and Statistics Information For A Base Table
SQLTablePrivileges - Get Privileges Associated With A Table
SQLTables - Get Table Information
SQLTransact - Transaction Management
Appendix A. Programming Hints and Tips
Setting Common Connection Attributes
SQL_ATTR_AUTOCOMMIT
SQL_ATTR_TXN_ISOLATION
Setting Common Statement Attributes
SQL_ATTR_MAX_ROWS
SQL_ATTR_CURSOR_HOLD
SQL_ATTR_TXN_ISOLATION
Comparing Binding and SQLGetData
Increasing Transfer Efficiency
Limiting Use of Catalog Functions
Using Column Names of Function Generated Result Sets
Loading DB2 CLI Specific Functions From ODBC Applications
Making use of Dynamic SQL Statement Caching
Making use of the Global Dynamic Statement Cache
Optimizing Insertion and Retrieval of Data
Optimizing for Large Object Data
Case Sensitivity of Object Identifiers
Using SQLDriverConnect Instead of SQLConnect
Implementing an SQL Governor
Turning Off Statement Scanning
Holding Cursors Across Rollbacks
Preparing Compound SQL Sub-Statements
Casting User Defined Types (UDTs)
Use Multiple Threads rather than Asynchronous Execution
Using Deferred Prepare to Reduce Network Flow
Appendix B. Migrating Applications
Summary of Changes
Incompatibilities
Deprecated Functions Not Supported in a 64-bit Environment
Changes from Version 2.1.1 to 5.0.0
DB2 CLI Functions Deprecated for Version 5
Replacement of the Pseudo Catalog Table for Stored Procedures
Setting a Subset of Statement Attributes using SQLSetConnectAttr()
Caching Statement Handles on the Client
Changes to SQLColumns() Return Values
Changes to SQLProcedureColumns() Return Values
Changes to the InfoTypes in SQLGetInfo()
Deferred Prepare now on by Default
Changes from version 2.1.0 to 2.1.1
Stored Procedures that return multi-row result sets
Data Conversion and Values for SQLGetInfo
Changes from version 1.x to 2.1.0
AUTOCOMMIT and CURSOR WITH HOLD Defaults
Graphic Data Type Values
SQLSTATES
Mixing Embedded SQL, Without CONNECT RESET
Use of VARCHAR FOR BIT DATA
User Defined Types in Predicates
Data Conversion Values for SQLGetInfo
Function Prototype Changes
Setting the DB2CLI_VER Define
Appendix C. DB2 CLI and ODBC
ODBC Function List
Isolation Levels
Appendix D. Extended Scalar Functions
String Functions
Numeric Functions
Date and Time Functions
System Functions
Conversion Function
Appendix E. SQLSTATE Cross Reference
Appendix F. Data Conversion
Data Type Attributes
Precision
Scale
Length
Display Size
Converting Data from SQL to C Data Types
Converting Character SQL Data to C Data
Converting Graphic SQL Data to C Data
Converting Numeric SQL Data to C Data
Converting Binary SQL Data to C Data
Converting Date SQL Data to C Data
Converting Time SQL Data to C Data
Converting Timestamp SQL Data to C Data
SQL to C Data Conversion Examples
Converting Data from C to SQL Data Types
Converting Character C Data to SQL Data
Converting Numeric C Data to SQL Data
Converting Binary C Data to SQL Data
Converting DBCHAR C Data to SQL Data
Converting Date C Data to SQL Data
Converting Time C Data to SQL Data
Converting Timestamp C Data to SQL Data
C to SQL Data Conversion Examples
Appendix G. Catalog Views for Stored Procedures
Appendix H. Pseudo Catalog Table for Stored Procedure Registration
Appendix I. Supported SQL Statements
Appendix J. CLI Sample Code
Embedded SQL Example
Interactive SQL Example
Appendix K. Using the DB2 CLI/ODBC/JDBC Trace Facility
Enabling the Trace Using the db2cli.ini File
Locating the Resulting Files
Reading the Trace Information
Detailed Trace File Format
Example Trace File
Tracing Multi-Threaded or Multi-Process Applications
ODBC Driver Manager Tracing
Appendix L. Using the DB2 Library
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
Appendix M. Notices
Trademarks
Trademarks of Other Companies