Appendix: Archiving Data (Deprecated)

This chapter provides overviews of PeopleSoft Data Archive Manager, archiving strategies, and archiving techniques, and discusses how to:

• Create and design archive templates.

• Work with the archives.

• Work with archive data.

• Run data archival processes.

• Run data archival reports and audits.

• Perform additional archival procedures.

Note. The Data Archive Manager documented in this chapter is a deprecated feature used in PeopleSoft 8.40 through 8.43. It has been replaced in the current release by a new Data Archive Manager, which is documented in this PeopleBook. New archives should be built using the current tool.

See Also

Using PeopleSoft Data Archive Manager

Understanding PeopleSoft Data Archive Manager

PeopleSoft applications create and maintain data. Often the data in online tables is no longer required, but you cannot simply delete the data to make room for new data. Managing the historical data is a time-consuming challenge for many database administrators. However, unless you develop an archiving strategy, databases increase to unmanageable sizes. Also, removing the historical data from online tables can improve overall performance.

PeopleSoft Data Archive Manager enables you to select the rows of data that you no longer need in the online system and move those rows into history and staging tables or into a flat file format. Maintaining the data in the system in history and staging tables keeps the data available for queries and reporting.

Also, you can archive data directly to a flat file for long-term storage if the historical data is no longer needed for reporting. You can export the data to flat files and delete it completely from the system.

Warning! PeopleSoft Data Archive Manager is a powerful tool. Improper use may result in data loss or corruption of the database. Only experienced database administrators who understand the archival process should use PeopleSoft Data Archive Manager.

Understanding Archiving Strategies

This section discusses:

• Types of strategies.

• History tables and staging tables.

• Flat files.

Types of Strategies

Determining an archiving strategy is essential for using PeopleSoft Data Archive Manager efficiently. This strategy depends on how the archived data will be used. The following table describes the two main archiving strategies.

The two strategies offer different approaches to archiving. If you are archiving to history tables, you must first create the history tables for temporary storage. If you are archiving to flat files, you can accomplish the task in one simple step. The system is designed to provide as much flexibility as possible. By reviewing your business requirements, you will be able to determine which strategy best fits your business needs.

Note. Before deciding which archiving strategy to use, review this entire chapter so that you are familiar with all of the archiving features.

History Tables and Staging Tables

Archiving to history tables involves using tables that you create for the sole purpose of storing archived data. You must determine whether the archived data should be stored in the history tables temporarily or on a long-term basis.

By definition, history tables are identical copies of the online tables, except they have an additional column, PSARCH_ID. The system uses this key field to denote when a piece of data was archived and to uniquely identify it. Some PeopleSoft applications deliver history tables prebuilt for use in common archiving processes. If you design a custom archiving scheme, you need to create the history tables using PeopleSoft Application Designer.

Here is a high-level-overview of the steps:

1. You move data into the history tables.

   This is known as the selection process. This enables you to query the selected data for information and delete the data from the online tables.

2. If you accidentally delete the data from the online tables, there is a process to restore the data back from the history tables.

   This rollback process is the optional second step.

3. When you no longer need to reference the data from the history tables, you can export them to flat files and delete them completely from the system.

4. If necessary, you can return the archived data back to the system using staging tables.

   Staging tables are identical to online tables.

History Table Considerations

When archiving to history tables, it is important to consider the following points:

• History tables can reside on the same database as the online tables, or they can reside in separate databases.

  If the history tables reside in a separate database, you need to set up database links using the proprietary methods for the relational database management system.

• After the archive process moves the data into the history table, the data resides in both the online tables and in the history table; you then have two options:

  • Deleting the archived data from the online tables.

  • Leaving the archived rows in the online tables such that the data exists in parallel.

Procedure to Build History Tables

Before you run the archiving process, you must first create (or build) the history tables.

You must build one history table for each table to be archived. The history table must be identical to the archive table, with an extra column PSARCH_ID.

The following example uses the record JRNL_HEADER.

To build a history table:

1. Open PeopleSoft Application Designer.

2. Open the JRNL_HEADER table.

3. Select File, Save As and name the history table with an appropriate name, such as JRNL_HEADER_HST.

