7
Scheduling workbooks

Scheduling workbooks

This chapter explains how to enable users to schedule workbooks and contains the following topics:

• "What are workbooks and scheduled workbooks?"

• "Why schedule workbooks?"

• "What happens when an end user schedules a workbook?"

• "What Oracle database features support workbook scheduling?"

• "Where to store the results of scheduled workbooks?"

• "What are the prerequisites for scheduling workbooks?"

• "How to confirm that the DBMS_JOB package is installed"

• "How to specify the owner of the tables containing scheduled workbooks results"

• "How to control the frequency that the server checks for new scheduled jobs"

• "How to view and manage scheduled workbooks"

• "About scheduled workbooks and compatibility between the current version of Discoverer and previous releases"

What are workbooks and scheduled workbooks?

A workbook is a collection of Discoverer worksheets. Workbooks are essentially documents containing query definitions. Discoverer end users can store their workbooks centrally in the database. In Discoverer Desktop, users can also store workbooks on their own PC or on a network file server.

A scheduled workbook is a workbook that has been set by the user to run automatically at a particular date, time, and frequency. Scheduled workbooks are placed on a process queue on the database server. End users can schedule workbooks using Discoverer Plus and Desktop. As the Discoverer manager, you can use Discoverer Administrator to monitor and maintain scheduled workbooks.

Note the following:

• Users can run workbooks in the background using the Discoverer command line options (Desktop only). Such workbooks are not scheduled workbooks.

• Scheduled workbooks, scheduling information and the results of scheduled workbooks are not exported when you export a business area.

• If the objects of the EUL used by a scheduled workbook changed between the time when the workbook is scheduled and the time when the result set is displayed, the scheduled workbook's status is set to indicate that the EUL has changed.

Why schedule workbooks?

From a Discoverer end user's point of view, the ability to schedule workbooks is useful for:

• reports that take a long time to run

• reports that have to run at regular intervals

For example, a Discoverer end user might want to run a report that they know will take a long time to complete. The user can schedule the report to run overnight and have the results ready to view the next morning.

From a Discoverer manager's point of view, workbook scheduling is useful to prevent long-running queries from adversely affecting system performance. You can force users to schedule workbooks (either all workbooks, or only those workbooks that will exceed a predicted time that you specify), and you can further specify the time periods that scheduled workbooks are permitted to run.

What happens when an end user schedules a workbook?

The table below shows what happens when an end user schedules a workbook in Discoverer Plus.

Sequence	Action	Notes
1.	User selects worksheet (or worksheets) to include in scheduled workbook	N/A
2.	User specifies date, time, and frequency at which scheduled workbook is to run	N/A
3.	Discoverer Plus confirms that scheduling the workbook does not exceed user's limit on number of scheduled workbooks	The limit on the number of scheduled workbooks restricts the number of scheduled workbooks that can be maintained at any one time. As the Discoverer manager, you specify this limit on the "Privileges dialog: Scheduled Workbooks tab". If the user exceeds their limit, Discoverer Plus displays a message and the workbook is not scheduled. The job_queue_processes value controls the maximum number of jobs that can be run at any one time on the server. For more information, see "What Oracle database features support workbook scheduling?". Setting a limit on the number of scheduled workbooks is not the same as the job_queue_processes value in the Oracle initialization file.
4.	Discoverer adds scheduled workbook to DBMS_JOB within Oracle database kernel	N/A
5.	Periodically, the job queue process is activated and the next job in the queue is run	The scheduled workbook is processed entirely on the server. You use the job_queue_interval value (desupported in Oracle9i (and later) Enterprise Edition databases) in the Oracle database initialization file to specify the length of time that the job queue process is inactive. For more information, see "How to control the frequency that the server checks for new scheduled jobs".
6.	A database table is created and populated with the output or result set of the scheduled workbook	The result set is stored under the schema specified by the Discoverer manager. For more information, see "Where to store the results of scheduled workbooks?".
7.	User can view scheduled workbook	N/A
8.	User can delete result set when no longer required - at which point the table is dropped	As the Discoverer manager, you can specify how long the results of a user's scheduled workbook can remain in the database before being automatically deleted (see "Privileges dialog: Scheduled Workbooks tab").

