Skip to Content

Configuring the SQL actor adapter

You can use the <configs> tag to help you configure the SQL adapter for multiple targets.

BMC recommends that you do not include unused elements in the adapter configuration because they might cause errors.

Adapter type: ro-adapter-sql-actor_vv.rr.nn

Default adapter name: SQLAdapter

You can define the URL for an SQL query in the following ways:

  • Method one: Define the target, port, subprotocol, database, and any parameter as elements in the configuration, allowing the process to form the URL.

    The following sample is an excerpt from the SQL actor adapter configuration that depicts method one of defining the URL. You need supporting elements to form the complete configuration.

    XML sample of method one for defining a URL

    <target>target1</target>
    <port>port1</port>
    <subprotocol>oracle:thin</subprotocol>
    <database>database1</database>
    <parameters>
     <param1>abc</param1>
     <param2>xyz</param2>
    </parameters>

  • Method two: Define the complete URL, including parameters, in a single element in the configuration.

    The following sample is an excerpt from the SQL actor adapter configuration that depicts method two of defining the URL. You need supporting elements to form the complete configuration.

    XML sample of method two for defining a URL

    <url>jdbc:oracle:thin:@target1:port1:database1?param1=abc&param2=xyz</url>

    To configure the SQL actor adapter, see Configuring base adapters.

    The following table describes the SQL actor adapter configuration elements that you can specify by using the form view, XML view, or both. You cannot use the form view to configure elements and attributes that do not have an entry in the "UI label" column.

 Configuration node elements for the SQL actor adapter

UI label

Element

Description

Required

None

<configs>

Configures SQL actor adapter on multiple target computers

No

Url

<url>

Specifies the complete connection URL for the target database

If <url> is not specified, <target>, <port>, <subprotocol>, <database>, and <parameters> must be specified as needed so that the process can create the URL.

Warning

Note

You can specify an IPv6 address with a zone ID for the <url> element---for example, <url>jdbc:mysql://address=(protocol=tcp)(host= fe80::20f:feff:fe40:abaa)(port=3306)/test </url>.
To find the zone ID of the required computer, see Zone ID for an IPv6 address.
For IPv6 URL details for different databases, see IPv6 specifications for databases.

No

Target

<target>

Specifies the host name or the IP address of the database server

Warning

Note

You can specify an IPv6 address with a zone ID for the <target> element---for example, <target>fe80::20c:29ff:fe5d:38f0%eth0</target>.
To find the zone ID of the required computer, see Zone ID for an IPv6 address.

Conditional; required if <url> is not specified

Port

<port>

Specifies the port on which the database server listens

The requirements for the JDBC driver specified in the <driver> element determine whether the <port> element is required.

Conditional; required if <url> is not specified

User Name

<user-name>

Specifies the user name to be used for database authentication

The database requirements in the user's environment determine whether <user-name> is required.

Conditional; required if <url> is not specified

Password

<password>

Specifies the password that corresponds to the <user-name> provided

The database requirements in the user's environment determine whether <password> is required.

Conditional; required if <url> is not specified

Subprotocol

<subprotocol>

Specifies the driver-specified subprotocol used to build the connection URL

The adapter prepends jdbc: to the value provided.

  • Format: database type:subprotocol
  • Sample values: sybase:Tds, oracle:thin

Conditional; required if <url> is not specified

Statement Terminator

<statement-terminator>

Specifies the special character used as a termination value for SQL statements

Do not use this element when communicating with Oracle databases.

No

Database

<database>

Specifies the name of the database to which the adapter connects

The database requirements determine whether <database> is required.

Conditional; required if <url> is not specified

Driver

<driver>

Specifies the fully qualified (absolute) JDBC driver class name

Sample values:

  • com.sybase.jdbc3.jdbc.SybDriver
  • oracle.jdbc.driver.OracleDriver

Conditional

Character Set

<character-set>

Specifies the supporting charset

Also called character set, it includes identifiers describing a series of universal characters.

You can use the <character-set> element only if the target database is MySql.

No

Max Wait

<max-wait>

Specifies the maximum time (in milliseconds) to wait before timing out when establishing a connection to a database

Default value: 60,000 milliseconds (1 minute)

