PeopleTools 8.51 PeopleBook: Data Management

Appendix: Archive Data Tool (Deprecated in PeopleTools 8.44)

The Archive Data tool, provided in PeopleTools 8.40-8.43, was deprecated in PeopleTools 8.44. The pages associated with the Archive Data tool are still provided with the current release of PeopleTools for upgrade compatibility, and they appear under PeopleTools, Archive Data. Do not use these pages to create or maintain data archives in the current PeopleTools release.

The deprecated Archive Data tool is superseded by the current Data Archive Manager. Any new archives, should be created and maintained with the Data Archive Manager, which is documented within this PeopleBook. Access the Data Archive Manager pages by selecting PeopleTools, Data Archive Manager.

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

Page Name	Definition Name	Navigation	Usage
Record Criteria	ARCH_PROJ	PeopleTools, Archive Data, Archive Designer, Record Criteria	Use this page to specify fields and archival criteria.
Join Record Criteria	ARCH_OTH_CTRL	PeopleTools, Archive Data, Archive Designer, Join Record Criteria	Use this page to join the record criteria.
SQL Designer	ARCH_SQL	PeopleTools, Archive Data, Archive Designer, SQL Designer	Use this page to generate and edit the SQL that will be used to perform the archive process.

Specifying Fields and Archival Criteria

Access the Record Criteria page (PeopleTools, Archive Data, Archive Designer, Record Criteria).

Warning! This page is associated with the deprecated Archive Data tool and should not be used to create or administer data archives. Use the Data Archive Manager (PeopleTools, Data Archive Manager) to create and administer data archives.

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.

Archive ID	Displays the ID for a group of transactions that comprise an archive definition during the archiving process.
Description	Enter a description. Use up to 30 characters to describe the archive.
Archive to Flat File	Select to archive the project directly to a flat file without having to create history tables.
Copy Archive ID	Click to copy the current archive project to a new archive ID. All tables, criteria, and other criteria are copied to the new archive ID.
Archiving Record	Select the online tables to be archived. You can archive multiple online tables within one archive ID.
History Record	Enter the name of the table where the archived data will be stored.
Copy Table	Click to copy all criteria to a new row in the existing archive ID. This button is useful when handling multiple tables.
Go to Request Page	Click to access the Archiving Process page.
Go to Report Page	Click to access the Report Request page.
FieldName	Enter columns in the online tables to specify archive criteria. Specifying the fields and adding the conditions is comparable to the WHERE clause in a SQL statement.
Operator	Select an operator. Options are =, <>, <, >, <=, >=, LIKE, and NOT LIKE.
Value to Match	Enter a column value to match against, as in 07/01/1999 or $75,000. You can also use special parameter markers in the format of %PSPARMnn% where nn can be any number. For example, valid parameter markers could be %PSPARM1% or %PSPARM18%. When the system generates the SQL statement, %PSPARMnn% is embedded into the SQL statement and substituted with values entered using the run control pages. For example, you can create an archive project based on a business unit and then enter the actual business unit at run time. Note. Parameter markers are currently not implemented with DATE fields.
A/O	Click to specify AND or OR. This button is only visible if you add multiple lines to the field list.

Joining Record Criteria

Access the Join Record Criteria page (PeopleTools, Archive Data, Archive Designer, Join Record Criteria).

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.

Archiving Record	Displays the table to be archived.
Copy Parent Record	Select to enable the criteria that exist in the parent record on the Record Criteria page to be copied to the Join Record Criteria page. When you select this check box, an edit box appears for you to select the parent table.
Record Name	Enter the name of the table to be joined. You can request multiple table joins per archiving table. The two tables must share common keys.
Field Name	Enter the columns of the online tables to add to the archive criteria.
Operator	Select an operator. Options are =, <>, <, >, <=, >=, LIKE, and NOT LIKE.
Value to Match	Enter a column value to match.
A/O	Click to select AND or OR.

Generating and Editing SQL

Access the SQL Designer page (PeopleTools, Archive Data, Archive Designer, SQL Designer).

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.

Generate Project SQL	Click to create all SQL statements for the entire archive template.
Generate Record SQL	Click to produce the SQL statements for the current record. The following types of SQL are created: Delete from the online tables (Archive Delete process). Remove data from history tables (Remove from History process). Roll back (Archive Rollback process). Create SELECT that moves rows from the online table to the history table (Archive Selection process).
Archive Process	Displays the processes that have been selected on the Archive Data page.
Count Rows	Click to view the row count that the generated SQL will affect.
Chk Dup Rows (check duplicate rows)	Click to see if an incorrect join will cause duplicate rows to be archived.
Edit SQL	Click to modify the generated SQL. If you edit and save the SQL, a flag is used to indicate that the SQL is user-modified and is not system-generated. When you modify the SQL and save it, the text above the edit box indicates that the SQL has been altered from the original, system-generated SQL.
Run SQL	Click to run the generated SQL. Typically, this button used by the archive developer during the development and testing of the archive. After the archive template is developed, a PeopleSoft Application Engine program runs the SQL in batch.