What Oracle database features support workbook scheduling?

Discoverer takes advantage of native scheduling functionality in the Oracle database to schedule workbooks. Specifically, Discoverer makes use of the DBMS_JOB package.

You control the processing of the DBMS_JOB package using the following parameters in the Oracle database initialization file (the INIT<SID>.ORA file).

Parameter in INIT<SID>.ORA	Use to:
job_queue_processes	Use this parameter to specify the maximum number of concurrent processing requests that can be used to process DBMS_JOB. The default value is zero, which means processing requests will not be created. If you want to have ten processing requests to be handled simultaneously, set this parameter to 10. If any other applications use DBMS_JOB, set this parameter to at least 2. Hint: You need more than one job queue process because a job that fails (for any reason) might continue to be re-submitted and prevent any other jobs in the queue from being submitted.
job_queue_interval	Use this parameter to specify the time frequency (in seconds) after which job processes are started to process pending jobs. The default is 60 seconds, which means that every 60 seconds the job processes start to serve processing requests. What you set this parameter to depends on how frequently you want the process to start up and serve the requests that have been made. Oracle recommends that you set this parameter to at least 600 seconds (i.e. 10 minutes). Note that this parameter also affects the Discoverer summary management feature (for more information about summary folders, see Chapter 13, "Managing summary folders"). Note: The job_queue_interval parameter is desupported in Oracle9i (and later) Enterprise Edition databases. In Oracle9i Enterprise Edition databases, job queue processes are dynamically spawned by a coordinator job queue (CJQ0) background process. The coordinator periodically selects jobs that are ready to run from the jobs shown in the dba_jobs view.

For more information about setting the parameters in the INIT<SID>.ORA file, see "How to control the frequency that the server checks for new scheduled jobs".

Note that workbook scheduling is only available when running against an Oracle database because both DBMS_JOB and the INIT<SID>.ORA file are only available with Oracle databases.

Where to store the results of scheduled workbooks?

Discoverer stores the results of scheduled workbooks in database tables. Before an end user can schedule a workbook, you must decide which database user (schema) is to own those tables. You have two choices as described in the table below:

Owner	Notes
the schema running the scheduled workbook	The advantage of specifying the result set storage in the end user's schema is that a database limit can be specified on the maximum amount of data an end user can store in the database. If the result set is stored under the end user's schema, you keep control over the maximum amount of space one individual end user can fill with result sets. If the end user creates a scheduled workbook that fills the space, only that end user's schema is affected. A disadvantage is that it increases the maintenance overhead. Note: Since Oracle Applications users often share the same secure database account, they are advised to create a scheduled workbook results schema to store the results of scheduled workbooks.
scheduled workbook results schema	The advantage of specifying the result set storage in a scheduled workbook results schema is that it is generally easier to manage than using multiple database user schemas. Also each end user does not need to set up additional database privileges to run scheduled workbooks. The disadvantage is that space quota is shared and so could be exhausted by a single end user. Note: Oracle Applications users must use the Oracle Applications APPS database user as the scheduled workbook results schema (for more information, see "How to set scheduled workbook limits"). Since the APPS database user is already the default schema for Oracle Applications users, there is no need to run the SQL script (i.e. batchusr.sql) to create a scheduled workbook results schema.

For more information about how to specify where the results of scheduled workbooks are stored, see "How to specify the owner of the tables containing scheduled workbooks results".

What are the prerequisites for scheduling workbooks?

Before an end user can schedule a workbook, you must:

• configure the database to enable the scheduled workbooks feature by:
  • confirming that the DBMS_JOB package is already installed on the database and installing the package if it is not already installed (for more information, see "How to confirm that the DBMS_JOB package is installed")

  • specifying the database user that will own the tables containing the results of scheduled workbooks (for more information, see "How to specify the owner of the tables containing scheduled workbooks results")

  • setting the frequency of scheduled workbook processing (for more information, see "How to control the frequency that the server checks for new scheduled jobs")

