A Oracle Data Integrator Driver for LDAP Reference

This appendix describes how to work with the Oracle Data Integrator driver for LDAP.

This appendix includes the following sections:

A.1 Introduction to Oracle Data Integrator Driver for LDAP

With Oracle Data Integrator Driver for LDAP (LDAP driver) Oracle Data Integrator is able to manipulate complex LDAP trees using standard SQL queries.

The LDAP driver supports:

  • Manipulation of LDAP entries, their object classes and attributes

  • Standard SQL (Structured Query Language) Syntax

  • Correlated subqueries, inner and outer joins

  • ORDER BY and GROUP BY

  • COUNT, SUM, MIN, MAX, AVG and other functions

  • All Standard SQL functions

  • Referential Integrity (foreign keys)

  • Persisting modifications into directories

A.2 LDAP Processing Overview

The LDAP driver works in the following way:

  1. The driver loads (upon connection) the LDAP structure and data into a relational schema, using a LDAP to Relational Mapping.

  2. The user works on the relational schema, manipulating data through regular SQL statements. Any changes performed in the relational schema data (insert/update) are immediately impacted by the driver in the LDAP data.

A.2.1 LDAP to Relational Mapping

The LDAP to Relational Mapping is a complex but automated process that is used to generate a relational structure. As LDAP servers do not provide metadata information in a standard way, this mapping is performed using data introspection from the LDAP tree. Therefore, automatic mapping is carried out on the contents of the LDAP tree used as a source for this process.

This section contains the following topics:

A.2.1.1 General Principle

The LDAP driver maps LDAP elements to a relational schema in the following way:

  • Each LDAP class or combination of classes is mapped to a table. Each entry from the LDAP tree is mapped to a record in the table.

  • Each attribute of the class instances is mapped to a column.

  • Hierarchical relationships between entries are mapped using foreign keys. A table representing a hierarchical level is created with a primary key called <tablename>PK. Records reference their parent tables through a <parent_level_tablename>FK column. The root of the LDAP tree structure is mapped to a table called ROOT containing a ROOTPK column in a unique record.

  • Attributes with multiple values for an entry (for example, a Person entry with several email attributes) are mapped as sub-tables called <parent_tablename><attribute_name>. Each sub-table contains a <parent_tablename>FK column linking it to the parent table.

Figure A-1 shows an LDAP tree with OrganizationalUnit entries linking to Person instances. In this case, certain Person entries have multiple email addresses.

Figure A-1 LDAP Tree Example

Description of Figure A-1 follows
Description of "Figure A-1 LDAP Tree Example"

This LDAP tree will be mapped into the following relational structure:

  • The ROOT table represents the root of the hierarchy and contains one ROOTPK column.

  • The ORGANIZATIONALUNIT table represents different organizationalUnit instances of the tree. It contains the ORGANIZATIONALUNITPK primary key column and the attributes of the organizationalUnit instances (cn, telephoneNumber, etc.). It is linked to the ROOT table by the ROOTFK foreign key column.

  • The PERSON table represents the instances of the person class. It contains the PERSONPK primary key column and the ORGANIZATIONALUNITFK linking it to the ORGANIZATIONALUNIT table and the attributes of PERSON instances, (telephoneNumber, description, cn).

  • The email attribute appears as a PERSON_EMAIL table containing the EMAIL column and a PERSONFK linking a list of email attributes to a PERSON record.

Figure A-2 shows the resulting relational structure.

Figure A-2 Relational Structure mapped from the LDAP Tree Example shown in Figure A-1

Description of Figure A-2 follows
Description of "Figure A-2 Relational Structure mapped from the LDAP Tree Example shown in Figure A-1"

A.2.1.2 Grouping Factor

In LDAP directories, class entries are often specified by inheriting attributes from multiple class definitions. In the relational mapping procedure, the LDAP driver translates this fact by combining each combination of classes in an LDAP entry to generate a new table.

For example, some entries of the Person class may also be instances of either of the Manager or BoardMember classes (or both). In this case, the mapping procedure would generate a PERSON table (for the instances of Person) but also MANAGER_PERSON, BOARDMEMBER_PERSON, BOARDMEMBER_MANAGER_PERSON and so forth, tables depending on the combination of classes existing in the LDAP tree.

In order to avoid unnecessary multiplication of generated tables, it is possible to parameterize this behavior. The Grouping Factor parameter allows this by defining the number of divergent classes below which the instances remain grouped together in the same table. This resulting table contains flag columns named IS_<classname>, whose values determine the class subset to which the instance belongs. For example, if IS_<classname> is set to 1, then the instance represented by the record belongs to <classname>.

The behavior where one table is created for each combination of classes corresponds to a Grouping Factor equal to zero. With a grouping factor equal to one, instances with only one divergent class remain in the same table.

In our example, with a Grouping Factor higher than or equal to 2, all company person instances (including Person, Manager and BoardMember class instances) are grouped in the PERSON table. The IS_MANAGER and IS_BOARDMEMBER columns enable the determination of PERSON records that are also in the Manager and/or BoardMember classes.

A.2.1.3 Mapping Exceptions

