7 Monitoring Events from Custom Applications

Event Format Required by Oracle BPEL Process Analytics

Oracle BPEL Process Analytics requires that events published by custom applications use the same format as that which is defined for the publishEvent request document schema, described in "publishEvents Operation".

Publishing Database Events to the Preconfigured OJMS Queue Using PL/SQL Packages

The preconfigured OJMS queue, bam_genericmonq, is installed when you install Oracle BPEL Process Analytics. It enables Oracle BPEL Process Analytics to monitor events that are generated by the same database as that which serves as the Oracle BPEL Process Analytics repository.

The following topics describe the prerequisites for using this queue and how to publish events to it:

• Prerequisites for Publishing Events to the bam_genericmonq Queue

• PL/SQL Packages for Publishing to the bam_genericmonq Queue

GRANT EXECUTE ON bam_generic_monitor TO user-name;
EXECUTE DBMS_AQADM.GRANT_SYSTEM_PRIVILGE ('ENQUEUE_ANY', 'user-name', TRUE);

OAS_HOME\integration\bam\admin\sql\CreateGenericMonitorPkg.sql

OAS_HOME\integration\bam\admin\sql\bamShowGenericEvent.sql

SQL> @bamShowGenericEvent.sql
Number of events in bam_genericmonq: 7
Displaying the most recent event in queue :
<?xml version="1.0"?> <BAMEvent><header>
<timestamp>2005-05-13T07:58:36.772</timestamp> <timezone>-07:00</timezone>
<type>deleteEmployee</type> </header>  <payload><ROWSET><ROW num="1">
<EMPNO>30528</EMPNO> <ENAME>Name_30528</ENAME> <JOB>job_30528</JOB> <
<MGR>30528</MGR> <HIREDATE>2005-04-22"T"16:00:00</HIREDATE> <SAL>3500</SAL>
<COMM>0</COMM> <DEPTNO>10</DEPTNO> </ROW></ROWSET> </payload>  </BAMEvent>
PL/SQL procedure successfully completed.

Publishing Events to a User-Specified JMS Queue

You can publish events to a user-specified OJMS or JMS queue to do either of the following:

• Publish events from a J2EE application

  See Oracle Application Server Containers for J2EE Services Guide for conceptual information, and see the demonstrations at the following URL for examples:

  http://www.oracle.com/technology/tech/java/oc4j/demos/904/index.html
  
  

• Publish events from an Oracle database

  See the scripts for implementing the BooksOnLine example in the Streams Advanced Queuing User's Guide and Reference for examples.

Using the XSU Command Line to Generate .xsd Files for Database Tables

To monitor a generic event source, Oracle BPEL Process Analytics requires that events be defined using XSD or DTD. When the generic event source is an Oracle Database, you can use the XML Schema Utility to generate .xsd files from database tables, which then require minor adjustments for use with Oracle BPEL Process Analytics.

See Oracle XML Developer's Kit Programmer's Guide for complete details about the XML Schema Utility. Follow these steps to generate an .xsd file from an Oracle database table or view for use with Oracle BPEL Process Analytics:

1. Add the following libraries to the JAVA classpath:

   • classes12.jar (JDBC)

   • xsu12.jar (XML Schema Utility, which contains the Oracle XML class)

   • xmlparserv2.jar (Oracle XML parser)

2. Create an .xsd file for the database table data for which you want to capture events, by saving the output from the following command, where:

   • username/password is the user name and password for the account that you want to use to access the database

   • db-machine-name is the name of the machine on which the database is running

   • db-port is the port number on which the listener is listening, and db-sid is the database system identifier (SID)

   • SQL-statement is the SQL statement that specifies the table and table attributes to include in the schema definition file (.xsd)

   java -classpath $CLASSPATH OracleXML getXML -user "username/<password" -conn "jdbc:oracle:thin:@db-machine-name:db-port:db-sid" -withSchema SQL-statement"
   
   

3. Delete the <Document> and <ROWSET> elements from the .xsd file and save it. You do not need to save it to a particular directory, but you will be asked to specify the directory location when you create a composite event on the generic event source.

See "Generating an .xsd File for the EMP Table" for an example of this process.

Generating an .xsd File for the EMP Table

Follow these steps to generate an .xsd file for the EMP table and all of the table attributes:

Step 1: Specify the Java classpath.

Add the following classes to the Java classpath:

• classes12.jar (JDBC)

