This section does not attempt to be a comprehensive Troubleshooting Guide for Derby, but there are a number of symptoms that are observed sometimes in the context of usage of Derby as the underlying database in TDI. These are:
Question
I'm trying to use Derby in networked mode and having issues. I've figured out how to start it up and I'm able to query it with sysinfo and testconnection, but when I run TDI and try to open the system store I get an error stating:
[com.ibm.db2.jcc.a.SQLException: Schema 'SYSIBM' does not exist]
How do I fix this?
Explanation
The reason you get this error is because you are trying to boot a database that was created in embedded mode into a networked mode server without starting the server using the -ld flag. Note that for a networked mode Derby server to open an embedded mode database, the SYSIBM schema MUST be loaded. The SYSIBM schema is a special schema loaded by the Derby server. The SYSIBM contains stored prepared statements that return result sets to determine metadata information.
Corrective action
To solve this problem start the Derby networked server with the "-ld" flag, like:
./dbserver start -p 1527 -ld
[ERROR XSDB6: Another instance of Derby may have already booted the database D:\tdi60\Derby.]
Explanation
Derby try to prevent two instances of Derby from booting the same database (in this case D:\tdi60\Derby). This can happen if you are running two AssemblyLines which are trying to update the same Derby database running in embedded mode. This error might also crop up if we have an unclosed connection to the database.
Corrective Action
If you look at the default global.properties file, there are some CREATE_TABLE statements for using and setting up the system store. If we use the right syntax, we can use non-Derby databases as system store. Here is the DB2 syntax:
## Location of the DB2 database (networked mode) com.ibm.di.store.database=jdbc:db2://168.199.48.4:3700/tdidb com.ibm.di.store.jdbc.driver=com.ibm.db2.jcc.DB2Driver com.ibm.di.store.jdbc.urlprefix=jdbc:db2: com.ibm.di.store.jdbc.user=db2inst1 com.ibm.di.store.jdbc.password=****** com.ibm.di.store.start.mode=automatic com.ibm.di.store.port=3700 com.ibm.di.store.sysibm=true # the varchar(length) for the ID columns used in system store and PES Connector tables com.ibm.di.store.varchar.length=512 # create statements for DB2 system store tables com.ibm.di.store.create.delta.systable=CREATE TABLE {0} (ID VARCHAR(VARCHAR_LENGTH) NOT NULL, SEQUENCEID int, VERSION int) com.ibm.di.store.create.delta.store=CREATE TABLE {0} (ID VARCHAR(VARCHAR_LENGTH) NOT NULL, SEQUENCEID int, ENTRY BLOB ) com.ibm.di.store.create.property.store=CREATE TABLE {0} (ID VARCHAR(VARCHAR_LENGTH) NOT NULL, ENTRY BLOB ) com.ibm.di.store.create.sandbox.store=CREATE TABLE {0} (ID VARCHAR(VARCHAR_LENGTH) NOT NULL, ENTRY BLOB )
Each com.ibm.di.store.create.xxx statement must be specified on one line, even though they are broken up in this example for illustration purposes.
For more details, check http://publib.boulder.ibm.com/infocenter/cldscp10/topic/com.ibm.cloudscape.doc/hubprnt22.htm