Using Non-Shared Tables

This section discusses how to:

  • Use PeopleTools temporary tables.

  • Use GL non-shared tables.

  • Set up GL non-shared tables.

Page Name

Definition Name

Usage

Shared Table Statistics Page

TEMP_TBL_STATS

Access a log containing each time the process used a shared base working table because a non-shared table was not available.

See Setting Up GL Non-Shared Tables

Non-Shared Table Maintenance Page

NONSHARED_TBL_ASGN

Add, delete, or modify non-shared tables.

See Using GL Non-Shared Tables

Define Batch Truncate Threshold Page

DELETE_LIMIT_ASGN

Specify delete or truncate threshold for Application Engine Processes.

Non-Shared Table Truncate Threshold

NST_ASGN_TRTHRS

Specify delete or truncate threshold for COBOL processes.

PeopleTools provides a feature that allows applications to dedicate a specific instance of a PeopleTools temporary table for each GL Application Engine program run. This concept is similar to the GL non-shared table design in that it drastically reduces the risk of table contention. This feature is supported in some of the General Ledger Application Engine processes that use the PeopleTools temporary tables. These General Ledger Application Engine processes use the PeopleTools temporary tables:

  • Journal Edit.

  • Journal Post.

  • Combination Editing.

  • Allocation Copy Utility.

  • Inter/IntraUnit Common Processor.

  • Journal Generator.

  • ADB Post and Calculation.

  • Budget Processor.

  • Allocations.

  • Summary Ledger Build.

  • Multicurrency (COBOL and Application Engine)

  • General Ledger Year End Close (COBOL and Application Engine)

See PeopleTools 8.5x Documentation: PeopleSoft Application Engine, "Using Temporary Tables"

Application Engine and COBOL SQL processes use GL non-shared tables to increase performance.

The records PS_LEDGER (ledger) and PS_JRNL_LN (journal line), which include the PS_JRNL_HEADER (journal header) record are the most heavily accessed records. While these two records are the focus of this topic, you can apply the same techniques to any record.

Several GL Application Engine and COBOL SQL processes use base working tables to process large volumes of temporary data. Base working tables are shared working temporary tables that can be used by more than one program to process temporary data. You can potentially hamper performance if you run concurrent processes that use the same base working table.

To increase performance, these processes use GL non-shared tables in place of the shared working tables:

  • Closing.

  • Journal Posting.

  • Ledger Load.

  • MultiCurrency.

  • Open Item Accounting.

GL non-shared tables are defined by appending the non-shared table version number, TEMP_TBL_NUM, from table TEMP_TBL_ASGNM to the base working table TEMP_TBL_NAME. Only the process that reserves the non-shared table can process transactions against that table.

The General Ledger Application Engine and COBOL SQL processes use table TEMP_TBL_ASGNM to identify and reserve a non-shared table. This table defines the fields in the TEMP_TBL_ASGNM table.

Column

Description

TEMP_TBL_NAME

Base working table name.

TEMP_TBL_NUM

Non-shared table version number.

IN_USE_SW

Indicator to specify whether the non-shared table is in use.

PROCESS_INSTANCE

The process instance of the process that has the table reserved.

Process for Assigning GL Non-Shared Tables

The process first determines if a non-shared table exists by matching the base working table name (shared table) of the processes to the base working table name in TEMP_TBL_ASGNM that has the IN_USE_SW indicator set to N:

  • If a non-shared table is available, it reserves it.

    The process reserves the non-shared table by updating the IN_USE_SW indicator to Y in the TEMP_TBL_ASGNM table. No other process can use the non-shared table as long as the IN_USE_SW indicator is Y. The process updates the PROCESS_INSTANCE to identify which process has the non-shared table reserved.

  • If a non-shared table is not available, the process uses the base working table (instead of a non-shared table) and inserts a row into the TEMP_TBL_STATS table.

    This table provides a way to monitor the load of base working tables. The statistics can be useful to decide whether more non-shared temp tables are needed. This table defines the fields in TEMP_TBL_STATS.

    Column

    Description

    TEMP_TBL_NAME

    Base working table name.

    PROCESS_INSTANCE

    The process that attempted to reserve a non-shared table.

    DATE_ASSIGNED

    Process run date.

The TEMP_TBL_ASGNM table is delivered with four non-shared tables for each base working table. If these do not meet your processing requirements, use the GL Non-Shared Table Maintenance page to add more non-shared table entries to TEMP_TBL_ASGNM.

Adding a Non-Shared Table

