IBM Support

Query Derby database outside of Security Directory Integrator

How To


Summary

How can data in the IBM Security Directory Integrator (SDI) SystemStore be accessed without the 'Browse Server Stores' option in the Config Editor or the SystemStore Connector?

Objective

Both TDI 7.1.x and SDI 7.2.x use the Derby database, in Network Server mode, as its default SystemStore.  In certain situations  modifications to data in the SystemStore maybe required.  

For example -- When using a Changelog connector, the processed change log number is stored as an integer in the SystemStore.  Specifically in the table name given in the 'Iterator State Key' parameter of the connector.  If an administrator (or non-TDI person) needs to update the last processed changelog number in the SystemStore, this operation can be perform by manually updating the row of the table associated with the Iterator State Key, or the entire row could be removed from the table.  

When the Derby instance is configured in Network Server mode, a sql client with proper credentials can access the database.  External host access is disabled by default.  Refer to 'Why can't remote connections be made to my Derby network server?'

For further details on the use of the Derby IJ client, refer to http://db.apache.org/derby/papers/DerbyTut/ij_intro.html

Steps

Here is a list of common commands found to be useful:


## How to start the Derby command line client (command is one continuous line)
@ Linux/Unix Version
/opt/IBM/TDI/V7.2/jvm/jre/bin/java -Dij.user=APP -Dij.password=APP -cp /opt/IBM/TDI/V7.2/jars/3rdparty/IBM/derby.jar:/opt/IBM/TDI/V7.2/jars/3rdparty/IBM/derbyclient.jar:/opt/IBM/TDI/V7.2/jars/3rdparty/IBM/derbytools.jar org.apache.derby.tools.ij

@ Windows Version
c:\ProgramFiles\IBM\TDI\V7.2\jvm\jre\bin\java  -Dij.user=APP -Dij.password=APP -cp "c:\Program Files\IBM\TDI\V7.2\jars\3rdparty\IBM\derby.jar";"c:\ProgramFiles\IBM\TDI\V7.2\jars\3rdparty\IBM\derbyclient.jar";"c:\Program Files\IBM\TDI\V7.2\jars\3rdparty\IBM\derbytools.jar" org.apache.derby.tools.ij



## Establish connection to the local database. 
## Update the command to reference the location of the TDISysStore directory. Commonly found in the SDI Solution Directory
CONNECT 'jdbc:derby://localhost:1527//opt/IBM/TDI/SolV72/TDISysStore;create=true';

## Show All ables in database
SHOW TABLES;

## Set Schema for Session
SET SCHEMA APP;



## If table names are long, I found it easier to locate table names in the following command output.
SELECT * FROM SYS.SYSTABLES;

## To Drop A Table
DROP TABLE <table name>

## View all rows of a table
SELECT * FROM IDI_PS_DEFAULT;

## Delete row from table
DELETE FROM IDI_PS_DEFAULT WHERE ID='<ID>';

Related Information

Developer.ibm.com reference

[{"Business Unit":{"code":"BU008","label":"Security"},"Product":{"code":"SSCQGF","label":"Tivoli Directory Integrator"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB24","label":"Security Software"}}]

Document Information

Modified date:
17 October 2018

UID

ibm10719381

Page Feedback