To issue ALTER SYSTEM statements
against a database, a user must meet at least one of the following
requirements:

• Be a member of the DBA system role


• Have ALTER_SYSTEM authorization, either
  explicitly or through membership in a user-created role

A user who is a member of the DBA system
role (or has the ALTER_SYSTEM authorization)
for the administration database and is connected to that database can
issue ALTER SYSTEM statements
that affect all warehouse databases.

The
following syntax diagram shows how to construct an ALTER SYSTEM statement.

>>-ALTER SYSTEM------------------------------------------------->



>--+-RESET STATISTICS--+-------------------------------+-+-----><

   |                   '-DATABASE--+-ALL-------------+-' |

   |                               '-logical_db_name-'   |

   +-QUIESCE--+-------------------------------+----------+

   |          '-DATABASE--+-ALL-------------+-'          |

   |                      '-logical_db_name-'            |

   +-RESUME--+-------------------------------+-----------+

   |         '-DATABASE--+-ALL-------------+-'           |

   |                     '-logical_db_name-'             |

   +-+-+-START-+--ADVISOR_LOGGING-+----------------------+

   | | '-STOP--'                  |                      |

   | '-SWITCH ADVISOR_LOG FILE----'                      |

   +-TERMINATE--+-ADMIN----------+--DAEMON---------------+

   |            '-ADMINISTRATION-'                       |

   +-alter_user_activity---------------------------------+

   +-alter_user_priority---------------------------------+

   +-alter_logging---------------------------------------+

   +-alter_accounting------------------------------------+

   +-alter_performance_monitor---------------------------+

   '-alter_clear_performance_stats-----------------------'

RESET STATISTICS

Resets
to 0 all statistics in the dynamic statistic tables for the current
or specified database.

QUIESCE

Changes
the state of the current or specified database to quiescent. No new
commands or connections are accepted by a quiescent database, except
those initiated by members of the DBA system role
or users with the IGNORE_QUIESCE task authorization. Currently
executing commands are allowed to complete.

RESUME

Changes
the state of one or more quiescent databases to active. This command
must be issued by an existing session (since you cannot start a
new session on a quiescent database) or by a user who is connected
to the administration database and has ALTER_SYSTEM authorization
for it.

DATABASE

Specifies a single database or all databases. If you are connected to
the administration database (ADMIN) when you execute the ALTER SYSTEM
command, this clause is required. If you
are connected to any other database, the command applies to the current
database and you cannot use the DATABASE clause.

ALL

Indicates that the ALTER SYSTEM statement
applies to all warehouse databases.

logical_db_name

Specifies a logical database name listed in the rbw.config file.

START, STOP ADVISOR_LOGGING

Starts
or stops logging information into the Vista Advisor log file.
There is no default setting for this statement. This statement overrides the
value set with the ADMIN ADVISOR_LOGGING parameter in
the rbw.config file.

SWITCH ADVISOR_LOG FILE

Creates a new active log file with a default name and logs
the following information:

Timestamp

Indicates the date and time the message was logged.

Database name

Specifies the name of the database being used.

Base table identification

Identifies the base table that was used to create the precomputed
view.

View identification

Identifies a precomputed view that was used to answer a query.

Rollup information

Indicates the number of times a view was referenced to answer
queries asking for either a subset of the view's grouping
columns or an attribute of a dimension with less granularity.

Elapsed time

for the query and each aggregate block within the query. Indicates
the total amount of time spent executing the aggregate parts of
a query.

SQL text

for the aggregate block. Represents the view's definition.

TERMINATE ADMIN DAEMON

Terminates the administration daemon (rbwadmd).
All information held in the dynamic statistic tables (DSTs)
is lost when rbwadmd terminates. Administrators
can restart the administration daemon by running the rbwadmd executable
from the /redbrick/bin directory.

alter_user_activity

Includes
two ALTER SYSTEM options: CLOSE USER SESSION and CANCEL USER COMMAND.
Both of these options cancel currently running user commands. The
difference is that the CLOSE USER SESSION option
also terminates the session or sessions that are running the commands.
The alter_user_activity clause is further
defined on page Alter user activity specification.

alter_user_priority

Changes the priorities of current user sessions. Any new sessions
started for the user have the original priority.

To make a permanent change to a user priority, use the ALTER USER statement.
Your platform must have the UNIX renice command
in order to support user priorities. You must specify the full pathname
of the renice script with the ADMIN RENICE_COMMAND configuration
parameter. The full pathname includes the directory name, but not
the name of the executable. The alter_user_priority clause
is further defined on page Alter user priority specification.

