Cool Blog: Relational Database Synchronization Using JDBC
Novell Cool Solutions: Feature
By Steven L. Weitzeil
Digg This -
Posted: 15 Nov 2006
The popularity of relational databases has made the the Identity Manager Driver for JDBC one of the most frequently purchased IDM drivers. It is sold as part of the Novell Identity Manager Integration Module for Database.
In this article I will: 1) Review the broad connectivity this driver adds to IDM; and 2) Provide a general overview of how the JDBC driver works.
Prior to reading this article, it is recommended that you have a general understanding of how IDM drivers work. May I refer you to my previous article Integrating Identity Manager with Web Services.
The purpose of any IDM driver is to enable a connected system to publish and subcribe data between a given system and Novell Identity Manager. In the case of the JDBC driver, it enables IDM to synchronize data with most relational databases and database-hosted applications. The following systems have been tested (as of this publication date):
- IBM DB2 Universal Database (UDB) 7.2 or higher
- IBM DB2 Universal Database (UDB) 8.2 or higher
- Informix Dynamic Server (IDS) 9.40 or higher
- Microsoft SQL Server 8 (2000) Service Pack 3a or higher
- Microsoft SQL Server 7.5 Service Pack 4 or higher
- MySQL 4.1.6 or higher
- Oracle 8i Release 3 (8.1.7)
- Oracle 9i Release 2 (184.108.40.206) or higher
- Oracle 10g
- PostgreSQL 7.0.6 or higher
- Sybase Adaptive Server Enterprise (ASE) 12.5 or higher
- Java Database Connectivity (JDBC)
The JDBC API was developed by Sun Microsystems to provide cross-platform SQL-based database access. It enables Java applications to connect to supported databases, perform SQL (Structured Query Language) operations (Select, Insert, Update, Delete), and process the results of those queries.
Data Synchronzation Models
One key challenge in synchronizing data with a database system is accurately mapping structured data in customer tables to the structure required by the driver. Since relational databases utilize tables to store data, it is ideal if customers have tables that exactly matched the structure required by the driver. Unfortunately, that is not typically the case. To address this issue, the JDBC driver supports two types of data synchronization models: Direct and Indirect.
If a customer has tables that match the structure required by the JDBC Driver, then the driver may be configured to utilize the Direct data synchronization model.
If the tables do not match the structure required by the driver, there is still another way to utilize the Direct model. This is done using views. You can think of a view as a virtual table. The virtual table is generated by a Select statement that references specific rows and columns in one or more customer tables. The contents of the referenced rows and columns are then returned to create a single table containing the requested data. Utilizing a view, virtually any table structure can be made compatible with the JDBC driver. The following diagrams (from the Novell JDBC driver documentation) show how this model works on both the subscriber and publisher channels.
As data passes from IDM to the JDBC driver (subscriber channel), it is written by the driver shim to a view which updates the customer tables that actually store the data.
Correspondingly, as data flows from the connected system to IDM (publisher channel), Publication triggers capture events by inserting rows into a special table called the Event Log. The driver shim then processes events from the Event Log and reads data from the view (if necessary) and sends it to IDM as XML. Once the data has been sent, the shim deletes or marks the rows in the Event Log table as processed.
It is worth noting that the publisher channel can capture events without the use of triggers or the Event Log table. For more information on triggerless processing, please refer to the JDBC Driver Documentation.
In situations where customer tables do not match the structure required by the shim and views cannot be used, it becomes necessary for the shim to synchronize with intermediate staging tables instead of directly with customer tables. As data passes from IDM to the connected system (subscriber channel), the XML data is converted and placed by the JDBC shim in the intermediate staging tables. Then, the data can be relayed to customer tables using triggers or any method deemed acceptable to the customer.
As data flows from the database to IDM (publisher channel), changes in customer tables are captured using triggers on customer tables (synchronization triggers) so they're reflected in the intermediate staging tables. Publication events are captured using triggers on the intermediate staging tables (publication triggers). The shim reads the events from the Event Log table and deals with the data as specified by the event.
The publisher channel can also capture events without the use of triggers or the Event Log table (see the JDBC Driver Documentation).
The JDBC driver does not support named passwords, but they can be used inside of the policies used while the data passes to and from the IDM Identity Vault.
The driver does support password set, check, and modify on the subscriber channel. Password set is also supported on the publisher channel.
The Novell Identity Manager JDBC Driver enables bi-directional data synchronization with systems that support the JDBC API. The driver provides two data synchronization models that enable it to conform to the specific design and access requirements of the target system. For more detailed information on this driver, please refer to the *LINK* JDBC driver documentation.
Novell Cool Solutions (corporate web communities) are produced by WebWise Solutions. www.webwiseone.com