7 About Index, Table and Task Actions

This chapter describes how to use the actions feature, which enables you to enables you to define and run SQL scripts to carry out various actions in relation to indexes, tables, and tasks.

This chapter contains the following topics:

• Overview of Index, Table and Task Actions

• Defining a SQL Script for an Action

• Assigning an Action to a Repository Object

• Functions for Use with Actions

• Example of How to Use a DAC Source System Parameter in an Action

Overview of Index, Table and Task Actions

The Actions feature enables you to define and run SQL scripts to carry out various actions in relation to indexes, tables, and tasks.

• Index actions can override the default behavior for dropping and creating indexes by mode type (full load, incremental load, or both). The Index action type are Create Index and Drop Index.

  For example you can define an index action to create indexes for tasks with the commands defined for full loads, or incremental loads, or both types. Index actions override all other index properties.

• Table actions can override the default behavior for truncating and analyzing tables by mode type. The Table action types are Truncate Table and Analyze Table.

  For example you can define a table action to truncate tables with the commands defined for full loads, or incremental loads, or both . Table actions override all other index properties.

• Task actions can add new functionality based on various task behaviors. The following task action types are available:

  • Preceding Action

    Use this type to execute a SQL script before a task runs.

  • Success Action

    Use this type to execute a SQL script after a task runs successfully.

  • Failure Action

    Use this type to execute a SQL script if a task fails during its execution.

  • Upon Failure Restart

    Use this type to execute a SQL script when a task that previously failed is restarted.

You can also use the Actions Template feature to:

• Combine SQL templates to do synchronized actions, such as create and analyze indexes.

• Combine object level properties with user-defined parameters in SQL statements and stored procedures.

To define an action and assign it to a repository object, you need to complete the following procedures:

• Defining a SQL Script for an Action

• Assigning an Action to a Repository Object

Defining a SQL Script for an Action

Follow this procedure to define a SQL statement for index, table, and task actions. After completing this procedure, proceed to "Assigning an Action to a Repository Object".

For more information about Index, Table and Task actions, see "Overview of Index, Table and Task Actions".

To define a SQL statement for an action

1. From the Tools menu, select Seed Data, then select one of the following:

   • Index Actions

   • Table Actions

   • Task Actions

   The Actions dialog box opens.

2. In the toolbar, click New.

3. In the new record field, enter a name for the action, and then click Save.

4. Double-click in the Value field.

   The Value dialog box appears.

   
   

5. Select a format for the tree view.

   • Flat view displays the SQL entries in a list format in their order of execution.

   • Category view displays the entries by the categories SQL and Stored Procedure.

     You can reorder the entries in the tree by dragging and dropping them.

6. Click Add.

7. In the new record field, enter or select the appropriate information.

   Field	Description
   Name	Logical name of the SQL block.
   Type	SQL or Stored procedure
   Database Connection Type	Should be used only for SQL types (not stored procedures). Defines which database the SQL statement will run against. Possible values are: Source - SQL runs against the source connection defined for the task. Target - SQL runs against the source connection defined for the task. Both - SQL runs against both the source and target connection. Table Connection - SQL runs against the table-specific connection if a separate table connection is available.
   Table Type	Specifies the table type against which the SQL will run. Possible values are: All Source - SQL runs against all source tables defined for the task. All Target - SQL runs against all target tables defined for the task. Source Lookup - SQL runs against all the source lookup tables defined for the task. Source Primary - SQL runs against all the source primary tables defined for the task. Source Auxiliary - SQL runs against all the source auxiliary tables defined for the task.
   Continue on Fail	Specifies whether an execution should proceed if a given SQL block fails.
   Retries	Specifies how many retries are allowed. If the number is not positive, a default number of one (1) will be used.
   Valid Database Platforms	Specifies the valid database platforms against which the SQL will run. If this field is left empty, the SQL can be run against any database.

   
   

8. In the lower-right side text box, enter a SQL statement.

   The SQL Statement tab to the left of the text box lists all the supported SQL functions and DAC source system parameters that you can use in constructing custom SQLs. Double-click a function or source system parameter to move it into the text box.

   For a description of the available functions, see "Functions for Use with Actions".

   The source systems parameters list contains the names of all source system parameters defined in the DAC Repository, with the prefix @DAC_. During runtime, the DAC Server resolves the source system parameter and replaces its name with the runtime value.

   For an example of how to use a source system parameter in a SQL statement, see "Example of How to Use a DAC Source System Parameter in an Action".

9. (Optional) Enter a comment about the SQL in the Comment tab.

10. Click OK.

    Note:

    You can add multiple SQL statements and stored procedures to a single action.

11. To assign this action to a repository object, proceed to "Assigning an Action to a Repository Object".

Assigning an Action to a Repository Object

Follow this procedure to assign an action to a DAC Repository object. Before you do this procedure, you must have defined a SQL script for an action. For instructions, see "Defining a SQL Script for an Action".

To assign an action to a repository object

1. In the Design view, navigate to one of the following tabs, depending on the object type for which you want to assign an action:

   • Indices tab

   • Tables tab

   • Tasks tab

2. Select or query for the object for which you want to assign an action.

3. With the appropriate object selected in the top window, select the Actions subtab.

4. Click New in the subtab toolbar.

