Appendix B: Oracle TNS Names

Working with Oracle TNS

An RDB entry for Oracle can be defined using the ORACLE_TNS driver. There are two methods for specifying the JDBC URL; inline on the System i or by referring to a TNS entry name in the external file (tnsnames.ora).

Inline Method

To define the RDB for Oracle, add a new Directory Entry (as shown in Creating a New Directory Entry

Ensure that the URL entry, as entered on the Modify DIrectory Entry - Part A screen, is of the form:
jdbc:oracle:thin@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=1.1.1.221)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=XE)))

External File Method

If you are running in Mode 1 - Inline or Mode 2 - Internal Server, you can find the available TNSNAMES entries listed in the tnsnames.ora file in the IFS of your IBM i.

If you are running in Mode 3 - External Server, they are listed on the client computer from which you are connecting.

The file is located and loaded upon an attempt to connect the RDB entry. The location of the file is written to the IFS file /iSecurity/DB-Gate/ sp.properties

for example, the default location of the tnsnames.ora file is:

oracle.net.tns_admin=/iSecurity/DB-Gate

If you move it to another location, you must also update the sp.properties file.

After modifying the sp.properties file in either mode, you must restart DB-Gate. If inline run mode is used, any new SQL job will be affected.

Below is an example of the tnsnames.ora file:

PROD=

(DESCRIPTION = (ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.221)(PORT =

1521))

)

(CONNECT_DATA = (SERVICE_NAME = XE)

)

)

TEST=

(DESCRIPTION = (ADDRESS_LIST =

(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.21)(PORT=1521)) (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.22)(PORT=1521))

) (CONNECT_DATA=

(SERVICE_NAME=XE2)

)

)

In the Modify Directory Entry - Part A screen, set the URL field to either jdbc:oracle:thin:@PROD or jdbc:oracle:thin:@TEST as appropriate.