Installing the Agent for Database Variables

The following guide describes the installation process of the database agent for Variable objects with the source SQL. This agent is also referred to as the DB Service Agent.

Notes:

• This installation guide can be used for Windows and for Unix.
• The agent for SQL variables (DB Service Agent) can access each supported database type. This type must be determined in a DB-type Connection object that can be assigned to Variable objects with the source SQL. In doing so, only one DB Service Agent is required for all SQL variables. For more information, see Connection (CONN).
  In this Connection object, you can also determine the database name and the connection parameters (such as Server name and port number). The agent is configured in the Automation Engine's INI file and not in its own INI file.
  The agent is only required for Variable objects with the source SQL. SQLI variables do not require an agent because they access the AE database directly via the Automation Engine.
• The DB Service Agent must be installed on the same computer as the Automation Engine.
  This includes that you can only log on to MS SQL Server databases via Windows authentication when the Automation Engine does not run on UNIX. The reason for this is that the library "sqljdbc_auth.dll" cannot be loaded under UNIX.
• The following guide describes how to install an agent in an AE system in which authentication is not used. Additional installation steps are required before the agent can be started and used if you intend to use one of the available authentication methods. For more information, see Agent Authentication.
• It is best to install the agent in a separate directory.

Supplied Files

The files that belong to the database agent are stored in the directory IMAGE:AGENTS\SQL.

Additionally, two empty folders (Temp and JDBC) are supplied. The Temp folder stores log files, and the JDBC driver must be installed in the JDBC folder.

Installing the Agent for Database Variables

1. Install the Java Runtime Environment (JRE) (admin and/or user computer).

   If JRE is already available in the required version, this step of the installation process can be ignored.

   • The Version of the current Java Virtual Machine (VM) of the system can be checked with the following command:

   java -version

   If several JRE or Java SDK Versions are installed on the computer, make sure that the order of the directories is correct in the settings of %PATH% or $PATH. The particular Java Runtime Environment that is found first in the list of directories is used.

   • Download the required Java Runtime Environment from http://java.sun.com and install it. The installation process includes the automatic installation of the Java Plug-in for Web browsers. You can deactivate it in the system control. AE does not need it.

2. Install the agent (host).

   • Create a separate directory for the agent (for example, C:\AUTOMIC\AGENTS\SQL\BIN or UC4/AGENTS/SQL/BIN).
   • Copy the content of IMAGE:AGENTS\SQL to the directory you just created. Under Windows, you can also use the program SETUP.EXE for the installation. It is available in the directory IMAGE:AGENTS\SQL\WINDOWS.

