Oracle Report Server Queue Monitoring

This article explains how to store the Oracle Reports Server queue information into a database. It discusses the installation of the tables and packages and its setup. The RW_SERVER_JOB_QUEUE table and PL/SQL API is an excellent feature to easy conserve and analyze historic meta information of a Reports Server’s job queue by simple database functionality. This functionality should enable in all application servers in which more than 3000 reports per day to save the historic data

Report Server Queue

Meta data of the job queue is stored in the (binary) persistent DAT-file in the file system. By default it is populated via the RWSERVLET function SHOWJOBS or SHOWMYJOBS and in the Enterprise Manager (AS Console). Using the Reports Server job queue repository, this data can be pushed into a database – the Reports Server’s job queue repository. Available there, this information is no longer a temporary snapshot and common SQL and PL/SQL methods can be used to keep and analyze it. The RW_SERVER_JOB_QUEUE table and PL/SQL API is a well-established optional functionality of the Reports Server available (with some modifications) since version 6.0.5.35.

How to setup

Step 1 - Get connected to any database schema. It is good practice to have a separate schema built to store the Reports Server’s queue repository (e.g.: RWJOBQ )

SQL> CREATE USER “RWJOBQ” IDENTIFIED BY ORACLE DEFAULT TABLESPACE “OTHER” TEMPORARY TABLESPACE “TEMP”;

SQL> GRANT CREATE SESSION,RESOURCE TO “RWJOBQ”;

Step 2 - Execute RW_SERVER.sql script (located in $ORACLE_HOME/reports/admin/sql). It creates

• the table RW_SERVER_JOB_QUEUE storing the job repository data
• the view RW_SERVER_QUEUE (available for backwards compatibility)
• the package RW_SERVER supposed to maintain the data records

cd $ORACLE_HOME/reports/admin/sql
sqlplus RWJOBQ/oracle@DBNAME
SQL> @rw_server.sql

Step 3 – Configure the Reports Server to work with the specific database schema

• The Reports Server’s configuration file is located at $ORACLE_HOME/reports/conf/<RepServName>.conf (make changes in all report server files)
• Ensure the repositoryconn property of the <jobStatusRepository> element is unhidden and active.
• Enter DB authentication credentials for the above generated DB schema: dbusername, dbpassword and dbtnsname
• In Oracle Reports 10gR2 (and below) the jobStatusRepository element might look like this in your server configuration file:

<jobStatusRepository>
<property name=”repositoryConn” value=” RWJOBQ/oracle@DBNAME” confidential=”yes” encrypted=”no”/>
</jobStatusRepository>

Step4 - Restart the Reports Server. Now when you run the report, job queue information will be stored into RW_SERVER_JOB_QUEUE table