Using the LDAP wrapper with InfoSphere Federation Server

Leverage and integrate your directory data in an SQL environment

The LDAP wrapper is a pure Java™ package that is based on InfoSphere™ Federation Server Java wrapper SDK technology. By providing read-only access to Lightweight Directory Access Protocol (LDAP) directory servers in an SQL environment, the LDAP wrapper facilitates the integration and connectivity between business data in a relational database and human resource data in the LDAP directory server.

Share:

Yun Feng Sun (sunyunf@cn.ibm.com), Advisory Software Engineer, IBM China

Sun Yun FengYun Feng Sun é engenheiro consultor de software no Laboratório de Desenvolvimento de Software da IBM na China. Ele trabalha atualmente para a equipe de Desenvolvimento do Produto IBM InfoSphere Federation Server.



Eileen Lin (etlin@us.ibm.com), Senior Technical Staff Member, IBM China

Author photoDra. Eileen Lin é membro senior da equipe técnica no Laboratório do Vale do Silício em San Jose, Califórnia. Ela é um dos primeiros membros responsáveis pelo sucesso do DataJoiner, um produto de banco de dados federado que é o predecessor da tecnologia de federação no DB2. Atualmente, é arquiteta chefe do WebSphere Federation Server. A Dra. Lin possui várias patentes que abrangem áreas como tecnologia de federação, otimização de consultas e processamento de consulta paralela.



Art Kaufmann (artkauf@us.ibm.com), Senior Software Engineer, IBM China

Art Kaufmann photoArt Kaufmann é engenheiro de software senior, e trabalha em integração da informação através de federação e serviços da Web. Art é supervisor técnico da equipe SOA e de Federação, responsável pelo SDK de wrapper.



Martin Klumpp (maklumpp@us.ibm.com), Technical Lead, Software Engineer, IBM China

Martin Klumpp photoMartin Klumpp é supervisor técnico do produto InfoSphere FastTrack e localiza-se no Laboratório da IBM do Vale do Silício em San Jose, Califórnia. Ele participou do projeto e do desenvolvimento do wrapper do LDAP em 2005.


developerWorks Contributing author
        level

Xing Yu Liu, Software Engineer Intern, IBM China

Xing Yu Liu photoXing Yu Liu foi estagiário de engenharia de software no Laboratório de Desenvolvimento de Software da IBM na China e esteve envolvido no desenvolvimento do wrapper do LDAP.



23 September 2010

Also available in Chinese Portuguese

Before you start

About this tutorial

This tutorial introduces a Java-based LDAP wrapper that helps InfoSphere Federation Server access and integrate LDAP directory data. The tutorial starts by highlighting the business value of the wrapper, then states the detailed steps of using the wrapper, and follows up with a simple scenario to join LDAP data with other enterprise data. The binary code of the LDAP wrapper is available for download in the Download section.

Objectives

This tutorial shows you how to configure and use the LDAP wrapper with InfoSphere Federation Server to read directory data in the LDAP directory server. After you've done this, you are able to join and union data in your LDAP directory server with other corporate data accessible to your federated database.

Prerequisites

This tutorial is written using InfoSphere Federation Server and LDAP directory server. You should have general familiarity with LDAP, relational databases, and InfoSphere Federation Server concepts, including federated server, wrapper, server definition, user mapping, and nicknames.

System requirements

To complete the steps in this tutorial, you need to install the following software:

  • InfoSphere Federation Server: This tutorial is based on InfoSphere Federation Server Version 9.5. InfoSphere Federation Server v9.1 and v9.7 can also be used. For details, see the InfoSphere Federation Server website.
  • LDAP directory server: This tutorial is based on the Tivoli Directory Server V6.0. For details, see the Tivoli Directory Server website. However, any LDAP v3 compatible LDAP directory server can be used.

Introduction

Today, any but the simplest of business tasks requires the use of information from the variety of data sources that businesses have built over many years. The data may be stored in a variety of formats, such as relational or non-relational data sources. This complex and dynamic environment presents significant challenges to business users and applications, and to the IT people who must maintain and manage it. Federation technology shields the requester from all the complexities associated with accessing data in diverse locations, including connectivity, semantics, formats, and access methods. Using a standards-based language such as structured query language (SQL), InfoSphere Federation Server enables users, or applications acting on their behalf, to access information transparently without concern for its physical implementation.

The LDAP wrapper adds value to existing federation technology by enabling it to access and integrate data in the LDAP directory server, which is widely used in enterprise environments to store human resource data, and identity information. It is developed with pure Java and based on InfoSphere Federation Server Java wrapper SDK technology. Internally it uses JNDI to access the LDAP directory server.

Figure 1 illustrates the architecture of the LDAP wrapper.

Figure 1. The LDAP wrapper Architecture
Federated client uses SQL to accesss federated server, which uses LDAP wrapper and JNDI to access LDAP Server. Results at the client is a relational table.

The Lightweight Directory Access Protocol, or LDAP, is an application protocol for querying and modifying directory services. The basic unit of information stored in the directory is called an LDAP entry, which is defined through the LDAP schema and organized as a Directory Information Tree (DIT) structure. An LDAP entry can be identified by its Distinguished Name (DN).

Figure 2 shows sample department entries, which are stored in the DIT. The object class is department and DN is ou=IBM (ou: organizational unit).

Figure 2. An LDAP DIT example
chart shows ou=IBM, connected to 4 departments: QA, Development, Marketing, and Sales, each with employees, country, and location

With the LDAP wrapper, you can map LDAP entries that are under a DN with the same schema to an LDAP nickname. You can then use SQL to query mapped LDAP entries over the LDAP nickname through InfoSphere Federation Server. Basically, you perform the mapping by specifying the DN and the object class of the LDAP entries. For example, if you have created the nickname DEPARTMENT with the DIT shown in Figure 2, you can use the SQL shown in Listing 1 to retrieve all departments with less than 20 employees.

