Novell Home

Advanced Triggerless Oracle JDBC Driver - Part 2

Novell Cool Solutions: Tip
By Michel Bluteau

Digg This - Slashdot This

Updated: 17 Aug 2005
 

Advanced Triggerless Oracle JDBC Driver for Users and Roles
Part 2: Optimization

Note: This article references author Michel Bluteau's earlier Cool Solutions piece at:
http://www.novell.com/coolsolutions/feature/11832.html


This article includes an optimization tip in relation to the article above, posted in March 2005. Please note that references to the triggerless driver are not for the JDBC 3.0 official driver, which also implement triggerless support, but in a different fashion - using a file. My driver leverages XSLT and the Eventlog table instead, and under some circumstances it can scale better. In this article, I briefly describe an alternative configuration, crafted in order to handle a heavy load.

Problem

After conducting some extensive tests with the original driver, we discovered that it could take up to two hours to perform each poll cycle, which are identical in practice to an initial loading of all the data. The DirXML engine can discard existing entries without giving an error message, but as the number of Oracle accounts, Roles and Role Memberships increases. Therefore, this approach may prove ineffective.

Solution

There is a documented optional parameter for the driver that modifies the way the Eventlog table is maintained by the driver. It is possible to keep processed records, instead of deleting them, and use them in XSLT in order to avoid performing a full data load (insert all records in Eventlog) for each and every poll cycle. The downside is that the Eventlog table would grow in size, but I believe this should be fairly limited growth. I prefer this alternative to an external file managed by the driver, as implemented by the JDBC 3.0 driver.

Of course, I strongly suggest that some extensive tests be conducted against your data and its change pattern before one approach is finally selected.

Adding the Driver Parameter

In order to modify how the driver manages processed records in Eventlog, you need to add a driver parameter to the parameter page in the Publisher section while in XML mode:

<delete-from-log display-name="Delete processed records in Eventlog?" id="110">no</delete-from-log>

Then you must replace the existing Sub-Clock XSLT code (Subscriber Event Transform) with the following code. You can also click here to get the modified Sub-Clock XSLT.

