PeopleTools 8.51 PeopleBook: Data Management

Using PeopleSoft Data Archive Manager

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

Access the Data Archive Manager homepage.
Manage archive objects.
Define archive queries.
Manage archive templates.
Archive data to history.
Audit archive processes.

Note. The Archive Data tool delivered with previous releases of PeopleTools is a deprecated feature, and has been replaced by this Data Archive Manager.

Understanding PeopleSoft Data Archive Manager

In any enterprise application, the ability to purge and archive transactional data is critical to data management. You need to have consistent methods to archive transactional data before your database increases to unmanageable sizes. PeopleSoft Data Archive Manager provides an integrated and consistent framework for archiving data from PeopleSoft applications.

Using a predefined template, you can select any queries and multiple objects that meet your archiving and restoration requirements. Leveraging the Archive Query in PeopleSoft Query, you can easily define your archive template.

To better manage the archive process, you don't have to make any commits to the database until the entire batch has completed.

PeopleSoft Data Archive Manager includes the following main elements:

Archive object definition.

An archive object is a collection of tables that you archive. The object definition determines how you archive data from a table. For base tables within an archive object, PeopleSoft Data Archive Manager archives data based on a user specified query. For non-base tables within an archive object, PeopleSoft Data Archive Manager archives data based on the archived data of the base table. This implementation eliminates the requirement of having query definitions for non-base tables.
Archive query definition.

PeopleSoft Data Archive Manager uses PeopleSoft Query to define selection criteria from the base table of the base archive object (for example, archive all rows in JRNL_HEADER where BUSINESS_UNIT = 'ABC01').
Archive template definition.

An archive template can contain multiple objects and multiple queries. One of the archive objects in the archive template must be a base object. You can simply define the selection criteria to archive from the base table without specifying criteria for all records in the archive template. Within the archive template, you must specify the AE processes to run before and after the data has been archived, for each of the archiving processes.
Archive job definition.

You define archive jobs to archive data to history. Before you submit an archive job, you must first define the archive job information including the Archive Template, Archive Process, and Commit Processing. You can submit archive jobs in a batch using the process scheduler. As part of the process, PeopleSoft Data Archive Manager prompts you for run time parameters such as bind variables and the query to use.
Restore query definition.

You define restore jobs to restore any subset of archived data to production tables. As with archive jobs, you must first define the job information, and then run the job using the process scheduler.
Archive auditing.

To facilitate auditing, PeopleSoft Data Archive Manager retains a record of the following:
- What process was executed.
- Who ran the batch process.
- When the process was executed.
- Which Archive ID and record was affected.
- What SQL statement was executed.

Understanding Archiving Strategy

This section discusses:

Archiving strategy.
History tables.

Archiving Strategy

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 describes the strategy for archiving to history table:

Use history tables for storing archived data.
Enable reporting and queries from history tables.
Must have a secondary step to delete archived data from online tables.
Must have additional database space.

The system is designed to provide as much flexibility as possible. By reviewing your business requirements, you will be able to determine which strategic step best fits your business needs.

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

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 copy data from the online tables into the history tables.
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.
When you no longer need to reference the data from the history tables, you can delete them completely from the system.

History 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. However, history records must include PSARCHIVE_SBR sub-record that contains the archive ID and batch number. 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 Application Designer.

History Table Considerations

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.

Building 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. PeopleSoft Data Archive Manager uses the PSARCHIVE_SBR sub-record that contains PSARCH_ID and PSARCH_BATCHNUM to denote when a piece of data was archived and to uniquely identify it.

The following example uses the record JRNL_HEADER.

To build a history table:

Open Application Designer.
Open the JRNL_HEADER table.
Select File, Save As and name the history table with an appropriate name, such as JRNL_HEADER_HST.
When prompted to copy the PeopleCode associated with the table, click No.
Select Insert, Sub-Record then insert the PSARCHIVE_SBR sub-record.
Save the record.
Build the table by selectingBuild, Current Object .
- Select the following build options: Create Tables and Create Indexes.
- Select the following build execute options: Execute and Build script.
- Click Build.

Understanding Archiving Techniques

This section discusses:

Business requirements analysis.
Commits.
Performance enhancement.
Index considerations.
Data 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. 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. Moving archived data 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

By default, the Archive Selection, Remove from History, Rollback, and Delete processes issue commits after each record has been processed unless Row-based processing or Unit-of-Work processing have been specified.

Enhancing Performance

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

Index Considerations

Index considerations include:

Database differences.
Non-unique indexes.

Platform Considerations

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 can't add other keys (PSARCH_ID and PSARCH_BATCHNUM from PSARCHIVE_SBR sub-record) to the corresponding history table.

