Knowledgebase:

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 path
Connectivity 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

  1. 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.

  2. Create a new driver in Ignition
    Follow 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
  3. Create a new database connection using your new driver
    Follow the steps described in the user manual to create a new database connection. Make sure to select your new driver when prompted.

Your new IBM AS400 database connection is now ready to use.

(7 vote(s))
Helpful
Not helpful