Troubleshooting database access

Obtaining DB2® drivers

Make sure you use the most recent drivers for your DB2® version. As of sMash version 1.1.0.1, when using zero.data with DB2®, you must use the DB2 JCC driver version 3.53 or later.

If you do not already have a DB2 license, there is a "community" version, DB2® Express-C, that is available with a free license to develop, deploy, and distribute.

Configuration

The DB2SimpleDataSource requires several properties in order to successfully connect. The following example show basic usage required to connect to to a DB2® database:

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

For complete details about the valid properties for DB2® DataSource implementations, see the DB2® documentation. The default driverType value 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.

#!/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

Obtaining Derby drivers

Derby is a pure Java database and is contained in IBM WebSphere sMash. The embedded drivers are contained in the core Derby jar file. To use Derby in embedded mode, declare an application dependency on derby as follows:

<dependency name="derby" org="org.apache.derby" rev="[10.3.0.0, 10.4.0.0["/>

To use Derby in network mode, declare an application dependency on derbyclient as follows:

<dependency name="derbyclient" org="org.apache.derby" rev="[10.3.0.0, 10.4.0.0["/>

Configuration

The configuration for embedded and network Derby differs slightly. Each is detailed below.

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

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

/config/db/mydb = {
    "class" : "org.apache.derby.jdbc.ClientDataSource",
    "databaseName" : "db/MYDBNAME",
    "serverName" : "localhost",
    "portNumber" : 1527,
    "user" : "someuser",
    "password" : "secretpassword"
}

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

Obtaining MySQL drivers

MySQL JDBC drivers, Connector/J 5.0, can be downloaded from MySQL's website under its own terms and license. For complete information about configuring and tuning connection properties, consult the MySQL DataSource Documentation

Configuration

The MysqlDataSource requires several properties in order to successfully connect. The following example show basic usage required to connec to to a MySQL database:

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

Obtaining Oracle drivers

Oracle JDBC drivers can be downloaded from Oracle's website under its own terms and license. For complete information about configuring and tuning connection properties, consult the Oracle's documentation.

Configuration

The OracleDataSource requires several properties in order to successfully connect. The following example show basic usage required to connec to to an Oracle database:

/config/db/mydb = {
    "class" : "oracle.jdbc.pool.OracleDataSource",
    "serverName" : "db.example.org",
    "portNumber" : 1521,
    "driverType" : "thin",
    "databaseName" : "MYDBNAME",
    "user" : "someuser",
    "password" : "secretpassword"
}

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

Obtaining SQL Server® drivers

JDBC drivers can be obtained from the Microsoft® website under its own terms and license. For complete information about configuring and tuning connection properties, consult the SQL Server® documentation.

Configuration

The SQLServerDataSource requires several properties in order to successfully connect. The following example show basic usage required to connec to to an Oracle database:

/config/db/mydb = {
    "class" : "com.microsoft.sqlserver.jdbc.SQLServerDataSource",
    "serverName" : "db.example.org",
    "portNumber" : 1433,
    "databaseName" : "MYDBNAME",
    "user" : "someuser",
    "password" : "secretpassword"
}