Communities
A collaborative platform to connect and grow with like-minded Informaticans across the globe
Product Communities
Connect and collaborate with Informatica experts and champions
Discussions
Have a question? Start a Discussion and get immediate answers you are looking for
User Groups
Customer-organized groups that meet online and in-person. Join today to network, share ideas, and get tips on how to get the most out of Informatica
Get Started
Community Guidelines
Knowledge Center
Troubleshooting documents, product guides, how to videos, best practices, and more
Knowledge Base
One-stop self-service portal for solutions, FAQs, Whitepapers, How Tos, Videos, and more
Support TV
Video channel for step-by-step instructions to use our products, best practices, troubleshooting tips, and much more
Documentation
Information library of the latest product documents
Velocity (Best Practices)
Best practices and use cases from the Implementation team
Learn
Rich resources to help you leverage full capabilities of our products
Trainings
Role-based training programs for the best ROI
Certifications
Get certified on Informatica products. Free, Foundation, or Professional
Product Learning Paths
Free and unlimited modules based on your expertise level and journey
Resources
Library of content to help you leverage the best of Informatica products
Tech Tuesdays Webinars
Most popular webinars on product architecture, best practices, and more
Product Availability Matrix
Product Availability Matrix statements of Informatica products
SupportFlash
Monthly support newsletter
Support Documents
Informatica Support Guide and Statements, Quick Start Guides, and Cloud Product Description Schedule
Product Lifecycle
End of Life statements of Informatica products
Ideas
Events
Change Request Tracking
Marketplace
English
  • Data Archive 6.4.4
  • All Products

Table of Contents

Search

  1. Preface
  2. Introduction
  3. Accessing Data Archive
  4. Working with Data Archive
  5. Scheduling Jobs
  6. Viewing the Dashboard
  7. Creating Data Archive Projects
  8. Salesforce Archiving
  9. SAP Application Retirement
  10. Creating Retirement Archive Projects
  11. Integrated Validation for Archive and Retirement Projects
  12. Retention Management
  13. External Attachments
  14. Data Archive Restore
  15. Data Discovery Portal
  16. Data Visualization
  17. Oracle E-Business Suite Retirement Reports
  18. JD Edwards Enterprise Retirement Reports
  19. Oracle PeopleSoft Applications Retirement Reports
  20. Smart Partitioning
  21. Smart Partitioning Data Classifications
  22. Smart Partitioning Segmentation Policies
  23. Smart Partitioning Access Policies
  24. Language Settings
  25. Appendix A: Data Vault Datatype Conversion
  26. Appendix B: Special Characters in Data Vault
  27. Appendix C: SAP Application Retirement Supported HR Clusters
  28. Appendix D: Glossary

User Guide

User Guide

Back Next

Expression-Based Retention

Expression-Based Retention

Configure expression-based retention when you want to base the retention period for records in an entity on an expression date. The expiration date for each record in the entity equals the retention period plus the date that the expression returns.
The expression must include statements that evaluate to a column date or to a single date value in DATE datatype. It can include all SQL statements that the Data Vault Service supports. The expression can include up to 4000 characters. If the expression does not return a date or if the expression syntax is not valid, the Update Retention Policy job fails. To prevent the Update Retention Policy job from failing, provide null-handling functions such as IFNULL or COALESCE in your expressions. This ensures that a suitable default value will be applied to records when the expression evaluates to a null or empty value.
The Update Retention Policy job includes the expression in the select statement that the job generates. The job selects the records from the Data Vault. It uses the date that the expression returns to update the retention expiration date for the selected records. When you monitor the Update Retention Policy job status, you can view the expression in the job parameters and in the job log. The job parameter displays up to 250 characters of the expression. The job log displays the full expression.
Use expressions to evaluate any of the following types of table data:
Dates in integer format
Use the TO_DATE function to evaluate columns that store dates as integers. The function converts the integer datatype format to the date datatype format so that the Update Retention Policy job can calculate the retention period based on the converted date. The TO_DATE function selects from the entity driving table.
To select a column from another table in the entity, use the full SQL select statement. For example, you want to set the retention policy to expire records 10 years after the employee termination date. The entity driving table, EMPLOYEE, includes the TERM_DATE column which stores the termination date as an integer, for example, 13052006. Create a retention policy, and set the retention period to 10 years. Add the following expression to the retention policy:
TO_DATE(TERM_DATE,'ddmmyyyy')
When the Update Retention Policy job runs, the job converts 13052006 to 05-13-2006 and sets the expiration date to 05-13-2016.
Dates or other values in tags
Add tags to archived records to capture dates or other values that might not be stored or available in the Data Vault. Use an expression to evaluate the tag and set the retention period based on the tag value.
For example, you retired a source application that contained products and manufacturing dates. You want to set the expiration date for product records to 10 years after the last manufacturing date. When you retired the application, some products were still in production and did not have a last manufactured date. To add a date to records that were archived without a last manufacturing date, you can add a tag column with dates. Then, define a retention policy and use an expression to evaluate the date tag.
For more information on how to use a tag to set the retention period, see Using a Tag Column to Set a Retention Period.
Data from all tables in the Data Vault archive folder
Use an expression to evaluate table columns across all entities in the Data Vault archive folder. You can use a simple SQL statement to evaluate data from one column, or you can use complex SQL statements to evaluate data from multiple columns.
For example, you retired an application that contained car insurance policies. An insurance policy might have related messages and claims. You want to set the expiration date for insurance policy records to five years after the latest transaction date from the POLICY, MESSAGES, or CLAIMS tables. If the insurance policy has a medical or property damage claim over $100,000, you want to set the expiration date to 10 years.
If you enter a complex SQL statement that evaluates column dates across high-volume tables, you might be able to increase query performance by changing the retention policy for specific records. Change the retention policy for records and then run the Update Retention Policy job instead of entering an expression to evaluate date columns across tables. Data Archive can generate SQL queries which run more quickly than user-entered queries that perform unions of large tables and contain complex grouping.
You can configure expression-based retention in the workbench when you manage retention policies or when you change retention policies for archived records in Data Discovery. The retention policy list in Data Discovery appends "(Expression)" to the names of retention policies with expression-based associations. You cannot configure expression-based retention for retention policies with indefinite retention periods.
0 COMMENTS
We’d like to hear from you!