To solve this problem, you can create the history table with the PSARCH_ID and PSARCH_BATCHNUM as non-key fields.

Non-Unique Indexes

The SQL generated by the Data Archive Manager assumes that index keys identify unique rows. All records that you intend to archive must have unique indexes. Records with non-unique indexes cannot be archived.

Data Limitations

For Oracle databases only, due to platform and meta-SQL restrictions, Data Archive Manager does not support archiving of records with LONG, IMAGE, or ATTACHMENT columns if you have not performed a data type switch. If you have performed a data type switch, there are no limitations. The selection process (inserting data from the online records to the history records) will result in the loss of the long, image, or attachment columns in the history record.

However, this restriction applies only to templates archived using set-based processing. Long, image, and attachment data are archived to history records (and back to the transactional records) if the template is archived using row-based processing.

Note. This potential limitation applies only to Oracle databases. No other databases are affected.

Accessing the Data Archive Manager Homepage

The Data Archive Manager homepage provides you with access to all of the functionality in PeopleSoft Data Archive Manager, including the Query Manager. Alternatively, you can select each menu item directly without accessing the homepage, with the exception of Query Manager.

Select PeopleTools, Data Archive Manager, Homepage to access the PeopleSoft Data Archive Manager homepage.

Manage Archive Objects

Click to access the Manage Archive Objects page, where you can define the objects to be archived. Each object is a logical grouping of records. The records specified in an archive object must be related by keys

See Managing Archive Objects.

Manage Archive Templates

Click to access the Manage Archive Templates page, where you can define an archive template. Archive templates define how data should be archived. Each archive template enables you to specify archive objects, archive queries, and application engine processes.

See Managing Archive Templates.

Archive Data to History

Click this link to access the Archive Data To History page where you can define a job to move data between transactional tables and history tables.

See Managing Archive Templates.

Audit Archiving

Click this link to access the Audit Archiving page where you can view the details of previous archive processes.

See Auditing Archive Processes.

Query Manager

Click this link to access the Query Manager page in PeopleSoft Query, where you can create a query for your archive process.

Managing Archive Objects

This section provides an overview of the base table and non-base tables, and discusses how to manage archive objects.

Understanding the Base Table and Non-base Tables

A base table is a table that contains all the keys by which all other tables in the archive object is archived from. Each archive object can have one and only one base table. You can define the selection criteria to archive from the base table.

Non-base tables are joined together by common keys. In each archive object, non-base tables are archived based on the archived data of the base tables. You don't need to define archive criteria for non-base tables.

Page Used to Manage Archive Objects

Page Name	Definition Name	Navigation	Usage
Manage Archive Objects	PSARCHOBJDEFN	PeopleTools, Data Archive Manager, Manage Archive Objects	Use this page to group archive records into archive objects.

Managing Archive Objects

Access the Manage Archive Objects page (PeopleTools, Data Archive Manager, Manage Archive Objects).

Archiving Record	Select the name of the record with the transactional data that you want to archive.
Base Record	Select this check box if the record that you select is the base record of this archive object. By definition, there can only be one base record per archive object.
History Record	Select the history record to which you want to archive the transactional data. You must first create the history record manually using Application Designer. An error message will appear if the history table has been defined incorrectly.

Defining Archive and Restore Queries

You can use PeopleSoft Query to define selection criteria to archive data from transactional tables to history tables. Each of the queries to be used by the Data Archive Manager must be defined as an Archive type or Restore type.

For an archive query, you must also select Public as owner. The first record of the archive query must be the same as the base table of the base record of the archive template. Otherwise, an error message appears.

A restore query is a type of archive query that is based on the history table rather than the online table.

See PeopleTools 8.51 PeopleBook: PeopleSoft Query.

Managing Archive Templates

This section discusses how to manage archive templates.

Page Used to Manage Archive Templates

Page Name	Definition Name	Navigation	Usage
Manage Archive Templates	PSARCHTEMPDEFN	PeopleTools, Data Archive Manager, Manage Archive Templates	Use this page to define the archive template.

Managing Archive Templates

Access the Manage Archive Templates page (PeopleTools, Data Archive Manager, Manage Archive Templates).

Archive Template Objects

Base Object	Select this check box if the archive object that you select is the base object of this archive template. Be definition, there can only be one base object per archive template. Data from tables in non-base objects are archived based on archived data from the link table in the base object.
Archive Object	Insert from the list of archive objects previously defined in the database.
Description	Displays the description of the query.
Link Record	If the archive object is not a base object, a link record must be defined. Similar to the concept of a foreign key constraint, the link table is used to “link” data between the base record of the non-base objects to archived data of any record in the base object. By this definition, only records that are defined in the base object of the archive template can be used as link records.

