Changing Your PeopleSoft Database Platform

Posted by: Brent Martin in PeopleTools

Tagged in: Replatform

Brent Martin

There are several reasons why you might decide to migrate PeopleSoft to a new database platform.  It could be that you need to move to a more scalable platform.  Or you may be looking to reduce your annual license costs and want to switch to a more cost-effective platform.  Whatever the reason, you can certainly take advantage of PeopleSoft's multiple-database support and change your database platform.  This article will give you some ideas about how to plan the effort.

One of the first things to consider is whether or not you want to upgrade to the latest version of PeopleTools.  This may be required, especially if you want to deploy the latest version to the database platform you’re migrating to.   If this can be done in advance of the replatforming that makes the actual replatforming easier, but it does require it’s own testing and deployment cycle, and depending on the age of your current database platform it might be impossible.  In that case you will have to do the tools upgrade along with the database replatforming. 

 If you’re upgrading PeopleTools as part of the upgrade, you need to decide if you’ll want to introduce the new look and feel of PeopleTools roll out new PeopleTools enhancements to your users.  Review the release notes to get a good list of new features and enhancements, carefully choose what will be deployed as part of your scope.

 You’ll probably need to purchase new hardware to run your new database.  This may also be a good time to do a larger hardware refresh on your web/app/batch tests.  If you’re adding new hardware, be sure you size the hardware to run the new database according to your hardware vendor’s recommendations. They all have PeopleSoft-specific sizing questionnaires and will be happy to assign an engineer to assist you with your needs.  Also give yourself adequate time in your project plan to procure the hardware and have it installed and configured.

 Minimize non-technical changes. This isn’t the best time to change business processes or implement new functionality.  It’s not even the best time to de-customize.  If you have to do it, plan for the additional testing and change management (training/communication) that will be required.

One of the first things you should do to plan out this effort is to get a good list of the objects that will need to be updated as part of the replatforming effort.  We can assume that the delivered code will work on whatever supported database platform you want to migrate to, so we can focus much of our level of effort analysis on custom and customized code.

Processes, Reports and Queries

Get a list of all process and reports from the process definition tables.  Use the process scheduler tables to flag which processes have been executed in the last 2 years or so.  This will serve as your active processes list and will define the in scope processes for this effort.  Any processes, reports and queries discussed should be filtered against this active process list to prevent spending time on unused or unneeded processes.

To capture which nVision and Crystal reports are being run you’ll need to join PSPRCSRQST to PSPRCSPARAM and pick out the report name from the command line stored in the ORIGPARMLIST field.

PeopleCode Analysis

·         Extract the custom Peoplecode from PSPCMPROG to a flat file using  DecodePeopleCode.sqr.   Run another program to extract all SQLEXEC statements, and execute each one against the target database platform.   Be sure to flag any App Engines as being Used in your process list if you find active application engine PeopleCode.

Views Analysis

Analyzing views are a bit more straightforward.  Use App Designer or Data Mover to build ALL views on the target database platform, and identify which ones error out during the build. 

SQL Object Analysis

Extract the custom SQL from SSQLTEXTDEFN into a flat file and search it for specific keywords that aren’t going to work on your current platform.  For example, if you’re migrating from SQLServer to Oracle you might look for keywords like getdate, nolock, datediff, dateadd, day, month, year, str, left, right, +, ..,*=, datepart,isnull,convert,select top, len, inner, outer, .dbo.,xact_abort,implicit,round. 

This approach still requires a human touch.  Some of the SQL flagged to be modified may be fine because the keywords may be valid PeopleSoft functions, which are prefixed with “%”,  eg Dateadd. 

 Also keep in mind different database platforms have different conventions for outer joins, allowing subquery joins, etc.  This type of syntax different is very difficult to scan for.  Some of these types of issues will simply need to be identified by an experienced developer who reviews them.

 SQR Analysis

One quick and easy way to identify 50% of your SQR problems is to compile them against the target database platform.  Any BEGIN-SELECT statements that are invalid will be flagged in the output file.

 Unfortunately, problems with BEGIN-SQL statements aren’t compiled until run time so they will not be caught.  So you’ll also need to scan the SQR’s for your list of invalid keywords keeping in mind false positives in SQR code will try to sneak in.

 Query Analysis

For queries you can turn on query stats to get a list of what queries are being run.  Turn this on as soon as possible.  Then you can search SQRYEXPR.EXPRESSIONTEXT for the keywords listed above.

 Stored Procedure and Triggers

If you have custom stored procedures and/or triggers you’ll need to scan them for compatibility and decide how you will re-implement them on the target database platform.  How you review stored procedures will depend on your database platform.


Security isn’t typically a problem but there are a few areas to watch out for:

  • If you’ve added anything to the delivered PeopleTools permission lists (ie PTPT1000) they will be deleted as part of the PT upgrade.  It would be a good idea to identify these items in advance and come up with a way to add them back after the PT upgrade is complete.
Comments (2)Add Comment
written by Giby, June 13, 2013
May I know why you suggested to do a tools upgrade before DB platform migration.

We are on tools 8.50 and planning to upgrade to 8.53 before DB migration. We are planning to migrate from DB2 to Oracle.

Also can you please explain any challenges which you have faced?

Brent Martin
RE: Tools upgrade before migration
written by Brent Martin, June 13, 2013
Often a PeopleTools upgrade is a prerequisite to getting to the latest database platform. Breaking the PeopleTools upgrade out into it's own project helps you manage risk by limiting the amount of change you're introducing at one time. The PeopleTools upgrade will create it's own issues (nVision layouts have to be converted, users may have to get used to a different look and feel, app servers may behave differently, etc). So it's good to deal with those issues before you move to the new database platform which will have it's own issues (mostly around SQL in your custom code and how other applications hit your database directly).

But that's not the only way to do it and I'd imagine most companies do them at once. We did. Consolidating the upgrade with the database migration makes it possible to do everything with 1 test phase and a single deployment which helps with budget and timeline. Just keep in mind the different challenges you're likely to encounter and manage accordingly. Specifically, plan for a thorough test phase, test your cutover multiple times, and plan for heightened support after the go-live.

Write comment

security code
Write the displayed characters