Service Response Monitor › Test Management › Create Tests › SQL Query Tests

SQL Query Tests

The SQL Query test monitors the amount of time required to connect to any database that supports JDBC and can execute SQL queries. It supports queries for Oracle, Microsoft SQL Server, and other databases.

Before you begin creating SQL Query tests, verify that the appropriate JDBC driver is available. If necessary, install the JDBC driver files that are specific to the database you are trying to test.

• sqljdbc.jar (JRE Version 1.5 or earlier) or sqljdbc4.jar (JRE Version 1.6 or later) for SQL Server
• ojdbc<XYZ>.jar for Oracle

Copy the JAR files to the jre/lib/ext directory under the SystemEDGE installation directory (for exampl, /opt/SystemEDGE/jre/lib/ext [UNIX] or drive:\sysedge\jre\lib\ext [Windows]). To obtain JDBC driver jar files for your database, check the Corporate Web site of the company that distributes the database.

Example to verify the availability of the SQL JDBC driver

1. Change to the Install_Path/SystemEDGE/jre/lib/ext directory.
2. Check, if sqljdbc.jar (JRE Version 1.5 or earlier) or sqljdbc4.jar (JRE Version 1.6 or later) is available.

If the SQL JDBC is not available, perform the following steps:

1. Download the SQL JDBC driver from microsoft.com/downloads.
2. Extract the downloaded driver package and copy sqljdbc.jar (JRE Version 1.5 or earlier) or sqljdbc4.jar (JRE Version 1.6 or later) to the Install_Path/SystemEDGE/jre/lib/ext directory.
3. Restart SystemEDGE to load the new Java classes.
4. Set up new SQL tests, for example, through Policy Configuration in CA Virtual Assurance. See also the help system from the extracted JDBC package if necessary.

Options and Arguments

SQL Query tests require the following specific options and arguments:

• Database Type: Select Oracle, MSSQL, or Other.

  Note: For Oracle and MSSQL, SRM creates a dynamic SQL connect string using a default JDBC driver installed with SRM. For any other JDBC driver that you download, regardless of the database vendor, you must obtain the Java class name of the driver and an appropriate SQL connect string from the vendor and provide specific keywords (see below).

• SQL Database Server: The hostname of the SQL database server.

  Note: IPv6 addresses can be used, but they must be embedded in brackets, for example [0aff::230:6eff:fe4b:51db]:8080. Here 0aff::230:6eff:fe4b:51db represents the IPv6 address and 8080 represents the port number.

• Port: The port on which the SQL database is running. The default port number appears when you select either Oracle or MSSQL. This field is mandatory.
• User Name: A valid user name for the database.
• Password: A valid password for the specified user name. SRM stores the password in encrypted form.
• SQL Driver: The JDBC driver required to connect to the database. If you select either Oracle or MSSQL as the Database Type, the SQL driver name defaults to the Oracle or MSSQL driver you specified during SRM installation (in which case this field does not appear). To specify a different JDBC driver for Oracle or MSSQL, select Other and complete the required fields. This includes identifying the Java class name of the SQL driver you want to use and the appropriate connect string. Again, the connect string format and the required information varies by vendor. Be sure to obtain that information from the vendor web site or from the vendor's driver documentation.

  Examples:

  com.microsoft.sqlserver.jdbc.SQLServerDriver
  oracle.jdbc.OracleDriver
  com.mysql.jdbc.Driver
  

• Database Name: Specify the database instance name. This field is mandatory for Oracle and optional for MSSQL. The driver name and connect string are sufficient for MSSQL (the database instance name is null). This field does not appear if you select Other because you provide that information in an explicit SQL connect string.
• Query: An SQL query to perform. For information about the SQL query language, refer to your database documentation.
• Connect String: The required SQL connect string for your database. This field appears only if you selected Other as the Database Type, otherwise, SRM builds the connect string based on the information you provide in the required fields in the SRM user interface. If your database driver is other than the default driver for Oracle or MSSQL, you must specify an explicit connect string in this field.

  Note: The format of an SQL connect string varies depending on the database driver from a particular vendor. Refer to your database vendor's web site to obtain the appropriate connect string for your JDBC driver or refer to your JDBC driver documentation.

  The connect string from your database vendor contains a sequence of fields, some of which you can replace with explicit keywords, such as a unameValue, pwdValue, hostnameValue, and portValue. When you enter the connect string in the GUI, type in "unameValue" and "pwdValue" and let the agent do the substitution. The vendor-specific connect string should stipulate where in the connect to place the keywords. (SRM does this automatically if you use the default JDBC driver for Oracle or MSSQL.) When an SRM test runs, the following SRM keywords in the connect string get replaced by the values that you specify:

  • The unameValue keyword is replaced by the specified username
  • The pwdValue keyword is replaced by the specified password
  • The hostnameValue keyword is replaced by the specified host name
  • The portValue keyname is replaced by specified port

  Examples:

  jdbc:oracle:thin:unameValue/pwdValue@hostnameValue:portValue:orcl (connects with orcl)
  jdbc:sqlserver://hostnameValue:portValue;databaseName=vasdb;user=unameValue;password=pwdValue (connects with vasdb)
  jdbc:sqlserver://hostnameValue:portValue;user=unameValue;password=pwdValue (connects with default database)
  jdbc:mysql://hostnameValue:portValue/mydb?user=unameValue&password=pwdValue (connects with mydb)
  

