PeopleCode records: PSPCMNAME and PSPCMPROG August 11, 2008Posted by Duncan in PeopleSoft, PeopleTools, SQL.
A widely known tip and a new (at least for me) discovery:
Widely known tip
Most techies who’ve looked under the covers will be aware of PSPCMPROG. It’s the underlying table where PeopleCode is storeed. This isn’t immediately useful however as the actual code itself is stored in the PROGTXT field in binary so it’s not easily accessible. This isn’t an insurmountable issue however as there are a couple of routines to decode the field (an SQR and a Java version, both by David L Price). I’ve not used either method personally, but a colleague has used the SQR version with great success.
For me, this field isn’t quite the most useful on PSPCMPROG. You may have noticed that if you update the PeopleCode on a record, the record properties aren’t updated to reflect the change – I guess because the record definition itself hasn’t changed (although strangely Component and Page PeopleCode do update the timestamp on the corresponding Component/Page definition – so there’s a bit of an inconsistency there). So how do you check when and by whom a piece of Record PeopleCode was last updated? If you check the LASTUPDDTTM and LASTUPDOPRID fields on the PSPCMPROG record via SQL then it’s all recorded there. This snippet of knowledge has saved me (or at least expedited troubleshooting by showing me the correct person to ask about the change) innumerable times.
Lesser known tip
I’ve not really had much reason to look at the PSPCMNAME record in the past, however a colleague and I checked it recently and discovered the RECNAME and REFNAME fields. Every time you create some PeopleCode your code is parsed and a line inserted into PSPCMNAME for every reference to a Tools object. I assume that this is the record searched when using ‘Find Definition References’ within App Designer. However, now we know the SQL table behind this we can therefore query this table to quickly find out objects affected by code in a specified project, for example.
Both the company I currently work for and my previous employer had utilities to export project details to file, printing out settings and details from Tools objects to speed-up the documentation process (and we know how much techies love documenting!). Now we can add to that utility any objects that are affected but which aren’t included in the project.