Categories:
Warehouse & Resource Monitor DDL

ALTER RESOURCE MONITOR

Modifies the properties and triggers for an existing resource monitor. Use this command to increase or decrease the credit quota, change the scheduling information, or change/replace the triggers for a resource monitor.

See also:
CREATE RESOURCE MONITOR , SHOW RESOURCE MONITORS , ALTER WAREHOUSE , ALTER ACCOUNT

Syntax

ALTER RESOURCE MONITOR [ IF EXISTS ] <name> SET { [ CREDIT_QUOTA = <num> ]
                                                  [ FREQUENCY = { MONTHLY | DAILY | WEEKLY | YEARLY | NEVER } ]
                                                  [ START_TIMESTAMP = { <timestamp> | IMMEDIATELY } ]
                                                  [ END_TIMESTAMP = <timestamp> ]
                                                  [ TRIGGERS triggerDefinition [ triggerDefinition ... ] ] }

Where:

triggerDefinition ::=
   ON <threshold> PERCENT DO { SUSPEND | SUSPEND_IMMEDIATE | NOTIFY }

Parameters

name
Specifies the identifier for the resource monitor to alter. If the identifier contains spaces or special characters, the entire string must be enclosed in double quotes. Identifiers enclosed in double quotes are also case-sensitive.

SET ...

CREDIT_QUOTA = num

Specifies the number of credits allocated monthly to the resource monitor. When total usage for all warehouses assigned to the monitor reaches this number for the current month (i.e. billing period), the resource monitor is considered to be at 100% of quota.

If a value is not specified for a resource monitor, the monitor has no quota and will never reach 100% usage within a monthly billing period.

FREQUENCY = MONTHLY | DAILY | WEEKLY | YEARLY | NEVER

The frequency interval at which the credit usage resets to 0.

If you specify NEVER for the frequency, the credit usage for the warehouse does not reset.

START_TIMESTAMP = timestamp | IMMEDIATELY

The date and time when the resource monitor starts monitoring credit usage for the assigned warehouses.

If you specify IMMEDIATELY for the start timestamp, the current timestamp is used.

END_TIMESTAMP = timestamp
The date and time when the resource monitor suspends the assigned warehouses.
TRIGGERS ... (aka actions)

Specifies one or more triggers for the resource monitor. Each trigger definition consists of:

  • ON threshold PERCENT (usage percentage; values larger than 100 are supported)
  • DO SUSPEND | SUSPEND_IMMEDIATE | NOTIFY (action to perform when the threshold is reached).

For more details, see CREATE RESOURCE MONITOR.

Usage Notes

  • If a SUSPEND or SUSPEND_IMMEDIATE trigger is active for a resource monitor and the trigger threshold has been reached for the month, thereby preventing all assigned warehouses from being started/resumed, you can use this command to either increase the credit quota above the trigger threshold or replace the trigger with a new trigger with a higher threshold.

    Once the credit quota or trigger threshold for the resource monitor has been increased, assigned warehouses can be started or resumed.

  • The SET action for triggers is not additive (i.e. it removes all existing triggers for the resource monitor and replaces them with the specified triggers).

    As a result, to make additions to the existing triggers, you must specify the new triggers and replicate the existing triggers.

Examples

Specify a new credit quota for the limiter resource monitor and replace the existing triggers for the monitor with a new set of triggers:

ALTER RESOURCE MONITOR limiter
  SET CREDIT_QUOTA=2000
      TRIGGERS ON 80 PERCENT DO NOTIFY
               ON 100 PERCENT DO SUSPEND_IMMEDIATE;