The EVMON_UPGRADE_TABLES procedure
alters event monitor target SQL or unformatted event tables to accommodate
new or changed monitoring elements that have been added since the
event monitor was created.
Syntax
>>-EVMON_UPGRADE_TABLES--(--evmon_name--,--evmon_type----------->
>--,--options--,--num_evmons_evaluated-------------------------->
>--,--num_evmons_to_upgrade--,--num_evmons_upgraded--)---------><
The schema is SYSPROC.
Procedure parameters
- evmon_name
- An input argument of type VARCHAR(128) that specifies the name
of the event monitor for which existing table definitions are to be
upgraded. The event monitor target type must be table or unformatted
event table. If evmon_name is specified, any evmon_type argument
is ignored.
- The name may be a pattern-expression similar to that used in the
LIKE predicate which means it can include underscore (_) or percent
(%) characters as wild card characters. For more information about
the LIKE predicate, see LIKE
predicate. If the pattern-expression needs to include either
the underscore or the percent character, the escape option is used
to specify a character to precede either the underscore or the percent
character in the pattern.
- If evmon_name is not provided or set to NULL,
all event monitors with table or unformatted event table output targets
will be processed, subject to criteria supplied by the other input
parameters.
- evmon_type
- An input argument of type VARCHAR(128) that specifies the type
of event monitor for which existing table definitions are to be upgraded.
The type specified must be one of the values in the TYPE column of
SYSCAT.EVENTS. Refer to SYSCAT.EVENTS
catalog view for details. Any event monitor for this event
type and having target type of table or unformatted event table will
be evaluated for upgrading. If evmon_type is specified, evmon_name must
be NULL. If evmon_type is not provided or set to
NULL, all types of event monitors will be evaluated, subject to the
criteria supplied by the other input parameters.
- options
- An input argument of type CLOB(8K) that enables you to specify
one or more event monitor upgrade settings. This parameter defaults
to NULL. Settings are specified as name value pairs using the format:
<setting name tag>value</setting name tag>
Each
setting can be specified a maximum of one time. Setting names are
case sensitive. The setting values are case insensitive unless otherwise
noted. The available setting name tags are as follows.- '<tbspaceName>value</tbspaceName>'
Specifies the name
of a table space into which any new groups are to be created. This
value is case sensitive. If this is not specified and an event monitor's
current target tables are all in the same table space then any new
table will be created in that same table space (so that all tables
are in the same table space). Otherwise the algorithm for picking
a table space as described for the "IN" clause for "CREATE EVENT MONITOR"
is used (see CREATE
EVENT MONITOR statement).
- '<createNewGroups>value</createNewGroups>'
Specifies whether
or not new groups are to be created. The value can be either "yes"
or "no". If not specified it defaults to "yes" and any new group will
be created for an event monitor being upgraded.
- '<verbose>value</verbose>'
Specifies
whether or not to return diagnostics from the stored procedure as
a result set. The value can be either "yes" or "no". If not specified
it defaults to "yes" and diagnostics are return in the result set.
If "no" is specified no result set is passed back. Note that a user
temporary table space must exist in order for the stored procedure
to return a result set. See Table
1 for result set.
- '<force>value</force>'
Specifies whether or not to force
the checking of event monitors to determine if upgrading is required.
If not specified it defaults to "no". When "no" is specified the VERSIONNUMBER
column of SYSCAT.EVENTMONITORS is checked; if its value equals the
current version then the event monitor is considered up to date. If
"yes" is specified the VERSIONNUMBER column of SYSCAT.EVENTMONITORS
is ignored and the event monitor's tables are rechecked to see if
upgrading is required or if new tables are to be added. Setting force
to yes is useful when an event monitor which has had a new event group
added to it has already been upgraded once using the createNewGroups
option with a value of NO and the need is to force that event monitor
to be upgraded again so that a new table is created for the new event
group.
- '<escape>char</escape>'
Specifies a character to be used
to modify the special meaning of the underscore (_) and percent (%)
characters in the pattern-expression of the evmon_name. This allows
the evmon_ name to be used to match values that contain the actual
percent and underscore characters. If this option is not specified
no escape character is provided.
- num_evmons_evaluated
- This output parameter of type INTEGER returns the number of event
monitors that were checked by the stored procedure to determine if
their tables required upgrading.
- num_evmons_to_upgrade
- This output parameter of type INTEGER returns the total number
of event monitors that had at least one target table that actually
required upgrading. This includes event monitors having new or modified
elements or new event groups.
- num_evmons_upgraded
- This output parameter of type INTEGER returns the total number
of event monitors whose target tables that were successfully upgraded
or created.
Authorization
DBADM
authority is required to execute the function.
Default PUBLIC privilege
None
Usage notes
- If evmon_name and evmon_type are both NULL, then the stored procedure
will scan the SYSCAT. EVENTTABLES catalog table and check each TABLE
and UE Table event monitor to see if it's target tables require upgrading.
- The VERSIONNUMBER column of SYSCAT.EVENTMONITORS contains the
version, release, and modification level in which the event monitor
was created or last upgraded. An event monitor target table is considered
to require upgrading if columns were added or modified between the
VERSIONNUMBER and the current release. That is, only changes made
between these releases will be taken into consideration.
- During database upgrade if the VERSIONNUMBER column is not filled
in it will be filled with the release number from which the event
monitor is being upgraded.
- If evmon_upgrade_tables processes an event monitor and that event
monitor requires no changes then VERSIONNUMBER is updated to the current
level. This makes it easy to see which event monitors have been processed.
- If evmon_type is specified then the stored procedure will scan
the SYSCAT.EVENTTABLES catalog table and check each event monitor
that records that event type to see if it's target tables require
upgrading. If an event monitor can record more than one event type
and one of its types is selected for upgrading then only the tables
for that type are checked and upgraded if necessary. For example,
if an event monitor is created CREATE EVENT MONITOR SAMPLE_EVMON FOR
CONNECTIONS, STATEMENTS WRITE TO TABLE and CONNECTIONS is specified
for the evmon_type, then only groups CONTROL, CONNHEADER and CONN
are checked; STMT and SUBSECTION are skipped but the event monitor
version number is updated regardless. If later it was required to
upgrade the statement event tables, then the force option would need
to be used.
- Before a table is checked for an upgrade, it
is first validated with the same rules used when an event monitor
activates. This validation ensures that each column name matches a
monitor element name, that the data type for each column is compatible
with that element, and that, in a partitioned database environment,
the first column of the table is PARTITION_KEY.
- If a new logical data group is to be added to the event monitor,
ALTER EVENT MONITOR is called to add the logical data group. The
new table's schema then defaults to the value in the CURRENT SCHEMA
special register.
- For any event monitor with tables that require upgrading:
- If the event monitor is active it is deactivated (the procedure
waits for queued events to drain first) and exclusive locks are acquired
on its target tables. If a table cannot be locked after 3 seconds
of waiting processing moves to the next event monitor.
- While a table for an event monitor is being upgraded, new events
for that event monitor are lost.
- The
tables are altered to drop columns or add or modify columns to make
them up to date. If the '<createNewGroups>yes</createNewGroups>'
option is specified any new tables are created.
- Tables are altered using the ALTER TABLE statement.
However the ALTOBJ stored procedure is used if a table contains a
BLOB or CLOB column with an inline lob length and that inline length
needs to decrease or
if a VARCHAR column changes to CLOB. The DB2_UTIL_MSGPATH registry
variable can be used to redirect any ALTOBJ messages into a directory
other than instanceName/tmp.
- If a table column is altered or dropped all dependent objects
will be invalidated. When these dependent objects are revalidated
is affected by the auto_reval database configuration
parameter setting. See the documentation for more details about auto_reval.
- After a table is altered the ADMIN_REVALIDATE_DB_OBJECTS
stored procedure is called to perform a table reorg (if necessary)
and ensure the table is usable.
- The exclusive table lock(s) are released.
- If all the tables for an event monitor were successfully upgraded
the VERSIONNUMBER column in SYSCAT.EVENTMONITORS is updated to the
current version number and a COMMIT is issued. Otherwise the unit
of work is rolled back.
- If an event monitor had been deactivated by the stored procedure,
it will reactivated after the stored procedure has completed processing.
- If the INCLUDES clause was specified on the CREATE EVENT MONITOR
statement when an event monitor table was created it is skipped. Check
the TABOPTIONS column of SYSCAT.EVENTTABLES to determine if INCLUDES
was specified. Note that if an event monitor was created before Version 10.1 and
utilized INCLUDES this information is lost and the TABOPTIONS column
will contain a blank.
- If the EXCLUDES clause was specified on the CREATE EVENT MONITOR
statement when an event monitor table was created it is a candidate
for upgrading. The table will be modified to accommodate changes since
the event monitor was created. Check the TABOPTIONS column of SYSCAT.EVENTTABLES
to determine if EXCLUDES was specified. Note that if an event monitor
was created before Version 10.1 and
utilized EXCLUDES this information is lost and the TABOPTIONS column
will contain a blank.
- The procedure will not return any errors encountered while processing
the individual event monitor target tables.
- If a result set is to be returned (see Procedure parameters), keep the following
information in mind:
- A user temporary table space must exist before you run the EVMON_UPGRADE_TABLES
procedure.
- The table used for the result set is named SESSION.EVMON_UPGRADE_TABLES_RESULTSET.
- If SESSION.EVMON_UPGRADE_TABLES_RESULTSET already
exists from a previous execution of EVMON_UPGRADE_TABLES in the same
session, the new results will be appended to the existing table. If
the table definition has changed in any way since the existing table
was created, it is dropped, and a new version of the table is created.
- SESSION.EVMON_UPGRADE_TABLES_RESULTSET is
dropped automatically when with the connection over which EVMON_UPGRADE_TABLES
runs is dropped. The user temporary table space used for the table
cannot be dropped until the SESSION.EVMON_UPGRADE_TABLES_RESULTSET table
is dropped.
Example
A user created the following event
monitors in DB2® Version 9.7:
create event monitor lock for locking write to unformatted event table
create event monitor act for activities write to table control (in
actspace), activity (in actspace), activitystmt (in actspace), activityvals
(in actspace)
create event monitor stat for statistics write to table
create event monitor conn for connections write to table
After
upgrading the database to the current release they upgrade all the
event monitor tables using the following command:
call evmon_upgrade_tables(null, null, null, ?, ?, ?)
If
instead they only wanted to upgrade act, they could use this command:
call evmon_upgrade_tables('ACT', null, null, ?, ?)
Alternatively
they could choose to upgrade only the activities event monitors by
using this command:
call evmon_upgrade_tables(null,'ACTIVITIES', null, ?, ?, ?)
Information Returned
Table 1 describes the information
returned in the result set if you choose to have it created.
DB2 Version 10.1 for Linux, UNIX, and Windows