• grant the Schedule Workbooks privilege to the database user (for more information, see Chapter 6, "How to specify the tasks a user or role (responsibility) can perform")

How to confirm that the DBMS_JOB package is installed

Before an end user can schedule a workbook, the DBMS_JOB package must have already been installed on the database. If the DBMS_JOB package has not yet been installed, you must install it.

To confirm that the DBMS_JOB package is installed:

1. Start SQL*Plus (if it is not already running) and connect as the database administrator.

   For example, if SQL*Plus is already running, you might type the following at the command prompt:

   SQL> CONNECT dba_user/dba_pw@database;
   
   

   Where dba_user is the database administrator and dba_pw is the database administrator password.

2. Type the following at the command prompt:

   SQL> select * from all_objects where object_name='DBMS_JOB' and object_type 
   = 'PACKAGE'; 
   
   

   If the above statement returns one or more rows, the DBMS_JOB package is already installed on the database.

   If the above statement returns no rows, the DBMS_JOB package has not yet been installed. You must install the DBMS_JOB package before users can schedule workbooks.

To install the DBMS_JOB package if it is not yet installed, for Oracle9i databases:

1. Start SQL*Plus (if it is not already running) and connect as the SYS user, or a user to which the sysdba privilege has been granted.

   For example, if SQL*Plus is already running, you might type the following at the command prompt:

   SQL> CONNECT sys/sys_pw@database AS SYSDBA;
   
   

   Where sys is the SYS user and sys_pw is the SYS user password.

2. Type the following at the command prompt:

   SQL> start <ORACLE_HOME>/rdbms/admin/dbmsjob.sql;
   
   

3. Type the following at the command prompt:

   SQL> start <ORACLE_HOME>/rdbms/admin/prvtjob.plb;
   
   

To install the DBMS_JOB package if it is not yet installed, for Oracle databases earlier than Oracle9i:

1. Choose Run from the Windows Start menu (on the database machine).

2. Type one of the following at the command prompt to start the appropriate version of the DBA utility:

   Version of Oracle:	Type the following:
   Oracle 8.0 and above	SVRMGRL
   Oracle8i Personal Edition	SVRMGR

3. Log on to the DBA utility as the database administrator.

4. Type the following at the command prompt:

   SVRMGRL> connect internal
   
   

5. Type the following at the command prompt:

   SVRMGRL> start <ORACLE_HOME>/rdbms/admin/dbmsjob.sql;
   
   

6. Type the following at the command prompt:

   SVRMGRL> start <ORACLE_HOME>/rdbms/admin/prvtjob.plb;
   
   

Notes

• In some operating systems, the RDBMS directory is suffixed with the version number (e.g. RDBMS73).

How to specify the owner of the tables containing scheduled workbooks results

The results of scheduled workbooks are stored in database tables. Discoverer's default behavior is to store scheduled workbooks in the EUL owners schema. Before an end user schedules a workbook, you must decide which database user is to own those tables. You have two choices:

• the database user running the scheduled workbook

• a scheduled workbook results schema

For more information about these two choices, see "Where to store the results of scheduled workbooks?"

Regardless of the choice you make, the database user that will own the tables containing scheduled workbook results must have certain database privileges.

Specify that the database user scheduling a workbook is to own the database tables containing the results of that workbook

To specify a database user to own the database tables containing the results of that workbook:

1. Start SQL*Plus (if it is not already running) and connect as the database administrator.

   For example, if SQL*Plus is already running, you might type the following at the command prompt:

   SQL> CONNECT dba_user/dba_pw@database;
   
   

   Where dba_user is the database administrator and dba_pw is the database administrator password.

2. Type the following at the command prompt:

   SQL> grant CREATE PROCEDURE to <user name>;
   SQL> grant CREATE TABLE to <user name>;
   SQL> grant CREATE VIEW to <user name>;
   
   

   where <user name> is the name of the database user that is to schedule workbooks.

   Note that you must grant these privileges directly to the database user and not to a database role.

