Data Archive Manager

This article explains how to use PeopleTools data archive manager to archive data in your PeopleSoft environment. The example in this article uses the PSACCESSLOG table which is used to track logins to your PeopleSoft environment. Over time, the PSACCESSLOG table can grow quite large, however you probably want to keep all of the information it contains (for auditing purposes) which is why archiving provides the best solution.

The following terminology is associated with PeopleSoft data archive manager:

  • Archive object which are the table(s) you want to archive.
  • Archive query which is used to determine the criteria for the set of data (rows) to archive
  • Archive template to combine the archive objects and queries
  • Archive data to history process/job which archives data to history
  • Restore query to restore archived rows from history
  • Archive auditing to track what has been archived

The base table is the key transactional table you will be archiving (e.g. PSACCESSLOG). It should contain all the keys by which any other tables you are archiving can be reference if you are archiving more than one table.

The history table is the table you will be storing the archived information (e.g. PSACCESSLOG_HST).

Archive processing involves:

  • Moving (selecting) the relevant data from the base table to the history table using the business rules specified in the archive query.
  • Deleting archive rows from the base table once they have successfully been transfered to the history table.
  • If required, restoring (rolling back) archived rows from the history table back to the base table.

Note that Data archive manager is new to PeopleTools 8.48.

Build the history table

The first step is to create the history table to store the archived data from the PSACCESSLOG transactional table. The history table must have EXACTLY the same structure as the base table with the addition of the PSARCHIVE_SBR sub-record to form the key structure. This sub-record includes the fields:

  • PSARCH_ID
  • PSARCH_BATCHNUM

The easiest way is to create the history table is to perform a save as of your base table (don't save any associated PeopleCode as you don't need it). Insert the PSARCHIVE_SBR sub-record as the first item in your history table, save, and build the table.

The standard convention is to add _HST to the table name if you can. You may need to shorten the rest of the table name in order to do this. Make sure you update the properties of the new history record and give it a description and comments.

In this example, the history table will be called PSACCESSLOG_HST:

psaccesslog-hst-definition.png

Note that to expand the sub-record display, select View > Expand All Subrecords from the application designer menu.

Archive Strategy

As recommended by PeopleBooks, you should first establish the business rules regarding the data that you want to archive before developing the archive process. In our example, the business rule will be to archive any data in PSACCESSLOG where the login date/time (LOGINDTTM) was 30 days ago or older. This is a very simple business rule, and your rules will vary in complexity.

You should also be aware of how the data in the base table is used. For example, there might be a report that shows anyone that logged in the last 3 months. Archiving data older than 30 days in PSACCESSLOG would have a direct impact on this report and modifications would need to be made for the report to continue working or the business rule may need to be adjusted.

Create the Archive Object

Navigate to: PeopleTools > Data Archive Manager > Manage Archive Objects

Add a new archive object. The archive object in this case will just be the name of the base table - PSACCESSLOG. Enter the archiving record (base table PSACCESSLOG) and the history record PSACCESSLOG_HST as shown. Also give the archive object a description and check the base table check box.

psaccesslog-manage-archive-objects.png

Create the Archive Query

Navigate to: Reporting Tools > Query > Query Manager

  1. Create a new query.
  2. In the records tab, find the record PSACCESSLOG and add this record to your query. You typically use the base table to determine the criteria for archiving.
  3. In the query tab, check all the fields.
  4. In the criteria tab:
    • Add a new field expression 1 for the field A.LOGINDTTM
    • Set the condition type to less than
    • Set the express 2 type to expression and the expression value to %CurrentDateIn - 30
psaccesslog-archive-query-criteria.png

This query returns rows from PSACCESSLOG 30 days or older.

  1. View the query SQL and run your query to ensure it works. You may find that your query result set is too large to display and warning appears - this is safe to ignore.
  2. Click on the properties link, name your query ARCHIVE_PSACCESSLOG and set the description to Archive PeopleSoft Access Log. Set the query type to archive and the owner to public.

Create the Archive Template

The archive template pulls the archive object definition and archive query together.

Navigate to: PeopleTools > Data Archive Manager > Manage Archive Templates

  1. Create a new archive template with the ID ACCLOG. The ID is limited to 8 characters.
  2. Specify the description Archive PeopleSoft Access Log
  3. Select your archive object (PSACCESSLOG) and check the base object check box.
  4. Select your archive query.
  5. Save the template.
  6. This simple example doesn't use a restore query. So leave the template allows selective restoring of data from history check box unchecked. There is a follow on article (coming soon) that will cover creating a restore query and will extend on this article.
  7. We won't need to use pre and post custom application engine programs. However you can create your own application engine programs to run before and after the archive process to perform additional custom steps. Leave the AE processes section empty.
psaccesslog-manage-archive-templates.png

Test the Archive Process