4. When prompted to copy the PeopleCode associated with the table, click No.

5. Select Insert, Field and insert the PSARCH_ID field.

6. Save the record.

7. Build the table by selecting Build, Current Object.

   • Select the following build options: Create Tables and Create Indexes.

   • Select the following build execute options: Execute and Build script.

   • Click Build.

Staging Table Considerations

Staging tables are used in the archiving process as a holding area for data. When using staging tables, consider the following:

• Staging tables are exact duplicates of the online tables.

  They do not contain the PSARCH_ID column.

• Staging tables are often used for reporting purposes.

Flat Files

You can also archive data directly from online tables to flat files. When you are ready to archive data and you no longer need to reference it from the online tables, you can extract the data to flat files.

The data is stored in the comma-separated values (CSV) format, which enables a portable representation of the online tables and data.

To archive to flat files:

1. Export the selected online rows to a CSV file.

2. If you need to access the archived data in the future, you can restore the data into staging tables.

Note. The data is deleted from the online tables as it is archived.

Understanding Archiving Techniques

This section discusses:

• Business requirements analysis.

• Commits.

• Performance enhancement.

• Index limitations.

Business Requirements Analysis

It is important to devise a business strategy before archiving the data. First, you must identify the tables that you want to archive. This includes identifying all of the parent and child tables associated with the tables. Failing to identify all of the related tables can cause corruption to the database. Next, you must know exactly which data to archive. It is important to recognize which rows are safe to remove from the online tables. Remember to remove only the data that is not required to maintain the day-to-day business and reporting.

Consider PeopleSoft General Ledger as an example. General Ledger contains the greatest amount of data to be archived because it is the module where the majority of reporting is required. There are two sets of data types that need to be maintained: balance information and transactional information. Balance information is retained in the ledger records. You might require balance information for online and reporting purposes to be available for a three-year period. On the other hand, transactional data is maintained in the journal header and line tables. Suppose that you require only one year of transactional data to be retained in the system for online purposes, but three years to be retained for reporting purposes.

Any data beyond the above time frames for balances and transactions can be archived and is only be accessed through reports. The data can be archived to history tables or as flat files to secondary storage devices. If data were to be archived into history tables, the data would still be available online for reporting purposes. However, you could not view it through standard PeopleSoft Internet Architecture pages without special configuration. In addition, reports would need to be modified to access the data in history tables. Archiving to secondary storage devices is generally used for long-term data retention. This option is preferred for data that is rarely retrieved, and secondary storage devices are usually used to satisfy legal requirements.

Commits

For both batch and online execution, the Archive Selection, Remove from History, Rollback, and Delete processes issue commits after each record has been processed unless a commit level has been specified otherwise.

Performance Enhancement

For better performance and increased speed during archiving processes, try dropping the indexes before inserting data from online tables into history tables.

Index Limitations

The database platform may have a limitation on the number of columns that an index can contain. Some have a restriction of 16 columns for an index. If the table that you want to archive already has 16 keys, then you cannot add another key (PSARCH_ID) to the corresponding history table.

To solve this problem, you can use the flat file archiving strategy. Alternatively, you can create the history table with the PSARCH_ID as a non-key field. For this situation, it is recommended that you either have different history tables for different archiving projects, or that you purge the history tables before executing the selection process of another archiving project.

Creating and Designing Archive Templates

This section discusses how to:

• Specify fields and archival criteria.

• Join record criteria.

• Generate and edit Structured Query Language (SQL).

Pages Used to Create and Design Archive Templates

Specifying Fields and Archival Criteria

Access the Record Criteria page.

The process of archiving data begins with the creation of an archiving template, which logically groups all of the online tables that are to be archived into a single entity. You associate the online table with its history table counterpart, and you select the fields to archive and the criteria by which to archive.

Joining Record Criteria

Access the Join Record Criteria page.

If there are dependencies from other tables in the archiving template, such as parent-child relationships or joining against reference tables, you must include the criteria on this page. This can also be done by selecting the Copy Parent Record check box. For this to work correctly, the parent table criteria must already exist on the Record Criteria page. You can specify multiple levels, such as grand-parent-to-parent, grand-parent-to-parent-to-child, and so on.

