Skip to main content

IBM i DB2 drivers

Drivers for IBM i DB2​

Applications using an IBM i DB2 database require drivers. Depending on your requirements, you can either use the DB2 .NET Core drivers or the IBM .NET Data Provider drivers.

We recommend using the newer .NET Core drivers due to vast performance improvements.

Use DB2 .NET Core drivers​

Indicium can use the DB2 .NET Core drivers, which do not require installation. This 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.

note

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.

See also Troubleshooting DB2 .NET Core drivers.

Use IBM .NET Data Provider drivers​

The IBM .NET Data Provider drivers are part of the IBM i Access Client Solutions - Windows Application Package. If you choose to use the IBM .NET Data Provider drivers, they must be installed separately on the client or server running the application. Make sure you install the latest version.

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

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 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 IBM .NET Data Provider driver due to vast performance improvements. However, if you encounter the issue mentioned above, you can run the old IBM .NET Data Provider 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 IBM .NET Data Provider 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
    }
  }
}
Was this article helpful?