Elasticsearch ELK Kibana Peoplesoft Perl SQL Server

Visualizing SQL Server Logspace Usage with Elasticsearch, Kibana and Perl

There are many ways possible ways to collect logspace usage data from SQL Server – this was a quick way using the tools I had at hand – DBCC, perl, elastic search and kibana.

All I did was capture the output of:

DBCC SQLPERF(logspace)

into a temporary table using a simple perl script using DBI. I then did a SELECT against the temporary table in the same perl script and posted the resulting data into ES using the Search::Elasticsearch CPAN module. The ES index was very simple – just 4 columns: database, logspace, logspaceused (%) and the timestamp of the capture (GETDATE()).

After that, all I had to do was visualize the data using Kibana. Here’s some sample output:


logspaceused graph
Logspace (%) usage over time

A great way to see the log space pressure points which can easily be tied back to specific batch processes at those times.

CRM Hints and Tips Peoplecode Peoplesoft

Display Template Debugging in PeopleSoft CRM

A quick tip – if you want lots of debugging feedback in CRM Display Template rendering, just create a userid CSPEER (Chris Speer) and use that. Chris Speer wrote a lot (all?) of the Application Package code for Display Templates and handily left debugging (messagebox) code in place that only happens when the current logged on user is CSPEER.

Useful to know.

Note: You will need to hack the filename the debug output goes to – it still refers to a UNC path of a machine at PeopleSoft.

Of course, if you don’t want to edit the code at all you could:

  • Create a NetBIOS alias called “sclappp532” on the application server through a Windows registry entry.In HKLM\SYSTEM\CurrentControlSet\services\LanmanServer\Parameters, just add a string value called OptionalNames with a value “sclapps532”. Personally, I would also create a matching DNS CNAME entry for completeness.
  • As the PeopleCode filename refers to a share name CR900DVL_LOGS you will also need to create that.
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.

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.

Fedora Linux Oracle Linux Peoplesoft Tuning VirtualBox

Reducing PeopleSoft DPK VM Size using zerofree

One of the slightly irritating parts of the build of a VirtualBox VM using the PUM downloads is the fact that the build script copies the DPK files into the VM prior to unpacking them. The data is stored under /opt/oracle/psft/dpk which is a mount point for “disk2”. Typically this disk expands to 23GB+ during the build process as a result of this approach.

To reduce the size of the VMs, what I like to do after the VM has been built is to:

  1. Attach the disk2, disk3 and disk 4 .vmdk’s to a simple Linux server VM – I use a minimal Fedora 24 install but it doesn’t really matter just so long as the zerofree utility is installed. Note: You could install zerofree into the PeopleSoft VM and do this step using Oracle Linux, but I use a smaller Linux install as it boots quicker.
  2. Boot the VM and mount the three disks read/write
  3. Delete any large files I no longer need e.g. the DPK tgz/zip files, PeopleTools 8.53/8.54 client folders, ptengs.db
  4. Re-mount the disks read only (mount -o remount,ro /dev/sd[bcd]1 {mount-point} )
  5. Run zerofree -v /dev/sd[bcd]1 on each disk to zero the empty space created by the file deletions.

I then close down the VM, detach the .vmdk files and clone the .vmdk disk files to .vdi files using virtual media manager. This has the effect of shrinking the resulting files – essentially doing a “VBoxManage modifyhd {vdi_file} –compact“.

Once I have the .vdi versions of the files, I remove the .vmdk files from the PeopleSoft VM, add back the .vdi files, boot the PeopleSoft VM, test it and delete the original .vmdk files if everything works.

In general, this approach releases approximately 27 Gb per VM – making the resulting VMs around 30-36 Gb. Still absurdly big of course 🙂

Configuration Elasticsearch ELK Hints and Tips Peoplesoft VirtualBox

DPK VirtualBox Memory Allocation

Even though my laptop has a decent 12 Gb of RAM, I still like to minimise the RAM allocated to PeopleSoft VMs.

My experience is that the sweet spot is 3072 Mb for VMs without SES – I never bother with the beast that is SES. After all it is a dead application – Elasticsearch cannot arrive soon enough for me. You can get away with 2560 Mb of RAM but you will see some swapping in OEL – not good even if you have a fast SSD. Mine is “ok” – a Samsung 1 TB 850 EVO but allowing any swapping still makes the system slow down considerably.

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.


Humour Peoplesoft Performance

A Conversation with a PeopleSoft “Developer” – Part 1

A bit of amusement, but unfortunately based on actual conversations I have had with developers over the years.

This one started with me noticing some heavy logical I/O at the database level across various views. These originated from various user actions – search records on pages, “submit” buttons on approval pages and even scheduled queries.

A bit of investigation into each case always brought me to a single locally developed “workflow” table. It had the usual structure for a workflow/worklist table – had all the workflow key columns, followed by the “data” key columns. Every column in the table was flagged as a key column. It was instantly obvious that the developer did not understand the importance of indexes and possibly quite a few other things. Here’s the conversation:

Me: So, this worklist table PS_xxx_WL – you created that right?

Developer: Yes.

Me: And you developed all the approval pages, the PeopleCode and the search records that use this table?

Developer: Yes (starting to look a bit nervous).