sp_iqevent procedure

Function

Displays information about system and user-defined events.

Syntax

sp_iqeventevent-name ], [ event-owner ], [ event-type ]

Permissions

DBA authority required. Users without DBA authority must be granted EXECUTE permission to run the stored procedure.

Usage

event-name The name of the event.

event-owner The owner of the event.

event-type The type of event. Allowed values are:

The sp_iqevent procedure can be invoked without any parameters. If no parameters are specified, only information about user events (events not owned by dbo or SYS) is displayed by default.

If you do not specify either of the first two parameters, but specify the next parameter in the sequence, you must substitute NULL for the omitted parameters. For example, sp_iqevent NULL, NULL, SYSTEM and sp_iqevent NULL, user1.

Table 7-23: sp_iqevent usage examples

Syntax

Output

sp_iqevent

Displays information about all user events in the database

sp_iqevent e1

Displays information about the event e1

sp_iqevent non_existing_event

No rows returned, as the event non_existing_event does not exist

sp_iqevent NULL, DBA

Displays information about all events owned by DBA

sp_iqevent e1, DBA

Displays information about the event e1 owned by DBA

sp_iqevent ev_iqbegintxn

ev_iqbegintxn is a system-defined event. If there is no user-defined event also named ev_iqbegintxn, no rows are returned. (By default only user-defined events are returned.)

sp_iqevent ev_iqbegintxn, dbo

No rows returned, as the event ev_iqbegintxn is not a user event (by default only user events returned)

sp_iqevent NULL, NULL, SYSTEM

Displays information about all system events (owned by dbo or SYS)

sp_iqevent ev_iqbegintxn, NULL, SYSTEM

Displays information about the system event ev_iqbegintxn

sp_iqevent ev_iqbegintxn, dbo, ALL

Displays information about the system event ev_iqbegintxn owned by dbo

Description

The sp_iqevent stored event displays information about events in a database. If you specify one or more parameters, the result is filtered by the specified parameters. For example, if event-name is specified, only information about the specified event is displayed. If event-owner is specified, sp_iqevent only returns information about events owned by the specified owner. If no parameters are specified, sp_iqevent displays information about all the user events in the database.

The sp_iqevent procedure returns information in the following columns:

Table 7-24: sp_iqevent columns

Column name

Description

event_name

The name of the event

event_owner

The owner of the event

event_type

For system events, the event type as listed in the SYSEVENTTYPE system table

enabled

Indicates whether or not the event is allowed to fire (Y/N)

action

The event handler definition

condition

The WHERE condition used to control firing of the event handler

location

The location where the event is allowed to fire:

  • C = consolidated

  • R = remote

  • A = all

remarks

A comment string

Examples

Display information about the user-defined event e1:

sp_iqevent e1

event_name    event_owner    event_type    enabled    action
e1            DBA           (NULL)         Y          (NULL)

condition    location    remarks
(NULL)       A           (NULL)

Display information about all system events:

sp_iqevent NULL, NULL, SYSTEM

event_name      event_owner    event_type       enabled   action
ev_iqbegintxn   dbo            IQTLVAvailable   Y         begin call
                                                          dbo.sp_iqlog...
ev_iqmpxcompact dbo           (NULL)            N         begin Declare
                                                          _Catalog...

condition    location    remarks
(NULL)       A           (NULL)
(NULL)       A           (NULL)

See also

CREATE EVENT statement in Reference: Statements and Options

Chapter 6, “Automating Tasks Using Schedules and Events” in the System Administration Guide: Volume 2