No

None

<parameters>

Specifies the JDBC connection parameters

The elements contained in <parameters> represent key and value pairs that are appended to the end of the JDBC URL that is created.

Format: <parameter-element-name>value</parameter-element-name>

This element is not used when the <url> element is present. The URL must contain all necessary parameters.

No

Max Connection Idle Time Millis

<max-connection-idle-time-millis>

Specifies the maximum time (in milliseconds) that a connection can remain idle before it is closed

Default value: 300,000 (5 minutes)

No

Min Connection Idle Time Millis

<min-connection-idle-time-millis>

Specifies the minimum time (in milliseconds) that a connection can remain idle before being evaluated for termination

No

Evictor Idletime Millis

<evictor-idle-time-millis>

Specifies how often (in milliseconds) a thread used to close idle connections runs

Default value: 60,000 milliseconds (1 minute)

No

Max Active Connections

<max-active-connections>

Specifies the maximum number of connections allowed at any time in the connection pool

Default value: 10

No

Initial Active Connections

<initial-active-connections>

Specifies the minimum number of connections created during adapter startup

The poolable API of Apache software does not support this feature. However, the BasicDataSource (non-poolable version), supports this feature.

Default value: 2

No

Max Idle Connections

<max-idle-connections>

Specifies the maximum number of idle connections allowed at any time

Default value: 5

No

Min Idle Connections

<min-idle-connections>

Specifies the minimum number of idle connections allowed at any time

Default value: 0

No

Validate Query

<validate-query>

Specifies a query statement used to validate the connection

Default value: None

Conditional; required if <validate-connections-on-return> or <validate-idle-connections> is specified

Validate Connections On Return

<validate-connections-on-return>

Indicates whether a connection is validated when it is returned to the pool

<validate-query> must be present to use this feature.

Valid values: true, false (default)

With a value of true, the connection is evaluated to determine the status and, if not alive, the connection is removed from the pool.

No

Validate Idle Connections

<validate-idle-connections>

Indicates whether periodic checks are done on idle connections in the pool (set of connections)

<validate-query> must be present to use this feature.

Valid values: true, false (default)

With a value of true, the connection is evaluated to determine the status and, if not alive, the connection is removed from the pool.

No

Ktgt Cache File

<ktgt-cache-file>

Specifies the location of a valid Kerberos Ticket Granting Ticket (TGT) to obtain a connection through the Kerberos tunnel

Warning

Note

For the Windows OS, the path should be a fully qualified path to the krb ticket file, using double backslashes instead of a single backslash, as in the following example:

C:\\testFolder\\ticketFile
<ktgt-cache-file>/tmp/krb5cc_0
</ktgt-cache-file> 

No

Krb Service Principal

<krb-service-principal>

Specifies the name of the service principal registered with the Sybase database to allow Kerberos authentication


Example:
<krb-service-principal>REALOPSLINUX1@SYNAPSE.COM
</krb-service-principal>

Conditional; required if <ktgt-cache-file> is used

Num Thread Dumps

<num-thread-dumps>

Specifies the number of times that threads should be dumped in grid logs when a pool exhausted exception is encountered

Valid value: Any integer

Default value: 1

No

Connection Properties

<connection-properties>

Specifies the connection properties, in a string format, when you use a JDBC driver to establish a connection

Valid value: Any string with key=value pairs separated by a semicolon delimiter.

Example: For an ORACLE JDBC driver, you can use the following string:
oracle.net.READ_Timeout=5000;defaultRowPrefetch=5

For more details about using Oracle JDBC driver with firewall, see Oracle documentation.

No

The following figure shows an XML sample for the SQL actor adapter configuration by using the connection-properties element for an ORACLE JDBC driver.

<configs>
 <config name="OracleTarget">
   <url>jdbc:oracle:thin:@(DESCRIPTION =(ENABLE=BROKEN)(ADDRESS = (PROTOCOL = TCP)(HOST = OracleTarget)(PORT = 1521))(CONNECT_DATA =  (SERVICE_NAME = TestDB)))</url>
   <user-name>XXX</user-name>
   <password>YYY</password>
   <driver>oracle.jdbc.driver.OracleDriver</driver>
   <max-wait>1200</max-wait>
   <connection-properties>oracle.net.READ_TIMEOUT=5000;defaultRowPrefetch=5</connection-properties>
   <max-idle-connections>0</max-idle-connections>
   <min-idle-connections>0</min-idle-connections>
 </config>
