Archive provides a facility that allows you to define actions to be executed at one or more predefined points (action events) during an Archive, Delete, or Restore Process. Each action event can have only one SQL statement associated with it, although action events can share an SQL statement association.
Action SQL statements are different from and should not be considered interchangeable with SQL WHERE clauses created to select data for processing. (See SQL WHERE Clause Specifications for information about this feature.)
To define an Archive Action, invoke the Select an Action to Be Defined panel from the Select Tables/Views for AD panel using the ACTIONS primary command with the desired table name as an operand. You can also enter the ACTIONS primary command and position the cursor on the desired table name in the Table List before pressing ENTER.
Alternatively, use the ACT line command to display the scrollable Select an Action to Be Defined panel.
Figure: Select an Action To Be Defined
-- Select Tables/Views for AD: GRP.FOPDEMO.AD ---------------------------------
Command ===> Scroll ===> PAGE
Pr +--------- Select an Action To Be Defined for FOPDEMO.CUSTOMERS -----+ UBS
¦ ¦
¦ 1 of 12 ¦
Def ¦ Cmd Action Description Active Shr ¦ E>>
Sta ¦ --- ------------------------------------------ ------ --- ¦ >>
Sta ¦ *** ************************ TOP ************************* ¦ >>
¦ ___ Before Extract of the First Row From a Table N N ¦
Cmd ¦ ___ Before Extract of a Row From a Table N N ¦
--- ¦ ___ After Extract of a Row From a Table N N ¦
*** ¦ ___ After Extract of Last Row From a Table N N ¦
ACT ¦ ___ Before Delete of the First Row From a Table N N ¦
___ ¦ ___ Before Delete of a Row From a Table N N ¦
___ ¦ ___ After Delete of a Row From a Table N N ¦
___ ¦ ___ After Delete of the Last Row From a Table N N ¦
*** ¦ ___ Before Restore of the First Row To a Table N N ¦
¦ ¦
¦ Line Commands: (S)elect, (INF)ormation, (CLR)Clear ¦
¦ ¦
+--------------------------------------------------------------------+
The Select an Action To Be Defined panel includes:
When an action statement is shared by action events, an asterisk after the Y under Shr indicates the primary action event, or the event that owns the shared action statement. Use the INF line command, as shown in the figure, to list action events that share the statement associated with the primary action.
Figure: Shared Actions List at INF Command
+--------- Select an Action To Be Defined for FOPDEMO.CUSTOMERS -----+ ¦ ¦ ¦ 1 of 12 ¦ ¦ Cmd Action Description Active Shr ¦ ¦ --- ----- ¦ ¦ *** ***** +-----Before Extract of the First Row From a Table-----+ ¦ ¦ INF Befor ¦ SQL Shared With 1 OF 2 ¦ ¦ ¦ ___ Befor ¦ --------------------------------------------- ¦ ¦ ¦ ___ After ¦ ******************** TOP ******************** ¦ ¦ ¦ ___ After ¦ After Extract of a Row From a Table ¦ ¦ ¦ ___ Befor ¦ Before Delete of the First Row From a Table ¦ ¦ ¦ ___ Befor ¦ ****************** BOTTOM ******************* ¦ ¦ ¦ ___ After +------------------------------------------------------+ ¦ ¦ ___ After ¦ ¦ ___ Before Restore of the First Row To a Table N N ¦ ¦ ___ Before Restore of a Row To a Table N N ¦ ¦ ___ After Restore of the First Row To a Table N N ¦ ¦ ___ After Restore of a Row To a Table N N ¦ ¦ ¦ ¦ Line Commands: (S)elect, (INF)ormation, (CLR)Clear ¦ ¦ ¦ +--------------------------------------------------------------------+
Use the S or SEL line command to select one or more action events. Archive displays the Enter an SQL Statement panel for each selection, in sequence.
Figure: SQL Statement
------- Enter an SQL Statement to be used by the Action Being Defined ---------
Command ===> Scroll ===> PAGE
Before Extract of the First Row From a Table
Cmd 1 of 5
--- ------------------------------------------------------------------------
*** ********************************* TOP **********************************
___
___
___
___
___
*** ******************************* BOTTOM *********************************
Line Commands: (I)nsert, (D)elete, (R)epeat, (M)ove, (C)opy
Use the LIST COLUMNS command to add column names, if needed
Use the LIST VARIABLES command to add Action Variables, if needed
Use the SQLEdit command to invoke the ISPF editor with all of its facilities
Use the SHARESQL command to select an Action whose SQL you wish to use
Use the COPYSQL command to select an Action whose SQL you wish to copy
Use this panel to enter the complete action statement or call to a stored procedure that is executed when the action event occurs during processing. For example, you might use an action statement to record deletions by inserting the primary key value into a table before deleting the row. As another example, you can place an indicator in a row that has been restored so that it will not be archived a second time or can be deleted in normal database maintenance procedures.
Type the desired SQL in the panel. The scrollable area displays the SQL in segments of 8 lines of 72 positions each. A maximum of 425 lines is available for the SQL and documenting comments. Notation next to it indicates the number of lines and the relative position of the first visible line.
Standard DB2 conventions apply to comments; each line must begin with two hyphens (--).
You can perform several editing functions using line commands. The functions and corresponding line commands are:
Use A (After) or B (Before) to indicate the destination for a Copy or Move line command.
A column variable can be used in the statement for any action event except one executed before processing the first row or after processing the last row. The column variable must be preceded by a colon (:) in the statement.
You can use a substitution variable that has been defined on the Substitution Variable Display panel. The name of the substitution variable must be preceded by a colon (:). (For more information, see Substitution Variables.)
Action Variables are built-in functions that can be used to return information about the specific Archive, Delete, or Restore Action. An Action Variable must be preceded by a colon (:) in the statement.
For users of action variables with releases of Optim prior to 7.2: Optim 7.2 increased processing limits and changed the precision for some action variables. In Optim 6.1 and earlier, precision was integer for the following variables. With Optim 7.2 and later, precision for the listed variables is bigint. Depending on the way in which you use these variables, you may need to make changes to accommodate the difference in data type. For example, if a variable inserts a value into a DB2 table, ensure that the destination column is defined to allow a bigint value.
The following General Variables are available to all action statements.
FOP_ACTION and FOP_ACTION_TEXT return the following values:
The following Extract Variables are available to Extract action statements.
The following Restore/Delete Variables are available to Restore and Delete action statements.
An example of Action Variable usage is shown in the following figure:
Figure: SQL Statement Using Action Variable
------- Enter an SQL Statement to be used by the Action Being Defined ---------
Command ===> Scroll ===> PAGE
Before Extract of the First Row From a Table
Cmd 1 of 3
--- ------------------------------------------------------------------------
*** ********************************* TOP **********************************
___ INSERT INTO FOPDEMO.ARCHIVE_ACTION
___ (ARCHIVE_CUSTID, ARCHIVE_FILE)
___ VALUES (CUST_ID, :FOP_ARCHIVE_FILE_NAME);
*** ******************************* BOTTOM *********************************
Line Commands: (I)nsert, (D)elete, (R)epeat, (M)ove, (C)opy
Use the LIST COLUMNS command to add column names, if needed
Use the LIST VARIABLES command to add Action Variables, if needed
Use the SQLEdit command to invoke the ISPF editor with all of its facilities
Use the SHARESQL command to select an Action whose SQL you wish to use
Use the COPYSQL command to select an Action whose SQL you wish to copy
Before each row is extracted, this action statement inserts CUST_ID and ARCHIVE_FILE name values (using the Action Variable FOP_ARCHIVE_FILE_NAME) into table FOPDEMO.ARCHIVE_ACTION.
An Action statement can call a stored procedure using one or both parts of the name (i.e., userid.procname). If one part is used, the current User ID is assumed as the prefix.
Parameters passed with the CALL statement must be enclosed in parentheses. The number of parameters must be the same as the number in the procedure and must be passed in the same order. If the stored procedure does not require parameters, the CALL statement can pass an empty parameter list ( ) or omit the parentheses.
The CALL statement parameter list may include column variables, substitution variables, Action Variables, and constants.
A stored procedure called by an action statement can cause archive processing to skip a row or to terminate the process by passing a value in the Action Variable, FOP_RETURN_CODE. Valid FOP_RETURN_CODE values are:
Use the LIST COLUMNS primary command to display a list of column variables for the table. You can scroll this list and use the S line command to select a column variable from it. The selected column variable is added to the end of the action statement.
Use the LIST SUBS primary command to display a list of substitution variables. You can scroll this list and use the S line command to select a substitution variable from it. The selected substitution variable is added to the end of the action statement.
Use the LIST VARIABLES command to display a list of Action Variables when formulating an action statement. Use the S line command to select a variable from the list. The selected variable is added at the end of the action statement.
Use the SQLEDIT command to invoke an ISPF edit session for the displayed action statement. All standard ISPF facilities are available. Use the ISPF COPY command to insert data from a file and edit as desired.
Use END to terminate the ISPF session and redisplay the Enter an SQL Statement panel. The edited data is inserted into the panel.
Use the SHARESQL command to select an action event that owns the action statement you wish to associate with the current action. A list of available action events is displayed. You can scroll this list and use the S line command to select a desired event. The statement associated with the selected event is inserted into the action statement, overwriting anything already entered. You can edit the shared statement. If so, it is changed for all actions for which it is active.
Use the COPYSQL command to select an event that owns the action statement you wish to copy to the current event. A list of available events is displayed. You can scroll this list and use the S line command to select a desired event. The action statement for the selected event is inserted at the end of your entry. Editing the action statement does not affect the statement from which it is copied.
Use END to save the contents of the SQL Editor and return to the Select an Action To Be Defined panel. Use END again to return to the Select Tables/Views for AD panel. When the Select Tables/Views for AD panel is redisplayed, Status contains ACT to indicate that an Archive Action has been specified for the selected table.