The answers to your questions:
Question 1) How do I test the connection between the Gateway and DB2, without using TESTSID? => you can only test the connection between the Oracle gateway and a DB2 with an Oracle database.
Question 2) How do I test the connection between TESTSID and the Gateway, without using DB2? => use tnsping
In general there are depending on the error message several ways to test the connection. The first point you have to check all the time is the listener. The listener status will show you on which IP address/hostname and port it is listening on. This information is required for the tnsnames.ora entry. When the address information in the tnsnames.ora is now correct and the listener is up and running, a tnsping should come back with a successful message.
There is no way to test with tnsping the SID an Oracle listener is serving, so here the only way to check if the SID information in your tnsnames.ora matches your listener is to test the connection.
You're seeing a No listener error (ORA-12541) so that means the address information in your tnsnames.ora doesn't match your listener.ora. If you're sure the info is correct, then check that you do not have a firewall between both machines that blocks the connection.
Can you post the following -
- tnsnames.ora you are using to connect to the gateway listener
- the listener.ora of the gateway listener ?
- the create database link statement from the TESTSID RDBMS
- what is the erro you get from tnsping ?
- what is the erro you get when trying a select using the database link ?
To try and answer your questions -
Question 1) How do I test the connection between the Gateway and DB2, without using TESTSID?
- there isn't really a way to do this. The gateway executable tries to make the connection to DB2 once it is called from a database link. You can check the following -
- that the gateway machine can ping the machine where DB2 is running
- the port define in HS_FDS_CONNECT_INFORMATION is correct
- the DB2 database name in the connect information is correct
Question 2) How do I test the connection between TESTSID and the Gateway, without using DB2?
Make sure of the following -
- you can ping the machine where the listener is running
- you can tnsnping the gateway listener
- the gateway listener is running and the gateway services are available, run -
lsnrctl status <gateway_listener_name>
lsnrctl services <gateway_listener_name>
As you have an SR with Support I would expect them to be able to resolve your problem as it looks like some sort of configuration error.