Use a script to specify a scheduled workbook results schema to own the scheduled workbook results tables (i.e. not the database user scheduling the workbook)

To specify a scheduled workbook results schema to own the results tables of scheduled workbooks:

Note: Oracle Applications users should not use this script, but instead use the existing Oracle Applications APPS user (for more information, see "How to set scheduled workbook limits").

Note: You must know the user name and password of the EUL owner in order to run the script batchusr.sql.

1. Start SQL*Plus (if it is not already running) and connect as the database administrator.

   For example, if SQL*Plus is already running, you might type the following at the command prompt:

   SQL> CONNECT dba_user/dba_pw@database;
   
   

   Where dba_user is the database administrator and dba_pw is the database administrator password.

2. Type the following at the command prompt:

   SQL> start<ORACLE_HOME>\discoverer\util\batchusr.sql;
   
   

3. Enter the user name, password and database connection details for the scheduled workbook results schema being created by the script batchusr.sql.

4. (optional) Change the tablespace settings for the scheduled workbook results schema.

   Note: Ask your database administrator if you are unsure about these settings.

5. Enter the EUL owner user name for the EUL that the scheduled workbook results schema will have access to.

   Note: The script creates a scheduled workbook results schema and grants the following database privileges:

   • CREATE PROCEDURE

   • CREATE TABLE

   • CREATE VIEW

   • SELECT ANY TABLE

   The scheduled workbook results schema created by the batchusr.sql script is granted the SELECT ANY TABLE database privilege to enable access to the underlying data needed for workbook scheduling. Without this grant, this database user's access to the underlying data might be limited.

   If you do not want the scheduled workbook results schema to have the SELECT ANY TABLE database privilege on the underlying data, you must revoke the privilege manually.

6. Connect to Discoverer Administrator as the EUL owner that you used in the previous step (when running the script batchusr.sql).

7. In Discoverer Administrator select Tools | Privileges and display the "Privileges dialog: Scheduled Workbooks tab".

   You must use Discoverer Administrator to choose the database user that will own the scheduled workbook result tables created in the database.

8. Use the Show scheduling limits for user drop down list to select the EUL owner.

9. Use the Select the user to own the results created in the database: drop down list to select the scheduled workbook results schema that you created in the script batchusr.sql.

10. Click OK.

    You have used the script batchusr.sql to create a scheduled workbook results schema and then used the Privileges dialog to select the scheduled workbook results schema to be used for the EUL owner.

    For more information about scheduled workbook privileges, see Chapter 6, "How to set scheduled workbook limits".

Notes

• The scheduled workbook results schema created by the script batchusr.sql can only have access to one EUL.

• Be aware that a Discoverer end user could run a scheduled workbook that fills the available result set space. Other database users would not be able to run scheduled workbooks until it is cleared.

• The scheduled workbook results schema created by the batchusr.sql script will not be able to directly schedule a workbook using Discoverer Plus.

How to control the frequency that the server checks for new scheduled jobs

You use the following two parameters in the INIT<SID>.ORA file to control scheduled workbook processing:

• job_queue_processes

• job_queue_interval (desupported in Oracle9i (and later) Enterprise Edition databases)

For more information about these parameters, see "What Oracle database features support workbook scheduling?".

To control the frequency of scheduled workbook processing:

1. Locate the INIT<SID>.ORA file.

   The INIT<SID>.ORA file is held in <ORACLE_HOME>\database. The default name of the file is INITORCL.ORA, where ORCL is the <SID> name.

2. Add the following two lines to the INIT<SID>.ORA file:

   job_queue_processes = <a_value>
   job_queue_interval = <a_value_in_seconds>
   
   

   where:

   • <a_value> is the number of concurrent processing requests that can be used to process DBMS_JOB (if other applications use DBMS_JOB, we recommend you set this parameter to at least 2)

   • <a_value_in_seconds> is the time frequency (in seconds) after which job processes are started to process pending jobs (we recommend you set this parameter to at least 600 seconds)

   For example, you might add the following two lines to the INIT<SID>.ORA file:

   job_queue_processes = 2
   job_queue_interval = 600
   
   