<?xml version="1.0" encoding="UTF-8"?><xsl:stylesheet exclude-result-prefixes="query cmd dncv" version="1.0" xmlns:cmd="http://www.novell.com/nxsl/java/com.novell.nds.dirxml.driver.XdsCommandProcessor" xmlns:dncv="http://www.novell.com/nxsl/java/com.novell.nds.dirxml.driver.DNConverter" xmlns:query="http://www.novell.com/nxsl/java/com.novell.nds.dirxml.driver.XdsQueryProcessor" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
	<!-- parameters passed in from the DirXML engine -->
	<xsl:param name="srcQueryProcessor"/>
	<xsl:param name="destQueryProcessor"/>
	<xsl:param name="srcCommandProcessor"/>
	<xsl:param name="destCommandProcessor"/>
	<xsl:param name="dnConverter"/>
	<xsl:param name="fromNds"/>
	<!-- identity transformation template -->
	<!-- in the absence of any other templates this will cause -->
	<!-- the stylesheet to copy the input through unchanged to the output -->
	<xsl:template match="node()|@*">
		<xsl:copy>
			<xsl:apply-templates select="@*|node()"/>
		</xsl:copy>
	</xsl:template>
	<!-- Detect a clock tick -->
	<xsl:template match="modify[@class-name='tsxClock']" xmlns:jdbc="urn:dirxml:jdbc">
		<xsl:message>We are in the stylesheet Sub-Clock for a clock tick</xsl:message>
		<!--	_________________	Query eDir for existing attributes __________________ -->
		<xsl:variable name="src-query">
			<query class-name="tsxClock" dest-dn="{@src-dn}" scope="entry">
				<read-attr attr-name="CN"/>
				<read-attr attr-name="currentTime"/>
			</query>
		</xsl:variable>
		<!--	_________________	Paste exiting eDir attributes into variables __________________ -->
		<xsl:variable name="result" select="query:query($srcQueryProcessor, $src-query)"/>
		<xsl:variable name="clock" select="$result/nds/output/instance[1]/attr[@attr-name='CN']/value[1]"/>
		<xsl:variable name="currenttime" select="$result/nds/output/instance[1]/attr[@attr-name='currentTime']/value[1]"/>
		<xsl:choose>
			<xsl:when test="$currenttime and string($clock) = 'Ora10gR2Clock'">
				<xsl:call-template name="clock-tick">
					<xsl:with-param name="clock" select="$clock"/>
					<xsl:with-param name="currenttime" select="$currenttime"/>
				</xsl:call-template>
			</xsl:when>
			<xsl:otherwise>
				<xsl:message>
					<status level="error" type="password-set-operation">
						<xsl:text>Unable to tick clock.  </xsl:text>
						<xsl:text>Missing 'currenttime' value or wrong clock.</xsl:text>
					</status>
				</xsl:message>
				<xsl:copy>
					<xsl:apply-templates select="@*|node()"/>
				</xsl:copy>
			</xsl:otherwise>
		</xsl:choose>
	</xsl:template>
	<!-- Adds SQL for clock tick -->
	<xsl:template name="clock-tick" xmlns:Mapping="http://www.novell.com/nxsl/java/com.novell.nds.dirxml.driver.jdbc.util.MappingPolicy" xmlns:jdbc="urn:dirxml:jdbc">
		<xsl:param name="cn"/>
		<xsl:param name="currenttime"/>
		<xsl:message>We are in the stylesheet Sub-Clock for processing a clock tick</xsl:message>
		<xsl:copy>
			<xsl:apply-templates select="node() | @*"/>
		</xsl:copy>
		<xsl:message>Create fake user events in eventlog</xsl:message>
		<jdbc:statement jdbc:op-id="0" jdbc:transaction-id="0" jdbc:transaction-type="manual" jdbc:type="update">
			<jdbc:sql>
				<xsl:text>INSERT INTO IDM.EVENTLOG</xsl:text>
				<xsl:text>(record_id, event_type, perpetrator, table_name, table_key)</xsl:text>
				<xsl:text>SELECT IDM.SEQ_RECID.nextval, 5, 'fake', 'VIEW_ORCLUSERS', 'pk_USERNAME=' || pk_USERNAME FROM VIEW_ORCLUSERS WHERE pk_USERNAME LIKE 'TEST%'</xsl:text>
				<xsl:text> AND 'pk_USERNAME=' || pk_USERNAME NOT IN (SELECT table_key FROM IDM.EVENTLOG WHERE statusnbsp = 'S')</xsl:text>
			</jdbc:sql>
		</jdbc:statement>
		<xsl:message>Create fake role events in eventlog</xsl:message>
		<jdbc:statement jdbc:op-id="0" jdbc:transaction-id="0" jdbc:transaction-type="manual" jdbc:type="update">
			<jdbc:sql>
				<xsl:text>INSERT INTO IDM.EVENTLOG</xsl:text>
				<xsl:text>(record_id, event_type, perpetrator, table_name, table_key)</xsl:text>
				<xsl:text>SELECT IDM.SEQ_RECID.nextval, 5, 'fake', 'VIEW_ORCLROLES', 'pk_ROLE=' || pk_ROLE FROM VIEW_ORCLROLES</xsl:text>
				<xsl:text> WHERE 'pk_ROLE=' || pk_ROLE NOT IN (SELECT table_key FROM IDM.EVENTLOG WHERE statusnbsp = 'S')</xsl:text>
			</jdbc:sql>
		</jdbc:statement>
		<xsl:message>Create fake priv events in eventlog</xsl:message>
		<jdbc:statement jdbc:op-id="0" jdbc:transaction-id="0" jdbc:transaction-type="manual" jdbc:type="update">
			<jdbc:sql>
				<xsl:text>INSERT INTO IDM.EVENTLOG</xsl:text>
				<xsl:text>(record_id, event_type, perpetrator, table_name, table_key)</xsl:text>
				<xsl:text>SELECT IDM.SEQ_RECID.nextval, 5, 'fake', 'VIEW_ORCLPRIVS', 'pk_GRANTEE=' || pk_GRANTEE || '+pk_GRANTED_ROLE=' || pk_GRANTED_ROLE FROM VIEW_ORCLPRIVS WHERE (pk_GRANTEE LIKE 'TEST%' OR pk_GRANTEE LIKE 'ROLE%')</xsl:text>
				<xsl:text> AND 'pk_GRANTEE=' || pk_GRANTEE || '+pk_GRANTED_ROLE=' || pk_GRANTED_ROLE NOT IN (SELECT table_key FROM IDM.EVENTLOG WHERE statusnbsp = 'S')</xsl:text>
			</jdbc:sql>
		</jdbc:statement>
	</xsl:template>
</xsl:stylesheet>

For those who can read SQL, the key here is to make sure not to stuff Eventog with records that have been successfully processed already, which would cause the driver to spin its wheels figuring out by itself that those records are redundant.

An important thing to keep in mind is that the Eventlog table can be purged anytime without causing data to be lost. What will happen after all entries are deleted is the same as for an initial data load - all records will be inserted in the Eventlog. The driver may be busy for a little while (e.g., 2 hours) rejecting redundant events, but will get back on its feet afterwards and resume business as usual. It might be a good idea to truncate the table once a month or so in order to clean things up, but that depends on your specifics.

I would appreciate feedback and suggestions, as well as questions, as usual - this is what I use to evolve my driver. Thanks!


Novell Cool Solutions (corporate web communities) are produced by WebWise Solutions. www.webwiseone.com

© 2014 Novell