This section details some specific situations of the mapping process.

  • Table name length limits and collisions: In certain cases, name-length restrictions may result in possible object name collisions. The LDAP driver avoids such situations by automatically generating 3 digit suffixes to the object name.

  • Key column: It is possible to have the driver automatically create an additional SNPSLDAPKEY column containing the Relative Distinguished Name (RDN) that can be used as identifier for the current record (original LDAP class instance). This is done by setting the key_column URL property to true. This SNPSLDAPKEY column must be loaded if performing DML commands that update the LDAP tree contents. Note that this column is created only in tables that originate from LDAP instances. Tables that correspond to multiple valued instance attributes will not be created with these columns.

  • Case sensitivity: This is set by the case_sens URL property that makes the RDBMS and LDAP servers to enforce case-sensitivity.

  • Special characters: It is possible in LDAP to have non-alphanumeric characters into attribute or class names. These characters are converted to underscores ("_") during the mapping. Exception: If non alphanumeric, the first character is converted to "x".

  • SQL Reversed Keywords: Generated tables and columns with names that match SQL keywords are automatically renamed (an underscore is added after their name) in the relational structure to avoid naming conflicts between table/column names and SQL keywords. For example, a class named SELECT will be mapped to a table named SELECT_.

A.2.1.4 Reference LDAP Tree

As LDAP servers do not provide metadata information in a standard way, the LDAP to Relational Mapping process is performed by default using data introspection from the LDAP tree.

With the LDAP driver it is also possible to use a Reference LDAP Tree for the LDAP to Relational Mapping process instead of using the LDAP tree that contains the actual data.

This Reference LDAP Tree is configured using the lm_props property of the driver URL. This property specifies a.properties file that contains the connection information to a LDAP tree whose hierarchical structure rigorously reflects that of the operational LDAP tree but without the accompanying data volume.

This technique reveals certain advantages:

  • The Reference LDAP Tree can be maintained by the directory administrator as a stable definition of the operational LDAP tree.

  • The Reference LDAP Tree contains few instances that make up the skeleton of the real LDAP tree, and the LDAP to Relational Mapping process runs faster on this small reference tree. This is particularly important for large operational LDAP directories, and will result in reduced processing time and resources for running the procedure.

The use of this technique, however, imposes a certain number of constraints in the design of the precise structure of the Reference LDAP Tree:

  • All optional LDAP instance attributes must be instantiated in the reference entries. Even if these attributes are absent in the operational LDAP directory entries, they must be declared in the Reference LDAP Tree if they are to be used at a later time.

  • Any multiple valued attributes that exist in the operational LDAP directory must be instantiated as such in the Reference LDAP Tree. For example, if any Person instance in the operational LDAP directory possesses two telephoneNumber attributes, then the generic Person class must instantiate at least two telephoneNumber attributes in the Reference LDAP Tree.

Note:

These issues have a direct impact on the generated relational structure by forcing the creation of additional tables and columns to map multiple attribute fields and must be taken into consideration when designing the Reference LDAP Tree.

A.2.2 Managing Relational Schemas

This section contains the following topics:

A.2.2.1 Relational Schema Storage

The relational structure resulting from the LDAP to Relational mapping may be managed by virtual mapping or stored in an external database.

The virtual mapping stores the relational structure in the run-time agent's memory and requires no other component. The relational structure is transparently mapped by the driver to the LDAP tree structure. SQL commands and functions that are available for the LDAP driver are listed in the SQL Syntax.

Note:

The virtual mapping may require a large amount of memory for large LDAP tree structures.

The external database may be any relational database management system. The driver connects through JDBC to this engine and uses it to store the relational schema. This method provides the following benefits:

  • Processing and storage capabilities of the selected external database engine.

  • Acccess to the specific SQL statements, procedures, and functions of the external database engine.

  • Flexible persistence of the relational structure. This schema content may persist after the connection to the LDAP driver is closed.

See Section A.3.2, "Using an External Database to Store the Data" for more information on how to set up external storage.

A.2.2.2 Accessing Data in the Relational Structure

DML operations on tables in the relational are executed with standard SQL statements.

Modifications made to the relational data are propagated to the directory depending on the selected storage :

  • In the case where the virtual mapping is used, all insert, update, and delete requests are automatically propagated to the original LDAP server in an autocommit mode. No explicit COMMIT or ROLLBACK statements will have any impact on the Oracle Data Integrator driver for LDAP.

  • In the case where the external database is used to store the relational structure, all types of DML statements may be used with the driver. However, it is important to know that no modifications will be propagated to the original LDAP server.

A.3 Installation and Configuration

The Oracle Data Integrator driver for LDAP is automatically installed during the Oracle Data Integrator installation. The following topics cover advanced configuration topics and reference information.

This section contains the following topics:

Note:

You must add the libraries and drivers required to connect the LDAP directory using JNDI to the Oracle Data Integrator classpath.

Note:

If using an external database engine you must also make sure that the JDBC driver used to connect to the external database and the .properties file are in the classpath.

A.3.1 Driver Configuration

