sp_addthreshold

Creates a threshold to monitor space on a database segment. When free space on the segment falls below the specified level, the SAP ASE server executes the associated stored procedure.

Syntax

sp_addthreshold dbname, segname, free_space, proc_name

Parameters

Examples

Usage

When a threshold is crossed, the SAP ASE server executes the associated stored procedure. The SAP ASE server uses the following search path for the threshold procedure:
  • If the procedure name does not specify a database, the SAP ASE server looks in the database in which the threshold was crossed.

  • If the procedure is not found in this database, and the procedure name begins with “sp_”, the SAP ASE server looks in the sybsystemprocs database.

If the procedure is not found in either database, the SAP ASE server sends an error message to the error log.

The SAP ASE server uses a hysteresis value , the global variable @@thresh_hysteresis, to determine how sensitive thresholds are to variations in free space. Once a threshold executes its procedure, it is deactivated. The threshold remains inactive until the amount of free space in the segment rises to @@thresh_hysteresis pages above the threshold. This prevents thresholds from executing their procedures repeatedly in response to minor fluctuations in free space.

See also:
  • create procedure, dump transaction in Reference Manual: Commands

  • System Administration Guide for more information about using thresholds.

  • lct_admin in Reference Manual: Building Blocks

Permissions

The permission checks for sp_addthreshold differ based on your granular permissions settings.

SettingDescription
Enabled

With granular permissions enabled, you must be a user with manage database privilege.

Disabled

With granular permissions disabled, you must be the database owner or a user with sa_role.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

InformationValues
Event

38

Audit option

exec_procedure

Command or access audited

Execution of a procedure

Information in extrainfo
  • Roles – Current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – All input parameters

  • Proxy information – Original login name, if set proxy in effect

Related reference
sp_dboption
sp_dropthreshold
sp_helpthreshold
sp_modifythreshold
sp_thresholdaction