Working With the Archives

This section discusses how to:

Grant access rights.
Administer archive projects.

Pages Used to Work with the Archives

Page Name	Definition Name	Navigation	Usage
Archive Security	ARCH_SECURITY	PeopleTools, Archive Data, Archive Security	Use this page to grant access rights to the permission lists that use PeopleSoft Archive Data Tool.
Archive Utilities	ARCH_UTILS	PeopleTools, Archive Data, Archive Utilities	Use this page to perform administrative tasks associated with the archive projects.

Granting Access Rights

Access the Archive Security page (PeopleTools, Archive Data, Archive Security).

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

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.

Permission List	Select the permission lists to which you grant archive data access.
Can Generate SQL?	Select to enable the user to generate SQL on the SQL Designer pages by activating the Generate SQL button.
Can Edit SQL?	Select to enable the user to edit the SQL on the SQL Designer page.
Can Run SQL?	Select to enable the user to run SQL on the SQL Designer page.
Can Purge Audit?	Select to enable the user to purge the audit history on the Archiving Audit page.

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 (PeopleTools, Archive Data, Archive Utilities).

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 appear in the text box to the right.

Copy Archive Project	Select to Create a copy of the archive template that you specify in the Archive ID field and give it the name that you enter in the New Archive ID field.
Rename Archive ID	Select to rename the archive according to the values that you enter in the Archive ID and New Archive ID fields.
Delete Archive ID	Select to delete the archive that you specify in the Archive ID field. Once an archive is deleted, you can no longer access it.
Generate DM Script	Select to generate both the import and the export PeopleSoft Data Mover scripts for the archive. You can also export all projects by selecting the corresponding check box.

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

Page Name	Definition Name	Navigation	Usage
Find Data	DATA_FIND_INPUT	PeopleTools, Archive Data, Find Data	Use this page to find data in the online system that meets your criteria for your archive project.
Data Transfer Input	DATA_TRANS_INPUT	PeopleTools, Archive Data, Transfer Data, Data Transfer Input	Use this page to search for specific fields in the database and create PeopleSoft Data Mover export and import scripts to move the data between databases.
Data Transfer Output	DATA_TRANS_OUTPUT	PeopleTools, Archive Data, Transfer Data, Data Transfer Output	Use this page to export and import data by running the PeopleSoft Data Mover scripts.

Finding Data That Meets Your Criteria

Access the Find Data page (PeopleTools, Archive Data, Find Data).

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.

Field Name	Enter the name of the field that you want to find a match for.
Match Type	Specify whether the match between the field value and the match value should be equal or like. Options are = and LIKE.
Value to Match	Enter the value for the system to search for within the specified field name.
Find Data	After you have entered the desired criteria, click Find Data for the system to begin searching the online data.
Record	Displays the record containing the rows that meet the criteria.
Row Count	Displays the number of rows in the record that meet the criteria.
Key?	Indicates whether the field is a key field in the record.
Build?	Click to include the record in the generated archiving project. This check box is automatically selected if the field is a key field.

Build Archive Project

Select to create a new archive project. After entering the name and description of the project, click Go to display the Archive Designer pages that enable you to create the new archive.

Creating Scripts to Move Data

Access the Data Transfer Input page (PeopleTools, Archive Data, Transfer Data, Data Transfer Input).

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.

Field Name	Enter the name of the field for which you want to find a match.
Match Type	Select the type of match between the field value and the match value. Options are = and LIKE.
Value to Match	Enter the value for the system to search for within the specified field name.
Find Data	Click to search for the values that you've specified.
Record	Displays the record containing the rows that meet the criteria.
Row Count	Displays the number of rows in the record that meet the criteria.
Key?	Indicates whether the field is a key field in the record.
Build?	Click to include this record in the generated script. This check box is automatically selected if the field is a key field.
Create Data Mover Export Script	Select to enable the system to create a PeopleSoft Data Mover export script.
Data Mover File Path	Enter the file path of the PeopleSoft Data Mover files in the generated script.
Data Mover Export File Name	Enter the export file name in the generated script.
Data Mover Import File Name	Enter the import file name in the generated script.
Delete Before Import	If this check box is selected, the generated script includes a DELETE statement for the user-specified criteria in the WHERE clause. The DELETE statement appears before the Data Mover IMPORT statement.

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 (PeopleTools, Archive Data, Transfer Data, Data Transfer Output).

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

Page Name	Definition Name	Navigation	Usage
Archive Data	ARCH_RQST	PeopleTools, Archive Data, Archive Data	Use this page to archive the selected data from your online tables.
Archive Online to Flat Files	ARCH_FLT_RQST	PeopleTools, Archive Data, Archive Online to Flat Files	Use this page to export data from online tables to flat files.
Export History to Flat Files	ARCH_HST_RQST	PeopleTools, Archive Data, Export History to Flat Files	Use this page to export data from history tables to flat files.
Import from Flat Files	ARCH_IMP_RQST	PeopleTools, Archive Data, Import from Flat Files	Use this page to restore archived data using staging tables.

