Archiving Data in PeopleSoft

Managing data is one of biggest job in bigger industries. Each and every day millions of rows are added into the database which eventually forces the table space to grow and finally results in meager performance of the application. In many cases, most of the data will lose the relevance as days pass by. For example consider the case of a service industry where the assignment of each employee for each day is stored as rows in database. For the ongoing and future assignments, it makes sense to read this data. But think of a situation where you have the 10 year old assignment data for an employee. By this time the employee may not even exists in the company. This is a big space where you could give some relief to your database and physical storage. The idea is to remove the old data from the actual transaction table. But you may have a question in mind like, what if for some reason I need that data? The answer is simple, delete the data from the real table and insert the same into another clone table called history table. This will highly improve the performance of your transactions and reports and will levy some of the load from the db server. This is exactly what is delivered by PeopleSoft called Data Archive Manager. It is a simple and easy method to archive the data on bigger tables in your enterprise. You need not be a PeopleSoft technical or database expert to use this tool. You just have to follow some simple and obvious steps to achieve the mission.

To create an archival system you have follow few steps such as creating history records, defining archival objects and templates and finally running the archival templates. I will be explaining the steps in details in the following sections.

The first step towards archival is creating history tables. Some of the PeopleSoft applications deliver the history tables along with the application. Even if there is no delivered table, you can create your own history table. Open the table for which history table needs to be created and save as <table name>_H and add the subrecord PSARCHIVE_SBR as the first field. Build the table and your history record is ready.

The next step is to create archive objects. This is nothing but defining the tables and corresponding history tables which requires archival. This can be defined at People Tools > Data Archive Manager > Manage Archive Objects. The one marked as Base Record will the one archived first and the rest of the tables will be archived based on that.

The next step is creating archival queries. It is nothing but PS QUERY of type Archive or Restore. This query should contain the where clause to filter the data that needs to be selected for archiving.

Once the above steps are done, you can create the Archival template. A archival template defines what tables needs to be archived, what is the criteria for selecting for archival and what are the process that needs to be done before and after archival. It can be accessed at People Tools > Data Archive Manager > Manage Archive Templates.

By this you have successfully defined the archival templates and process. Now all you need to do is to run the archival template. Run the process by going to People Tools > Data Archive Manager > Archive Data To History and providing the template which is created in the previous steps.