This section details the driver configuration.

  • The driver name is: com.sunopsis.ldap.jdbc.driver.SnpsLdapDriver

  • The driver supports two URL formats:

    • jdbc:snps:ldap?<property=value>[&...]

    • jdbc:snps:ldap2?<property=value>[&...]

    The first URL requires the LDAP directory password to be encoded. The second URL allows you to give the LDAP directory password without encoding it.

    Note:

    It is recommended to use the first URL to secure the LDAP directory password.

    The LDAP driver uses different properties depending on the established connection. Figure A-3 shows when to use which properties.

    Figure A-3 Properties Files for LDAP Driver

    Description of Figure A-3 follows
    Description of "Figure A-3 Properties Files for LDAP Driver"

    The LDAP driver connects to the LDAP directory. You can configure this connection with the properties that start with ldap_. For example, ldap_basedn. Instead of passing the LDAP directory properties in the driver URL, you can use a properties file for the configuration of the connection to the LDAP directory. This properties file must be specified in the ldap_props property of the driver URL.

    If you want to use the hierarchical structure of the LDAP tree without the accompanying data volume, you can use the Reference LDAP tree. The connection to the Reference LDAP tree is configured with the properties that start with lm_. For example, lm_basedn. Instead of passing the lm_ properties in the driver URL, you can use a properties file. This properties file must be specified in the lm_props property of the driver URL. See Section A.2.1.4, "Reference LDAP Tree" for more information.

    To configure the connection of the LDAP driver to an external database, use the properties that start with db_. For example, db_url. Instead of passing the external database properties in the driver URL, you can use a properties file for the configuration of the connection to the external database. This properties file must be specified in the db_props property of the driver URL. See Section A.3.2, "Using an External Database to Store the Data" for more information.

Table A-1 describes the properties that can be passed in the driver URL.

Table A-1 Driver Properties

Property Mandatory Type Default Description

db_props or dp

No

string (file location)

Empty string

Name of a .properties file containing the external database connection configuration. See Section A.3.2, "Using an External Database to Store the Data" for the details of this file content.

Note: This property should contain the name of the .properties file without the file extension.

Note: This .properties file must be in the run-time agent classpath.

Note: You can specify the external database connection configuration using all the db_ properties listed below in this table.

ldap_props or lp

No

string (file location)

N/A

Name of a .properties file containing the directory connection configuration. See Section A.3.3, "LDAP Directory Connection Configuration" for the details of this file content.

Note: This property should contain the name of the .properties file without the file extension.

Note: This .properties file must be in the run-time agent classpath.

Note: You can specify the LDAP directory connection configuration using all the ldap_ properties listed below in this table.

lm_props or lm

No

string (file location)

N/A

Name of a .properties file containing the directory connection configuration for the Reference LDAP Tree. See Section A.3.3, "LDAP Directory Connection Configuration" for the details of this file content, and Section A.2.1.4, "Reference LDAP Tree" for an explanation of the reference tree.

Note: This property should contain the name of the .properties file without the file extension.

Note: This .properties file must be in the run-time agent classpath.

Note: You can specify the reference LDAP directory connection configuration using all the lm_ properties listed below in this table.

case_sens or cs

No

boolean (true | false)

false

Enable / disable case sensitive mode for both LDAP- and RDBMS-managed objects.

alias_bundle or ab

No

string (file location)

Empty string

Full name of a properties file including both the absolute path to the properties file and the file extension. The properties file is a file that contains the list of aliases for the LDAP to Relational Mapping. If this file does not exist, it will be created by the driver. See Section A.3.4, "Table Aliases Configuration" for more information.

Note: The file extension does not need to be .properties.

alias_bundle_encoding or abe

No

string (encoding code)

Default encoding

Alias bundle file encoding. This encoding is used while reading and overwriting the alias_bundle file. If it is not defined then the default encoding would be used.

You will find a list of supported encoding at the following URL: http://java.sun.com/j2se/1.3/docs/guide/intl/encoding.doc.html.

grouping_factor or gf

No

integer

2

Determines how many object classes will be grouped together to set up a single relational table mapping. See Section A.2.1.2, "Grouping Factor" for more information.

key_column or kc

No

boolean (true | false)

false

If set to true, a technical column called SNPSLDAPKEY is created to store the Relative Distinguished Name (RDN) for each LDAP entry. See Section A.2.1.3, "Mapping Exceptions" for more information.

numeric_ids or ni

No

boolean (true | false)

false

If set to true, all internal Primary and Foreign Keys are of NUMERIC type. Otherwise, they are of the VARCHAR type.

id_length or il

No

integer

10 / 30

The length of the internal Primary and Foreign Key columns. The default is 10 for NUMERIC column types and 30 for VARCHAR column types.

table_prefix or tp

No

string

N/A

Prefix added to relational tables of the current connection.

log_file or lf

No

string (file location)

N/A

Trace log file name. If the log file name is not set the trace data is displayed on the standard output.

The presence of this property triggers trace logging for a particular relational schema.

log_level or ll

No

integer

1

Log level. This property is ignored if log_file is not specified. The log level can is a bit mask that can be specified either in hexadecimal or decimal value.

