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.
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.
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.
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.
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
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
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
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
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>/functiondirectory. 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
CLASSPATHenvironment 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>/cfgdirectory. Put LDAPWrapper.properties in the
<InfoSphere Federation Server install path>/tools/en_US/wrapper_cfgdirectory.
- 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:
- Set the database manager configuration parameter called
JDK_PATHto 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 ('').
- 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
- 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:
- Register the custom functions for the LDAP wrapper.
- Register the LDAP wrapper.
- Register the LDAP server definitions.
- Optionally: create the LDAP user mapping.
- 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
ldap. For each of the LDAP
custom functions, issue the
statement with the
AS TEMPLATE DETERMINISTIC NO EXTERNAL ACTION
keywords. The fully qualified name of each function is
The example shown in Listing 5 registers one version of the
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
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
When using this function to query an LDAP nickname, it will overwrite the DN option defined in the nickname.
The argument value can be one of the following choices:
Note: For the two functions in Table 1, the data type
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
|In LDAP search filter, there is no direct
|In LDAP search filter, there is no direct
For the functions listed in Table 2,
arg2 have the same data type. These are
the data types that the LDAP wrapper supports except
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
ldap_wrapper on the federated
server that uses the AIX operating system, issue the statement in
Listing 7. Create wrapper statement
CREATE WRAPPER ldap_wrapper LIBRARY 'libdb2qgjava.a' options( UNFENCED_WRAPPER_CLASS 'com.ibm.wfs.wrapper.ldap.UnfencedLdapWrapper');
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
UNFENCED_WRAPPER_CLASSwrapper 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
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
TIMEOUT server options specified in the
CREATE SERVER statement, as shown in Table
Table 3. LDAP Server options
This is the connection URL for the LDAP directory data source server
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
URLoption to connect to a blank-separated list of hosts, and each host can optionally use the form
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=comthat 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.
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
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
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.
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
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,
a keyword that identifies the current user, not a username of
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 options||Description|
Type: Non-zero length String
The remote user ID that can be used to connect to the LDAP directory server
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:
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 type||LDAP attribute data format|
|Any LDAP attribute with string syntax in nature|
|An LDAP attribute that has string data with a floating-point number format|
|LDAP attribute that has string data with a decimal/numeric format defined in the nickname|
|LDAP attribute with Generalized Time (220.127.116.11.4.1.1418.104.22.168.24)|
|LDAP attribute with Generalized Time
The LDAP wrapper extracts the DATE part of LDAP attribute value and converts it to DB2
|LDAP attribute with Generalized Time
The LDAP wrapper extracts the TIME part of LDAP attribute value and converts it to DB2
|Any 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
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
A Distinguished Name specifies the base object in the DIT for LDAP search.
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
Specifies how deep within the DIT to search from the base object. The value can be one of the following choices:
Restricts the number of entries returned from the search.
The default value is
Limits the total time of the search in minutes.
The default value is
Specifies whether alias objects (as defined in
Default value is
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 NICKNAMEstatement to change the
DNvalue before querying
- Use the
ldap.search_dnfunction in predicates to overwrite the
DNvalue 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;
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
Specify what type of entries can be retrieved. For example: The search context contains entries from structural object class
Department. Therefore, when you search for a nickname, depending on how the
OBJECTCLASSis specified in the
CREATE NICKNAMEstatement, either the
Departmentobject class instance is returned.
Enforce the nickname column names. The column names in the
CREATE NICKNAMEstatement 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_NAMEcolumn 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');
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
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: email@example.com
The objectClass called
person is structural,
so it has attributes of
The objectClass called
contact is auxiliary,
so it has an attribute called
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
allows multiple values with the semicolon
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');
You can set the
SUBTREE search scopes for each LDAP search
query. You can then change the
in the following two ways when querying a nickname.
You can use the
ALTER NICKNAMEstatement to change the
SCOPEvalue before querying.
You can use the
ldap.search_scopefunction in the predicates, so that the
ldap.search_scopefunction overwrites the
SCOPEvalue specified in the nickname. For example, Listing 21 shows how to query with the
Listing 21. Query with ldap.search_scope
SELECT * FROM Department WHERE LDAP.SEARCH_SCOPE (Department.name,'SUBTREE') = 1;
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
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
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
TIME_LIMIT to set the time limit
parameter for an LDAP search.
If you set too high of a limit, then the
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
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
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
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
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
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:
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
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:
You must enter a search term column argument and a query term argument
in custom functions. The following example shows an
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
<> 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;
NOT (ldap.eq (col,value) = 1)
is equivalent to
ldap.eq (col,value) <> 1.
The relational predicates, such as
<>, 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
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
Table 7. Examples showing an SQL query conversion to an LDAP search filter
|Description||SQL||Converted LDAP query filter|
|Query without predicates|
|Query with relational predicates|
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
is defined with a non-string type column that cannot use
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
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
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
integer, then returns it to the federated
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
<>) that are specified in the
nickname columns. The LDAP directory data source server engine
processes predicates that use LDAP custom functions for the search
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
which is a predefined nickname on the InfoSphere Federation Server.
Listing 36. Nickname
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
Listing 37. Nickname
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
Listing 38. Join nickname
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.
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.
|LDAP Wrapper for this article||LDAPWrapper.zip||34KB|
- "Using data federation technology in IBM WebSphere Information Integrator: Data federation design and configuration" (developerWorks, June 2005): Discover federated data concepts and read about design and configuration considerations for WebSphere Information Integrator (the former product name of InfoSphere Federation Server).
- "Using data federation technology in IBM WebSphere Information Integrator: Data federation usage examples and performance tuning" (developerWorks, June 2005): Learn about federated query optimization, find usage examples, and familiarize yourself with performance tuning considerations for WebSphere Information Integrator(the former product name of InfoSphere Federation Server).
- Access the DB2 Information Center for the most current documentation for InfoSphere Federation Server.
- Download PDF versions of the InfoSphere Federation Server documentation.
- "Develop user mapping plug-ins for WebSphere Federation Server" (developerWorks, April 2008): Explore how to configure user mapping plugin in WebSphere Federation server.
- InfoSphere Federation Server product page: Learn more about InfoSphere Federation Server.
- Tivoli® Directory Server product page: Learn more about Tivoli Directory Server.
- The JNDI Tutorial. Rosanna Lee.
- The IBM Redbooks document Understanding LDAP - Design and Implementation"
- In the developerWorks Information Management zone, learn more about IBM Information Management. Find technical documentation, how-to articles, education, downloads, product information, and more.
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®.
- Participate in the discussion forum.
- Check out developerWorks blogs and get involved in the developerWorks community.