Access to add and change pages is restricted. See: https://cwiki.apache.org/confluence/display/OFBIZ/Wiki+access

Since OFBiz does not support DB2 out of the box, It might be worthwhile to work towards setting up an installation using DB2 for a number of reasons. Outside of the fact that Derby is syntactically similar to DB2, which should make the setup fairly easy, by setting up the free version of DB2 within OFBiz it gives users another option for back-end persistence as well as allowing for the possibility of running OFBiz on such platforms as the i5 (IBM mid-range) or even the zSeries (IBM mainframe).

To reach that goal we will perform a sample install using OFBiz and DB2 Express-C version 9 from IBM (available at http://www-306.ibm.com/software/data/db2/express/download.html). Additionally it might be helpful to download the DB2 Express-C developers handbook (http://www.redbooks.ibm.com/abstracts/SG247301.html?Open). It is also extremely helpful to remember that DB2 uses something called ordinary identifiers which are case sensitive (after a fashion) identifiers that must be all uppercase. So for example, the schema name for DB2 should be all uppercase (ex. SCHEMANAME). If you do not make the schema name uppercase in the entityengine.xml file then a number of lookup functions can fail (retrieving table names, etc).

Included in the installation of DB2 is all of the SQL drivers that are required to connect to the database. The DB2 license JAR (db2jcc_license_cu.jar) and the DB2 driver JAR (db2jcc.jar) need to be copied from the instance java directory (<INSTANCE_NAME>/sqllib/java) to the OFBiz framework JDBC lib directory (ofbiz/framework/entity/lib/jdbc) and added to the classpath by whatever tool you will use to perform the ANT build or deployments.

Below is an example of the necessary data source blocks to include in the entityengine.xml:

<!--  Setup for local DB2 Express-C  -->
    <!--  Schema needs to be uppercase since it is used as an ordinary identifier -->
    <datasource name="localdb2"
            use-pk-constraint-names="false"
            schema-name="DB2INST1"
            constraint-name-clip-length="18"
            helper-class="org.ofbiz.entity.datasource.GenericHelperDAO"
            field-type-name="db2"
            check-on-start="true"
            add-missing-on-start="true"
            join-style="ansi">
        <read-data reader-name="seed"/>
        <read-data reader-name="demo"/>
        <read-data reader-name="ext"/>
        <!--  older driver: COM.ibm.db2.jdbc.app.DB2Driver -->
        <!--  Type 2/4 Universal driver: com.ibm.db2.jcc.DB2Driver -->
        <!--  XADataDriver: COM.ibm.db2.jdbc.DB2XADataSource # does not seem to work -->

        <!--  Tablespace must be at least 8192 for the products table, will create just one tablespace for all that is 8192 but might be better to
                split tablespaces, but then the SQL creates would need to be modified
        -->
        <!--  trying to set the cursor sensitivity to see if we can get around the issue with the survey, it seems that URI
                properties are not supported -->
        <!--  user=db2inst1;password=password;cursorSensitivity=2;driverType=4 -->
        <inline-jdbc
                jdbc-driver="com.ibm.db2.jcc.DB2Driver"
                jdbc-uri="jdbc:db2://localhost:50000/OFBIZ"
                jdbc-username="db2inst1"
                jdbc-password="password"
                isolation-level="ReadCommitted"
                pool-minsize="2"
                pool-maxsize="20"/>

        <!-- <jndi-jdbc jndi-server-name="localjndi" jndi-name="java:/DB2DataSource" isolation-level="Serializable"/> -->
    </datasource>
    <datasource name="localdb2byodbc"
            use-pk-constraint-names="false"
            schema-name="DB2INST1"
            constraint-name-clip-length="18"
            helper-class="org.ofbiz.entity.datasource.GenericHelperDAO"
            field-type-name="db2"
            check-on-start="true"
            add-missing-on-start="true"
            join-style="ansi">
        <inline-jdbc
                jdbc-driver="com.ibm.db2.jcc.DB2Driver"
                jdbc-uri="jdbc:db2://localhost:50000/OFBIZ"
                jdbc-username="db2inst1"
                jdbc-password="password"
                isolation-level="ReadCommitted"
                pool-minsize="2"
                pool-maxsize="20"/>

         <!-- <jndi-jdbc jndi-server-name="localjndi" jndi-name="java:/DB2DataSource" isolation-level="Serializable"/>-->
    </datasource>


Current problems with the DB2 setup:

  • Primary Key lookup does not work with the current DatabaseUtil class (DB2 does not support lookup of primary keys without a table name).
  • Error with the Survey portion of the sample site homepage (still investigating but seems to be due to the CLOB objects in the survey tables. The SQL used to pull the data fails when run against DB2 with an error related to pulling CLOBs)
  • No labels