Date to Timestamp Conversion during PeopleTools Upgrade

This blog posting describes a script to convert Oracle date columns to Timestamps as used from PeopleTools 8.50 but only rebuilding those indexes that reference those columns, rather than drop and recreate every index in the system, thus producing a significant saving of time during the upgrade.

(A longer version of this article is available on my website)

I am working on a PeopleSoft upgrade project.  We are going from PeopleTools 8.49 to 8.53.  One of the things that happens is that some date columns in the Oracle database become timestamps.

Timestamps were introduced by Oracle in version 10g of the database, and provide the ability to store times accurate to the nanosecond (although the default is microsecond). Dates are accurate to the whole second.

There are 3 types of temporal column in PeopleSoft as defined on PSDBFIELD.  Prior to PeopleTools 8.50 they all become Oracle date columns in the database.  However, from PeopleTools 8.50; Time and DateTime fields are built as TimeStamp columns.if bit 5 of PSSTATUS.DATABASE_OPTIONS (value 32) is set.

PeopleTools Field Type		Database Column Type
		PeopleTools <= 8.49	PeopleTools >= 8.50
4	Date	DATE	DATE
5	Time	DATE	TIMESTAMP
6	DateTime	DATE	TIMESTAMP

Timestamps must be handled differently to dates in SQL.  Some date arithmetic must be done differently, in particular the difference between two timestamps is a timestamp rather than a number of days.  Therefore this setting also controls how PeopleCode date macros expand on Oracle.

During the upgrade, PeopleSoft Change Assistant simply alters all the Time and DateTime columns from dates to timestamps.  This generally works well.  The data value doesn't appear to get longer, so the block doesn't run out of room leading to row migration, and so it isn't necessary to rebuild every table that is affected.

However, there are some limitations. If the column being converted to a timestamp falls into one of the following categories you will get an error.

• The column is a key column in a function-based index.
• The table or index is partitioned by the column.

The functional key index issue has not affected many customers because the upgrade template drops all the indexes before altering the tables and rebuilding them again.

However, dropping and recreating all these indexes can be very time consuming and increases the duration of the outage required to perform the upgrade.  This has been my incentive to find a better way.

Function-Based Indexes
PeopleSoft Application Designer defines some key and search fields as descending.  The rows in components and the results of search dialogue are sorted on the key fields in the order specified.  Application Designer then indexes these columns in descending order (prior to PeopleTools 8 and since PeopleTools 8.47).  If any column in an Oracle index is in descending order the index is created as a function-based index.  Consequently, there can be a lot of descending indexes in a PeopleSoft system!  HR systems are particularly affected because many tables are effective-dated, and the field EFFDT is usually a descending key field.

It is not possible to alter a column to a timestamp if it appears anywhere in a function-based index. You get the following error:

ORA-30556: functional index is defined on the column to be modified 

Partitioning
Partitioning is not something that you usually encounter in a vanilla PeopleSoft system, but it can be added by customisation.  You generate the necessary DDL yourself if you want to use it. However, from PeopleTools 8.51 Application Designer will preserve existing partitioning.

In the system on which I am working, when partitioned the audit tables by AUDIT_STAMP which is a DateTime field.

ORA-14060: data type or length of an table partitioning column may not be changed 

We have had no alternative but to rebuild these tables and repopulate the data. This has also dealt with all locally partitioned indexes.

We have also found that we have one global index partitioned on a timestamp.

ORA-14061: data type or length of an index partitioning column may not be changed 

We also have had to drop this index in order to alter the table.

My Approach
We have had no alternative but to rebuild and repopulate our partitioned audit tables which are partitioned by a DateTime field.  However, that is what we did when we first partitioned them.  The scripts are very similar to those generated by Application Designer.  The table is renamed, a new one is built, and the data is copied.  In our case these scripts are built with a PL/SQL utility.  This also addressed the need to rebuild the locally partitioned indexes..

