Home Services Partners Company
Oracle Timesten Database Gateway for ODBC for realtime downstream connectivity

Oracle Timesten Database Gateway for ODBC for realtime downstream connectivity

April 21, 2014 0 Comments

Oracle TimesTen is an in-memory database that can be used as the core database of record or as a cache database for Oracle.  When used as a core database for data intensive workloads, we're seeing significant demand to setup an Oracle Database Gateway to federate TimesTen data for downstream applications, including those built on Oracle.

Downstream with Oracle TimesTen without a paddle

If you search Oracle Support, you'll find that the Oracle TimesTen client by itself is not supported with the Oracle Database Gateway for ODBC per Oracle Support, "Connecting Oracle to Timesten Using DG4ODBC in Linux x86 and x86-64 (Doc ID 1444607.1)".  This is because the Oracle Database Gateway for ODBC (DG4ODBC) 11g is ODBC V3 while TimesTen ODBC-Driver/Manager only supports ODBC V2 calls.

The result is typically an error from the HS trace such as: Failed to load ODBC library symbol: /u01/app/TimesTen/tt1122/lib/libtten.so(SQLAllocHandle)+

DataDirect Unix/Linux driver manager for Oracle TimesTen to the rescue

DataDirect is the only commercial ODBC Driver Manager recommended by Oracle per Doc ID 813942.1, and our Unix/Linux driver manager is able to support the Oracle TimesTen client from the Oracle Database Gateway, as well as other downstream applications including other real-time database federation technologies like Sybase ECDA.  The driver manager can also load the Oracle TimesTen client directly from other non-Oracle business applications including SAS/Access, IBM Cognos, or SAP Business Objects.

Oracle TimesTen Gateway

Oracle TimesTen Gateway

 

Setup the Gateway to Oracle TimesTen

1. Assume Database Gateway for ODBC (DG4ODBC) is installed (article is for Linux x64).  DG4ODBC is included with Oracle Databases, or available for download from Oracle.com if you want to set it up on a separate tier.

2. Download the Connect for ODBC Unix/Linux Driver Manager which is included in the standard DataDirect Connect64 for ODBC package and comes included with any ODBC driver installation.  Verify the version is 07.13.0081 (U0075) or higher by running the ddtestlib utility included with the install package on the driver manager library, libodbc.so.

3. Configure Database Gateway for ODBC using our white paper.

4. The specific steps include adding the DataDirect Unix/Linux driver manager path and file "libodbc.so" (or odbc.so on AIX) to the HS_FDS_SHAREABLE_NAME initialization property configured in the .  The location of the shared library path to the driver manager library should also be added to the ENVS variable in the $ORACLE_HOME/network/admin/listener.ora file.

5. Below are example data sources to add to the odbc.ini installed with DataDirect, or sys.odbc.ini installed with the TimesTen client:

[ODBC Data Sources] Oracle Wire Protocol=DataDirect 7.1 Oracle Wire Protocol cachedb1_cs=DataDirect 7.1 TimesTen
[cachedb1_1122] Driver=/home/oracle/11.2.2.5.1/TimesTen/tt112251/lib/libtten.so DataStore=/home/oracle/11.2.2.5.1/TimesTen/tt112251/info/cachedb1_1122 PermSize=40 TempSize=32 PLSQL=1 DatabaseCharacterSet=AL32UTF8

6. Create the $ORACLE_HOME/hs/admin/init<sid>.ora file as follows:

HS_FDS_CONNECT_INFO = cachedb1_1122 HS_FDS_TRACE_LEVEL = DEBUG HS_FDS_SHAREABLE_NAME = /opt/Progress/DataDirect/Connect64_for_ODBC_71/lib/libodbc.so
set ODBCINI=/opt/Progress/DataDirect/Connect64_for_ODBC_71/odbc.ini

7) Modified the listener.ora file under $ORACLE_HOME/network/admin to include following service

(SID_DESC = (SID_NAME =dg4odbc) (ORACLE_HOME =/u01/app/oracle/product/11.2.0/dbhome_1) (PROGRAM =dg4odbc) (ENVS=LD_LIBRARY_PATH=/opt/Progress/DataDirect/Connect64_for_ODBC_71/lib/libodbc.so: /u01/app/oracle/product/11.2.0/dbhome_1/lib:/u01/app/oracle/product/11.2.0/dbhome_1/network/lib: /home/oracle/11.2.2.5.1/TimesTen/tt112251/lib) )

8) Modify the tnsnames.ora file under $ORACLE_HOME/network/admin to include following tns entry

CACHEDB1= (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = shoe-tt)(PORT = 1521)) (CONNECT_DATA = (SID = dg4odbc) ) (HS = OK) )

Use tnsping CACHEDB1 to check whether there is any issue or not. It should be ok.

9) Create and test the database link as follows:

SQL> CREATE DATABASE LINK TT CONNECT TO :"user" IDENTIFIED BY "password" USING 'CACHEDB1';
SQL> select * from ttuser.test@TT;
A ---------- 1 2 3

Got data connectivity questions?

Call 1-800-876-3101 to speak with a Systems Engineer to learn how other organizations are making progress with real-time gateway connectivity to Oracle TimesTen, as well as other data sources for which we supply both the driver manager and ODBC driver including SQL Server, DB2, Salesforce, Hadoop Hive, MongoDB, Cloudera Impala and more.

Sumit Sakar

Sumit Sarkar

Sumit Sarkar is a Chief Data Evangelist at Progress, with over 10 years experience working in the data connectivity field. The world's leading consultant on open data standards connectivity with cloud data, Sumit's interests include performance tuning of the data access layer for which he has developed a patent pending technology for its analysis; business intelligence and data warehousing for SaaS platforms; and data connectivity for aPaaS environments, with a focus on standards such as ODBC, JDBC, ADO.NET and ODATA. He is an IBM Certified Consultant for IBM Cognos Business Intelligence and TDWI member. He has presented sessions on data connectivity at various conferences including Dreamforce, Oracle OpenWorld, Strata Hadoop, MongoDB World and SAP Analytics and Business Objects Conference, among many others. 

Read next SAS ODBC Access to Salesforce, SQL Server and Marketo
Comments
Comments are disabled in preview mode.