DB2 on Linux/Windows

For modification Tomcat context params, add to context config file (and modify according to your credentials):

<Parameter name="jdbc.driverClassName" value="com.ibm.db2.jcc.DB2Driver" override="false" />
<Parameter name="jdbc.url" value="jdbc:db2://localhost:50000/clover" override="false" />
<Parameter name="jdbc.username" value="usr" override="false" />
<Parameter name="jdbc.password" value="pwd" override="false" />
<Parameter name="jdbc.dialect" value="org.hibernate.dialect.DB2Dialect" override="false" />

Or If you use properties file for configuration:

jdbc.driverClassName=com.ibm.db2.jcc.DB2Driver
jdbc.url= jdbc:db2://localhost:50000/clover
jdbc.username=usr
jdbc.password=pwd
jdbc.dialect=org.hibernate.dialect.DB2Dialect

Possible problems

Wrong pagesize

Database clover has to be created with suitable PAGESIZE. DB2 has several possible values for this property: 4096, 8192, 16384 or 32768.

CloverETL Server should work on DB with PAGESIZE set to 16384 or 32768. If PAGESIZE value is not set properly, there should be error message in the log file after failed CloverETL Server startup:

ERROR:
DB2 SQL Error: SQLCODE=-286, SQLSTATE=42727, SQLERRMC=16384; 
ROOT, DRIVER=3.50.152

SQLERRMC contains suitable value for PAGESIZE.

You can create database with proper PAGESIZE like this:

CREATE DB clover PAGESIZE 32768;

The table is in the reorg pending state

After some ALTER TABLE commands, some tables may be in "reorg pending state". This behaviour is specific for DB2. ALTER TABLE DDL commands are executed only during the first start of new CloverETL Server version.

Error message for this issue may look like this:

Operation not allowed for reason code "7" on table "DB2INST2.RUN_RECORD".. SQLCODE=-668, SQLSTATE=57016

or like this

DB2 SQL Error: SQLCODE=-668, SQLSTATE=57016, SQLERRMC=7;DB2INST2.RUN_RECORD, DRIVER=3.50.152

In this case "RUN_RECORD" is table name which is in "reorg pending state" and "DB2INST2" is DB instance name.

To solve this, go to DB2 console and execute command (for table run_record):

reorg table run_record

DB2 console output should look like this:

db2 => connect to clover1
Database Connection Information
						
Database server        = DB2/LINUX 9.7.0
SQL authorization ID   = DB2INST2
Local database alias   = CLOVER1
						
db2 => reorg table run_record
DB20000I  The REORG command completed successfully.
db2 => disconnect clover1
DB20000I  The SQL DISCONNECT command completed successfully.

"clover1" is DB name

DB2 does not allow ALTER TABLE which trims DB column length.

This problem depends on DB2 configuration and we've experienced this only on some AS400s so far. CloverETL Server applies set of DP patches during the first installation after application upgrade. Some of these patches may apply column modifications which trims length of the text columns. These changes never truncate any data, however DB2 does not allow this since it "may" truncate some data. DB2 refuses these changes even in DB table which is empty. Solution is, to disable the DB2 warning for data truncation, restart CloverETL Server which applies patches, then enable DB2 warning again.

DB2 on AS/400

The connection on AS/400 might be slightly different.

For modification Tomcat context params, add to context config file (and modify according to your credentials):

<Parameter name="jdbc.driverClassName" value="com.ibm.as400.access.AS400JDBCDriver" override="false" />
<Parameter name="jdbc.url" value="jdbc:as400://localhost/cloversrv;date format=iso" override="false" />
<Parameter name="jdbc.username" value="javlin" override="false" />
<Parameter name="jdbc.password" value="clover" override="false" />
<Parameter name="jdbc.dialect" value="org.hibernate.dialect.DB2400Dialect" override="false" />

Or If you use properties file for configuration:

jdbc.driverClassName=com.ibm.as400.access.AS400JDBCDriver
jdbc.username=javlin
jdbc.password=clover
jdbc.url=jdbc:as400://host/cloversrv;libraries=cloversrv;date format=iso
jdbc.dialect=org.hibernate.dialect.DB2400Dialect

Use credentials of your OS user for jdbc.username and jdbc.password.

cloversrv in jdbc.url above is the name of the DB schema.

You can create schema in AS/400 console:

Proper JDBC driver must be in the application server classpath.

I use JDBC driver jt400ntv.jar, which I've found in /QIBM/ProdData/Java400 on the server.

Use jt400ntv.jar JDBC driver.

Do not forget to add jar with JDBC driver to the Tomcat classpath.