3.31 RunSql

Use this Knowledge Script to run a SQL statement. The statement can be entered directly in the script or loaded from a file. You specify the column to monitor, by number or name, and whether to monitor the value found in the column or the value’s rate of change (changes per second). You can also specify a string that the script can search for in the retrieved rows.

This script is designed to execute simple SQL statements, for example, a statement that returns the size of a table or the number of columns in a table. There is a limit of 10000 characters for the entire statement when it is contained in a file, and a limit of 1000 characters when the statement is entered via the SQL statement parameter. Regardless of the source of the SQL statement, there is a limit of 100 columns retrieved by a SELECT statement, and a limit of 8 KB for the size of each row retrieved.

This script supports SQL statements using the number, character, date, and raw data types. However, only a column returning numeric data can be selected to monitor.

The account you use to run this script must have SELECT permissions for V_$VERSION and any table the script is run against.

3.31.1 Resource Objects

Oracle Database folders. When you drop a script on an Oracle Database folder, a job executes on that database and monitors only that database.

3.31.2 Default Schedule

The default interval for this script is Every 10 minutes.

3.31.3 Setting Parameter Values

Set the following parameters as needed:

Description

How to Set It

Oracle Username

Enter the username that this script needs to access the target databases. If you run this script on more than one database, configure each database with the same username. To use SYSDBA authentication, leave this parameter blank. The default is blank.

Collect data specified in the SQL query?

Select Yes to collect data for charts and reports. When you enable data collection, the Knowledge Script returns the number of rows returned from the SQL statement. If you choose to monitor the column’s rate of change and set this parameter to y, the script returns the change in the number of rows since the last time the SQL statement was run. The default is Yes.

Legend

Enter a legend for the output of your SQL statement.

For example, if the column number is 0, the constructed legend is “# Result Rows”. If the column number is greater than 0, the constructed legend is the specified column heading. If no heading exists, the constructed legend is: “Column <num> Value”.

If you leave this parameter blank, AppManager constructs a legend based on the column number. The default is blank.

Load SQL script from file?

Select Yes to load a SQL script from a file. The default is unselected.

SQL script file (full path)

If you select Yes for the Load SQL script from file? parameter, enter the complete path to the file that contains the SQL statement. For example: /netiq/Sample.sql. The default is blank.

SQL statement

If the Load SQL script from file? parameter is not selected, enter the SQL statement to be executed. The default statement selects all processes from the V_$PROCESS table: SELECT * FROM V_$PROCESS

Unless you are entering very simple queries, you might find typing a SQL statement into this field is error‑prone. To avoid errors, use the Load SQL script from file? parameter. Or, if you have an AppManager Developer’s license, you can check this script out of the repository, use the Knowledge Script Editor to paste the desired SQL statement into the SQL statement parameter, then check in the modified script. The default is blank.

Select column by number, or name?

Set to Number or Name:

  • Number--to select the column by number

  • Name--to select the column by name.

The default is number.

Column number to retrieve

If you set the Select column by number or name? parameter to Number, enter the column number to use as the primary output value. The column you specify must contain numeric data. Entering 0 returns the number of rows returned from the SQL statement. Any other positive value returns the value for the specified column’s first row of data. If the specified column is not a numeric field, the job raises an event. The default is 1.

Column name to retrieve

If you set the Select column by number or name? parameter to Name, enter the column name to use as the primary output value. The column you specify must contain numeric data. The value for the specified column’s first row of data is returned. If the specified column is not a numeric field, the job raises an event. The default is blank.

Number of rows to display

Enter the number of rows you want displayed in the Event Properties dialog box. The default is 5.

NOTE:You can enter 0 to indicate no limit (keep all output rows). Currently, the message in the Event Properties dialog box is limited to 32K characters.

Monitor the column’s value or rate of change?

Set to Value to monitor the column’s value. Set to Change to monitor the column’s rate of change (per second). The default is Value.

Raise event when threshold is exceeded or not met?

Select Yes to raise an event when a threshold is crossed.The default is unselected.

Condition: <, =, or >

Indicate the condition (less than, equal to, or greater than) that you want to check. This parameter is used in conjunction with the Threshold - Value or rate of change parameter to control when the job raises events. The default is >, indicating that the job raises an event if the value retrieved exceeds (is greater than) the value specified in the threshold.

Threshold - Value or rate of change

Enter a threshold for the value or rate you are monitoring. Depending on how you set the Monitor the column’s value or rate of change? parameter, this might indicate a threshold for the statistic’s value or for the number of times the value changes per second. The value you set here is used in conjunction with the Condition: <, =, or > parameter to control when the job raises events. The default is 1,000.

Severity

Set the event severity level, from 1 to 40, to indicate the importance of the event. The default is 5 (red event indicator).

Raise event for string found in row results?

Select Yes to raise an event if the string you specify below you can find in the row results. The default is unselected.

String(s) to find in results (separate multiple strings with semicolons)

Enter a string (text) to search for in row results. If you enter multiple strings, separate them with semicolons (;) and no spaces. The default is blank.

Severity

Set the event severity level, from 1 to 40, to indicate the importance of the event. The default is 15 (yellow event indicator).

Event severity for internal failure

Set the event severity level, from 1 to 40, to indicate the importance of the event. The default is 5 (red event indicator).