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

Parallel Delete Controlled by Data Archive

Parallel Delete Controlled by Data Archive

Data Archive spawns and controls multiple delete processes. Each delete process is a Java thread, where each worker processes a range of rows. This method is slower than Oracle's parallelism, but does not lock the table. Therefore, use this option when you schedule archive cycles during business hours.
The following are sample delete statements executed by parallel worker threads:
DELETE FROM PO.PO_DISTRIBUTIONS_ALL A WHERE (ROWID, PO_DISTRIBUTION_ID) IN ( SELECT /*+ CARDINALITY(X,1) */ APPLIMATION_ROW_ID, PO_DISTRIBUTION_ID FROM AM_STAGE.AA_3666113_T1 X WHERE APPLIMATION_ROW_NUM BETWEEN 1 AND 100000) DELETE FROM PO.PO_DISTRIBUTIONS_ALL A WHERE (ROWID, PO_DISTRIBUTION_ID) IN ( SELECT /*+ CARDINALITY(X,1) */ APPLIMATION_ROW_ID, PO_DISTRIBUTION_ID FROM AM_STAGE.AA_3666113_T1 X WHERE APPLIMATION_ROW_NUM BETWEEN 1000001 AND 200000)
Consider the following factors before you use Oracle parallel DML:
  • Oracle parallelism during delete operations requires an exclusive lock on the table that is deleted, preventing other users from updating the same table. Read access on these tables is still possible. Use Oracle parallelism if archive cycles are scheduled outside of business hours when no users need write access to the ERP system.
  • The source or ERP tables need to support parallel DML. Tables that you created in an Oracle 8/8i database, which you upgraded to 9i/10g, do not support parallel DML. Use the query below to check the relevant table property:
    select a.property, decode(bitand(a.property,536870912), 0, 'DISABLED', 'ENABLED') pdml_enabled from sys.tab$ a, dba_objects b where a.obj# = b.object_id and b.object_name = '&table_name' and b.owner = '&owner';
If a table does not support parallel DML, Oracle provides a means to enable it. For more information, contact Informatica Global Customer Support.