DB2

Creating Database
CloverETL Server Setup
Troubleshooting
DB2 on AS/400

Creating Database

  1. Create a dedicated user for the Clover database and set a password (UNIX/Linux).

    useradd clover
    passwd clover
  2. Create a new database.

    db2 "CREATE DATABASE cloverdb PAGESIZE 32768 RESTRICTIVE"
  3. Activate the database.

    db2 activate db cloverdb
  4. Connect to the database.

    db2 connect to cloverdb
  5. Grant the user DBADM authority (DBADM authority is an administrative authority for a specific database. The database administrator possesses the privileges that are required to create objects and issue database commands. By default, DATAACCESS and ACCESSCTRL authority are also granted).

    db2 "GRANT DBADM ON DATABASE TO USER clover"
  6. Disconnect from database

    db2 connect reset

CloverETL Server Setup

Example of a properties file configuration:

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

Add a JDBC 4 compliant driver on the classpath. A JDBC driver which doesn't meet JDBC 4 specifications won't work properly.

Troubleshooting

Wrong pagesize

The cloverdb database 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 the PAGESIZE value is not set properly, there should be an error message in the log file after failed CloverETL Server startup.

The error indicating wrong pagesize:

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

SQLERRMC contains suitable value for PAGESIZE.

Solution:

You can create a database with a proper page size using the PAGESIZE command, e.g.:

CREATE DB clover PAGESIZE 32768;

The table is in the reorg pending state

On rare occasions, the ALTER TABLE commands may cause tables to remain in "reorg pending state". This behavior is specific for DB2. The ALTER TABLE DDL commands are executed only during the first start of a new CloverETL Server version.

The issue may return the following error messages:

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

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

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

Solution:

Go to DB2 console and execute the following 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 the DB2 configuration and has been experienced only on some AS400s, so far. CloverETL Server applies a set of DP patches during the first installation after the application upgrade. Some of these patches may apply column modifications which trim the 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 empty DB table.

Solution:

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.

Example of a properties file configuration:

jdbc.driverClassName=com.ibm.as400.access.AS400JDBCDriver
jdbc.username=user
jdbc.password=password
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 the schema in AS/400 console:

  1. execute command STRSQL (SQL console)

  2. execute command

    CREATE COLLECTION cloversrv IN ASP 1

    cloversrv is the name of the DB schema and it may be at most 10 characters long

Proper JDBC driver must be in the application server classpath.

Use jt400ntv.jar JDBC driver found in /QIBM/ProdData/Java400 on the server.

Add a JDBC 4 compliant driver on the classpath. A JDBC driver which doesn't meet JDBC 4 specifications won't work properly.

[Note]Note

Continue with:  Encrypted JNDI or Activation