Using Stat to Auto-Clear the PS Cache

Introduction:

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.

 

 

 

The How-To:

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:

 

P:\BatchFiles\BatchScripts\Clear_PS_Cache\Clear_PS_Cache.sql

 

 

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

SPOOL C:\temp\ClearPSCacheFrmStat.log

SPOOL ON

-- --------------------------------------------------------------------------------------

-- ---- Check Status of DB ----

-- --------------------------------------------------------------------------------------

SELECT NAME FROM V$database

-- --------------------------------------------------------------------------------------

-- ---- Execute the Update Statement ----

-- --------------------------------------------------------------------------------------

/

UPDATE PSSTATUS SET LASTREFRESHDTTM = SYSDATE

/

COMMIT

/

-- --------------------------------------------------------------------------------------

-- ---- SQL SCRIPT COMPLETE ----

-- Please See Log File: C:\temp\ClearPSCacheFrmStat.log

-- --------------------------------------------------------------------------------------

SPOOL OFF

 

 

Well, that was pretty painless, right?

 

Return to Top

 

 

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

 

 

Command Breakdown:

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:

 

Uninterrupted:

CMD /K SQLPLUS -s %%OracleDBSYSADM%%/%%OrclDBSYSADMPWD%%@%%PS_TARGET_DB_NAME%%@%%BatchDirectory%%Clear_PS_Cache\Clear_PS_Cache.sql

 

Interpreted Command:

CMD /K SQLPLUS -s SYSADMID/SYSADMPWD@HRPROD@P:\BatchFiles\BatchScripts\Clear_PS_Cache\Clear_PS_Cache.sql

 

 

Runtime Processing:

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:

 

 

 

Return to Top

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:

  • You cannot purge shared file cache.
  • You can purge the cache regardless of whether the application server domain is running; there's no need to shut it down and reboot.
    However, the procedure is less disruptive and runs more quickly if the domain is shut down or its activity level is low.
  • Purging the cache can take five minutes or more on a large or busy domain, depending on the domain configuration.
  • If database cache is enabled, the database cache will be purged. If the database cache is purged, it affects all domains using that cache.
    The database cache is shared across all domains that use the same byte-order (little-endian or big-endian).”

Courtesy: PeopleTools 8.51 PeopleBook: System and Server Administration Guide, Oracle, Inc.

 

 

Summary:

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.

 

Return to Top

 

Thank you for taking the time to read this Blog and I hope that you found it useful.

 

Respectfully Submitted,

 

William R. Hart
Solutions Architect
Quest Software, Inc.

About the Author
William.Hart
Promoting a positive Stat User Community and enriching the value of the Stat investment. William has worked with the Stat application for over twelve years. He specializes in the areas of PeopleSoft and...