Configuring the SQL monitor adapter

Adapter type: ro-adapter-sql-monitor[version]

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

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

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

    XML sample of method 1 for defining a URL


    <host>host1</host>
    <port>port1</port>
    <subprotocol>oracle:thin</subprotocol>
    <database>database1</database>

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

    The following figure is an excerpt from the SQL actor adapter configuration that depicts method 2 to define the URL. You need supporting elements to form the complete configuration.

    XML sample of method 2 for defining a URL


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

The following table describes the adapter configuration elements for the SQL monitor adapter.

Note

The SQL monitor adapter does not support form-based adapter configuration at all. You would have to configure this adapter using the XML view. To configure the adapter, see Configuring base adapters.

 SQL monitor adapter configuration node elements 

 

 ~|

Element

 ~|

Description

 ~|

Required

 

 <targets> 

 Contains the <target> element 

 Yes 

 <target> 

Contains the SQL server, database, and connection information.

This element has a name attribute that provides a target name. If the target name has a default attribute with the value set to true, the target is a global target, else the target is a local target.

 No 

 <url> 

Specifies the complete connection URL for the target database

If you do specify the <url> element, you must specify <host>, <port>, <subprotocol>, and <database> elements as needed so that the process can create the URL.

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 

 <host> 

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

This element is required in the absence of the <url> element.

Note

You can specify an IPv6 address with a zone ID for the <host> element---for example, <host>fe80::78d0:8144:ddee:f5a%4</host>.
To find the zone ID of the required computer, see Zone ID for an IPv6 address.

 Conditional 

 <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. This element is not used if you specify the <url> element.

 Conditional 

 <user-name> 

Specifies the user name to be used for database authentication

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

 Conditional 

 <password> 

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

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

 Conditional 

 <subprotocol> 

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

The adapter prepends jdbc: to the value provided. This element is required in the absence of the <url> element.

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

 Conditional 

 <database> 

 Specifies the name of the database to which the adapter connects This element is required in the absence of the <url> element. 

 Conditional 

 <driver> 

Specifies the fully qualified JDBC driver classname

The sample values are:

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

 Yes 

 <character-set> 

 Specifies the supporting CharSet

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

You must specify the <character-set> element only if the target database is MySql.

 |

 No 

 <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 

 <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 milliseconds (5 minutes)

 No 

 <min-connection-idle-time-millis> 

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

Default value: 120,000 milliseconds (2 minutes)

 No 

 <evictor-idle-time-millis> 

Specifies the frequency, in milliseconds, in which a thread used to close idle connections runs

Note: Set the value of this element lower than the value of the <max-connection-idle-time-millis> element.

Default value: 60,000 milliseconds (1 minute)

 No 

 <max-active-connections> 

 Specifies the maximum number of connections allowed at any time in the connection pool Default value: 10 

 No 

 <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> 

Specifies the maximum number of idle connections allowed at any time

Default value: 5

 No 

 <min-idle-connections> 

Specifies the minimum number of idle connections allowed at any time

Default value: 0

 No 

 <validate-query> 

Specifies the query statement used to validate the connection

This element is required in the presence of the <validate-connections-on-return> or <validate-idle-connections> element.

 Conditional 

 <validate-connections-on-return> 

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

The <validate-query> element 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> 

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

The <validate-query> element 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 

 <event-statements> 

 Contains the <event-statement> elements 

 Yes 

 <event-statement> 

 Contains the details about the query to be performed on the specified host 

 Yes 

 <query> 

Specifies the query to be executed periodically on the specified database

The query results are returned in the adapter response. The SQL query for a Monitor adapter only supports SELECT statements, not INSERT, UPDATE, and DELETE statements.

 Yes 

 <poll-interval> 

Specifies the frequency, in seconds, in which the specified <host> is queried

Default value: 60 seconds

 No 

 <single-monitor-event> 

Generates a single event for all the records retrieved by the <query> statement

Valid values: true (default), false

 No 

 <ktgt-cache-file> 

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

Note:

