Actions tab
Use the Actions tab to create supplemental SQL statements (actions) to be executed at selected phases of an archive, delete, insert, extract, or restore process. For example, you might use this feature to audit deleted rows by executing an appropriate SQL statement before the start of the delete process, or after the deletion of each row. You can also create an SQL statement to call a stored procedure. You can define actions for all phases, including insert or restore phases, in the access definition. The access definition is copied to the archive or extract file during the processing. Any insert actions in the access definition become the default actions when data is inserted. These actions can be overridden in the table map that is used in the restore, insert, or load process. For load processing, however, actions for most phases are disregarded.
Run Action For
- Data Growth
- Actions in the access definition apply only to processing that includes archive files generated with this access definition.
- Test Data Management
- Actions in the access definition apply only to processing that includes extract files generated with this access definition.
- All Solutions
- The actions apply to processing that includes archive or extract files generated with this access definition.
Action Phase
Click the arrow to select an action phase from the list. You can create an SQL statement for each selected action phase or use one SQL statement for multiple action phases. The box lists phases in the order in which they occur.
Actions are in two classes:
- Global Actions
- Apply to the process, and can be executed at the:
- Start of Extract Process
- End of Extract Process
- Start of Delete Process
- End of Delete Process
- Start of Insert/Restore Process
- End of Insert/Restore Process
- Local Actions
- Apply only to a selected table and can be executed:
- Before Extract of First Row from Table
- Before Extract of Row
- After Extract of Last Row from Table
- Before Delete of First Row from Table
- Before Delete of Row
- After Delete of Row
- After Delete of Last Row from Table
- Before Insert/Restore of First Row from Table
- Before Insert/Restore of Row
- After Insert/Restore of Row
- After Insert/Restore of Last Row from Table
During processing, all global and local actions for insert and restore can be executed except during load processing. During load processing, only actions for Start of Insert/Restore Process, End of Insert/Restore Process, Before Insert/Restore of First Row from Table, and After Insert/Restore of Last Row from Table can be executed; any actions for other phases are ignored.
DB Alias
Direct references to a DB alias are invalid in an SQL statement. Thus, supplementary SQL is generated to identify the database. Enter a DB alias needed to reference a table in a database other than the database referenced by the default qualifier for the access definition table list.
Action Delimiter
A character in an action statement that identifies a column value or built-in variable. To change the delimiter, click the arrow and select from the list. The action delimiter must differ from the AD delimiter.
AD Delimiter
A character in an action statement that identifies a variable defined in the access definition.
To change the delimiter, click the arrow and select from the list. The AD delimiter must differ from the AA delimiter.
SQL Statement
Select SQL Statement to type an appropriate SQL statement (Insert, Update, Delete, or Stored Procedure Call) in the text box. You can select a column value, built-in variable, or access definition variable from the list boxes to insert it, with delimiter at the cursor location.)
To validate the syntax of an INSERT, UPDATE, or DELETE statement, right-click the text box and select Validate SQL from the menu. To remove an SQL statement, right-click the text box and select Remove SQL.
The syntax for a stored procedure call is (qualifier.procedurename
(arg1,arg2, ...argn). The parentheses around the argument
list are required, and empty parentheses ( ) must
be used if no arguments are required. Arguments can be literals or
parameters. You can use a raise exception statement in a stored procedure
that is called by a Before
statement to issue a message that
includes the text OPTIM_STATE=SKIP_ROW (anywhere
in the message). That text in a message, returns the value 10 for
the Built-In Variable OPTIM_AFTER_ROW_STATUS, causing the row to be
skipped. Similarly, a stored procedure that is called by any action
(Before, After, Start, or End), can issue a message that includes
the text OPTIM_STATE=ABORT anywhere in the
message, to end the process.
See documentation for your DBMS to determine the correct syntax for a raise exception statement. The following is an example for an Oracle database:
CREATE PROCEDURE YourStoredProcedure () AS BEGIN
/* Issue User-defined error message */
raise_application_error(-20101, ‘OPTIM_STATE=SKIP_ROW'),
END YourStoredProcedure
Same SQL as Action Phase
Select Same SQL Statement as Action Phase to share an SQL statement that is specified for a selected action phase of the same class. Create the statement for one phase, select an action phase of the same class, and select Same SQL as Action Phase. Click the arrow to select the action phase that is associated with the SQL statement you want to apply. If a shared SQL statement includes one or more variables, you must be certain that the variables are available to all action phases that share the statement.
On Error Options
The Error Option determines how processing continues if an error occurs when an action is executed, :
- Stop
- Processing stops.
- Process Row
- The action is ignored for the row and processing continues.
- Skip Row
- The row is not processed, but processing of other rows continues. Skip Row is available for Before Extract of Row, Before Delete of Row, and Before Insert/Restore of Row and does not apply to load processing. The process report notes the number of rows skipped.
Column Values
Names of columns that can be used in actions that are associated with certain local action phases. When you select a column value for an action, the column name is inserted into the SQL statement as a variable, and the column value is used each time that statement is executed. Select a column name to insert it at the cursor position.
Built-Ins
Supported built-in functions that can be used in actions to return information about an action phase. Select a built-in variable name to insert it at the cursor position.
AD Variables
Lists any variables that are defined for the specified table in the access definition. Select an access definition variable to insert the name at the cursor position in the SQL Statement box. AD variables are available for extract and insert/restore action phases only.
Action Phases and Associated Built-Ins
In the following table the available action phases, the built-in functions that are associated with each, and the corresponding data type are listed.
DATE_TIME data types vary according to the DBMS:
| DBMS | Data Type |
|---|---|
| UDB | TIMESTAMP |
| Oracle | DATE |
| Sybase ASE | DATETIME |
| SQL Server | DATETIME |
| Informix® | DATETIME |
- Start of Extract Process
-
Available Built-in Variables Data Type OPTIM_ARCHIVE_ID INTEGER OPTIM_FILE_NAME
(For Optim V11.3.0.7 or lower, the name was OPTIM_ARCHIVE_FILE_NAME)VARCHAR (250) OPTIM_ACTION (returns “0” for this phase) INTEGER OPTIM_ACTION_TEXT (returns “BefExtProcess”) VARCHAR (20) OPTIM_INDEX_FILE_NAME VARCHAR (250) OPTIM_GROUP_NAME VARCHAR (8) OPTIM_USER_ID VARCHAR (30) OPTIM_SERVER_NAME VARCHAR (15) OPTIM_MACHINE_NAME VARCHAR (15) OPTIM_DESC
(For Optim V11.3.0.7 or lower, the name was OPTIM_ARCHIVE_DESC)VARCHAR (128) OPTIM_START_DATETIME vary OPTIM_CURRENT_DATETIME vary OPTIM_CURRENT_DATETIME_CHAR VARCHAR(24) OPTIM_TOTAL_ROWS_EXTRACTED INTEGER OPTIM_GUID
(For Optim V11.3.0.7 or lower, the name was OPTIM_ARCHIVE_GUID)CHAR (38) OPTIM_PROCESS_STATUS INTEGER - Before Extract of First Row from Table
-
Available Built-in Variables Data Type OPTIM_ARCHIVE_ID INTEGER OPTIM_FILE_NAME
(For Optim V11.3.0.7 or lower, the name was OPTIM_ARCHIVE_FILE_NAME)VARCHAR (250) OPTIM_ACTION (returns “1” for this phase) INTEGER OPTIM_ACTION_TEXT (returns “BefExtFirstRow”) VARCHAR (20) OPTIM_INDEX_FILE_NAME VARCHAR (250) OPTIM_GROUP_NAME VARCHAR (8) OPTIM_USER_ID VARCHAR (30) OPTIM_SERVER_NAME VARCHAR (15) OPTIM_MACHINE_NAME VARCHAR (15) OPTIM_DESC
(For Optim V11.3.0.7 or lower, the name was OPTIM_ARCHIVE_DESC)VARCHAR (128) OPTIM_START_DATETIME vary OPTIM_CURRENT_DATETIME vary OPTIM_CURRENT_DATETIME_CHAR VARCHAR(24) OPTIM_TOTAL_ROWS_EXTRACTED INTEGER OPTIM_GUID
(For Optim V11.3.0.7 or lower, the name was OPTIM_ARCHIVE_GUID)CHAR(38) OPTIM_PROCESS_STATUS INTEGER OPTIM_TBL_DBALIAS VARCHAR (12) OPTIM_TBL_SCHEMA_NAME VARCHAR (64) OPTIM_TBL_NAME VARCHAR (64) OPTIM_TOTAL_ROWS_EXTRACTED INTEGER - Before Extract of Row
-
Available Built-in Variables Data Type OPTIM_ARCHIVE_ID INTEGER OPTIM_FILE_NAME
(For Optim V11.3.0.7 or lower, the name was OPTIM_ARCHIVE_FILE_NAME)VARCHAR (250) OPTIM_ACTION (returns “2” for this phase) INTEGER OPTIM_ACTION_TEXT (returns “BefExtRow”) VARCHAR (20) OPTIM_INDEX_FILE_NAME VARCHAR (250) OPTIM_GROUP_NAME VARCHAR (8) OPTIM_USER_ID VARCHAR (30) OPTIM_SERVER_NAME VARCHAR (15) OPTIM_MACHINE_NAME VARCHAR (15) OPTIM_DESC
(For Optim V11.3.0.7 or lower, the name was OPTIM_ARCHIVE_DESC)VARCHAR (128) OPTIM_START_DATETIME vary OPTIM_CURRENT_DATETIME vary OPTIM_CURRENT_DATETIME_CHAR VARCHAR(24) OPTIM_TOTAL_ROWS_EXTRACTED INTEGER OPTIM_GUID
(For Optim V11.3.0.7 or lower, the name was OPTIM_ARCHIVE_GUID)CHAR (38) OPTIM_PROCESS_STATUS INTEGER OPTIM_TBL_DBALIAS VARCHAR (12) OPTIM_TBL_SCHEMA_NAME VARCHAR (64) OPTIM_TBL_NAME VARCHAR (64) OPTIM_TBL_ROWS_EXTRACTED INTEGER - After Extract of Last Row from Table
-
Available Built-in Variables Data Type OPTIM_ARCHIVE_ID INTEGER OPTIM_FILE_NAME
(For Optim V11.3.0.7 or lower, the name was OPTIM_ARCHIVE_FILE_NAME)VARCHAR (250) OPTIM_ACTION (returns “3” for this phase) INTEGER OPTIM_ACTION_TEXT (returns “AftExtLast Row”) VARCHAR (20) OPTIM_INDEX_FILE_NAME VARCHAR (250) OPTIM_GROUP_NAME VARCHAR (8) OPTIM_USER_ID VARCHAR (30) OPTIM_SERVER_NAME VARCHAR (15) OPTIM_MACHINE_NAME VARCHAR (15) OPTIM_DESC
(For Optim V11.3.0.7 or lower, the name was OPTIM_ARCHIVE_DESC)VARCHAR (128) OPTIM_START_DATETIME vary OPTIM_CURRENT_DATETIME vary OPTIM_CURRENT_DATETIME_CHAR VARCHAR(24) OPTIM_TOTAL_ROWS_EXTRACTED INTEGER OPTIM_GUID
(For Optim V11.3.0.7 or lower, the name was OPTIM_ARCHIVE_GUID)CHAR (38) OPTIM_PROCESS_STATUS INTEGER OPTIM_TBL_DBALIAS VARCHAR (12) OPTIM_TBL_SCHEMA_NAME VARCHAR (64) OPTIM_TBL_NAME VARCHAR (64) OPTIM_TBL_ROWS_EXTRACTED INTEGER - End of Extract Process
-
Available Built-in Variables Data Type OPTIM_ARCHIVE_ID INTEGER OPTIM_FILE_NAME
(For Optim V11.3.0.7 or lower, the name was OPTIM_ARCHIVE_FILE_NAME)VARCHAR (250) OPTIM_ACTION (returns “4” for this phase) INTEGER OPTIM_ACTION_TEXT (returns “AftExtProcess”) VARCHAR (20) OPTIM_INDEX_FILE_NAME VARCHAR (250) OPTIM_GROUP_NAME VARCHAR (8) OPTIM_USER_ID VARCHAR (30) OPTIM_SERVER_NAME VARCHAR (15) OPTIM_MACHINE_NAME VARCHAR (15) OPTIM_DESC
(For Optim V11.3.0.7 or lower, the name was OPTIM_ARCHIVE_DESC)VARCHAR (128) OPTIM_START_DATETIME vary OPTIM_CURRENT_DATETIME vary OPTIM_CURRENT_DATETIME_CHAR VARCHAR(24) OPTIM_TOTAL_ROWS_EXTRACTED INTEGER OPTIM_GUID
(For Optim V11.3.0.7 or lower, the name was OPTIM_ARCHIVE_GUID)CHAR (38) OPTIM_PROCESS_STATUS INTEGER - Start of Delete Process
-
Available Built-in Variables Data Type OPTIM_ARCHIVE_ID INTEGER OPTIM_FILE_NAME
(For Optim V11.3.0.7 or lower, the name was OPTIM_ARCHIVE_FILE_NAME)VARCHAR (250) OPTIM_GROUP_NAME VARCHAR (8) OPTIM_USER_ID VARCHAR (30) OPTIM_SERVER_NAME VARCHAR (15) OPTIM_MACHINE_NAME VARCHAR (15) OPTIM_DESC
(For Optim V11.3.0.7 or lower, the name was OPTIM_ARCHIVE_DESC)VARCHAR (128) OPTIM_START_DATETIME vary OPTIM_CURRENT_DATETIME vary OPTIM_CURRENT_DATETIME_CHAR VARCHAR(24) OPTIM_ACTION (returns “5” for this phase) INTEGER OPTIM_ACTION_TEXT (returns “BefDelProcess”) VARCHAR (20) OPTIM_TOTAL_PROCESS_STATUS INTEGER OPTIM_TOTAL_ROWS_PROCESSED INTEGER OPTIM_TOTAL_ROWS_IN_ERROR INTEGER OPTIM_TOTAL_ROWS_REMAINING_TO_BE_PROCESSED INTEGER OPTIM_GUID
(For Optim V11.3.0.7 or lower, the name was OPTIM_ARCHIVE_GUID)CHAR (38) - Before Delete of First Row from Table
-
Available Built-in Variables Data Type OPTIM_ARCHIVE_ID INTEGER OPTIM_FILE_NAME
(For Optim V11.3.0.7 or lower, the name was OPTIM_ARCHIVE_FILE_NAME)VARCHAR (250) OPTIM_GROUP_NAME VARCHAR (8) OPTIM_USER_ID VARCHAR (30) OPTIM_SERVER_NAME VARCHAR (15) OPTIM_MACHINE_NAME VARCHAR (15) OPTIM_DESC
(For Optim V11.3.0.7 or lower, the name was OPTIM_ARCHIVE_DESC)VARCHAR (128) OPTIM_START_DATETIME vary OPTIM_CURRENT_DATETIME vary OPTIM_CURRENT_DATETIME_CHAR VARCHAR(24) OPTIM_ACTION (returns “6” for this phase) INTEGER OPTIM_ACTION_TEXT (returns “BefDelFirstRow”) VARCHAR (20) OPTIM_TOTAL_PROCESS_STATUS INTEGER OPTIM_TOTAL_ROWS_PROCESSED INTEGER OPTIM_TOTAL_ROWS_IN_ERROR INTEGER OPTIM_TOTAL_ROWS_REMAINING_TO_BE_PROCESSED INTEGER OPTIM_GUID
(For Optim V11.3.0.7 or lower, the name was OPTIM_ARCHIVE_GUID)CHAR (38) OPTIM_SRCTBL_DBALIAS VARCHAR (12) OPTIM_SRCTBL_SCHEMA_NAME VARCHAR (64) OPTIM_SRCTBL_NAME VARCHAR (64) OPTIM_DSTTBL_DBALIAS VARCHAR (12) OPTIM_DSTTBL_SCHEMA_NAME VARCHAR (64) OPTIM_DSTTBL_NAME VARCHAR (64) OPTIM_TBL_ROWS_PROCESSED INTEGER OPTIM_TBL_ROWS_IN_ERROR INTEGER OPTIM_TBL_ROWS_REMAINING_TO _BE_PROCESSED INTEGER OPTIM_AFTER_ROW_STATUS INTEGER - Before Delete of Row
-
Available Built-in Variables Data Type OPTIM_ARCHIVE_ID INTEGER OPTIM_FILE_NAME
(For Optim V11.3.0.7 or lower, the name was OPTIM_ARCHIVE_FILE_NAME)VARCHAR (250) OPTIM_GROUP_NAME VARCHAR (8) OPTIM_USER_ID VARCHAR (30) OPTIM_SERVER_NAME VARCHAR (15) OPTIM_MACHINE_NAME VARCHAR (15) OPTIM_DESC
(For Optim V11.3.0.7 or lower, the name was OPTIM_ARCHIVE_DESC)VARCHAR (128) OPTIM_START_DATETIME vary OPTIM_CURRENT_DATETIME vary OPTIM_CURRENT_DATETIME_CHAR VARCHAR(24) OPTIM_ACTION (returns “7” for this phase) INTEGER OPTIM_ACTION_TEXT (returns “BefDelRow”) VARCHAR (20) OPTIM_TOTAL_PROCESS_STATUS INTEGER OPTIM_TOTAL_ROWS_PROCESSED INTEGER OPTIM_TOTAL_ROWS_IN_ERROR INTEGER OPTIM_TOTAL_ROWS_REMAINING_TO_BE_PROCESSED INTEGER OPTIM_GUID
(For Optim V11.3.0.7 or lower, the name was OPTIM_ARCHIVE_GUID)CHAR (38) OPTIM_SRCTBL_DBALIAS VARCHAR (12) OPTIM_SRCTBL_SCHEMA_NAME VARCHAR (64) OPTIM_SRCTBL_NAME VARCHAR (64) OPTIM_DSTTBL_DBALIAS VARCHAR (12) OPTIM_DSTTBL_SCHEMA_NAME VARCHAR (64) OPTIM_DSTTBL_NAME VARCHAR (64) OPTIM_TBL_ROWS_PROCESSED INTEGER OPTIM_TBL_ROWS_IN_ERROR INTEGER OPTIM_TBL_ROWS_REMAINING_TO _BE_PROCESSED INTEGER - After Delete of Row
-
Available Built-in Variables Data Type OPTIM_ARCHIVE_ID INTEGER OPTIM_FILE_NAME
(For Optim V11.3.0.7 or lower, the name was OPTIM_ARCHIVE_FILE_NAME)VARCHAR (250) OPTIM_GROUP_NAME VARCHAR (8) OPTIM_USER_ID VARCHAR (30) OPTIM_SERVER_NAME VARCHAR (15) OPTIM_MACHINE_NAME VARCHAR (15) OPTIM_DESC
(For Optim V11.3.0.7 or lower, the name was OPTIM_ARCHIVE_DESC)VARCHAR (128) OPTIM_START_DATETIME vary OPTIM_CURRENT_DATETIME vary OPTIM_CURRENT_DATETIME_CHAR VARCHAR(24) OPTIM_ACTION (returns “8” for this phase) INTEGER OPTIM_ACTION_TEXT (returns “AftDelRow”) VARCHAR (20) OPTIM_TOTAL_PROCESS_STATUS INTEGER OPTIM_TOTAL_ROWS_PROCESSED INTEGER OPTIM_TOTAL_ROWS_IN_ERROR INTEGER OPTIM_TOTAL_ROWS_REMAINING_TO_BE_PROCESSED INTEGER OPTIM_GUID
(For Optim V11.3.0.7 or lower, the name was OPTIM_ARCHIVE_GUID)CHAR (38) OPTIM_SRCTBL_DBALIAS VARCHAR (12) OPTIM_SRCTBL_SCHEMA_NAME VARCHAR (64) OPTIM_SRCTBL_NAME VARCHAR (64) OPTIM_DSTTBL_DBALIAS VARCHAR (12) OPTIM_DSTTBL_SCHEMA_NAME VARCHAR (64) OPTIM_DSTTBL_NAME VARCHAR (64) OPTIM_TBL_ROWS_PROCESSED INTEGER OPTIM_TBL_ROWS_IN_ERROR INTEGER OPTIM_TBL_ROWS_REMAINING_TO _BE_PROCESSED INTEGER OPTIM_AFTER_ROW_STATUS INTEGER - After Delete of Last Row from Table
-
Available Built-in Variables Data Type OPTIM_ARCHIVE_ID INTEGER OPTIM_FILE_NAME
(For Optim V11.3.0.7 or lower, the name was OPTIM_ARCHIVE_FILE_NAME)VARCHAR (250) OPTIM_GROUP_NAME VARCHAR (8) OPTIM_USER_ID VARCHAR (30) OPTIM_SERVER_NAME VARCHAR (15) OPTIM_MACHINE_NAME VARCHAR (15) OPTIM_DESC
(For Optim V11.3.0.7 or lower, the name was OPTIM_ARCHIVE_DESC)VARCHAR (128) OPTIM_START_DATETIME vary OPTIM_CURRENT_DATETIME vary OPTIM_CURRENT_DATETIME_CHAR VARCHAR(24) OPTIM_ACTION (returns “9” for this phase) INTEGER OPTIM_ACTION_TEXT (returns “AftDelLastRow”) VARCHAR (20) OPTIM_TOTAL_PROCESS_STATUS INTEGER OPTIM_TOTAL_ROWS_PROCESSED INTEGER OPTIM_TOTAL_ROWS_IN_ERROR INTEGER OPTIM_TOTAL_ROWS_REMAINING_TO_BE_PROCESSED INTEGER OPTIM_GUID
(For Optim V11.3.0.7 or lower, the name was OPTIM_ARCHIVE_GUID)CHAR (38) OPTIM_SRCTBL_DBALIAS VARCHAR (12) OPTIM_SRCTBL_SCHEMA_NAME VARCHAR (64) OPTIM_SRCTBL_NAME VARCHAR (64) OPTIM_DSTTBL_DBALIAS VARCHAR (12) OPTIM_DSTTBL_SCHEMA_NAME VARCHAR (64) OPTIM_DSTTBL_NAME VARCHAR (64) OPTIM_TBL_ROWS_PROCESSED INTEGER OPTIM_TBL_ROWS_IN_ERROR INTEGER OPTIM_TBL_ROWS_REMAINING_TO _BE_PROCESSED INTEGER OPTIM_AFTER_ROW_STATUS INTEGER - End of Delete Process
-
Available Built-in Variables Data Type OPTIM_ARCHIVE_ID INTEGER OPTIM_FILE_NAME
(For Optim V11.3.0.7 or lower, the name was OPTIM_ARCHIVE_FILE_NAME)VARCHAR (250) OPTIM_GROUP_NAME VARCHAR (8) OPTIM_USER_ID VARCHAR (30) OPTIM_SERVER_NAME VARCHAR (15) OPTIM_MACHINE_NAME VARCHAR (15) OPTIM_DESC
(For Optim V11.3.0.7 or lower, the name was OPTIM_ARCHIVE_DESC)VARCHAR (128) OPTIM_START_DATETIME vary OPTIM_CURRENT_DATETIME vary OPTIM_CURRENT_DATETIME_CHAR VARCHAR(24) OPTIM_ACTION (returns “10” for this phase) INTEGER OPTIM_ACTION_TEXT (returns “AftDelProcess”) VARCHAR (20) OPTIM_TOTAL_PROCESS_STATUS INTEGER OPTIM_TOTAL_ROWS_PROCESSED INTEGER OPTIM_TOTAL_ROWS_IN_ERROR INTEGER OPTIM_TOTAL_ROWS_REMAINING_TO_BE_PROCESSED INTEGER OPTIM_GUID
(For Optim V11.3.0.7 or lower, the name was OPTIM_ARCHIVE_GUID)CHAR (38) - Start of Restore Process
-
Available Built-in Variables Data Type OPTIM_ARCHIVE_ID INTEGER OPTIM_FILE_NAME
(For Optim V11.3.0.7 or lower, the name was OPTIM_ARCHIVE_FILE_NAME)VARCHAR (250) OPTIM_GROUP_NAME VARCHAR (8) OPTIM_USER_ID VARCHAR (30) OPTIM_SERVER_NAME VARCHAR (15) OPTIM_MACHINE_NAME VARCHAR (15) OPTIM_DESC
(For Optim V11.3.0.7 or lower, the name was OPTIM_ARCHIVE_DESC)VARCHAR (128) OPTIM_START_DATETIME vary OPTIM_CURRENT_DATETIME vary OPTIM_CURRENT_DATETIME_CHAR VARCHAR(24) OPTIM_ACTION (returns “11” for this phase) INTEGER OPTIM_ACTION_TEXT (returns “BefRestProcess”) VARCHAR (20) OPTIM_TOTAL_PROCESS_STATUS INTEGER OPTIM_TOTAL_ROWS_PROCESSED INTEGER OPTIM_TOTAL_ROWS_IN_ERROR INTEGER OPTIM_TOTAL_ROWS_REMAINING_TO_BE_PROCESSED INTEGER OPTIM_GUID
(For Optim V11.3.0.7 or lower, the name was OPTIM_ARCHIVE_GUID)CHAR (38) - Before Restore of First Row to Table
-
Available Built-in Variables Data Type OPTIM_ARCHIVE_ID INTEGER OPTIM_FILE_NAME
(For Optim V11.3.0.7 or lower, the name was OPTIM_ARCHIVE_FILE_NAME)VARCHAR (250) OPTIM_GROUP_NAME VARCHAR (8) OPTIM_USER_ID VARCHAR (30) OPTIM_SERVER_NAME VARCHAR (15) OPTIM_MACHINE_NAME VARCHAR (15) OPTIM_DESC
(For Optim V11.3.0.7 or lower, the name was OPTIM_ARCHIVE_DESC)VARCHAR (128) OPTIM_START_DATETIME vary OPTIM_CURRENT_DATETIME vary OPTIM_CURRENT_DATETIME_CHAR VARCHAR(24) OPTIM_ACTION (returns “12” for this phase) INTEGER OPTIM_ACTION_TEXT (returns “BefRestFirstRow”) VARCHAR (20) OPTIM_TOTAL_PROCESS_STATUS INTEGER OPTIM_TOTAL_ROWS_PROCESSED INTEGER OPTIM_TOTAL_ROWS_IN_ERROR INTEGER OPTIM_TOTAL_ROWS_REMAINING_TO_BE_PROCESSED INTEGER OPTIM_GUID
(For Optim V11.3.0.7 or lower, the name was OPTIM_ARCHIVE_GUID)CHAR (38) OPTIM_SRCTBL_DBALIAS VARCHAR (12) OPTIM_SRCTBL_SCHEMA_NAME VARCHAR (64) OPTIM_SRCTBL_NAME VARCHAR (64) OPTIM_DSTTBL_DBALIAS VARCHAR (12) OPTIM_DSTTBL_SCHEMA_NAME VARCHAR (64) OPTIM_DSTTBL_NAME VARCHAR (64) OPTIM_TBL_ROWS_PROCESSED INTEGER OPTIM_TBL_ROWS_IN_ERROR INTEGER OPTIM_TBL_ROWS_REMAINING_TO _BE_PROCESSED INTEGER OPTIM_AFTER_ROW_STATUS INTEGER - Before Restore of Row
-
Available Built-in Variables Data Type OPTIM_ARCHIVE_ID INTEGER OPTIM_FILE_NAME
(For Optim V11.3.0.7 or lower, the name was OPTIM_ARCHIVE_FILE_NAME)VARCHAR (250) OPTIM_GROUP_NAME VARCHAR (8) OPTIM_USER_ID VARCHAR (30) OPTIM_SERVER_NAME VARCHAR (15) OPTIM_MACHINE_NAME VARCHAR (15) OPTIM_DESC
(For Optim V11.3.0.7 or lower, the name was OPTIM_ARCHIVE_DESC)VARCHAR (128) OPTIM_START_DATETIME vary OPTIM_CURRENT_DATETIME vary OPTIM_CURRENT_DATETIME_CHAR VARCHAR(24) OPTIM_ACTION (returns “13” for this phase) INTEGER OPTIM_ACTION_TEXT (returns “BefRestRow”) VARCHAR (20) OPTIM_TOTAL_PROCESS_STATUS INTEGER OPTIM_TOTAL_ROWS_PROCESSED INTEGER OPTIM_TOTAL_ROWS_IN_ERROR INTEGER OPTIM_TOTAL_ROWS_REMAINING_TO_BE_PROCESSED INTEGER OPTIM_GUID
(For Optim V11.3.0.7 or lower, the name was OPTIM_ARCHIVE_GUID)CHAR (38) OPTIM_SRCTBL_DBALIAS VARCHAR (12) OPTIM_SRCTBL_SCHEMA_NAME VARCHAR (64) OPTIM_SRCTBL_NAME VARCHAR (64) OPTIM_DSTTBL_DBALIAS VARCHAR (12) OPTIM_DSTTBL_SCHEMA_NAME VARCHAR (64) OPTIM_DSTTBL_NAME VARCHAR (64) OPTIM_TBL_ROWS_PROCESSED INTEGER OPTIM_TBL_ROWS_IN_ERROR INTEGER OPTIM_TBL_ROWS_REMAINING_TO _BE_PROCESSED INTEGER OPTIM_AFTER_ROW_STATUS INTEGER - After Restore of Row
-
Available Built-in Variables Data Type OPTIM_ARCHIVE_ID INTEGER OPTIM_FILE_NAME
(For Optim V11.3.0.7 or lower, the name was OPTIM_ARCHIVE_FILE_NAME)VARCHAR (250) OPTIM_GROUP_NAME VARCHAR (8) OPTIM_USER_ID VARCHAR (30) OPTIM_SERVER_NAME VARCHAR (15) OPTIM_MACHINE_NAME VARCHAR (15) OPTIM_DESC
(For Optim V11.3.0.7 or lower, the name was OPTIM_ARCHIVE_DESC)VARCHAR (128) OPTIM_START_DATETIME vary OPTIM_CURRENT_DATETIME vary OPTIM_CURRENT_DATETIME_CHAR VARCHAR(24) OPTIM_ACTION (returns “14” for this phase) INTEGER OPTIM_ACTION_TEXT (returns “AftRestRow”) VARCHAR (20) OPTIM_TOTAL_PROCESS_STATUS INTEGER OPTIM_TOTAL_ROWS_PROCESSED INTEGER OPTIM_TOTAL_ROWS_IN_ERROR INTEGER OPTIM_TOTAL_ROWS_REMAINING_TO_BE_PROCESSED INTEGER OPTIM_GUID
(For Optim V11.3.0.7 or lower, the name was OPTIM_ARCHIVE_GUID)CHAR (38) OPTIM_SRCTBL_DBALIAS VARCHAR (12) OPTIM_SRCTBL_SCHEMA_NAME VARCHAR (64) OPTIM_SRCTBL_NAME VARCHAR (64) OPTIM_DSTTBL_DBALIAS VARCHAR (12) OPTIM_DSTTBL_SCHEMA_NAME VARCHAR (64) OPTIM_DSTTBL_NAME VARCHAR (64) OPTIM_TBL_ROWS_PROCESSED INTEGER OPTIM_TBL_ROWS_IN_ERROR INTEGER OPTIM_TBL_ROWS_REMAINING_TO_BE_PROCESSED INTEGER OPTIM_AFTER_ROW_STATUS INTEGER - After Restore of Last Row to Table
-
Available Built-in Variables Data Type OPTIM_ARCHIVE_ID INTEGER OPTIM_FILE_NAME
(For Optim V11.3.0.7 or lower, the name was OPTIM_ARCHIVE_FILE_NAME)VARCHAR (250) OPTIM_GROUP_NAME VARCHAR (8) OPTIM_USER_ID VARCHAR (30) OPTIM_SERVER_NAME VARCHAR (15) OPTIM_MACHINE_NAME VARCHAR (15) OPTIM_DESC
(For Optim V11.3.0.7 or lower, the name was OPTIM_ARCHIVE_DESC)VARCHAR (128) OPTIM_START_DATETIME vary OPTIM_CURRENT_DATETIME vary OPTIM_CURRENT_DATETIME_CHAR VARCHAR(24) OPTIM_ACTION (returns “15” for this phase) INTEGER OPTIM_ACTION_TEXT (returns “AftRestLastRow”) VARCHAR (20) OPTIM_TOTAL_PROCESS_STATUS INTEGER OPTIM_TOTAL_ROWS_PROCESSED INTEGER OPTIM_TOTAL_ROWS_IN_ERROR INTEGER OPTIM_TOTAL_ROWS_REMAINING_TO_BE_PROCESSED INTEGER OPTIM_GUID
(For Optim V11.3.0.7 or lower, the name was OPTIM_ARCHIVE_GUID)CHAR (38) OPTIM_SRCTBL_DBALIAS VARCHAR (12) OPTIM_SRCTBL_SCHEMA_NAME VARCHAR (64) OPTIM_SRCTBL_NAME VARCHAR (64) OPTIM_DSTTBL_DBALIAS VARCHAR (12) OPTIM_DSTTBL_SCHEMA_NAME VARCHAR (64) OPTIM_DSTTBL_NAME VARCHAR (64) OPTIM_TBL_ROWS_PROCESSED INTEGER OPTIM_TBL_ROWS_IN_ERROR INTEGER OPTIM_TBL_ROWS_REMAINING_TO _BE_PROCESSED INTEGER OPTIM_AFTER_ROW_STATUS INTEGER - End of Restore Process
-
Available Built-in Variables Data Type OPTIM_ARCHIVE_ID INTEGER OPTIM_FILE_NAME
(For Optim V11.3.0.7 or lower, the name was OPTIM_ARCHIVE_FILE_NAME)VARCHAR (250) OPTIM_GROUP_NAME VARCHAR (8) OPTIM_USER_ID VARCHAR (30) OPTIM_SERVER_NAME VARCHAR (15) OPTIM_MACHINE_NAME VARCHAR (15) OPTIM_DESC
(For Optim V11.3.0.7 or lower, the name was OPTIM_ARCHIVE_DESC)VARCHAR (128) OPTIM_START_DATETIME vary OPTIM_CURRENT_DATETIME vary OPTIM_CURRENT_DATETIME_CHAR VARCHAR(24) OPTIM_ACTION (returns “16” for this phase) INTEGER OPTIM_ACTION_TEXT (returns “AftRestProcess”) VARCHAR (20) OPTIM_TOTAL_PROCESS_STATUS INTEGER OPTIM_TOTAL_ROWS_PROCESSED INTEGER OPTIM_TOTAL_ROWS_IN_ERROR INTEGER OPTIM_TOTAL_ROWS_REMAINING_TO_BE_PROCESSED INTEGER OPTIM_GUID
(For Optim V11.3.0.7 or lower, the name was OPTIM_ARCHIVE_GUID)CHAR (38)
Extract Process Status
Extract Action Phases enable a built-in variable labeled OPTIM_PROCESS_STATUS. Here are the possible numerical values that are returned.
| Value | Explanation |
|---|---|
| 0 | Success. |
| 1 | The process was canceled. |
| 2 | A DBMS error occurred that aborted Archive. |
| 3 | An Action aborted Archive. |
| 4 | An error occurred that aborted Archive. |
After Row Status
Several Action Phases enable a built-in variable labeled OPTIM_AFTER_ROW_STATUS. Here are the possible numerical values that are returned.
| Value | Explanation |
|---|---|
| 0 | Status unavailable. |
| 1 | Inserted successfully. |
| 2 | Updated successfully. |
| 3 | Deleted successfully. |
| 4 | Row (primary key) not found for deletion. |
| 5 | Row (primary key) found, but non-primary key columns do not match (therefore, row not deleted). |
| 6 | Row was not found for updating. |
| 7 | Duplicate index. |
| 8 | Constraint restriction. |
| 9 | View check error (when you define a view with a WITH CHECK clause, any inserted or updated row must conform to the specified condition). |
| 10 | The before row action indicated that the row should be skipped. |
| 99 | All other status conditions. |
Total Process Status
Several Action Phases enable a built-in variable labeled OPTIM_TOTAL_PROCESS_STATUS. Here are the possible numerical values that are returned.
| Value | Explanation |
|---|---|
| 0 | All rows were successfully processed. |
| 1 | Some rows with errors. |
| 2 | Unrecoverable DBMS error. |
| 3 | Action aborted. |
| 4 | Unrecoverable error. |