Beginning the Archiving Process

Access the Archive Data page (PeopleTools, Archive Data, Archive Data).

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.

Archive ID	Select an existing archive ID.
Auto Fill Records	Click to display all the tables to be included in the archive project.
Go to Project Page	Click to access the Archive Designer pages.
Archive Process	Select the archive process to run. Options are: Selection: Copy the data from the online tables to the history tables. Rollback: Copy the data from the history tables to the online tables. Delete: Remove the data from the online tables, but only when they have already been copied to the history tables. Remove from History: Delete the data from the history tables.
Commit Processing	Select to enable the Commit After option, which specifies how many rows of data the system processes before issuing a database commit. Otherwise, the system issues a commit after each record has been processed.
Pre/Post AE Processing (pre/post Application Engine processing)	If you have any custom PeopleSoft Application Engine programs that you want to run against the data, either before or after archiving, specify the appropriate program here. The available fields are: Pre AE: Select an existing Application Engine program to run before the archiving process. Post AE: Select an existing Application Engine program to run after the archiving process.
Do	Select to indicate whether the table is processed at run time
Table Names	Displays the tables containing the data to be archived.
History Tables	Displays the tables where the system stores the archived data.
Run Time Parameters	If your archive template contains runtime parameters (%PSPARMnn%), specify a value that the system substitutes in the SQL statement at run time.

Exporting Data From Online Tables to Flat Files

Access the Archive Online to Flat Files page (PeopleTools, Archive Data, Archive Online to Flat Files).

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

Run	Click to export the data to the flat files.
Archive ID	Select an existing archive ID.
Directory to store flat files	Enter the path for the directory in which you want to store the resulting flat files. When you click Auto Fill Records, the directory is added as a prefix to each of the resulting file names.
Auto Fill Records	Once you've selected an archive ID, click to display the table names associated with the archive. In addition, you must specify the path where the data will be exported.
Go to Project Page	Click to access the Archive Designer pages.
Pre/Post AE Processing (pre/post Application Engine processing)	If you have any custom PeopleSoft Application Engine programs that you want to run against your data either before or after archiving, specify the appropriate program here. The available fields are: Pre AE: Select an existing Application Engine program to run before the archiving process. Post AE: Select an existing Application Engine program to run after the archiving process.
Do	Select to specify whether to process this table.
File Path	Displays path and file names to which the data for each table will be written. If necessary, you can manually enter the file path so that a table has unique file name and location.
Run Time Parameters	If the archive process contains the runtime parameter markers (%PSPARMnn%), specify a value that the system substitutes in the SQL statement at run time.

Exporting Data From History Tables to Flat Files

Access the Export History to Flat Files page (PeopleTools, Archive Data, Export History to Flat Files).

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 (PeopleTools, Archive Data, Import from Flat Files).

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

Page Name	Definition Name	Navigation	Usage
Archive Report	ARCH_RPT	PeopleTools, Archive Data, Archive Report	Use this page to run archive reports.
Audit Inquire	ARCH_AUDIT_INQ	PeopleTools, Archive Data, Audit Archiving, Audit Inquire	Use this page to view all online processes that have been executed in the Data Archiving tool without waiting for the output from the batch process. You can also delete the audit if you have the necessary access privileges.
Audit Report	ARCH_AUDIT_RPT	PeopleTools, Archive Data, Audit Report	Use this page to view all processes that have been run in the Data Archiving tool. You can also print the resulting SQL.

Running Archive Reports

Access the Archive Report page (PeopleTools, Archive Data, Archive Report).

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 (PeopleTools, Archive Data, Audit Archiving, Audit Inquire).

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.

User ID	Select which user to audit.
Archive ID	Select an existing archive ID.
Event	Select an archiving event from the list. You can select from all processes that have been run by Data Archiving Manager.
From Date	Select a start date for the audit
To	Select an ending date for the audit.
View Audit	Click to have the system create the audit report and display the appropriate fields on the page.
Purge Audit	If you have the correct security permission set up on the Audit Security page, you can click to purge an audit.
File Path	Enter the path where the generated report is saved on the batch server.
Detail	Click to show the details related to the audit events . For example, if you edited the SQL using the SQL Designer page, the Details page will show the original SQL as well as the modified SQL.

Viewing Audit Results

Access the Audit Report page (PeopleTools, Archive Data, Audit Report).

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.

Archive Operator Class	Select which user ID to audit.
Archive ID	Select an existing archive ID.
Event	Select an archiving event from the list. You can select from all processes that have been run by Data Archiving Manager.
From Date	Select a start date for the audit.
To	Select an ending date for the audit.
Print SQL?	Select to have the SQL statements for each of the archiving processes printed in the report

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.

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.

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.