DB2
Creating database
Create a dedicated user for the Clover database and set a password (UNIX/Linux).
useradd clover
passwd clover
Create a new database.
db2 "CREATE DATABASE cloverdb PAGESIZE 32768 RESTRICTIVE"
Activate the database.
db2 activate db cloverdb
Connect to the database.
db2 connect to cloverdb
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"
Disconnect from database
db2 connect reset
DB2 on Linux/Windows
If you use a properties file for configuration, specify these parameters:
jdbc.driverClassName, jdbc.url, jdbc.username,
jdbc.password, jdbc.dialect.
For example:
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
Please don't forget to add a JDBC 4 compliant driver on the classpath. A JDBC driver which doesn't meet JDBC 4 specifications won't work properly.
Possible problems
Wrong pagesize
The clover 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:
ERROR: DB2 SQL Error: SQLCODE=-286, SQLSTATE=42727, SQLERRMC=16384; ROOT, DRIVER=3.50.152
SQLERRMC contains suitable value for
PAGESIZE.
You can create a database with 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, 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 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.
To solve this issue, 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 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 the 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.
If you use a properties file for configuration,
specify these parameters:
jdbc.driverClassName, jdbc.url, jdbc.username,
jdbc.password, jdbc.dialect.
For example:
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 the schema in AS/400 console:
execute command
STRSQL(SQL console)execute
CREATE COLLECTION cloversrv IN ASP 1cloversrvis 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.
Please don't forget to add a JDBC 4 compliant driver on the classpath. A JDBC driver which doesn't meet JDBC 4 specifications won't work properly.
![[Note]](figures/note.png) | Note |
|---|---|
|