Queries Run on Archive Objects

Query Name	Select from a list of queries defined in the template. The selection determines how PeopleSoft Data Archive Manager will generate the where clause for the base table of the base object at runtime. Only queries of the type Archive or Restore can be defined in the Archive Template. You can insert multiple archive or restore queries into the template.
Description	Displays the description of the query.

Queries Run on Restore Objects

Query Name	Select from a list of queries defined in the template. The selection determines how PeopleSoft Data Archive Manager will generate the where clause for the base table of the base object at runtime. Only queries of the type Archive or Restore can be defined in the Archive Template. You can insert multiple archive or restore queries into the template.
Description	Displays the description of the query.

AE Processes

Archive Process

Specify a PeopleSoft Application Engine archive process. Valid options are:

Archive Selection
Archive Delete
Archive Rollback
Remove from History

You can define different Application Engine (AE) programs to run for each of the archiving processes. For example, you can define an Application Engine program called SEL_PRE that creates summary data in a work table before the Archive Selection process (Pre-AE) is executed. If you perform a rollback, you might want to create an Application Engine program called RBK_POST that executes after the Archive Rollback process (Post-AE) to remove the summary data in the work table.

Pre AE Program

Select the custom Application Engine program that you want to run against your data before archiving.

Post AE Program

Select the custom Application Engine programs that you want to run against your data after archiving.

Managing Archive Jobs

This section discusses how to:

Define archive jobs.
View details.
Define archive query binds.

Pages Used to Manage Archive Jobs

Page Name	Definition Name	Navigation	Usage
Archive Data To History	PSARCHRUNCTRL	PeopleTools, Data Archive Manager, Archive Data to History	Use this page to submit batch Application Engine jobs through Process Scheduler.
Archive Run Control Details	PSARCHEXAMRUNCNTL	PeopleTools, Data Archive Manager, Archive Data to History, View Details	Use this page to view the details of the data
Define Query Bind Variables	PSARCHRUNQRYBND	PeopleTools, Data Archive Manager, Archive Data to History, Define Binds	If you've defined prompts for the selection query that you use for the job, use this page to define the archive query bind variables for your archiving process.

Defining Archive Jobs

Access the Archive Data To History page (PeopleTools, Data Archive Manager, Archive Data to History).

Archive Template

Select the archive template to use for this batch job. Choosing a restore or an archive template is the way you choose the type of job to run.

Run

Click to run this batch job after defining the archive process and commit processing.

View Details

Click to access the Archive Run Cntl Details page to view the SQL and row counts of this batch job.

Note. If you're using bind variables, you must save the run control data before clicking the View Details link.

See Viewing Details.

Archive Process

Use this section to manage the processes that are associated with the selected archive template. As the archiving process runs, counters are inserted into work tables to indicate which records have been processed (for both set-based and row-based operations) and the number of rows processed (for row-based operations only).

For commits by table, the database server commits only after each record is processed. If the process fails in the middle of processing a record (say, the database logs were full), it will perform a rollback of everything that has not committed.

For commits by row, if the process fails for any reason, the counters keep track of only those rows that have been committed to the database. When the Application Engine job is restarted, it skips all the rows that have been committed, and begins with the first uncommitted row.

Process Type	Select an option: Select Delete to delete data from transaction tables. Data rows will be deleted from the transaction tables only if they've already been archived in the history tables. Select Rollback to copy data from history tables back to transaction tables. Important! History rows have the same keys as their corresponding transaction rows, so attempting to copy them to the transaction tables will fail with a duplicate key error if the transaction rows still exist. Before running a rollback process for a given archive job, you must first run a delete process to delete the transaction rows for the same job, so that the history rows can be successfully copied into the transaction tables. Select Remove from History to delete data from the history tables.
Selective Query	Specify the archive or restore query defined within the archive template to use at run time. If there are bind variables, you will be prompted to enter the bind variables when you click the Define Binds link.
Define Binds	Click to access the Define Query Bind Variables page. See Defining Archive Query Binds.
Batch Number	For archiving processes that are based on data in the history tables (such as delete data from transactional tables, copy data from history tables to transactional tables, and delete data from history tables), you will be prompted to enter an Archive Batch Number.
Batch Number	For archiving processes that are based on data in the history tables (such as delete data from transactional tables, copy data from history tables to transactional tables, and delete data from history tables), you will be prompted to enter an Archive Batch Number.
Audit Row Count	Select to audit the number of rows in the record that meet the criteria. This number is displayed in the Number of Rows field on the Audit Archiving page.

