![Close [x]](http://dw1.s81c.com/i/c.gif){kind=small}
Open database connectivity (ODBC) is a standard, callable, SQL interface for accessing data from a variety of databases that helps to make applications independent of database management systems. An application can use these interfaces regardless of the underlying database.
ODBC drivers are libraries that have the implementations of the ODBC APIs. Database vendors each have their own set of ODBC drivers, and these drivers communicate to the DBMS using a known protocol.
The following descriptions show the differences between the two ODBC drivers that you can use for application development with Informix, and Figure 1 shows the different databases that the common client CLI Driver works with.
- IBM Informix ODBC Driver — IBM Informix ODBC Driver
is available for download as part of the Informix Client SDK. This
ODBC Driver works only with Informix and uses the SQLI protocol for
communication with Informix servers.
- IBM Data Server CLI Driver (Common CLI Driver) — The IBM Data Server CLI Driver is also referred to as the Common CLI Driver. It is available for download as part of the Informix Client SDK (version 3.50xC6 or later) or the IBM Data Server Driver Package. This ODBC Driver(version v97FP2 or later) supports both Informix (V11.10 or later) and DB2 databases and uses the DRDA protocol for communication with data servers.
Figure 1. Common client CLI Driver with different databases
Benefits for using IBM Data Server CLI Driver for Informix database
The rich features in IBM Data Server CLI Driver help the application to efficiently access data. The following are some of the advanced features that are available in IBM Data Server CLI Driver:
- Asynchronous execution
- LOB locators
- Retry on error
- Advanced tracing mechanisms
- Advanced CLI APIs
- Batch SQL and SQLMoreResults()
- CLI array input chaining
- Mechanism to register Microsoft ODBC DSN from command line
Each of these advanced features will be explained in detail in later sections.
Using IBM Data Server CLI Driver lets a single application access data from Informix and DB2 servers. This application will load IBM Data Server CLI Driver and use it to insert or retrieve data from Informix as well as DB2 servers. Listing 1 shows an example of two connection handles, one connecting to an Informix server and the other connecting to a DB2 server, and how a table is created in both Informix and DB2 databases.
Listing 1. Sample code to connect to both Informix and DB2
...
SQLRETURN cliRC = SQL_SUCCESS;
SQLHANDLE henv1, henv2; // Environment handles
SQLHANDLE hdbc1, hdbc2; // Connection handles
SQLHANDLE hstmt1, hstmt2; // Statement handles
// DB2 Database details
char szDbAlias1[] = "DB2_DATABASE";
char szUser1[] = "USER";
char szPwd1[] = "********";
int rc = 0;
// Informix Database details
char szDbAlias2[] = "INFORMIX_DB";
char szUser2[] = "USER";
char szPwd2[] = "*********";
// allocate an environment handle
cliRC = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, henv1);
if (cliRC != SQL_SUCCESS)
{
printf("\n--ERROR while allocating the environment handle.\n");
return 1;
}
// allocate a database connection handle - DB2
cliRC = SQLAllocHandle(SQL_HANDLE_DBC, henv1, hdbc1);
ENV_HANDLE_CHECK(henv1, cliRC);
// allocate a database connection handle - Informix
cliRC = SQLAllocHandle(SQL_HANDLE_DBC, henv1, hdbc2);
ENV_HANDLE_CHECK(henv1, cliRC);
printf("\n Connecting to the DB2 database %s ...\n", szDbAlias1);
// connect to the database
cliRC = SQLConnect(hdbc1,
(SQLCHAR *)szDbAlias1,
SQL_NTS,
(SQLCHAR *)szUser1,
SQL_NTS,
(SQLCHAR *)szPwd1,
SQL_NTS);
DBC_HANDLE_CHECK(hdbc1, cliRC);
printf(" Connected to the database %s.\n", szDbAlias1);
printf("\n Connecting to the Informix database %s ...\n", szDbAlias2);
// connect to the database
cliRC = SQLConnect(hdbc2,
(SQLCHAR *)szDbAlias2,
SQL_NTS,
(SQLCHAR *)szUser2,
SQL_NTS,
(SQLCHAR *)szPwd2,
SQL_NTS);
DBC_HANDLE_CHECK(hdbc1, cliRC);
printf(" Connected to the database %s.\n", szDbAlias2);
// allocate the handle for statement 1
cliRC = SQLAllocHandle(SQL_HANDLE_STMT, hdbc1, hstmt1);
DBC_HANDLE_CHECK(hdbc1, cliRC);
// allocate the handle for statement 2
cliRC = SQLAllocHandle(SQL_HANDLE_STMT, hdbc2, hstmt2);
DBC_HANDLE_CHECK(hdbc2, cliRC);
printf("\n Executing query against DB2 database %s ...\n", szDbAlias1);
// execute statement 1 on connection 1
cliRC = SQLExecDirect(hstmt1,
(SQLCHAR *)"CREATE TABLE table1(col1 INTEGER)",
SQL_NTS);
MC_STMT_HANDLE_CHECK(hstmt1, henv1, cliRC);
printf(" Query executed against database %s.\n", szDbAlias1);
printf("\n Executing query against Informix database %s ...\n", szDbAlias2);
// execute statement 1 on connection 2
cliRC = SQLExecDirect(hstmt2,
(SQLCHAR *)"CREATE TABLE table1(col1 INTEGER)",
SQL_NTS);
MC_STMT_HANDLE_CHECK(hstmt2, henv1, cliRC);
printf(" Query executed against database %s.\n", szDbAlias2);
// execute statement 2 on connection 1
cliRC = SQLExecDirect(hstmt1, (SQLCHAR *)"DROP TABLE table1", SQL_NTS);
MC_STMT_HANDLE_CHECK(hstmt1, henv1, cliRC);
// execute statement 2 on connection 2
cliRC = SQLExecDirect(hstmt2, (SQLCHAR *)"DROP TABLE table1", SQL_NTS);
MC_STMT_HANDLE_CHECK(hstmt2, henv2, cliRC);
printf("\n Disconnecting from the database %s...\n", szDbAlias1);
// disconnect from the database
cliRC = SQLDisconnect(hdbc1);
DBC_HANDLE_CHECK(hdbc1, cliRC);
printf(" Disconnected from the database %s.\n", szDbAlias1);
printf("\n Disconnecting from the database %s...\n", szDbAlias2);
// disconnect from the database
cliRC = SQLDisconnect(hdbc2);
DBC_HANDLE_CHECK(hdbc2, cliRC);
printf(" Disconnected from the database %s.\n", szDbAlias2);
// free the connection handle
cliRC = SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);
DBC_HANDLE_CHECK(hdbc1, cliRC);
// free the connection handle
cliRC = SQLFreeHandle(SQL_HANDLE_DBC, hdbc2);
DBC_HANDLE_CHECK(hdbc2, cliRC);
// free the environment handle
cliRC = SQLFreeHandle(SQL_HANDLE_ENV, henv1);
ENV_HANDLE_CHECK(henv1, cliRC);
...
|
By default, an application makes calls to an ODBC driver which then synchronously executes statements against the database server. In this mode of operation, the driver does not return control to the application until its own request to the database server is complete. For complex queries taking more than few seconds to execute, synchronous execution may affect the overall application's performance.
With IBM Data Server CLI Driver, applications can take advantage of asynchronous execution.
For more information on CLI Drivers Asynchronous support, see the Asynchronous execution of CLI functions topic in the IBM Information Center.
Applications that use IBM Data Server CLI Driver can use LOB locators to retrieve LOB data in pieces. If you are interested in only a few parts of LOB data, then using LOB locators will greatly reduce network traffic and improve performance. A LOB locator is not stored in the database, it refers to a LOB value during a transaction, and does not persist beyond the transaction in which it was created.
The following CLI APIs uses LOB Locators to retrieve data in pieces.
When the applications bind a wrong datatype for the application parameters, the ODBC drivers will fail because of incorrect binding. IBM Data Server CLI Driver has the capability to detect such errors and take corrective actions using the IPD information obtained during SQLPrepare(). The CLI/ODBC configuration keyword ReportRetryErrorsAsWarnings allows you to set whether error recovery warnings are returned to the application or not. You should correct these errors in the application. Otherwise multiple executions will occur each time the application runs, making it less efficient.
For more information about retry on error, see the RetryOnError CLI/ODBC configuration keyword topic in the IBM Information Center.
IBM Data Server CLI Driver comes with an extensive diagnostic support to diagnose a problem. The following tracing mechanism helps you to trace the complete flow of control and data.
CLI trace helps you to trace the following:
- CLI API names that are being called by the application
- Value of the parameters that are passed to the CLI APIs
- The return codes of the various CLI APIs. This information is captured in a log file.
- Time taken for the API call
You can enable the CLI trace mechanism as shown in Listing 2.
db2trc dump <dump_filename> db2trc off db2trc fmt -cli <dump_filename> <ODBC-CLI_trace_filename> |
For more information on CLI trace, see the Trace CLI/ODBC configuration keyword topic in the IBM Information Center.
You can use db2trc to extensively trace
function and data flows, as well as for troubleshooting any problems that
you are investigating. Enabling this trace facility will negatively impact
system performance, so you should not enable it unless recommended by IBM
Software Support and development teams.
Perform the following steps to enable DB2 trace:
- Enable db2trc:
db2trc on -f db2trc.dmp - Run your application.
- Stop the trace:
db2trc off - Format the dump file:
db2trc flw db2trc.dmp <filename.flw>
db2trc fmt db2trc.dmp <filename.fmt>
For more information on DB2 trace, see the Obtaining a DB2 trace using db2trc topic in the IBM Information Center.
The following shows three advanced CLI APIs.
SQLExtendedPrepare()andSQLExtendedBind()API
IBM Data Server CLI Driver supports
SQLExtendedPrepare() and
SQLExtendedBind() APIs, which drastically
reduce the ODBC API's call, and help you to achieve a goal with minimum
calls.
Click the following links for more information about the arguments and
usage for these APIs.
SQLCreateDb()andSQLDropDb()API
IBM Data Server CLI Driver supports
SQLCreateDb() and
SQLDropDb() APIs. These APIs help you to create
or drop a database.
Click the following links for more information about the arguments and
usage for these APIs:
- Batch SQL and
SQLMoreresults()API
IBM Data Server CLI Driver supports
SQLMoreResults() API. This API helps you to do
the following:
- Execute a batch SQL
- Fetch the result sets returned by a stored procedure
For more information on Batch SQL and SQLMoreresults() API, see the SQLMoreResults function (CLI) - Determine if there are more result sets topic in the IBM Information Center.
You can greatly reduce network traffic by enabling array chaining in IBM Data Server CLI Driver. When enabled, execution of all the prepared statements will be chained and executed together when the chaining ends.
For more information on CLI array input chaining, see the Reduction of network flows with CLI array input chaining topic in the IBM Information Center.
Mechanism to register Microsoft ODBC DSN from command line
You can use the db2cli registerdsn option,
provided by the IBM Data Server, to add or remove a Microsoft ODBC DSN
from the command prompt. When there are multiple installations of IBM Data
Server CLI Driver across multiple workstations, this feature lets you to
use a single command to register the DSNs, rather than manually removing a
DSN using the ODBC Data Source Administrator GUI.
For more information on registering Microsoft ODBC DSN, see the db2cli - DB2 interactive CLI command topic in the IBM Information Center.
Installing the IBM Data Server CLI Driver
To use the IBM Data Server CLI Driver, you need to install one of the following packages.
- IBM Data Server Driver Package — This is a light
weight deployment that provides runtime support for applications using
ODBC, CLI, .NET, OLE DB, PHP, Ruby, JDBC, or SQLJ, without the need of
installing IBM Data Server Client. IBM Data Server Driver installation
can also be invoked while installing Informix CSDK. This is the
recommended package when connecting to Informix servers.
- IBM Data Server Client — This includes all of the functionality of IBM Data Server Runtime Client, along with the functionalities for database administration, application development, and client/server configuration. Though this supports connecting to Informix server, it is not the recommended client package for Informix users.
How to install and configure IBM Data Server Driver
Click the following links for information on how to install IBM Data Server Driver Package on Windows and UNIX platforms respectively.
- Installing IBM Data Server Driver Package -
WINDOWS
-
Installing IBM Data Server Driver Package - UNIX
Once the ODBC/CLI Driver is installed, you need to set up the CLI
configuration keywords by editing the
db2dsdriver.cfg or
db2cli.ini file. The CLI Driver reads these
configuration files before establishing the connection to the database.
Click the following links for information on how to edit the
db2dsdriver.cfg or
db2cli.ini files.
The db2dsdriver.cfg file is the recommended
configuration file, as most of the entries in that file apply to ODBC,
CLI, .NET, OLE DB, or open source (PHP or Ruby) applications.
Enabling the Informix DRDA port
The following error will be returned by IBM Data Server CLI Driver if the Informix DRDA port is not enabled correctly.
A communication error has been detected. Communication protocol being used: "TCP/IP". Communication API being used: "SOCKETS". Location where the error was detected: "Host Name". Communication function detecting the error "Connect". Protocol specific error code(s): "10061","*","*" SQLSTATE=08001.
For more information on configuring Informix to allow connections from IBM Data Server clients, see the Configuring Informix for connections to IBM Data Server Clients topic in the IBM Information Center.
Testing the IBM Data Server CLI Driver setup after installation
Listing 3 shows how you can check if the IBM Data Server CLI Driver is installed correctly. The connection section of the output shows whether the connection of successful or not.
Listing 3. Command to check if the IBM Data Server CLI Driver is installed correctly
db2cli validate
{ [-dsn <dsn_name>] |
[-database <dbname:server:port>] }
-connect [-user <user> [-passwd <password>]]
Connection Section:
----------------------------------------------------------------------------------------
Connecting to: <dsn_name>
Connect Status: success
End Connection Section
----------------------------------------------------------------------------------------
|
The command shown in Listing 3 validates the configuration files that the
CLI Driver uses.
For more information on how to validate the
configuration files, see the db2cli - DB2 interactive CLI command topic in the IBM
Information Center.
Current limitations of CLI with Informix server
The following are some limitations that you may find with using CLI with Informix server.
- User-defined data types and distinct data types are not currently
supported by CLI Driver against Informix server.
- Positioned updates using
SQLBulkOperations()orSQLSetPos()are not supported with Informix servers, because Informix server does not support updatable scrollable cursors. - CLI LOAD utility is not supported against Informix server.
- CLI LOB streaming and data interleaving are not supported with
Informix server because the server does not support Dynamic Data
Format.
- Binary Large Object blocking is not supported against Informix
server.
- CLI Driver against Informix server does not allow binary values to be
inserted into CHAR columns. Informix server will give the following
error:
-1260- It is not possible to convert between the specified types.
This article has given you the basic information that you need to start using the IBM Data Server CLI Driver for Informix. It includes an introductory-level description of the CLI Driver, highlights of the benefits of using it, and shows you how to install and configure the driver. You can now use this information as a foundation for using IBM Data Server CLI Driver for Informix for your own applications.
- Download the IBM Data Server Driver Package
- Download the IBM Informix Client Software Development Kit (SDK)
- Download the a
trial version of Informix
Discuss
- Participate in the discussion forum
- Check out developerWorks blogs and get involved in the developerWorks
community
Table of contents
Next steps from IBM
Tags
Use the slider bar to see more or fewer tags.
For articles in technology zones (such as Java technology, Linux, Open source, XML), Popular tags shows the top tags for all technology zones. For articles in product zones (such as Info Mgmt, Rational, WebSphere), Popular tags shows the top tags for just that product zone.
For articles in technology zones (such as Java technology, Linux, Open source, XML), My tags shows your tags for all technology zones. For articles in product zones (such as Info Mgmt, Rational, WebSphere), My tags shows your tags for just that product zone.
Popular article tags |
My article tagsSkip to tags list
Popular article tags |
My article tags
