Security Directory Integrator – Custom SQL for JDBC Connectors
By Joshua Moore, PathMaker Group Consultant
Security Directory Integrator, formally known as Tivoli Directory Integrator, is a powerful tool that we often use to bulk load data into ISIM. Security Directory Integrator, otherwise known as SDI, has the capability of transforming data of one type to another. One of the challenges is querying specific data from a database source. For example, we often use a SDI to match existing system data to another source based on a User ID, Employee ID, or some type of unique identifier that is maintained in both systems.
For the purposes of this blog entry we will focus on the JDBC Connector provided with the standard SDI installation/configuration. As shown in “Figure 1” the connector properties are relatively standard. Providing the required connection parameters and connection to the database should be seamless.
Figure 1:
With connection properties configured link criteria can be provided to match input data, also known as “work,” and matched to data within the connected table. To provide the custom SQL to the database connection Lookup we will need to bypass the standard “Link Criteria” and feed in custom JavaScript back on the Connections tab as shown above. Leave “Link Criteria.”
Figure 2:
Context for this Example
In order to provide more context around this scenario, here is the background on the task at hand. A report (csv format) has been provided with a list of server names and supporting content. These server names have been abbreviated in ways to encompass more than one unique value, such as a wildcard character (i.e. myservername*001 or myservernamedev*.) The wildcards therefore denote only one instance in the report when realistically there could be multiple servers for all related supporting content of the report. For this scenario, the wildcard must be translated into a manner in which SQL can look up related server names and output the unique servers for each wildcard value.
To provide the JDBC connection with a custom SQL statement we need to tell SDI to use advanced JavaScript for the connection. On the connection tab for JDBC connector there is an “Advanced” option (Figure 3) below the standard connection criteria.
Figure 3:
Connection Tab – Advanced Options
In this Advanced section, confirm that “Use custom SQL prepared statements” check box is checked. This tells SDI to use custom JavaScript and bypass the Link Criteria. The next step is to provide the custom JavaScript. Click on “SQL Lookup.” It does not look like much of a link but it will launch a new window (Figure 4). In this new window, you will provide the JavaScript to create, format, and customize your SQL to be used as “Link Criteria” for the JDBC connector.
As for our scenario, we are querying server names that have asterisks (*) as wildcard characters to denote more than one unique server. If you are familiar with SQL syntax you know that these asterisks cannot be used in a SQL query as wildcard characters. As noted in Figure 4, the SQL must be returned in a “string value.”
Figure 4:
Custom SQL Statement for JDBC connector:
Conclusion
There are a variety of use cases for providing custom SQL to complete the JDBC Connectors connection criteria. This simple example, although not exhaustive, was chosen to demonstrate how to provide the connector with the appropriate custom SQL using JavaScript. There is always potential for more work around types of “Link Criteria” to provide, but hopefully this will get you started on the right path.
