search cancel

How Autosys Configures the Oracle Database Connection Properties

book

Article ID: 42154

calendar_today

Updated On:

Products

CA Workload Automation AE - Business Agents (AutoSys) CA Workload Automation AE - Scheduler (AutoSys) Workload Automation Agent

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

Release: ATSYHA99000-11.3.6-Workload Automation AE-High Availability Option
Component: