IBM i DB2 drivers

Drivers for IBM i DB2

Applications using an IBM i DB2 database require drivers.

Drivers for the Windows GUI, Web GUI and Indicium Basic

The Thinkwise Windows GUI, Web GUI and Indicium Basic service tier require the DB2 drivers to be installed separately on the client or server running the application. This concerns the IBM.Data.DB2.iSeries .NET Data Provider, which is part of the IBM i Access Client Solutions - Windows Application Package. Please make sure you have the latest version installed.

For developers, to be able to deploy code from the Windows GUI development environment, you must also install the ODBC driver.

To install the IBM .NET Data Provider, and optionally the ODBC driver:

1. Download the IBM i Access Client Solutions - Windows Application Package here: ACS Windows App Pkg English (64bit) (login required).
2. Install the .NET Data Provider driver.
3. Optionally, install the ODBC driver.

Installing the IBM i Access .NET Data Provider

Drivers for Indicium

For Indicium, it is not necessary to separately install any drivers because Indicium can use the newer DB2 .NET Core drivers, which do not require installation.

This also makes it possible to run Indicium on platforms other than Windows, and to deploy Indicium in managed cloud services, such as Azure Web Apps and AWS Elastic Beanstalk. This is not possible using the old drivers.

To deploy Indicium with the DB2 .NET Core drivers:

1. Download the IBM.Data.DB2.Core driver from TCP - Thinkwise products.
2. Extract the clidriver folder to the root the Indicium installation directory.
3. Place a valid DB2 Connect license file (db2consv_*.lic) in the <Indicium installation path>\clidriver\license folder.

Points of attention:

• Unlike the old iSeries drivers, IBM requires IBM i and z/OS customers to have a valid DB2 Connect license to use the new driver. For more information, please contact IBM Customer Support.
• Using DB2 with any of the reporting types mentioned in the .NET Framework section here currently requires the old iSeries DB2 driver to be installed.

We recommend using the new .NET Core driver over the old iSeries driver due to vast performance improvements.

However, it is still possible to use Indicium with the old iSeries driver, with some limitations. To install the latest version of the IBM .NET Data Provider, see here.

See also Troubleshooting DB2 .NET Core drivers.

Ports

The database provider uses various network ports to connect with the database server. Ports 8470, 8471, 8475, and 8476 have to be available from the client. If not, setting up a connection can take a very long time.

• You can find an overview of all port numbers and their functions here.
• Troubleshooting instructions are available here.

Logging and tracing

Logging and tracing DB2 connections can be enabled in the registry using the following keys (32/64 bit):

• HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\IBM\Client Access\CurrentVersion\Common\ManagedProvider\Service
• HKEY_LOCAL_MACHINE\SOFTWARE \IBM\Client Access\CurrentVersion\Common\ManagedProvider\Service

Logging and tracing DB2 connections

Performance optimizations

You can improve the performance of database connections and SQL for IBM i DB2 with the following settings:

• Ports - Make sure the required ports are open.
• Prestarted Jobs - Creating connections (jobs) takes a relatively long time for IBM i. Using Prestarted jobs prepares these connections in advance. Make sure that enough prestarted jobs are always available. See Prestart jobs.
• Separate subsystem - Query plans remain valid as long as the environment variables (load, disk space, free memory) remain stable. Therefore, we advise placing the QZDASOINIT tasks into a separate subsystem based on, for example, an IP address. See IBM i Subsystems.
• Plan cache - Increase the plan cache with the QQQOOOCACH command, so query plans remain available long enough. See Plan cache.

Troubleshooting DB2 .NET Core drivers

Problems with activating the .NET Core drivers

To solve problems with activating the .NET Core drivers:

1. Adjust Indicium's web.config:

   <aspNetCore ... >
      <environmentVariables>
      <environmentVariable name="Path" value="[Drive:\IndiciumPath]\clidriver\bin" />
      </environmentVariables>
   </aspNetCore>

Compatibility issues .NET Core with column types

In some cases, the DB2 .NET Core driver has compatibility issues with column types:

• XML
• DBCLOB.

Both column types can throw errors about not being able to perform the required CCSID conversion.

Running Indicium with the iSeries driver

We recommend using the newer .NET Core driver over the old iSeries driver due to vast performance improvements. But, when you encounter the issue mentioned above, you can run the old iSeries driver by using a plugin system.

Please note that since this means data now needs to travel between process boundaries from the plugin to Indicium, this method might degrade performance in some situations, compared to Indicium versions that previously used this driver directly. The most noticeable cases are:

• Selecting large data sets.
• Downloading file data stored in a database storage column.

To switch to the old iSeries driver:

1. Set the DataProviders:DB2:Mode configuration setting to iSeries.
2. The number of plugin workers spawned can be modified using the DataProviders:DB2:WorkerCount setting. The default number is 3.

For example, using appsettings.json:

{
  "DataProviders": {
    "DB2": {
      // Switch to using the old iSeries driver.
      "Mode": "iSeries",
      // Only spawn one plugin worker process instead of 3.
      "WorkerCount": 1
    }
  }
}