alter_logging

Contains options for controlling logging operations. The alter_logging clause
is further defined on page Alter logging specification.

alter_accounting

Contains options for controlling accounting operations. The alter_accounting clause
is further defined on page Alter accounting specification.

alter_performance_monitor

Contains options for controlling the performance monitor.
The alter_performance_monitor clause is
further defined on page Alter performance monitor.

alter_clear_performance_statistics

Contains options for clearing the performance DSTs.
The alter_clear_performance_statistics clause
is further defined on page Alter clear performance monitor.

Alter user activity specification

The following syntax diagram shows how to construct an alter_user_activity clause.
To see how the alter_user_activity clause
relates to the ALTER SYSTEM statement,
see page ALTER SYSTEM.

>>-+-CLOSE USER SESSION--+--+-db_username-+--------------------->

   '-CANCEL USER COMMAND-'  '-ALL---------'



>--+-------------------------------+--+------------------+-----><

   '-DATABASE--+-ALL-------------+-'  '-PROCESS--+-ALL-+-'

               '-logical_db_name-'               '-pid-'

CLOSE USER SESSION

Cancels
and terminates currently executing commands for one or all user
sessions on the current database.

CANCEL USER COMMAND

Cancels
currently executing commands for one or all user sessions on the
current database.

db_username

Specifies a valid database username.

ALL

Specifies that the statement applies to all users of the specified database
or databases.

DATABASE

Specifies a database or all databases. If you are connected
to the administration database (ADMIN) when you execute the ALTER
SYSTEM command, this clause is required.
If you are connected to any other database, the command applies
to the current database and you cannot use the DATABASE clause.

ALL

Indicates that the statement applies to all warehouse databases.

logical_db_name

Specifies a logical database name listed in the rbw.config file.

PROCESS

Specifies
a particular session by its process ID.

If you are connected to the administration database (ADMIN), this
clause must follow a DATABASE clause. For
example, if you are connected to the administration database and
you want to terminate all processes for a specific user on the database
DB1, this clause must follow a DATABASE DB1
clause.

ALL

Specifies all sessions. ALL is the default if no PROCESS option
is specified.

pid

Specifies the process ID of a particular
user session.

Examples

The following statement cancels the currently executing
statement for a particular session run by user diaz on the current
database:

alter system cancel user command diaz

   process 23581

The following statement cancels the statements for all sessions
run by user intern on the database marketing, and it terminates
those sessions. To execute this statement, the user must have the ALTER SYSTEM authorization
for the administration database and must be connected to that database.

alter system close user session intern

   database marketing

   process all

The following example cancels the statements for all sessions
on all warehouse databases that are running for user jones,
and it terminates all those sessions. To execute this statement,
the user must have the ALTER SYSTEM authorization
for the administration database and must be connected to the administration database.

alter system close user session jones database all

Alter user priority specification

The
following syntax diagram shows how to construct an alter_user_priority clause.
To see how this clause relates to the ALTER SYSTEM statement,
see page ALTER SYSTEM.

>>-CHANGE USER--+-db_username-+--SET PRIORITY--integer---------->



                '-ALL---------'



>--+----------------------------------+--+------------------+--><

   '-ON DATABASE--+-ALL-------------+-'  '-PROCESS--+-ALL-+-'

                  '-logical_db_name-'               '-pid-'

CHANGE USER

Changes
the priority of one or more user sessions. (The ADMIN RENICE_COMMAND configuration
parameter must be set in order to make use of this clause.)

db_username

Specifies a valid database username.

ALL

Specifies that the statement applies to all users of the specified database
or databases.

SET PRIORITY integer

Sets
the priority of the session or sessions to the value specified by integer.
This value can be between 0 and 100, inclusive. The highest priority
has value 0.

ON DATABASE

Specifies a database or all databases. If you are connected
to the administration database (ADMIN) when you execute the ALTER
SYSTEM command, this clause is required.
If you are connected to any other database, the command applies
to the current database and you cannot use the DATABASE clause.

ALL

Indicates that the statement applies to all warehouse databases.

logical_db_name

Specifies a logical database name listed in the rbw.config file.

PROCESS

Use
this keyword to specify a particular session by its process ID.

If you are connected to the administration database, this clause must
follow a DATABASE clause. For example,
if you are connected to the administration database and you want
to change the priority for a specific user on the database DB1,
this clause must follow a DATABASE DB1
clause.

ALL

Specifies all sessions. This is the default if no PROCESS option
is specified.

pid

Specifies the process ID of a particular
user session.