Determine how many rows will be archived using a query (this one is for an Oracle database). This will be very similar to your archive query so you can copy the SQL from that but replace the select fields with a count(*) like this:

SELECT count(*)
FROM PSACCESSLOG A
WHERE A.LOGINDTTM < TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD') - 30;

Navigate to: PeopleTools > Data Archive Manager > Archive Data to History

  1. Add a new Archive run control ID - ARCHIVE_PSACCESSLOG.
  2. Specify the archive template - ACCLOG.
  3. Leave the process type to selection as we will first be testing a straight copy from the base table to the history table.
  4. Specify the archive query as the selective query ARCHIVE_PSACCESSLOG
  5. Leave commit processing to Set-based Processing.
  6. Check the audit row count checkbox. This allows you to see the number of rows archived by your run in the audit archiving page.
  7. Save and run the process. This calls the data archive manager PSARCHIVE application engine program.
psaccesslog-archive-run-control-id.png

This is now going to go through and copy all the rows from the base table to the history table. So make sure you have built the history table first or PSARCHIVE will fail. This will vary in the amount of time required depending on the number of rows to be archived. I ran the process to copy just over 950,000 rows to the history table and this took about 1 minute. Obviously, results will vary.

After the process has run to success, query the contents of the history table. It should now have data in it.
Perform a count on your history table, you should have the same number of rows as the rows returned by the archive query (provided this is your first run). If you don't make sure you are using EXACTLY the same SQL to count the rows in the base table as what is in the View SQL tab of the archive PeopleSoft query (ARCHIVE_PSACCESSLOG).

You should also navigate to: PeopleTools > Data Archive Manager > Audit Archiving

Check that your archive worked and that the correct number of rows are displayed. The view details button shows you the SQL used by the PSARCHIVE application engine to perform the archive.

Test the Delete Process

If you are happy with the archive process, the next step is to test the delete from the base table. This will remove rows that have been archived to the history table from the base table. Normally you would not perform the delete in your production environment until you have completely tested the impact of removing archived rows (in the appropriate test environment).

Be careful with your delete process! As noted in the comments if the delete does not find a unique key match it may delete more rows than you expect. In certain situations, you may need to create your own delete query or another mechanism for deleting data that has been well tested.

Navigate to: PeopleTools > Data Archive Manager > Archive Data to History

  1. Add a new Archive run control ID - DELETE_PSACCESSLOG. Do not reuse the last run control as this can lead to confusion.
  2. Select the archive template - ACCLOG again
  3. Set the process type to Delete
  4. Use the batch number criteria and press the prompt on the Batch Number field. Select the appropriate batch number. If you have only run the archive once, then this will be batch number 1.
  5. Leave commit processing as set-based processing.
  6. Check the audit row count checkbox.
  7. Save and run the process.
psaccesslog-delete-run-control-id.png

This time, the process will be deleting rows from the base table. So it is best to run a process like this outside of normal hours to minimise the impact on day to day business (especially on high volume transactional tables).

Once the process has completed, run your archive query count on the base table for the rows that should now be deleted (SQL in previous step). This should return 0 rows. You should also check that your history table count has not changed. Also, just check how many rows remain in PSACCESSLOG and confirm with a quick scan that these are as expected (LOGINDTTM was up to 29 days ago).

Test the Rollback Process

Now that you have deleted the rows from the base table, you will want to test your rollback to ensure you can restore data from your history table to your base table.

Navigate to: PeopleTools > Data Archive Manager > Archive Data to History

  1. Add a new Archive run control ID - ROLLBACK_PSACCESSLOG. Do not reuse the last run control as this can lead to confusion.
  2. Select the archive template - ACCLOG again
  3. Set the process type to Rollback
  4. Use the batch number criteria and press the prompt on the Batch Number field. Select the appropriate batch number. If you have only run the archive once, then this will be batch number 1.
  5. Leave commit processing as set-based processing.
  6. Check the audit row count checkbox.
  7. Save and run the process.
psaccesslog-rollback-run-control-id.png

The rollback should have restored the rows in your base table. So perform a count on PSACCESSLOG and you should have a lot more rows nows.

You can write SQL to compare the keys in the base and history table to ensure every row was restored. Also, use the auditing archiving page to verify the number of rows restored.

Put it all into a Project

To migrate and manage your data archive process for the PSACCESSLOG table, you will want to put it all into an application designer project.

The following needs to be include in this project (the definition types and names are also provided):

  • The new history record (Record - PSACCESSLOG_HST)
  • The archive object (Archive Object Definitions - PSACCESSLOG)
  • The archive PeopleSoft query (Queries - ARCHIVE_PSACCESSLOG)
  • The arhicve template (Archive Templates Type 2 - ACCLOG)

These can all inserted into an application designer project using: Insert > Insert Definitions into Project
Check your upgrade tab to ensure you have all these definitions.

Comments

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License