Categories
Peoplesoft PeopleTools Upgrade

PeopleTools Upgrade to 8.53.20

I have just completed a PeopleTools upgrade from 8.52.10 to 8.53.20 on a production CRM 9.1 MP5 system. Everything went very smoothly but it was a very intense weekend due to the size of the production stack and the need to ensure all the configurations were correct. The environment to be upgraded comprise:

  • 4 web-only servers on Windows 2008 R2 each with a single PIA
  • 8 application servers on Windows 2008 R2 running 7 application server domains, 3 process schedulers, 1 dedicated PUB/SUB domain and 1 shared search server.
  • 1 clustered SQL Server 2008 R2 database

Things to watch out for on the upgrade, and some techniques I used to speed/simplify the process:

Categories
BI Publisher Configuration Peoplesoft Tools

BI Publisher fix for XLSX Cell Merging Problem in PeopleSoft

Under PeopleTools 8.53 the default Excel output format for BI Publisher reports is now native XLSX rather than “MHTML” as in previous releases. This has a number of advantages such as a much smaller output file size due to it being a real Excel format and not HTML :-).

The change to this format by default also has some interesting issues with cells and cell merging that are fixed in a BI Publisher patch. The description of the bug is:

Categories
Integration Broker Peoplesoft PeopleTools

PT 8.5x Integration Broker connection to pre-8.50 Node

Although not visible, all 8.4x nodes have a default Domain Connection Password of ‘PS’. During the security hardening process for Peoplesoft 8.5x this type of hidden password was made required and the hidden defaulting logic removed.

To connect to an 8.4x system from PeopleTools 8.53 and 8.54 onwards you must specify the password. You can override the password in the config of the 8.4x system to be something other than PS – in fact you should do that. But to get things working when you cannot “ping” the old node, try ‘PS’ (upper case) as the password.

Categories
Peoplesoft PUM VirtualBox

Peoplesoft PUM – Re-starting VM Config

If you need to re-start the Peoplesoft configuration process for an Update Manager Virtualbox VM, then you can do so by logging in as root and setting the VBOX_SETUP environment variable to “1” before running the VM template shell script e.g.

export VBOX_SETUP=1
./opt/oracle/psft/vm/oracle-template.sh

Note that if you had previously created the APPDOM/PRCSDOM domains then you will need to delete them prior to re-starting the configuration script or it will fail.

Categories
BI Publisher Peoplesoft

Displaying related content on Peoplesoft BI Publisher Crosstab Report

If you need to print related content against each row of a crosstab report, you can do this by assigning an “incontext” variable to the value of the controlling field on the row (typically assigned to “./H”), and then using that as a filter in an XPath query back to the query results e.g.

Assign a variable with the value we are searching for:

<?variable@incontext:case_id;’. /H’?>

And then use the variable in an XPath filter to find (say) a sibling value from the result set. In this case RC_SUMMARY for the given CASE_ID on the row:

<?(/query/row/CASE_ID[text()=$case_id]/../RC_SUMMARY)[1]?>

This query accesses the first RC_SUMMARY for the given CASE_ID in the query results.

Note the importance of the brackets around the query here … ( ) – this ensures we find just one node. Without them we potentially find repeated RC_SUMMARY values for a given CASE_ID.

NOTE: You could use the XPath sibling functions to find the related value(s), but ‘../’ seems somehow more readable to me.

Categories
Peoplesoft Tools Utilities

Downloading from Oracle with wget

Downloads from My Oracle Support are facilitated through a wget shell (wget.sh) script that can be used as an alternative to interactively downloading the individual files via a web browser. This feature is particularly useful for headless *nix servers or where X is not installed at all, but also for those large image downloads you would like to download outside core office hours.

However, more recent versions of wget can have issues with the generated script provided by Oracle. This post offers some .wgetrc settings to make the supplied scripts work correctly without modification (apart from adding your password).

Here is a .wgetrc that works on the Linux systems I have tried i.e. Debian/Ubuntu based ones like LinuxMint:

#
# Suitable wget settings for downloads from https://updates.oracle.com 
# 
max-redirect=5 
continue=on 
secure-protocol=TLSv1 
progress=dot:giga

Note: The entries continue=on and progress=dot:giga are more for useablity. I like to use resumed downloads where possible in case a script fails and I need to restart it. Oracle’s web server supports ranged downloads so this does work. The progress report changes just reduce the size of the log files by making each line represent 32M of downloaded data.

Categories
Peoplesoft SQL Server

Read Committed Snapshot and PeopleSoft

  1. Turn it on.
  2. And if you decide to create a database manually and not via the PeopleSoft scripts – remember to turn it on.
Categories
Peoplesoft SQL Server Tuning

Trace Flag 4199 and PeopleSoft

Whilst I have a general rule not to use SQL Server trace flags unless I actually have the underlying issue, there is one exception:

Trace flag 4199

It switches on all the Query Optimizer (QO) fixes and saves you turning on the numerous individual QO fixes one by one. This trace flag is very well documented on the web (e.g. at Microsoft) and many people consider it essential for their systems. I do too.

One of the interesting comments in the above Microsoft article (last updated in 2012, but present in the article from the original in 2010) is the statement that future query optimizer fixes will not have individual trace flags of their own. So you are pretty much forced to use it in preference to individual trace flags going forwards.

PeopleSoft and TF 4199