Listing 1. Query Nickname DEPARTMENT
SELECT * FROM DEPARTMENT WHERE EMPLOYEES < 20;

NAME        EMPLOYEES   COUNTRY   LOCATION
----------  ----------- --------- ---------
Marketing   12          USA       CA 
Sales       10          USA       CA

This tutorial provides you detailed instructions on how to use the LDAP wrapper.


Deploy the LDAP wrapper

Download the LDAP wrapper package

The LDAP wrapper is available from the Download section of this tutorial. Download the LDAPWrapper.zip onto your local file system and unzip it into a directory. The zip file contains the following files:

  • LDAPWrapper.jar is the Java package of the LDAP wrapper. Put the jar file in the <InfoSphere Federation Server install path>/function directory. This directory is in the default class path for the InfoSphere Federation Server JVM. Or, you can put the package in any path you want, and update your CLASSPATH environment variable to include the path.
  • LDAPWrapper.xml and LDAPWrapper.properties are used by the DB2 Control Center to define LDAP wrapper objects in the Control Center. Put LDAPWrapper.xml in the <InfoSphere Federation Server install path>/cfg directory. Put LDAPWrapper.properties in the <InfoSphere Federation Server install path>/tools/en_US/wrapper_cfg directory.
  • create_function_mappings.ddl contains a DDL statement for creating a function mapping for the LDAP wrapper. You use this ddl file later in the tutorial.

Set up dbm cfg parameters for LDAP wrapper

The LDAP wrapper is a Java-based wrapper that uses JDNI to connect to the LDAP directory server. The JNDI class libraries, and some of the features the LDAP wrapper uses, are included with the Java 2 SDK V1.4.2 and above. Use the JDK shipped with InfoSphere Federation Server.

To set up dbm cfg parameters for LDAP wrappers, do the following:

  1. Set the database manager configuration parameter called JDK_PATH to point to a valid JDK installation.

    If the JDK was installed with your InfoSphere Federation Server product, this parameter is set properly. However, if you reset the database manager (dbm cfg) parameter, you need to specify where the SDK for Java is installed. To do this, use the command in Listing 2 and replace <your_jdk_path > with the actual path for your JDK.

    Listing 2. Update JDK PATH
    db2 update dbm cfg using JDK_PATH <your_jdk_path>

    If your JDK is installed in the $HOME/sqllib/java/jdk directory, you can define the path as an empty string ('').

  2. Increase the heap size for the Java VM.

    By default, the Java VM heap size is set to 4096 pages(4K) on HP-UX, and 2048 pages on all the other operating systems in InfoSphere Federation Server V9.5. This may not be sufficient to load and run the LDAP wrapper, so you should increase the heap size.

    For example, run the command in Listing 3 to set your Java VM heap size to 8192 pages:

    Listing 3. Update Java heap size
    db2 update dbm cfg using JAVA_HEAP_SZ 8192
  3. After you update the database manager configuration, you have to restart DB2 using the commands shown in Listing 4.
    Listing 4. Restart DB2
    db2 terminate
    db2stop
    db2start

Adding LDAP data sources to a federated server

To configure the federated server to access LDAP data sources, you must provide the federated server with information about the data sources and objects that you want to access. You can configure the federated server to access LDAP data sources by using the DB2 Control Center or the DB2 command line. The DB2 Control Center includes a wizard to configure the federated server.

Before you begin

  • Federation must be installed on a server that will act as the federated server.
  • A database must exist on the federated server.

To add the LDAP directory server and LDAP objects to a federated server, do the following:

  1. Register the custom functions for the LDAP wrapper.
  2. Register the LDAP wrapper.
  3. Register the LDAP server definitions.
  4. Optionally: create the LDAP user mapping.
  5. Register nicknames for the LDAP entries.

Registering the custom functions for the LDAP wrapper

You must register the LDAP custom functions before you register the LDAP wrapper. The LDAP custom functions are used with the LDAP wrapper to push predicates to the query engine on the LDAP directory data source server.

You must register all of the custom functions on each federated database instance where the LDAP wrapper is installed. All of the custom functions for the LDAP wrapper must be registered with the schema name ldap. For each of the LDAP custom functions, issue the CREATE FUNCTION statement with the AS TEMPLATE DETERMINISTIC NO EXTERNAL ACTION keywords. The fully qualified name of each function is ldap.function_name.

The example shown in Listing 5 registers one version of the ldap.search_dn function:

Listing 5. Create function template example
CREATE FUNCTION ldap.search_dn (varchar(),varchar()) 
    RETURNS INTEGER AS TEMPLATE 
    DETERMINISTIC NO EXTERNAL ACTION;

To register the custom functions, you can use the sample file create_function_mappings.ddl from the downloaded package, which includes the CREATE FUNCTION statements for each of the possible data type combinations. You can then connect to the federated database and use the command in Listing 6 to register the custom functions on each federated database.

Listing 6. Register the LDAP wrapper custom functions
db2 connect to <federated database>

db2  -tvf create_function_mappings.ddl

LDAP search uses two categories of functions:

  • Functions for search configurations
  • Functions for search filter

A later section of the tutorial shows you how to use these functions.

Table 1 and Table 2 list the descriptions of the custom functions that are used with the LDAP wrapper.