Log Level Values:

  • 0x1 (1): General information (important)

  • 0x2 (2): General information (detailed)

  • 0x4 (4): SQL statements

  • 0x8 (8): LDAP-Relational mapping information

  • 0x10 (16): LDAP-Relational mapping validation & renaming information (Table and columns name modifications, etc)

  • 0x20 (32): Display the LDAP model parsed and the corresponding relational model.

  • 0x40 (64): Display the table creation statements.

  • 0x80 (128): Display data insert statements.

  • 0x100 (256): Grouping information (important)

  • 0x200 (512): Grouping information (detailed)

  • 0x400 (1024): Display details on the relational model building

  • 0x800 (2048): Display the elements read from the LDAP tree

  • 0x1000 (4096): Display SQL statements causing changes into the LDAP tree

Examples:

  • Important and detailed general information: log_level=3 (1+2)

  • Trace native SQL commands and important internal events: log_level=5 (1+4)

  • Trace relational mapping calculation and validation: log_level=24 (16+8)

  • Trace all events: log_level=8191 (1+2+ ... + 2048 + 4096)

ldap_auth

No

string

simple

LDAP Directory authentication method. See the auth property in Section A.3.3, "LDAP Directory Connection Configuration".

ldap_url

Yes

string

N/A

LDAP Directory URL. See the url property in Section A.3.3, "LDAP Directory Connection Configuration".

ldap_user

No

string

Empty string

LDAP Directory user name. See the user property in Section A.3.3, "LDAP Directory Connection Configuration".

ldap_password

No

string

Empty string

LDAP Directory user password. See the password property in Section A.3.3, "LDAP Directory Connection Configuration".

ldap_basedn

No

string

N/A

LDAP Directory basedn. See the basedn property in Section A.3.3, "LDAP Directory Connection Configuration".

lm_auth

No

string

simple

Reference LDAP authentication method. See the auth property in Section A.3.3, "LDAP Directory Connection Configuration".

lm_url

Yes

string

N/A

Reference LDAP URL. See the url property in Section A.3.3, "LDAP Directory Connection Configuration".

lm_user

No

string

Empty string

Reference LDAP Directory user name. See the user property in Section A.3.3, "LDAP Directory Connection Configuration".

lm_password

No

string

Empty string

Reference LDAP Directory user password. See the password property in Section A.3.3, "LDAP Directory Connection Configuration".

lm_basedn

No

string

N/A

Reference LDAP Directory basedn. See the basedn property in Section A.3.3, "LDAP Directory Connection Configuration".

db_driver

Yes

string

N/A

External Database JDBC Driver. See the driver property in Section A.3.2, "Using an External Database to Store the Data".

db_url

Yes

string

N/A

External Database JDBC URL. See the url property in Section A.3.2, "Using an External Database to Store the Data".

db_user

No

string

Empty string

External Database user. See the user property in Section A.3.2, "Using an External Database to Store the Data".

db_password

No

string

Empty string

External Database password. See the password property in Section A.3.2, "Using an External Database to Store the Data".

db_schema

No

string

Empty string

External Database schema. See the schema property in Section A.3.2, "Using an External Database to Store the Data".

db_catalog

No

string

Empty string

External Database catalog. See the catalog property in Section A.3.2, "Using an External Database to Store the Data".

db_drop_on_disconnect or db_dod

No

boolean (true|false)

true

Drop tables on disconnect on the external database. See the drop_on_disconnect property in Section A.3.2, "Using an External Database to Store the Data".

db_load_mode or db_lm

No

string

ci

Loading method for the external database. See the load_mode property in Section A.3.2, "Using an External Database to Store the Data".

tna

No

boolean (true|false)

true

Transform Non Ascii. Set to false to keep non-ascii characters.


URL Examples

The following section lists URL examples:

  • jdbc:snps:ldap?lp=ldap_mir&ldap_basedn=o=tests&gf=10&lf=

    Connects to the LDAP directory specified in the ldap_mir .properties file, overriding the basedn property of the ldap bundle and using a grouping factor of 10. General information (important) is sent to the standard output.

  • jdbc:snps:ldap?lp=ldap_ours&lm=generic&ab=c:/tmp/aliases.txt&gf=10&kc=true

    Connects to the LDAP directory using the ldap_ours .properties file; a generic Directory tree for relational model creation is signaled by the lm property; an alias bundle file is used for the creation of the relational structure; a maximum grouping factor of 10 is used; key column creation is enabled for the SNPSLDAPKEY field to allow updates requests in the relational model.

  • jdbc:snps:ldap?lp=ldap_mir&dp=mysql_mir_ldap&ldap_basedn=dc=tests&lm=ldap_mir&lm_basedn=dc=model&ab=d:/temp/mapldap.txt&

    Connects to the LDAP directory using the ldap_mir .properties file; overriding ldap basedn property; using the "dc=model" subtree of the same directory to perform mapping; using an alias bundle; overriding the lm database property (load mode); specifying a grouping factor of 0 to indicate no grouping (grouping disabled); Full trace logging is activated.

  • Connects to a LDAP directory on the hydraroid machine. The LDAP server connection information - url, base dn, user and password - is specified in the URL using the ldap_xxx properties.

    jdbc:snps:ldap?ldap_url=ldap://hydraroid:389/dc=localhost,dc=localdomain&ldap_password=KPLEKFMJKCLFJMDFDDGPGPDB&ldap_user=cn=orcladmin&ldap_basedn=ou=applications
    

