This tip is brought to you by the International Oracle Users Group (IOUG), a user-driven organization that empowers Oracle database and development professionals by delivering the highest quality information, education, networking and advocacy. To become a member of the IOUG and gain access to thousands more tips and techniques created for Oracle users by Oracle users, visit http://w3.ioug.org/searchoracle2. Part 1 of this series explained the process behind a PeopleSoft upgrade and the steps to prepare for it, and Part 2 described the initial upgrade process and the move to production.
Application-specific (Group X) conversions
Each application module conversion step is a separate step in the delivered Upgrade Assistant template. The conversion steps were first moved to the Process Scheduler in order to run them concurrently. The exception to this was the GL application conversion step which had to be run first and complete prior to running the 'other ' (AP, AM, AR...) application conversion steps.
More on PeopleSoft applications
Learn how PeopleSoft 9.2 boosts productivity for PeopleSoft HCM users
Check out these six tips to maximize your PeopleSoft upgrade
Find out what makes the new PeopleSoft applications big news
Here is a breakdown of application specific conversion times for the initial 'move-to-production':
- GL -- open item conversion (took 4 hours)
- AP -- This was our longest running conversion step. It took approximately 72 hrs to complete on our first conversion attempt.
- AR -- took approximately 40 hours
The following describes some of the improvements made to application specific conversion steps:
Replaced the application engine (AE) 'do when' loop (processing single PS_OPEN_ITEM table item entries) with a single SQL update statement. This allowed the Oracle database to employ 'set processing' that is much faster than the delivered 'procedural' method. An additional clean-up step, to delete PS_OPEN_ITEM entries not having corresponding PS_JRNL_LINE entries, was inserted. The 'original' (delivered) steps were inactivated in the Upgrade Assistant and customized ones inserted into the conversion job-stream.
The changes resulted in the GL conversion taking approximately two hours.
Steps were disabled that did not pertain to Trimac's use of the system. This required an in-depth understanding of the application and requires a lot of coordination between technical and functional team members. The AP conversion uses several intermediary tables to perform individual table conversions. It first copies data to a temporary table performs its modification and then copies the converted information back into the original table. This really 'beats up' indexes associated with the tables affected by the conversion. The 'improvement' was to combine these steps into a single SQL update statement -- making a single pass at the data and taking advantage of Oracle's set processing capabilities. This was accomplished with several of the 'larger' AP tables (e.g. PS_PENDING_DST, PS_VOUCHER_ACCTG_LINE, PS_DISTRIB_LINE).
The combined result reduced the AP conversion time to less than 18 hours.
As with AP, several steps were combined into single SQL Update steps. Additionally, because one of the 'alter table' steps for AR was taking a long time (copying the contents of one column to another column) the step was moved from the 'alter table' portion of the upgrade to the AR conversion program. (This was done to try to optimize the overall execution time of the entire upgrade. Since the 'alter table' scripts run serially, everything else must wait for it to complete. As AP was the longest running of the application specific conversions, and the AR conversion had 'spare' time it was moved there. The result actually added two hours to the AR conversion. This did not matter as it still completed before the AP conversion).
Result -- reduced the AR execution time to approximately 10 hours
Alter table with deletes
This step of the upgrade involves removing columns from tables that are no longer used by the newer PeopleSoft application version. PeopleSoft's delivered method of performing an 'alter table ... drop column' forces Oracle to scan each row entry of the affected table in order to perform the 'drop column' operation. The execution of this step during the initial 'move to production' took several hours to perform.
The 'alter table ... drop column' statements for several large tables were modified to 'alter table ... set column … unused'. This command effectively makes the column 'invisible' to the database and is performed instantaneously. It does force some post-conversion work to later (during a maintenance window) clean up the unused columns from the Oracle system.
Result -- reduced the 'alter table with delete' to approximately 15 minutes.
Other improvements and lessons learned
Nothing runs in a development / test environment as it will in production. If possible, try to run several of the 'moves to production' on production hardware -- gives better time estimates for the real thing.
Having a good working relationship with your hardware vendor can pay off. The development / test environment hardware was nowhere near the speed of the production gear and or hardware vendor was able to supply 'loaner' (production speed) hardware.
Maintaining a close working relationship with your PeopleSoft account representative is equally important. PeopleSoft too, has a large stake in the success of your upgrade and can (as they did with Trimac) provide additional consulting / tuning resources -- to confirm the tuning approach.
Once the initial 'move-to-production' is complete PeopleSoft recommends 'freezing' all changes to the production environment that affect PeopleTools tables. These include such objects as trees, queries and combo-edits. For a project that may take up to ten months to a year to complete this is an unreasonable stipulation. PeopleSoft recognizes this shortcoming by describing an extra conversion step that Trimac incorporated in its upgrade process to extract PeopleTools changes. This is described in detail in appendix 'E' of the 'Financials / Supply Chain Management 7.5x to 8.4 Service Pack 1Upgrade' document.
The 'go-live' or final conversion is the culmination of several months of effort, including multiple practice runs of the conversion process. By the time you 'go-live' you should feel confident that all conversion steps have been thoroughly tested and you should have a good approximation of the time it will take to complete the conversion process.
The building of a 'go-live' checklist is essential in order to ensure a smooth 'go-live'. The checklist should not be left until the last minute to develop. On the contrary, it should be developed and reviewed at the end of each practice move. The go-live checklist is effectively the task list of activities that are to be performed at 'go-live' (typically a long weekend). By developing this early on in the project and inviting all stakeholders to comment and improve upon the plan you'll increase the likelihood of a successful final conversion. This involves not only members of the upgrade team -- including both technical and functional team members but also non-project staff such as system administrators, operational scheduling personnel and helpdesk staff who will assist you with the smooth transition to production. In this way, the go-live event will be better communicated to all involved with the rollout.
The technical upgrade portion of a PeopleSoft application upgrade is complex. PeopleSoft delivers a series of tasks and technology that will allow an organization to perform the upgrade. However, the delivered procedures and tools do not allow for the technical portion to be completed in a reasonable amount of time. The processes delivered by PeopleSoft are generic in nature and do not take into consideration the specifics of how your organization utilizes the application. They also do not take into account organization specific data volumes.
Having trained in-house staff, coupled with hired help experienced in your planned upgrade, a solid plan along with knowledgeable functional team members and support staff will go a long way to ensuring a successful technical upgrade. By making improvements to the upgrade conversion process and by practicing the upgrade multiple times you will ensure a predictable (no failure) and well-tuned (competes over a long weekend and not a week) upgrade.