How to install and configure DB2 ODBC driver. On this page you can find and download the ODBC-drivers that. In Visual Studio working with Informix or DB2 is. DataDirect for IBM Informix improves performance and scalability. DataDirect Connectors Powerful IBM Informix ODBC driver. Download ODBC connectors.
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
Advanced CLI features
Asynchronous execution
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.
Lob locators
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.
Retry on error
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.
Advanced tracing options
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
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.
Listing 2. Enabling CLI trace
For more information on CLI trace, see the Trace CLI/ODBC configuration keyword topic in the IBM Information Center.
DB2 trace
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.
Advanced CLI APIs
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.
CLI array input chaining
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.
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 ServerCLI Driver is installed correctly
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.
Microsoft Odbc Driver Download
Conclusion
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.
Resources
- Download the IBM Data Server Driver Package
- Download the IBM Informix Client Software Development Kit (SDK)
Discuss
Informix Odbc Connection
- Check out developerWorks blogs and get involved in the developerWorks community
Downloadable resources
Microsoft Sql Server Odbc Driver Download
Comments
Sign in or register to add and subscribe to comments.