A.3.2 Using an External Database to Store the Data

The relational structure resulting from the LDAP to relational mapping of the LDAP tree can be stored in the run-time agent's memory or in an external database.

Note:

The list of technologies that support external storage is available on Oracle Technical Network (OTN) :

http://www.oracle.com/technology/software/products/ias/files/fusion_certification.html

The external storage is configured with a set of properties described in Table A-2.

The external storage properties can be passed in several ways:

A.3.2.1 Passing the Properties in the Driver URL

The properties can be directly set in the driver URL. When using this method, the properties have to be prefixed with db_ . For example, if connecting to an Oracle database, specify the Oracle JDBC driver name in the driver parameter as follows:

db_driver=oracle.jdbc.driver.OracleDriver.

A.3.2.2 Setting the Properties in ODI Studio

The properties can be specified on the Properties tab of the Data Server editor in Topology Navigator. When using this method, the properties have to be prefixed with db_. For example, if you want to set the driver parameter:

  1. In the Key column, enter db_driver

  2. In the Value column, enter oracle.jdbc.driver.OracleDriver if you are connecting to an Oracle database.

A.3.2.3 Setting the Properties in a Properties File

The properties can be set in an external database properties file. This properties file, also called property bundle, is a text file with the .properties extension containing a set of lines with on each line a <property>=<value> pair.This external database porperties file contains the properties of a JDBC connection to the relational database schema. The properties file is referenced using the db_props property in the JDBC URL.

Note:

It is important to understand that the LDAP driver loads external property bundle files once only at runtime startup. If errors occur in these files, it is advisable to exit Oracle Data Integrator and then reload it before re-testing.

When using this method, note the following:

  • The properties in the properties file are not prefixed and used as described in Table A-2.

  • The db_props property is set to the name of the properties file without the .properties extension. For example, if you have in your classpath the prod_directory.properties file, you should refer to this file as follows: db_props=prod_directory.

    The db_props property indicates that the schema must be loaded in a database schema whose connection information is stored in a external database properties file.

  • The properties files have to be deployed by the agent using the LDAP connection. The location the properties file depends on the agent you are using:

  • When using property bundle files, you must make sure that the property bundle is present in the Oracle Data Integrator classpath. Typically, you should install this bundle in the drivers directories.

Note:

When connecting to the external database, the LDAP driver uses JDBC connectivity. Make sure that the JDBC driver to access this external database is also available in the ODI classpath.

It is possible to set or override the external database properties on the URL. These properties must be prefixed with the string db_. For example:

jdbc:snps:ldap?ldap_url=ldap://localhost:389/&ldap_basedn=o=company&db_driver=oracle.jdbc.driver.OracleDriver&db_url=<external_db_url>

The properties for configuring external storage are described inTable A-2.

Table A-2 External Database Connection Properties

Property Mandatory Type Default Description

driver

Yes

string

N/A

JDBC driver name

url

Yes

string

N/A

JDBC URL

user

No

string

Empty string

Login used to connect the database

password

No

string

Empty string

Encrypted database user password.

Note: To encrypt the password, use the encode.bat command. See the Installing and Configuring Oracle Data Integrator for more information.

schema

No

string

Empty string

Database schema storing the LDAP Tree. This property should not be used for Microsoft SQLServer, and the catalog property should be used instead.

catalog

No

string

Empty string

Database catalog storing the LDAP Tree. For Microsoft SQL Server only. This property should not be used simultaneously with the schema property.

drop_on_disconnect or dod

No

boolean (true | false)

true

If true, drop the tables from the database at disconnection time. If set to false the tables are preserved in the database.

load_mode or lm

No

string

ci

The loading method. Values may be:

  • n (none): the model and table mappings are created in memory only.

  • dci (drop_create_insert): drop all tables that may cause name conflicts then create tables and load the LDAP tree into the relational model.

  • ci(create_insert): Create the relational tables and throw an exception for existing tables, then load the LDAP tree into the relational model.


The following is an example of an external database .properties file to connect to an external Oracle database:

driver=oracle.jdbc.OracleDriver
url=jdbc:oracle:thin:@hydraro:1521:SNPTST1
user=LDAP_T_1
password=ENCODED_PASSWORD
schema=LDAP_T_1

A.3.3 LDAP Directory Connection Configuration

The Oracle Data Integrator driver for LDAP uses the properties described in Table A-3 to connect to a directory server that contains the LDAP data or the Reference LDAP Tree. These properties can be provided either in a property bundle file or on the driver URL.

The properties for configuring a directory connection are detailed in Table A-3.

Table A-3 LDAP Directory Connection Properties

Property Mandatory Type Default Description

auth

No

string

simple

The authentication method

url

Yes

string

N/A

URL to connect to the directory. It is an LDAP URL.

Note: This driver supports the LDAPS (LDAP over SSL) protocol. The LDAPS URL must start with ldaps://. To connect a server using LDAPS, you must manually install the certificate in the java machine. See the keytool program provided with the JVM for more information.

user

No

string

Empty string

The LDAP server user-login name. Mandatory only if "auth" is set.