5. In the new record field, do the following:

   1. Select an Action Type.

      For a description of the available Action Types, see the following: "Indices Tab: Actions Subtab", "Tables Tab: Actions Subtab", and "Tasks Tab: Actions Subtab".

   2. Select the Full, Incremental, or Both load type.

   3. Double-click the Action field to open the Choose Action dialog box, and select an action.

      Note:

      For instructions on defining an action, see "Defining a SQL Script for an Action".

   4. Click OK to close the Choose Action dialog box.

   5. Click Save in the subtab toolbar.

Functions for Use with Actions

This section includes a list of functions that are available for Index, Table and Task actions.

• Table 7-1, "Functions for Index Actions"

• Table 7-2, "Functions for Table Actions"

• Table 7-3, "Functions for Task Actions"

Table 7-1 Functions for Index Actions

Function	Description
getAdditionalColumns()	Returns a comma separated list of included index columns. This function is related to the #Unique Columns index property. For DB2 and DB2-390 databases, the list may include a combination of unique and included columns.
getAnalyzeStatement()	Returns the default DAC index analyze statement (for this particular index).
getAnalyzeTableStatement()	Returns the default DAC table analyze statement (for the parent table).
getBitMapString	Resolves to the string BITMAP if it is a bitmap index; otherwise the string is empty.
getClusteredString	Resolves to the string CLUTSTERED if it is a bitmap index; otherwise the string is empty.
getCreateIndexStatement()	Returns the default DAC index creation statement.
getDBType()	Returns the physical data source connection type (Oracle OCI8, Oracle Thin, DB2, DB2-390, MSSQL, or Teradata).
getDropIndexStatement()	Returns the default DAC index drop statement.
getHashString()	Resolves to the string HASH if it is a hash index; otherwise the string is empty.
getImageSuffix()	Resolves to the table image suffix if one is specified; otherwise the string is empty.
getIndexColumns()	Returns a comma separated list of index columns.
getIndexName()	Returns the index name.
getIndexTableSpace()	Resolves to the index space name if one exists; otherwise the string is empty.
getNamedSource()	Returns the DAC physical connection name.
getRvrsScanString()	Resolves to the string ALLOW REVERSE SCANS if the index supports reverse scans; otherwise the string is empty.
getTableName()	Returns the table name.
getTableOwner()	Returns the table owner name.
getTableSpace()	Returns the table space name if one exists; otherwise the string is empty.
getTruncateTableStatement()	Returns the default DAC table truncate statement.
getUniqueColumns()	Returns a comma separated list of unique index columns. This function is a counterpart of the getAdditionalColumns() function.
getUniqueString()	Resolves to the string UNIQUE if the index is unique; otherwise the string is empty.

Table 7-2 Functions for Table Actions

Function	Description
getAnalyzeTableStatement()	Returns the default DAC table Analyze statement.
getDBType()	Returns the physical data source connection type (Oracle OCI8, Oracle Thin, DB2, DB2-390, MSSQL, or Teradata).
getImageSuffix()	Returns the table image suffix if one exists; otherwise the string is empty.
getCreateIndexStatement()	Returns the default DAC index creation statement.
getNamedSource()	Returns the DAC physical connection name.
getDropIndexStatement()	Returns the default DAC index drop statement.
getTableName()	Returns the table name.
getTableOwnerName()	Returns the table owner.
getTableSpace()	Returns the table space name if one exists; otherwise the string is empty.
getTruncateTableStatement()	Returns the default DAC table truncate statement.

Table 7-3 Functions for Task Actions

Function	Description
getAnalyzeTableStatement()	Returns the default DAC analyze table statement.
getDBType()	Returns the physical data source connection type (Oracle OCI8, Oracle Thin, DB2, DB2-390, MSSQL, or Teradata).
getImageSuffix()	Returns the table image suffix if one exists; otherwise the string is empty.
getNamedSource()	Returns the physical connection name in DAC.
getTableName()	Returns the table name.
getTableOwner()	Returns the table owner.
getTableSpace()	Returns the table space name if one exists; otherwise the string is empty.
getTruncateTableStatement()	Returns the default DAC truncate table statement.

Note:

Table-related task action functions should be used only for SQL blocks with a specified table type. For these blocks, DAC will loop through the tables of the type you specify and execute custom SQL for each table. Functions will be substituted with table-specific values for each iteration.

For example, if you wanted to gather statistics in a particular way after creating specific indexes, you would need to create index actions with two SQL blocks:

1. getCreateIndexStatement()

2. DBMS_STATS.GATHER_TABLE_STATS(ownname => 'getTAbleOwner()', tabname => 'getTableName()', estimate_percent => 70, method_opt +> 'FOR ALL COLUMNS SIZE AUTO', cascade => false)

Example of How to Use a DAC Source System Parameter in an Action

The following example illustrates how to use a DAC source system parameter in defining an action.

Assume there is a source system parameter called COUNTRY (note: source system parameter names are case sensitive). And, you use this parameter in an action using the following SQL statement:

DELETE FROM TABLE1 WHERE COUNRY_NAME='@DAC_COUNTRY'

Assume that during the ETL process, COUNTRY gets resolved to Canada. The resulting SQL that is executed by the DAC Server would be the following:

DELETE FROM TABLE1 WHERE COUNTRY_NAME='Canada'