Alter logging specification

The following syntax diagram shows how to construct an alter_logging clause.
To see how this clause relates to the ALTER SYSTEM statement,
see page ALTER SYSTEM.

alter_logging clause:



|--+-START LOGGING--------------------------------------+-------|

   +-STOP LOGGING---------------------------------------+

   +-SWITCH LOGGING FILE--------------------------------+

   +-TERMINATE LOGGING LEVEL----------------------------+

   '-CHANGE LOGGING LEVEL--+-AUDIT-------+--+-ROUTINE-+-'

                           +-ERROR-------+  +-ALERT---+

                           +-OPERATIONAL-+  '-URGENT--'

                           +-SCHEMA------+

                           '-USAGE-------'

START LOGGING

Starts
event logging. The log daemon begins accepting log request messages
from warehouse processes and writes corresponding log records to
a new log file.

STOP LOGGING

Stops
event logging. The log daemon stops logging and closes the active
log file. The log daemon continues to run; therefore, logging can
be restarted at any time.

SWITCH LOGGING FILE

Closes
the active log file and creates a new active log file for subsequent log
records. The closed file is renamed from rbwlog.<daemon_name>.active to rbwlog.<daemon_name>.<datetime_stamp>.
If logging is stopped, this statement has no effect.

TERMINATE LOGGING DAEMON

Terminates
the log daemon process (rbwlogd) that
performs both logging and accounting tasks.

CHANGE
LOGGING LEVEL

Changes the log severity level for a selected log event category. The
change takes effect immediately. The event categories are as follows:

• AUDIT (events relating to security
  and access control)
• ERROR (error events)
• OPERATIONAL (administrative actions)


• SCHEMA (changes to physical and logical
  database structures)
• USAGE (load, unload, and DML operations)

ROUTINE, ALERT, URGENT

Only log events having severity equal to or higher than the specified
level are logged for that event category. The lowest severity level
is ROUTINE and the highest is URGENT.
The ALERT severity level is higher than ROUTINE but
lower than URGENT.

Alter accounting specification

The following syntax diagram shows how to construct an alter_accounting specification.
To see how this clause relates to the ALTER SYSTEM statement,
see page ALTER SYSTEM.

alter_accounting specification:



|--+-START ACCOUNTING----------------------+--------------------|

   +-STOP ACCOUNTING-----------------------+

   +-SWITCH ACCOUNTING FILE----------------+

   '-CHANGE ACCOUNTING LEVEL--+-WORKLOAD-+-'

                              '-JOB------'

START ACCOUNTING

Starts
accounting operations. The log daemon begins accepting accounting
request messages and writes corresponding account records to a new
account file. If accounting is already running, this option has
no effect.

STOP ACCOUNTING

Stops
accounting operations. The log daemon closes the active account file.
The log daemon continues to run; therefore, accounting can be restarted
at any time.

SWITCH ACCOUNTING FILE

Closes
the active account file and creates a new active file for subsequent account
records. The closed file is renamed from rbwacct.<daemon_name>.active
to rbwacct.<daemon_name>.<datetime_stamp>.
If accounting is not running, this statement has no effect.

CHANGE ACCOUNTING LEVEL

Sets
the level of detail of the captured account records to job accounting or
workload accounting. This change takes effect immediately.

WORKLOAD, JOB

Specifies job accounting or workload accounting. Job accounting is
limited to basic resource utilization information. Workload accounting
includes additional details and is intended primarily for the use
of IBM support personnel.

Alter performance monitor

The
following syntax diagram shows how to construct an alter_performance_monitor clause.
To see how this clause relates to the ALTER SYSTEM statement,
see page ALTER SYSTEM.

>>-+-START-+--PERFORMANCE MONITOR--+---------------------+------>

   '-STOP--'                       '-SESSION--session_id-'



>--+---------------------------+-------------------------------><

   '-DATABASE--logical_db_name-'

START, STOP PERFORMANCE MONITOR

Starts
or stops the performance monitor daemon (rbwpmond)
when you want to collect and store query execution statistics. This statement
uses the current values of the QUERYPROCS, PERFORMANCE_MONITOR_MAXSESSIONS,
and PERFORMANCE_MAXOPERATORS parameters
to allocate memory to gather and store statistics. For more information
on memory requirements and using the performance monitor, see the Query Performance Guide.

Important:

The performance daemon should be enabled for the duration
of a performance study and not started and stopped for each individual
query. Use SET PERFORMANCE MONITOR ON/OFF statements
to control monitoring for queries within a session. For more information,
see SET PERFORMANCE MONITOR.

