Monitoring Rule-Based Transformations

28 Monitoring Rule-Based Transformations

A rule-based transformation is any modification to a message that results when a rule in a positive rule set evaluates to TRUE. This chapter provides sample queries that you can use to monitor rule-based transformations.

The following topics describe monitoring rule-based transformations:

Note:

The Oracle Streams tool in Oracle Enterprise Manager Cloud Control is also an excellent way to monitor an Oracle Streams environment. See the online Help for the Oracle Streams tool for more information.

28.1 Displaying Information About All Rule-Based Transformations

The query in this section displays the following information about each rule-based transformation in a database:

The owner of the rule for which a rule-based transformation is specified
The name of the rule for which a rule-based transformation is specified
The type of rule-based transformation:
- SUBSET RULE is displayed for subset rules, which use internal rule-based transformations.
- DECLARATIVE TRANSFORMATION is displayed for declarative rule-based transformations.
- CUSTOM TRANSFORMATION is displayed for custom rule-based transformations.

Run the following query to display this information for the rule-based transformations in a database:

COLUMN RULE_OWNER HEADING 'Rule Owner' FORMAT A20
COLUMN RULE_NAME HEADING 'Rule Name' FORMAT A20
COLUMN TRANSFORM_TYPE HEADING 'Transformation Type' FORMAT A30

SELECT RULE_OWNER, 
       RULE_NAME, 
       TRANSFORM_TYPE
  FROM DBA_STREAMS_TRANSFORMATIONS;

Your output looks similar to the following:

Rule Owner           Rule Name            Transformation Type
-------------------- -------------------- ------------------------------
STRMADMIN            EMPLOYEES23          DECLARATIVE TRANSFORMATION
STRMADMIN            JOBS26               DECLARATIVE TRANSFORMATION
STRMADMIN            DEPARTMENTS33        SUBSET RULE
STRMADMIN            DEPARTMENTS32        SUBSET RULE
STRMADMIN            DEPARTMENTS34        SUBSET RULE
STRMADMIN            DEPARTMENTS32        CUSTOM TRANSFORMATION
STRMADMIN            DEPARTMENTS33        CUSTOM TRANSFORMATION
STRMADMIN            DEPARTMENTS34        CUSTOM TRANSFORMATION

28.2 Displaying Declarative Rule-Based Transformations

A declarative rule-based transformation is a rule-based transformation that covers one of a common set of transformation scenarios for row LCRs. Declarative rule-based transformations are run internally without using PL/SQL.

The query in this section displays the following information about each declarative rule-based transformation in a database:

The owner of the rule for which a declarative rule-based transformation is specified.
The name of the rule for which a declarative rule-based transformation is specified.
The type of declarative rule-based transformation specified. The following types are possible: ADD COLUMN, DELETE COLUMN, KEEP COLUMNS, RENAME COLUMN, RENAME SCHEMA, and RENAME TABLE.
The precedence of the declarative rule-based transformation. The precedence is the execution order of a transformation in relation to other transformations with the same step number specified for the same rule. For transformations with the same step number, the transformation with the lowest precedence is executed first.
The step number of the declarative rule-based transformation. If more than one declarative rule-based transformation is specified for the same rule, then the transformation with the lowest step number is executed first. You can specify the step number for a declarative rule-based transformation when you create the transformation.

Run the following query to display this information for the declarative rule-based transformations in a database:

COLUMN RULE_OWNER HEADING 'Rule Owner' FORMAT A15
COLUMN RULE_NAME HEADING 'Rule Name' FORMAT A15
COLUMN DECLARATIVE_TYPE HEADING 'Declarative|Type' FORMAT A15
COLUMN PRECEDENCE HEADING 'Precedence' FORMAT 99999
COLUMN STEP_NUMBER HEADING 'Step Number' FORMAT 99999

SELECT RULE_OWNER, 
       RULE_NAME, 
       DECLARATIVE_TYPE,
       PRECEDENCE,
       STEP_NUMBER
  FROM DBA_STREAMS_TRANSFORMATIONS
  WHERE TRANSFORM_TYPE = 'DECLARATIVE TRANSFORMATION';

Your output looks similar to the following:

                                Declarative
Rule Owner      Rule Name       Type            Precedence Step Number
--------------- --------------- --------------- ---------- -----------
STRMADMIN       JOBS26          RENAME TABLE             4           0
STRMADMIN       EMPLOYEES23     ADD COLUMN               3           0

Based on this output, the ADD COLUMN transformation executes before the RENAME TABLE transformation because the step number is the same (zero) for both transformations and the ADD COLUMN transformation has the lower precedence.

When you determine which types of declarative rule-based transformations are in a database, you can display more detailed information about each transformation. The following data dictionary views contain detailed information about the various types of declarative rule-based transformations:

The DBA_STREAMS_ADD_COLUMN view contains information about ADD COLUMN declarative transformations.
The DBA_STREAMS_DELETE_COLUMN view contains information about DELETE COLUMN declarative transformations.
The DBA_STREAMS_KEEP_COLUMNS view contains information about KEEP COLUMNS declarative transformations.
The DBA_STREAMS_RENAME_COLUMN view contains information about RENAME COLUMN declarative transformations.
The DBA_STREAMS_RENAME_SCHEMA view contains information about RENAME SCHEMA declarative transformations.
The DBA_STREAMS_RENAME_TABLE view contains information about RENAME TABLE declarative transformations.

