How to configure Teiid to offer JDBC services

[ralfr68]

Dear all,

We have installed "Teiid 8.13.3 with WildFly" on a fresh Red Hat Enterprise Linux 6.7 Linux server with JDK 1.8.0_74, and have applied the following changes:

• Copy standalone-full.xml to myconfig.xml
• Edit myconfig.xml to allow non-local admin access, since there is no browser on the server
• Add an admin user using bin/add-user.sh
• Start the server using bin/standalone.sh -c myconfig.xml
• Verify that the admin interface is available under http://servername:9990/
• Downloaded ojdbc7.jar from oracle and deployed it using bin/jboss-cli.sh
• Added a new datasource using bin/jboss-cli.sh
• Added a VDB file to standalone/deployments and deployed it by creating a *.dodeploy file
• Verified there are no errors in the log
• Verified the new datasource is shown in the admin interface

So far, so good. Now I want to access the data through jdbc. I have installed SQuirreL on my local PC and downloaded and installed the teiid-jdbc driver. To connect it to the Teiid server, I need to know:

• Where do I define username and PW for JDBC (in Teiid, not in SQuirreL)?
• Where do I define the port (again, in Teiid)?
• In other words, where do I specify the logical database view that Teiid "exports" to the world?

Some docs tell something about defining a "TEIID-DS" datasource, but I was unable to find any details on that. On the Teiid server, i used netstat -atn to list all listening ports. When the server is running, only the ports 8080 and 9990 are added to the list of sockets in LISTENING state, and both are used for http based management. In other words, this server will never accept any JDBC connection, regardless which username or port I will specify.

I bet this has something to do with the <socket-binding name="teiid-jdbc" port="31000"/> entry I found in another reference installation, but which is missing in my new server. But only adding this entry does not change anything.

What am I missing here? How to install or activate the teiid-jdbc part?

Thanks for any hint!

Ralf

[rareddy]

Ralf,

I am guessing you missed some important steps. The default configuration "standalone-full.xml" does NOT contain the Teiid subsystem. We have another configuration file called "standalone-teiid.xml" that has all required properties to install Teiid and its components. If you want to use your current "myconfig.xml" then you can also add Teiid configuration on top it using the scripts in the "bin/scripts" directory, these are CLI scripts that you can run using the jboss-cli.sh, upon successful completion you will see the Teiid system come alive inside the WildFly. There are installation steps here https://teiid.gitbooks.io/documents/content/admin/Installation_Guide.html

Now your process of deploying the JDBC driver for Oracle looks right, now you said you deployed the VDB fine too. When you deploy the VDB you will see a message like "MYVDB.1" is ACTIVE, that signals that you have successfully deployed a VDB. There are other ways t find out too, but this is simple, quick.

Now, to access to VDB will be just like any other database. You provide the user, password on the connection URL, in your case in SquirreL. By default the port is 31000 of JDBC, 35432 for ODBC. When a VDB is deployed it is also available through ODATA using your web browser. Please JDBC and other connection details here https://teiid.gitbooks.io/documents/content/client-dev/Connecting_to_a_Teiid_Server.html

Teiid documentation is pretty extensive you should be able to find most of the stuff, if do not see something please be sure let us know.

Thanks. Hope this helps

Ramesh..

[m.ardito]

Also, you already defined an admin user, which is needed to access the web console, but (in the simplest case) you need also an application user (use the same tool to create, as you did for admin user, choose the other option)

That is the user/password you need to provide do squirrel and other tools (also odbc and odata, iirc). But there many other authentication options possible

Marco

[ralfr68]

Ramesh,

and Marco,

thank you very much for your valuable responses! With your help, I was able to configure Teiid to work as expected.

Keep up the good excellent work!

Ralf