Hi,
In my situation, I have
- IBM Informix Dynamic Server 11.5 64b database (AIX OS)
//Informix database is not in transaction logging mode
- Oracle 11gR2 EE 64b database (Windows OS).
I need to have nearly real time data integration from the Informix to Oracle DB. I need to import some tables data from Informix db to Oracle db minimum two times at day.
For this on the separate server (Windows) I did this:
- install Oracle Database Gateway for ODBC 12c,
- configure ODBC System DSN
- install Informix Client Software Development Kit 4.10.FC6DE,
- create SetNet32 Informix connection with Protocolname: onsoctcp,
- with ODBC Data Source Administrator I create System DSN for Informix connection and connection with Informix db was successful
3. I configure Gateway listener:
LISTENER=
(DESCRIPTION_LIST=
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=GATE.mydomain)(port=1521)))
)
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=dg4odbc)
(ORACLE_HOME=C:\app\tg\Administrator\product\12.1.0\tghome_1)
(PROGRAM=dg4odbc)
)
)
4. On the database server I add entry on tnsnames.ora:
dg4odbc=
(DESCRIPTION =
(ADDRESS=(PROTOCOL=TCP)(HOST=GATE.mydomain)(port=1521))
(CONNECT_DATA =
(SID = dg4odbc)
)
(HS=OK)
)
I successful test this with tnsping dg4odbc
5. On the gateway site I set initdg4odbc.ora
HS_FDS_CONNECT_INFO = informix //my ODBC System DSN
HS_FDS_TRACE_LEVEL = OFF
6. On the database I create database link:
create database link INF connect to "informix_username" identified by "informix_pass" using 'dg4odbc';
When I test the database link I get this errors:
The Database Link is not active.
ORA-28500: connection from ORACLE to a non-Oracle system returned this message: [Informix][Informix ODBC Driver]Driver not capable. {HYC00,NativeErr = -11092} ORA-02063: preceding 2 lines from INF
Despite the error I can select data from Informix data:
select "col1" from "informix_table"@inf
But when I try to commit inserted data I get the same error:
insert into ora_table (select "col1" from "informix_table"@inf
where "col1" = 'TEST')
30 rows inserted.
COMMIT;
Error report -
SQL Error: ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Informix][Informix ODBC Driver]Driver not capable. {HYC00,NativeErr = -11092}
ORA-02063: preceding 2 lines from INF
- 28500. 00000 - "connection from ORACLE to a non-Oracle system returned this message:"
*Cause: The cause is explained in the forwarded message.
*Action: See the non-Oracle system's documentation of the forwarded
message.
If you have any other solution please suggest me.
Thanks for any reply, I really need some help...!!