Changes to Long Columns and Unicode in PT8.48

Unicode and the use of Long columns in PeopleSoft have been subjects that I have grumbled about in the past. However, Oracle now appear to have addressed them in PeopleTools 8.48. Unfortunately I cannot find any guiding documentation., and I stumbled across these changes by accident. However, if they work, they are both things that customers should know about.

PeopleSoft deliver two scripts in the PT8.48 distribution in %PS_HOME%/scripts: upgradedboptions_enable.sql and upgradedboptions_disable.sql, but I cannot find any documentation.

They set PSSTATUS.DATABASE_OPTIONS to 2 and 0 respectively. This one setting controls both features.

Unicode

I found the following in the platform advisory note: Operating System, RDBMS & Additional Component Patches Required for Installation on PT 8.48 under RDBMS Patches for 10g.

"Note#3: Required INIT.ORA Parameters for building 'Unicode' databases with PT8.48 and Enterprise application releases 9 or later...
Note. If it is your intention to create a Unicode DB for an application release 9 or later database, then the following init.ora parameter is mandatory. The PeopleSoft Unicode implementation for PT8.48 no longer triples the VARCHAR2 datatype columns. Instead we rely on an Oracle feature called CHARACTER LENGTH SEMANTICS. This parameter is not needed for non-unicode DB's or for pre-9 unicode application databases.

NLS_LENGTH_SEMANTICS=CHAR "

However, this advice is not in the same note for PeopleTools 8.49. Tests have shown PeopleTools 8.48 no longer trebles the length of string columns if PSSTATUS.UNICODE_ENABLED = 1 and PSSTATUS.DATABASE_OPTIONS is set to 2 (I haven't tested 8.49 yet, but the installation guide now says that you now 'must' use Character Semantics for Unicode databases).

This change will be a huge improvement for PeopleSoft customers who need Unicode support. The length checking constraints can dramatically increase the overhead of parsing SQL in Oracle (I have seen as much as a 300% increase in parse time - see my Unicode Oddity presentation).

LONG -v- CLOB columns

If PSSTATUS.DATABASE_OPTIONS is set 2, columns that would normally be created as type LONG, are now created as CLOBs.

Use of CLOBs has a number of advantages.

It is not possible to partition a table with a LONG, but it is permitted with a CLOB.

It permits the use of SQL string functions on CLOBs that cannot be used on LONGs

It is possible to move data in CLOBs across database links (or at least the first 32K).

In Oracle 9i, it was not possible to use Streams to replicate a table with a long column. This restriction was removed in 10g. However, most PeopleSoft customer upgrading to PeopleTools 8.48 will also take the opportunity to upgrade to Oracle 10g

Examples

The settings in these fields on the table PSSTATUS control the generation by Application Designer and Data Mover of the column list [TBCOLLIST] in the create table DDL model.

PSSTATUS. DATABASE _OPTIONS	PSSTATUS. UNICODE _ENABLED	Create Table DDL
0	0	CREATE TABLE PS_DMK (EMPLID VARCHAR2(11) NOT NULL ,EMPL_RCD SMALLINT NOT NULL ,EFFDT DATE ,DESCRLONG LONG VARCHAR) ...
0	1	CREATE TABLE PS_DMK (EMPLID VARCHAR2(33) NOT NULL CHECK(LENGTH(EMPLID)<=11) ,EMPL_RCD SMALLINT NOT NULL ,EFFDT DATE ,DESCRLONG LONG VARCHAR) ...
2	either 0 or 1	CREATE TABLE PS_DMK (EMPLID VARCHAR2(11) NOT NULL ,EMPL_RCD SMALLINT NOT NULL ,EFFDT DATE ,DESCRLONG CLOB) ...