Tchater maintenant avec le support
Tchattez avec un ingénieur du support

Space Manager with LiveReorg 9.0 - User Guide

Smart Start for Space Manager Collect Statistics Profile DML Activity Manage Storage Reorganize Objects with Reorg Manager Partition Tables with the Partitioning Wizard Run and Monitor Scripts Run Reports Options Troubleshooting Administration Functions Appendix About Us

Manage Scheduled Scripts

Note: To view full execution information in the Script Properties dialog, right-click a script from the Detail pane of the Job Monitor and select Properties.

Restart Scheduled Scripts

Restart Scheduled Scripts

When execution of a scheduled script is interrupted, you can use the Restart function to start it again from the Scripts/Job Monitor. This function lets you easily resume execution after it is cancelled or stops due to an error, a system failure, or a system shutdown. Execution automatically resumes from the correct point.

Review the following best practices for script restart:

  • If execution is interrupted due to a failure, resolve the problem that caused the error before you restart the script. To determine why a script failed, check the Status Message column of the Scripts/Job Monitor. Also check the script log.
  • Before you restart a script, do not make changes to the structure of the original table or copy table (if this exists). Also, do not make changes to indexes, constraints, or triggers. Finally, do not change or drop LiveReorg objects or collection triggers. LiveReorg objects should be left in place in the LiveReorg tablespace while a script is in an interrupted state.
  • Do not restart a script with the Execute or Schedule functions. These functions run a script from the beginning instead of from a restart point.
  • If you want to review a script before restarting it, double-click the script in the Detail pane of the Scripts/Job Monitor to open it in the SQL Editor. The script is displayed in read-only mode. Changes cannot be made before the script is restarted. If you open the script in another way and make changes, the Restart function is disabled afterward and the script’s status is set to Unscheduled.

To restart a script with the Restart function:

  1. If a script was interrupted due to failure, resolve the problem behind the failure. See Troubleshoot Script Execution Errors for troubleshooting tips.

    Tip: To determine why a script failed, check the Status Message column of the Scripts/Job Monitor. Also check the script log. To view the log, right-click the script in the Detail pane and select Retrieve Scripts Logs.

  2. Right-click an interrupted script in the Scripts/Job Monitor and select Restart/Resume.
  3. When a message asks if you want to restart a script immediately, click Yes.

 

Related Topics

Restart for Live and Partitioning Scripts

Restart for live reorganization and partitioning scripts is managed by QSA. The agent determines whether a script can be restarted, at what point it should be restarted, and which statements must be run. Only one restart command is used.

The restart point for live reorganization and partitioning scripts is the “point of failure”. This is the point at which execution failed or was cancelled:

  • If a script failed before the copy table was created or if it failed due to a collection trigger error, execution restarts with creation of the copy table.
  • If a script failed after the copy table was created, execution restarts from the statement on which it failed. This can be at any point after creation of the copy table. Restart does not recreate a table and indexes that were successfully created when a script was first run. This saves time during restart for large tables and tables with many indexes.

How QSA Determines Whether a Script Can Be Restarted

When a live reorganization or partitioning script is restarted, QSA first validates the script. QSA makes sure all LiveReorg commands are scripted correctly and that LiveReorg objects and triggers have not been changed or dropped.

If QSA detects problems with LiveReorg commands or objects, a script cannot be restarted. In this case, the script is given a status of 324 Aborted no restart or 325 Aborted restart after you try to restart it. For help with cleanup of LiveReorg objects, contact Quest Software Support. Once cleanup is performed, a new script can be generated for objects in a failed or cancelled script.

How QSA Determines Where a Script Should Be Restarted

If a live reorganization or partitioning script can be restarted, QSA determines where it should restart—with creation of the copy table or at a point after that. To determine the restart point, QSA checks its QUEST_QSA_PDATA table. This table records all tasks performed during each run of a live reorganization or partitioning script.

The agent also “plays back” or reviews commands and statements from the last run of a script and identifies which statements do not need to be repeated and which
ones do. This ensures that restart is efficient. LiveReorg commands sometimes must be repeated. SQL statements are never repeated. They are only executed if they failed or were not executed during the last run. For example, if a CREATE INDEX statement was successfully executed for a copy index, the copy index is not created again after restart.

Restart from Creation of the Copy Table

Restart begins with creation of a copy table if:

In both cases, execution is initiated with the ALWAYS_RUN and RESTART_POINT commands. The first SQL statement executed is the CREATE TABLE statement that creates a copy of the original table. All remaining SQL statements and LiveReorg commands for the table are then executed.

Restart After Creation of the Copy Table

Restart bypasses creation of a copy table if that table was created during the last
run of a script. In this case, execution is initiated with the ALWAYS_RUN command. The first SQL statement executed is the statement on which execution was interrupted. This can be anywhere after the CREATE TABLE statement that creates a copy of the original table.

If execution failed on any of the statements inside a “parallel block”, execution resumes from the start of the parallel block and the entire block is executed. However, any indexes created during the first run of a parallel block are not created again. (A parallel block is a section of script where parallel QSA processes are used to create indexes or compile dependencies.)

If any WHENEVER, WAIT, or RETRY commands occur after the restart point,
all are active and used for the tasks they apply to, including specific tasks within parallel blocks.

Note If the switch is approved before a script is interrupted, it is still approved after the script is restarted. You do not need to approve the switch for the table that was being reorganized at the time of interruption. However, you do need to approve it for all subsequent tables in the script.

Activity While a Script Is Interrupted

If a live reorganization or partitioning script is interrupted before the switch for a table, DML activity can continue against the table. This is the case whether the table is being reorganized with the online switch or the T-Lock switch. Live transactions are posted to the reorganized copy table when the script is restarted.

If a live reorganization or partitioning script is interrupted during the switch, whether DML activity can continue depends on switch style:

  • Online switch—When this switch style is used for a table, DML activity can continue against the original table. Live transactions are posted to the reorganized copy table when the script is restarted.
  • T-Lock switch—When this switch style is used for a table, activity against the original table should be stopped until after the script is restarted. The T-Lock placed on the original table at the start of the switch stage remains in effect while the script is interrupted.

Logging for Restarted Live and Partitioning Scripts

Execution logs for restarted live reorganization and partitioning scripts contain comments that identify the restart statement. One comment is displayed near the top of the log and reads as follows: “Restarting at statement 39” (where 39 is the statement number in the log). A second comment is displayed at the point where QSA restarted execution. This reads: “Restart playback is now off at line 39”.

Logs also identify which script statements QSA played back and which ones were executed:

  • Statements that QSA plays back but does not execute are marked with asterisks.
  • Statements that QSA executes do not have asterisks.

In both scripts and logs, the restart command for each table is assigned a sequential number. This is displayed in parentheses beside the command. It associates a table with its parallel blocks (used for tasks performed in parallel). The following is an example of the command and sequential number for the second table in a script:

EXECUTE QUEST_EXEC.RESTART_POINT('2');

  

Related Topics

Restart for Standard Scripts

Documents connexes

The document was helpful.

Sélectionner une évaluation

I easily found the information I needed.

Sélectionner une évaluation