Use the Archive Actions tab to create supplemental SQL statements (Actions) to be executed at selected phases of an Archive, Delete, 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.
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 Action Phase box lists phases in chronological order, from Start of the Extract Process through End of Restore Process.
Archive Actions are in two classes:
Direct references to a DB Alias are invalid in an SQL statement. Thus, Optim generates supplementary SQL 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.
Character required to identify a column value or built-in variable in SQL statements for Archive Actions. To change the delimiter, click the arrow and select from the list. The AA delimiter must differ from the AD delimiter.
Character required to identify an Access Definition variable (defined on the Variables tab in the Access Definition) in an SQL statement for the Archive Action.
To change the delimiter, click the arrow and select from the list. The AD delimiter must differ from the AA delimiter.
The SQL Statement option enables the text box for the selected Action Phase. Type the desired SQL statement (Insert, Update, Delete, or Stored Procedure Call) in the box. You can select a column value, built‑in variable, or Access Definition variable from the list boxes. (The selected variable and delimiter are inserted at the cursor location.)
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.
To validate the syntax of an INSERT, UPDATE, or DELETE statement, right-click the text box and select Validate SQL from the shortcut menu. To remove an SQL statement, right-click the text box and select Remove SQL. You may also overtype with blanks or use the Delete or Backspace key.
You can use a raise exception statement in a stored procedure called by a 'Before' Archive Action statement to issue a message that includes the text PST_STATE=SKIP_ROW (anywhere in the message). If that text is in a message, Archive returns the value 10 for the Built-In Variable PST_AFTER_ROW_STATUS and the row is skipped.
Similarly, a stored procedure called by any Archive Action (Before, After, Start, or End), can issue a message that includes the text PST_STATE=ABORT (anywhere in the message). If that text is in a message, the process terminates.
See documentation for your DBMS to determine the proper 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, ‘PST_STATE=SKIP_ROW'), END YourStoredProcedure
Use the Same SQL Statement as Action Phase option to share an SQL statement specified for an 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 associated with the SQL statement you want to apply.
If an error occurs when an SQL statement is executed, the Error Option you select determines how processing continues:
Names of columns that can be used in SQL statements associated with certain local Action Phases. When you select a column value (i.e., column name) for an action, the column name is inserted into the SQL statement as a variable, and the column value is used each time the statement is executed. Select a column name to insert it at the cursor position.
Supported built-in functions that can be used in SQL statements to return information about an Action Phase. Select a built-in variable name to insert it at the cursor position.
Lists any Access Definition variables defined on the Variables tab of the Access Definition Editorfor the specified table. Select an Access Definition variable to insert the name at the cursor position in the SQL Statement box.
The following table lists the available Action Phases, the built-in functions associated with each, and the corresponding data type:
DBMS | Data Type |
---|---|
UDB | TIMESTAMP |
Oracle | DATE |
Sybase ASE | DATETIME |
SQL Server | DATETIME |
Informix® | DATETIME |
Available Built-in Variables | Data Type |
---|---|
PST_ARCHIVE_ID | INTEGER |
PST_ARCHIVE_FILE_NAME | VARCHAR (250) |
PST_ACTION (returns “0” for this phase) | INTEGER |
PST_ACTION_TEXT (returns “BefExtProcess”) | VARCHAR (20) |
PST_INDEX_FILE_NAME | VARCHAR (250) |
PST_GROUP_NAME | VARCHAR (8) |
PST_USER_ID | VARCHAR (30) |
PST_SERVER_NAME | VARCHAR (15) |
PST_MACHINE_NAME | VARCHAR (15) |
PST_ARCHIVE_DESC | VARCHAR (128) |
PST_START_DATETIME | vary |
PST_CURRENT_DATETIME | vary |
PST_CURRENT_DATETIME_CHAR | VARCHAR(24) |
PST_TOTAL_ROWS_EXTRACTED | INTEGER |
PST_ARCHIVE_GUID | CHAR (38) |
PST_PROCESS_STATUS | INTEGER |
Available Built-in Variables | Data Type |
---|---|
PST_ARCHIVE_ID | INTEGER |
PST_ARCHIVE_FILE_NAME | VARCHAR (250) |
PST_ACTION (returns “1” for this phase) | INTEGER |
PST_ACTION_TEXT (returns “BefExtFirstRow”) | VARCHAR (20) |
PST_INDEX_FILE_NAME | VARCHAR (250) |
PST_GROUP_NAME | VARCHAR (8) |
PST_USER_ID | VARCHAR (30) |
PST_SERVER_NAME | VARCHAR (15) |
PST_MACHINE_NAME | VARCHAR (15) |
PST_ARCHIVE_DESC | VARCHAR (128) |
PST_START_DATETIME | vary |
PST_CURRENT_DATETIME | vary |
PST_CURRENT_DATETIME_CHAR | VARCHAR(24) |
PST_TOTAL_ROWS_EXTRACTED | INTEGER |
PST_ARCHIVE_GUID | CHAR(38) |
PST_PROCESS_STATUS | INTEGER |
PST_TBL_DBALIAS | VARCHAR (12) |
PST_TBL_SCHEMA_NAME | VARCHAR (64) |
PST_TBL_NAME | VARCHAR (64) |
PST_TOTAL_ROWS_EXTRACTED | INTEGER |
Available Built-in Variables | Data Type |
---|---|
PST_ARCHIVE_ID | INTEGER |
PST_ARCHIVE_FILE_NAME | VARCHAR (250) |
PST_ACTION (returns “2” for this phase) | INTEGER |
PST_ACTION_TEXT (returns “BefExtRow”) | VARCHAR (20) |
PST_INDEX_FILE_NAME | VARCHAR (250) |
PST_GROUP_NAME | VARCHAR (8) |
PST_USER_ID | VARCHAR (30) |
PST_SERVER_NAME | VARCHAR (15) |
PST_MACHINE_NAME | VARCHAR (15) |
PST_ARCHIVE_DESC | VARCHAR (128) |
PST_START_DATETIME | vary |
PST_CURRENT_DATETIME | vary |
PST_CURRENT_DATETIME_CHAR | VARCHAR(24) |
PST_TOTAL_ROWS_EXTRACTED | INTEGER |
PST_ARCHIVE_GUID | CHAR (38) |
PST_PROCESS_STATUS | INTEGER |
PST_TBL_DBALIAS | VARCHAR (12) |
PST_TBL_SCHEMA_NAME | VARCHAR (64) |
PST_TBL_NAME | VARCHAR (64) |
PST_TBL_ROWS_EXTRACTED | INTEGER |
Available Built-in Variables | Data Type |
---|---|
PST_ARCHIVE_ID | INTEGER |
PST_ARCHIVE_FILE_NAME | VARCHAR (250) |
PST_ACTION (returns “3” for this phase) | INTEGER |
PST_ACTION_TEXT (returns “AftExtLast Row”) | VARCHAR (20) |
PST_INDEX_FILE_NAME | VARCHAR (250) |
PST_GROUP_NAME | VARCHAR (8) |
PST_USER_ID | VARCHAR (30) |
PST_SERVER_NAME | VARCHAR (15) |
PST_MACHINE_NAME | VARCHAR (15) |
PST_ARCHIVE_DESC | VARCHAR (128) |
PST_START_DATETIME | vary |
PST_CURRENT_DATETIME | vary |
PST_CURRENT_DATETIME_CHAR | VARCHAR(24) |
PST_TOTAL_ROWS_EXTRACTED | INTEGER |
PST_ARCHIVE_GUID | CHAR (38) |
PST_PROCESS_STATUS | INTEGER |
PST_TBL_DBALIAS | VARCHAR (12) |
PST_TBL_SCHEMA_NAME | VARCHAR (64) |
PST_TBL_NAME | VARCHAR (64) |
PST_TBL_ROWS_EXTRACTED | INTEGER |
Available Built-in Variables | Data Type |
---|---|
PST_ARCHIVE_ID | INTEGER |
PST_ARCHIVE_FILE_NAME | VARCHAR (250) |
PST_ACTION (returns “4” for this phase) | INTEGER |
PST_ACTION_TEXT (returns “AftExtProcess”) | VARCHAR (20) |
PST_INDEX_FILE_NAME | VARCHAR (250) |
PST_GROUP_NAME | VARCHAR (8) |
PST_USER_ID | VARCHAR (30) |
PST_SERVER_NAME | VARCHAR (15) |
PST_MACHINE_NAME | VARCHAR (15) |
PST_ARCHIVE_DESC | VARCHAR (128) |
PST_START_DATETIME | vary |
PST_CURRENT_DATETIME | vary |
PST_CURRENT_DATETIME_CHAR | VARCHAR(24) |
PST_TOTAL_ROWS_EXTRACTED | INTEGER |
PST_ARCHIVE_GUID | CHAR (38) |
PST_PROCESS_STATUS | INTEGER |
Available Built-in Variables | Data Type |
---|---|
PST_ARCHIVE_ID | INTEGER |
PST_ARCHIVE_FILE_NAME | VARCHAR (250) |
PST_GROUP_NAME | VARCHAR (8) |
PST_USER_ID | VARCHAR (30) |
PST_SERVER_NAME | VARCHAR (15) |
PST_MACHINE_NAME | VARCHAR (15) |
PST_ARCHIVE_DESC | VARCHAR (128) |
PST_START_DATETIME | vary |
PST_CURRENT_DATETIME | vary |
PST_CURRENT_DATETIME_CHAR | VARCHAR(24) |
PST_ACTION (returns “5” for this phase) | INTEGER |
PST_ACTION_TEXT (returns “BefDelProcess”) | VARCHAR (20) |
PST_TOTAL_PROCESS_STATUS | INTEGER |
PST_TOTAL_ROWS_PROCESSED | INTEGER |
PST_TOTAL_ROWS_IN_ERROR | INTEGER |
PST_TOTAL_ROWS_REMAINING_TO_BE_PROCESSED | INTEGER |
PST_ARCHIVE_GUID | CHAR (38) |
Available Built-in Variables | Data Type |
---|---|
PST_ARCHIVE_ID | INTEGER |
PST_ARCHIVE_FILE_NAME | VARCHAR (250) |
PST_GROUP_NAME | VARCHAR (8) |
PST_USER_ID | VARCHAR (30) |
PST_SERVER_NAME | VARCHAR (15) |
PST_MACHINE_NAME | VARCHAR (15) |
PST_ARCHIVE_DESC | VARCHAR (128) |
PST_START_DATETIME | vary |
PST_CURRENT_DATETIME | vary |
PST_CURRENT_DATETIME_CHAR | VARCHAR(24) |
PST_ACTION (returns “6” for this phase) | INTEGER |
PST_ACTION_TEXT (returns “BefDelFirstRow”) | VARCHAR (20) |
PST_TOTAL_PROCESS_STATUS | INTEGER |
PST_TOTAL_ROWS_PROCESSED | INTEGER |
PST_TOTAL_ROWS_IN_ERROR | INTEGER |
PST_TOTAL_ROWS_REMAINING_TO_BE_PROCESSED | INTEGER |
PST_ARCHIVE_GUID | CHAR (38) |
PST_SRCTBL_DBALIAS | VARCHAR (12) |
PST_SRCTBL_SCHEMA_NAME | VARCHAR (64) |
PST_SRCTBL_NAME | VARCHAR (64) |
PST_DSTTBL_DBALIAS | VARCHAR (12) |
PST_DSTTBL_SCHEMA_NAME | VARCHAR (64) |
PST_DSTTBL_NAME | VARCHAR (64) |
PST_TBL_ROWS_PROCESSED | INTEGER |
PST_TBL_ROWS_IN_ERROR | INTEGER |
PST_TBL_ROWS_REMAINING_TO _BE_PROCESSED | INTEGER |
PST_AFTER_ROW_STATUS | INTEGER |
Available Built-in Variables | Data Type |
---|---|
PST_ARCHIVE_ID | INTEGER |
PST_ARCHIVE_FILE_NAME | VARCHAR (250) |
PST_GROUP_NAME | VARCHAR (8) |
PST_USER_ID | VARCHAR (30) |
PST_SERVER_NAME | VARCHAR (15) |
PST_MACHINE_NAME | VARCHAR (15) |
PST_ARCHIVE_DESC | VARCHAR (128) |
PST_START_DATETIME | vary |
PST_CURRENT_DATETIME | vary |
PST_CURRENT_DATETIME_CHAR | VARCHAR(24) |
PST_ACTION (returns “7” for this phase) | INTEGER |
PST_ACTION_TEXT (returns “BefDelRow”) | VARCHAR (20) |
PST_TOTAL_PROCESS_STATUS | INTEGER |
PST_TOTAL_ROWS_PROCESSED | INTEGER |
PST_TOTAL_ROWS_IN_ERROR | INTEGER |
PST_TOTAL_ROWS_REMAINING_TO_BE_PROCESSED | INTEGER |
PST_ARCHIVE_GUID | CHAR (38) |
PST_SRCTBL_DBALIAS | VARCHAR (12) |
PST_SRCTBL_SCHEMA_NAME | VARCHAR (64) |
PST_SRCTBL_NAME | VARCHAR (64) |
PST_DSTTBL_DBALIAS | VARCHAR (12) |
PST_DSTTBL_SCHEMA_NAME | VARCHAR (64) |
PST_DSTTBL_NAME | VARCHAR (64) |
PST_TBL_ROWS_PROCESSED | INTEGER |
PST_TBL_ROWS_IN_ERROR | INTEGER |
PST_TBL_ROWS_REMAINING_TO _BE_PROCESSED | INTEGER |
Available Built-in Variables | Data Type |
---|---|
PST_ARCHIVE_ID | INTEGER |
PST_ARCHIVE_FILE_NAME | VARCHAR (250) |
PST_GROUP_NAME | VARCHAR (8) |
PST_USER_ID | VARCHAR (30) |
PST_SERVER_NAME | VARCHAR (15) |
PST_MACHINE_NAME | VARCHAR (15) |
PST_ARCHIVE_DESC | VARCHAR (128) |
PST_START_DATETIME | vary |
PST_CURRENT_DATETIME | vary |
PST_CURRENT_DATETIME_CHAR | VARCHAR(24) |
PST_ACTION (returns “8” for this phase) | INTEGER |
PST_ACTION_TEXT (returns “AftDelRow”) | VARCHAR (20) |
PST_TOTAL_PROCESS_STATUS | INTEGER |
PST_TOTAL_ROWS_PROCESSED | INTEGER |
PST_TOTAL_ROWS_IN_ERROR | INTEGER |
PST_TOTAL_ROWS_REMAINING_TO_BE_PROCESSED | INTEGER |
PST_ARCHIVE_GUID | CHAR (38) |
PST_SRCTBL_DBALIAS | VARCHAR (12) |
PST_SRCTBL_SCHEMA_NAME | VARCHAR (64) |
PST_SRCTBL_NAME | VARCHAR (64) |
PST_DSTTBL_DBALIAS | VARCHAR (12) |
PST_DSTTBL_SCHEMA_NAME | VARCHAR (64) |
PST_DSTTBL_NAME | VARCHAR (64) |
PST_TBL_ROWS_PROCESSED | INTEGER |
PST_TBL_ROWS_IN_ERROR | INTEGER |
PST_TBL_ROWS_REMAINING_TO _BE_PROCESSED | INTEGER |
PST_AFTER_ROW_STATUS | INTEGER |
Available Built-in Variables | Data Type |
---|---|
PST_ARCHIVE_ID | INTEGER |
PST_ARCHIVE_FILE_NAME | VARCHAR (250) |
PST_GROUP_NAME | VARCHAR (8) |
PST_USER_ID | VARCHAR (30) |
PST_SERVER_NAME | VARCHAR (15) |
PST_MACHINE_NAME | VARCHAR (15) |
PST_ARCHIVE_DESC | VARCHAR (128) |
PST_START_DATETIME | vary |
PST_CURRENT_DATETIME | vary |
PST_CURRENT_DATETIME_CHAR | VARCHAR(24) |
PST_ACTION (returns “9” for this phase) | INTEGER |
PST_ACTION_TEXT (returns “AftDelLastRow”) | VARCHAR (20) |
PST_TOTAL_PROCESS_STATUS | INTEGER |
PST_TOTAL_ROWS_PROCESSED | INTEGER |
PST_TOTAL_ROWS_IN_ERROR | INTEGER |
PST_TOTAL_ROWS_REMAINING_TO_BE_PROCESSED | INTEGER |
PST_ARCHIVE_GUID | CHAR (38) |
PST_SRCTBL_DBALIAS | VARCHAR (12) |
PST_SRCTBL_SCHEMA_NAME | VARCHAR (64) |
PST_SRCTBL_NAME | VARCHAR (64) |
PST_DSTTBL_DBALIAS | VARCHAR (12) |
PST_DSTTBL_SCHEMA_NAME | VARCHAR (64) |
PST_DSTTBL_NAME | VARCHAR (64) |
PST_TBL_ROWS_PROCESSED | INTEGER |
PST_TBL_ROWS_IN_ERROR | INTEGER |
PST_TBL_ROWS_REMAINING_TO _BE_PROCESSED | INTEGER |
PST_AFTER_ROW_STATUS | INTEGER |
Available Built-in Variables | Data Type |
---|---|
PST_ARCHIVE_ID | INTEGER |
PST_ARCHIVE_FILE_NAME | VARCHAR (250) |
PST_GROUP_NAME | VARCHAR (8) |
PST_USER_ID | VARCHAR (30) |
PST_SERVER_NAME | VARCHAR (15) |
PST_MACHINE_NAME | VARCHAR (15) |
PST_ARCHIVE_DESC | VARCHAR (128) |
PST_START_DATETIME | vary |
PST_CURRENT_DATETIME | vary |
PST_CURRENT_DATETIME_CHAR | VARCHAR(24) |
PST_ACTION (returns “10” for this phase) | INTEGER |
PST_ACTION_TEXT (returns “AftDelProcess”) | VARCHAR (20) |
PST_TOTAL_PROCESS_STATUS | INTEGER |
PST_TOTAL_ROWS_PROCESSED | INTEGER |
PST_TOTAL_ROWS_IN_ERROR | INTEGER |
PST_TOTAL_ROWS_REMAINING_TO_BE_PROCESSED | INTEGER |
PST_ARCHIVE_GUID | CHAR (38) |
Available Built-in Variables | Data Type |
---|---|
PST_ARCHIVE_ID | INTEGER |
PST_ARCHIVE_FILE_NAME | VARCHAR (250) |
PST_GROUP_NAME | VARCHAR (8) |
PST_USER_ID | VARCHAR (30) |
PST_SERVER_NAME | VARCHAR (15) |
PST_MACHINE_NAME | VARCHAR (15) |
PST_ARCHIVE_DESC | VARCHAR (128) |
PST_START_DATETIME | vary |
PST_CURRENT_DATETIME | vary |
PST_CURRENT_DATETIME_CHAR | VARCHAR(24) |
PST_ACTION (returns “11” for this phase) | INTEGER |
PST_ACTION_TEXT (returns “BefRestProcess”) | VARCHAR (20) |
PST_TOTAL_PROCESS_STATUS | INTEGER |
PST_TOTAL_ROWS_PROCESSED | INTEGER |
PST_TOTAL_ROWS_IN_ERROR | INTEGER |
PST_TOTAL_ROWS_REMAINING_TO_BE_PROCESSED | INTEGER |
PST_ARCHIVE_GUID | CHAR (38) |
Available Built-in Variables | Data Type |
---|---|
PST_ARCHIVE_ID | INTEGER |
PST_ARCHIVE_FILE_NAME | VARCHAR (250) |
PST_GROUP_NAME | VARCHAR (8) |
PST_USER_ID | VARCHAR (30) |
PST_SERVER_NAME | VARCHAR (15) |
PST_MACHINE_NAME | VARCHAR (15) |
PST_ARCHIVE_DESC | VARCHAR (128) |
PST_START_DATETIME | vary |
PST_CURRENT_DATETIME | vary |
PST_CURRENT_DATETIME_CHAR | VARCHAR(24) |
PST_ACTION (returns “12” for this phase) | INTEGER |
PST_ACTION_TEXT (returns “BefRestFirstRow”) | VARCHAR (20) |
PST_TOTAL_PROCESS_STATUS | INTEGER |
PST_TOTAL_ROWS_PROCESSED | INTEGER |
PST_TOTAL_ROWS_IN_ERROR | INTEGER |
PST_TOTAL_ROWS_REMAINING_TO_BE_PROCESSED | INTEGER |
PST_ARCHIVE_GUID | CHAR (38) |
PST_SRCTBL_DBALIAS | VARCHAR (12) |
PST_SRCTBL_SCHEMA_NAME | VARCHAR (64) |
PST_SRCTBL_NAME | VARCHAR (64) |
PST_DSTTBL_DBALIAS | VARCHAR (12) |
PST_DSTTBL_SCHEMA_NAME | VARCHAR (64) |
PST_DSTTBL_NAME | VARCHAR (64) |
PST_TBL_ROWS_PROCESSED | INTEGER |
PST_TBL_ROWS_IN_ERROR | INTEGER |
PST_TBL_ROWS_REMAINING_TO _BE_PROCESSED | INTEGER |
PST_AFTER_ROW_STATUS | INTEGER |
Available Built-in Variables | Data Type |
---|---|
PST_ARCHIVE_ID | INTEGER |
PST_ARCHIVE_FILE_NAME | VARCHAR (250) |
PST_GROUP_NAME | VARCHAR (8) |
PST_USER_ID | VARCHAR (30) |
PST_SERVER_NAME | VARCHAR (15) |
PST_MACHINE_NAME | VARCHAR (15) |
PST_ARCHIVE_DESC | VARCHAR (128) |
PST_START_DATETIME | vary |
PST_CURRENT_DATETIME | vary |
PST_CURRENT_DATETIME_CHAR | VARCHAR(24) |
PST_ACTION (returns “13” for this phase) | INTEGER |
PST_ACTION_TEXT (returns “BefRestRow”) | VARCHAR (20) |
PST_TOTAL_PROCESS_STATUS | INTEGER |
PST_TOTAL_ROWS_PROCESSED | INTEGER |
PST_TOTAL_ROWS_IN_ERROR | INTEGER |
PST_TOTAL_ROWS_REMAINING_TO_BE_PROCESSED | INTEGER |
PST_ARCHIVE_GUID | CHAR (38) |
PST_SRCTBL_DBALIAS | VARCHAR (12) |
PST_SRCTBL_SCHEMA_NAME | VARCHAR (64) |
PST_SRCTBL_NAME | VARCHAR (64) |
PST_DSTTBL_DBALIAS | VARCHAR (12) |
PST_DSTTBL_SCHEMA_NAME | VARCHAR (64) |
PST_DSTTBL_NAME | VARCHAR (64) |
PST_TBL_ROWS_PROCESSED | INTEGER |
PST_TBL_ROWS_IN_ERROR | INTEGER |
PST_TBL_ROWS_REMAINING_TO _BE_PROCESSED | INTEGER |
PST_AFTER_ROW_STATUS | INTEGER |
Available Built-in Variables | Data Type |
---|---|
PST_ARCHIVE_ID | INTEGER |
PST_ARCHIVE_FILE_NAME | VARCHAR (250) |
PST_GROUP_NAME | VARCHAR (8) |
PST_USER_ID | VARCHAR (30) |
PST_SERVER_NAME | VARCHAR (15) |
PST_MACHINE_NAME | VARCHAR (15) |
PST_ARCHIVE_DESC | VARCHAR (128) |
PST_START_DATETIME | vary |
PST_CURRENT_DATETIME | vary |
PST_CURRENT_DATETIME_CHAR | VARCHAR(24) |
PST_ACTION (returns “14” for this phase) | INTEGER |
PST_ACTION_TEXT (returns “AftRestRow”) | VARCHAR (20) |
PST_TOTAL_PROCESS_STATUS | INTEGER |
PST_TOTAL_ROWS_PROCESSED | INTEGER |
PST_TOTAL_ROWS_IN_ERROR | INTEGER |
PST_TOTAL_ROWS_REMAINING_TO_BE_PROCESSED | INTEGER |
PST_ARCHIVE_GUID | CHAR (38) |
PST_SRCTBL_DBALIAS | VARCHAR (12) |
PST_SRCTBL_SCHEMA_NAME | VARCHAR (64) |
PST_SRCTBL_NAME | VARCHAR (64) |
PST_DSTTBL_DBALIAS | VARCHAR (12) |
PST_DSTTBL_SCHEMA_NAME | VARCHAR (64) |
PST_DSTTBL_NAME | VARCHAR (64) |
PST_TBL_ROWS_PROCESSED | INTEGER |
PST_TBL_ROWS_IN_ERROR | INTEGER |
PST_TBL_ROWS_REMAINING_TO_BE_PROCESSED | INTEGER |
PST_AFTER_ROW_STATUS | INTEGER |
Available Built-in Variables | Data Type |
---|---|
PST_ARCHIVE_ID | INTEGER |
PST_ARCHIVE_FILE_NAME | VARCHAR (250) |
PST_GROUP_NAME | VARCHAR (8) |
PST_USER_ID | VARCHAR (30) |
PST_SERVER_NAME | VARCHAR (15) |
PST_MACHINE_NAME | VARCHAR (15) |
PST_ARCHIVE_DESC | VARCHAR (128) |
PST_START_DATETIME | vary |
PST_CURRENT_DATETIME | vary |
PST_CURRENT_DATETIME_CHAR | VARCHAR(24) |
PST_ACTION (returns “15” for this phase) | INTEGER |
PST_ACTION_TEXT (returns “AftRestLastRow”) | VARCHAR (20) |
PST_TOTAL_PROCESS_STATUS | INTEGER |
PST_TOTAL_ROWS_PROCESSED | INTEGER |
PST_TOTAL_ROWS_IN_ERROR | INTEGER |
PST_TOTAL_ROWS_REMAINING_TO_BE_PROCESSED | INTEGER |
PST_ARCHIVE_GUID | CHAR (38) |
PST_SRCTBL_DBALIAS | VARCHAR (12) |
PST_SRCTBL_SCHEMA_NAME | VARCHAR (64) |
PST_SRCTBL_NAME | VARCHAR (64) |
PST_DSTTBL_DBALIAS | VARCHAR (12) |
PST_DSTTBL_SCHEMA_NAME | VARCHAR (64) |
PST_DSTTBL_NAME | VARCHAR (64) |
PST_TBL_ROWS_PROCESSED | INTEGER |
PST_TBL_ROWS_IN_ERROR | INTEGER |
PST_TBL_ROWS_REMAINING_TO _BE_PROCESSED | INTEGER |
PST_AFTER_ROW_STATUS | INTEGER |
Available Built-in Variables | Data Type |
---|---|
PST_ARCHIVE_ID | INTEGER |
PST_ARCHIVE_FILE_NAME | VARCHAR (250) |
PST_GROUP_NAME | VARCHAR (8) |
PST_USER_ID | VARCHAR (30) |
PST_SERVER_NAME | VARCHAR (15) |
PST_MACHINE_NAME | VARCHAR (15) |
PST_ARCHIVE_DESC | VARCHAR (128) |
PST_START_DATETIME | vary |
PST_CURRENT_DATETIME | vary |
PST_CURRENT_DATETIME_CHAR | VARCHAR(24) |
PST_ACTION (returns “16” for this phase) | INTEGER |
PST_ACTION_TEXT (returns “AftRestProcess”) | VARCHAR (20) |
PST_TOTAL_PROCESS_STATUS | INTEGER |
PST_TOTAL_ROWS_PROCESSED | INTEGER |
PST_TOTAL_ROWS_IN_ERROR | INTEGER |
PST_TOTAL_ROWS_REMAINING_TO_BE_PROCESSED | INTEGER |
PST_ARCHIVE_GUID | CHAR (38) |
Extract Action Phases enable a built-in variable labeled PST_PROCESS_STATUS. Possible numerical values returned are:
Value | Explanation |
---|---|
0 | Success. |
1 | The process was cancelled. |
2 | A DBMS error occurred that aborted Archive. |
3 | An Archive Action aborted Archive. |
4 | An error occurred that aborted Archive. |
Several Action Phases enable a built-in variable labeled PST_AFTER_ROW_STATUS. Possible numerical values returned are:
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 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 the row should be skipped. |
99 | All other status conditions. |