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 com.microsoft.sqlserver.jdbc.SQLServerDriver	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 and XML sample for the SQL adapter configuration for Microsoft JDBC driver.

<config>
<target>172.19.148.205</target>
<port>1433</port>
<user-name>sa</user-name>
<password>bmcAdm1n</password>
<subprotocol>sqlserver</subprotocol>
<database>master</database>
<driver>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver>
<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>

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:

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

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

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>

Configuring the SQL actor adapter

(archive) BMC Atrium Orchestrator Content 20.13.01

On this page