Notes

• Discoverer's summary management feature and workbook scheduling feature both use the Oracle database's native scheduling capability. The values you specify for job_queue_processes and job_queue_interval will affect both features. The database must be stopped and re-started for these changes to take effect.

How to view and manage scheduled workbooks

As the Discoverer manager, you will want to monitor the status of currently scheduled workbooks, as well as:

• view error messages for scheduled workbooks that have not run successfully

• delete scheduled workbook results from the database

• remove scheduled workbooks from the process queue

• edit scheduled workbook settings

Note: If you have an Oracle Applications mode EUL you must login as the EUL owner to view and manage scheduled workbooks.

To view and manage a scheduled workbook:

1. Choose Tools | Manage Scheduled Workbooks to display the "Manage Scheduled Workbooks dialog".

   Figure 7-1 Manage Scheduled Workbooks dialog

   
   
   Text description of the illustration schwbm.gif
2. Select the user or role whose scheduled workbooks you want to display in the list from the Show workbooks for drop down list.

   Hints: You can:

   • show all scheduled workbooks by selecting All Users from the drop down list

   • sort the list using different columns by clicking the appropriate column heading

   • see a description (where one exists) of the currently selected scheduled workbook in the Description field.

3. (optional) To view an error message for a scheduled workbook that has not run successfully, select the scheduled workbook and click View Error.

   The View Error button is only enabled for scheduled workbooks when the status is set to indicate that an error occurred while running the query.

4. (optional) To prevent a scheduled workbook from running by removing it from the process queue:
   1. Select the scheduled workbook.

   2. Click Unschedule.

   3. Click Yes to confirm that you want to remove the scheduled workbook.

5. (optional) To delete the results of a scheduled workbook from the database (i.e. after the scheduled workbook has run), select the scheduled workbook and click Delete.

6. (optional) To edit a scheduled workbook scheduling information, select the workbook and click Edit... to display the Schedule Workbook dialog and:
   • use the "Schedule Workbook dialog: Schedule tab" to view the sheets included in the scheduled workbook, set the schedule time and repeat interval.

   • use the "Schedule Workbook dialog: General tab" to view the name of the scheduled workbook, view and edit the description, choose whether to save just the latest results and set the interval after which results will automatically be deleted.

7. (optional) To see the effect of the changes you have made in the "Manage Scheduled Workbooks dialog", click Refresh.

8. Click Close to close the Manage Scheduled Workbooks dialog.

Notes

• The Delete button only appears when you select a scheduled workbook that has run.

• If a scheduled workbook uses EUL objects that have changed between the time when the workbook is scheduled and the time when the result set is displayed, the scheduled workbook's status is set to EUL has changed.

About scheduled workbooks and compatibility between the current version of Discoverer and previous releases

New version of batch PL/SQL package is installed into user's schema

When you carry out an EUL upgrade to this version of Discoverer, a new version of the batch PL/SQL package is installed into the user's schema alongside the existing package. Preserving the existing PL/SQL package prevents any existing scheduled workbook batch jobs from being destroyed. Function names in the new batch PL/SQL package however, remain the same as the existing batch PL/SQL package.

Run script to specify that a scheduled workbook results schema is to own the results tables

When you upgrade the EUL to this version of Discoverer and the following conditions are true:

• the database user that schedules a workbook does not own the database tables containing the workbook results

• you want a different database user (a scheduled workbook results schema) to own the database tables containing the workbook results

In this case you must install the new version of the batch PL/SQL package (EUL5_BATCH_USER) using the SQL script batchusr.sql. The script is located in the <ORACLE_HOME>\discoverer\util directory. The new version of the batch PL/SQL package can be installed into the scheduled workbook results schema (i.e. the user's schema that owns the tables containing the workbook results). For more information, see "Use a script to specify a scheduled workbook results schema to own the scheduled workbook results tables (i.e. not the database user scheduling the workbook)".