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

Use the Partitioning Key High Value

Use the Partitioning Key High Value

The most effective set up of the partition interim table, therefore, is to use the partitioning key high value to determine if a partition qualifies for processing. The high value for each partition can be stored in the interim table, and then you can build business rules to validate the high value of the partition meets the archive criteria.
For example, the following figure shows how you can store the high value in the interim table if the partitioning key is a date column:
The following figure shows the select clause:
Note that the high value is populated by using a function, AA_PARTITION_PKG.GET_DATE_HIGH_VALUE. The high_value column in the ALL_TAB_PARTITIONS table is a LONG data type. This limits the ability to query and evaluate the high_value column directly from ALL_TAB_PARTITIONS. The function retrieves the high value and translates the value into a date. The high value of a partition may be:
TO_DATE(' 2007-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
The function translates this value into 01-DEC-2007. The following figure shows how you can then build a business rule to validate that the partition key is less than the archive date:
The following figure shows the condition:
This rule disqualifies any partitions where the high value date is greater than the archive date.
If the partition key is based on a varchar or number column, high value can be retrieved and stored in the interim table using the function, AA_PARTITION_PKG.GET_HIGH_VALUE.
The following figure shows the select clause:
You can now build a business rule to validate the high value that meets the archive or purge criteria. The following syntax illustrates this:
(A.table_owner, A.table_name, A.partition_name) IN (select X.table_owner, X.table_name, X.partition_name from XC_346_ST2 X where high_value < :p_archive_partition_key_value) Where :p_archive_partition_value is a value determined by the user at run-time. (A.table_owner, A.table_name, A.partition_name) IN (select X.table_owner, X.table_name, X.partition_name from XC_346_ST2 X where high_value between :p_range_low and :p_range_high ) (A.table_owner, A.table_name, A.partition_name) IN (select X.table_owner, X.table_name, X.partition_name from XC_346_ST2 X where high_value IN (‘AR’,’AP’,’GL’)

0 COMMENTS

We’d like to hear from you!