3.5 ODBCQuery

Use this Knowledge Script to query a Microsoft SQL Server using Open Database Connectivity (ODBC) and measure the response time.

This script does not support Windows authentication. Use the ODBCDSNQuery Knowledge Script if you need to use Windows authentication. That script lets you supply a DSN to use. You can then configure the Data Source Name (DSN) file to use Windows authentication.

This script generates the following data streams:

Response time
- Overall response time. The information returned by this data stream is also saved with the data point, and can be viewed by double-clicking the data point in the Graph Pane or Chart Console.
- Response-time Breakdown. If enabled as separate parameters, up to three response-time breakdown data streams. These are individual data points for the different parts of the Knowledge Script transaction that are timed. See Setting Parameter Values below for more information.
Availability--Returns one of two values:
- 1 or 100 = transaction was successful
- 0 = transaction was not successful.
The Availability data point is an indication of whether the transaction succeeded or failed.

This script raises an event whenever one of the following occurs:

A threshold that you have specified as an event parameter is exceeded.
The SQL-RT engine cannot be initialized. An initialization error is raised, but neither an Availability nor a Response Time data stream is generated.
The job transaction does not complete successfully. A transaction error is raised. Only an Availability data stream is generated, with a value of 0.

You can select where some of the possible events are displayed in the Operator Console TreeView or Control Center Console Server view. This event proxying feature is useful in Control Center Service Map views. It is not supported for jobs that are started in the Operator Web Console.

3.5.1 Resource Object

The SQL response time ODBC client.

3.5.2 Default Schedule

The default interval for this Knowledge Script is Every 15 minutes.

3.5.3 Setting Parameter Values

Set the following parameters as needed:

Description	How to Set It
Availability
Collect data for availability?	Select Yes to collect data for graphs and reports. If enabled, data collection returns: 1 or 100 -- Transaction completed successfully 0 -- Transaction did not complete successfully Time taken to execute the query (in seconds) The default is Yes.
Data stream format	Select a format for the Availability data stream. You can use a 0 (“not available”) or 100 (“available”) format. The default value is 0-100.
Raise event if transaction fails?	Select Yes to raise an event when the server cannot be contacted. The default is Yes.
Event severity when transaction fails	Set the event severity level, from 1 to 40, to indicate the importance of an event in which the server cannot be contacted. The default is 5.
Response Time
Collect data for response time?	Select Yes to collect response time data for graphs and reports. The default is Yes. If you enable data collection, you also have the option to see a breakdown in the response times for the component parts of the query, such as the time taken to connect to the SQL Server.
Threshold -- Maximum response time (seconds)	Specify the maximum response time in seconds. When response time exceeds this value, an event is raised. The default is 15 seconds.
Raise event if threshold is exceeded?	Select Yes to raise an event when the threshold is exceeded. The event message contains a breakdown of the total response time. The default is Yes.
Event severity when threshold is exceeded	Set the event severity level, from 1 to 40, to indicate the importance of an event in which response time exceeds the threshold. The default is 15.
Response Time Breakdown
Collect data for connecting to SQL Server?	Select Yes to collect response-time data showing how much of the overall response time could be attributed to the time taken to establish a connection to the SQL Server. The default is unselected.
Collect data for executing SQL statement?	Select Yes to collect response-time data showing how much of the overall response time could be attributed to the time taken to execute the SQL statement. The default is unselected.
Collect data for fetching data?	Select Yes to collect response-time data showing how much of the overall response time could be attributed to the time taken to perform a fetch of the query data. The default is unselected.
Server and instance name	Provide the names of the server and instance where the transaction will be run. Use the following syntax: Server/Instance. If you set the Event on parameter, this parameter lets you select the server where the event will appear in your console. The instance name is stripped out. Provide the name of the server and instance, or click Browse [...] to select from a list of available servers. The server you select must already be in the TreeView.
Database name	Provide the database name on the SQL Server. This is the “Initial Catalog” part of the Connection Properties collection. If the provider does not support this property, use the default database.
SQL statement	Set this value appropriately based on your DSN settings. The format is execute stored_procedure.
Event on	Select the TreeView location where events should be displayed. Select either: Agent (the client computer in the response-time tests). This is the default. Server (the AppManager server being tested--see the Server and instance name parameter, above) Both. The event will be shown in two locations in the TreeView. Notes This setting does not apply to events related to the Knowledge Script itself, such as Knowledge Script failure or initialization problems. Such events are always displayed on the computer where the job ran. You must select Agent when starting jobs in the Operator Web Console. If you select Server, no events are generated. If you select Both, an event is only shown on the agent.
SQL Logon
Username	Set this value appropriately based on your DSN settings.
Password	Set this value appropriately based on your DSN settings.
Run As
Username	Provide the username associated with a specific user who has the required permissions to run this application. Required. Interactive User is a possible value. Leave the Password and Domain parameters blank if you specify "Interactive User". Interactive User requires a user to be physically logged into the computer for the test to run. You might want to do this in environments where a firewall is preventing access to an Active Directory domain controller, or where the test computer is part of a workgroup and not part of a domain. With this feature, the user is not validated, so the test can proceed despite the lack of access to the domain.
Password	Provide the password associated with this user that is required to log on to the network and run the application.
Domain	Provide the domain associated with this user that is the domain name you are logging onto. Required.
Administrators group on managed client	Provide the name of the Administrators Group on the managed client. Typically, this name is “Administrators”, except on some foreign language operating systems. The default is “Administrators”.
Connection timeout	Specify the number of seconds to wait while executing a command before terminating the attempt and generating an error. The default is 15 seconds.