To add a non-shared table:

  1. Determine if you need to create a non-shared table entry in TEMP_TBL_ASGNM.

    On the Shared Table Statistics page, check the TEMP_TBL_STATS table to determine if the usage of the base working table justifies creating additional non-shared tables.

  2. Create the GL non-shared table in PeopleSoft Application Designer.

    Save the record definition from an existing non-shared table or the base working table. You should create the non-shared table from existing non-share tables rather than the base working table. Non-shared tables usually have less fields in the index structure that the base working table.

  3. Create a GL non-shared table entry in TEMP_TBL_ASGNM using the GL Non-Shared Tables page.

  4. Add a non-shared table for a custom base working table.

    If you have created your own base working tables, add non-shared table entries to your Base Working Tables entries (repeat steps 1 to 3). Note that most non-shared tables do not require any unique indexes.

Shared Table Statistics Page

Use the Shared Table Statistics page (TEMP_TBL_STATS) to access a log containing each time the process used a shared base working table because a non-shared table was not available.

Navigation:

General Ledger > Monitor Background Process > View Shared Tbl Statistics Log

This log helps you to determine whether you need to create additional non-shared table entries in TEMP_TBL_ASGNM.

Note: The process inserts a log in TEMP_TBL_STATS only if the non-shared table entries exist for the base working table in TEMP_TBL_ASGNM.

Field or Control

Description

Record (Table) Name

Enter the name of the shared table whose statistics you want to view.

Non-Shared Tables

Displays the number of non-shared tables currently created.

Table Name

Displays the base working table name.

Process Instance

Displays the process that attempted to reserve a non-shared table.

Job ID

Displays the Job ID of the process that attempted to reserve a non-shared table.

Date Assigned

Displays the run date of the process that attempted to reserve a non-shared table.

Non-Shared Table Maintenance Page

Use the Non-Shared Table Maintenance page (NONSHARED_TBL_ASGN) to add, delete, or modify non-shared tables.

Navigation:

General Ledger > Monitor Background Process > Non-Shared Table Maintenance > Non-Shared Table Maintenance

You add instances of non-shared tables to the TEMP_TBL_ASGNM table so that the non-shared table is available for use by the batch processes.

To add a new instance of a GL non-shared table, enter its name in the Table Name field and click the Search button. All instances of that table are displayed. Click the Add icon to add a new instance.

Field or Control

Description

Table Name

Enter or select the name of the table that you want to maintain. Leave this field blank to view all tables available for maintenance.

Process Instance

Enter or select a specific process instance to maintain. Leave this field blank to view all process instances available for maintenance.

Reset

Click to reset the In Use field to No, which removes the check mark from the check box. Select Reset regularly for all tables, but make sure that no one is using the system.

If a process aborts, the In Use flag remains set to Yes. In order to free the table for other processes, the you must reset the flag to No.

Warning! If you reset a process instance while it is running, it can cause data integrity problems.

Reset does not clear the contents from a table. However, the table is cleared the next time it is assigned.

Field or Control

Description

Table Name

Displays the name of a non-shared table. You can select another name.

Number

Displays the instance number of the non-shared table. You can change this number.

In Use

Indicates whether the non-shared table is being used in a process. Click Reset to remove the check mark and change the selection to N.

Note: See previous Warning before you select Reset.

Process Instance

Instance number of the batch process that has reserved this non-shared table.

Note: If you are using your own customized base working table, make sure the table name does not exceed 12 characters. The total character length of a table cannot exceed 15 characters, so you must allow for three characters for the non-shared table instances.

The following table identifies some of the General Ledger COBOL processes that use the General Ledger Non-Shared tables. The menu paths identify where the base working tables are assigned to the processes that support the use of non-shared tables, as well as the temp tables whose base table names are fixed.

In this table, if the temporary table is specified in the ledger template or some definition pages, the record name is listed in the Dynamic Working Tables column below; otherwise the record name is listed under the Fixed Working Tables column:

Process

Menu Path

Dynamic Working Tables

Fixed Working Tables

Closing

General Ledger > Ledgers > Templates > Record Definitions

Closing Tmp

Closing RE Tmp

Closing Account Tmp

Closing Journal Header Tmp

Closing Journal Line Tmp

Closing Journal Line Tmp2

Ledger Load

General Ledger > Ledgers > Templates > Record Definitions

Led Load Temp Record

Led Load Temp Record 2

Multicurrency

General Ledger > Ledgers > Templates > Record Definitions

MultiCurrency Tmp

MultiCurrency Tmp1

TREE_SELnn_R

Close Budget

N/A

N/A

CFV_SEL

KK_RSCFV_SEL

TREE_SELnn_R