Originally posted by: lgarripee

It's been a number of years since I accessed a db in a map. I wish to access a remote oracle database and get a nextval from a sequence. I assume I need to create a mdq file and reference it in my mapping rule.

I created a mdq with the following settings:

Adapter
Type: Oracle
Platform: Linux
Connect String
Database Interface Designer: datahubd
Runtime: datahubd
Security
User ID: myid
Password: mypassword

The DB is on a remote linux server and has the following entry in the tnsnames.ora file:

DATAHUBD = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = factorydatdev) (PORT = 1521) ) ) (CONNECT_DATA = (SID = datahubd) ) )

I don't appear to be connecting. How would I retrive a nextval within my mapping rule?

Thanks!
#DataExchange
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender

Originally posted by: TarunB

Do you have Oracle client installed on your TX machine?

Can you ping, and then tnsping, the Oracle machine? The ping will test the server/network connectivity and tnsping will check for the Oracle listener service.

What kind of error are you getting from the TX map and TX DB trace?
#IBM-Websphere-Transformation-Extender
#DataExchange
#IBMSterlingTransformationExtender

Originally posted by: lgarripee

Yes, I do have the oracle client installed on my laptop. This is the same machine where WTX resides.

I can successfully ping the oracle machine so the network connectivity is ok. However tnsping fails.

C:\>tnsping datahubd   TNS Ping Utility 

for 32-bit Windows: Version 9.2.0.1.0 - Production on 07-SEP-2011 10:22:15   Copyright (c) 1997 Oracle Corporation.  All rights reserved.   Used parameter files: C:\oracle\ora920\network\admin\sqlnet.ora   TNS-03505: Failed to resolve name

I will tell you I can access the datahubd database from my laptop with Oracle SQL Developer using a TNS connection type.
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender
#DataExchange

Originally posted by: TimUK

In the same directory as C:\oracle\ora920\network\admin\sqlnet.ora file on your laptop will be your local tnsnames.ora

In there you should put the tns entry from your original post. Then repeat the tnsping test and you should get a positive response.
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender
#DataExchange

Originally posted by: lgarripee

The tns entry from my original post is already in tnsnames.ora. That's where I got it from.

Thanks.
#IBMSterlingTransformationExtender
#DataExchange
#IBM-Websphere-Transformation-Extender

Originally posted by: TimUK

Sorry - I'd missed the last line in your previous response.

The issue may be then that Oracle SQL Developer is using a different tnsnames.ora from the one that tnsping is. This is often caused by multiple oracle homes (in my limited understanding)

To overcome it on my laptop I set the following environment variable on my windows command line before running tnsping or sqlplus.

set tns_admin=<directory location of tnsnames.ora>

Hope that helps
Tim
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender
#DataExchange

Originally posted by: TimUK

In answer to the original question there are a number of ways of doing this without the mdq file

=valid(get("DB","-DBTYPE ORACLE -CONNECT sid_value_from_tnsnames -USER userid -PASSWORD xxx -stmt 'select nextval from sequenecename'"), fail(lasterrormsg())
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender
#DataExchange

Originally posted by: lgarripee

Thanks Tim. I'll look into the tnsnames.ora file location. That sounds like a likely cause.

With regards to using or not using a mdq file. Is there a performance difference in the two methods?

I'll be retrieving a nextval for every record hit and am thinking your solution forces a db open -> login -> close iteration for each record. Would using a mdq file just do one open -> login iteration when the map runs and then each record would hit the sequence only?

Thanks again for your help.
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender
#DataExchange

Originally posted by: TimUK

Typically the Launcher will control the connection to the database and only disconnect it when the idledb/keepdb settings in the dtx.ini file are reached. I don't believe using an mdq file affects that.

The map would still enter the credentials each time, whatever the method and if that is an issue (for performance or other reasons) then a sequence isn't the right solution. an alternative would be a table with a number column that you cache at the start of the map and then increment by the appropriate amount at the end of the transaction. That has it's own problems if the map is multi-threaded.

Problems , problems!
#DataExchange
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender