Saturday, March 8, 2008

Using non-ITM data in the TEP

Page Two.

The TEP can be used to display non-ITM data using ODBC provders, this example will show a simple scenario where we will display such data.

First thing is to establish an ODBC entry from your TEP server to the database you want to query. Let's say we are going to query a MySQL database from the OpenESM toolset. This database table named "TRIGGERS" has the following schema:

HOSTNAME
TRIGGER
TIMESTAMP
STATUS
UID

Using the MySQL for Windows ODBC drivers, we create the entry - then we need to tell TEP about it.

The TEP only recognizes data sources that have been defined to it. This definition is in the "KFWENV" file. Add an entry for your data source like this one:

DSUSER1="DSN=ODBC-NAME;UID=USER-NAME;PASSWORD=PASSWORD"

After this is added, you must recycle your TEP server.

With this entry in place you can create new Queries in the Query Editor, create one called TEST. When creating a custom query like this, YOU have to specify the SQL. So in our case here, the SQL statement would like:

select * from TRIGGERS where STATUS=ON

This will return any record where the TRIGGER STATUS is ON. By default the query returns the daya into a TABLE view - this may be appropriate.

Want more??? Keep reading.

2 comments:

Scott said...

I added the ODBC connection, added to kfwenv file and restarted. But it doesn't show up in TEP.

I'm wondering if I have to add to another file?

Here's my entries near JDBC Data Warehouse:


DSUSER1="DSN=ourdsn;UID=dbuser;PWD=ourpwd"
JVM__CTJDBC__CLASSPATH=D:\IBM\ITM\CNPS\tepjdbc.jar;D:\IBM\ITM\CNPSJ\derby\lib\derby.jar
JVM__CTJDBC__CLASS=com.ibm.TEPS.CTSQLJDBC.CTSQLJDBCModule

Venkat said...

Scott,

I think the ITM behavior changed since the article was posted. Even though, the steps work fine, I am getting database connection errors due to TEPS expecting the credentials in encrypted form. I have posted an updated blog article describing how to do it.

http://blog.gulfsoft.com/2012/03/using-non-itm-data-in-teps-updated.html

Hope this helps,
-Venkat