To Configure JDBC Tables, Attributes, and Object Classes

When you configure a JDBC data view, you must also configure the following objects:

• JDBC object class. Maps one or more JDBC tables to an LDAP object class.

• JDBC table. Defined for each relational database table.

• JDBC attribute. Defines an LDAP attribute from a specified column in a JDBC table.

1. Create a JDBC table for each table in the relational database.

   % dpconf create-jdbc-table jdbc-table-name db-table

   The name of the db-table is case sensitive. Make sure that you use the identical case that is used in the relational database, otherwise operations that target that table might fail.

2. Create a JDBC attribute for each column in each relational database table.

   % dpconf add-jdbc-attr table-name attr-name sql-column

   Creating a JDBC attribute maps the table column to an LDAP attribute.

3. (Optional) If the column in the relational database is case sensitive, change the LDAP syntax of the JDBC attribute.

   % dpconf set-jdbc-attr-prop table-name attr-name ldap-syntax:ces

   The value of ldap-syntax is cis by default. This implies that the jdbc-attr is case insensitive. Change the value to ces if your relational database is case sensitive.

   Certain relational databases, such as Oracle and DB2, are case sensitive by default. LDAP is case insensitive by default. When Directory Proxy Server detects that a column of the relational database table is case sensitive, an ldapsearch query with the corresponding attribute in the filter is translated into a SQL query using the function UPPER.

   For example, the query ldapsearch -b "dc=mysuffix" "(attr=abc)" is translated into the following SQL query:

   SELECT * FROM mytable WHERE (UPPER(attr)='ABC')

   By default, this type of query is not indexed. Queries of this nature can therefore have a substantial performance impact.

   You can alleviate the performance impact in two ways:

   • By setting the ldap-syntax property of the jdbc-attr to ces.

   • By creating an index with the function UPPER for each jdbc-attr that might be used in an LDAP filter.

4. Create a JDBC object class for the LDAP relational database table.

   % dpconf create-jdbc-object-class view-name objectclass primary-table \ [secondary-table... ] DN-pattern

   Creating a JDBC object class essentially specifies an LDAP object class with which these tables will be associated. The JDBC object class also specifies the primary table and the secondary tables, if they exist.

   When you create a JDBC object class, you specify a DN pattern. The DN pattern shows how the DN of the entry will be constructed.

5. If a secondary table exists, define the join rule between the primary table and the secondary table.

   % dpconf set-jdbc-table-prop secondary-table-name filter-join-rule:join-rule

   A join rule is defined on the secondary table and determines how data from that table is linked to data from the primary table. How you define the relationships between the primary and secondary tables of an object class is important. For more information, see Defining Relationships Between JDBC Tables.

6. Specify the super class for the JDBC object class.

   % dpconf set-jdbc-object-class-prop view-name objectclass super-class:value

   The super class indicates the LDAP object class from which the JDBC object class inherits.