IBM Books

Call Level Interface Guide and Reference

This document contains proprietary information of IBM. It is provided under a license agreement and is protected by copyright law. The information contained in this publication does not include any product warranties and any statements provided in this manual should not be interpreted as such.

Order publications through your IBM representative or the IBM branch office serving your locality or by calling 1-800-879-2755 in U.S. or 1-800-IBM-4YOU in Canada.

When you send information to IBM, you grant IBM a nonexclusive right to use or distribute the information in any way it believes appropriate without incurring any obligation to you.

© Copyright International Business Machines Corporation 1993, 1997. All rights reserved.
Note to U.S. government Users -- Documentation related to restricted rights -- Use, duplication or disclosure is subject to restrictions set forth in GSA ADP Schedule contract with IBM Corp.

Table of Contents

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
  • Distributed Unit of Work (Coordinated Distributed Transactions)
  • Attributes that Govern Distributed Unit of Work Semantics
  • Establishing a Coordinated Transaction Connection
  • Distributed Unit of Work Example
  • Querying System Catalog Information
  • Input Arguments on Catalog Functions
  • Catalog Functions Example
  • Scrollable Cursors
  • 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 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. Functions

  • Status of this Function in DB2 CLI Version 5
  • Purpose
  • Syntax
  • Arguments
  • Usage
  • Return Codes
  • Diagnostics
  • Restrictions
  • Example
  • References
  • 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
  • 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
  • SQLExtendedFetch - Extended Fetch (Fetch Array of Rows)
  • 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
  • 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
  • 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

  • SYSCAT.PROCEDURES
  • SYSCAT.PROCPARMS

    • Appendix H. Pseudo Catalog Table for Stored Procedure Registration

    Appendix I. Supported SQL Statements

    Appendix J. Example Code Listing

  • Embedded SQL Example
  • Interactive SQL Example

    • Appendix K. Using the DB2 CLI/ODBC 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 Muli-Threaded or Muli-Process Applications
  • ODBC Driver Manager Tracing

    • Appendix L. How the DB2 Library Is Structured

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

    • Appendix M. Notices

  • Trademarks
  • Trademarks of Other Companies

    • Appendix N. Contacting IBM

    Appendix O. Bibliography

    Index

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

    [ DB2 List of Books | Search the DB2 Books ]