Table 1. Custom functions for the LDAP search configuration parameters
FunctionDescription
ldap.search_dn(arg1,arg2)agr1 specifies any column name of the nickname that will apply the change.
arg2 specifies LDAP search base object - the search starting point in DIT.
When using this function to query an LDAP nickname, it will overwrite the DN option defined in the nickname.
ldap.search_scope(arg1,arg2)agr1 specifies any column name of the nickname that will apply the change.
arg2 specifies how deep within the DIT to search from the base object.

The argument value can be one of the following choices:

  • OBJECT— Search only the base object.
  • ONELEVEL— Search only the immediate children of the base object; the base object itself is not examined.
  • SUBTREE— Search the base object and all of its descendants.

Note: For the two functions in Table 1, the data type for the arg1 parameters is any data type that the LDAP wrapper supports. The data type for the arg2 parameters is varchar().

Table 2. Custom functions for the LDAP search filter
FunctionDescription
ldap.eq(arg1,arg2)Operator = in LDAP search filter.
ldap.ge(arg1,arg2)Operator >= in LDAP search filter.
ldap.le(arg1,arg2)Operator <= in LDAP search filter.
ldap.gt(arg1,arg2)In LDAP search filter, there is no direct > operator. The gt function will be converted to &( arg1>=arg2)(! (arg1=arg2))
ldap.lt(arg1,arg2)In LDAP search filter, there is no direct < operator. The lt function will be converted to &( arg1<=arg2)(! (arg1=arg2))

Note:

For the functions listed in Table 2, arg1 and arg2 have the same data type. These are the data types that the LDAP wrapper supports except BLOB and CLOB. arg1 specifies the name of a column. arg2 specifies the value to be compared. For ldap.eq, the * character can be used in arg2 and matches any substring if remote LDAP attribute syntax support substring match.

For the column that represents an attribute with multi-value, the entries with any of the matching values are retrieved, and all the values concatenated with nickname column option DELIMITER value can displayed.

Registering the LDAP wrapper

You must register a wrapper to access LDAP data sources. Federated servers use wrappers to communicate with and retrieve data from data sources.

To register a wrapper, use the CREATE WRAPPER statement. For example, to register the ldap_wrapper on the federated server that uses the AIX operating system, issue the statement in Listing 7.

Listing 7. Create wrapper statement
CREATE WRAPPER ldap_wrapper LIBRARY 'libdb2qgjava.a' options( 
    UNFENCED_WRAPPER_CLASS 'com.ibm.wfs.wrapper.ldap.UnfencedLdapWrapper');
  • LIBRARY 'libdb2qgjava.a'

    This is the name of the Java wrapper SDK library file for federated servers that use AIX operating systems. The library name to use on the Windows platforms is 'db2qgjava.dll'. The library name to use on AIX is 'libdb2qgjava.a', and on other UNIX platforms the library name is 'libdb2qgjava.so'.

  • UNFENCED_WRAPPER_CLASS 'com.ibm.wfs.wrapper.ldap.UnfencedLdapWrapper'

    The UNFENCED_WRAPPER_CLASS wrapper option is required for any Java-based wrapper, which points to the name of the LDAP wrapper unfenced wrapper class.

Registering the server definition for an LDAP data source

After you register the wrapper, you must register a corresponding server.

For LDAP wrapper, you create a server definition, because the wrapper has to be provided with connection information that it can use to connect to the LDAP directory data source server.

To register the LDAP server definition to the federated system, use the CREATE SERVER statement.

The server object must be associated with the LDAP wrapper that you registered using the CREATE WRAPPER statement.

For example, suppose that you want to create a server object called ldap_server for an LDAP directory data source server that resides on the same machine as the federated server, listening on the port 389. The communication with the LDAP directory data source server is unencrypted, and the connection timeout is set to 3 minutes. You can use the CREATE SERVER statement shown in Listing 8 to register the server object.

