Troubleshooting database access

Obtaining DB2

If you do not already have a DB2 license, there is a "community" version, DB2 Express-C, that is available for with a free license to develop, deploy, and distribute. Make sure you use the most recent drivers for your DB2 version.

Configuration

/config/db/mydb = {
    "class" : "com.ibm.db2.jcc.DB2SimpleDataSource",
    "driverType" : 4,
    "serverName" : "localhost",
    "portNumber" : 50000,
    "databaseName" : "MYDBNAME",
    "user" : "someuser",
    "password" : "<xor>Lz4sLCgwLTs="
}

For complete details about the valid properties for DB2 DataSource implementations, see the DB2 Documentation. By default, driverType is 2, which is the native CLI wrapper. Unless you have your jars and dlls all set up correctly, it can be a pain to debug exactly why you aren't getting a Connection. By setting to 4, the pure Java driver, you'll have better success. For more information about selecting the appropriate DB2 DataSource implementation, see the DB2 Documentation. If you face any issues, look below for workarounds or post to the forum.

Configure tracing on DB2

DB2 DataSource implementations conveniently allow the setting of the trace file on the DataSource. As such, you can configure the driver to trace file by simply adding a property in zero.config:

/config/db/mydb = {
    "class" : "com.ibm.db2.jcc.DB2DataSource",
    ...
    "traceFile" : "${appname}/logs/db.txt"
}

In this example, ${appname} should be replaced by your application name from the application's ivy.xml. For instance, if your application is named myapp, you would use ${myapp}. Of course, the traceFile property can always be a hardcoded value. C:/db/logs/db-trace, for instance. But using the variable allows for better portability.

Throughput is slow on DB2

By default, DB2 activates and deactivates a database on demand when connections are made. Thus, with short-lived connections, as IBM® WebSphere® sMash, under some situations, you may see a significant decrease in performance and throughput because the database is being activated and deactivated upon each connection open and close.

In testing, performance increase was discovered by issuing a CONNECT TO. This caused a connection to persist for the lifetime of the Command Line Processor's session, for when the DB2 user logged off, decreased performance resumed.

Rather, database administrators can use ACTIVATE DATABASE to start up specific databases. This eliminates any application time spent on database initialization.

Consult DB2's documentation on ACTIVATE DATABASE for more usage information. Specifically, look at the "Usage notes" on the behavior of this command when the database server is stopped and restarted.

#!/bin/bash
###################################################
####Startup script for activating DB2 databases ###
###################################################

USER=db2inst1
case "$1" in
start)
/bin/su - $USER -c "db2 activate db emp_db"
;;
stop)
/bin/su - $USER -c "db2 deactivate db emp_db"
;;
restart|force-reload)
/bin/su - $USER -c "db2 deactivate db emp_db"
/bin/su - $USER -c "db2 activate db emp_db"
;;
*)
exit 1
;;
esac

exit 0

$ chmod +x /etc/init.d/dbactivate
$ update-rc.d dbactivate defaults

$ /etc/init.d/dbactivate start
$ /etc/init.d/dbactivate stop
$ /etc/init.d/dbactivate restart

Configuration

/config/db/mydb = {
    "class" : "org.apache.derby.jdbc.EmbeddedDataSource",
    "databaseName" : "db/customer",
    "createDatabase" : "create"
}

/config/db/mydb = {
    "class" : "org.apache.derby.jdbc.ClientDataSource",
    "databaseName" : "db/customer",
    "user" : "someuser",
    "password" : "secretpassword",
    "createDatabase" : "create"
}

/config/db/mydb = {
    "class" : "org.apache.derby.jdbc.ClientDataSource",
    ...
    "traceFile" : "${appname}/logs/db.txt"
}

Configuration

The following configuration is known to work with MySQL 5.0.45 on the Connector/J 5.0 drivers:

/config/db/mydb = {
    "class" : "com.mysql.jdbc.jdbc2.optional.MysqlDataSource",
    "serverName" : "db.example.org",
    "portNumber" : 3306,
    "databaseName" : "voting",
    "user" : "someuser",
    "password" : "secretpasssword"
}

For more information on available MysqlDataSource properties, see the complete MySQL DataSource Documentation. Downloads:

Performance issues making connections

Oracle's oracle.jdbc.pool.OracleDataSource implementation of the javax.sql.DataSource interface contains connection pooling built right in. Thus, they assume everybody will use connection pooling, which WebSphere sMash does not provide out of the box. Because of this assumption, OracleDataSource justifies expensive methods calls to Java's cryptography library. This can result in long connections to the database as compared to other vendors.

The behavior of zero.data.Manager's methods is to obtain a connection, use it, and call the close method (unless methods are executed under the inTransaction() method or a java.sql.Connection is passed into the zero.data.Manager constructor).