Setting up an Event Table¶
Before you begin logging or tracing, you must set up an event table that will collect the message and event data generated by handler code. With a predefined set of columns, an event table is designed to record events, event logs and trace events logged from function and procedure handler code.
An event table has the following characteristics that set it apart from other tables you create:
The table contains a predefined set of columns for capturing log entries and trace events. The table’s structure includes columns and key/value attribute object definitions to hold both predefined data and data you design.
You associate an event table with your account in order to capture log entries and trace events to that table. You can associate an account with only one event table at a time. The associated event table is referred to as the active event table.
You can specify the severity level of log messages and verbosity of trace events to capture.
Log messages and trace events generated by handler code for stored procedures, UDFs, and UDTFs are stored in the active event table.
In order to set up an event table for logging, you must do the following (described in this topic):
Create an Event Table¶
To create an event table for storing log and trace event data, execute the CREATE EVENT TABLE command and specify a name for the event table. You will use the event table name later to enable the table to capture logs produced by stored procedures, UDFs, and UDTFs in your account.
Note that when you create an event table, you do not specify the columns in the table. An event table already has a set of predefined columns, as described in Event Table Columns.
Replication of event tables is not currently supported. If you attempt to replicate a database that contains an event table, replication will fail.
You should create the event table in a database that is not enabled for replication.
For example, to create an event table with the name
my_events, execute the following statement:
CREATE EVENT TABLE my_database.my_schema.my_events;
Associate the Event Table with the Account¶
To enable storage of log and trace event data from functions and procedures for an account, you must specify that the event table you created is the active event table for the account.
To specify the active event table for your account, execute the ALTER ACCOUNT command, and set the EVENT_TABLE parameter to the name of your event table.
In order to execute this command, you must use the ACCOUNTADMIN role.
In addition, you must have both of the following privileges:
OWNERSHIP privilege for the account.
See the documentation on the ALTER ACCOUNT command for more information on the privileges needed to execute ALTER ACCOUNT.
For example, to set up the event table named
my_events in the schema
my_schema in the database
the active event table for your account, execute the following statement:
ALTER ACCOUNT SET EVENT_TABLE = my_database.my_schema.my_events;
As shown above, you must specify the fully-qualified name of the event table.
To disassociate an event table from an account, execute the ALTER ACCOUNT command and unset the EVENT_TABLE parameter. For example:
ALTER ACCOUNT UNSET EVENT_TABLE;
You can confirm the EVENT_TABLE value with the SHOW PARAMETERS command:
SHOW PARAMETERS LIKE 'event_table' IN ACCOUNT;
Set the Level for Log and Trace Events¶
You can manage the level of log and trace event data stored in the event table by setting the log or trace level. Before logging or tracing, use these settings to make sure you’re capturing the amount and kind of data you want.
If you intend to log messages, you can set threshold for log message severity as described in Setting Log Level
If you intend to trace events, you can set the verbosity of event data as described in Setting Trace Level