The duration of a performance study can be several days or weeks.
You can keep the performance monitor enabled for the entire duration
if the amount of memory allocated can accommodate statistics for
all queries monitored. If you run out of memory, you can clear rows
from the performance DSTs with the CLEAR PERFORMANCE MONITOR option
of the ALTER SYSTEM command. For more information
on this option, see page Alter clear performance monitor.

SESSION

Starts or stops the performance monitor for a specific session. The DBA can
limit the use of the performance monitor to individual sessions
with this keyword.

session_pid

Specifies a currently active session.

DATABASE

Specifies a database for which you start or stop the performance monitor.
If you are connected to the administration database (ADMIN) when
you execute the ALTER SYSTEM command, this clause is required.
If you are connected to any other database, the command applies
to the current database and you cannot use the DATABASE clause.

logical_db_name

Specifies a logical database name listed in the rbw.config file.

Usage notes

The START PERFORMANCE MONITOR option
of the ALTER SYSTEM command starts the
performance monitor daemon rbwpmond and
allocates resources for system-wide performance monitoring.

RISQL> alter system start performance monitor;

The START PERFORMANCE MONITOR option
of the ALTER SYSTEM command prepares the Red Brick server
for monitoring. To start or stop actual query monitoring, use the SET PERFORMANCE MONITOR command
in an individual session.

RISQL> set performance monitor on;

RISQL> select ...;

RISQL> set performance monitor off;

For more information, see SET PERFORMANCE MONITOR.

By default, the performance monitor is enabled for all sessions.
The DBA can stop the monitor for a specific
session by executing the ALTER SYSTEM STOP PERFORMANCE MONITOR command
with the SESSION session_pid clause.

RISQL> alter system stop performance monitor session 6042;

If the user in session 6042 tries to turn on monitoring, an error
is issued.

RISQL> set performance monitor on;

** ERROR ** (9112) Performance monitoring for this session has

   been disabled

If the user had previously turned on monitoring with the SET PERFORMANCE MONITOR command,
this ALTER SYSTEM STOP command with the SESSION clause turns
off monitoring in that session.

After manually stopping the performance monitoring for a session,
the DBA can later
enable it with the SESSION clause in the ALTER SYSTEM START PERFORMANCE MONITOR command.

RISQL> alter system start performance monitor session 6042;

The STOP PERFORMANCE MONITOR command
without the SESSION keyword disables the
performance monitor daemon rbwpmond and de-allocates resources for
system-wide performance monitoring.

RISQL> alter system stop performance monitor;

For information on how to use the performance monitor, see the Query Performance Guide.

Alter clear performance monitor

The
following syntax diagram shows how to construct an alter_clear_performance_monitor clause.
To see how this clause relates to the ALTER SYSTEM statement,
see page ALTER SYSTEM.

>>-CLEAR PERFORMANCE MONITOR--+----------------------------+---->

                              +-START_TIMESTAMP--timestamp-+

                              '-USERNAME--user_name--------'





>--+---------------------------+-------------------------------><

   '-DATABASE--logical_db_name-'

CLEAR PERFORMANCE MONITOR

Clears
rows that are stored in the performance DSTs
based on the START_TIMESTAMP and USERNAME options.
If neither START_TIMESTAMP and USERNAME options
are specified, all rows are cleared.

The performance monitor daemon, rbwpmond, must be enabled before
you can use this clause.

START_TIMESTAMP

Clears rows from the performance DSTs
based on the command start time.

timestamp

Specifies the command start time prior to which all rows are
to be cleared from the performance DSTs.

USERNAME

Clears rows from the performance DSTs
based on the username.

db_username

Specifies a valid database username. Rows with this username are
to be cleared from the performance DSTs.

DATABASE

Specifies a database for which rows in the performance DSTs
are cleared. If you are connected to the administration database (ADMIN)
when you execute the ALTER SYSTEM command, this clause is required.
If you are connected to any other database, the command applies
to the current database and you cannot use the DATABASE clause.

logical_db_name

Specifies a logical database name listed in the rbw.config file.

The PERFORMANCE_MONITOR_COMMANDS_LIMIT parameter
specifies the maximum number of commands that the performance monitor
can store in the performance DSTs. If the
threshold specified by this parameter is reached, a new query cannot
start under the performance monitor. In this case, the performance
monitor issues an error message indicating
that these performance DSTs must be cleared.
Use the CLEAR PERFORMANCE MONITOR clause
of the ALTER SYSTEM command to clear a
subset of the rows or all of the rows so that additional queries
can be monitored.