Version

    DB2

    Creating Database
    CloverDX Server Setup
    Troubleshooting
    DB2 on AS/400

    Creating Database

    1. Create a dedicated user for the CloverDX 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

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

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