EVMON_FORMAT_UE_TO_TABLES procedure - move an XML document to relational tables

Syntax

>>-EVMON_FORMAT_UE_TO_TABLES--(--evmon_type--,--xsrschema--,---->

>--xsrobjectname--,--xmlschemafile--,--tabschema--,------------->

>--tbsp_name--,--options--,--commit_count--,--fullselect--)----><

The schema is SYSPROC.

Table function 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 a 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.

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:

1. If the XSR object is not registered, but the tables exist.
2. 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.

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 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.

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

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

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.

Examples

• Example 1: Using default parameters
• Example 2: Attempting to use tables under a different schema
• Example 3: Attempting to use tables under a different schema
• Example 4: Using the RECREATE_FORCE option
• Example 5: Picking up new elements in a fix pack update
• Example 6: Picking up new elements in a release update
• Example 7: Using the previous relational tables on a release update

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')

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')

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')

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')

Example 5: Picking up new elements in a fix pack update

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')

Example 7: Using the previous relational tables on a release update

Greg has upgraded to a new DB2 release and does not want to pick up the new changes in the event monitor XML schema file. 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')

Information returned