As you mention, Designer does not have an importer for LDAP metadata, so to create a model for an LDAP data source, you can do it manually.
Here's a step-by-step of what you'll want to do:
1. Create a new relational source model.
2. Add a new table -- this will represent a base DN in LDAP. The following property should be set:
- Name In Source: Enter the base DN, e.g. ou=people,dc=company,dc=com. Queries against this DN will search the entire DN subtree by default.
3. Create columns in the table, representing attributes in LDAP. The following properties should be set on the attribute:
- Name In Source: Enter the LDAP attribute name, e.g. "cn".
- Type: You'll typically want to enter String here
- Length: Provide an approximate maximum string length
You can then add the model to a VDB and create an LDAP connector binding to test it out. The binding will require the LDAP URL, username, and password of someone that has proper permissions to access whatever you've modeled.
Then, execute the VDB and try it out. SQL queries will be translated into LDAP searches, and the results will be returned in a relational format. Anything that LDAP does not natively support will be taken out of the search, and performed by Teiid as a post-processing step.
If you intend on joining these LDAP-based tables to other data sources, and want to improve performance, you can manually gather a few statistics and add them as properties to the table/columns. This will allow the cost-based optimizer to make better decisions. The properties are Cardinality (on the table), and Distinct Value Count, Null Value Count, Minimum Value, and Maximum Value (on the columns). These attributes are purely optional.
Thanks Michael. It worked.
I have tried the above steps mentioned for generating the model from ldap metadata but I am getting the error :-
2012-08-06 15:50:55,349 WARN [org.teiid.CONNECTOR] (Worker9_QueryProcessorQueue37) Connector worker process failed for atomic-request=HLkNerJ7/Mty.0.0.9
[TranslatorException]Unable to get managed connection for PREVIEW_22348dd2-d111-430c-9a3e-8e95c2d5f744_EWI_Party_Reference_DOLNPCR1
1 [ResourceException]Unable to get managed connection for PREVIEW_22348dd2-d111-430c-9a3e-8e95c2d5f744_EWI_Party_Reference_DOLNPCR1
2 [ResourceException]Initializing LDAP directory context failed. Please check LDAP connection properties, including username and password: [LDAP: error code 49 - Invalid Credentials]
Caused by: javax.resource.ResourceException: Unable to get managed connection for PREVIEW_22348dd2-d111-430c-9a3e-8e95c2d5f744_EWI_Party_Reference_DOLNPCR1
... 14 more
Caused by: javax.resource.ResourceException: Initializing LDAP directory context failed. Please check LDAP connection properties, including username and password: [LDAP: error code 49 - Invalid Credentials]
... 19 more
2012-08-06 15:50:55,380 WARN [org.teiid.PROCESSOR] (Worker8_QueryProcessorQueue38) Processing exception 'DOLNPCR1: Unable to get managed connection for PREVIEW_22348dd2-d111-430c-9a3e-8e95c2d5f744_EWI_Party_Reference_DOLNPCR1' for request HLkNerJ7/Mty.0. Exception type org.teiid.core.TeiidProcessingException thrown from org.teiid.resource.adapter.ldap.LDAPConnectionImpl.initializeLDAPContext(LDAPConnectionImpl.java:138). Enable more detailed logging to see the entire stacktrace.
I have double checked the credentials, they are okay.
What exactly do u mean by the steps 2-3, what it is doing,can u please elaborate....
Teiid Designer does not have a importer for LDAP based source, so you can *not* create model for LDAP automatically. What Michael suggesting above is how to create a relational representation of LDAP. For that you need to create a "source model" in Designer, and then follow his instructions above to create the tables.
The error above indicates the issue you are having when you are trying to preview the data from LDAP. What you need to do is once you create the above model, you need to create a data source for that source model where it can read the data from. I see you did this step, but something is wrong. You can create a datasource, right click -> modeling->assign/create data source on the source model.
Ditto on everything Ramesh said.
The stack trace indicates that your LDAP server is responding with LDAP error code 49 - invalid credentials. This comes directly from the LDAP server, so it's a certainty that your credentials are not correct in some way.
You mentioned you've checked the username and password. You should also double-check all the other properties, including the context name, server name, etc. It's possible that the username/password are correct but you're not using the right context for those credentials. Something in the combination of properties is not passing LDAP authentication. Trace-level stack trace could give more information.
Hi Ramesh and Michael,
I have done exactly what was mentioned and created the data source as what Ramesh mentioned earlier also but getting the same error. As far as the LDAP properties are concerned which I am using were :-
username and password (double checked it)
Context Factory Name - com.sun.jndi.ldap.LdapCtxFactory
Connection Url - ldap://"host":"port"
There is one more question when I tried to connect to Oracle db(not from ldap) by importing metadata from jdbc I am always getting the error " java.lang.ClassNotFoundException: oracle.jdbc.OracleDriver from BaseClassLoader"..but I have provided the ojdbc14.jar while doing connection profiling and I have extracted the jar also to check whether it contains OracleDriver class or not and it does contains .The steps which I followed was import -> metadata from jdbc->connection profile -> selected desired table to import -> finish -> preview data. The error logs are attached.
What could be the possible reason. Is there anywhere that I have to provide the ojdbc14.jar.
what I am not able to understand is why during testing the connection using the above jar, it says ping succeeded and creates the connection and why during the previewing of the data it is not able to load the OracleDriver class from the above jar.
errorLogs.txt.zip 1.7 K
For Oracle issue, I have an explanation and solution.
The Teiid Designer and Teiid Server are running in separate VMs, when you create a connection to import the metadata in Designer, it is adding the ojdbc14.jar to the Designer VM and it creates the source model with metadata and ping succeeds. Any query to for preview of results on that model goes to the Teiid Server in a different VM. Undercovers Designer deploys the source model created to Server for querying purposes. There the Server again needs a connection to source in the form of -ds.xml file. Designer can create these -ds.xml files automatically through management APIs, however it can not deploy the jar files for the Oracle driver. This is a manual exercise for now. So, if you copy your ojdbc14.jar to the "<jboss-as>/server/default/lib" directory and restart the server then everything should work fine. Note you only need to do this once, there upon if you create any more oracle connections they work seamlessly. For this reason we recommend users to copy all their JDBC or any dependent jar files into the "lib" directory before they start out with Designer. We hope to make enhancement in this area with Teiid Designer 8.x.
Your LDAP issue separate from this, as the core classes are provided with the JDK. For this, I suggest you write a simple java class to test out the connection semantics out side the Teiid. If it works there with above credentials and not working in Teiid then we can take a closer look.
Sarik, one easy way to validate whether your LDAP credentials and connection information are correct is to use a third-party LDAP client to test them out. Softerra makes a free one:
Use that to connect to your LDAP server outside of Teiid, and see if you can successfully connect using the same settings. If not, then your settings are wrong.
It is easy to get the username incorrect -- keep in mind this must be the full DN of the user with appropriate access rights on the LDAP server. For example, "uid=admin,ou=system" would be a valid username.
Finally, as mentioned, trace-level logging during the connect attempt will provide more information about the settings used, which would be useful for debugging.
Finally it(Oracle problem) worked.. Thnx a ton mate.
If you don't mind I have another ques.. Lets say I have made 2 relational sources (one for the oracle db having table A and one for the sybase db having table B )in a project.
eg-> first_source.xmi has trade(db) has A table
second_source.xmi has reference(db) has B table
Now I created a view from the relational model first_source.xmi and within its "edit transformation" I have a query "select * from first_source.trade.A"(this is working fine). Now within this transformation If I modify sql query as to join A and B table from different db(like select a.x, b.y from first_source.trade.A a, second_source.reference.B b where a.id = b.id ), does it work?? becoz While I am doing this logging is showing group second_source.reference.B b doesn't exist and ending in "[SingleInstanceCommunicationException]". Am I doing joining of different tables wrong(i think so yes as I have created the view the view from the first relational model), If yes then what is the right way??
As far as Ldap problem is concerned, I will try and run it locally to check the properties which I am using...
Absolutely!, that is what a Data Virtualization system like Teiid can provide.
You are running into scoping issues from one model to other. When you imported from Oracle (first_source.xmi) say this ModelA, and second import from Sybase (second_source.xmi) ModelB. These are called "source" or "physical models, that represent the database schema in your source. These source models can only access the tables from within the same model. Just like your source Oracle system can only access the tables within its database. To solve the issue, you need to create yet another model we call "virtual" or "view" model using "new->Metadata Model -> select view model type". On this model, if you create a "base table" or "view" table and provide the above mentioned sql query that shows the join between A and B tables in its transformation window, you will get the joined data you are looking for.
Yes, it is working now (getting a taste of teiid now). just one thing again, I am able to get the data from three different sources, but it is actually taking sometime(anywhere b/w 40 - 60 secs) in order to fetch nearly 500 rows, and sometimes it shows "java.util.concurrent.TimeoutException" after 3-4 mins when the query somewhat modified to to have sub queries. So what can be the possible reason for this, how teiid is actually utilizing 3 different sources.
It is not typical for Teiid to take that long to get results normally, it really depends upon on the type user query you are submitting and type of joins you used in your view table transformations and number rows you are fetching from sources etc. First thing is taking a look at the query plan and seeing how Teiid is is executing. Teiid has many optimizations that make sure it sends a optimal query to source for fetching the results from source before it is joining the data from other sources. You can have the server in DEBUG mode and see the source queries that Teiid is submitting to the sources and how long they are taking. Based on that you can further tune your join semantics or provide criteria on source to limit the number of rows returned to increase the speed. Also there are many hints you can supply to optimizer depending upon situation. Also, you can supply costing information on tables to produce better pushdown queries. so, there is no one thing I can tell you that will be the answer. Please check out the Reference Guide for more details.
I agree to what u said. The thing is I just fired up a simple query
Select a.x,a.y from A a , B b where a.id = b.id ,where A is a table from oracle db and B is a table from Sybase db. But it runs for 4-5 mins and finally ends up with "java.util.concurrent.TimeoutException".