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