To minimize the number of indexes which must be rebuilt I have written a PL/SQL script (http://www2.go-faster.co.uk/scripts/gfc_desc_timestamp_index.sql) that:

• identifies the indexes that need to be dropped.
• captures the DDL to recreate the indexes using DBMS_METADATA and stores it in a table,
• drops the indexes,
• alters the columns that cannot be altered with the index in place,
• recreates the index.

The script successfully handles partitioned function-based indexes.

On this particular HR system we only rebuilt about 400 indexes instead of over 10000. Now the standard PeopleSoft upgrade template can be run without dropping or recreating any further indexes.

Minimum Number of Recycling Server Processes

When I rebuilt my demo system (some while ago) with PeopleTools 8.52, I noticed a new message generated by ubbgen in PeopleTools 8.52 when the minimum number of recycling servers is set to 1.

WARNING: PSAPPSRV, PSSAMSRV, PSQRYSRV, PSQCKSRV, PSPPMSRV and PSANALYTICSRV are configured with Min instance set to 1. 
To avoid loss of service, configure Min instance to at least 2.

What Produces the Message?
ubbgen is the PeopleSoft utility that merges the template file (psappsrv.ubx) with the configuration file (psappsrv.cfg) file to produce the Tuxedo configuration file (psappsrv.ubb) and the environment file (psappsrv.env). It is invoked by psadmin during Tuxedo domain configuration.

ubbgen -t psappsrv.ubx -c psappsrv.cfg -o psappsrv.ubb -v psappsrv.val -q y -u PUBSUB=n/QUICKSRV=n/QUERYSRV=n/JOLT=y/JRAD=n/DBGSRV=n/RENSRV=n/MCF=n/PPM=n/ANALYTICSRV=n

The message is produced at this time.

Recycling Servers
Several servers in a PeopleSoft application Server domain recycle after they have handled a number of services. Recycling is a PeopleSoft behaviour and not a Tuxedo behaviour.  It is controlled by the Recycle Count parameter in the PeopleSoft configuration file (psappsrv.cfg).  This parameter is not referenced in the template file (psappsrv.ubx).

[PSAPPSRV]
;=========================================================================
; Settings for PSAPPSRV
;=========================================================================

;-------------------------------------------------------------------------
; UBBGEN settings
Min Instances=2
Max Instances=3
Service Timeout=0

;-------------------------------------------------------------------------
; Number of services after which PSAPPSRV will automatically restart.
; If the recycle count is set to zero, PSAPPSRV will never be recycled.
; The default value is zero.
; Dynamic change allowed for Recycle Count
Recycle Count=1000

PeopleSoft first started using BEA Tuxedo (as it was then) in PeopleTools 6 to remote call Cobol processes in the Financials product.  The Application Server was introduced in PeopleTools 7.  PSAPPSRV had recycling from the first release.  Legend has it that the engineers at Tuxedo where horrified when they heard that PeopleSoft had introduced recycling to resolve problems created by dynamic memory allocation and deallocation by the Panel Processor (now known as the component Processor).

Tuxedo servers are supposed to be robust, long lived and not require to be regularly restarted.  Hence the server restart functionality in Tuxedo is only designed to be invoked in the rare occasions when a server process crashes.  Server processes are started by the Restart Server (restartsrv), and only one process can be started concurrently.

The minimum of 2 applies to the following servers because they can both recycle and be configured to spawn additional instances on demand PSAPPSRV, PSANALYTICSRV, PSSAMSRV, PSQCKSRV, PSQRYSRV, PSPUBHND, PSSUBHND, PSBRKHND.  In the delivered configuration file for the developer domain, recycling is disabled for several servers by setting the recycle count to zero.  However, the message is produced by ubbgen irrespective of the value of recycle count.

The message handler servers can be set to have just a single instance, without producing any warning.  They only consume messages from the dispatcher processes so their temporary disappearance will not cause user errors.

What Happens When The Only Server Recycles?
Tuxedo server processes consume service requests placed on queues.  When a server starts up, it advertises its services on the Bulletin Board.  Tuxedo processes that submit requests (mostly the JSH processes, but also the message dispatcher processes, the Process Scheduler) look up on the Bulletin Board where a service is advertised and place it on the appropriate queue.

When a server process performs an orderly shutdown (as it does during a recycle) it removes the adverts for its services (the command is unadvertise).  If a process crashes the Bulletin Board Liaison process (BBL) detects the crash and cleans the Bullentin Board. When all services advertised on a queue have been unadvertised, the queue is also removed from the Bulletin Board.  If the process submitting the service request cannot find any server advertising a service it generates an error.  

074001.GO-FASTER-6!JSH.3124.5736.-2: JOLT_CAT:1043: "ERROR: tpacall() call failed, tperrno = 6"

This is why the minimum is 2.  If you recycled the only server process, it is possible for someone to produce this error (see also Minimum Number of Application Server Processes).

Should I set the minimum higher than 2?
The number of services handled by different server processes on the same queue is usually uneven because the service is handled by the first free server.  Therefore it is rare for the processes will reach the recycle count simultaneously, but it can still happen.  Even in a quiet system that doesn't have sufficient activity to justify 3 PSAPPSRVs, I prefer to set the minimum number of servers to at least 3. 

Debugger
NB: If the debugger process is enabled then PSADMIN will force the minimum and maximum number of PSAPPSRV processes to at least 2.  ubbgen will actually update psappsrv.cfg.

Warning:  PSAPPSRV Min Instances too small, setting to 2 for debugger.