• xsu12.jar (XML Schema Utility, which contains the Oracle XML class)

• xmlparserv2.jar (Oracle XML parser)

Step 2: Generate XML output for the database table of interest.

Use the XML Schema Utility to generate the required schema definition using the following command, which:

1. Connects user SCOTT with password TIGER to a database with SID db1 through port 1521 of host myhost, using the Thin driver.

2. Executes the query SELECT * FROM EMP WHERE ROWNUM < 1

   The WHERE ROWNUM < 1 clause is specified to exclude the EMP table data from the generated .xsd file.

3. Converts the result to XML.

4. Places the results into a file named Employee.xsd.

If you want to try this example on your system, you need to replace myhost:1521:db1 with the system, port number, and database SID for your environment.

java -classpath $CLASSPATH OracleXML getXML -user "scott/tiger" -conn "jdbc:oracle:thin:@myhost:1521:db1" -withSchema "select * from emp where rownum<1" > Employee.xsd

The output should appear similar to that shown in Example 7-1. However, your output will not contain bold text; the bolding is added in Example 7-1 for clarity in describing the next step.

<?xml version = '1.0'?>
<DOCUMENT xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
 <xsd:element name="ROWSET">
    <xsd:complexType>
       <xsd:sequence>
          <xsd:element name="ROW" minOccurs="0" maxOccurs="unbounded">
             <xsd:complexType>
                <xsd:sequence>
                   <xsd:element name="EMPNO" type="xsd:integer" minOccurs="0"/>
                   <xsd:element name="ENAME" nillable="true" minOccurs="0">
                      <xsd:simpleType>
                         <xsd:restriction base="xsd:string">
                            <xsd:maxLength value="10"/>
                         </xsd:restriction>
                      </xsd:simpleType>
                   </xsd:element>
                   <xsd:element name="JOB" nillable="true" minOccurs="0">
                      <xsd:simpleType>
                         <xsd:restriction base="xsd:string">
                            <xsd:maxLength value="9"/>
                         </xsd:restriction>
                      </xsd:simpleType>
                   </xsd:element>
                   <xsd:element name="MGR" type="xsd:integer" nillable="true" minOccurs="0"/>
                   <xsd:element name="HIREDATE" type="xsd:string" nillable="true" minOccurs="0"/>
                   <xsd:element name="SAL" type="xsd:float" nillable="true" minOccurs="0"/>
                   <xsd:element name="COMM" type="xsd:float" nillable="true" minOccurs="0"/>
                   <xsd:element name="DEPTNO" type="xsd:integer" nillable="true" minOccurs="0"/>
                </xsd:sequence>
                <xsd:attribute name="num" type="xsd:integer"/>
             </xsd:complexType>
          </xsd:element>
       </xsd:sequence>
    </xsd:complexType>
 </xsd:element>
</xsd:schema>
<ROWSET xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:noNamespaceSchemaLocation="#/DOCUMENT/xsd:schema[not(@targetNamespace)]"/>
</DOCUMENT>

Step 3: Edit and Save the Output.

The output generated in the previous step contains two extraneous elements, <DOCUMENT> and <ROWSET>, that you must delete. These elements are bold in the previous step.

1. Delete the <DOCUMENT> and <ROWSET> elements.

2. Save the edited output.

Publishing Events from the EMP Table to the bam_genericmonq Queue

Connect to the database as the BAM user and grant privileges to the user SCOTT, by running the addGrantsForScottSchema.sql file located in the following directory, where OAS_HOME is the directory specification for the Oracle Application Server home in which you installed Oracle BPEL Process Analytics:

OAS_HOME\integration\bam\admin\sql

For example:

> sqlplus BAM/BAM
SQL> @addGrantsForScottSchema.sql
Grant succeeded.
PL/SQL procedure successfully completed.
SQL> exit

To create triggers on the EMP table and publish the SQL INSERT and DELETE statements to the bam_genericmonq queue, connect to the database using the username SCOTT and the password TIGER and run the SQL script, createScottSchemaTriggers.sql.

> sqlplus SCOTT/TIGER
SQL> @createScottSchemaTriggers.sql
Trigger created.
No errors.
Trigger created.
No errors.
SQL> exit

Connecting to the Generic Event Source in the Admin Console

Follow these steps to specify Oracle Database as a generic event source:

1. Open the Oracle BPEL Process Analytics Console and log in, as described in "Getting Started with the Admin Console".

2. Click Admin.

3. Click Event Source.

