Creating and Designing Archive Templates

This section discusses how to create and archive templates.

Note: 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 select PeopleTools, then select 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 described within this documentation. Access the Data Archive Manager pages by selecting select PeopleTools, then select Data Archive Manager.

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.

Image: Record Criteria page

This example illustrates the fields and controls on the Record Criteria page.

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.

Field or Control

Definition
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.

Access the Join Record Criteria page (PeopleTools, Archive Data, Archive Designer, Join 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.

Image: Join Record Criteria

This example illustrates the fields and controls on the Join Record Criteria page.

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.

Field or Control

Definition
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.

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

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.

Image: SQL Designer page

This example illustrates the fields and controls on the SQL Designer page.

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.

Field or Control

Definition
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.