If you have worked on either HCM or Campus Solutions applications in PeopleSoft then you would have come across the term 'HCM SOA Registry'. Associated with the HCM SOA Registry is a step/process to 'Refresh Cache' which can either be done online or batch.
I have seen many customers (using HCM or Campus Solutions) who run this 'Refresh Cache' on a regular basis and sometimes as a post step to standard migrations. By standard migrations, I mean the weekly/fortnightly migrations which are part of production support release cycles and not necessarily the bundle/patch/upgrade migrations.
What is wrong with running this process regularly as a post step to any migration?
Well, nothing. But of late, I am finding several cases where the 'Refresh Cache' process fails with "RowsetCache::Save: Save failed for xxx/xxx=: unknown error. (2,975)" error particularly since HRMS 9.0 Bundle 26 (last HR bundle for CS).
Since this online/batch cache refresh has been causing a lot of pain, I wanted to find out when it is appropriate to refresh cache (reverse engineering and identifying the events - object changes - that would necessitate a refresh). I ended up building a SQL query to identify whether a migration of a project (say part of weekly/fortnightly production support release) would require a 'HCM SOA - Refresh Cache' just based on the objects it contains.
Before I share the details of the SQL query, I would like to share some of my notes since I did not find any information that was straight forward or easy to understand specifically on HCM Registry - Refresh Cache.
HCM SOA Registry:
HCM SOA Registry, like any other SOA Registry, provides a mechanism for service registration, discovery and binding.
SOA Registry Example:
Reference: Web 2.0 Architectures
HCM SOA Registry:
PeopleBooks Reference: Understanding the HCM Interface Registry
My Process/Flow Diagram: HCM SOA Registry
How do we access HCM SOA Registry?
Main Menu > Set Up HRMS > System Administration > HCM Registry
Service Registry:
Transformation Registry:
Where does HCM SOA Registry data get stored?
According to Doc ID 658098.1: HCM Service Framework Error When Adding, Updating, or Viewing Person Data in Campus Solutions:
"The HCM Service Registry (Set Up HRMS > System Administration > HCM Registry > Service Registry) captures the meta-data about the HCM SOA services: input and output signatures, implementation application classes, web services, component interfaces, etc. This meta-data is stored on the HC_REGISTRY portal (PeopleTools > Portal). This portal contains no other data."
How do we access this HC_REGISTRY portal where the HCM SOA Registry is stored?
- Navigate to Portal – Structure and Content: Main Menu > PeopleTools > Portal > Structure and Content.
- Replace the default portal (EMPLOYEE) with HC_REGISTRY in the URL.
For example:
https://www.test.edu/psp/ps/EMPLOYEE/HRMS/c/PORTAL_ADMIN.PORTAL_OBJ_LIST.GBL
>>>>
https://www.test.edu/psp/ps/HC_REGISTRY/HRMS/c/PORTAL_ADMIN.PORTAL_OBJ_LIST.GBL
HCM SOA Registry Hierarchy:
PeopleSoft Rowset Cache and how is it used by HCM SOA Registry?
- PeopleTools stores application data in a database cache to increase system performance.
- The RowsetCache contains a rowset, and serializes the contained rowset to binary form for speed.
- The RowsetCache class enables you to access this memory structure, created at runtime, and shared by all users.
- As with most PeopleTools objects, RowsetCache objects are cached to memory and file, but they are also cached to the data base.
- Rowset cache is stored in PSCONTDEFN and PSCONTENT.
PeopleBooks Reference:
PeopleSoft Rowset Cache
Using PeopleSoft Rowset Cache
According to Doc ID 658098.1: HCM Service Framework Error When Adding, Updating, or Viewing Person Data in Campus Solutions:
"The meta-data stored in the HC_REGISTRY portal is needed at run-time in order to execute the service. To speed up the retrieval of this meta-data at run-time, it is stored in cache, using PeopleTools Rowset Cache objects."
Layers of HCM Registry Metadata Caching:
App Server Cache*: As with most PeopleTools objects, Rowset cache also gets cached at the app server layer.
HCM Registry - Online Refresh Cache:
SQL to identify if a migration (list of projects) requires a HCM SOA - Refresh Cache:
Based on the HCM SOA Registry Hierarchy diagram above, we can see that all metadata (HC_REGISTRY Portal) and interfaces (App Classes, SQL Views, Messages) are PeopleTools Managed Objects. So the below SQL looks at PeopleTools metadata to figure out if a list of projects (part of a release) contain any objects associated with HCM SOA Registry.
-- Test Value for &&1: 'HCM900_MP11_D','PRJ812865','PRJ889693','PRJ889694','PRJ882259'
/* HCM Registry Objects - Content References */
SELECT 'HC Registry objects (CREFs) found in release. Run SOA Refresh Cache.' AS Found,
'HC_REGISTRY' AS OBJECT,
OBJECTVALUE3 AS PORTAL_OBJNAME
FROM PSPROJECTITEM A
WHERE A.projectname IN (&&1)
AND A.OBJECTTYPE = 55
AND OBJECTVALUE1 = 'HC_REGISTRY'
UNION
/* Interface Objects - Application Classes (Services, Types, Exceptions) */
SELECT 'HCM metadata objects (services/types/exceptions) found in release. Run HCM SOA Refresh Cache.' AS Found,
TO_CHAR(I.PORTAL_ATTR_VAL) AS OBJECT,
I.PORTAL_OBJNAME
FROM PSPRSMSYSATTRVL I
WHERE I.PORTAL_NAME = 'HC_REGISTRY'
AND I.PORTAL_REFTYPE = 'C'
AND I.PORTAL_OBJNAME IN
(SELECT A.PORTAL_OBJNAME
FROM PSPRSMSYSATTRVL A
WHERE A.PORTAL_NAME = 'HC_REGISTRY'
AND A.PORTAL_REFTYPE = 'C'
AND A.PORTAL_ATTR_NAM = 'TYPE'
AND TO_CHAR(A.PORTAL_ATTR_VAL) IN ('EXC', 'SRV', 'TYP')
)
AND I.PORTAL_ATTR_VAL like '%:%'
AND TO_CHAR(I.PORTAL_ATTR_VAL) IN
(SELECT REPLACE (A.objectvalue1 | | ':' | | A.objectvalue2 | | ':' | | A.objectvalue3 | | ':' | | A.objectvalue4, ': ')
FROM PSPROJECTITEM A
WHERE A.projectname IN (&&1)
AND objecttype = 58
)
UNION
/* Interface Objects - Views */
SELECT 'HCM metadata objects (views) found in release. Run HCM SOA Refresh Cache.' AS Found,
TO_CHAR(I.PORTAL_ATTR_VAL) AS OBJECT,
I.PORTAL_OBJNAME
FROM PSPRSMSYSATTRVL I
WHERE I.PORTAL_NAME = 'HC_REGISTRY'
AND I.PORTAL_REFTYPE = 'C'
AND I.PORTAL_OBJNAME IN
(SELECT A.PORTAL_OBJNAME
FROM PSPRSMSYSATTRVL A
WHERE A.PORTAL_NAME = 'HC_REGISTRY'
AND A.PORTAL_REFTYPE = 'C'
AND A.PORTAL_ATTR_NAM = 'TYPE'
AND TO_CHAR(A.PORTAL_ATTR_VAL) IN ('IVW')
)
AND PORTAL_ATTR_NAM = 'IMPLEMENTATION'
AND TO_CHAR(I.PORTAL_ATTR_VAL) IN
(SELECT REPLACE (A.objectvalue1 | | ':' | | A.objectvalue2 | | ':' | | A.objectvalue3 | | ':' | | A.objectvalue4, ': ')
FROM PSPROJECTITEM A
WHERE A.projectname IN (&&1)
AND objecttype = 0
)
UNION
/* Interface Objects - HCM Transformation Maps */
SELECT 'HCM metadata objects (messages) found in release. Run HCM SOA Refresh Cache.' AS Found,
TO_CHAR(I.PORTAL_ATTR_VAL) AS OBJECT,
I.PORTAL_OBJNAME
FROM PSPRSMSYSATTRVL I
WHERE I.PORTAL_NAME = 'HC_REGISTRY'
AND I.PORTAL_REFTYPE = 'C'
AND I.PORTAL_OBJNAME IN
(SELECT A.PORTAL_OBJNAME
FROM PSPRSMSYSATTRVL A
WHERE A.PORTAL_NAME = 'HC_REGISTRY'
AND A.PORTAL_REFTYPE = 'C'
AND A.PORTAL_ATTR_NAM = 'TYPE'
AND TO_CHAR(A.PORTAL_ATTR_VAL) IN ('TRF')
)
AND PORTAL_ATTR_NAM IN ('MSGNAME', 'TO_MSGNAME')
AND TO_CHAR(I.PORTAL_ATTR_VAL) IN
(SELECT OBJECTVALUE1
FROM PSPROJECTITEM A
WHERE A.projectname IN (&&1)
AND A.OBJECTTYPE = 37
);
If the query returns results then we need to run HCM SOA - Refresh Cache. If not, there is no need.
Note: This post is only focusing on the need (if any) for running the HCM SOA - Refresh Cache as a post step to migrations. This does not mean that there would not be other circumstances (outside of migrations) where the Refresh Cache might be necessary.
I have seen many customers (using HCM or Campus Solutions) who run this 'Refresh Cache' on a regular basis and sometimes as a post step to standard migrations. By standard migrations, I mean the weekly/fortnightly migrations which are part of production support release cycles and not necessarily the bundle/patch/upgrade migrations.
What is wrong with running this process regularly as a post step to any migration?
Well, nothing. But of late, I am finding several cases where the 'Refresh Cache' process fails with "RowsetCache::Save: Save failed for xxx/xxx=: unknown error. (2,975)" error particularly since HRMS 9.0 Bundle 26 (last HR bundle for CS).
Since this online/batch cache refresh has been causing a lot of pain, I wanted to find out when it is appropriate to refresh cache (reverse engineering and identifying the events - object changes - that would necessitate a refresh). I ended up building a SQL query to identify whether a migration of a project (say part of weekly/fortnightly production support release) would require a 'HCM SOA - Refresh Cache' just based on the objects it contains.
Before I share the details of the SQL query, I would like to share some of my notes since I did not find any information that was straight forward or easy to understand specifically on HCM Registry - Refresh Cache.
HCM SOA Registry:
HCM SOA Registry, like any other SOA Registry, provides a mechanism for service registration, discovery and binding.
SOA Registry Example:
Reference: Web 2.0 Architectures
HCM SOA Registry:
PeopleBooks Reference: Understanding the HCM Interface Registry
My Process/Flow Diagram: HCM SOA Registry
How do we access HCM SOA Registry?
Main Menu > Set Up HRMS > System Administration > HCM Registry
Service Registry:
Transformation Registry:
Where does HCM SOA Registry data get stored?
According to Doc ID 658098.1: HCM Service Framework Error When Adding, Updating, or Viewing Person Data in Campus Solutions:
"The HCM Service Registry (Set Up HRMS > System Administration > HCM Registry > Service Registry) captures the meta-data about the HCM SOA services: input and output signatures, implementation application classes, web services, component interfaces, etc. This meta-data is stored on the HC_REGISTRY portal (PeopleTools > Portal). This portal contains no other data."
How do we access this HC_REGISTRY portal where the HCM SOA Registry is stored?
- Navigate to Portal – Structure and Content: Main Menu > PeopleTools > Portal > Structure and Content.
- Replace the default portal (EMPLOYEE) with HC_REGISTRY in the URL.
For example:
https://www.test.edu/psp/ps/EMPLOYEE/HRMS/c/PORTAL_ADMIN.PORTAL_OBJ_LIST.GBL
>>>>
https://www.test.edu/psp/ps/HC_REGISTRY/HRMS/c/PORTAL_ADMIN.PORTAL_OBJ_LIST.GBL
HCM SOA Registry Hierarchy:
PeopleSoft Rowset Cache and how is it used by HCM SOA Registry?
- PeopleTools stores application data in a database cache to increase system performance.
- The RowsetCache contains a rowset, and serializes the contained rowset to binary form for speed.
- The RowsetCache class enables you to access this memory structure, created at runtime, and shared by all users.
- As with most PeopleTools objects, RowsetCache objects are cached to memory and file, but they are also cached to the data base.
- Rowset cache is stored in PSCONTDEFN and PSCONTENT.
PeopleBooks Reference:
PeopleSoft Rowset Cache
Using PeopleSoft Rowset Cache
According to Doc ID 658098.1: HCM Service Framework Error When Adding, Updating, or Viewing Person Data in Campus Solutions:
"The meta-data stored in the HC_REGISTRY portal is needed at run-time in order to execute the service. To speed up the retrieval of this meta-data at run-time, it is stored in cache, using PeopleTools Rowset Cache objects."
Layers of HCM Registry Metadata Caching:
App Server Cache*: As with most PeopleTools objects, Rowset cache also gets cached at the app server layer.
HCM Registry - Online Refresh Cache:
- Main Menu > Setup HRMS > System Administration > HCM Registry > Service Registry > Refresh Cache
- Refreshes HCM SOA Rowset Cache (PSCONTDEFN and PSCONTENT) with current HCM SOA metadata (HC_REGISTRY Portal).
- Main Menu > Setup HRMS > System Administration > HCM Registry > Refresh HCM SOA Portal Access.
- Refreshes security on HC_REGISTRY portal.
- Makes sure all folders and CREFs in HC_REGISTRY are PUBLIC.
- DOES NOT add any roles. Adding a default role to all users is now a deprecated functionality. Refer: Doc ID 1370324.1 - FAQ on Refresh HCM SOA Portal Access - Question 1.
- Refreshes HCM SOA Rowset Cache (PSCONTDEFN and PSCONTENT) with current HCM SOA metadata (HC_REGISTRY Portal).
Based on the HCM SOA Registry Hierarchy diagram above, we can see that all metadata (HC_REGISTRY Portal) and interfaces (App Classes, SQL Views, Messages) are PeopleTools Managed Objects. So the below SQL looks at PeopleTools metadata to figure out if a list of projects (part of a release) contain any objects associated with HCM SOA Registry.
-- Test Value for &&1: 'HCM900_MP11_D','PRJ812865','PRJ889693','PRJ889694','PRJ882259'
/* HCM Registry Objects - Content References */
SELECT 'HC Registry objects (CREFs) found in release. Run SOA Refresh Cache.' AS Found,
'HC_REGISTRY' AS OBJECT,
OBJECTVALUE3 AS PORTAL_OBJNAME
FROM PSPROJECTITEM A
WHERE A.projectname IN (&&1)
AND A.OBJECTTYPE = 55
AND OBJECTVALUE1 = 'HC_REGISTRY'
UNION
/* Interface Objects - Application Classes (Services, Types, Exceptions) */
SELECT 'HCM metadata objects (services/types/exceptions) found in release. Run HCM SOA Refresh Cache.' AS Found,
TO_CHAR(I.PORTAL_ATTR_VAL) AS OBJECT,
I.PORTAL_OBJNAME
FROM PSPRSMSYSATTRVL I
WHERE I.PORTAL_NAME = 'HC_REGISTRY'
AND I.PORTAL_REFTYPE = 'C'
AND I.PORTAL_OBJNAME IN
(SELECT A.PORTAL_OBJNAME
FROM PSPRSMSYSATTRVL A
WHERE A.PORTAL_NAME = 'HC_REGISTRY'
AND A.PORTAL_REFTYPE = 'C'
AND A.PORTAL_ATTR_NAM = 'TYPE'
AND TO_CHAR(A.PORTAL_ATTR_VAL) IN ('EXC', 'SRV', 'TYP')
)
AND I.PORTAL_ATTR_VAL like '%:%'
AND TO_CHAR(I.PORTAL_ATTR_VAL) IN
(SELECT REPLACE (A.objectvalue1 | | ':' | | A.objectvalue2 | | ':' | | A.objectvalue3 | | ':' | | A.objectvalue4, ': ')
FROM PSPROJECTITEM A
WHERE A.projectname IN (&&1)
AND objecttype = 58
)
UNION
/* Interface Objects - Views */
SELECT 'HCM metadata objects (views) found in release. Run HCM SOA Refresh Cache.' AS Found,
TO_CHAR(I.PORTAL_ATTR_VAL) AS OBJECT,
I.PORTAL_OBJNAME
FROM PSPRSMSYSATTRVL I
WHERE I.PORTAL_NAME = 'HC_REGISTRY'
AND I.PORTAL_REFTYPE = 'C'
AND I.PORTAL_OBJNAME IN
(SELECT A.PORTAL_OBJNAME
FROM PSPRSMSYSATTRVL A
WHERE A.PORTAL_NAME = 'HC_REGISTRY'
AND A.PORTAL_REFTYPE = 'C'
AND A.PORTAL_ATTR_NAM = 'TYPE'
AND TO_CHAR(A.PORTAL_ATTR_VAL) IN ('IVW')
)
AND PORTAL_ATTR_NAM = 'IMPLEMENTATION'
AND TO_CHAR(I.PORTAL_ATTR_VAL) IN
(SELECT REPLACE (A.objectvalue1 | | ':' | | A.objectvalue2 | | ':' | | A.objectvalue3 | | ':' | | A.objectvalue4, ': ')
FROM PSPROJECTITEM A
WHERE A.projectname IN (&&1)
AND objecttype = 0
)
UNION
/* Interface Objects - HCM Transformation Maps */
SELECT 'HCM metadata objects (messages) found in release. Run HCM SOA Refresh Cache.' AS Found,
TO_CHAR(I.PORTAL_ATTR_VAL) AS OBJECT,
I.PORTAL_OBJNAME
FROM PSPRSMSYSATTRVL I
WHERE I.PORTAL_NAME = 'HC_REGISTRY'
AND I.PORTAL_REFTYPE = 'C'
AND I.PORTAL_OBJNAME IN
(SELECT A.PORTAL_OBJNAME
FROM PSPRSMSYSATTRVL A
WHERE A.PORTAL_NAME = 'HC_REGISTRY'
AND A.PORTAL_REFTYPE = 'C'
AND A.PORTAL_ATTR_NAM = 'TYPE'
AND TO_CHAR(A.PORTAL_ATTR_VAL) IN ('TRF')
)
AND PORTAL_ATTR_NAM IN ('MSGNAME', 'TO_MSGNAME')
AND TO_CHAR(I.PORTAL_ATTR_VAL) IN
(SELECT OBJECTVALUE1
FROM PSPROJECTITEM A
WHERE A.projectname IN (&&1)
AND A.OBJECTTYPE = 37
);
If the query returns results then we need to run HCM SOA - Refresh Cache. If not, there is no need.
Note: This post is only focusing on the need (if any) for running the HCM SOA - Refresh Cache as a post step to migrations. This does not mean that there would not be other circumstances (outside of migrations) where the Refresh Cache might be necessary.
Awesome!!
ReplyDeleteAny idea what PS roles will result in the cache clearance being successful.
ReplyDeleteThis does not have any special access as far as I know. So the regular page access should be sufficient.
DeleteThese are the permission list:
select * from PSAUTHITEM where BARITEMNAME = 'RUN_HMCR_BLDPERM';
You can try the following roles:
HR System Administrator
HR Technical/Admin