</configs>

The following figure shows an XML sample for the SQL actor adapter configuration without a URL.

XML sample of the SQL actor adapter configuration without a URL

<configs>
    <config name = "mysql">
       <target>localhost</target>
       <port>3306</port>
       <user-name>root</user-name>
       <password>root123</password>
       <subprotocol>mysql</subprotocol>
       <database>test</database>
       <driver>com.mysql.jdbc.Driver</driver>
    </config>
   <config name = "oracle">
       <target>10.254.1.76</target>
       <port>1521</port>
       <user-name>system</user-name>
       <password>system</password>
       <subprotocol>oracle:thin</subprotocol>
       <database>ORCL</database>
       <driver>oracle.jdbc.driver.OracleDriver</driver>
    </config>
    <config name = "mysql111">
       <target>localhost</target>
       <port>3306</port>
       <user-name>firstuser</user-name>
       <password>firstuser</password>
       <subprotocol>mysql</subprotocol>
       <database>firstuser</database>
       <driver>com.mysql.jdbc.Driver</driver>
    </config>
</configs>

The following figure shows an XML sample for the SQL actor adapter configuration with a URL.

XML sample of the SQL actor adapter configuration with a URL

<configs>
   <config name = "mysql">
       <url>jdbc:mysql://localhost:3306/test</url>
        <user-name>root</user-name>
       <password>root123</password>
       <driver>com.mysql.jdbc.Driver</driver>5
       <max-wait>1200</max-wait>
   </config>
   <config name = "oracle">
       <url>jdbc:oracle:thin:@10.254.1.76:1521:bcas</url>
       <user-name>system</user-name>
       <password>system</password>
       <driver>oracle.jdbc.driver.OracleDriver</driver>
       <max-wait>1200</max-wait>
   </config>
   <config name = "mysql111">
       <url>jdbc:mysql://localhost:3306/firstuser</url>
        <user-name>firstuser</user-name>
       <password>firstuser</password>
       <driver>com.mysql.jdbc.Driver</driver>
       <max-wait>1200</max-wait>
   </config>
</configs>

The SQL actor adapter supports the following MySql JDBC charsets:

Warning

Note

