Oracle Data Integrator On Connect and XML/LDAP driver

Oracle Data Integrator 11.1.1.5.0 onwards introduces a very useful feature called 'On Connect' and 'On Disconnect'. These are SQL queries that are run on the JDBC connection obtained from the Data Server when the connection is created or closed respectively. See http://docs.oracle.com/cd/E21764_01/integrate.1111/e12643/setup_topology.htm#BGBBCAEH for instructions on how to do this and also limitations on its usage.

ODI comes along with a set of special JDBC drivers that allow users to connect to and use XML/LDAP sources as JDBC sources. The interesting thing to note here is that 'On Connect'/'On Disconnect' SQL commands can be executed on these JDBC connections also. The magical part is that the SQL commands that you enter here - if they are not XML driver commands - will get pushed down to the underlying DB and will be actually executed by the underlying DB. This statement is, in fact, true for any SQL command that you execute on an XML/LDAP Data Server. In case of XML driver, if your command is one of the XML driver commands (listed here : http://docs.oracle.com/cd/E23943_01/integrate.1111/e12644/appendix_xml_driver.htm#CHDFDFJF) then it is intercepted and executed by the driver itself. Otherwise all commands are actually executed on the underlying DB.

Now there is one wrinkle that you need to be aware of when executing 'On Connect' commands on XML/LDAP drivers. As you may be already aware these drivers create/drop tables and indexes and also populate the tables. Parts of this are controlled by properties that you set on the JDBC URL or as property of the Data Server eg: create_indexes for XML driver and load_mode for LDAP driver.


Here is how 'On Connect' would work for a plain DB JDBC connection

1. You provide a SQL for 'On Connect'
2. ODI asks the DB's JDBC driver for a JDBC connection.
3. DB's JDBC driver returns a JDBC connection.
4. ODI executes the SQL that you provided for On Connect on this connection
5. Now ODI uses this connection to handle your Procedure commands.

Now here is how it works for XML/LDAP

1. You provide a SQL for On Connect
2. ODI asks XML/LDAP JDBC driver for a connection. The connection URL has XML/LDAP connection parameters
3. XML/LDAP driver internally obtains a JDBC connection to internal HSQL/external DB.
4. XML/LDAP driver executes SQL commands that do whatever it is that user has asked it to do via connection properties : truncate tables, drop schema, load data ….
5. XML/LDAP driver returns an XML/LDAP JDBC connection
6. ODI executes the SQL that you provided for On Connect on this connection
7. Now ODI uses this connection to handle your Procedure commands.
   

The points to note are (3) and (4) in the way it works for XML/LDAP drivers. So if you are providing a SQL for 'On Connect' that you would expect to affect all table/index operations on the underlying DB, do not be surprised if it does not work for the initial operations performed by the XMl/LDAP JDBC drivers.