The Oracle Reports Services handle incoming client requests to run reports using a job queue mechanism, which are then submitted to the report engines. This Report Server job queue information is stored on disk, in binary format, in the Report Server instance’s persistence file .i.e. $ORACLE_INSTANCE/reports/server/<reports server name>.dat
Now querying this information can easily be done using Enterprise Manager or by calling RWSERVLET commands from a web browser, but what if you wanted to query this data using PL/SQL to monitor activity, past executions, or perhaps report on the reporting statistics themselves? This is where the RW_SERVER_JOB_QUEUE table and it’s PL/SQL API comes in handy, as the data can be pushed from the job queue repository into a database. It’s pretty easy to setup, but does require a restart of the Reports Server…
Database Setup
Firstly, create a new schema (optional) or pick an existing schema to host the objects:
create user rw_server identified by password default tablespace users temporary tablespace temp; grant create session, resource to rw_server;
Connect to the database as the new user (or an appropriate one) from the Oracle Reports Server machine and run the following SQL:
cd $ORACLE_HOME/reports/admin/sql sqlplus rw_server/password@snapdb @rw_server.sql
The following database objects are created:
RW_SERVER_JOB_QUEUE – main queue table
RW_SERVER_QUEUE – view for 6i compatibility purposes
RW_SERVER – package contaning the following functions: insert_job, remove_job, clean_up_queue
NOTE: The .SQL script is certified to work with 10g+ databases.
Reports Server Configuration
These next steps can vary somewhat depending on the version of Oracle Reports being used.
In the example below, I’m using release 11gR2 and the Enterprise Manager GUI to perform the setup. Take a look at MOS Doc ID 72531.1 for more details.
- Log into Oracle Enterprise Manager (EM)
- Expand “WebLogic Domain” and click on “ClassicDomain”
- From the drop down menu go to “Security” and “Credentials”
- Expand “Reports” and select “Create Key”
- Type in a key name e.g. RepoKey
- Select “Password” for the “Type”
- Enter the same “User Name” used when you ran the rw_server.sql script above.
- Enter the “Password” for the same user and click OK.
- Still from within EM, navigate to “Administration” and “Advanced Configuration” for your Reports Server instance.
- Go to the “Job Status Repository” section and tick “Enable Job Status Repository DB”.
Enter the following details:
Username: rw_server Password Key: csf:reports:RepoKey Database: [hostname/IP]:[DB listener port]:[SID]
Finally, restart the WLS_REPORTS managed server.
Querying the Database
Once the server is back and a reports are being executed again, you should now see data being populated in the RW_SERVER_JOB_QUEUE table:
-- Jobs Running SELECT COUNT(*) "Jobs Running" FROM RW_SERVER_JOB_QUEUE WHERE STATUS_CODE = 1 AND JOB_TYPE != 'Scheduled' / -- Jobs Summary by Status SELECT COUNT(*) COUNT, DECODE(STATUS_CODE,1,'ENQUEUED',2,'OPENING',3,'RUNNING',4,'FINISHED',5,'TERMINATED_W_ERR') STATUS FROM RW_SERVER_JOB_QUEUE WHERE STATUS_CODE IN (1,2,3,4,5) GROUP BY STATUS_CODE /
It’s worth pointing out that this table housekeeps itself (but you can always create a job/trigger to archive off the data elsewhere) and that deleting data from this table isn’t linked to the Reports Server .dat persistence file. The job meta data is pumped across to this table via the packaged API.
A list of columns and information stored in the table can be found here:
Structure of the RW_SERVER_JOB_QUEUE Table
References:
Reports Server Configuration File (jobStatusRepository)
How to Setup jobStatusRepository Feature in Reports 11g (Doc ID 858200.1)
Using the Reports Server Queue PL/SQL Table and API – RW_SERVER_JOB_QUEUE (Doc ID 72531.1)
Job Submission Status Codes