How Autosys Configures the Oracle Database Connection Properties
Article ID: 42154
Updated On:
Products
Issue/Introduction
Workload Automation AE (Autosys)
How Autosys Configures the Oracle Database Connection Properties
Introduction Summary:
This document explains how the database connection is determined and established between Autosys and Oracle.
Background:
It’s not clear how Autosys is configured to connect to the database as there isn’t a setting or configuration which controls it. The connection is determined based on the presence of the TNS_ADMIN environment variable and based on this, the connection type and details are determined.
Environment:
This document applies to Autosys 11.3.X and higher.
Instructions:
To establish a connection with Oracle, Autosys and its components utilize the EventServer definition in the Autosy config file (config.HAL). Then depending if $TNS_ADMIN is set, it will use the values of EventServer for the parameters in the connection string.
Autosys Configuration File
In this example, the Autosys instance is HAL, Oracle ServiceName is HA1136, Port is 1521 and Host is win-db2. EventServer holds the parameters needed to build the database connection string.
$AUTOUSER/config.HAL
EventServer_1=HA1136,1521,win-db2
EventServer=SERVICENAME,PORT,HOST
Oracle $TNS_ADMIN Environment Variable
Depending if $TNS_ADMIN is set, the appropriate database parameters are passed into the JDBC driver.
The $TNS_ADMIN variable needs to be set in the Autosys profile that is sourced in when the services are started.
If $TNS_ADMIN is set, Autosys …
· Uses the SERVICENAME as the ALIAS to select the matching entry from tnsnames.ora
· Passes in all the values from the tnsnames.ora entry to the JDBC driver
· JDBC Driver uses - oracle.tns.connection.string
if $TNS_ADMIN is NOT set, Autosys …
· Uses the SERVICENAME, PORT & HOST from the Autosys config file to establish a connect
· JDBC Driver uses - oracle.connection.string
Additional Information:
Oracle tnsnames.ora File
The formatting of the file is very important.
· Use Oracle Net Manager to make any updates to tnsnames.ora (Recommended)
· Each sub-line requires a two-space indention
· The Alias should NOT have any periods
· If an “Internal Exception: IO Error: could not resolve” message is seen, there is a likely formatting issue and the JDBC driver is not able to connect to the database.
Example: tnsnames.ora
$TNS_ADMIN/tnsnames.ora
HA1136 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = win-db2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = HA1136)
)
)
JDBC Driver Connection Properties File
The connection.properties file should remain as default as the JDBC driver will handle the different scenarios automatically. Making any updates or hard-coding values into this file is not recommended.
Example: connection.properties
$AUTOSYS/config/cmplapp/connection.properties
# *****************************************************************************
# COPYRIGHT CA 2011
# this Java property file contains the definition of different database
# connection properties
#
# *****************************************************************************
# *************************************
# connection configuration for Microsoft SQL Server
msql.driver.class.name=com.microsoft.sqlserver.jdbc.SQLServerDriver
msql.connection.string=jdbc:sqlserver://${servername}:${port};databasename=${dbname}
msql_trusted.driver.class.name=com.microsoft.sqlserver.jdbc.SQLServerDriver
msql_trusted.connection.string=jdbc:sqlserver://${servername}:${port};databasename=${dbname};integratedSecurity=true;
# *************************************
# connection configuration for Oracle Thin driver
oracle.driver.class.name=oracle.jdbc.OracleDriver
oracle.connection.string=jdbc:oracle:thin:@${servername}:${port}:${dbname}
oracle.tns.connection.string=jdbc:oracle:thin:@${dbname}
# *************************************
# connection configuration for SyBase
sybase.driver.class.name=com.sybase.jdbc4.jdbc.SybDriver
sybase.connection.string=jdbc:sybase:Tds:${servername}:${port}/${dbname}
Environment
Component:
Feedback
