ODBC DataFlux Connection Error, Mondash

[SAS_LuisBolivar]

Good morning colleagues.

I am presenting the following situation:

For performance and policy reasons we need to change the Mondash schema database engine, by default it is PostgreSQL and we must change it to ORACLE.

The first thing we must adapt is the connection from the client and for that we make the following modifications:

Inside the mondash.cfg file we update the connection string

Using the DataFlux 32-bit Oracle Wire Protocol driver as follows:

MONDASH/DSN=Driver=DataFlux 32-bit Oracle Wire Protocol;DFXDOMAIN=xxxxx_ _Auth;DB=oracle;Host=xxxxxxx.com;Port=1521;DFXTYPE=ODBC

Result: [DataFlux][ODBC Oracle Wire Protocol driver]Insufficient information to connect to the data source.

Using an Oracle driver installed on the client machine:

MONDASH/DSN=Driver={Oracle in instantclient_12_2};DFXDOMAIN=xxxxx_ _Auth;DB=oracle;Host=xxxxxxx.com;Port=1521;DFXTYPE=ODBC

Result: [Oracle][ODBC][Ora]ORA-12560: TNS:protocol adapter error

Does anyone have experience with these types of errors?

Thanks.

[Sajid01]

Looks like SAS is unable to connect to ORACLE. Try tnsping from the command line.
In any case your oracle dba should be able to resolve the issue. 
I hope you have the instant client installed and configured on the dataflux/SAS Server.

[AhmedAl_Attar]

@SAS_LuisBolivar 

If you have installed Oracle Client Software, you'll need to configure to point to your Oracle Database instance. You'll need to find a file called tnsnames.ora and updated it, by entering 

<MYSID>=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = <mydnshostname>)(PORT = <DB_Port>))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = <MYSID>)
    )
  )

PS. Confirm with your Oracle DBA on the values for <MYSID>, <mydnshostname>, <DB_Port>

Check this web site https://www.orafaq.com/wiki/Tnsnames.ora for more info about the tnsnames.ora file 

Hope this helps,

Ahmed 

[AhmedAl_Attar]

Here is another link https://www.microfocus.com/documentation/enterprise-analyzer/ea602/EA/GUID-1D791A6B-4B9F-457D-B617-A...
This is more related to configuring ODBC connection to Oracle

[SAS_LuisBolivar · Accepted solution]

Greetings.

In the end I managed to solve applying the following steps:

1. Delete the connection established by the modash macro.
2. Add Mondash odbc connection to DataFlux.
3. In the echo transformation, add the variables required by the process, such as DB_DSN, DB_SCHEMA, etc.
4. The SQL process that deletes the data from the current repository was changed to a process that executes SAS code.
5. The other processes run without any adaptation.

Thanks.