Generating and Editing SQL

Access the SQL Designer page.

The SQL Designer page is useful for generating and editing the SQL that will be used to perform the archive process. In addition, you can count the number of rows that will be affected by the current archive process and check for duplicate rows that the SQL is affecting. To access this page, you must have entered basic information on the Record Criteria page and the Join Record Criteria page.

Note. The buttons that appear on this page depend on your security access privileges and the current archive setting. To set security access to the page, access the Archive Security page.

Working With the Archives

This section discusses how to:

• Grant access rights.

• Administer archive projects.

Pages Used to Work with the Archives

Granting Access Rights

Access the Archive Security page.

This page enables you to grant access rights to the permission lists that use PeopleSoft Data Archive Manager.

The permission lists that you add need to exist already in PeopleTools Security. The permission list must be the primary permission list for the user profile in order for the user to have access to the command button.

Note. PeopleTools delivers a process group ARCHALL that should be added to the appropriate permission list using PeopleTools Security. You cannot run any archive processes until this group is added to the permissions list.

Administering Archive Projects

Access the Archive Utilities page.

The Archive Utilities page is used for archive project administration. The administrative operations include copying, renaming, deleting, exporting and importing.

Select the action that you want to perform. The instructions for each action appears in the text box to the right.

Working With Archive Data

This section discusses how to:

• Find data that meets your criteria.

• Create scripts to move data.

• View and edit scripts.

Pages Used to Work with Archive Data

Finding Data That Meets Your Criteria

Access the Find Data page.

This page enables you to find data in the online system that meets your criteria. Once you have located the data, you can then immediately create a new archive project.

Creating Scripts to Move Data

Access the Data Transfer Input page.

The Data Transfer Input page enables you to search for specific fields in the database and create PeopleSoft Data Mover export and import scripts to move the data between databases. For example, this can be used to generate Data Mover scripts that can export data from the production database into a training or test database.

Note. The generated PeopleSoft Data Mover script appears in a text box on the Data Transfer Output page.

Viewing and Editing Scripts

Access the Data Transfer Output page.

The Data Transfer Output page contains two PeopleSoft Data Mover scripts: one to export the data and one to import the data.

To run the script using PeopleSoft Data Mover, you need to copy the generated script to a text editor (Notepad, for example) and then save it as a Data Mover script (DMS) file.

Running Data Archival Processes

This section discusses how to:

• Begin the archiving process.

• Export data from online tables to flat files.

• Export data from history tables to flat files.

• Restore archived data using staging tables.

Pages Used to Run Data Archival Processes

Beginning the Archiving Process

Access the Archive Data page.

Once you have created an archive template, you can begin the archiving process. This is when the system moves the selected data from your online tables.

Exporting Data From Online Tables to Flat Files

Access the Archive Online to Flat Files page.

Note. This archive process deletes the data from the online tables immediately after the system has exported it to the flat files.

Exporting Data From History Tables to Flat Files

Access the Export History to Flat Files page.

Run	Click to export the data in the history table to the designated flat file.
Archive ID	Select an existing archive ID.
Directory to store flat files	Enter the path for the directory in which to store the resulting flat files. When you click Auto Fill Records, the path is added as a prefix to each of the resulting the file names.
Auto Fill Records	Click to display the table names. In addition, you must specify the path where the data is exported.
Table Name	Displays the name of the table that is processed. This field is populated by clicking the Auto Fill Records button.
File Path	Displays the path and file names to which the data for each table is written. If necessary, you can manually enter the file path so that a table has a unique file name and location.

Restoring Archived Data Using Staging Tables

Access the Import From Flat Files page.

Run	Click to import the data from the flat file into the designated staging table.
Number of rows to be processed before commit to DB	Enter the number of rows that the program processes before it issues a COMMIT.
File Path	Enter the path and the name of the file containing the flat file that you want to import into the database.
Table Name	Enter the staging table name where the program inserts the data from the import file.

Running Data Archival Reports and Audits

This section provides an overview of archival reports and audits and discusses how to:

• Run archive reports.

• Create an audit anquiry.

• View audit results.

