Troubleshooting Apache Derby issues

Schema 'SYSIBM' does not exist error

Question: A customer is attempting to utilize Derby in networked mode and is encountering difficulties. They've successfully initilized it and can query it using sysinfo and testconnection. However, when running IBM® Security Verify Directory Integrator and attempting to open the system store, an error is reported.

[com.ibm.db2.jcc.a.SQLException: Schema 'SYSIBM' does not exist]

Explanation: The reason for the error is because the server is attempting 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 resolve, start the Derby networked server with the "-ld" flag, like:

./dbserver start -p 1527 -ld

Another Instance of Derby may already be booted

You may get the following error sometimes, especially when using Derby in embedded mode:

[ERROR XSDB6: Another instance of Derby may have already booted the database D:\svdi\Derby.]

Explanation: Derby (in embedded mode) will try to prevent two instances of Derby from booting the same database (in this case D:\svdi\Derby). This can happen if two AssemblyLines are running which attempt update the same Derby database running in embedded mode. This error might also appear if an unclosed connection to the database exist.

Corrective Action: If multiple AssemblyLines must update same Derby database, then the correct mode of Derby should be networked mode; this mode of operation does not have that limitation. The default mode for Derby in IBM® Security Verify Directory Integrator is networked mode.

A customer can work around this by closing the database using the Browse Server Stores option and then clicking on the Close button. Alternatively, if the database is not currently open, simply initiating the process of opening and closing it through the "Browse Server Stores" feature can effectively address the problem.

Future versions of IBM® Security Verify Directory Integrator attempt to handle this situation automatically, and stop and start Derby as required.

Delta Store name - Table/View 'IDI_DS_example' does not exist

Question: During assembly line initialization, a customer is trying to use Derby and encounters the following message.

java.sql.SQLSyntaxErrorException: Table/View 'IDI_DS_example' does not exist.
at org.apache.derby.client.am.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:94)
at org.apache.derby.client.am.SqlException.getSQLException(SqlException.java:325)
at org.apache.derby.client.am.ClientConnection.prepareStatement(ClientConnection.java:448)
at com.ibm.di.store.DeltaStore.<init>(DeltaStore.java:164)
at com.ibm.di.store.DeltaStore.<init>(DeltaStore.java:100)
at com.ibm.di.store.DeltaSysTable.getDeltaStore(DeltaSysTable.java:54)
at com.ibm.di.fc.DeltaFC.initDelta(DeltaFC.java:288)
at com.ibm.di.fc.DeltaFC.checkInitialized(DeltaFC.java:435)
at com.ibm.di.fc.DeltaFC.perform(DeltaFC.java:463)
at com.ibm.di.server.CSDeltaTaskComponent.getnext(CSDeltaTaskComponent.java:321)
at com.ibm.di.server.AssemblyLine.msGetNextIteratorEntry(AssemblyLine.java:3740)
at com.ibm.di.server.AssemblyLine.executeMainStep(AssemblyLine.java:3431)
at com.ibm.di.server.AssemblyLine.executeMainLoop(AssemblyLine.java:3032)
at com.ibm.di.server.AssemblyLine.executeMainLoop(AssemblyLine.java:3015)
at com.ibm.di.server.AssemblyLine.executeAL(AssemblyLine.java:2972)
at com.ibm.di.server.AssemblyLine.run(AssemblyLine.java:1339)
Caused by: ERROR 42X05: Table/View 'IDI_DS_example' does not exist.

Explanation: The value defined in the 'Delta Store' field of the Delta tab contains invalid characters. Refer to Invalid character usage for delta store name

Can I use DB2® as a system store?

In IBM® Security Verify Directory Integrator it is possible to use DB2 as a system store, instead of the bundled Derby database system. However, some modification of system properties files is required for this to function correctly. The customer must replace the section on Derby networked mode with a section similar to the following.

In the default global.properties file, there are some CREATE_TABLE statements for using and setting up the system store. If you use the right syntax, you 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 )

Note: Each com.ibm.di.store.create.* statement must be specified on one line.

For further details refer to Third-party RDBMS as System Store

Why can't remote connections be made to the Derby network server start by IBM® Security Verify Directory Integrator

This can be due to the Derby Server starting with "localhost" as the hostname. If the com.ibm.di.store.hostname is set to localhost then remote connections will not be allowed. If it is set to the IP address of the local machine - then remote clients can access this Derby instance by mentioning the IP address.

Corrective Action:

• Modify the com.ibm.di.store.hostname property found in global.properties or solution.properties.
• Use the Configuration Editor's Server System Store Server Settings window (available from the menu on a server in the Servers view).

For more details on Derby, refer to http://db.apache.org/derby/docs/10.5/adminguide/tadmincbdjhhfd.html