Tuesday, March 20, 2018

PeopleSoft has always used regular database tables for temporary working storage in batch processes. Up to PeopleTools 7.x working storage tables were shared by all instances of a program. That led to consistent read contention when multiple processes concurrently used the same table, and much higher high water marks that increased durations of full scans.
From PeopleTools 8, many copies of each temporary working storage table are created. Application Engines that run on the Process Scheduler are allocated exclusive use of a particular copy of the table. This avoids the inter-process contention. They start by truncating each allocated table, which resets the high-water mark.
Some delivered processing uses batch programs that are run apparently synchronously from the PIA. On-line edit and post in Financials General Ledger is a common example. Up to PeopleTools 7, the application server would synchronously spawn a batch process and wait for it to complete. From PeopleTools 8 the process is submitted to the process scheduler, and the PIA polls the Scheduler tables waiting for the process to complete. However, Application Engine can be run within the component processor. In Financials General Ledger, this can be chosen by a setting an installation configuration option. The truly on-line method can perform better because you are no longer waiting for the process scheduler to pick up the process request. A separate process Application Engine is not spawned, but the Application Engine program is executed by the PSAPPSRV application server process. One of the limitations is that the Application Engine program cannot commit. Committing after steps or sections is suppressed, and the %TruncateTable macro generates a delete statement instead. Therefore, on-line temporary table instances are never truncated by any process and their high-water marks can be raised by processes that handle larger volumes of data. This can have impacts for subsequent processes with smaller data volumes but that still have to full-scan working storage tables up to their high water marks.

Truncating On-line Temporary Table Instances

The answer is to implement a periodic process that truncates working storage tables, but only doing so when the table is not currently being used by a process. Every on-line Application Engine program is allocated a temporary table instance number, it locks the corresponding row on the table PS_AEONLINEINST. If it allocated to instance 1, it locks the row where CURTEMPINSTANCE is 1 and uses instance 1 of each temporary record that it needs.

Therefore the proposed truncate process must also lock the row on PS_AEONLINEINST that corresponds to each table that is to be truncated. The truncate must be done in an autonomous transaction so that the implicit commit does not release that lock. The lock can be released after the truncate completes. Thus, the truncate process waits for any online process to complete before truncating a table with the same instance number, and no process can start while the truncate process is holding the lock. However, each truncate will be very quick, and so each lock will only be held briefly, and it will have only a minimal effect on any online process that may be running at the time.

Package Usage

Usually, the package will be run without any parameters. The default behaviour will be to truncate tables with more than a single extent. Information on what the package does is emitted to the server output.

Set serveroutput on
EXECUTE xx_onlineinsthwmreset.main;

The package can be run in test mode when it will list the commands without executing them. Thus you can see what it will do without actually doing it.

EXECUTE xx_onlineinsthwmreset.main(p_testmode=>TRUE);

The package can optionally deallocate any physical storage. Storage will be reallocated next time the table is used.

Wednesday, March 07, 2018

It has always been possible in Application Deisnger to specify upon which databases platforms each index should be built. This is really a feature that is used by PeopleSoft development, rather than customers to deliver indexes that are more appropriate for a particular platform due to differences in the optimizer.
O﻿ver the years, the number of supported PeopleSoft platforms has declined. In PeopleTools 8.45, it went down from 9 to 6 and in PeopleTools 8.55 it has gone down to just 4, but there are still 9 columns on PSINDEXDEFN that correspond to the original 9 supported platforms.
I explained in a previous blog that you can have all or none of the platform flags set to the same value, but with the platform radio button on the index properties dialogue box is still set to 'some' because one or more of the platform flag columns for some of the unsupported platforms is set differently. Of course, this is a purely cosmetic problem, but one that can cause confusion in Application Designer.

PeopleTools 8.45

PeopleTools 8.55

I fix this by updating PeopleTools tables as follows. The first query reports on the indexes where the supported platform flags all have the same value and one of the unsupported platform flags are different.

spool platformfix855
SELECT *
FROM PSINDEXDEFN
WHERE PLATFORM_DB2=PLATFORM_DBX
AND PLATFORM_DBX=PLATFORM_ORA
AND PLATFORM_ORA=PLATFORM_MSS
AND (PLATFORM_ORA!=PLATFORM_SBS
OR PLATFORM_ORA!=PLATFORM_ALB
OR PLATFORM_ORA!=PLATFORM_SYB
OR PLATFORM_ORA!=PLATFORM_INF
OR PLATFORM_ORA!=PLATFORM_DB4)
;

These are the indexes that have inconsistent platform flags. In this case PS_PSPMTRANSHIST is to be disabled on DB2/AS400. You can't update the flag via Application Designer, but you could set the radio button to ALL.

It could be a tedious process to do this for a lot of indexes. So the following SQL commands correct all indexes. They set the SQL flags for the unsupported platforms to the value for the supported platforms if they are all the same. The version number on the record definition is updated so that Application Desinger refreshes the object.

UPDATE PSVERSION
SET VERSION = VERSION + 1
WHERE OBJECTTYPENAME IN('SYS','RDM');
UPDATE PSLOCK
SET VERSION = VERSION + 1
WHERE OBJECTTYPENAME IN('SYS','RDM');
UPDATE PSRECDEFN
SET VERSION = (
SELECT VERSION
FROM PSVERSION
WHERE OBJECTTYPENAME = 'RDM')
WHERE RECNAME IN (
SELECT RECNAME
FROM PSINDEXDEFN
WHERE PLATFORM_DB2=PLATFORM_DBX
AND PLATFORM_DBX=PLATFORM_INF
AND PLATFORM_INF=PLATFORM_ORA
AND PLATFORM_ORA=PLATFORM_SYB
AND ( PLATFORM_ORA!=PLATFORM_SBS
OR PLATFORM_ORA!=PLATFORM_ALB
OR PLATFORM_ORA!=PLATFORM_DB4)
);
UPDATE psindexdefn
SET PLATFORM_DB4=PLATFORM_ORA
WHERE PLATFORM_DB2=PLATFORM_DBX
AND PLATFORM_DBX=PLATFORM_INF
AND PLATFORM_INF=PLATFORM_ORA
AND PLATFORM_ORA=PLATFORM_SYB
AND PLATFORM_SYB=PLATFORM_MSS
AND PLATFORM_ORA!=PLATFORM_DB4;
UPDATE psindexdefn
SET PLATFORM_ALB=PLATFORM_ORA
WHERE PLATFORM_DB2=PLATFORM_DBX
AND PLATFORM_DBX=PLATFORM_INF
AND PLATFORM_INF=PLATFORM_ORA
AND PLATFORM_ORA=PLATFORM_SYB
AND PLATFORM_SYB=PLATFORM_MSS
AND PLATFORM_ORA!=PLATFORM_ALB;
UPDATE psindexdefn
SET PLATFORM_SBS=PLATFORM_ORA
WHERE PLATFORM_DB2=PLATFORM_DBX
AND PLATFORM_DBX=PLATFORM_INF
AND PLATFORM_INF=PLATFORM_ORA
AND PLATFORM_ORA=PLATFORM_SYB
AND PLATFORM_SYB=PLATFORM_MSS
AND PLATFORM_ORA!=PLATFORM_SBS;