Note: If user and password properties are provided to create the connection with the JDBC Driver for LDAP, then they are used to connect the LDAP directory.

password

No

string

Empty string

LDAP server user-login password. Mandatory only if "auth" is set.

Note: The password needs to be encrypted, unless the 'jdbc:snps:ldap2' URL syntax.

Note: To encrypt the password, use the encode.bat command. See the Installing and Configuring Oracle Data Integrator for more information.

basedn

No

string

N/A

The base dn with which you wish to connect to the LDAP tree. The base dn is the top level of the LDAP directory tree. If it not specified, the base dn specified in the LDAP URL is used.


The following is an example of an LDAP properties file content:

url=ldap://ours:389
user=cn=Directory Manager
password=ENCODED_PASSWORD
basedn=dc=oracle,dc=com

A.3.4 Table Aliases Configuration

The LDAP driver allows a certain flexibility in the definition of the model table names in Oracle Data Integrator by the use of table aliases. This is particularly useful when the algorithm used to navigate the LDAP tree generates long composite names from the LDAP object class hierarchy. To avoid issues related to RDBMS-specific object name-length constraints, the LDAP driver can set up and use aliases.

Note:

It is also possible to change the default "Maximum Table Name Length" and "Maximum Column Name Length" values on the Others tab of the Technology Editor in the Physical Architecture accordion.

To create a table alias file:

  1. In the LDAP Driver Data Server URL, include and set the alias_bundle (ab) property that indicates the name of the alias text file, for example:

    jdbc:snps:ldap?.....&ab=C:/tmp/aliases.txt&....

    The alias file is created by the driver at connection time when the alias_bundle property is specified. Typically, a user connects initially through the LDAP driver which creates this file containing a list of potential table names to be created by the reverse-engineering operation.

  2. Test the connection to the LDAP data server.

  3. Verify the that the text file has been created and has the expected structure. The list consists of <original table name > = <desired alias name> values. Example A-1 shows an extract of an alias file after the user has provided shortened names. See step 4 for more information.

    Example A-1 Alias File

    INETORGPERSON_ORGANIZATIONALPERSON_PERSON_BISOBJECT_MAIL = PERSONMAIL
    ORGANIZATIONALUNIT_RFC822MAILMEMBER = ORG_228MAIL
    INETORGPERSON_ORGANIZATIONALPERSON_PERSON = ORG_PERSON
    ORGANIZATIONALUNIT_MEMBER = ORG_UN_MEMBER
    ORGANIZATIONALUNIT = ORG_UNIT
    ROOT = ROOT
    ....
     
    
  4. In the alias text file, add short text value aliases to replace the originally derived composite names and save the file.

  5. Reconnect to the same LDAP data server. The relational schema is created and this time the aliases will be used for defining relational table names.

  6. Now reverse-engineer the LDAP directory as described in Section 17.5.2, "Reverse-Engineering an LDAP Model". Oracle Data Integrator will create datastores with the table names defined as aliases in the alias file.

Note:

If any modifications have been applied to the object class structure or attribute sets of the LDAP directory, the driver will rewrite this file while including the new or modified entries to the table name list.

A.4 SQL Syntax

The SQL statements described in Section A.4.1, "SQL Statements" are available when using the Oracle Data Integrator driver for LDAP. They enable the management of relational data structure and data through standard SQL Syntax.

Note:

  • If you are using an external database you may use its proprietary query engine syntax in place of the following commands.

  • The LDAP driver works uniquely in auto commit mode. No explicit transaction management with COMMIT or ROLLBACK commands is permitted.

  • When using an external database to store LDAP tree data, DDL statements may only be carried out on temporary tables.

Table A-4 summarizes the recommendations to apply when performing the listed DML operations on specific key fields.

Table A-4 DML Opertaions on Key Fields

Type of Column Insert Update Delete

Foreign Key

Pay attention to master table referential constraints and ordered table populate operations.

Not permitted

Pay attention to master table referential constraints and ordered delete requests.

Primary Key

Pay attention to slave table referential constraints and ordered table populate operations.

Not permitted

Pay attention to slave table referential constraints and ordered delete requests

IS_xxx

Pay attention to associating the correct flag value to the original object class.

Not permitted

OK

Key_Column

Pay attention to setting the RDN value in the correct LDAP syntax.

Not permitted

OK


A.4.1 SQL Statements

Any number of commands may be combined. The semicolon (;) may be used to separate each command but is not necessary.

A.4.1.1 DISCONNECT

DISCONNECT

Closes this connection.

Remarks

  • It is not required to call this command when using the JDBC interface: it is called automatically when the connection is closed.

  • After disconnecting, it is not possible to execute other queries with this connection.

A.4.1.2 INSERT INTO

Insert one or more new rows of data into a table.

INSERT INTO <table_name> [ ( <column_name> [,...] ) ] 
      { VALUES (<expression> [,...]) | <SELECT Statement> }

A.4.1.3 SELECT

Retrieves information from one or more tables in the schema.

SELECT [DISTINCT] { <select_expression> | <table_name>.* | * } [, ... ]
    [ INTO <new_table> ]
      FROM <table_list>
    [ WHERE <expression> ]
    [ GROUP BY <expression> [, ...] ]
    [ ORDER BY <order_expression> [, ...] ]
    [ { UNION [ALL] | {MINUS|EXCEPT} | INTERSECT } <select_statement>
 ]