The SQL actor adapter supports the following databases:

  • MySql (<character-set> element required when configuring the adapter)
  • Sybase (You can append charset=value to the <url> element or include the <charset> element and it's value in the <parameters> element)
  • Oracle (<character-set> element not required when configuring the adapter)
  • Microsoft SQL Server (<character-set> element not required when configuring the adapter)
  • DB2 (<character-set> element not required when configuring the adapter)

    The target database must contain the supporting character set to get the desired results.

The following figure shows an XML sample for the SQL actor adapter configuration with a charset that is supported.

XML sample of the SQL actor adapter configuration with a supported charset

<config>
 <target>localhost</target>
 <port>3306</port>
 <user-name>root</user-name>
 <password>root</password>
 <subprotocol>mysql</subprotocol>
 <database>test</database>
 <driver>com.mysql.jdbc.Driver</driver>
 <character-set>sjis</character-set>
</config>

The following figure shows an XML sample for the SQL actor adapter configuration with a charset that is not supported.

XML sample of the SQL actor adapter configuration with a charset that is not supported

<config>
 <target>localhost</target>
 <port>3306</port>
 <user-name>root</user-name>
 <password>root</password>
 <subprotocol>mysql</subprotocol>
 <database>test</database>
 <driver>com.mysql.jdbc.Driver</driver>
 <parameters>
   <characterEncoding>shift_jis</characterEncoding>
   <useUnicode>true</useUnicode>
 </parameters>
</config>

When the target database is Sybase, the character set must be provided as a parameter or it must be appended to the URL.

The following figure shows an XML sample for the SQL actor adapter configuration when the target database is Sybase.

XML sample of the SQL actor adapter configuration when the target database is Sybase

<config>
 <url>jdbc:sybase:Tds:10.128.249.29:5000/MASTER?charset=sjis</url>
 <user-name>root</user-name>
 <password>root</password>
 <driver></driver>
</config>       or          <config>
  <target>10.128.249.29</target>
 <port>5000</port>
 <user-name>root</user-name>
 <password>root</password>
 <subprotocol>sybase:Tds</subprotocol>
 <database>MASTER</database>
 <driver></driver>
 <parameters>
   <charset>sjis</charset>
 </parameters>
</config>

The following figure shows an XML sample for the SQL actor adapter configuration with Sybase Kerberos Authentication.

XML sample of an SQL actor adapter configuration for Sybase Kerberos Authentication

<config name = "sybasedb">
 <target>10.128.248.105</target>
 <url>jdbc:sybase:Tds:10.128.248.105:5000/MASTER</url>
 <user-name>sa</user-name>
 <password />
 <driver>com.sybase.jdbc3.jdbc.SybDriver</driver>
 <database>master</database>
 <ktgt-cache-file>/tmp/krb5cc_0</ktgt-cache-file>
 <krb-service-principal>REALOPSLINUX1@SYNAPSE.COM</krb-service-principal>
</config>

You can also have an empty configuration, as shown in the following figure.

XML sample of an SQL actor adapter with an empty configuration

<config/>

The following figure shows an XML sample for the SQL actor adapter configuration when the adapter uses the <num-thread-dumps> element.

XML sample for the SQL actor adapter configuration when the adapter uses the <num-thread-dumps> element

<config>
 <max-wait>60000</max-wait>
 <max-connection-idle-time-millis>300000</max-connection-idle-time-millis>
 <evictor-idletime-millis>60000</evictor-idletime-millis>
 <max-active-connections>10</max-active-connections>
 <initial-active-connections>2</initial-active-connections>
 <max-idle-connections>5</max-idle-connections>
 <min-idle-connections>0</min-idle-connections>
 <validate-connections-on-return>false</validate-connections-on-return>
 <validate-idle-connections>false</validate-idle-connections>
 <num-thread-dumps>1</num-thread-dumps>
</config> 

Encrypting an element's contents

You can add the attribute secure="true" to an XML adapter element XML view to ensure that the element's contents is encrypted when displayed.

Warning

Note

You must be using TrueSight Orchestration Platform version 8.1 or later to use this encryption attribute.

Enabling custom logging

To enable custom logging for the adapter, you must specify a log file name. You can also provide additional parameters for logging.

Warning

Note

You must be using TrueSight Orchestration version 8.1 or later to use the custom logging feature. These parameters will be ignored in earlier versions of TrueSight Orchestration Platform.

These parameters are available with supported adapter versions. See TrueSight Orchestration Content documentation for details.

  • Log File Name: Provide a name for the log file.
    This file will be stored in the AO_HOME/tomcat/logs directory. If Log File Max Backup Index value is greater than 0, the log file name is suffixed with the backup index. For example, if the parameter value is a.log, backup log files will have names, such as a.log.1, a.log.2.
  • Log File Size: Specify a size limit for the log file. 
    If the value specified for Log File Max Backup Index is greater than 0, when the specified size is reached, the current file is renamed with the suffix .1. Otherwise, the log file will be reset and over-written. The default value is 10MB. The available units are KiloBytes (KB), MegaBytes (MB) or GigaBytes (GB).
  • Log File Max Backup Index: Enter the maximum number of backup files allowed. The default value is 10.
  • Log File Append: Select this option to append new log information to the existing information in the file. If unselected, the file will be overwritten with new log information.

  • Log Level: Enter the logging level using one of the following choices:

    Logging level

    Description

    DEBUG

    The most detailed logging level; logs low-level messages, normal execution, recoverable erroneous conditions, and unrecoverable erroneous conditions

    INFO

    (default)

    Logs normal execution, recoverable erroneous conditions, and unrecoverable erroneous conditions

    WARN

    Logs recoverable erroneous conditions and unrecoverable erroneous conditions

    ERROR

    The least detailed logging level; logs only error conditions that are not usually recoverable

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*

TrueSight Orchestration Content 24.3