Skip to Content
Information
Unsupported content This version of the documentation is no longer supported. However, the documentation is available for your convenience. You will not be able to leave comments.

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[baob201301:version]

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; not used if <url> is provided

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

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

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; not used if <url> is provided

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> 

 

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

(archive) BMC Atrium Orchestrator Content 20.13.02