<table_list> ::=
<table_name> [ { INNER | LEFT [OUTER] } JOIN <table_name> ON <expression> ] 
   [, ...]
<select_expression> ::=
{ <expression> | COUNT(*) | {COUNT | MIN | MAX | SUM | AVG} 
  (<expression>) <column_alias>}
<order_expression> ::=
{ <column_number> | <column_alias> | <select_expression> } [ ASC | DESC ]

A.4.1.4 UPDATE

Modifies data of a table in the database.

UPDATE table SET column = <expression> [, ...] [WHERE <expression>]

A.4.1.5 Expressions, Condition & values

<expression> ::=
[NOT] <condition> [ { OR | AND } <condition> 
]
<condition> ::=
{ <value> [ || <value> ]
| <value> { = | < | <= | > | >= | <> | != | IS [NOT] } <value>
| EXISTS(<select_statement>)
| <value> BETWEEN <value> AND <value>
| <value> [NOT] IN ( {<value> [, ...] | selectStatement } )
| <value> [NOT] LIKE <value> [ESCAPE] value }
<value> ::=
[ + | - ] { term [ { + | - | * | / } term ]
| ( condition )
| function ( [parameter] [,...] )
| selectStatement giving one value
<term> ::=
{ 'string' | number | floatingpoint | [table.]column | TRUE | FALSE | NULL }
<string> ::=
  • Starts and ends with a single '. In a string started with ' use '' to create a '.

  • LIKE uses '%' to match any (including 0) number of characters, and '_' to match exactly one character. To search for '%' itself, '\%' must be used, for '_' use '\_'; or any other escaping character may be set using the ESCAPE clause.

<name> ::=
  • A name starts with a letter and is followed by any number of letters or digits. Lowercase is changed to uppercase except for strings and quoted identifiers. Names are not case-sensitive.

  • Quoted identifiers can be used as names (for example for tables or columns). Quoted identifiers start and end with ". In a quoted identifier use "" to create a ". With quoted identifiers it is possible to create mixed case table and column names. Example: CREATE TABLE "Address" ("Nr" INTEGER,"Name" VARCHAR); SELECT * FROM "Address". Quoted identifiers are not strings.

<values> ::=
  • A 'date' value starts and ends with ', the format is yyyy-mm-dd (see java.sql.Date).

  • A 'time' value starts and ends with ', the format is hh:mm:ss (see java.sql.Time).

  • Binary data starts and ends with ', the format is hexadecimal. '0004ff' for example is 3 bytes, first 0, second 4 and last 255 (0xff).

A.4.2 SQL FUNCTIONS

Table A-5 describes the numeric functions.

Table A-5 Numeric Functions

Function Description

ABS(d)

returns the absolute value of a double value

ACOS(d)

returns the arc cosine of an angle

ASIN(d)

returns the arc sine of an angle

ATAN(d)

returns the arc tangent of an angle

ATAN2(a,b)

returns the tangent of a/b

BITAND(a,b)

returns a & b

BITOR(a,b)

returns a | b

CEILING(d)

returns the smallest integer that is not less than d

COS(d)

returns the cosine of an angle

COT(d)

returns the cotangent of an angle

DEGREES(d)

converts radians to degrees

EXP(d)

returns e (2.718...) raised to the power of d

FLOOR(d)

returns the largest integer that is not greater than d

LOG(d)

returns the natural logarithm (base e)

LOG10(d)

returns the logarithm (base 10)

MOD(a,b)

returns a modulo b

PI()

returns pi (3.1415...)

POWER(a,b)

returns a raised to the power of b

RADIANS(d)

converts degrees to radians

RAND()

returns a random number x bigger or equal to 0.0 and smaller than 1.0

ROUND(a,b)

rounds a to b digits after the decimal point

SIGN(d)

returns -1 if d is smaller than 0, 0 if d==0 and 1 if d is bigger than 0

SIN(d)

returns the sine of an angle

SQRT(d)

returns the square root

TAN(d)

returns the trigonometric tangent of an angle

TRUNCATE(a,b)

truncates a to b digits after the decimal point


Table A-6 describes the string functions.

Table A-6 String Functions

Function Description

ASCII(s)

returns the ASCII code of the leftmost character of s

BIT_LENGTH(s)

returns the string length in bits

CHAR(c)

returns a character that has the ASCII code c

CHAR_LENGTH(s)

returns the string length in characters

CONCAT(str1,str2)

returns str1 + str2

DIFFERENCE(s1,s2)

returns the difference between the sound of s1 and s2

HEXTORAW(s1)

returns the string translated from hexadecimal to raw

INSERT(s,start,len,s2)

returns a string where len number of characters beginning at start has been replaced by s2

LCASE(s)

converts s to lower case

LEFT(s,count)

returns the leftmost count of characters of s

LENGTH(s)

returns the number of characters in s

LOCATE(search,s,[start])

returns the first index (1=left, 0=not found) where search is found in s, starting at start

LTRIM(s)

removes all leading blanks in s

OCTET_LENGTH(s)

returns the string length in bytes

RAWTOHEX(s)

returns translated string

REPEAT(s,count)

returns s repeated count times

REPLACE(s,replace,s2)

replaces all occurrences of replace in s with s2

RIGHT(s,count)

returns the rightmost count of characters of s

RTRIM(s)

removes all trailing blanks

SOUNDEX(s)

returns a four character code representing the sound of s

SPACE(count)

returns a string consisting of count spaces

SUBSTR(s,start[,len])

(alias for substring)

SUBSTRING(s,start[,len])

returns the substring starting at start (1=left) with length len.

Another syntax is SUBSTRING(s FROM start [FOR len])

TRIM

TRIM([{LEADING | TRAILING | BOTH}] FROM s): removes trailing and/or leading spaces from s.

UCASE(s)

converts s to upper case

LOWER(s)

converts s to lower case

UPPER(s)

converts s to upper case


Table A-7 describes the date and time functions.

Table A-7 Date and Time Functions

Function Description

CURDATE()

returns the current date

CURTIME()

returns the current time

CURRENT_DATE

returns the current date

CURRENT_TIME

returns the current time

CURRENT_TIMESTAMP

returns the current timestamp

DATEDIFF(s, d1,d2)

returns the counts of unit of times specified in s elapsed from datetime d1 to datetime d2. s may take the following values: 'ms'='millisecond', 'ss'='second','mi'='minute','hh'='hour', 'dd'='day', 'mm'='month', 'yy' = 'year'.

DAYNAME(date)

returns the name of the day

DAYOFMONTH(date)

returns the day of the month (1-31)

DAYOFWEEK(date)

returns the day of the week (1 means Sunday)

DAYOFYEAR(date)

returns the day of the year (1-366)

EXTRACT

EXTRACT ({YEAR | MONTH | DAY | HOUR | MINUTE | SECOND} FROM <datetime>): extracts the appropriate part from the <datetime> value.

HOUR(time)

return the hour (0-23)

MINUTE(time)

returns the minute (0-59)

MONTH(date)

returns the month (1-12)

MONTHNAME(date)

returns the name of the month

NOW()

returns the current date and time as a timestamp

QUARTER(date)

returns the quarter (1-4)

SECOND(time)

returns the second (0-59)

WEEK(date)

returns the week of this year (1-53)

YEAR(date)

returns the year


Note that A date value starts and ends with ', the format is yyyy-mm-dd (see java.sql.Date). A time value starts and ends with ', the format is hh:mm:ss (see java.sql.Time).

Table A-8 describes the system functions.

Table A-8 System Functions

Function Description

IFNULL(exp,value)

if exp is null, value is returned else exp

CASEWHEN(exp,v2,v2)

if exp is true, v1 is returned, else v2

CONVERT(term,type)

converts exp to another data type

COALESCENCE(e1,e2,e3,...)

if e1 is not null then it is returned, else e2 is evaluated. If e2 is null, then is it returned, else e3 is evaluated and so on.

NULLIF(v1,v2)

returns v1 if v1 is not equal to v2, else returns null

CASE WHEN

There are two syntax for the CASE WHEN statement:

CASE v1 WHEN v2 THEN v3 [ELSE v4] END: if v1 equals v2 then returns v3 [otherwise v4 or null if ELSE is not specified].

CASE WHEN e1 THEN v1[WHEN e2 THEN v2] [ELSE v4] END: when e1 is true return v1 [optionally repeated for more cases] [otherwise v4 or null if there is no ELSE]

CAST(term AS type)

converts exp to another data type


Table A-9 describes the system and connection functions.

Table A-9 System and Connection Functions

Function Description

DATABASE()

returns the name of the database of this connection

USER()

returns the user name of this connection

IDENTITY()

returns the last identity values that was inserted by this connection


A.5 JDBC API Implemented Features

Table A-10 lists the JDBC API features of the Oracle Data Integrator driver for LDAP.

Table A-10 JDBC API Features

Feature Groups JDBC Version Support

Batch Update

2.0 Core

Yes

Blob/Clob

2.0 Core

No

JNDI DataSources

2.0 Optional

No

Failover support

-

No

Transaction SavePoints

3.0

No

Unicode support

-

No

Disributed Transaction

2.0 Optional

No

Connection Pooling

2.0 Optional

No

Cluster support

-

No


The following table identifies the JDBC classes supported by the Oracle Data Integrator driver for LDAP.

Table A-11 JDBC Classes

JDBC Classes JDBC Version Support

Array

2.0 Core

No

Blob

2.0 Core

No

Clob

2.0 Core

No

CallableStatement

1.0

Yes

Connection

1.0

Yes

ConnectionPoolDataSource

2.0 Optional

No

DatabaseMetaData

1.0

Yes

DataSource

2.0 Optional

No

Driver

1.0

Yes

PreparedStatement

1.0

Yes

Ref

2.0 Core

No

RowSet

2.0 Optional

No

ResultSet

1.0

Yes

ResultSetMetaData

1.0

Yes

Statement

1.0

Yes

Struct

2.0 Core

No

XAConnection

2.0 Optional

No

XADataSource

2.0 Optional

No