Managing Oracle WebLogic Integration Solutions

Querying Oracle WebLogic Integration Reporting Data

As described in the About System Administration section in Using The Oracle WebLogic Integration Administration Console, the reporting database tables contain information regarding events that occur during the execution of processes. These tables are created by the SQL commands in the file wli_archive.sql described in Configuring a Production Database..

To generate reports from reporting database, you will need to run SQL queries. This section describes useful tables and provides example queries:

The WLI_PROCESS_EVENT_ARCH Table

As a process executes, events are generated that track its execution. The events generated depend on the tracking level configured (see Managing Process Tracking Data section in Using Oracle WebLogic Integration Administration Console). For example, if the tracking level for a process is set to Full or Node, two events, start node and end (or abort) node, are generated by each node.

If the process tracking data is transmitted to the reporting database, each event is stored as row in WLI_PROCESS_EVENT_ARCH table. The row contains the process name (a URI value), process instance ID, process event type (see com.bea.wli.management.archiving.TrackingEventType), and other values.

The PROCESS_LABEL column is set only for events generated by calls to:

JpdContext.setProcessLabel(String)

The WLI_DOCUMENT_DATA Table

Invoking the JpdContext.trackData(payload) method generates an event of type EVENT_TYPE_PROCESS_LOG. If the data is transmitted to the reporting database, each event is stored as a new row in the WLI_DOCUMENT_DATA table. The payload is stored in the DATA column of that table, and the EVENT_DATA_ID column provides a link to the event in the WLI_PROCESS_EVENT_ARCH table.

In addition to containing the results of trackData(), the WLI_DOCUMENT_DATA table contains unhandled exceptions generated by the process instance and business message payloads (if business messages are tracked).

The valid types for WLI_DOCUMENT_DATA.TYPE are defined in com.bea.wli.management.archiving.DocumentDataType.

For additional information about:

Example Queries

The following example queries are provided:

Get the Average Elapsed Time for a Process

To get the average elapsed time for a given process on a given day, the SQL query is:

SELECT AVG(EVENT_ELAPSED_TIME) FROM WLI_PROCESS_EVENT_ARCH
WHERE PROCESS_TYPE = PROC_TYPE
AND ACTIVITY_ID = 0
AND EVENT_TYPE = 3
AND (EVENT_TIME >= START_TIME AND EVENT_TIME < END_TIME)
AND DEPLOYMENT_ID IN
	(SELECT MAX(DEPLOYMENT_ID)
	FROM WLI_PROCESS_EVENT_ARCH 
	WHERE PROCESS_TYPE = PROC_TYPE)

In this query, PROC_TYPE should be replaced by a value from the WLI_PROCESS_EVENT_ARCH table, and START_TIME and END_TIME should be literal timestamps.

Get the Average Elapsed Time for a Node

To get the average elapsed time for a given node in a given process on a given day, the SQL query is:

SELECT AVG(WPEA.EVENT_ELAPSED_TIME)
FROM WLI_PROCESS_EVENT_ARCH WPEA, WLI_PROCESS_DEF_ARCH WPDA
WHERE WPEA.PROCESS_TYPE = PROC_TYPE
AND WPEA.EVENT_TYPE = 3
AND (WPEA.EVENT_TIME >= START_TIME and WPEA.EVENT_TIME < END_TIME)
AND WPEA.PROCESS_TYPE = WPDA.PROCESS_TYPE
AND WPEA.ACTIVITY_ID = WPDA.ACTIVITY_ID
AND WPEA.DEPLOYMENT_ID = WPDA.DEPLOYMENT_ID
AND WPDA.USER_NODE_NAME = NODE_NAME
AND WPDA.DEPLOYMENT_ID IN
	(SELECT MAX(DEPLOYMENT_ID) FROM WLI_PROCESS_DEF_ARCH
	WHERE PROCESS_TYPE = PROC_TYPE)

In this query, PROC_TYPE and NODE_NAME should be replaced by values from the WLI_PROCESS_EVENT_ARCH table, and START_TIME and END_TIME should be literal timestamps.

Get Results of the trackData() API

To get the result of all trackData() calls for a given process type, the SQL query is:

SELECT WDD.DATA, WDD.TYPE, WPEA.PROCESS_INSTANCE
FROM WLI_DOCUMENT_DATA WDD, WLI_PROCESS_EVENT_ARCH WPEA
WHERE WDD.EVENT_DATA_ID = WPEA.EVENT_DATA_ID 
AND WPEA.PROCESS_TYPE = PROC_TYPE
AND WPEA.EVENT_TYPE = 20

In this query, PROC_TYPE should be replaced by a value from the WLI_PROCESS_EVENT_ARCH table.