Category: App Engine

Categories
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 🙂

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

Categories
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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

#!/usr/bin/perl 
#
# 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 'prcsapi.pl';
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 prcsapi.pl 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:

1

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.

Enjoy.

Categories
App Engine Hints and Tips Oracle PeopleTools PSVERSION Tools VERSION

VERSION 3.0 Application Engine!

If you are running a pre-8.53 version of PeopleTools you will (hopefully) be aware of the issues and requirements of using the VERSION Application Engine to correct problems with object versioning in PeopleSoft.

To summarise, the older VERSION App Engine just resets all the counter to 1! This is known as “Classic Mode” in the later release of the VERSION App Engine and is to be avoided at all costs really. You need everyone out of PeopleSoft, have to take all Application and Process Schedulers down, clear cache and run the App Engine from the command line to be 100% safe using it. Ugh.

Oracle recommends downloading and installing the most current VERSION Application Engine on all systems running PeopleTools >= 8.44 up to the latest release. Why? Because the later release is safer and better in all respects.

It has three execution modes:

  • Report Only Mode – Prefix your Run Control Id with the string “REPORTONLY” and you will get a report of the problems you have (if any) with versioning.
  • Classic Mode – Prefix your Run Control Id with the string “RESETVERSIONS” and the App Engine will run the “old way” – with all of it’s limitations (servers down, everybody out, cache cleared and run from command line). DO NOT USE THIS!
  • Enhanced Mode – Use and other Run Control prefix and the App Engine will just fix the versions that are wrong. It won’t reset back to 1 – just make everything line up correctly. You can even run this via Process Scheduler and with the servers up. What’s not to like about that?

Oracle Support Document 611565.1 (E-AS: Instructions Regarding the Use of the VERSION Application Engine Program) can be found at: Oracle Support Document 611565.1

Categories
App Engine Peoplecode Peoplesoft

Where you do CreateRowset does matter …..

In Peoplecode, it is common to see this construct:

1

Local Rowset &rs = CreateRowset(Record.RECNAME);

Often, this construct is used within a loop. But this is not a “free” statement – it goes to the database:

1

SELECT {column_list} FROM {tab_name} WHERE 1=2

Which, on SQL server would get wrapped in a SET FMTONLY ON / SET FMTONLY OFF statement pair in order to get the column META-DATA (describe output).

But if you use the above construct inside a loop, then there will be “n” executions of the above SQL – so “n” database round trips too. This is not free and will impact your performance.

Better to define the rowset at the component level and re-use inside the loop.

PS: The same applies to CreateRecord(Record.RECNAME). Think about where you place these statements in your code – especially in App Engine code where you are likely to be looping through rows..