Removing Extraneous Temporary Tables from Record Suites

Jobstreams use record suites and their corresponding temporary tables to process data. PeopleSoft delivers record suites with an entire set of EPM temporary tables, regardless of the products you license. For example, if you only purchase the Global Consolidations analytical application, the delivered record suites still contain temporary tables for all other EPM products—such as ABM and Budgeting. The following diagram depicts this scenario:

Image: Record suite with extraneous temporary tables

This diagram depicts this scenario:

Each delivered record suite can potentially contain hundreds or thousands of extraneous temporary tables—due to the fact that the total number of EPM temporary tables exceed 3,300. Each time a record suite is processed with these superfluous temporary tables, processing efficiency is severely degraded. However, PeopleSoft provide functionality that enables you to remove unnecessary temporary tables from the EPM database

Pages Used to Remove Temporary Tables from EPM

Page Name	Definition Name	Navigation	Usage
Clean Up Temporary Tables	PF_RUN_CLEAN_TMP	select EPM Foundation, then select Job Processing, then select Temporary Tables, then select Cleanup Temp Tables, then select Clean Up Temporary Tables	Run the Clean Temporary Tables application engine.
Cleanup Log	PF_TMPTBL_VW	select EPM Foundation, then select Job Processing, then select Temporary Tables, then select Cleanup Log	View temporary tables dropped from the EPM database.

Understanding the Temporary Table Removal Process

Extra temporary tables are removed from the EPM database by running the Clean Temporary Tables application engine (PF_CLEAN_TMP). To identify the temporary tables that need to be removed from EPM, all temporary tables are assigned an ownerID. Each owner ID is assigned to a specific EPM product. While temporary tables can only be assigned to one owner ID, you can assign several owner IDs to a single product.

Image: Temporary table to product hierarchy

This diagram depicts the hierarchical relationship between temporary tables and the products.

The Clean Temporary Tables process uses the product, ownerID, and customer licensing information (plus the relationships between these objects), to identify the tables that should be removed from the EPM database. The information is stored in the following EPM tables:

PS_PF_PROD_TO_OWNR: Contains product code, ownerID, and record type, and identifies the relationship between EPM product and ownerID.
PSRECDEFN: Identifies the relationship between EPM temporary tables and ownerID.
PSINSTALLATION: Identifies which EPM products you have licensed.

At runtime the Clean Temporary Tables application engine uses the information stored in the aforementioned tables to identify and delete all instances of temporary tables that are not required by your licensed product(s). It then regenerates the EPM_TEMP_RECORDS and EPM_TEMP_RECORDS_INC application designer projects with the new temporary tables.

All temporary tables being shared among applications belong to a special ownerID named AppCommon and are not deleted. There are also fundamental temporary tables associated with the ownerID EPM Foundation and are not deleted.

After running the Clean Temporary Tables process, all temporary tables that have been dropped from the database are logged in the PF_TMPTBL_LOG table. You can view the results of this process using the Cleanup Log page.

The Clean Temporary Tables application engine should be run after every install, upgrade or patch of PeopleSoft EPM products. After it runs, open the EPM_TEMP_RECORDS_INC project in Application Designer, and if not empty, rebuild the project to ensure that newly added temporary tables are built.

Delivered EPM_TEMP_RECORDS_INC Project

The EPM_TEMP_RECORDS_INC project is provided to help you integrate new temporary tables into EPM when you have added (licensed) a new EPM product but already run the Clean Temporary Tables process. Keep in mind that when you run the Clean Temporary Tables process, you delete temporary tables associated with any uninstalled EPM product. Therefore, when you introduce a new EPM product you also add new temporary tables back into the related projects. As described in this topic, when you add new temporary tables to a project, you have to rebuild the project. The EPM_TEMP_RECORDS_INC project is an incremental project which contains only newly added temporary tables. You can use the EPM_TEMP_RECORDS_INC project to rebuild your temporary tables, instead of using the EPM_TEMP_RECORDS project with the entire set of your temporary tables. Because of its smaller size, rebuilding the EPM_TEMP_RECORDS_INC project saves you processing time.

See Creating Additional Instances of Temporary Tables for Record Suites.

Clean Up Temporary Tables Page

Use the Clean Up Temporary Tables page (PF_RUN_CLEAN_TMP) to run the Clean Temporary Tables application engine.

Navigation

select EPM Foundation, then select Job Processing, then select Temporary Tables, then select Cleanup Temp Tables, then select Clean Up Temporary Tables

Image: Clean Up Temporary Tables page

This example illustrates the fields and controls on the Clean Up Temporary Tables page. You can find definitions for the fields and controls later on this page.

When

Select the frequency in which you would like the PF_CLEAN_TMP process to run.

Run

Click to run the PF_CLEAN_TMP process.

Cleanup Log Page

Use the Cleanup Log page (PF_TMPTBL_VW) to view temporary tables dropped from the EPM database.

Navigation

select EPM Foundation, then select Job Processing, then select Temporary Tables, then select Cleanup Log

Product Name

Displays the product associated with the deleted temporary tables.

DateTime

Displays the date and time the process was run.

Number of temporary tables

Displays the total number of temporary tables associated with the selected product.

Installed Product

Indicates whether this is an installed product.

Only temporary tables from non-installed products should be dropped.

Tables Dropped

Displays the total number of temporary tables dropped from the EPM database.

Table Name

Displays the name of the temporary table deleted from the EPM database.

Object Owner ID

Displays the Owner ID associated with a particular temporary table.