Skip to main content

If you don't have an IBM ID and password, register here.

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

The first time you sign into developerWorks, a profile is created for you. This profile includes the first name, last name, and display name you identified when you registered with developerWorks. Select information in your developerWorks profile is displayed to the public, but you may edit the information at any time. Your first name, last name (unless you choose to hide them), and display name will accompany the content that you post.

All information submitted is secure.

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.

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

All information submitted is secure.

Using the LDAP wrapper with InfoSphere Federation Server

Leverage and integrate your directory data in an SQL environment

Yun Feng Sun (sunyunf@cn.ibm.com), Advisory Software Engineer, IBM
Sun Yun Feng
Yun Feng Sun is an Advisory Software Engineer at the IBM China Software Development Lab. He currently works for the development team on the IBM InfoSphere Federation Server product.
Eileen Lin (etlin@us.ibm.com), Senior Technical Staff Member, IBM
Author photo
Dr. Eileen Lin is a Senior Technical Staff Member in Silicon Valley Laboratory in San Jose, California. She is one of the original members responsible for the success of DataJoiner, a federated database product that is the predecessor of the federation technology in DB2. Currently, she is the lead architect for WebSphere Federation Server. Dr. Lin has many patents covering areas such as federation technology, query optimization and parallel query processing.
Art Kaufmann (artkauf@us.ibm.com), Senior Software Engineer, IBM
Art Kaufmann photo
Art Kaufmann is a senior software engineer working in information integration through federation and Web services. Art is technical lead for the SOA and Federation team responsible for the wrapper SDK.
Martin Klumpp (maklumpp@us.ibm.com), Technical Lead, Software Engineer, IBM
Martin Klumpp photo
Martin Klumpp is a technical lead for the InfoSphere FastTrack product and is located at IBM Silicon Valley Laboratory in San Jose, California. He participated in the LDAP wrapper design and development in 2005.
Xing Yu Liu, Software Engineer Intern, IBM
Xing Yu Liu photo
Xing Yu Liu was a software engineer intern at the IBM China Software Development Lab and was involved into the LDAP wrapper development.

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

Date:  23 Sep 2010
Level:  Introductory PDF:  A4 and Letter (106 KB | 32 pages)Get Adobe® Reader®

Comments:  

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

5 of 11 | Previous | Next

Comments



Help: Update or add to My dW interests

What's this?

This little timesaver lets you update your My developerWorks profile with just one click! The general subject of this content (AIX and UNIX, Information Management, Lotus, Rational, Tivoli, WebSphere, Java, Linux, Open source, SOA and Web services, Web development, or XML) will be added to the interests section of your profile, if it's not there already. You only need to be logged in to My developerWorks.

And what's the point of adding your interests to your profile? That's how you find other users with the same interests as yours, and see what they're reading and contributing to the community. Your interests also help us recommend relevant developerWorks content to you.

View your My developerWorks profile

Return from help

Help: Remove from My dW interests

What's this?

Removing this interest does not alter your profile, but rather removes this piece of content from a list of all content for which you've indicated interest. In a future enhancement to My developerWorks, you'll be able to see a record of that content.

View your My developerWorks profile

Return from help

static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management, Tivoli, Java technology
ArticleID=525159
TutorialTitle=Using the LDAP wrapper with InfoSphere Federation Server
publish-date=09232010
author1-email=sunyunf@cn.ibm.com
author1-email-cc=
author2-email=etlin@us.ibm.com
author2-email-cc=
author3-email=artkauf@us.ibm.com
author3-email-cc=
author4-email=maklumpp@us.ibm.com
author4-email-cc=
author5-email=dwinfo@us.ibm.com
author5-email-cc=

Tags

Help
Use the search field to find all types of content in My developerWorks with that tag.

Use the slider bar to see more or fewer tags.

Popular tags shows the top tags for this particular content zone (for example, Java technology, Linux, WebSphere).

My tags shows your tags for this particular content zone (for example, Java technology, Linux, WebSphere).

Use the search field to find all types of content in My developerWorks with that tag. Popular tags shows the top tags for this particular content zone (for example, Java technology, Linux, WebSphere). My tags shows your tags for this particular content zone (for example, Java technology, Linux, WebSphere).