The System Store can also be configured to use other multi-user RDBMS systems, as opposed to using the bundled database, Apache Derby. This is done by specifying appropriate SQL Data Definition Language (DDL) statements and driver parameters as system properties in global.properties or solution.properties. Example statements, commented out, are present in the distribution version of global.properties in the TDI_install_dir/etc directory, for the supported configurations of IBM DB2, Oracle and MS SQL*Server.
It is also possible to take advantage of suitable templates built in to the Configuration Editor, by going to the appropriate TDI Server document. Right-click on the Server in the Servers pane, and select Edit system store settings. The Server System Store header in the window is a context-sensitive menu; it has selections for Derby Embedded, Derby Networked, Oracle, DB2, MS SQL*Server 2005+ and IBM SolidDB.
A System Store can also be configured on a per-project basis in the Configuration Editor; these settings are then stored in the Config file when the project is exported, and take precedence over the System Store defined for the Server.
JDBC Driver parameters provide a path to the database; additional properties are used to specify tailored SQL for certain operations TDI must be able to perform in the System Store. Multiple SQL statements can be specified per property. Each separate statement should be terminated with a semicolon. An example property could be (note that for display purposes, the statements in this document are broken up in multiple lines; however, in your property file all statements for a given property should be on one line):
com.ibm.di.store.create.delta.systable=CREATE TABLE {0} (ID VARCHAR(VARCHAR_LENGTH) NOT NULL, SEQUENCEID int, VERSION int); ALTER TABLE {0} ADD CONSTRAINT IDI_DS_{UNIQUE} PRIMARY KEY (ID)
where {0} => is replaced by the Table name; and
{UNIQUE} => is a special variable which can be used to generate a unique name based on the current system time.
The following section lists example connection parameters and statements for each of the supported RDBMS systems.
Usage of Oracle requires that you drop the JDBC driver client library, ojdbc14.jar, in the TDI_install_dir/jars directory.
com.ibm.di.store.database=jdbc:oracle:thin:@itdidev.in.ibm.com:1521:itimdb com.ibm.di.store.jdbc.driver=oracle.jdbc.OracleDriver com.ibm.di.store.jdbc.urlprefix=jdbc:oracle:thin: com.ibm.di.store.jdbc.user=SYSTEM {protect}-com.ibm.di.store.jdbc.password=password
Where itimdb is the SID of the database to be used as System Store.
com.ibm.di.store.create.delta.systable=CREATE TABLE {0} (ID VARCHAR(VARCHAR_LENGTH) NOT NULL, SEQUENCEID int, VERSION int);ALTER TABLE {0} ADD CONSTRAINT IDI_CS_{UNIQUE} PRIMARY KEY (ID) com.ibm.di.store.create.delta.store=CREATE TABLE {0} (ID VARCHAR(VARCHAR_LENGTH) NOT NULL, SEQUENCEID int, ENTRY BLOB );ALTER TABLE {0} ADD CONSTRAINT IDI_DS_{UNIQUE} Primary Key (ID) com.ibm.di.store.create.property.store=CREATE TABLE {0} (ID VARCHAR(VARCHAR_LENGTH) NOT NULL, ENTRY BLOB );ALTER TABLE {0} ADD CONSTRAINT IDI_PS_{UNIQUE} Primary Key (ID) com.ibm.di.store.create.sandbox.store=CREATE TABLE {0} (ID VARCHAR(VARCHAR_LENGTH) NOT NULL, ENTRY BLOB ) com.ibm.di.store.create.recal.conops=CREATE TABLE {0} (METHOD varchar(VARCHAR_LENGTH), RESULT BLOB, ERROR BLOB)
Use of MS SQL Server requires that you install a number of Microsoft client libraries in the TDI_install_dir/jars directory.
com.ibm.di.store.database=jdbc:Microsoft:sqlserver://localhost:1433;DatabaseName=master;selectMethod=cursor; com.ibm.di.store.jdbc.driver=com.microsoft.jdbc.sqlserver.SQLServerDriver com.ibm.di.store.jdbc.user=sa com.ibm.di.store.jdbc.password=passw0rdThe above connection parameters are used with these Microsoft JDBC jars:
For Microsoft SQL Server 2005, the driver jar file to be placed in the TDI_install_dir/jars directory is sqljdbc.jar (only one file is required) and it can be obtained from the SQL Server 2005 installation at <Microsoft SQL Server 2005-Install-Dir>/sqljdbc_<version>/<language>/sqljdbc.jar; the JDBC connection parameters need to be specified as follows:
com.ibm.di.store.database=jdbc:sqlserver://localhost:1433;DatabaseName=name;selectMethod=cursor; com.ibm.di.store.jdbc.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver com.ibm.di.store.jdbc.user=sa com.ibm.di.store.jdbc.password=passw0rdThe selectMethod property is optional to the jdbc URL. When this property is set to "cursor", a database cursor is created. This is useful when reading very large result sets that cannot be contained in the clients memory.
The default behavior of selectMethod is not "cursor", but "direct", which keeps result sets in clients memory, thus providing much faster performance. So unless memory is a problem, it is better to use the default "direct" behavior. For more information: http://msdn.microsoft.com/en-us/library/ms378988(SQL.90).aspx.
com.ibm.di.store.database= jdbc:JSQLConnect://itdiderver/database=reqpro com.ibm.di.store.jdbc.driver= com.jnetdirect.jsql.JSQLDriver com.ibm.di.store.jdbc.urlprefix= jdbc:JSQLConnect: com.ibm.di.store.jdbc.user=administrator {protect}-com.ibm.di.store.jdbc.password=passwordThese connection parameters are used with JSQLConnect drivers. You must download the JSQLConnect.jar file and copy it into the TDI_install_dir/jars directory.
com.ibm.di.store.create.delta.systable=CREATE TABLE {0} (ID VARCHAR(VARCHAR_LENGTH) NOT NULL, SEQUENCEID int, VERSION int); ALTER TABLE {0} ADD CONSTRAINT IDI_MYCONSTRAINT_{UNIQUE} PRIMARY KEY (ID) com.ibm.di.store.create.delta.store=CREATE TABLE {0} (ID VARCHAR(VARCHAR_LENGTH) NOT NULL, SEQUENCEID int, ENTRY IMAGE ); ALTER TABLE {0} ADD CONSTRAINT IDI_DS_{UNIQUE} Primary Key (ID) com.ibm.di.store.create.property.store=CREATE TABLE {0} (ID VARCHAR(VARCHAR_LENGTH) NOT NULL, ENTRY IMAGE ); ALTER TABLE {0} ADD CONSTRAINT IDI_PS_{UNIQUE} Primary Key (ID) com.ibm.di.store.create.sandbox.store=CREATE TABLE {0} (ID VARCHAR(VARCHAR_LENGTH) NOT NULL, ENTRY IMAGE) com.ibm.di.store.create.recal.conops=CREATE TABLE {0} (METHOD varchar(VARCHAR_LENGTH), RESULT IMAGE, ERROR IMAGE)
com.ibm.di.store.database=jdbc:db2:net://localhost:50000/ididb com.ibm.di.store.jdbc.driver=com.ibm.db2.jcc.DB2Driver com.ibm.di.store.jdbc.urlprefix= jdbc:db2:net: com.ibm.di.store.jdbc.user=db2admin {protect}-com.ibm.di.store.jdbc.password=db2admin
Where ididb in the database URL is the DSN for a DB2 instance.
The above connection parameters are used with the db2jcc_license_cisuz.jar license jar file.
com.ibm.di.store.create.delta.systable=CREATE TABLESPACE TS1DSYS LOCKSIZE ROW BUFFERPOOL BP32K; CREATE TABLE {0} (ID VARCHAR(VARCHAR_LENGTH) NOT NULL, SEQUENCEID int, VERSION int) IN TS1DSYS; CREATE UNIQUE INDEX DSTIX1 ON {0} (ID ASC); ALTER TABLE {0} ADD CONSTRAINT IDI_DT_{UNIQUE} PRIMARY KEY (ID) com.ibm.di.store.create.delta.store=CREATE TABLESPACE TS1DST LOCKSIZE ROW BUFFERPOOL BP32K; CREATE TABLE {0} (ID VARCHAR(VARCHAR_LENGTH) NOT NULL, SEQUENCEID int, ENTRY BLOB) IN TS1DST; CREATE UNIQUE INDEX DSIX1 ON {0} (ID ASC); ALTER TABLE {0} ADD CONSTRAINT IDI_DS_{UNIQUE} Primary Key (ID); CREATE LOB TABLESPACE DSENT11 BUFFERPOOL BP32K LOCKSIZE LOB; CREATE AUX TABLE TBDSEN1 IN DSENT11 STORES {0} COLUMN ENTRY; CREATE INDEX IXEN1 ON TBDSEN1 com.ibm.di.store.create.property.store=CREATE TABLESPACE PS3DST LOCKSIZE ROW BUFFERPOOL BP32K; CREATE TABLE {0} (ID VARCHAR(VARCHAR_LENGTH) NOT NULL, ENTRY BLOB) IN PS3DST; CREATE UNIQUE INDEX PSIX3 ON {0} (ID ASC); ALTER TABLE {0} ADD CONSTRAINT IDI_PS_{UNIQUE} Primary Key (ID); CREATE LOB TABLESPACE PSENT31 BUFFERPOOL BP32K LOCKSIZE LOB; CREATE AUX TABLE TBPSEN3 IN PSENT31 STORES {0} COLUMN ENTRY; CREATE INDEX PSIXEN3 ON TBPSEN3 com.ibm.di.store.create.sandbox.store=CREATE TABLE {0} (ID VARCHAR(VARCHAR_LENGTH) NOT NULL, ENTRY BLOB) com.ibm.di.store.create.recal.conops=CREATE TABLESPACE IM{UNIQUE} LOCKSIZE ROW BUFFERPOOL BP32K; CREATE TABLE {0} (METHOD VARCHAR(VARCHAR_LENGTH), RESULT BLOB, ERROR BLOB) IN IM{UNIQUE}; CREATE LOB TABLESPACE LB{UNIQUE} BUFFERPOOL BP32K LOCKSIZE LOB; CREATE AUX TABLE AT{UNIQUE} IN LB{UNIQUE} STORES {0} COLUMN RESULT; CREATE INDEX IX{UNIQUE} ON AT{UNIQUE}; CREATE LOB TABLESPACE LS{UNIQUE} BUFFERPOOL BP32K LOCKSIZE LOB; CREATE AUX TABLE AE{UNIQUE} IN LS{UNIQUE} STORES {0} COLUMN ERROR; CREATE INDEX IN{UNIQUE} ON AE{UNIQUE}
com.ibm.di.store.database=jdbc:db2:net://localhost:50000/ididb com.ibm.di.store.jdbc.driver=com.ibm.db2.jcc.DB2Driver com.ibm.di.store.jdbc.urlprefix= jdbc:db2:net: com.ibm.di.store.jdbc.user=db2admin {protect}-com.ibm.di.store.jdbc.password=db2admin
Where ididb in the database URL is the DSN for a DB2 instance.
com.ibm.di.store.create.delta.systable=CREATE TABLE {0} (ID VARCHAR(VARCHAR_LENGTH) NOT NULL, SEQUENCEID int, VERSION int); ALTER TABLE {0} ADD CONSTRAINT IDI_MYCONSTRAINT_{UNIQUE} PRIMARY KEY (ID) com.ibm.di.store.create.delta.store=CREATE TABLE {0} (ID VARCHAR(VARCHAR_LENGTH) NOT NULL, SEQUENCEID int, ENTRY BLOB ); ALTER TABLE {0} ADD CONSTRAINT IDI_DS_{UNIQUE} Primary Key (ID) com.ibm.di.store.create.property.store=CREATE TABLE {0} (ID VARCHAR(VARCHAR_LENGTH) NOT NULL, ENTRY BLOB ) ;ALTER TABLE {0} ADD CONSTRAINT IDI_PS_{UNIQUE} Primary Key (ID) com.ibm.di.store.create.sandbox.store=CREATE TABLE {0} (ID VARCHAR(VARCHAR_LENGTH) NOT NULL, ENTRY BLOB )
IBM SolidDB requires that the SolidDriver2.0.jar file is put in the TDI_install_dir/jars directory. This JAR can be obtained from the SolidDB installation (from SolidDB_install_dir/jdbc/SolidDriver2.0.jar.
com.ibm.di.store.database=jdbc:solid://localhost:1315 com.ibm.di.store.jdbc.driver=solid.jdbc.SolidDriver com.ibm.di.store.jdbc.urlprefix=jdbc:solid: com.ibm.di.store.jdbc.user=dba {protect}-com.ibm.di.store.jdbc.password=dba
com.ibm.di.store.create.delta.systable=CREATE TABLE {0} (ID VARCHAR(VARCHAR_LENGTH) PRIMARY KEY NOT NULL, SEQUENCEID int, VERSION int) com.ibm.di.store.create.delta.store=CREATE TABLE {0} (ID VARCHAR(VARCHAR_LENGTH) PRIMARY KEY NOT NULL, SEQUENCEID int, ENTRY BLOB) com.ibm.di.store.create.property.store=CREATE TABLE {0} (ID VARCHAR(VARCHAR_LENGTH) PRIMARY KEY NOT NULL, ENTRY BLOB) com.ibm.di.store.create.sandbox.store=CREATE TABLE {0} (ID VARCHAR(VARCHAR_LENGTH) NOT NULL, ENTRY BLOB) com.ibm.di.store.create.recal.conops=CREATE TABLE {0} (METHOD VARCHAR(VARCHAR_LENGTH), RESULT BLOB, ERROR BLOB)
The remainder of this chapter discusses the operational aspects of using Derby, in particular in conjunction with using Derby to hold your System Store.
With regards to third party RDBMSs, in order to hold encrypted password values you may have to size the fields that hold them quite large. A typical small password might use as much as 178 characters. It depends on both your server's key, and the length of the unencrypted data you try to store (in bytes). Since this is a blocked encoding a larger password might use the same space, or double or triple that amount. Also, the size of the block depends on the server's key. One way to find the size we need, is to store the password (protected) to a file first, and then look at that file to see how many characters were used.
Derby can run in either of two modes: embedded and networked. By default, as specified in the global.properties file, Derby runs in networked mode.
The System Store used by TDI releases before V7.0 was Derby (then called Cloudscape) in embedded mode. There are drawbacks to the way Derby runs in embedded mode. In embedded mode, Derby runs as a separate thread within the JVM when required. Startup and shutdown of Derby is automatic in embedded mode. However, when run this way, this Derby thread claims exclusive access to the database files. This can become problematic when different JVMs, each with its own Derby thread, try to access the same System Store.
In embedded mode, these actions cause a new, independent JVM to be started, triggering an access conflict when more than one JVM is active at any given time:
None of these actions by themselves causes the Derby thread to start. However, the Derby thread does start if access to any of the objects in the System Store is required (for example, Objects supported by the System Store such as Delta Tables and the User Property Store).
The solution to the access conflicts as outlined previously is to run Derby in networked mode, which enables concurrent access to the System Store. Also enable user authentication in derby to avoid security concerns in networked mode. To provide security at the database level, TDI uses the BUILTIN security provider for Derby. BUILTIN ensures that only valid users are able to access the Derby database. When we have Derby configured in networked mode, we can work with multiple instances of Derby databases booted as System Stores. We can also configure a Derby instance to work with a specific Configuration file instance.
Depending on how Derby was started, instances of Derby my be left running in networked mode, even after all other TDI processes have terminated.
When you set the property derby.drda.startNetworkServer to true (by default, this is the case, in global.properties), the Network Server automatically starts when we start Derby (in this context, Derby starts when the embedded driver is loaded).You may have to terminate Derby manually, if desired.