Connecting to IBM AS400 (iSeries) database

Posted by Anna Christian on 10/20/2017 04:06 PM

Ignition includes an IBM DB2 driver, however, it will not work for all IBM databases. This article explains how to create a custom driver in order to successfully connect to IBM AS400 iSeries database (QAS).

Background

If you try to connect to an IBM AS400 iSeries database using the IBM DB2 driver included with Ignition, the connection will either report as "Faulted", or be stuck in the "Connecting" status. You may also see error like the following posted in the log:

org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory ([jcc][t4][10109][10354][4.7.85] The version of the IBM Universal JDBC driver in use is not licensed for connectivity to QAS databases.To connect to this server, please obtain a licensed copy of the IBM DB2 Universal Driver for JDBC and SQLJ.An appropriate license file db2jcc_license_*.jar for this target platform must be installed to the application class pathConnectivity to QAS databases is enabled by any of the following license files: [ db2jcc_license_cisuz.jar ]. ERRORCODE=-4472, SQLSTATE=42968)

To connect to a database, Ignition uses a JDBC driver. JDBC drivers are specific to the particular database(s), so it is important to use the driver that works with the particular database you are trying to connect to. To connect to a QAS database, Ignition needs the JTOpen driver (jt400.jar). The steps below will walk you through downloading, installing, and configuring this driver to work with Ignition.

Steps

Download the driver file

Download the latest driver from the JTIOpen project page. Extract the zip archive and locate the jt400.jar file. You will need the path to this file in the next step.

Create a new driver in IgnitionFollow the steps for adding a JDBC driver outlined in the Ignition user manual. Use the following settings:

Name

Use a descriptive name for your new driver, for example AS400

Description

This is optional; you can add a description of your choice

Class name

com.ibm.as400.access.AS400JDBCDriver

Jar File(s)

Navigate to the jt400.jar file you obtained in step 1.

Driver Type

Generic

URL Format

jdbc:as400://IPorHOSTNAME/Schema

URL Instructions

This is optional; you can add instructions of your choice

Default Connection Properties

This is optional, but the following are helpful:naming=sql;errors=full

Connection Properties Instructions

This is optional; you can add instructions of your choice

Default Validation Query

select 1 from sysibm.sysdummy1

Default Translator

DB2

URL Instructions

This is optional; you can add instructions of your choice

Create a new database connection using your new driverFollow the steps described in the user manual to create a new database connection. Make sure to select your new driver when prompted.