Cloudscape Cookbook

Overview

Cloudscape is a Java-based RDBMS included with TDI. 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 or 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 db2j.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 db2j.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/global.properties ). If you open this file in a text editor, you will see a section dedicated to System Store settings:

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 character 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 control Cloudscape from the command line using a batch-file like this one. Save this batch-file to your TDI installation directory and then type the following in at the command line:

Now Cloudscape will start on your machine, ready to accept requests over port 1527 (the default). To stop the server again, simply enter:

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

Gotchas

The currently bundled Cloudscape version 5.1.2 (5.1 with fixpack 1) does not support any other locale setting than English (I have tested with US and UK and it worked with both of these). In fact, the database server will not start at all. This refers to the locale settings for output, not your keyboard layout. You can also customize specific display parameters like date, time and currency without affecting Cloudscape, as long as the locale is English.

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

Common Errors

For more details on some types of errors, check the Cloudscape logfile (db2j.log) written to your TDI solution directory for embedded mode, and your installation directory during networked mode operations.

Note also that if you get any error messages that mention a missing IBM Schema, then you can correct this by setting the com.ibm.di.store.sysibm property to "true" in the TDI properties file and using the -ld option when starting the Cloudscape server manually.

com.ibm.db2.jcc.a.SQLException: IO Exception opening socket to server localhost on port 1527. The DB2 Server may be down.

Cloudscape is not started. This means you have configured Cloudscape for networked mode but set the com.ibm.di.store.start.mode property to "manual". You could also have your System Store parameters set for embedded mode, but are using the JDBC URL prefix for networked mode. If are using networked mode, you can either start the Cloudscape server manually or set com.ibm.di.store.start.mode "automatic", instructing the TDI Server to start Cloudscape as required.

com.ibm.db2.jcc.a.SQLException: userid length, 0, is not allowed.

The User parameter is blank. Note that unless you have configured Cloudscape for required authentication (in the db2j.properties file), you can enter any value for the User id.

ERROR 42X01: Syntax error: Encountered "<EOF>" at line 1, column 14.

This is a syntax error in the underlying SQL statement. One possible cause is forgetting to fill out the Table Name parameter of the JDBC Connector.

java.sql.SQLException: No suitable driver

You must enter the name of the JDBC Driver class to use. For embedded mode, this is "com.ibm.db2j.jdbc.DB2jDriver" and the URL prefix will be "jdbc:db2j:". For networked mode you must use the "com.ibm.db2.jcc.DB2Driver" class and related prefix: "jdbc:db2j:net:". Note that these are the same driver classes and prefixes used when working with IBM DB2.

SQL Exception: Failed to start database 'Cloudscape'

Cloudscape can fail to start for a number of reasons, including locale setting as described earlier. One common error for embedded mode is that you have started Cloudscape in the CE (for example, by opening the System Store) and then try to run an AL or EH that needs to access the database. In this case, the Server running your task is unable to start up a second instance of an embedded Cloudscape engine.

com.ibm.db2.jcc.a.SQLException: Connection authorization failure occurred. Reason: local security service non-retryable error.

One common reason for this is that the database directory you are referencing in the JDBC URL was not created by Cloudscape. If this is the case, either delete this directory, or point the System Store (or your JDBC Connector) at a different database/directory.

com.ibm.db2.jcc.a.SQLException: Connection authorization failure occurred. Reason: local security service non-retryable error.

One common reason for this is that the database directory you are referencing in the JDBC URL was not created by Cloudscape. If this is the case, either delete this directory, or point the System Store (or your JDBC Connector) at a different database/directory.

-- EddieHartman - 04 Jan 2006

• cs6.0.bat: