PeopleTools table PSSTATUS

PeopleTools table PSSTATUS stores significant information's like current database PeopleTools release version, database owner id, Date TimeStamp that is referred by the Purge Cache process, Flag that indicates whether the database is Unicode or Non-Unicode and Database options. In this doodle I will explain the values stored in some of the fields in PSSTATUS table and how this information is used by the processes.

TOOLSREL - This fields stores the current PeopleTools release like 8.53. When applying patches to a certain PeopleTools release (eg: original version 8.49.08 upgraded to 8.49.18) the TOOLSREL field won't be affected.

OWNERID - This field stores the database owner of the PeopleSoft objects, by default this is SYSADM. You have one and only one OWNERID for a DBID (PSDBOWNER).

 

UNICODE_ENABLED -  This field store value 1 or 0. Value 1 indicates this database is UNICODE database.

 

LASTREFRESHDTTM - This field stores date timestamp based on which any objects cache that is older than the value of LASTREFRESHDTTM are purged. If this value is updated to the current date and time and cache purge process is initiated then entire cache will be purged.

 

DATABASE_OPTIONS - This field is used to record the data type changes. This column value changes during upgrade process, and is a critical column for the PeopleSoft tools functionality. This column is introduced from PeopleTools 8.47 onwards.

 

Below are the possible values for the DATABASE_OPTIONS field in PSSTATUS.

 

1: DB2/zOS LOBs in use
2: Oracle LOBs in use
4: SQL Server Varchar in use
8: SQL Server RTRIM in use (only used during upgrade process)
16: Oracle TimeStamp Meta-SQL in use (8.48 and 8.49 only) 
32: Oracle TimeStamp in use (8.50 and beyond) - From PeopleSoft 8.50 onwards (tools related) the DATETIME and TIME data types are changed to TIMESTAMP.  This is a feature introduced from PT850 (Irrespective of Apps Version)

 

These are all bit values they can be combined. For example, all Oracle customers that upgrade to 8.50 and App version 9.0 and above will have a DATABASE_OPTIONS of 34 (2+32), meaning they're using both LOBs and TimeStamp. This is because App versions 9.0 and above use Oracle LOBs and Tools versions 8.50 and above use Oracle Timestamps.

 

The 16 value is meant for Oracle customers who have customized their database to use Timestamp before they were officially supported.

 

The build process using Application Designer or Data Mover refers this value during build process and determine the data type that should be used during build is decided based on this value.For example if the value is zero VARCHAR fields are built as LONG and if the value is 32 they are built as CLOBs or BLOBs.