Commit Processing

By default, batch processing is performed by the Data Archive Manager using set-based processing. Unless specified using the check boxes below, a commit is issued to the database after each table is processed within the Archive Template

Commit at End	Data Archive Manager processes data using set-based processing, but doesn't issue any commits to the database server until the entire process has completed. For example, if your Archive Template is defined with Pre- and Post- AE programs, the Data Archive Manager will first execute the Pre-AE program, then it will process all of the tables in the Archive Template, then it will execute the Post-AE program. Upon successful execution of all these steps, a commit will be issued to the database. When you select this option, the set-based processing option is automatically selected as well.
Commit by Table	Data is processed by passing a single SQL statement per record to be archived to the database server. A commit is issued to the database server after successful completion of each SQL statement.
Commit by Row	Data Archive Manager processes data one row at a time using PeopleCode fetches. This method of archiving is more memory intensive and takes longer than set-based processing. However, for archiving processes that contain significant amounts of data, row-based processing could be used to reduce adverse affects on the database server Row-based processing is appropriate when you're archiving large amounts of data from transactional tables and wish to issue commits more frequently. If you select this option, you must enter a commit frequency.
Commit Frequency	Specify the number of rows to process before issuing a commit to the database.

Viewing Details

Access the Archive Run Control Details page (PeopleTools, Data Archive Manager, Archive Data to History, View Details).

View SQL

Select to view the archive selection SQL for the archive object. The View Details page appears, with a text box containing the SQL, for example:

%InsertSelect(CONF_OB2_PARENT, CONF_OB2_PAR_HS)
 FROM PS_CONF_OB2_PAR_HS WHERE
 PSARCH_ID = 'CONFDEMO' AND PSARCH_BATCHNUM = 1

Count Rows

Select to view the number of rows of the archive object that the archiving process will affect in the related database. The View Details page appears, with a description of the number of rows that will be processed by Data Archive Manager.

Defining Archive Query Binds

Access the Define Query Bind Variables page (PeopleTools, Data Archive Manager, Archive Data to History, Define Binds).

Click the Reset Query Bind Variables button, and a prompt page appears where you can enter the new query bind values. The prompt page appears only if you have defined prompts for the selection query that you use for the job. When you enter the query bind values and click OK, they appear as read-only information on the Define Query Bind Variables page. Click OK to return to the Archive Data To History page.

Auditing Archive Processes

This section discusses how to audit the details of previous archiving processes:

Page Used to Audit Archive Processes

Page Name	Definition Name	Navigation	Usage
Audit Archiving	PSARCHIVEAUDIT	PeopleTools, Data Archive Manager, Audit Archiving	Use this page to view details of previous archiving processes.

Audit Archiving

Access the Audit Archiving page (PeopleTools, Data Archive Manager, Audit Archiving).

User ID	Select which user to audit.
Archive ID	Select an existing archive ID to audit.
From Date	Select a start date for the audit.
To Date	Select an ending date for the audit.
Search	Click this button to have the system create the audit report and display the appropriate fields on the page.
Delete	Click this button to purge audited rows based on the criteria specified.
Archive ID	Select an existing archive ID.
Event Date/Time	Displays the date and time that corresponds to the date when the data was archived for that particular archive number.
Archive Process	Displays the archive process you want to run.
Archive Batch Number	Displays the batch number of the archive process.
Record (Table) Name	Displays the name of the table that you want to archive.
Number of Rows	Displays the number of rows to be archived. Note. This field displays valid information only if you selected Audit Row Count on the Archive Data to History page.
User ID	Displays the user ID that you want to audit.
Run Control ID	A unique ID to associate each user with his or her own run control table entries.
Process Instance	A unique number that identifies each process request. This value is automatically incremented and assigned to each requested process when the process is submitted to run.
View Details	Click this button to view the SQL detail of previous archiving processes.

Manage Archive Objects	Click to access the Manage Archive Objects page, where you can define the objects to be archived. Each object is a logical grouping of records. The records specified in an archive object must be related by keys See Managing Archive Objects.
Manage Archive Templates	Click to access the Manage Archive Templates page, where you can define an archive template. Archive templates define how data should be archived. Each archive template enables you to specify archive objects, archive queries, and application engine processes. See Managing Archive Templates.
Archive Data to History	Click this link to access the Archive Data To History page where you can define a job to move data between transactional tables and history tables. See Managing Archive Templates.
Audit Archiving	Click this link to access the Audit Archiving page where you can view the details of previous archive processes. See Auditing Archive Processes.
Query Manager	Click this link to access the Query Manager page in PeopleSoft Query, where you can create a query for your archive process.