Listing 8. Create server statement
CREATE SERVER ldap_server WRAPPER ldap_wrapper 
   OPTIONS (URL 'ldap://localhost:389', TIMTOUT ‘3');

You can use the URL and TIMEOUT server options specified in the CREATE SERVER statement, as shown in Table 3.

Table 3. LDAP Server options
Server optionsDescription
URLRequired
Type: String
This is the connection URL for the LDAP directory data source server
TIMEOUTOptional
Type: String. This must be an integer string.
The timeout value for the connection (LDAP bind operation) is in minutes.
The default value is 2 minutes.

URL option value format

You can either use an explicit host list, or a default host to specify one or more target LDAP directory servers.

  • Explicit host list

    You can explicitly specify the name of the host on which the LDAP directory data source server is running. You can use the URL option to connect to a blank-separated list of hosts, and each host can optionally use the form scheme://host:port or scheme://host. The examples in Listing 9 show how the LDAP wrapper connects to the first active server in the list.

    Listing 9. Explicit host list in server URL option
    CREATE SERVER ldap_server WRAPPER ldap_wrapper 
       OPTIONS ( URL 'ldap://server1');
       
    CREATE SERVER ldap_server WRAPPER ldap_wrapper 
       OPTIONS ( URL 'ldap://server1:389');
       
    CREATE SERVER ldap_server WRAPPER ldap_wrapper 
       OPTIONS ( URL 'ldap://server1:389 ldap://server2');
  • Default hosts

    If you have registered LDAP services in DNS on your network infrastructure, then with the help of JNDI, the LDAP wrapper can locate one or more default LDAP directory data source servers with the following format:

    Listing 10. Default hosts in server URL option
    CREATE SERVER ldap_server WRAPPER ldap_wrapper OPTIONS (URL 'ldap://');

    If the host parameter is set to ldap://, then the LDAP library will try to find one or more default LDAP directory data source servers that have non-SSL ports. If it finds more than one default server, then it processes the list in sequence until it finds an active server.

    You can add a Distinguished Name as a filter to the URL to select LDAP directory data source servers based on the server's suffixes. If the main portion of the DN is an exact match with a server's suffix after normalizing for case, then it adds the server to the list of candidate servers. For example, Listing 11 shows a statement that returns the default LDAP data source servers that have a suffix that supports the specified DN only.

    Listing 11. Default hosts with suffix in server URL option
    CREATE SERVER ldap_server WRAPPER ldap_wrapper 
       OPTIONS (URL 'ldap:///cn=fred, dc=austin, dc=ibm, dc=com’);

    The server has a suffix of dc=austin, dc=ibm, dc=com that matches the search parameters. If more than one default server is located, then the list is processed in sequence until an active server is found.

The LDAP wrapper stays connected to the selected LDAP directory data source server until the connection is cleaned up.

TIMEOUT

The LDAP wrapper aborts a connection attempt if it cannot establish a connection to the LDAP directory data source server within a certain timeout period. The default timeout period set by the LDAP wrapper is two minutes. You can change the timeout period by setting the option value as a string of an integer representing the connection timeout in minutes.

The LDAP wrapper security model

The LDAP wrapper uses JNDI security, which are the security models in the underlying Java platform and LDAP protocol. SSL Encryption is used, and as a prerequisite, the LDAP directory data source server has to be configured for SSL. The SSL certificate must be registered with the Federation Server JRE. You must then install the server or CA certificate in your Federation Server JRE database of trusted certificates. For example, Listing 12 shows how to add a certificate to JRE.

Listing 12. Add certificate to JRE
cd <Federation_Server_JRE_PATH>/lib/security
keytool -import -file server_cert.cer -keystore cacerts

For more information about registering a SSL certificate, refer to the JDK documents.

You can request SSL from LDAPS URLs. If an LDAPS URL is in the scheme part of a URL option, then it is ldaps instead of ldap. It specifies the use of SSL when communicating with the LDAP directory data source server. For example, Listing 13 shows how the LDAP wrapper uses SSL with the CREATE SERVER statement.

Listing 13. Create server statement with SSL enabled
CREATE SERVER ldap_server WRAPPER ldap_wrapper 
   OPTIONS ( URL 'ldaps://localhost:636', TIMEOUT ‘3’);

Note: You must use SSL to connect to a server on a port that is using SSL. The default LDAP SSL port is 636, and the default port for unsecured communications is 389.

Creating the user mappings for an LDAP data source

When you try to access an LDAP directory data source server, the federated server establishes a connection to the LDAP directory data source server. You may not have to create user mappings, depending on the account access methods that are used in your LDAP system.

A user mapping is an association between each federated server user ID and password, and the corresponding data source user ID and password.

You can use two methods to specify user mappings with federated systems. You can include an external repository such as another LDAP directory server to store the user mappings, or you can create the user mappings in the federated database catalog.

  • You do not have to create user mappings in InfoSphere Federation Server if your LDAP directory data source server is configured for anonymous access.

  • You do not have to create user mappings if you have an external repository to store the user mappings, such as another LDAP directory server, but you must specify the DB2_UM_PLUGIN option on the LDAP wrapper or LDAP server definition when you register or alter the wrapper or server.

  • You must create user mappings for any accounts that use the LDAP wrapper if your LDAP directory data source server is configured to authenticate user accounts with IDs and passwords.

User mappings provide a way to authenticate the access of users or applications that query an LDAP data source with the LDAP wrapper. If a user or application submits an SQL query to a registered LDAP nickname, and no user mappings are defined for that user or application, then the LDAP wrapper uses anonymous authentication to retrieve data from the remote LDAP directory data source server. If an LDAP DIT is queried and requires authentication, then an error message may be returned. To ensure that the correct user ID and password are passed to the LDAP directory data source server, you can create user mappings in your federated database for users who are authorized to search LDAP data sources. When you create a user mapping, the password is stored in an encrypted format in a federated database system catalog table.

Use the CREATE USER MAPPING statement to register the user for the LDAP server object. The user object must be associated with the LDAP server object that you registered using the CREATE SERVER statement. For example, Listing 14 shows how the CREATE USER MAPPING statement maps a user DEMO to user cn=root on the LDAP directory data source server.

Listing 14. Create user mapping statement for specific user
CREATE USER MAPPING FOR DEMO SERVER ldap_server OPTIONS (
   REMOTE_AUTHID 'cn=root',
   REMOTE_PASSWORD 'amIAdmin');

In the example shown in Listing 15, USER is a keyword that identifies the current user, not a username of USER.

Listing 15. Create user mapping statement for current user
CREATE USER MAPPING FOR USER SERVER ldap_server OPTIONS (
   REMOTE_AUTHID 'cn=root',
   REMOTE_PASSWORD 'amIAdmin');
Table 4. LDAP user mapping options
User mapping optionsDescription
REMOTE_AUTHIDRequired
Type: Non-zero length String
The remote user ID that can be used to connect to the LDAP directory server
REMOTE_PASSWORDRequired
Type: Non-zero length String
The remote password that belongs to the user ID

Registering nicknames for a LDAP data source

After you register a server, you must register a corresponding nickname to refer to the LDAP entries in a query.

To define an LDAP nickname, you have to map LDAP entry attributes against the relational model. The nickname column names are required to match the LDAP entry attribute names. The LDAP attribute types can be mapped to the following DB2 data types: VARCHAR, CHARACTER, CLOB, INTEGER, SMALLINT, BIGINT, DOUBLE/FLOAT, REAL, DECIMAL/NUMERIC, TIMESTAMP, TIME, DATE, and BLOB.

The attribute data types should either be the same or compatible, so that the LDAP wrapper can cast the attribute data to the DB2 data type. If the LDAP directory data source server stores string type data that has a compatible format, then the LDAP wrapper can set the data to a DB2 data type. The wrapper will report an error if the data could not be correctly set, or exceeds the range of the DB2 data type when retrieving data from data source. If a non-numeric field is too long for its column type, then the excess data is truncated with a warning message. If a decimal field has more digits after the radix char than are allowed by the scale parameter of its column type, then the excess data is truncated with a warning message (SQL1844W).

Table 5. DB2/LDAP data type mapping table
DB2 data typeLDAP attribute data format
VARCHAR, CHARACTER, CLOBAny LDAP attribute with string syntax in nature
INTEGER, SMALLINT, BIGINT
  • An LDAP attribute with an INTEGER syntax (1.3.6.1.4.1.1466.115.121.1.27)
  • An LDAP attribute that has string data with an integer format
DOUBLE/FLOAT, REALAn LDAP attribute that has string data with a floating-point number format
DECIMAL/NUMERICLDAP attribute that has string data with a decimal/numeric format defined in the nickname
TIMESTAMPLDAP attribute with Generalized Time (1.3.6.1.4.1.1466.115.121.1.24)
DATELDAP attribute with Generalized Time (1.3.6.1.4.1.1466.115.121.1.24).
The LDAP wrapper extracts the DATE part of LDAP attribute value and converts it to DB2 DATE format.
TIMELDAP attribute with Generalized Time (1.3.6.1.4.1.1466.115.121.1.24).
The LDAP wrapper extracts the TIME part of LDAP attribute value and converts it to DB2 TIME format.
BLOBAny LDAP attribute with a binary syntax in nature

To register an LDAP nickname, use the CREATE NICKNAME statement. For example, Listing 16 shows how the statement creates the Department nickname.

Listing 16. Create nickname statement
CREATE NICKNAME Department (
   department VARCHAR(20) NOT NULL, 
   name VARCHAR (150),
   employees INTEGER,
   country VARCHAR(50),
   location VARCHAR(10)) 
   FOR SERVER ldap_server 
      OPTIONS (
         DN 'ou=IBM'
         OBJECTCLASS 'department'
         SCOPE ‘ONELEVEL’
         SIZE_LIMIT '1000'
         TIME_LIMIT '10'
         DEREF ‘FINDING’);

The LDAP wrapper translates all queries against an LDAP nickname to an LDAP search operation. Table 6 shows how the specified LDAP nickname options correspond to the LDAP search parameters.

Table 6. LDAP nickname options
Nickname optionsDescription
DNRequired
Type: String
A Distinguished Name specifies the base object in the DIT for LDAP search.
OBJECTCLASSRequired
Type: String
Specifies the object classes of the entries that can be retrieved through the nickname for the given context. It allows a multi-value separated by a semi-colon ;.
SCOPEOptional
Type: String
Specifies how deep within the DIT to search from the base object. The value can be one of the following choices:
  • OBJECT— Searches only the base object.
  • ONELEVEL— Searches only the immediate children of the base object; the base object itself is not examined.
  • SUBTREE— Searches the base object and all of its descendants. This is the default search scope.
SIZE_LIMITOptional
Type: String. It must be an integer string.
Restricts the number of entries returned from the search.
The default value is 0, so the search results have no maximum return limit.
TIME_LIMITOptional
Type: String. It must be an integer string.
Limits the total time of the search in minutes.
The default value is 0, so there is no time limit for search.
DEREFOptional
Type: String
Specifies whether alias objects (as defined in X.501) are handled during the search. If this option value is set to Y, then the data source will de-reference aliases in subordinates of the base object during the search.
Default value is N.

DN

The Distinguished Name (DN) specifies the Base parameter for an LDAP search context. The base object is a node that defines the search starting point within the DIT.

You can change the DN value in two ways when querying a nickname:

  • Use the ALTER NICKNAME statement to change the DN value before querying
  • Use the ldap.search_dn function in predicates to overwrite the DN value specified in the nickname. Listing 17 shows an example query.
    Listing 17. Query with ldap.search_dn
    select * from department where ldap.search_dn (department.name,‘cn=dev,ou=ibm’)=1;

OBJECTCLASS

The object class is an LDAP term that describes the type of object that is represented by a directory entry. The OBJECTCLASS option is used to do the following tasks:

  1. Specify what type of entries can be retrieved. For example: The search context contains entries from structural object class Person and Department. Therefore, when you search for a nickname, depending on how the OBJECTCLASS is specified in the CREATE NICKNAME statement, either the Person or the Department object class instance is returned.

  2. Enforce the nickname column names. The column names in the CREATE NICKNAME statement should be attribute names specified in the object classes, or their superior object classes. If you want to define a nickname column name that is different from the LDAP attribute name, then you must use the REMOTE_NAME column option to register the nickname. For example, Listing 18 shows how the employees attribute is defined with a different name employee_number.

    Listing 18. Create nickname with column option REMOTE_NAME
    CREATE NICKNAME Department (
       department VARCHAR(20) NOT NULL, 
       name VARCHAR (150),
       employee_number INTEGER OPTIONS(REMOTE_NAME ’ employees’),
       country VARCHAR(50),
       location VARCHAR(10)) 
       FOR SERVER ldap_server 
       OPTIONS (
           DN 'ou=IBM'
           OBJECTCLASS 'department');

The OBJECTCLASS value can be defined with any valid object class name that is defined on the LDAP directory data source server, but it cannot contain a * in the name.

You can declare an object class as abstract, structural, or auxiliary in an LDAP system. You can use an abstract object class as a template for creating other object classes. A directory entry cannot be proven from an abstract object class; instead it requires structural object classes. An auxiliary object class cannot be proven by itself as a directory entry, but it can be attached to directory entries that are proven from structural object classes. Auxiliary object classes provide a method for extending structural object classes without having to change the schema definition of a structural class.

Listing 19 shows an example of an LDAP entry.

Listing 19. Sample LDAP entry with auxiliary object class
dn: cn=Barbara J Jensen,dc=example,dc=com
objectClass: person 
objectClass: contact
cn: Barbara J Jensen
sn: Jensen
mail: jensen@example.com

The objectClass called person is structural, so it has attributes of cn and sn.

The objectClass called contact is auxiliary, so it has an attribute called mail.

You can retrieve the attributes in the auxiliary object class by combining it with attributes in structural or other auxiliary object classes in the same entry. The OBJECTCLASS allows multiple values with the semicolon ; delimeter.

For example, Listing 20 shows how to use multiple object classes to create a nickname.

Listing 20. Create a nickname with multiple object classes
CREATE NICKNAME ldap_person_mail (
   cn char(50) NOT NULL, 
   sn char(30), 
   mail char(50)) 
   FOR SERVER ldap_server 
      OPTIONS (
          DN 'dc=example.com’, 
          OBJECTCLASS 'person;contact');

SCOPE

You can set the OBJECT, ONELEVEL, and SUBTREE search scopes for each LDAP search query. You can then change the SCOPE value in the following two ways when querying a nickname.

  • You can use the ALTER NICKNAME statement to change the SCOPE value before querying.

  • You can use the ldap.search_scope function in the predicates, so that the ldap.search_scope function overwrites the SCOPE value specified in the nickname. For example, Listing 21 shows how to query with the ldap.search_scope function.

    Listing 21. Query with ldap.search_scope
    SELECT * FROM Department WHERE LDAP.SEARCH_SCOPE (Department.name,'SUBTREE') = 1;

TIME_LIMIT and SIZE_LIMIT

When you set a query on an LDAP nickname, it is translated to an LDAP search operation. Since general LDAP searches return a large number of entries, you should specify time and size limits to avoid consuming resources. The SIZE_LIMIT restricts the number of entries returned from search. The TIME_LIMIT limits the total time of the search. The LDAP directory data source servers may also impose more strict limits than those requested by the LDAP wrapper. Therefore, the actual TIME_LIMIT and SIZE_LIMIT are the lesser of the value specified on this option, as well as the value configured in the LDAP directory data source server.

Note: When you query an LDAP nickname, the SIZE_LIMIT is not meant to limit the number of rows returned by the LDAP wrapper. Instead, it sets the size limit parameter for an LDAP search operation for the LDAP query to retrieve data from the LDAP directory data source server. Similarly, you can use the TIME_LIMIT to set the time limit parameter for an LDAP search.

If you set too high of a limit, then the SIZE_LIMIT and TIME_LIMIT options may give a non-deterministic query result. For example, Listing 22 shows how to query a nickname with specific size and time limits.

Listing 22. Query nickname with SIZE_LIMIT and TIME_LIMIT
CREATE NICKNAME department (
    department VARCHAR(20) NOT NULL, 
    name VARCHAR (150),
    employees INTEGER,
    country VARCHAR(50),
    location VARCHAR(10)) 
    FOR SERVER ldap_server 
    OPTIONS (
         DN 'ou=IBM'
         OBJECTCLASS 'department'
         SIZE_LIMIT '1000'
         TIME_LIMIT '10');
         
SELECT * FROM department WHERE location=’CA';

If you set the object class department under the DN ou=ibm to a SIZE_LIMIT of 1000 entries, and there are actually more than 1000 entries, then the query may return a different number of rows. This happens because some of the entries that match the criteria location=CA may not be retrieved from the LDAP directory data source server.

Defining multiple value attributes for the LDAP wrapper

You can maximize the query capabilities of the LDAP wrapper by defining each attribute as its true equivalent DB2 data type, where LDAP integers are defined as DB2 integers. However, by doing this, you prevent the return of multiple values for non-VARCHAR attributes because only the first value is returned. This is restricted, because when you select attributes with multiple values, the wrapper returns only one row of results per LDAP entry. However, you can define a second column for the same remote attribute with a data type of VARCHAR. You can then specify the remote name of the second column by using the REMOTE_NAME column option.

You can use the column name in the SELECT list to return all values as a delimiter-separated list of all of its values. You specify the delimiter that you want to use within each column's DELIMITER option.

You can also standardize the local names of each multi-value column. For example, you can add the m_ prefix to the local name of the column that is defined as its true data type. For example, suppose you have a nickname column called actionDate in an LDAP multi-value attribute that is defined with the data type TIMESTAMP. You can then create a second nickname column called m_actionDate that has a REMOTE_NAME column option pointing to actionDate. You can then define it as a VARCHAR data type, and use the m_actionDate in a SELECT list to return all approval dates in a delimiter-separated list. For example, Listing 23 shows how to create the nickname using multiple value attributes.

Listing 23. Create a nickname for multiple value attributes
CREATE NICKNAME date (
     actionDate  TIMESTAMP,
     m_actionDate  VARCHAR(256) OPTIONS (REMOTE_NAME 'actionDate', DELIMITER  ';')) 
     FOR SERVER ldap_server
     OPTIONS (
        DN 'ou=IBM'
        OBJECTCLASS 'date');

Queries and custom functions for LDAP data sources

You can use custom functions in a query.

Custom functions for LDAP search configuration parameters

You can change the search DN and the SCOPE of a nickname, depending on the query. You can use the following two custom functions to temporarily set the value for a query:

  • ldap.search_dn
  • ldap.search_scope

When you use the custom functions, you must compare their return value to the value 1 in the equality predicate. You must also use AND with any other predicates that apply to the columns in the same LDAP nickname, as shown in Listing 24.

Listing 24. Correct examples of custom functions for LDAP search configuration parameters
SELECT * FROM ldap_nickname 
   WHERE ldap.search_scope(ldap_nickname.column, ‘ONELEVEL’)=1 
   AND location='CA';

SELECT * FROM ldap_nickname1,ldap_nickname2 
   WHERE ldap.search_dn(ldap_nickname1.column,'cn=dn1')=1 
   AND     ldap.search_dn(ldap_nickname2.column,'cn=dn2')=1;

Listing 25 shows an incorrect statement that will return the error message SQL0142N (SQL statement not supported).

Listing 25. Incorrect example of custom functions for LDAP search configuration parameters
SELECT * from ldap_nickname 
   WHERE ldap.search_scope(ldap_nickname.column, ‘ONELEVEL’)=1 
   OR location=’CA’;

Custom functions for an LDAP search filter

The federated environment uses the federated database server and LDAP directory data source server query engines for the LDAP wrapper. You can use the following LDAP custom functions to specify that predicates get pushed down to the LDAP directory data source server:

  • ldap.eq
  • ldap.ge
  • ldap.le
  • ldap.gt
  • ldap.lt

You must enter a search term column argument and a query term argument in custom functions. The following example shows an ldap.eq statement: ldap.eq (<search term column>,<query term>).

When you set the value of the search term column argument, then you must refer to a column defined in an LDAP nickname. The value of the query term argument must be a literal, or a host variable. You cannot use an arithmetic or string concatenation. Also, the value of the query term argument cannot be NULL, even if you define the search term column to allow null values. Varchar is the valid data type and format of the query term argument even if you defined the search term column with a different data type.

The functions return an integer result, and when the functions are used in a predicate, you must compare the return value to the value 1, using the = or <> operators. For example, Listing 26 shows how to use custom functions.

Listing 26. Example of using custom functions for LDAP search filter
SELECT * FROM department WHERE ldap.eq (name, ’development’) = 1;

SELECT * FROM department WHERE ldap.eq (name, ’development’) <> 1;

The expression NOT (ldap.eq (col,value) = 1) is equivalent to ldap.eq (col,value) <> 1.

The relational predicates, such as =, LIKE, and <>, on nickname columns are not pushed down to the LDAP directory data source server for processing because the semantics of those operators are not exactly the same between the LDAP directory data source server and federated database server. Since the relational predicates are compensated by the federated database server, if you mix the relational predicates and LDAP custom function predicates, you will receive the error message SQL0142N (SQL statement not supported). For example, Listing 27 shows a query that incorrectly separates the parts of the predicate that are processed by the wrapper (the ldap.eq) and the relational predicate on employees that must be processed by the federated database server.

Listing 27. Incorrect example of custom functions for LDAP search filter
SELECT * FROM department  WHERE ldap.eq (name, ’mark*’) = 1 OR employees>3;

All custom functions are converted to an LDAP search filter. For example, Listing 28 shows how a nickname is defined with custom functions.

Listing 28. A nickname is used to illustrating usage of custom functions for LDAP search filter
CREATE NICKNAME Department (
   department VARCHAR(20) NOT NULL, 
   name VARCHAR (150),
   employees INTEGER,
   country VARCHAR(50),
   location VARCHAR(10)) 
   FOR SERVER ldap_server 
   OPTIONS (
     DN 'ou=IBM'
     OBJECTCLASS 'department');

Table 7 shows some examples of how an SQL query is converted to an LDAP search filter by the LDAP wrapper

Table 7. Examples showing an SQL query conversion to an LDAP search filter
DescriptionSQLConverted LDAP query filter
Query without predicatesSELECT * FROM Department(objectclass=department)
Query with relational predicatesSELECT * FROM Department where location='CA'(objectclass=department)
ldap.eq in query predicatesSELECT * FROM Department WHERE ldap.eq(location, 'NY')=1(&((location=NY)(objectclass=department))
ldap.eq in query predicatesSELECT * FROM Department WHERE ldap.eq(name,'Mark*')=1(&(name=Mark*)(objectClass=department))
ldap.ge in query predicatesSELECT * FROM Department WHERE ldap.ge(employees ,'20')=1(& (employees>=20)(objectClass=department))
ldap.le in query predicatesSELECT * FROM Department WHERE ldap.le(employees ,'20')=1(& (employees<=20)(objectClass=department))
ldap.gt in query predicatesSELECT * FROM Department WHERE ldap.gt(employees,’40’)=1(&(&(employees>=40)(!(employees=40)))(objectClass=department))
ldap.lt in query predicatesSELECT * FROM Department WHERE ldap.lt(employees,’40’)=1(&(&(employees<=40)(!(employees=40)))(objectClass=department))
AND in query predicatesSELECT * FROM Department WHERE ldap.eq(location,'CA')=1 AND ldap.le(employees,’20’)=1(&(&(location=CA)(employees<=20))(objectClass=department))
OR in query predicatesSELECT * FROM Department WHERE ldap.eq(location,'CA' )=1 OR ldap.eq(location , 'NY)'=1;(&(|(location=CA)(location=NY))(objectClass=department))
NOT in query predicatesSELECT * FROM Department WHERE NOT ldap.eq(location,'CA')=1
SELECT * FROM Department WHERE ldap.eq(location,'CA')<>1
(&(!(location=CA))(objectClass=department))

How to query LDAP entries with multi-value attributes

A field cannot have multiple values in a relational model, but an attribute in an LDAP entry may have more than one value. The following examples explain how a query against an LDAP nickname behaves in a multi-value attribute situation.

Suppose you have a multi-value LDAP entry like this:

Listing 29. Example of a multi-value LDAP entry
   dn: value=1,dc=example       
   objectClass: data
   value: 1
   value: 2

Listing 30 then defines the nickname as nick_char, and is defined with a column string type, and a DELIMETER column option.

Listing 30. Define nickname nick_char
   CREATE NICKNAME nick_char (value  VARCHAR(256) OPTIONS (DELIMETER ';')) 
      FOR SERVER …
      OPTIONS(…);

Listing 31 shows how the nickname nick_int is defined with a non-string type column that cannot use DELIMETER.

Listing 31. Define nickname nick_int
CREATE NICKNAME nick_int (value  integer) 
   FOR SERVER …
   OPTIONS(…);

When you use queries with relational predicates, the predicates are compensated by the federated database server. For multi-value columns with a relational predicate, although all LDAP entry data are retrieved, only the first value is returned to the federated database for processing.

Listing 32 shows how queries retrieve the entry and display only the first value.

Listing 32. Query nickname nick_char and nick_int with relational predicates
SELECT * FROM nick_char WHERE value=’1;

SELECT * FROM nick_int WHERE value=1;


VALUE
---------------
1

Listing 33 shows queries that could not retrieve the entry.

Listing 33. Query nickname nick_char and nick_int with relational predicates
SELECT * FROM nick_char WHERE value='2’;

SELECT * FROM nick_int WHERE value=2;

You can use custom functions for the LDAP search filter in predicates to have the LDAP directory server process the search filter. The LDAP directory server’s rule says that if an LDAP entry has an attribute that includes ANY of the value matches, then the criteria will be retrieved.

Listing 34 shows how the query nickname uses ldap.eq for nick_char to retrieve the sample entry and display all of the values.

Listing 34. Query nickname nick_char and nick_int with custom functions
SELECT * FROM nick_char WHERE ldap.eq (‘value’, ‘1’) = 1;

SELECT * FROM nick_char WHERE ldap.eq (‘value’, ‘2’) = 1;

VALUE
---------------
1;2

Listing 35 shows how you can use ldap.eq for nick_int to have a sample entry retrieved that displays only the first value. The LDAP wrapper only converts the first piece of data from string to integer, then returns it to the federated database server.

Listing 35. Query nickname nick_char and nick_int with custom functions
SELECT * FROM nick_int WHERE ldap.eq (value, ‘1’) = 1;

SELECT * FROM nick_int WHERE ldqp.eq (value, ‘2’) = 1;

VALUE
---------------
1

How to optimize LDAP wrapper performance

You can optimize the LDAP wrapper performance to minimize the amount of data that is transferred between the LDAP directory data source server and the federated server.

The LDAP wrapper in the federated environment uses the federated database and LDAP directory data source server query engines. The federated database engine processes predicates (relational operators such as =, BETWEEN, LIKE, and <>) that are specified in the nickname columns. The LDAP directory data source server engine processes predicates that use LDAP custom functions for the search filter.

You can minimize the amount of data that is transferred between the two search engines by structuring your queries to have data processing pushed down to the LDAP system whenever possible.


Join LDAP directory data with other enterprise data

Now that you have configured InfoSphere Federation Server to access LDAP directory data, you can join and union this data with data from other data sources.

You can add the appropriate wrapper, server, user mapping, and nickname objects for other data sources in your environment. The process is similar to configuring the InfoSphere Federation Server to access the LDAP directory data source server, although it does vary for different data sources. Consult the InfoSphere Federation Server documentation, tutorials, or Redbooks for details.

This section shows you how to issue a sample query to join data in LDAP entries with data in a predefined nickname in the federated database.

Listing 36 shows the structure of EMPLOYEE, which is a predefined nickname on the InfoSphere Federation Server.

Listing 36. Nickname EMPLOYEE structure
EMPLOYEE
	empno (primary key, char(6))
	firstname (varchar(12))
	midiint (char(1))
	lastname (varchar(15))
	workdept (char(3))
	photo (char(4))
	hiredate (date)
	job (char(8))
	  ...

Listing 37 shows the structure of DEPARTMENT, which you have defined an LDAP nickname for.

Listing 37. Nickname DEPARTMENT structure
DEPARTMENT
   department (varchar(20))
   name (varchar (150)
   employee(integer)
   country COUNTRY (varchar(50))
   location (varchar(10))

Listing 38 shows the SELECT statement you can write to list employee information whose department location is CA.

Listing 38. Join nickname EMPLOYEE and DEPARTMENT
SELECT empno,firstname,lastname,workdapt, job
   FROM DEPARTMENT D, EMPLOYEE E
   WHERE D.department= E.workdept
   AND ldap.eq(D.location, ‘CA’)=1;

You can issue this join query directly from a command window, from tools available through the Control Center, or from tools available through third party offerings. If you prefer not to write your own SQL statement, you can use the Control Center or third-party tools to graphically construct your join query.


Summary

This tutorial showed you how to deploy and configure the LDAP wrapper, including how to create an LDAP wrapper, a server, user mapping, and nickname objects. Concrete examples showed you how to query LDAP data sources. The tutorial also provided performance tips for the LDAP wrapper. Finally, a simple example demonstrated how to join an LDAP nickname with another nickname.


Download

DescriptionNameSize
LDAP Wrapper for this articleLDAPWrapper.zip34KB

Resources

Learn

Get products and technologies

  • Download IBM product evaluation versions and get your hands on application development tools and middleware products from DB2®, Lotus®, Rational®, Tivoli®, and WebSphere®.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management, Tivoli, Java technology
ArticleID=525159
ArticleTitle=Using the LDAP wrapper with InfoSphere Federation Server
publish-date=09232010