IBM Tivoli Directory Integrator
The System Store Connector provides access to the underlying
System Store. The primary use of the System Store Connector is to
store Entry objects into the System Store tables.
However, we can also use the connector to connect to an external
Derby, DB2 8.1, Oracle, Microsoft SQL*Server or IBM SolidDB database, not just the database configured
as the System Store. Each Entry object is identified
by a unique value called the key attribute.
The System Store Connector creates a new table in a specified database
if one does not already exist. If you iterate on a non-existing table, the (empty) table is created, and the Iterator returns no values.
The System Store Connector uses the following SQL statements
to create a table and set the primary key constraint on the table
(Derby syntax):
"CREATE TABLE {0} (ID VARCHAR(VARCHAR_LENGTH) NOT NULL, ENTRY BLOB );
ALTER TABLE {0} ADD CONSTRAINT {0}_PRIMARY Primary Key (ID);"
This connector provides pre-set SQL statements for a number
of popular databases, but there is also the ability to modify them
as we see fit. For other databases, enter our own, equivalent
SQL statements (multiple ones, if required) by specifying these in
the Create Table Statement parameter. The parameter
can not be empty; in that case, an exception is thrown.
Notes:
- The VARCHAR_LENGTH value is picked up from the com.ibm.di.store.varchar.length property
set in the Properties Store (TDI-P). The default VARCHAR_LENGTH
is set to 512. We can change this value by setting the value of the com.ibm.di.store.varchar.length in
the Properties Store.
- Another attribute, tdi.pesconnector.return.wrapped.entry, exists for TDI 6.0 backward compatibility. If you define this property
in the TDI global.properties file
and set it to true , then TDI reverts
back to its earlier behavior where for example, the findEntry() method
(used by the system in Iterator, Lookup and Update modes) would return
an Entry object of the format: [ENTRY: <Instance of Entry object
containing Attributes passed by user>]. In TDI 6.0, in order to obtain
the original passed attributes, you would need to write JavaScript
code something like this:
Entry e = (Entry)conn.getAttribute("ENTRY");
at
some appropriate place, after which e contains
the Attributes originally passed in when writing to the System Store.
We could do this in the Input Attribute Map Hook where you would
have to carefully map the Attributes in e to
the work entry, or use a Script Component
after this Connector to unpack the composite entry attribute
in the work entry using the aforementioned
JavaScript example (substitute work for conn.)
In TDI 7.1, by default the entry is unwrapped and therefore all attributes passed
by you are now directly available as attributes in the Entry. The
above scripting will not be needed any longer (unless you set the
tdi.pesconnector.return.wrapped.entry attribute to true.)
- The System Store Connector operates in the following
modes: AddOnly, Update, Delete, Iterate, Lookup. However, AddOnly, Update and Delete operations are not permitted on the Delta Tables
and Property store tables.
The Connector supports both simple and advanced Link Criteria.
This Connector, like the JDBC Connector it is based upon, in principle can handle secure connections using the SSL protocol.
However, it may require driver specific configuration steps in order
to set up the SSL support. Refer to the manufacturer's driver documentation
for details.
The System Store Connector requires the following parameters.
- Database
- The location of the database. This is an optional parameter;
if left blank, the System Store as configured in property com.ibm.di.store.database in
the global.properties file is used. Note that this is the
value displayed in the Store -> View
System Store screen.
- Username
- The name of the user used to make a JDBC connection to the specified
database. Only the tables available to this user are shown. If this
is not specified then the value of the com.ibm.di.store.jdbc.user property
set in the global.properties file is used as the default
value.
- Password
- The password of the user used to make a JDBC connection to the
specified database. If this is not specified then the value of the com.ibm.di.store.jdbc.password property
set in the global.properties file is used as the default
value.
- Key Attribute Name
- The attribute name giving the unique value for the entry. This
is a required parameter.
We can specify multiple Key
Attribute Names separated by the "+" sign. The System Store Connector
will concatenate these into a single varchar(255) key to
obtain a unique key.
- Selection Mode
- Specify All, Existing or Deleted. In order to use the Existing and Deleted keywords, the Connector must reference a
Delta table in the System Store. When Delta is enabled on an Iterator, the AssemblyLine stores a sequence property in the database, adding
a sequence number to each entry read from the source. This parameter
is to be used on Delta tables only.
Delta table names
in TDI 6.0 and above, have an "IDI_DS_" prefix added
to the identifier specified in "Delta Store"
field of the Delta configuration tab.
- Table Name
- The table name to store the entries in. This is a required parameter.
The System Store Connector will create a table with the specified
table name if it does not exist.
Notes:
- The "Select" button in the Connector configuration tab of the
connector provides a list of tables in the connected database. Only
the tables available to the user specified in the Username field are
shown.
- The "Delete" button in the Connector configuration tab can be
used to delete a selected table. Ideally, the Delete button should
be used when an AssemblyLine has run and you would now want to delete the table
created by the System Store Connector. This does not work with the
Delta tables.
- The table name must be a valid name for the database
you are accessing. In most cases, this will mean the name must begin
with a letter, and otherwise may only contain letters, digits and
the underscore (_) character.
- JDBC Driver
- This parameter contains the Java class name of the JDBC driver
(instead of the database name as in previous versions. Existing configurations
will be migrated automatically).
If the parameter is left empty
or one of its provided options is selected, the Create Table Statement
parameter is initialized with a default "CREATE TABLE" statement
for the database used. If JDBC Driver is not specified the
JDBC driver configured in the System Store settings is used to obtain
the proper value for Create Table Statement.
This
parameter enables the System Store Connector to connect to different
System Store databases without changing the System Store settings.
The
possible values are:
- org.apache.derby.jdbc.ClientDriver
- org.apache.derby.jdbc.EmbeddedDriver
- com.ibm.db2.jcc.DB2Driver
- oracle.jdbc.OracleDriver
- com.microsoft.sqlserver.jdbc.SQLServerDriver
- solid.jdbc.SolidDriver
The default value is empty.
- Create Table Statement
- The "CREATE TABLE" SQL statement used to create the tables in
the selected data source. You are required to enter the correct CREATE
TABLE statement corresponding to the database that us choose to connect
to. Otherwise the Connector will fail to create the table if the table
is missing.
- Delete table on close
- If this value is set to true then the
table created by the System Store Connector will be dropped when the
Connector terminates.
- SQL Select
- The select statement to execute when selecting entries for iteration.
Specifies the WHERE clause. This will be used as a search filter to
return the data set in Iterator mode. If this is left blank, the default
construct (SELECT * FROM TABLE) is used, where TABLE is
the name specified in the "Table Name" field.
- Commit
- Controls when database transactions are committed. Options are:
- After every database operation
- On Connector Close
- Manual
- End of Cycle
Manual means user must call the commit() method of the System Store Connector --
or, alternatively, rollback() if your logic
requires this.
- Detailed Log
- If this field is checked, additional log messages are generated.
The System Store Connector provides access to the tables
created in the System Store. The System Store can be located on any
DB server for which a JDBC driver is available. Furthermore, if the
System Store uses Derby, it can be configured to run in either embedded
(inside the TDI Server process) or networked mode. The connector
is able to resolve globally defined parameters to obtain a connection
the default System Store. In order to configure a connection to a
different DB at least the following parameters must be explicitly
provided: Database, Username, Password or JDBC Driver.
The correct way to specify the database and JDBC Driver for different
configurations of System Store is given below.
The
examples are specific to Windows.
- Using System Store Connector with embedded Derby server
configured as System Store
-
Database: f:\Program Files\IBM\IBMDirectoryIntegrator/Derby
JDBC Driver: org.apache.derby.jdbc.EmbeddedDriver
In the embedded mode of operation, the Derby server is automatically
started and the specified database is booted into the database if
it exists. If it does not exist a new database is created at the specified
location.
- Using System Store Connector with networked Derby server
configured as System Store
-
Database: jdbc:derby://localhost:1527/E:\TDI\TDISysStore;create=true
JDBC Driver: org.apache.derby.jdbc.ClientDriver
Notes:
- It is important to specify the "create=true" flag in the database
URL. This will create the database if it does not exist. This is required
when Derby is configured to run in networked mode.
- In networked mode of operation, the Derby server my need to be
started manually. For details regarding the ways in which a Derby
server can be started in networked mode, please refer to the chapter
on System Store in IBM TDI V7.1 Installation and Administrator Guide
- Using System Store Connector with DB2 8.1 server as
System Store
-
Database: jdbc:db2://machine-name:50000/testDB
JDBC Driver: com.ibm.db2.jcc.DB2Driver
Notes:
- The DB2 instance and the DB2 database must be created ahead of
time for it to be used as System Store.
- The specified instance must be running on the specified port in
the database URL.
Connector usage examples in TDI_install_dir/examples/systore, and TDI_install_dir/examples/SystemStore;
The chapter on System Store in IBM TDI V7.1 Installation and Administrator Guide.