Understanding Archive Reports and Audits

Before running the archive process, you should generate reports to verify the data that you are archiving before deleting it from the online tables. This report lists the archive ID definitions, which consist of the following:

• Archiving tables.

• Selection criteria.

• Dependency criteria (criteria from other tables).

• SQL that will run each of the archiving processes.

These definitions also help you create your own reports through PeopleSoft Query.

The Audit Report page generates report for a specific archive ID and places the file in the destination that you provide.

Pages Used to Run Data Archival Reports and Audits

Running Archive Reports

Access the Archive Report page.

Archive ID	Select the archive ID to audit.
Go to Project Page	Click to access the Archive Designer pages.
File Path	Enter the path where the generated report is saved on the batch server.

Creating an Audit Inquiry

Access the Audit Inquire page.

The Audit Inquire page is useful for online viewing of all processes that have been executed in the Data Archiving tool without waiting for the output from the batch process. In addition, you can delete the audit if you have the necessary access privileges.

Add the criteria in the edit boxes, click View Audit, and view the results arranged by column.

Viewing Audit Results

Access the Audit Report page.

The Audit Report page creates a batch output file showing all processes that have been run in the Data Archiving tool. In addition, you can print the resulting SQL.

Performing Additional Archival Procedures

This section discusses how to:

• Archive from online tables to history tables.

• Roll back history table data.

• Archive from history tables to flat files.

• Restore archived data from flat files.

• Understand commits.

• Gain increased performance.

• Modify indexes.

Archiving from Online Tables to History Tables

Use this procedure to copy the data from the online table to the history tables using the selection process and then remove the data from the online tables using the delete process.

To complete the online-to-history-table process:

1. Create an archive project.

   Create a project (with a unique archive ID) that identifies the tables or rows that are to be archived.

2. Run archiving reports.

   Print an archive project definition report to verify the data that you have selected to archive.

3. Run archive data with the Archive Selection process.

   Based on the archive ID, run the selection process. This copies the data rows for archiving to the history tables.

4. Run archive data with the Delete process.

   This removes the data from the online tables. Do this only when you are confident that the archived data has been successfully stored in the history tables.

Rolling Back History Table Data

To roll back history table data:

1. Run archive data with the Rollback process.

   This copies the data back from the history tables to the online tables. You use this when the data has been accidentally deleted from the online tables, and it has already been archived to the history tables.

2. Run archive data with the Remove from History process.

   If the Archive Selection process has been run, and it selected the wrong set of data to be copied to history tables, run the Remove from History process to remove the misplaced data. This returns you to the state where you started before the Archive Selection process.

Archiving From History Tables to Flat Files

To archive from a history table to a flat file:

1. Run the Export History to Flat Files process.

   This process copies the data to the flat files.

2. Run archive data with the Remove from History process.

   This deletes the data from the history tables. Only run this process after you have verified that the data has been successfully archived to flat files.

Restoring Archived Data From Flat Files

To restore archived data from flat files:

1. Run the Archive Audit report for the Batch History process.

   This report lists the definition of the history tables at the time the data was archived. This report provides the names of the flat files that contain the data.

2. Create staging tables.

   The staging table is a clone of the online table. The Import process imports the data from the flat files into staging tables.

3. Run the Import for Flat Files process.

4. Run the programs to populate the tables from the flat files.

Understanding Commits

For both batch and online execution, the Archive Selection, Remove from History, Rollback, and Delete Processes issue commits after each record has been processed unless a commit level has been specified otherwise.

Gaining Increased Performance

For better performance and increased speed during archiving processes, try dropping the indexes before inserting data from online tables into history tables.

Modifying Indexes

Your database platform may have a limitation to the number of columns an index can contain. Some have a restriction of 16 columns for an index. If the table that you want to archive already has 16 keys, then you cannot add another key (PSARCH_ID) to the corresponding history table.

The first option for solving this situation is to use the Flat File Archiving Strategy. The second option for solving this situation is to create the history table with the PSARCH_ID as a non-key field. For this situation, it is recommended that you either have different history tables for different archiving projects, or you have a strategy of purging the history tables prior to executing the selection process of another archiving project.