Defining a Connection Using JDBC

You must create connections to one or more databases using JDBC before TA can run your JDBC jobs or monitor database events. These connections also must be licensed before TA can use them.

The connection for the adapter is defined like other TA connections except it requires configuration information unique to JDBC.

You can create connections to one or more databases. JDBC access can be managed by limiting a user account to use only certain connections to particular databases. A connection is created through the Connection Definition dialog.

This dialog contains these elements:

Name – Enter a name for this adapter connection.
Enabled – Activates or deactivates the connection, shutting down communication with the database. A job cannot run on an adapter if its connection is disabled.
Use as default for JDBC Jobs – Click to use the connection as your default connection when setting up future JDBC jobs.
General – This tab designates a job limit and default runtime user.
JDBC Connection – This tab provides information to connect to the designated database.
Options – This tab provides configuration options for accessing the database.
Description – This tab describes the connection and provides a convenient place for notes about its purpose and use.

Adding a Connection Using JDBC

To add a connection:

Click Administration > Connections to display the Connections pane.
Click Add on the toolbar or right-click and click Add Connection > JDBC Adapter to display the JDBC Connection Definition dialog.
Enter a name for the new connection in the Name field.
Click the Job Limit field of the General tab.
Click the maximum number of concurrent active processes that TA should submit to this database at one time.
(Optional) Click the Default Runtime User list, and then the name of a default user for JDBC jobs.

Only users that have been defined with adapter passwords display in this list. The user selected is automatically supplied as the runtime user in TA JDBC job definitions.
Click the JDBC Connection tab.

This tab defines how to access a database using JDBC. There are several different ways to connect to a database using JDBC. If you wish to connect to a specific database, you can designate that database for the connection.
Use automatic population or manual entry to enter data:

Automatic population: Use predefined templates for the most commonly used database connections:
1. In the JDBC Template field, click a template from the drop-down menu.
  
  The JDBC Driver field and the JDBC Connection String (URL) field are automatically populated.
  
  Note: You must choose the JDBC driver before overriding the JDBC connection string, otherwise, the connection will not be saved.
2. In the JDBC Host field, enter the host machine.
  
  The <Host> placeholder in the JDBC Connection String (URL) field is replaced.
3. In the Connect User field, optionally enter a user that is used to connect to the database from the drop-down menu.
  
  You must enter a user to obtain a drop-down list of databases in the Database/DSN field.
  
  (Optional) In the Database/DSN field, choose from the drop-down menu or enter the name of the database for this connection.
  
  Note: If the JDBC connection was created using an Oracle/DB2 database, the “Database Name” is the Schema Name and not the actual Database name used while creating JDBC jobs and events.
  
  The drop-down menu only lists databases for connections where a database name is optional; otherwise, if a database name is required to make a JDBC connection, you must type in the name.
  
  This value is added to the JDBC connection string in the JDBC Connection String (URL) field.
Manual entry: If the driver for your RDBMS is not listed in the JDBC Template drop-down menu, enter the information manually:
1. In the JDBC Driver field, enter your JDBC driver’s fully qualified class name.
  
  The driver class entered here must be found in the CLASSPATH on the master machine. For information, see Requirements.
2. In the Connect User field, enter the user that is used to connect to the database from the drop-down menu.
  
  You must enter a user to specify a database in the Database/DSN field.
  
  (Optional) In the Database/DSN field, choose from the drop-down menu or enter the name of the database for this connection.
  
  The drop-down menu only lists databases for connections where a database name is optional; otherwise, if a database name is required to make a JDBC connection, you must type in the name.
3. In the JDBC Connection String (URL) field, enter the JDBC driver connection URL as specified in your driver’s documentation.
Click the Options tab.

Note: If the DB Server is Azure SQL database, add CONN_TYPE=Azure as the Configuration Parameter.
Click the Event poll field, enter the number of seconds between successive poll checks of an event monitor that the adapter uses to check whether it should raise an event.
Enter queries specific to your database platform in the Connection Attribute Queries pane.

If you had selected a JDBC template previously, queries for the selected database will populate automatically in many cases. Optionally, you can override these queries or provide your own custom queries.
- Version – Queries the version number of the database.
- Local Time – Queries the time of the database’s server.
- UTC Time – Queries the UTC time for the database server.
  
  The local time and the UTC is used to get the time zone and calculate the time difference between the database server and the scheduling master.
- Health – Queries the health of the database based on any custom SQL select statement.
  
  If the query returns a result set, the connection is considered healthy (green). If an empty result set is returned, the connection is considered unavailable (red).
- Procedure Help – Queries the content of a stored procedure that is displayed in the SQL tab in the JDBC Job Definition dialog.
  
  Use $1 as a placeholder for the stored procedure name, which is replaced by an actual procedure name when the adapter needs to get the help or text associated with the stored procedure.
Click OK.

JDBC Connection Status Light

If the connection using JDBC is not available to the master, the connection status light in the first column of the display is red, and the Connections status light at the bottom of the console is red (or yellow if other adapters are connected to the Master).

Note: This light is relative to the status of the connection to the TA Master, not the connection to the client.

The status light indicates the health of the adapter connection to the database. If a health query has been provided on the Options tab described previously in Adding a Connection Using JDBC it will also be used to determine the connection status.

If the TA master cannot connect or loses its connection to a database, you will see a red status light next to your connection using JDBC in the Connections pane.

Note: If the adapter connection using JDBC is not available, you will not be able to define JDBC jobs.