This Blog has been prepared to assist you in automatically clearing the PeopleSoft Cache on the Target Server when you migrate an updated PeopleSoft Page Object. One of the biggest concerns with migrating PeopleSoft Pages is that you need to clear the cache on the target server. If it’s during Business Hours, this involves asking all the users on that target to Log-Off that environment, using the PeopleSoft Administrator (PSAdmin) Console to clear the cache, and finally restarting the Server (Bouncing) to enable the new cache to load properly.
This can be very disruptive to the Business Users.
Based on the version of PeopleTools you are running, this may vary slightly. Provided you are on PeopleTools 8.x and above, this will apply.
Using the SQL method to clear the cache allows you to update the LastRefreshDTTM (Last Refresh Date Time) on the PSSTATUS Table which manages the Application Server Cache. If the LastRefreshDTTM is later than what the Application Server expects, which is the last time the server cache was cleared, then by default, the Application Server Cache is cast as Invalid.
All files within the cache directory are considered invalid and are refreshed as required. The SQL Statements for this process is as follows:
UPDATE PSSTATUS SET LASTREFRESHDTTM = SYSDATE; COMMIT;
Let’s combine this SQL Statement with a Stat Pre-Migration Step to Auto-Clear the cache just prior to migration of a PeopleSoft Page Object to our Target Environment.
Clear Cache Script:
Pre and Post migration steps define activities you perform immediately before or after a migration of an archive set occurs. These steps are performed either manually by the user or automatically by Stat.
The steps are meant to promote adherence to development standards and are not mandatory or enforced.
Please see the previous Blog Post entitled “Using Stat & PuTTY’s PLink Utility” for additional details on Pre & Post Migration Steps. Or please refer to the Stat Administrators Guide.
The first thing we need to do is create a SQL Script that will automate the needed call and then have Stat execute that call via a Pre-Migration Step.
Our SQL Script is stored on the following network drive on our server:
Return to Top
Our Full SQL Script:
-- DESCRIPTION: Clear PeopleSoft Cache - Without Bouncing App Server.
-- USED FOR: Clearing the PS Cache when updated Page is migrated to target.
-- TESTING: SQLPlus @P:\BatchFiles\BatchScripts\Clear_PS_Cache\Clear_PS_Cache.sql
-- CMD-LINE PARAMS: None.
-- CREATED: Copyright 2011, Quest Software, Inc.
-- AUTHOR: William R. Hart, Solutions Architect, Quest Software Inc.
-- The following Code is for Reference Only.
-- Quest Software, Inc. makes no claim of usability or -- accuracy of the statements contained in this sample.
WHENEVER SQLERROR EXIT SQL.SQLCODE
SET echo ON
SET feedback ON
SET linesize 270
-- ---- Check Status of DB ----
SELECT NAME FROM V$database
-- ---- Execute the Update Statement ----
UPDATE PSSTATUS SET LASTREFRESHDTTM = SYSDATE
-- ---- SQL SCRIPT COMPLETE ----
-- Please See Log File: C:\temp\ClearPSCacheFrmStat.log
Well, that was pretty painless, right?
Sample Pre-Migration Step:
Now let’s call this script from within a Pre-Migration Step: Step I: Pre Migration Step Record Setup:
Once you hit the Apply button the “Define Command” button is enabled in the top right hand corner of the window.
Select the “Define Command” button to enter in the process that we want run when this step gets triggered.
Step II: Pre-Migration Step Command Setup:
We enter our command, with parameters to be included, in the lower left hand box. We can select the “Interpret Command” at anytime to see what the parameters will be interpreted into. (Provided there is a value to get at the time.)
The following two values, Uninterrupted and Interpreted, shows how the command line parameters are converted from a stored/encrypted value, to the required value needed to properly execute the job at runtime.
Command with Parameters:
CMD /K SQLPLUS -s %%OracleDBSYSADM%%/%%OrclDBSYSADMPWD%%@%%PS_TARGET_DB_NAME%%@%%BatchDirectory%%Clear_PS_Cache\Clear_PS_Cache.sql
CMD /K SQLPLUS -s SYSADMID/SYSADMPWD@HRPROD@P:\BatchFiles\BatchScripts\Clear_PS_Cache\Clear_PS_Cache.sql
When the Stat Migration starts, and we drag-n-drop the Archive Set from one environment to another Stat looks through the objects that are physically getting moved and checks to see if there are processes that need to be run prior to their migration.
When we migrate an object, such as a PeopleSoft Page Object in our example, Stat will do a look-up in the Pre-Migration Steps command and pull back the matching job that must get executed prior to the migration.
The Pre-Migration Runtime Window will appear at the time of migration. The job listed in the below screenshot is controlled by the items included in the migration package.
When loaded, we select the “Run Command” button to execute the Clear-Cache Script. When completed, we select the “Ok” button to continue our migration.
Pre-Migration Step Runtime Execution:
Acknowledgements & Notices:
Our many thanks to David Kurtz, the original author of the real-time clear cache script:
UPDATE PSSTATUS SET LASTREFRESHDTTM = SYSDATE;
Important! You should purge the cache only after due consideration, and in after consultation with PeopleSoft support.
“Please keep the following in mind:
Courtesy: PeopleTools 8.51 PeopleBook: System and Server Administration Guide, Oracle, Inc.
Having the ability to clear the cache just prior to a PeopleSoft Page migration ensures that the page will properly on the target server. The fact that we can do this without
having to 'bounce' the server, and ask our users to log-off is a benefit for everyone. Combining this capability into a Pre-Migration step provides a repeatable, enforceable process
that all members of the team can utilize.
Stat’s Pre & Post Migration steps make the everyday, monotonous and repetitive, tasks easier to execute and allows us the freedom to take on more enjoyable tasks that interest us.
Thank you for taking the time to read this Blog and I hope that you found it useful.
William R. HartSolutions ArchitectQuest Software, Inc.