PeopleTools Performance SQL SQL Server

PeopleTools and Parallelism on SQL Server

During a recent discussion with another PS Admin running on SQL Server it became apparent that his efforts to improve application SQL performance through adding more CPUs (with the associated license costs) were based on a fundamentally wrong assumption:

PeopleTools/Application SQL will go parallel if needed.

This is simply not true. In fact, most SELECT SQL can never go parallel under PeopleTools on SQL server due to the fact that they are run through cursors and PeopleTools requests a cursor type of FAST_FORWARD. FAST_FORWARD cursors result in an execution plan with a NonParallelReason value of NoParallelFastForwardCursor.

PeopleTools Performance SQL Server Tuning

PeopleTools Indexing on SQL Server

PeopleTools was designed to be database agnostic. In the past, the database support included databases such as Gupta SQLBase, Allbase, Informix and Sybase. The ability to support multiple platforms and add others relatively easily was a consequence of some excellent initial design decisions. Sadly, database platform support has dwindled to Oracle, SQL Server and DB2 in more recent releases of PeopleTools.

However, supporting multiple databases in this way meant that many database specific features were not taken advantage of. Since the Oracle takeover of Peopleoft, there have been some moves to add some Oracle database specific feature support e.g. GTT, but almost nothing on the other databases.

PeopleTools SQL Server Tuning Upgrade

PSTREENODE Clustered Index Change

I noticed that in PT 8.55 (well at least in .08 and later) that the PeopleTools meta-data for the Clustered Index on PSTREENODE lost the “clustered” attribute. I’m not sure why this was changed but you should be aware that this change could impact your execution plans – especially if you use row level security.

PT 8.55 loses Clustered Flag on "key"
PT 8.55 loses Clustered Flag on “key”


"Key" is now Nonclustered
“Key” is now Nonclustered


And, more importantly what we now have is a “heap” and so data is no longer ordered as we might expect. This also forces any sequential access to refer to index allocation map pages. Overall, changing this is not something to be done lightly, and I do wonder at the thought process behind this change – or was it just a mistake?

Hints and Tips Peoplesoft PeopleTools SQL Server Tuning

PeopleSoft – INCLUDE and WHERE (filtering) on SQL Server Indexes

It is trivial to add support for INCLUDE() and WHERE() clauses on SQL Server indexes in PeopleSoft – just change the model DDL for Index creation to have the two optional clauses defaulted to blank and then override on the specific index as needed:


Obviously, at the individual index level you will need the full syntax including the keywords e.g.

INCLUDE (col1,col2,col3 …)

WHERE (criteria)

Be aware that the criteria you can include is limited – refer to the Microsoft documentation for more details.

Hints and Tips PeopleTools PUM VirtualBox Windows

EnableLinkedConnections and VirtualBox PUM Images

When you map a network drive to the Samba share of a VirtualBox PUM VM in order to install (say) the PeopleTools client, the mapped drive may be invisible to your cmd prompt running as Administrator – something you need in order to update the registry and install the client software.

To work around this on Windows 7 through 10, see this article:

App Engine Hints and Tips PeopleTools Performance

Re-Use in Application Engine

Almost without exception whenever I am asked to review the performance of a PeopleSoft system I discover issues that lead back to locally developed Application Engine processes. In particular, high database SQL parsing rates invariably originate from SQL steps that should have the re-use flag set to Yes.

What this flag does is replace any %bind() variables in the SQL step with proper bind variables and compiles the SQL only once. Without this flag, the %bind() variables are substituted as literals at run-time and executed directly. This can lead to huge parsing rates as typically the offending SQL steps are executed within a loop. Of course, this is generally contrary to what you should be doing with SQL – set processing, but all too often Application Engine is used as a direct replacement for procedural languages such as SQR.

Some metrics from a system I looked at a while ago:

Approximately 15300 SQL statements in the cache, of which over 7700 originated from a single Application Engine run just 9 times during the day. These 7700 could have been been reduced to 3 (well 2 actually) just by setting the ReUse flag to ‘Y’ on the three offending SQLs. Using set processing of course, none of them would have been needed 🙂

