Oracle Data Integrator Driver for LDAP Reference

A Oracle Data Integrator Driver for LDAP Reference

The Oracle Data Integrator Driver for LDAP (LDAP driver) allows Oracle Data Integrator to manipulate complex LDAP trees using standard SQL queries.

This appendix includes the following sections:

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

LDAP Processing Overview

The LDAP driver works in the following way:

The driver loads (upon connection) the LDAP structure and data into a relational schema, using a LDAP to Relational Mapping.
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.

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:

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 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 Relational Structure mapped from the LDAP Tree Example shown in Figure A-1"

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.

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_.

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 ldap_metadata 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.

Managing Relational Schemas

This section contains the following topics:

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 Using an External Database to Store the Data for more information on how to set up external storage.

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.

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.

Driver Configuration

Note:

ODI LDAP driver's support for LDAP servers is limited. All the features of the driver will work on any given instance of an LDAP server. ODI uses Java JNDI API to interact with the LDAP servers. If the LDAP server adheres exactly with LDAP specifications, then driver features will work. Otherwise, some of the features may not work.

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 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 ldap_metadata property of the driver URL. See 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 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 URL 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 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 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.
ldap_metadata or lm	No	string (file location)	N/A	Name of a `.properties` file containing the directory connection configuration for the Reference LDAP Tree. See LDAP Directory Connection Configuration for the details of this file content, and 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 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: https://docs.oracle.com/javase/8/docs/technotes/guides/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 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 Mapping Exceptions for more information.
numeric_ids or ni	No	boolean (true \| false)	true	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.
ldap_auth	No	string	simple	LDAP Directory authentication method. See the `auth` property in LDAP Directory Connection Configuration.
ldap_url	Yes	string	N/A	LDAP Directory URL. See the `url` property in LDAP Directory Connection Configuration.
ldap_user	No	string	Empty string	LDAP Directory user name. See the `user` property in LDAP Directory Connection Configuration.
ldap_password	No	string	Empty string	LDAP Directory user password. See the `password` property in LDAP Directory Connection Configuration.
ldap_basedn	No	string	N/A	LDAP Directory basedn. See the `basedn` property in LDAP Directory Connection Configuration.
lm_auth	No	string	simple	Reference LDAP authentication method. See the `auth` property in LDAP Directory Connection Configuration.
lm_url	Yes	string	N/A	Reference LDAP URL. See the `url` property in LDAP Directory Connection Configuration.
lm_user	No	string	Empty string	Reference LDAP Directory user name. See the `user` property in LDAP Directory Connection Configuration.
lm_password	No	string	Empty string	Reference LDAP Directory user password. See the `password` property in LDAP Directory Connection Configuration.
lm_basedn	No	string	N/A	Reference LDAP Directory basedn. See the `basedn` property in LDAP Directory Connection Configuration.
db_driver	Yes	string	N/A	External Database JDBC Driver. See the `driver` property in Using an External Database to Store the Data.
db_url	Yes	string	N/A	External Database JDBC URL. See the `url` property in Using an External Database to Store the Data.
db_user	No	string	Empty string	External Database user. See the `user` property in Using an External Database to Store the Data.
db_password	No	string	Empty string	External Database password. See the `password` property in Using an External Database to Store the Data.
db_schema	No	string	Empty string	External Database schema. See the `schema` property in Using an External Database to Store the Data.
db_catalog	No	string	Empty string	External Database catalog. See the `catalog` property in 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 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 Using an External Database to Store the Data.
page_size	No	integer	0	Read data from LDAP servers with this page size limit. Setting this property to a positive value will cause the LDAP driver to try to use pagination to retrieve all the results, in case the LDAP driver has enforced pagination on search results. Note: The value set for page_size must match the maximum page size (maximum number of results) set on the LDAP server.
transform_nonascii or 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
```

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:

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.OracleDriver.

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:

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

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:
- Local agent (Studio): Place the external DB properties file in the <user.dir>/odi/oracledi/userlib folder
- Standalone Agent: Place the external DB properties file in oracledi/agent/drivers folder
- JavaEE Agent: The external DB properties file should be packed into a JAR or ZIP file and added to the template generated by the Java EE agent. See Deploying an Agent in a Java EE Application Server (Oracle WebLogic Server) in Administering Oracle Data Integrator for more information.
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.OracleDriver&db_url=<external_db_url>

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

Table A-2 External Storage Configuration 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 (cmd\|sh)` command. See Encoding a Password in Administering 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.
unicode	No	boolean (true \| false)		For MS SQL Server: If unicode = true, nvarchar is used. If unicode = false or not set, varchar is used.
varchar_length or vl	No	integer	255	Size of all the columns of the relational structure that will be used to contain string data.

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

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 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 (cmd\|sh)` command. See Encoding a Password in Administering 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

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:

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.
Test the connection to the LDAP data server.
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.
In the alias text file, add short text value aliases to replace the originally derived composite names and save the file.
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.
Now reverse-engineer the LDAP directory as described in Reverse-Engineering an LDAP Model. Oracle Data Integrator will create datastores with the table names defined as aliases in the alias file.

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
....

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.

SQL Syntax

The SQL statements described in 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

SQL Statements

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

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.

INSERT INTO

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

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

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 ]

UPDATE

Modifies data of a table in the database.

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

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).

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

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