In my experience, TF4199 is essential for PeopleSoft systems. Some of the early 2005/2008 fixes covered by TF 4199 are fundamental to good query execution plans in many areas of Peoplesoft. In particular, the fixes related to Fast Forward Read Only cursors are especially relevant as Peoplesoft makes extensive use of them.

Note: Trace flag 4199 is not a panacea for all your PeopleSoft performance problems – I have seen it slow down some SQL statements as well. But typically the affected statements were user queries that were badly designed in the first place 🙂

Categories
Peoplesoft Performance Tuning

SET ARITHABORT ON and PeopleSoft

My previous post focused on the specific issue of poor PS Query performance when ARITHABORT was set to OFF on the connection.

But the fix I suggested for that will also affect all other Peoplesoft connections. That means app servers, process schedulers, app engines, BI Publisher reports …pretty much every PS connection. But that really is what you want ….

So remember:

EXEC sys.sp_configure N'user options', N'64'
GO
RECONFIGURE WITH OVERRIDE
GO

is your friend 🙂

But remember to test it first …. 🙂

Categories
Peoplesoft SQL Server Tuning

Poor Query Performance under SQL Server (PeopleSoft)

Background

A PeopleSoft HRMS 8.8 system running on SQL Server 2008 R2 SP2 that is used as a reporting environment. Data is copied each night from production and a select but limited group of users run queries against it.

The system performance was both terrible and unpredictable. There was a “rule” in place that queries that ran for over two hours were cancelled. Two minutes seemed a long time for the data volumes involved to me.

Analysis

There were lots of things wrong with the instance setup such as default values for MAXDOP and “Cost threshold for Parallelism”, as well as only one TempDB file and unlimited RAM allocated to SQL server. Essentially, an out of the box SQL server install. Not even a Cumulative Update had been applied. In addition, SQL server had 128 GB of RAM and 16 Processors allocated to it so it was hardly short of resources.

But none of that, in itself, really explained the very poor performance of many of the user queries in PeopleSoft. Especially given that running the exact same query in SSMS gave completely different (and quite fast) results. The lack of indexing on the underlying tables and unnecessary cartesian products in the PeopleSoft query security views made the queries less than quick, but they still typically completed in a few seconds or minutes in SSMS. They often never finished in PS query either on-line or scheduled – even after 5 or 6 hours.

Now this database is a very old HRMS 8.8 system that has been upgraded from SQL Server 2000 and various old Peopletools releases up to the giddy heights of 8.49.28 🙂

After much investigation and running SQL Server Profiler along with Adam Machanic’s superb sp_WhoIsActive (read more about it from Brent Ozar here) it turned out to be all related to the setting of ARITHABORT – a well known but I think poorly understood issue.

There are numerous articles on the internet about the issues of SQL running faster in SSMS than via other clients. I leave you to search for those yourself, but to summarise:

You must make sure the connection settings you use in SSMS match those used by the application. Otherwise the query plan you see in SSMS may not bear any similarity to the one the application is using. This makes problem solving nearly impossible.

Profiler will help you identify the options set at the connection level:

ARITHABORT_SQLSERVER

And using:

EXEC sp_WhoIsActive @get_plans=1

will give you the session and the query plan which you can open and compare to the same plan in SSMS. If they are different you need to investigate why.

sp_WhoIsActive_Example

ARITHABORT

ARITHABORT is query plan affecting and a query plan “cache key” – so you will not get the same query plan in SSMS as the client application if the values are different. SQL server will have two plans for the same SQL statement. Remember as well that SSMS issues SET commands as defined in Tools -> Options -> SQL Server -> Query Execution:

 

SQLServerQueryExecutionOptions

ARITHABORT in PeopleSoft

Now the interesting thing about ARITHABORT is that the standard Peoplesoft install scripts create the Peoplesoft database and then issue two ALTER DATABASE commands:

ALTER DATABASE <DBNAME>
SET ARITHABORT ON
GO
ALTER DATABASE <DBNAME>
SET QUOTED_IDENTIFIER ON
GO

where <DBNAME> is your database name.

The install guide also tells you to ensure that QUOTED_IDENTIFIER ON is specified for any client connections to a Peoplesoft database. It goes on to explain that this can be set in the ODBC connection setup. What it fails to mention is setting ARITHABORT for client connections. This should also be set but you cannot set it on the ODBC connection. Best to set it at the INSTANCE connection level:

EXEC sys.sp_configure N'user options', N'64'
GO
RECONFIGURE WITH OVERRIDE
GO

Now assuming you have done that, and you have the default SSMS settings in place, the query execution plan you see for the PeopleSoft application via ODBC should match the one you get in SSMS. And, more importantly the plan that gets chosen will be a much better plan than with ARITHABORT OFF in a great many cases.

Now in this specific case it seems that someone created the original database without using the supplied script and so they did not set either of these default settings at the database. For QUOTED_IDENTIFER they did at least stick with the ODBC connection defaults so that was always ON. But sadly ARITHABORT was not.

The Fix

A quick ALTER database and a re-configure of SQL server, drop the Peoplesoft Application Servers and a SQL Server instance re-start and the problem was solved. It didn’t make every query faster but it did improve the vast majority. The ones it did not fix are typically the ones with missing joins 🙂 Now all I need to do is create some indexes that reflect the way the data is accessed and re-write the query security views to be more efficient and I should be able to get all the queries under two minutes. Well … those with joins between the tables … ….

NOTE: You could and perhaps **should** include QUOTED_IDENTIFER in that user options setting for extra safety (use 320 instead of 64 in the above sp_reconfigure). Just in case someone un-ticks that option whilst setting up ODBC.