App Engine Hints and Tips Peoplecode Peoplesoft PeopleTools Performance Tuning

Jackson Structured Programming (JSP) – Read Ahead and PeopleCode

Jackson Structured Programming – now that brings back memories of my COBOL training at British Telecom in the late 1980’s.

What prompted this short post was a dreadful piece of hand-crafted PeopleCode to load a CSV file using a file layout. The usual “Operand of . is null” occurred unless the input file contained a “blank line” at the end.

The underlying reason for this was a failure to apply one of the fundamental techniques in JSP – the single read-ahead rule:

Single Read-ahead rule: Place the initial read immediately after opening the file, prior to any code that uses the data; place subsequent reads in the code that processes the data, immediately after the data has been processed.

In fact, this approach is exactly what you get when you drag a file layout into a PeopleCode step in Application Engine – sample code that uses the JSP single read-ahead rule.

App Engine Languages Peoplesoft PeopleTools Perl Process Scheduler SQR

Perl and PeopleSoft

Way back in 1998 I was implementing PeopleSoft Financials 7.5 for a UK Charity. SQR and Application Engine (the COBOL version back then) were the only options available in the PeopleSoft toolset for updating the database. Other than straight SQL updates in SQLPlus of course!

Whilst SQR was an OK tool, I always felt it lacked so many capabilities. In fact, at that point it could not even read a CSV file – I had to code a user DLL in C to achieve even that. All very frustrating.

Having rescued various projects using perl scripts prior to this, I decided I would add perl as an available language to process scheduler. Taking the SQR include files for the process scheduler API as an example, I emulated the same approach with perl. It worked brilliantly and allowed me to add some sophisticated features to PeopleSoft including:

  • SQL and query output to CSV and XLS formats (remember this was prior to the PeopleSoft Internet Architecture) through the SpreadSheet::WriteExcel, and DBD::CSV CPAN modules
  • User defined SFTP/FTP/SCP file transfers to and from third-party systems
  • Bank Statement loads by encapsulating mainframe remote access software into process scheduler jobs
  • Exchange rate loading via Website “screen scraping”
  • Spreadsheet Aged Debt reporting
  • Fuzzy duplicate customer identification/matching
  • Automatic customer identification in Accounts Receivable

Here’s the start of one such perl script from 2004:

# This is a perl routine to find possible matches for originator's
# sort code and bank account by looking to find possible customers.
# (1) Fetch the list of bank statement entries.
# (2) Try to find customer like this.
# Author: XXX
# Date : 29th January 2004.
# Amendment History
# -----------------
# 29-JAN-2004 XXX First version
#$debug = 1;
use lib 'h:\perl';
use Strict;
use Spreadsheet::WriteExcel::Big;
use String::Approx qw(amatch);
require '';
use Date::Calc qw (Delta_Days);
$row = 0;
# Connect to database using parameters resolved from command line
$dbh = DBI->connect( "dbi:$dbtype:$dbname", "$accessid", "$accesspswd" ) or die $dbh->errstr;

The require of brings in all the necessary sub-modules needed for the process scheduler API. Updating the process scheduler status is then simply a call to the appropriate API function:

Update_Process_Status($prcs_run_status_processing,'Processing has started.');

More recently, I have taken a similar approach but for ruby …. more on that later.


Administration Humour Oracle Peoplesoft PeopleTools Performance Tuning

A Conversation with a PeopleSoft “Developer” – Part 2

Another amusing conversation and somewhat shocking “discovery”.

More poor performing SQL – obviously missing any sort of suitable index (on Oracle).

Me (to developer): Could you add a suitable composite (multi-column) index to table PS_xxxx please for columns C,B and A – there seems to be frequent access using these three columns that would be vastly improved by adding an index.

Developer: OK.

After a few days I notice new “alternate search key” indexes in the DEV environment. One for each of the columns A, B and C.

Me: Did you add that index?

Developer: Yes, but in the testing I did it wasn’t very much faster.

Hints and Tips Peoplesoft PeopleTools PUM – well worth a visit. Great podcasts, discussions on DPK, the latest PeopleTools features and how to use them as well as more mundane PeopleSoft admin topics.