3. Install the JDBC driver (host).

   • A suitable JDBC driver must be installed for all databases that the agent will use. Refer to the particular vendor's installation guide.

   • Create the folder JDBC in the database agent's BIN folder. Copy the JDBC driver files to this folder after successful installation.

   • The DB Service Agent must be restarted after installing a new JDBC driver in its JDBC folder.

   Microsoft SQL Server

   • Install the Microsoft JDBC Driver for SQL Server or Microsoft JDBC Driver 4.0 for SQL Server

   • The driver supports MS SQL Server 2005 and 2008 (2008 R2).

   • Copy the file sqljdbc4.jar to the folder's JDBC directory after you have installed the driver.

     Notes:

     • If several JRE or Java SDK Versions are installed on the computer, make sure that the order of the directories is correct in the settings of %PATH% or $PATH. The particular Java Runtime Environment that is found first in the list of directories is used.

     • When you start the agent under Windows, you can use the corresponding OS user for logging on to the MS SQL database (Windows authentication). When you install the JDBC driver, you must also copy the file "sqljdbc_auth.dll to the database agent's BIN directory. Make sure that this file's architecture complies with the architecture of the JVM that is used (such as x64).

     • Usually one DB Service Agent per system will be sufficient. Please take care to use the same JDBC driver for any additional instances of the agent, should you want to use more than one!

   Oracle

   • Install the driver.

   • Copy the file ojdbc5.jar or ojdbc6.jar to the folder's JDBC directory after you have installed the driver.

   • The JDBC driver of version 9.0.1 or earlier cannot be used with this agent due to a program error in the driver.

   • The agent searches the relevant jar-file classes in the JDBC folder. The file name is irrelevant. It is important to use the most current jar file. You might want to store only one jar file in the JDBC folder.

     Important!

     Oracle RAC: The agent can also be configured so that it can connect to an Oracle database in RAC. For more information, see Connection to Oracle Databases.

     Note: The agent's connection information itself has to be defined by using Connection objects (for example: in SQL variables). In the Connection object you would use Oracle or Oracle OCI as connection type.

   MySQL

   • Install the driver.

   • Copy the file mysql-connector-java-5.0.3-bin.jar to the folder's JDBC directory after you have installed the driver.

   DB2

   • The JDBC driver is part of the DB2 installation. It is available in the directory SQLLIB/java (under Windows: C:\Program Files\IBM\SQLLIB\java).

   • Copy the following two files to the agent's JDBC directory:

   • db2jcc.jar (JDBC Type 4 Driver)

   • db2jcc_license_cu.jar (Server license)

   • The JDBC driver can be downloaded from IBM's homepage.

   • Click on the entry "DB2 Personal Developers Edition: Re-distributable JDBC Type 4 Driver."

   Sybase

   • Install the driver (jConnect 5.5/6.05).

   • Copy the file jconn2.jar or jconn3.jar to the folder's JDBC directory after you have installed the driver.

   Informix

   • Install the driver (V3.5).

   • When the driver is installed, copy the files ifxjdbc.jar and ifxlang.jar to the agent's jdbc directory.

   Notes:

   • The agent requires Informix databases with transaction support.

   • Due to an Informix error, it is necessary to specify the value for the environment variable DB_LOCALE in particular versions. The error "Database locale information mismatch." occurs if DB_LOCALE has not been correctly set. In this case, set the corresponding value in the agent's INI file, section [INFORMIX], using the db_locale= parameter (use lower case).

     Example

     [INFORMIX] db_locale=EN_US.CP1252 

   Ingres

   • Install the jdbc driver.

   • When the driver is installed, copy the file iijdbc.jar to the agent's JDBC directory.

   SAP HANA

   • Copy the file ngdbc.jar from the HANA client's installation directory to the agent's jdbc directory.

4. Set up the system environment.

   • Server computer
   • Database agents for variables use the INI file of the Automation Engine. Adjust the section [DB_SERVICE] which contains the specific parameters for the database agent. The agent's INI file is not required. For more information, see Automation Engine.
   • Now create a DB-type Connection object in the AE system for each database in use. You can also create connections for different database types. For more information, see Connection (CONN).
   • If the agent starts under Windows, you can use the Windows user in order to log on to the MS SQL database. The corresponding DB-type Connection object requires the additional parameter "IntegratedSecurity" to be specified (value "true").

5. Start the agent.

   • Server computer
   • The AE system must be running (server computer). For more information, see Multi-Server Operations.
   • Host
   • (Host) In order to start the agent in the mode for database variables, specify the parameter -service and the path and name of the Automation Engine's INI file.
     For example:
     java -jar -Xrs -Xmx256M ucxjsqlx.jar -service -iC:\uc4\server\bin\ucsrv.ini
     
     An Agent object is automatically created in system client 0000 and stored in the folder HOST.
     

     The maximum size of the memory allocation poolshould be set to at least 256MB for starting Java agents (Databases, Rapid Automation , JMX, SAP). You can specify a value for the storage limitation of Java applications by using the start parameter -Xmx. Specifying a value that is too low can cause the agent to crash. The default value depends on the Java version that is used. This value must a multiple of 1024 greater than 2MB. Append the letter k or K to indicate kilobytes, or m or M to indicate megabytes. If large spool lists or Job logs are transferred the value should be much higher (for example -Xmx1024M).

     The Java parameter -Xrs ensures that the agent keeps running in the Service Manager when a User logs off. It is best to use this parameter on Windows only. More detailed information is provided in the Java documentation.

   • Admin computer or Server computer
   • Verify that the agent is logged on to the AE system.
     • Start the AWI for client 0000. Call information about agents in the Administration perspective. Newly logged-on agents have not yet been assigned to a client; they can only be viewed in client 0000. The newly installed agent can now be assigned to clients with the required rights via the Agent object. For more information, see Authorizations Page.

   Use the ServiceManager to start or end the agent as a service. For more information, see ServiceManager.