Schedule Scripts
After creating a reorganization script, you can schedule the script to execute the reorganization at a later time. This is useful if you want to run a reorganization during a period when database activity is light. You can schedule a script through one of the reorganization wizards, the Script/Job Monitor, or script Properties.
Important: Reorganization scripts should be run as soon as possible after they are generated. If the data structures of the objects in the script are modified between script generation and execution, the DDL and DML statements in the script can cause errors and/or data loss during script execution.
The scheduling methods described in this topic allow you to schedule a one-time execution. You can also schedule a live reorganization to run on a recurring basis. This can be done with recurring script generation and can be set up at the time you create the script in the Reorg Manager. This method also provides the advantage of reducing the time between script generation and execution. See Schedule Script Generation for more information.
After scheduling a script, you can monitor and manage script execution in the Script/Job Monitor. See Manage Scheduled Scripts.
About Scheduled Scripts and QSA
Scheduled scripts are executed (and scheduled) by QSA. If you want QSA to execute a script, you must schedule it. Some scripts, such as Live reorganization scripts and scripts that use FastCopy or parallel QSA processes, must be executed by QSA because they contain commands that must be interpreted by QSA. That is why these scripts must be scheduled and cannot be run manually through the Editor.
Note: Even when you select the Execute Immediately option in the reorganization wizard (Schedule Script Execution page), the script is scheduled with QSA.
Schedule Scripts in the Reorg Manager
In the Reorg Manager (or Partitioning Wizard), select the Run at option on the Schedule Script Execution page to schedule and run a reorganization script. Scheduled scripts are run by QSA after they are stored in the Space Manager repository. See Schedule Script Execution in Wizards to learn how to schedule a script in the Reorg Manager.
Schedule Scripts from the Job Monitor
If you do not schedule a script when you store it from the Reorg Manager or the Partitioning Wizard, you can schedule it later from the Script/Job Monitor window.
To schedule a script from the Job Monitor
- Right-click a script in the Job Monitor and select Schedule.
- In the Schedule Script dialog, select a date and time to execute the script. Click OK.
Schedule Scripts from Script Properties
You can also schedule or reschedule script execution with schedule functions in the Script Properties dialog (Job Monitor window).
To schedule a script from Script Properties
- Right-click a script or script group in the Job Monitor and select Properties.
-
Select one of the Scheduling Info options in the Script Attributes tab:
- Execute Immediately—Select this option to run the script immediately.
- Schedule to Run at—Select this option to run the script on a certain date at a certain time.
- Click OK to save your schedule and display it in the Scheduled column of the Script/Job Monitor.
Tip: You can cancel a scheduled script execution with the Unschedule function in the Script/Job Monitor. This function prevents execution of scripts at their scheduled time, but leaves them stored in the Space Manager repository.
Schedule Script Execution in Wizards
Use the Schedule Script Execution page of the Reorg Manager or Partitioning Wizard to run, schedule, or store a script. You can also open the script in the Editor to review it (or run it).
Prior to scheduling the script, review space usage information on the Space Usage Summary tab. See Review Space Usage for more information.
From the Schedule Script Execution page you can do any of the following:
- View (or run) the script in the Editor
- Schedule the script to run once
- Execute the script immediately (schedule script to run immediately)
- Store the script without scheduling it
Note: For LiveReorg scripts, if you want to schedule the script to run on a recurring basis, return to the Choose Scheduling Method page and select Schedule Script Generation. See Choose a Scheduling Method (LiveReorg) and About Scheduling a Recurring Reorganization for more information.
See About Running and Scheduling Scripts to learn more about your script execution options and to review important considerations and best practices.
To view (or run) the script in the Editor
- Click View in Editor to open the script in the Editor window to review it before you schedule it.
-
You can also run the script interactively from the Editor window. See Run Scripts from the Editor for more information.
Important: Live reorganization scripts and scripts that use FastCopy or parallel QSA processes cannot be run from the SQL Editor. They must be scheduled and run by QSA. These scripts contain commands that must be interpreted by QSA. To run a script in QSA, it must be scheduled (even when executed immediately).
To schedule the script to run once
-
Select Run at to run the script at a certain date and time.
Note: Reorganization scripts should be run as soon as possible after they are generated. If the data structures of the objects in the script are modified between script generation and execution, the DDL and DML statements in the script can cause errors and/or data loss during script execution.
See Schedule Scripts, to learn more about scheduling scripts.
- Select a date and time at which you want the scheduled script to run.
- Enter a name for the script.
- Click Submit. Space Manager stores the scheduled script and displays a confirmation message.
- Click Yes in the prompt to open the Script/Job Monitor (or select Manage | Job/Script Monitor).
- Your script displays in the list of scripts/jobs in the Job Monitor. If not, click
to refresh the list.
- Use the Script/Job Monitor window to manage your script. You can reschedule, execute, or delete the script. You can monitor status of the script or change the approval option (automatic or wait for approval). You can open the script or view script logs. See Using Job Monitor to Monitor Scripts for more information.
- After the script is stored, you can close the Reorg Manager or Partitioning Wizard.
To execute the script immediately
- Select Execute immediately to run the script immediately, as soon as it is stored in the Space Manager repository.
- Enter a name for the script.
- Click Submit. The script is scheduled to run immediately by QSA. Monitor script execution from the Scripts/Job Monitor.
To store the script without scheduling it
- Select Store without scheduling to store the script and schedule it later from the Script/Job Monitor.
- Enter a name for the script.
- Click Submit. Space Manager displays a confirmation message that the script is successfully stored.
- Click Yes in the prompt to open the Script/Job Monitor (or select Manage | Job/Script Monitor).
- Your script displays in the list of scripts/jobs in the Job Monitor. If not, click
- To schedule the script, right-click the script and select Schedule. Select a date and time to execute the script.
- Or you can right-click the script and select Properties. Then select one of the Scheduling Info options in the Script Attributes tab.
Tip: Click Auto-Refresh in the Script/Job Monitor window to specify a frequency at which to automatically refresh the scripts list.
Run Scripts from the Editor
Use the View in Editor option in the Reorg Manager (or Partitioning Wizard) to run reorganization scripts interactively from the SQL Editor. As a script runs, you can monitor execution directly from the SQL Editor. You can also use the View in Editor option to open a script and review it before you schedule it.
Important: Live reorganization scripts and scripts that use FastCopy or parallel QSA processes cannot be run from the SQL Editor. They must be scheduled and run by QSA. These scripts contain commands that must be interpreted by QSA. To run a script in QSA, it must be scheduled (even when executed immediately).
To run scripts from the Editor
- Select View in Editor in the schedule window of Reorg Manager or the Partitioning Wizard. The script is displayed in the SQL Editor.
- Run the script from the SQL Editor in continuous mode or single-step mode:
- To run the entire script in continuous mode, click Run in the toolbar or press F9.
-
To run the script in single-step mode (one statement at a time), click Single Step in the toolbar or press F8 from the first statement. Repeat for each statement in the script.
Note: If the Connection dialog displays, enter the user name and password of an Oracle user with the DBA role or privileges for Space Manager. Then click OK to connect to your database.
- As the script is executed, the SQL Editor scrolls through script statements. The current statement is highlighted. Check the status bar at the bottom of the window. If a statement is executed successfully, processing time is displayed. If an error occurs, an error message is displayed.
- When script execution is finished, an execution log for all script statements displays in the Spool tab. View this to see script start and stop times, any errors that occurred, and the output of all executed statements.
Schedule Recurring Scripts to Run
You can schedule a live reorganization to run at a specific interval, date, and time using the Schedule Task wizard. Space Manager saves the reorganization settings as a Reorg Plan (XML file). By saving a Reorg Plan, you can update or complete the settings, generate current reorganization scripts, and schedule recurring reorganizations.
Saving the Reorg Manager settings is different from saving the resulting reorganization script. Saving the settings is more versatile because it accommodates changes that may happen to the objects and database over time.
For example, assume you select a table with five columns to reorganize. You save the Reorg Manager settings as a Reorg Plan and you save the current reorganization script. A week later, you add a sixth column to the table. Then, you open the saved Reorg Plan and generate and run a new reorganization script. Space Manager reorganizes all six columns instead of the original five columns that existed when you saved the file. If you had run the script that you saved a week before, the new table would only have the original five columns and you would lose the sixth column and its data.
To learn how to setup a recurring reorganization for a LiveReorg script (including saving a Reorg Plan), see Schedule Script Generation.
To learn about Reorg Plans, see Save Reorg Manager Settings.