Building a C/C++ application using CLI - IBM DB2

In this section, we show you the fundamentals of building a C/C++ application using CLI. We discuss first some basic concepts and the environment setup. We then guide you step-by-step to build an application starting from a simple code template.

CLI handles
A Call Level Interface (CLI) application needs to initially set up data structures and variables to manage work such as connecting to the database, running SQL statements, and disconnecting from the database. Luckily, data objects called handles exist to make things easier. Handles can be allocated, used for processing, and then freed. The possible CLI handles are:

  • Environment (SQL_HANDLE_ENV)
  • This object is the base handle, which holds iholds information and provides a context for all connections. Typically, only a single environment handle exists for an application.
  • Connection (SQL_HANDLE_DBC)
  • This object holds information about a connection to a single database and provides a context for all statements executed against that database. A CLI application can connect to multiple databases. This means that there can be multiple connection handles associated with a single environment handle.
  • Statement (SQL_HANDLE_STMT)
  • This object contains information about the processing of an SQL statement. This must be associated with a connection handle.
  • Descriptor (SQL_HANDLE_DESC)
  • This handle holds information about either parameter markers or columns in a resultset. It can be associated with either a statement or connection handle.

relationship of the handles shows below. Connection handles can only be defined under the context of an environment handle, and statement handles can only be defined under the context of a connection handle. Descriptor handles can be optionally allocated to associate with a connection or statement handle. Handle allocation is done in sequence. The environment handle must be declared first, then the connection handle is declared under the environment handle, and then the statement handle is declared under the connection handle. When freeing the handles, all child handles should be freed explicitly before freeing the parent handle.

CLI handles

CLI handles

The CLI driver
In order for us to use Call Level Interface (CLI) APIs, we need to make use of the CLI driver available in DB2. The name of the library specific to each platform is:

  • On Windows: db2cli.dll (for dynamic loading) or db2cli.lib (for static linking)
  • Linux: libdb2.a (for either dynamic loading at runtime or static linking at compile time)

The CLI configuration file (db2cli.ini)
If we ever need to change the default behavior of CLI, we can change the db2cli.ini configuration file by adding or modifying CLI keywords. This file is read by the CLI driver during runtime, and is either located in the directory specified by the DB2 environment registry setting DB2CLIINIPATH (if set by the user) or in the following directories.

Locations of the db2cli.ini file

Locations of the db2cli.ini file

An example of the contents of a db2cli.ini file is found below.

Sample db2cli.ini file contents

In a db2cli.ini file, the name of a database is surrounded by square brackets. Each database in the file will have its own section of CLI keywords, which are listed below the database name. If a database is not listed in the db2cli.ini file, this means that there are no CLI keywords associated with it. Comments are denoted by semicolons (;). In Example 4-28, we see that there is a database named testDB with one CLI keyword named autocommit.
If we want a CLI keyword to apply to all databases, instead of defining the keyword in each database section, we can use a common section. A common section (denoted by “[common]”) in the configuration file means that the CLI driver will read the CLI keyword during runtime for all databases it accesses. We can choose to modify the db2cli.ini manually or on the DB2 Command Line Processor (CLP). To list the entire contents of the db2cli.ini file on the CLP, we can run the command:

db2 get cli cfg

To only get CLI keywords applicable for a particular database, we can run the following command (where <database name> is the name of the database):

To add or update a CLI keyword for a particular database, we can run the following command (substituting for the actual database name, CLI keyword, and value):

If a section for a database does not already exist and we use the UPDATE CLI CFG command, a section will be automatically added to the configuration file. An example of adding a CLI keyword to the db2cli.ini file on the CLP for the SAMPLE database is:

db2 update cli cfg for section sample using autocommit 0

Setting up the CLI Environment
In addition to setting up the C/C++ development environment as outlined in 4.1.1, “C/C++ development environment setup” on page 148, programs written using CLI need to ensure that the necessary CLI packages exist on the database server.

The CLI driver will communicate with the application and the database to process SQL statements. Precompile and bind steps are not required for CLI applications. This means that no application packages are created in the database. However, for an SQL statement to be executed, there still needs to be a package with available sections in the database for access plans. This is where CLI bind files come in. CLI bind files are supplied by DB2 and only have to be bound against the database once. Once bound, CLI dynamic placeholder packages will exist in the database. These packages are ready to handle the dynamic SQL passed by the driver.

DB2 supplies text files listing the required bind files to be bound against a particular database server platform. outlines the list files specific to each database server platform. They are available in the BND directory of the DB2 installation path.

DB2 list files for CLI

DB2 list files for CLI

To bind the packages, you need to connect to the database server and run the BIND command to process the appropriate .lst file. For example, within a DB2 UDB CLP window on a Windows machine, suppose we want to bind the CLI packages against a DB2 database on a Linux machine.The commands we execute to create the CLI and DB2 utility packages in the SAMPLE database shows below.

Binding CLI bind files

db2 connect to sample db2 bind C:Program FilesIBMSQLLIBnd@db2ubind.lst sqlerror continue grant public db2 bind C:Program FilesIBMSQLLIBnd @db2cli.lst sqlerror continue grant public db2 terminate

In the example above, you might notice that an at character, (@), is added in front of the list filename. This is needed for the DB2 binder to properly process each bind file listed in the list file.

Overview of steps
The steps to build a C/C++ application using CLI are as follows:

  1. Allocate an environment handle.
  2. Allocate one or more connection handles to be associated with the single environment handle allocated in step 1.
  3. With a connection handle allocated in Step 2, perform a database connection.
  4. Allocate one or more statement handles under the connection handle to do SQL processing against the database.
  5. Repeat step 3 if there are multiple connection handles.
  6. Clean up resources by ensuring all database connections are disconnected and all handles are freed before the application terminates.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

IBM DB2 Topics