Archiving Policies

Archiving policies describe the details for archiving. There are several existing core archiving policies which you can use to archive the following data:

  • Payroll data
  • Interaction data
  • Temporary teams
  • Application mail data
  • Employee schedule data
  • Audit log data
  • Staff scheduler data
  • SO historical data
  • SO schedule data
  • Import interface data
  • Export interface data
  • Change history data
  • Clock Transaction data
  • Workmail XML attachments as part of the Payroll Based Journal Export for CMS report
  • Attendance Control logs

Notes

  • All archiving policies are enabled by default for new installations. The Archiving Task in the Job Scheduler will run all archiving policy definitions by default except for the Workbrain Sys State Purge archiving policy. For more information on the Archiving Task, see Infor Workforce Management Time and Attendance Implementation and Administration Guide.
  • Archive policies are stored in the WB_ARCH_POLICY and WB_ARCH_POL_DET tables.
  • The nature of the policy, such as the archive type, aging days, and archive class, is defined in WB_ARCH_POLICY.
  • Each WB_ARCH_POL_DET belongs to a WB_ARCH_POLICY. You can define the tables and their related SELECT statements in the WB_ARCH_POL_DET.
  • Only purge-type policies use the archive policy aging date at the policy level.
  • Only the archive-type policies use the ARCHIVE_BOUNDARY_DATE and ARCHIVE_POLICY_AGING_DAYS registry parameters.

Commit Types

Archive policy definitions determine how data is moved between datasources based on their commit type. A commit refers to deleting and setting data for purging from the core datasource and transferring data from the core datasource to the archive datasource. This activity is performed in a single transaction. In archiving, if both of these processes are successful, then a commit is completed.

There are several commit types which define when the commit occurs:

  • All or nothing
  • Every policy detail
  • Every X days
  • Every X employee
  • Every X records

ALL_OR_NOTHING

From all the available types, this type is the most aggressive for storage considerations because data processing performs complete archival or deletions for an archive policy in one transaction. In the event the transaction fails all archive or delete operations are rolled back. However, if the rollback segments for both the core datasource or archive datasource are not carefully managed, policies with large amounts of data can fail due to storage issues.

All policy details are archived at once.

EVERY_POLICY_DETAIL

This type commits the changes to the core or archive datasources for each policy detail. This type should be used when the dependency of policy details does not violate the archive policies consistency. For example, payroll data is not a good policy for this commit type because a failure in any of the policy details may create inconsistent and inaccurate data in the system. However, interaction data would be a good match for this type because failure in any detail does not affect other details. Moreover, another execution of the same policy would continue to process any remaining details from previous executions if administrators have addressed the issues by reviewing transaction logs.

The EVERY_POLICY_DETAIL commit type commits changes for each policy detail individually.

EVERY_X_DAYS

This commit type is the safest because the consistency of archive policy as a whole is significant and ALL_OR_NOTHING is nearly impossible to achieve.

The EVERY_X_DAYS commit type archives all policy details between the set amount of days.

In the algorithm, the archive or purge process starts from the given start date for every X days until the aging date is reached. Any failures are rolled back for each X days and the process proceeds to the next X days. In this setup the entity of a payroll data is preserved, but the transactions are committed at certain intervals to prevent possible storage and database problems due to long transactions.

Commit parameters are supplied in following format:


ParamName1=paramValue1, ParamName2=paramValue2… ParamNameN=paramValueN

The following parameters are required for this commit type:

  • START_DATE

    The date to start archiving (format = MM/DD/YYYY)

  • BATCH_DAYS

    The number of days to commit transactions (that is, X=? Days)

EVERY_X_EMPLOYEE

Even though the EVERY_X_DAYS algorithm is effective for archiving transactions in batches, the algorithm does not archive policies where data for one day can be very large and can create storage problems (that is, rollback segment size may be insufficient). Alternatively, you can use the EVERY_X_EMPLOYEE. This option can only be applied to archive policies which involves employee data.

The EVERY_X_EMPLOYEE commit type archives all policy details by employee.

