Creating Threshold Procedures

Any user with create procedure privilege can create a threshold procedure in a database. Usually, a system administrator creates sp_thresholdaction in the sybsystemprocs database, and the database owners create threshold procedures in user databases.

sp_addthreshold does not verify that the specified procedure exists. It is possible to add a threshold before creating the procedure it executes.

sp_addthreshold checks to ensure that the user adding the threshold procedure has been granted the “sa_role”. All system roles active when the threshold procedure is created are entered in systhresholds as valid roles for the user writing the procedure.

The SAP ASE server passes four parameters to a threshold procedure:
  • @dbname, varchar(30), which identifies the database

  • @segmentname, varchar(30), which identifies the segment

  • @space_left, int, which indicates the number of free pages associated with the threshold

  • @status, int, which has a value of 1 for last-chance thresholds and 0 for other thresholds

These parameters are passed by position rather than by name; your threshold procedure can use other names for them, but it must declare them in the order shown and with the correct datatypes.

It is not necessary to create a different procedure for each threshold. To minimize maintenance, you can create a single threshold procedure in the sybsystemprocs database that is executed for all thresholds in the SAP ASE server.

Include print and raiserror statements in the threshold procedure to send output to the error log.