4. Click the Create button associated with the BPA Generic Sources table.

5. Specify ScottTiger for the event source name and specify the rest of the field values as described in Table 2-2.

6. Click Finish.

Creating a Composite Event for an Oracle Database Generic Event Source

Follow these steps to create a composite event that includes insert and delete events, correlated by the EMPNO column:

1. If the Oracle BPEL Process Analytics Console is not open, open it and log in, as described in "Getting Started with the Admin Console".

2. Click Modeling.

3. Click Composite Events.

4. Click the Add Composite Event button.

5. Select the ScottTiger event source, and then click the Create button. The first page of the Add Composite Event Wizard opens.

6. On the Properties page of the wizard, follow these steps:

   1. In the Name field, enter ScottTiger_CE.

   2. In the Description field, enter: Composite event for ScottTiger example.

   3. In the Timeout field, leave the values as 0 Days 8 Hours 0 Minutes.

   4. Click Next. The Schema Definitions page opens.

7. On the Schema Definitions page, follow these steps:

   1. In the Schema File field, enter the complete path to the Employee.xsd file you created in "Generating an .xsd File for the EMP Table".

   2. Click the Add button.

   3. Leave the Root Element for DTD field empty.

   4. Click Next. The Events page opens.

8. On the Events page, follow these steps:

   1. In the Event Name field, enter Employee. (This string, including uppercase and lowercase characters used in the string, must match the second parameter passed to the first bam.bam_generic_monitor.publish procedure in the SQL script that generates the events (createScottTigerSchemaTriggers.sql, in this example).

   2. In the Schema Name field, keep Employee.xsd.

   3. In the Root Element for DTD field, keep ROWSET.

   4. Click the Add button.

   5. In the Event Name field, enter deleteEmployee. (This string, including uppercase and lowercase characters used in the string, must match the second parameter passed to the first bam.bam_generic_monitor.publish procedure in the SQL script that generates the events (createScottTigerSchemaTriggers.sql, in this example.)

   6. In the Schema Name field, keep Employee.xsd.

   7. In the Root Element for DTD field, keep ROWSET.

   8. Click the Add button.

   9. Select deleteEmployee as the End Event.

   10. Click Next. The Attributes page opens.

9. In the Attributes page, follow these steps:

   1. In the first table, select Employee.

   2. Expand the navigation tree.

   3. Select EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, and DEPTNO.

   4. In the first table, select deleteEmployee.

   5. Expand the navigation tree.

   6. Select EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, and DEPTNO.

   7. Click Next. The Attribute Properties page opens.

10. In the Attribute Properties page, follow these steps:

    1. In the first table, select Employee.

    2. In the second table, select EMPNO as the correlation attribute.

    3. In the first table, select deleteEmployee.

    4. In the second table, select EMPNO as the correlation attribute.

    5. Click Finish.

Generating Events and Viewing Them in the Dashboard

In a production system, events would be generated as a result of normal system operations. For the purposes of this example, connect to the database using the SCOTT/TIGER account, and issue the statements such as the following to generate events. Be sure to commit each statement and, for ease of viewing in the Real-Time Viewer, pause for a few minutes in between issuing each statement:

SQL> INSERT INTO EMP VALUES(1249,'istuart','Designer',10,sysdate,2000,0,20);
SQL> COMMIT;
SQL> INSERT INTO EMP VALUES(1300,'dBradford','ANALYST',10,sysdate,30000,0,30);
SQL> COMMIT;
SQL> DELETE FROM EMP WHERE EMPNO=1249;
SQL> COMMIT;
SQL> SQL> DELETE FROM EMP WHERE EMPNO=1300;
SQL> COMMIT;

Open the Oracle BPEL Process Analytics Dashboard and view the events:

1. Open the Oracle BPEL Process Analytics Console and log in, as described in "Getting Started with the Admin Console".

2. Click Dashboard.

3. In the Switch View box, select Real-Time Viewer.

4. In the Composite Event box, select ScottTiger_CE. The Dashboard should appear similar to Figure 7-1.

   Figure 7-1 Sample Dashboard for Generic Event Source

   
   Description of the illustration generic_rtv.gif
   
   

5. Click a data point in the Real-Time Viewer to see details about the event. For example, clicking the first data point in Figure 7-1, reveals the details shown in Figure 7-2.

   Figure 7-2 Sample Composite Event Details for Generic Event Source

   
   Description of the illustration generic_ce_details.gif