Commit parameters are supplied in following format:


ParamName1=paramValue1, ParamName2=paramValue2… ParamNameN=paramValueN

The following parameters are required for this commit type:

  • EMP_BATCH_SIZE

    The number of employees that are processed in each subtransaction.

  • CONTROLLING_POLICY_PRIORITY

    The priority number for the policy which determines the number of employees that are used in batching employees. This number is generally the policy detail with the highest priority number.

Example:

Policy Parameters

EMP_BATCH_SIZE = 5

CONTROLLING_POLICY_PRIORITY = 70

WORK_DETAIL 60

Wrks_in (SELECT wrks_id FROM work_summary 
WHERE #WHERE.WORK_SUMMARY#)
WORK_SUMMARY 70

Wrks_work_date < #WBAR_AGING_DATE# 
AND #EMP_ID_SELECTOR#

To iterate through every X employee, the algorithm:

  1. Resolves the policy with CONTROLLING_POLICY_PRIORITY and retrieves the employee ids from the resolved query by replacing #EMP_ID_SELECTOR# with 1=1. For example:

    SELECT emp_id FROM work_summary

    WHERE Wrks_work_date < to_date('10/10/2003','mm/dd/yyyy') AND 1=1

  2. Once the employees are known ArchiveData.ArchivePolicyEmpIdTokenizer returns the policy details resolved for each employee size iteration until all employees are consumed.

If there are seven employees, then two iterations are made.

First iteration

WORK_DETAIL 60

Wrks_in (SELECT wrks_id FROM work_summary 
WHERE Wrks_work_date < to_date('10/10/2003','mm/dd/yyyy') 
AND emp_id in (1,2,3,4,5)
WORK_SUMMARY 70

Wrks_work_date < to_date('10/10/2003','mm/dd/yyyy') 
AND emp_id in (1,2,3,4,5)

Second iteration

WORK_DETAIL 60

Wrks_in (SELECT wrks_id FROM work_summary 
WHERE Wrks_work_date < to_date('10/10/2003','mm/dd/yyyy') 
AND emp_id in (6,7))
WORK_SUMMARY 70

Wrks_work_date < to_date('10/10/2003','mm/dd/yyyy') 
AND emp_id in (6,7)

EVERY_X_RECORDS

There are cases where previous commit types do not fit because of transactional constraints. Consider the case of interface staging table data. EVERY_X_DAYS cannot be used for this data because typically, one day of data can have thousands of records and deleting them in day precision would create transactional issues (that is, not enough rollback segment). EVERY_X_EMPLOYEE cannot be used because records do not have employee references.

For such above cases where purging or archiving by entity is not mandatory and failure of one transaction does not affect the others, EVERY_X_RECORDS enables manipulating policy details by every X records defined in commit parameters.

Although this commit type can be used for both purge and archive policies, this commit type is more suitable for purge policies where the failure of one purge batch would not affect the others.

Commit parameters are supplied in following format:


ParamName1=paramValue1, ParamName2=paramValue2… ParamNameN=paramValueN

The following parameters are required for this commit type:

BATCH_RECORD_COUNT —the number of records that are processed in each subtransaction.

Example:

Policy Parameters

BATCH_RECORD_COUNT = 1000

CHANGE_HISTORY 10

chnghist_change_date < #WBAP_AGING_DATE#

For the above policy, the archiving process iterates for 1000 records until all the records are purged or archived. Therefore, if there are 2,500 records that fit the criteria, then 3 transaction details would resemble:

CHANGE_HISTORY 1000 records have been processed

chnghist_change_date < 
to_date('04/04/2004','mm/dd/yyyy') 
and rownum <= 10000
CHANGE_HISTORY 1000 records have been processed

chnghist_change_date < 
to_date('04/04/2004','mm/dd/yyyy') 
and rownum <= 10000
CHANGE_HISTORY 500 records have been processed

chnghist_change_date < 
to_date('04/04/2004','mm/dd/yyyy') 
and rownum <= 10000