TDI 6.1(.1) System Store: Derby Cookbook

Follow this link to information tailored for TDI 6.0 (including some common errors).

Cloudscape 10 (aka "Derby") is a Java-based RDBMS included with TDI 6.1. In addition to serving as the default TDI System Store, it also provides a lightweight, portable JDBC data source.

A Cloudscape database is accessed via the name of the directory where data is stored. This can either be a fully qualified pathname, or one that is relative to the TDI solutions directory for embedded mode, or your TDI installation directory if you are using networked mode (see below).

Embedded vs. Networked Mode

Cloudscape offers two modes of operation:

Embedded Mode Default mode after TDI installation.
Runs in same JVM as TDI.
Supports access by a single instance of the TDI CE or Run-time Server at a time.
Cloudscape is automatically started by TDI whenever a database table is accessed.
Database names are relative to your solutions directory.
The derby.log file is created in your solutions directory.

Networked Mode Causes Cloudscape to run in its own JVM.
Allows simultaneous access from multiple clients over IP.
Automatically started by the TDI Server \x96 but not by the CE \x96 if the com.ibm.di.store.start.mode property is set to automatic (see description below).
Database names are relative to your installation directory.
The derby.log file is created in your solutions directory.

To avoid exclusive database locking and to allow data sharing between TDI Servers, you must configure Cloudscape to run in Networked mode.

Configuration of Cloudscape is done by editing parameters in the TDI properties file (solution in your TDI solutions directory, or if you do not have one then global.properties in the TDI install-dir/etc folder). Note that if you access either of these via the Properties window in the Config Editor then those lines that are commented out (#) will not appear. As a result, you will need to make the changes suggested here using a text editor.

In both properties files is a section dedicated to System Store settings:

##
## SYSTEM STORE
##

## Location of the database (embedded mode) - Cloudscape 10
#com.ibm.di.store.database=TDISysStore
#com.ibm.di.store.jdbc.driver=org.apache.derby.jdbc.EmbeddedDriver
#com.ibm.di.store.jdbc.urlprefix=jdbc:derby:
#com.ibm.di.store.jdbc.user=APP
#{protect}-com.ibm.di.store.jdbc.password=APP


## Location of the database to connect (networked mode) - Cloudscape 10 - DerbyClient driver
com.ibm.di.store.database=jdbc:derby://localhost:1527/C:\Program Files\IBM\TDI\V6.1\TDISysStore;create=true
com.ibm.di.store.jdbc.driver=org.apache.derby.jdbc.ClientDriver
com.ibm.di.store.jdbc.urlprefix=jdbc:derby:
com.ibm.di.store.jdbc.user=APP
{protect}-com.ibm.di.store.jdbc.password=APP


## Details for starting Cloudscape in network mode.
## Note: 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 Cloudscape
## instance by mentioning the IP address. The network server can only be started for the local machine.
#
com.ibm.di.store.start.mode=automatic
com.ibm.di.store.hostname=localhost
com.ibm.di.store.port=1527
com.ibm.di.store.sysibm=true

The above properties have been modified for networked mode by prepending each line related to embedded mode with a hash character (#) and then removing the hash in front of all networked mode parameters. In addition to those shown on this page, there are additional properties to control the length of VARCHAR columns, as well as the SQL/DDL statements required to create the standard System Store tables. These can be tailored to support the use of another RDBMS as the System Store.

Once the necessary configuration is complete, you can choose to allow TDI to automatically start up the server process when required by setting the com.ibm.di.store.start.mode property to "automatic", or start Cloudscape manually. In either case you must stop the database manually.

The CE provides the Store -> Manage System Store menu selection for starting and stopping the database server.

This screen offers a set of buttons for starting, stopping and restarting the currently configured database server.

Alternatively, you can start and stop the Cloudscape server using batch-files/scripts like these that you can set up in your TDI installation directory:

For Windows (dbserver.bat)
set DB_JAR_DIR=jars/3rdparty/IBM
set DB_CLASSPATH=%DB_JAR_DIR%\derby.jar;%DB_JAR_DIR%\derbyclient.jar;%DB_JAR_DIR%\derbynet.jar;%DB_JAR_DIR%\derbytools.jar;
java -classpath %DB_CLASSPATH% org.apache.derby.drda.NetworkServerControl %*

For Unix (dbserver.sh)
export DB_JAR_DIR=jars/3rdparty/IBM
export DB_CLASSPATH=$DB_JAR_DIR/derby.jar:$DB_JAR_DIR/derbyclient.jar:$DB_JAR_DIR/derbynet.jar:$DB_JAR_DIR/derbytools.jar
java -classpath $DB_CLASSPATH org.apache.derby.drda.NetworkServerControl "$@"

Try running your batch/script file to see the various commands available, like start, shutdown and ping.

Once Cloudscape is started on your machine, it is ready to accept requests over port 1527 (the default).

Note that there is also a derby.properties file in the TDI installation directory that controls system-level parameters, including security settings.

Gotchas

One common mistake is changing solution.properties (or global.properties) and forgetting to restart TDI. These configuration parameters are read in when TDI loads, and are not read again until the system is restarted.

-- EddieHartman - 17 Aug 2006

  • cs.bat: So you can do cs start -h localhost -p 1527 _and _ cs shutdown.
Topic revision: r8 - 05 Dec 2007, EddieHartman - This page was cached on 05 Aug 2023 - 18:57.

This site is powered by FoswikiCopyright © by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TDI Users? Send feedback