The EVMON_FORMAT_UE_TO_TABLES procedure
retrieves data stored in an unformatted event (UE) table produced
by an event monitor and converts it into a set of relational tables.
The process of creating relational tables takes place in two steps.
First the data in the UE table is converted to XML format, using the
EVMON_FORMAT_UE_TO_XML table function. This table function is run
for you automatically as part of running the EVMON_FORMAT_UE_TO_TABLES
procedure. Next, the XML document that contains the event monitor
data is turned into relational tables using XML decomposition.
Syntax
>>-EVMON_FORMAT_UE_TO_TABLES--(--evmon_type--,--xsrschema--,---->
>--xsrobjectname--,--xmlschemafile--,--tabschema--,------------->
>--tbsp_name--,--options--,--commit_count--,--fullselect--)----><
The schema is SYSPROC.
Procedure parameters
- evmon_type
- An input parameter of type VARCHAR(128) that represents the type
of data stored in the unformatted event table. The possible values
are as follows:
- LOCKING
- Data stored in the unformatted event table is from a locking event
monitor.
- PKGCACHE
- Data stored in the unformatted event table is from a PACKAGE CACHE
event monitor.
- UOW
- Data stored in the unformatted event table is from a UOW event
monitor.
- xsrschema
- An input parameter of type VARCHAR (128) that specifies the first-part
of the name of the XSR object that describes how data from the UE
file corresponds to columns in tables. The second-part of the XSR
object name is derived from the xsrobjectname parameter.
The complete XSR object name is defined as xsrschema.xsrobjectname. If this value is NULL, then the
authorization ID of the current session user is used.
- xsrobjectname
An input parameter of type VARCHAR (128) that specifies the
second-part of the name of the XSR object that describes how data
from the UE file corresponds to columns in tables. The first-part
of the XSR object name is derived from the xsrschema parameter. The complete XSR object name is defined as xsrschema.xsrobjectname and is unique among all objects
in the XSR. If this value is NULL then the xsrobjectname is derived as follows:EVMON_<evmon_type>_SCHEMA_<SQL
release level>. For example, a locking event monitor in DB2® Version
9.7 would have an derived xsrname of EVMON_LOCKING_SCHEMA_SQL09070.
The XSR object is
a copy of the XML schema file that describes the output of the event
monitor. It is stored in the XML schema repository (XSR), and defines
the relationship between the elements of the interim XML document
produced by the first stage of EVMON_FORMAT_UE_TO_TABLES processing,
and the tables and columns the procedure ultimately produces. The
XSR object is also used to manage the mutual dependency between any
tables that have been created and the XML schema from which those
tables are derived. If the XSR object is dropped, or if any of the
tables produced by the procedure are dropped or the columns altered,
the dependency between the two is said to be broken. If EVMON_FORMAT_UE_TO_TABLES
(or the EVMON_FORMAT_UE_TO_XML table function) has not yet been run
against the UE file for a specific type of event monitor, the XSR
object that describes the event monitor output will not yet exist.
In this case, the XML schema file for the event monitor is used to
create and register an XSR object in the system catalog tables.
If the database
has been upgraded to a newer release, the original xsrobjectname must be explicitly specified in order to maintain the dependency
between the relational tables and the XML schema.
- xmlschemafile
- An input parameter of type VARCHAR (1024) that is a fully qualified
path to the XML schema document on disk that describes the output
produced by the event monitor. The XML schema document elements are
annotated with information that maps XML elements and attributes to
the relational tables and their columns.
This parameter is used
register an XSR object. If there is no XSR object registered and enabled
for the type of event monitor specified in evmon_type, then an XSR object is registered as follows:
- If xmlschemafile is NULL, then the procedure
uses the XML schema file on disk that corresponds to value specified
for evmon_type, as follows:
- LOCKING
- sqllib/misc/DB2EvmonLocking.xsd
- PKGCACHE
- sqllib/misc/DB2EvmonPkgCache.xsd
- UOW
- sqllib/misc/DB2EvmonUOW.xsd
- If you specify the name of an XML schema file, then that file
is used to register and enable the XSR object for decomposition.
- If you specify values for the xsrschema and xsrobjectname parameters, then XSR object is created with
these names. Otherwise, the XSR object is named as using the defaults
previously described for xsrobjectname.
Important: If an XSR object has previously been registered
and is enabled for decomposition, this parameter is ignored. If you
want to register an XSR object using a different XML schema file,
you must first drop the existing XSR object.
- tabschema
- An input parameter of type VARCHAR (128) that represents the SQL
schema name where the event monitor relational tables are created.
If this value is NULL, then the authorization ID of the current session
user is used. The SQL schema under which the tables are created is
determined as follows:
- If <db2-xdb:SQLSchema> is specified, use this
schema;
- If <db2-xdb:defaultSchema> is specified, use
this schema;
- If neither of these values is specified, use the value from the sqlschema input parameter.
Note: When an XML schema
is registered for decomposition, the XSR schema repository creates
a dependency between each table referenced in the schema and the XSR
object that corresponds to this schema. Which means the XSR object
name is linked to a unique set of relational tables in the database.
If you reference an existing XSR object, its data is always decomposed
and inserted into the tables to which the XSR object was linked.
- tbsp_name
- An input parameter of type VARCHAR(128) that indicates the table
space where the relational tables are created. The default value for
this parameter is NULL. The table space name specified on the CREATE
TABLE statement within the XML schema file takes precedence over this
input parameter.
- options
- An input parameter of type VARCHAR(1024) which represents a list
of keyword options supported by this table function. Each option must
be delimited using a semicolon (;) character. The possible values
are:
- RECREATE_FORCE
- Indicates that the relational tables are dropped and re-created
before decomposition.
- RECREATE_ONERROR
- Indicates that the relational tables are dropped and re-created
in the following situations:
- If the XSR object is not registered, but the tables exist.
- On the first failed decomposition attempt. Subsequent failures
are returned, and no attempts are made to re-create the tables.
If an error occurs, for example, a table space full error or
an authorization error, the procedure does not filter the SQLCODE
returned by the decomposition procedure. The procedure treats all
negative SQLCODES equally and tries to re-create the tables.
- PRUNE_UE_TABLE
- Indicates that any binary events that are successfully inserted
into relational tables are to be pruned (that is, deleted) from the
UE table. Pruning occurs in the same unit of work in which the inserts
into the relational tables are performed.
- UPGRADE_TABLES
- Indicates that the relational tables produced
by this procedure are to be altered so that the table definitions
match those defined in the XSR object xsrobjectname for the current release. Specify this parameter if you want to upgrade
any relational tables that were created in an earlier release to reflect
any changes made for the current release. The following types of changes
might occur from release to release:
- New columns might be added to tables
- New tables might be added to output of the event monitor
- Column definitions might change (For example data type, or length).
If you do not use the UPGRADE_TABLES option,
then the existing table definitions are retained. Data for any new
columns or tables added in the current release is not written to the
relational tables.If UPGRADE_TABLES is specified,
the original xsrobjectname must also be explicitly
specified.
- commit_count
- An input parameter of type INTEGER. The possible values are as
follows:
- -1
- Commit after every 100 successful documents decomposed.-1 is the
the default value.
- 0
- Never commit.
- n
- Commit after every n documents successfully
decomposed.
- fullselect
- An input parameter of type CLOB(2M) that represents the fullselect
statement from an unformatted event table. The fullselect statement
is a query that conforms to the rules of the SELECT statement. The
query must follow the following rules:
- The query must use the "*" clause or specify all the columns of
the unformatted event table. Otherwise an error is returned. The columns
must be specified in the same order as returned by the DESCRIBE statement
of the unformatted event table.
- The query must select only from an unformatted event table.
- The WHERE clause can use any of the non-LOB columns of the unformatted
event table to filter out events.
Authorization
EXECUTE privilege on the EVMON_FORMAT_UE_TO_TABLES
stored procedure.
SELECT privilege on the unformatted event
table, if you did not create it.
CREATE privilege to create
the relational tables in the specified SQL schema.
INSERT privilege
to insert into the relational tables, if you did not create them.
All privileges required by the XDB_DECOMP_XMP_FROM_QUERY procedure.
Default PUBLIC privilege
In a non-restrictive database, EXECUTE privilege is granted to PUBLIC
when the procedure is automatically created.
Usage notes
- Relationship of records in a UE table to the output of the EVMON_FORMAT_UE_TO_TABLES
table function
There is not a one-to-one mapping between the records written
to the UE table and the output of the EVMON_FORMAT_UE_TO_TABLES procedure.
Some events generate multiple records in the UE table; some result
in just one record being added. When writing data to relational tables,
the EVMON_FORMAT_UE_TO_TABLES procedure might, in some cases combine
information in multiple UE table records into a single relational
table, or it may produce more than one row in different output tables.
- Table creation
In order for decomposition to occur, a set of relational tables
must exist. The EVMON_FORMAT_UE_TO_TABLES procedure creates the relational
tables automatically, as follows:
- The procedure parses the event monitor XML schema file to find
the <db2-mon:createStmt> elements. Each element
contains a complete CREATE TABLE statement.
- The procedure extracts and runs the CREATE TABLE statements.
The <db2-mon:createStmt> is a child
element of the existing <db2-xdb:table> element.
Only the EVMON_FORMAT_UE_TO_TABLES procedure recognizes and uses this
element. All other procedures that parse the XML schema file, such
as the XSR objects, ignore this element.
Do not qualify the
table name within the <db2-mon:createStmt>.
- XML schema files from release to release
The default XML schema files provided by each event monitor
always reflects the XML schema for the current release. So, when you
run EVMON_FORMAT_UE_TO_TABLES (or EVMON_FORMAT_UE_TO_XML), the output
reflects the monitor elements defined for that event monitor in that
release. The next section describes what happens if the schema files
for the event monitors happen to change over time. Understanding the
impact of these changes is important if you create tables using the
EVMON_FORMAT_UE_TO_TABLES procedure, and then apply a fix pack or
upgrade to a new release.
- Impact of schema updates on tables produced by EVMON_FORMAT_UE_TO_TABLES
New monitor elements are likely to be added to event monitors
in future fix packs or releases. These new monitor elements might
result in new columns or even new tables being produced by the EVMON_FORMAT_UE_TO_TABLES
procedure. However, if you already have tables that were created by
this procedure before a fix pack was applied, or before upgrading
to a new release, you need to do the following to have the new relational
columns or tables created:
- For fix pack updates
- If relational tables produced by EVMON_FORMAT_UE_TO_TABLES before
the installation of the latest fix pack still exist, you must force
the creation of a new set of tables based on the new schema shipped
in the fix pack if you want to see the new monitor elements in relational
format.
To force the EVMON_FORMAT_UE_TO_TABLES procedure to use
the new schema shipped in the fix pack and create new tables, perform
the following steps:
- Break the dependency between the currently registered version
of the XML schema (see the note under
the tabschema parameter of the EVMON_FORMAT_UE_TO_TABLES procedure for more information
about schema registration) and the existing tables by performing one
of the following actions:
- Drop one of the existing tables that were produced by EVMON_FORMAT_UE_TO_TABLES
- Drop the registered XML schema object associated with the existing
tables using the DROP XSROBJECT statement. For example, to drop the
registered XML schema object associated with the tables produced by
EVMON_FORMAT_UE_TO_TABLES for the locking event monitor for DB2 V9.7, use the following command: DROP XSROBJECT EVMON_LOCKING_SCHEMA_SQL09070.
- Alter any existing column that corresponds to an annotated monitor
element in the currently registered XML schema object.
- Run the EVMON_FORMAT_UE_TO_TABLES procedure, using the FORCE option. This option causes the old tables to be dropped,
and a new set of tables to be produced. If you omit this option, a
SQL0601N error is returned.
This process is illustrated in Example 5: Picking up new elements in a fix pack update.
If you do not perform
the preceding steps, existing tables are updated based on the previously
registered schema file. Any new columns or tables that might have
been added in the fix pack are not reflected in the output of the
EVMON_FORMAT_UE_TO_TABLES procedure.
- For release upgrades
- Unless you specify otherwise, the default version of the XML schema
file for the current release is used when you call the EVMON_FORMAT_UE_TO_TABLES
procedure. So, if you upgrade to a new release of the DB2 product, then, by default, the new version of the schema
file is used when you run the procedure.
If tables from the previous
release do not exist, EVMON_FORMAT_UE_TO_TABLES produces tables using
the most recent schema. However, if tables from the previous release
exist, you must use the FORCE or RECREATE_ONERROR options to cause the old tables to be replaced by new ones. Otherwise,
a SQL0601N error is returned. Example 6: Picking up new elements in a release update shows an example of recreating
the tables using the default schema for a new release.
Alternatively,
you can continue to use the existing tables, without adding any new
columns or tables that might have been introduced in the latest release.
To have the existing tables updated, you must specify the name of
the registered XML schema file that was used to create the tables
for the xsrobjectname parameter of the EVMON_FORMAT_UE_TO_TABLES
procedure. Example 7: Using the previous relational tables on a release update shows an example of using the
schema from a previous release.
Note: You cannot pick up any new columns or tables
introduced in fix packs or in new releases while retaining the data
that was previously in the relational tables produced by EVMON_FORMAT_UE_TO_TABLES.
Picking up any new columns requires the tables to be re-created.
- Partial events
If partial or incomplete events exist in the UE table, a message
(SQL443N) is returned when you run EVMON_FORMAT_UE_TO_TABLES. Incomplete
events can occur when an agent finishes processing before the entire
event record can be inserted in to the UE table. This situation can
sometimes arise where locking is involved, particularly in partitioned
database environments. For example, when the LOCKWAIT threshold is
exceeded, details about the holder of the lock are written to the
UE table. However, details about agents waiting for a lock on the
same object are not captured until the lock times out or the waiter
acquires the lock. If EVMON_FORMAT_UE_TO_TABLES is run before the
agent waiting for the lock has written its information, then only
a part of the information about the lock might exist in the UE table.
To see details about the incomplete events, run EVMON_FORMAT_UE_TO_XML
with the LOG_PARTIAL_EVENTS option.
Example 1:
Using default parameters
A user named Paul calls the procedure
using the default parameters and requires all events that are part
of the service class STUDENTS to be inserted into the relational tables.
EVMON_FORMAT_UE_TO_TABLES (
'UOW', NULL, NULL, NULL, NULL, NULL, NULL, -1,
'SELECT * FROM UOWUE
WHERE service_subclass_name = 'STUDENTS'
ORDER BY event_id, event_timestamp')
The results
of the call are as follows:
- The procedure parses the DB2EvmonUOW.xsd file,
which is the default XML schema file, to identify the set of relational
tables to create.
- The relational tables are created under SQL schema Paul.
- The XML schema is registered with an XSR object name of PAUL.EVMON_UOW_SCHEMA_SQL09070
- XSR object is enabled for decomposition.
- Data is decomposed and inserted into the tables under SQL schema
Paul.
Example 2:
Attempting to use tables under a different schema
In a continuation
of the previous example, a user named Dave calls the stored procedure,
setting the tabschema parameter to Paul.
EVMON_FORMAT_UE_TO_TABLES (
'UOW', NULL, NULL, NULL, 'Paul', NULL, NULL, -1,
'SELECT * FROM UOWTBLE
ORDER BY event_timestamp')
The results of the call
are as follows:
- The procedure parses the DB2EvmonUOW.xsd file,
which is the default XML schema file, to identify the set of relational
tables to create.
- The procedure attempts to create the tables under schema Paul.
However, an error is returned because the relational tables currently
exist under the SQL schema PAUL. Previously existing tables cannot
be used when a new XSR object is being registered.
Example 3:
Attempting to use tables under a different schema
In a continuation
of the previous example, a user named Greg calls the stored procedure
setting the input parameter xsrschema to Paul.
EVMON_FORMAT_UE_TO_TABLES (
'UOW', 'Paul', NULL, NULL, NULL, NULL, NULL, -1,
'SELECT * FROM UOWTBL
ORDER BY event_timestamp')
The results of the call
are as follows:
- The XSR object Paul.EVMON_UOW_SCHEMA_SQL09070, which exists, is enabled for decomposition.
- If Greg has INSERT privileges on the tables, then data is decomposed
and inserted into the relational tables under SQL schema Paul. The
existing XSR object Paul.EVMON_UOW_SCHEMA_SQL09070 is used, so the SQL schema for the relational tables is obtained
from the XSR object, instead of being provided as an input parameter
to the procedure.
Example 4:
Using the RECREATE_FORCE option
In a continuation of the
previous example, Paul wants to re-create the tables again, but in
table space MYSPACE. Paul calls the procedure with the RECREATE_FORCE option and the tbsp_name parameter.
EVMON_FORMAT_UE_TO_TABLES (
'UOW', NULL, NULL, NULL, NULL, 'MYSPACE', 'RECREATE_FORCE', -1,
'SELECT * FROM UOWTBL
ORDER BY event_timestamp')
The results of the call
are as follows:
- The XSR object Paul.EVMON_UOW_SCHEMA_SQL09070, which exists, is enabled for decomposition.
- The RECREATE_FORCE option is set.
- The XML schema file is retrieved from the schema repository and
parsed to identify the set of relational files.
- The current tables are dropped and created again in the MYSPACE
table space.
- Data is decomposed and inserted into the new tables.
Example 5:
Picking up new elements in a fix pack update
A new XML element
called
"db2EventNew" has been added to the XML schema file of
the locking event monitor in the latest fix pack. Paul wants to pick
up the new element to use in the decomposition of an XML file. To
do so, he follows the following steps:
- Paul drops the XSR object created in the original release:
DROP XSROBJECT EVMON_LOCKING_SCHEMA_SQL09070
- He calls the procedure with the RECREATE_ONERROR option.
EVMON_FORMAT_UE_TO_TABLES (
'LOCKING', NULL, NULL, NULL, NULL, NULL, 'RECREATE_ONERROR', -1,
'SELECT * FROM LOCK
ORDER BY event_timestamp')
The results of the call
are as follows:
- The XSR object does not exist, so the default DB2EvmonLocking.xsd schema file is parsed to identify the set of relational tables.
- As the RECREATE_ONERROR option was specified,
the existing tables are dropped and re-created.
Example 6:
Picking up new elements in a release update
Paul is upgrading
to a new DB2 release and wants
to pick up the new changes in the event monitor XML schema file.
Paul calls the procedure with the RECREATE_ONERROR option.
EVMON_FORMAT_UE_TO_TABLES (
'LOCKING', NULL, NULL, NULL, NULL, NULL, 'RECREATE_ONERROR', -1,
'SELECT * FROM LOCK
ORDER BY event_timestamp')
The results of the call
are as follows:
- The XSR object Paul.EVMON_LOCKING_SCHEMA_SQL1000 does not exist.
- As the RECREATE_ONERROR option was specified,
the tables are dropped and re-created.
Example 7:
Using the previous relational tables on a release update
Greg has upgraded to a new DB2 release. Greg calls the procedure with the xsrobjectname value from the previous release.
EVMON_FORMAT_UE_TO_TABLES (
'LOCKING', NULL, 'EVMON_LOCKING_SCHEMA_SQL09070', NULL, NULL, NULL, NULL, -1,
'SELECT * FROM LOCK
ORDER BY event_timestamp')
Example 8: Using the UPGRADE_TABLES and PRUNE_UE_TABLE options
Paul created a unit of work event monitor in V9.7 that writes
its output to a UE table called UOWTABLE. He then upgrades to V10.1
and wants the relational tables produced in the previous release by EVMON_FORMAT_UE_TO_TABLES to be upgraded using the UPGRADE_TABLES option which occurs before the new data is
processed. Furthermore he wants to have the records from UOWTABLE
deleted using the PRUNE_UE_TABLE option after they
have been processed.
EVMON_FORMAT_UE_TO_TABLES (
'UOW', NULL, 'EVMON_UOW_SCHEMA_SQL09070', NULL, NULL, NULL,
'UPGRADE_TABLES;PRUNE_UE_TABLE', -1,
'SELECT * FROM UOWTABLE
ORDER BY event_timestamp')
Note: In this example, the value
'EVMON_UOW_SCHEMA_SQL09070' must be specified for the xsrobjectname parameter, since 'EVMON_UOW_SCHEMA_SQL09070' is the name of the
XSR object that was used in the most recent release where EVMON_FORMAT_UE_TO_TABLES was run to create relational
tables from a UE table.
Information returned
There is no output
from the procedure except the SQLCA. The SQLCA indicates the completion
status. The possible SQLCODES are:
- 0
- All events were successfully inserted into the relational tables.
- 16278
- One or more events were not inserted into the relational tables.
The tokens within the SQLCA contain the total number of documents
that were attempted and the total number of documents that succeeded
decomposition.
A diagnostic file is also created; and the name
and location of that diagnostic file is stored in the db2diag log
files, located in the DB2 diagnostic
path.
- negative sqlcode
- An error has occurred, and investigating the SQLCODE message can
provide additional details regarding the failure. For additional diagnostic
messages, see the db2diag log files located in
the DB2 diagnostic path.