Specify these options and arguments when you create or modify tests.

Example 1: Testing an Oracle Database

Use this example to create a test that logs into an Oracle database and performs a query to retrieve the table names from the user tables once every 300 seconds. The test waits up to 10 seconds for a successful response and calculates response time and availability statistics over the last 1800 seconds (30 minutes).

To create a new test in a policy

1. Click + (New) on the Test Monitors toolbar.

   The New test pane appears.

2. From the Test Type list, select SQL Query.
3. In the Description field, specify SQLQuery_OracleTest.
4. In the Test Name field, specify Oracle Test.
5. In the Test Interval field, specify 300.
6. In the Test Timeout field, specify 10.
7. In the Samples Per Interval field, specify 1.
8. In the Statistics Window field, specify 1800.
9. In the Database Type field, select Oracle.
10. In the SQL Database Server field, specify OracleTest.
11. In the Port field, accept the default: 1521.
12. In the User Name field, specify OracleUser.
13. In the Password field, specify Oracle123.
14. In the Database Name field, specify the name of the database you want to test, for example, MYDB.
15. In the Query field, specify select table_name from user_tables.
16. Click Save Test.

When you commit your changes, SRM adds an entry similar to the following to the svcrsp.cf file:

{ 
index=11 
type=sql
desc="SQLQuery_OrcaleTest" 
dest="OracleTest:1521" 
username="OracleUser" 
encoded=yes 
password="aW1vdXJhdmlldg" 
args="query=select table_name from user_tables&dbtype=oracle&dbname=MYDB" 
interval=300 
samples=1 
timeout=10 
window=1800 
tos=0 
limit=0 
status=active 
name="Oracle Test"
class=""
context=""
flags="1"
loglevel=2
}

Example 2: Testing a Microsoft SQL Server Database

Use this example to create a test that logs into a Microsoft SQL database and performs a query to retrieve the table names from the user tables once every 60 seconds. The test waits up to 10 seconds for a successful response and calculates response time and availability statistics over the last 120 seconds.

To create a new test in a policy

1. Click + (New) on the Test Monitors toolbar.

   The New test pane appears.

2. From the Test Type list, select SQL Query.
3. In the Description field, specify SQLQuery_SQLServer.
4. In the Test Name field, specify SQL Test.
5. In the Test Interval field, specify 60.
6. In the Test Timeout field, specify 10.
7. In the Samples Per Interval field, specify 1.
8. In the Statistics Window field, specify 120.
9. In the SQL Database Server field, select MSSQL.
10. In the Port field, accept the default: 1433.
11. In the User Name field, specify SQLUser.
12. In the Password field, specify SQL123.
13. In the Database Name field, specify the name of the database you want to test, for example, Northwind.
14. In the Query field, specify select * from categories.
15. Click Save Test.

When you commit your changes, SRM adds an entry similar to the following to the svcrsp.cf file:

{ 
index=11 
type=sql
desc="SQLQuery.SQLServer" 
dest="mySQLServer:1433" 
username="SQLUser" 
encoded=yes 
password="bWnvTRPJhdmll" 
args="query=select * from categories"&dbtype=mssql&dbname=Northwind" 
interval=60 
samples=1 
timeout=10 
window=120 
tos=0 
limit=0 
status=active 
name="SQL Test"
class=""
context=""
flags="1"
loglevel=1
}

For information about errors you may encounter when running SQL Query tests, see SQL Query Test Error Codes.