Table of Contents

Search

  1. Preface
  2. Backing Up and Restoring the Data Vault
  3. Configuring Centera as a Remote Data Vault Store
  4. Configuring Data Archive for a Legacy Source Using Legacy Adapters
  5. Data Archive Seamless Access for PeopleSoft
  6. Data Archive Transaction Restore API
  7. Dropping and Truncating Partitions in Data Archive
  8. High Availability Configuration for the Data Archive and File Archive Service Versions 6.1 to 6.3
  9. 0955-High Availability Configuration for the Data Vault Version 6.4 and Later
  10. How to Create Business Rules to Archive and Purge Transactional Data
  11. How to Uninstall Data Archive 5.1
  12. How to Uninstall Data Archive 5.3
  13. How to Use Scripts to Change Database User Passwords in the ILM Repository
  14. IBM DB2 Database Connectivity and Setup for Data Archive
  15. Installing Data Visualization
  16. Integrating Third-Party Schedulers in ILM Engine
  17. Parallel Processing in Data Archive
  18. Seamless Access Configuration for Siebel Applications
  19. Seamless Access Setup for Oracle E-Business Suite
  20. Using the Data Vault Service JDBC Driver to Connect to the Data Vault
  21. Using Multiple Engines in an ILM Environment
  22. Using PowerExchange ODBC Connections in a Data Archive Retirement Project
  23. Discovering Foreign Key Relationships in Enterprise Data Manager

Data Archive How-To Guide

Data Archive How-To Guide

Metadata Tuning

Metadata Tuning

To enable parallelism, complete the following tasks:
  1. Set the relevant Degree of Parallelism attribute.
  2. Add parallel hints to Data Archive metadata.
For example, to archive the PO_DISTRIBUTIONS_ALL table with parallelism, add the following parallel hint to the insert statement metadata for the table:
A.po_header_id IN (SELECT /*+ FULL(X) CARDINALITY(X, 1) PARALLEL(X, #) */ X.po_header_id FROM XA_4149_PO_HEADERS_INTERIM X WHERE X.purgeable_flag = 'Y')
The pound sign (#) is a placeholder that is replaced at runtime with the corresponding Degree of Parallelism attribute. This variable enables the same metadata to work with different degrees of parallelism, such as the Test and Production environments.
A Data Archive insert statement executes in parallel if you set the Insert Degree of Parallelism and add a parallel hint metadata where clause. Using the where clause above the engine constructs an insert statement as follows:
INSERT /*+ APPEND PARALLEL (Z, 8) */ INTO AM_STAGE.AA_3666113 Z SELECT /*+ PARALLEL (A, 8) */ A.*, A.ROWID FROM PO.PO_DISTRIBUTIONS_ALL A WHERE A.po_header_id IN (SELECT /*+ FULL(X) CARDINALITY(X, 1) PARALLEL(X, 8) */ X.po_header_id FROM XA_4149_PO_HEADERS_INTERIM X WHERE X.purgeable_flag = 'Y')
If the metadata does not include a PARALLEL(X, #) hint, the Data Archive engine does not process the table in parallel. Also, no parallel hint is added to the insert clause of the statement. This allows you to control which tables the Data Archive engine archives in parallel.
The FULL and CARDINALITY hints ensure that the optimizer uses the proper execution plan when selecting the data to archive. The FULL hint suggests that Oracle does a full table scan on the interim table. The CARDINALITY hint tells the optimizer that the subselect only returns one row, which of course is not accurate, but it ensures that the ERP table is accessed using an index.
It is important to tune the statement properly for the parallel processing to work efficiently. Parallel and other hints might be required in multiple places in the metadata statement text, often combined with other hints, to be effective and improve performance. If you need help tuning one or more statements, contact Informatica Global Customer Support.
The Data Archive engine can also execute business rule update statements in parallel. The implementation is the same. The Update Degree of Parallelism attribute controls the parallelism for these update statements.
Data Archive does not require metadata changes to support delete parallelism. If the Delete Degree of Parallelism attribute is greater than one, the Data Archive engine adds the necessary parallel hints automatically. However, it only processes tables without a primary key metadata constraint in parallel if the Use Oracle Parallel DML For Delete source repository attribute is enabled and the metadata delete statement contains a parallel hint.