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.

   ---

   Note –

   If your relational database is not case sensitive, use ldap-syntax with the default value, that is, cis. ldap-syntax:ces is not supported with the case insensitive databases.

   ---

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. DN pattern describes which attributes are to be used to construct DN of the entry. For example, when you specify DN pattern as uid then the DN of the entry is constructed using attribute uid and view base of the data view. For example, uid=bjensen,ou=people,dc=example,dc=com. The DN pattern can constitute multiple attributes. In that case, attributes should be separated by , (comma). For example, if DN pattern is specified as uid,country, DN of the entry returned by the data view is uid=bjensen,country=America,ou=people,dc=example,dc=com.

   When data source contains duplicate entries and the duplicate entries are not required, set perform-distinct-select to true using the following command:

   % dpconf set-jdbc-object-class-prop view-name objectclass perform-distinct-select:true

   All the subtree components defined in the DN pattern of JDBC object class should have a JDBC object class defined for them. For example, if there is a DN pattern uid,ou in a JDBC object class, there should be a JDBC object class definition with a DN pattern ou. This is necessary for Directory Proxy Server to construct a properly structured DIT. Otherwise, the subtree with values like ou=xxx,base-DN would not be returned in the search results.

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.