For the Windows OS, the path should be a fully
qualified path using double slashes ('\\') instead of
a single slash ('\') to the krb ticket file. 

Example:

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

 |

 No 

 <krb-service-principal> 

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

This element is required if the <ktgt-cache-file> element is used.

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

 Conditional

Note

The following database servers support an IPv6 address:

  • Microsoft SQL Server 2005 and later
  • MySQL version 5.5.3 and later
  • MySQL Connector/Driver version 5.5.18 and later
  • Oracle version 11gR2 and later
  • Oracle Connector/Driver version 1.4

The following figure shows an XML sample of the non-URL SQL monitor adapter configuration.

XML sample of the non-URL SQL monitor adapter configuration


<config>
   <targets>
       <target name = "target1" default = "true">
           <host>localhost</host>
           <port>3306</port>
           <user-name>root</user-name>
           <password>qbasic</password>
           <subprotocol>mysql</subprotocol>
           <database>moviedb</database>
           <driver>com.mysql.jdbc.Driver</driver>
           <max-wait>1200</max-wait>
           <max-connection-idle-time-millis>10000
           </max-connection-idle-time-millis>
   <min-connection-idle-time-millis>1000
           </min-connection-idle-time-millis>
   <evictor-idle-time-millis>5000</evictor-idle-time-millis>
   <max-active-connections>20</max-active-connections>
   <initial-active-connections>2</initial-active-connections>
   <max-idle-connections>5</max-idle-connections>
   <min-idle-connections>1</min-idle-connections>
   <validate-connections-on-return>true</validate-connections-on-return>
   <validate-idle-connections>true</validate-idle-connections>
   <validate-query>select sysdate from dual</validate-query>
       </target>
   </targets>
   <event-statements>
       <event-statement>
           <query>select * from movie</query>
           <poll-interval>2</poll-interval> (In Seconds)
           <single-monitor-event>true</single-monitor-event>
       </event-statement>
       <event-statement>
           <query>select * from movietype</query>
           <poll-interval>4</poll-interval>
          <single-monitor-event>true</single-monitor-event>
       </event-statement>
   </event-statements>
</config>

The following figure shows an XML sample of the URL SQL monitor adapter configuration.

XML sample of the URL SQL monitor adapter configuration


<config>
   <targets>
       <target name = "mysql_bpm" default = "true">
           <url>jdbc:mysql://localhost:3306/test</url>
           <user-name>root</user-name>
           <password>root123</password>
           <driver>com.mysql.jdbc.Driver</driver>
           <max-wait>1200</max-wait>
       </target>
       <target name = "mysql_cmdb" default = "true">
           <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>
       </target>
   </targets>
   <event-statements>
       <event-statement>
           <query>select * from test</query>
           <poll-interval>15</poll-interval>
           <single-monitor-event>true</single-monitor-event>
           <targets>
               <target>mysql_cmdb</target>
           </targets>
       </event-statement>
       <event-statement>
           <query>select * from test</query>
           <poll-interval>20</poll-interval>
           <single-monitor-event>true</single-monitor-event>
       </event-statement>
   </event-statements>
</config>

The SQL monitor adapter supports the following MySql JDBC charsets:

Note

The SQL monitor 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 monitor adapter configuration with a charset that is supported.

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


<config>
     <target>10.128.249.29</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>utf8</character-set>
     <event-statements>
       <event-statement>
         <query>select * from  employee where address='1〒108-8282東京都港港南'</query>
         <poll-interval>30</poll-interval>
         <single-monitor-event>true</single-monitor-event>
       </event-statement>
     </event-statements>
   </config

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

XML sample of the SQL monitor 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>
   <event-statement>
       <query>select * from  employee where address='1?108-8282??????'</query>
       <poll-interval>30</poll-interval>
       <single-monitor-event>true</single-monitor-event>
   </event-statement>
</config>

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


<config>
 <targets>
    <target name="target1" default="true">
       <url>jdbc:sybase:Tds:10.128.249.29:5000/MASTER?charset=iso_1</url>
       <user-name>sa</user-name>
       <password />
       <driver>com.sybase.jdbc3.jdbc.SybDriver</driver>
     </target>
 </targets>
 <event-statements>
    <event-statement>
       <query>select * from  employee where address='äöÖüÜß'</query>
       <poll-interval>30</poll-interval>
       <single-monitor-event>true</single-monitor-event>
    </event-statement>
 </event-statements>
</config>

The following figure shows an XML sample of the non-URL SQL monitor adapter configuration supporting stored procedures.

XML sample of the non-URL SQL monitor adapter configuration supporting a stored procedure


<config>
 <targets>
   <target name="oracle" default = "true">
     <user-name>u1</user-name>
     <password>p1</password>
    <driver>oracle.jdbc.driver.OracleDriver</driver>
     <host>localhost</host>
     <port>1521</port>
     <subprotocol>oracle:thin</subprotocol>
     <database>D1</database>
   </target>
 </targets>
 <event-statements>
   <event-statement>
    <procedure>
   <name>customer_pkg.getCustomerCount</name>
   <argument-set>
     <argument>
       <index>1</index>
       <in-out>IN</in-out>
       <data-type>VARCHAR</data-type>
       <value>c1</value>
     </argument>
            <argument>
               <index>2</index>
       <in-out>IN</in-out>
       <data-type>NUMBER</data-type>
     </argument>
   </argument-set>
 </procedure>
     <poll-interval>4</poll-interval>
     <single-monitor-event>true</single-monitor-event>
   </event-statement>
 </event-statements>
</config>

 

Note

When the value of the <in-out> parameter is IN, the <index> parameter does not exist.

The following figure shows an XML sample of the SQL monitor adapter configuration with a function returning an integer.

XML sample of the SQL monitor adapter configuration with a function returning an integer


<config>
 <targets>
   <target name="oracle" default="true">
     <host>10.254.1.78</host>
     <port>1521</port>
     <user-name>bmcadmin</user-name>
     <password>abc123</password>
     <subprotocol>oracle:thin</subprotocol>
     <database>testdb</database>
     <driver>oracle.jdbc.driver.OracleDriver</driver>
   </target>
 </targets>
 <event-statements>
   <event-statement>
     <procedure>
       <name>Testing</name>
       <return-data-type>INTEGER</return-data-type>
       <argument-set>
         <argument>
           <index>1</index>
           <in-out>IN</in-out>
           <data-type>NUMBER</data-type>
           <value>100</value>
         </argument>
       </argument-set>
     </procedure>
     <poll-interval>60</poll-interval>
     <single-monitor-event>true</single-monitor-event>
   </event-statement>
 </event-statements>
</config>

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

XML sample of the SQL monitor adapter configuration with Sybase Kerberos Authentication


<config>
<targets>
 <target name="SybaseDB1" default="true">
 <url>jdbc:sybase:Tds:10.128.248.105:5000/MASTER</url>
 <user-name>sybuser1</user-name>
 <driver>com.sybase.jdbc3.jdbc.SybDriver</driver>
 <password />
 <database>master</database>
 <ktgt-cache-file>/tmp/krb5cc_0</ktgt-cache-file>
 <krb-service-principal>REALOPSLINUX1@SYNAPSE.COM</krb-service-principal>
 </target>
</targets>
<event-statements>
 <event-statement>
  <query>select * from spt_monitor</query>
  <poll-interval>15</poll-interval>
  <single-monitor-event>true</single-monitor-event>
 </event-statement>
</event-statements>
</config>

 

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