For example, the previous query listed an ADD COLUMN transformation and a RENAME TABLE transformation. The following sections contain queries that display detailed information about these transformations:

Note:

Precedence and step number pertain only to declarative rule-based transformations. They do not pertain to subset rule transformations or custom rule-based transformations.

28.2.1 Displaying Information About ADD COLUMN Transformations

The following query displays detailed information about the ADD COLUMN declarative rule-based transformations in a database:

COLUMN RULE_OWNER HEADING 'Rule|Owner' FORMAT A9
COLUMN RULE_NAME HEADING 'Rule|Name' FORMAT A12
COLUMN SCHEMA_NAME HEADING 'Schema|Name' FORMAT A6
COLUMN TABLE_NAME HEADING 'Table|Name' FORMAT A9
COLUMN COLUMN_NAME HEADING 'Column|Name' FORMAT A10
COLUMN COLUMN_TYPE HEADING 'Column|Type' FORMAT A8

SELECT RULE_OWNER, 
       RULE_NAME, 
       SCHEMA_NAME,
       TABLE_NAME,
       COLUMN_NAME,
       ANYDATA.AccessDate(COLUMN_VALUE) "Value",
       COLUMN_TYPE
  FROM DBA_STREAMS_ADD_COLUMN;

Your output looks similar to the following:

Rule      Rule         Schema Table     Column                          Column
Owner     Name         Name   Name      Name       Value                Type
--------- ------------ ------ --------- ---------- -------------------- --------
STRMADMIN EMPLOYEES23  HR     EMPLOYEES BIRTH_DATE                      SYS.DATE

This output show the following information about the ADD COLUMN declarative rule-based transformation:

It is specified on the employees23 rule in the strmadmin schema.
It adds a column to row LCRs that involve the employees table in the hr schema.
The column name of the added column is birth_date.
The value of the added column is NULL. Notice that the COLUMN_VALUE column in the DBA_STREAMS_ADD_COLUMN view is type ANYDATA. In this example, because the column type is DATE, the ANYDATA.AccessDate member function is used to display the value. Use the appropriate member function to display values of other types.
The type of the added column is DATE.

28.2.2 Displaying Information About RENAME TABLE Transformations

The following query displays detailed information about the RENAME TABLE declarative rule-based transformations in a database:

COLUMN RULE_OWNER HEADING 'Rule|Owner' FORMAT A10
COLUMN RULE_NAME HEADING 'Rule|Name' FORMAT A10
COLUMN FROM_SCHEMA_NAME HEADING 'From|Schema|Name' FORMAT A10
COLUMN TO_SCHEMA_NAME HEADING 'To|Schema|Name' FORMAT A10
COLUMN FROM_TABLE_NAME HEADING 'From|Table|Name' FORMAT A15
COLUMN TO_TABLE_NAME HEADING 'To|Table|Name' FORMAT A15

SELECT RULE_OWNER, 
       RULE_NAME, 
       FROM_SCHEMA_NAME,
       TO_SCHEMA_NAME,
       FROM_TABLE_NAME,
       TO_TABLE_NAME
  FROM DBA_STREAMS_RENAME_TABLE;

Your output looks similar to the following:

                      From       To         From            To
Rule       Rule       Schema     Schema     Table           Table
Owner      Name       Name       Name       Name            Name
---------- ---------- ---------- ---------- --------------- ---------------
STRMADMIN  JOBS26     HR         HR         JOBS            ASSIGNMENTS

This output show the following information about the RENAME TABLE declarative rule-based transformation:

It is specified on the jobs26 rule in the strmadmin schema.
It renames the hr.jobs table in row LCRs to the hr.assignments table.

28.3 Displaying Custom Rule-Based Transformations

A custom rule-based transformation is a rule-based transformation that requires a user-defined PL/SQL function. The query in this section displays the following information about each custom rule-based transformation specified in a database:

The owner of the rule on which the custom rule-based transformation is set
The name of the rule on which the custom rule-based transformation is set
The owner and name of the transformation function
Whether the custom rule-based transformation is one-to-one or one-to-many

Run the following query to display this information:

COLUMN RULE_OWNER HEADING 'Rule Owner' FORMAT A20
COLUMN RULE_NAME HEADING 'Rule Name' FORMAT A15
COLUMN TRANSFORM_FUNCTION_NAME HEADING 'Transformation Function' FORMAT A30
COLUMN CUSTOM_TYPE HEADING 'Type' FORMAT A11
 
SELECT RULE_OWNER, RULE_NAME, TRANSFORM_FUNCTION_NAME, CUSTOM_TYPE
  FROM DBA_STREAMS_TRANSFORM_FUNCTION;

Your output looks similar to the following:

Rule Owner           Rule Name       Transformation Function        Type
-------------------- --------------- ------------------------------ -----------
STRMADMIN            DEPARTMENTS31   "HR"."EXECUTIVE_TO_MANAGEMENT" ONE TO ONE
STRMADMIN            DEPARTMENTS32   "HR"."EXECUTIVE_TO_MANAGEMENT" ONE TO ONE
STRMADMIN            DEPARTMENTS33   "HR"."EXECUTIVE_TO_MANAGEMENT" ONE TO ONE

Note:

The transformation function name must be of type VARCHAR2. If it is not, then the value of TRANSFORM_FUNCTION_NAME is NULL. The VALUE_TYPE column in the DBA_STREAMS_TRANSFORM_FUNCTION view displays the type of the transform function name.