Monday, July 02, 2007

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 coulmns 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 advantanges.


  • 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 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 oppotunity 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
    00CREATE 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) ...

    5 comments :

    Anonymous said...

    Thanks for this info on long/clobness. Looking forward to the 848 upgrade!

    Anonymous said...

    If you already have attachments stored as long raw, how will you convert those to blob?

    Anonymous said...

    Hi Dave,

    good job.

    cu

    Peter B.

    Charlie said...

    Search for CLOB in this doc:
    http://www.salisbury.edu/gullnet/dev/PeopleTools_8.49_Documentation_Issues_and_Corrections_PT849.pdf

    Also, an excerpt entitled "Understanding Converting Database Data Types" in MyOracleSupport ID 750677.1

    Heidi Vermeersch said...

    Is it correct that the unicode scripts are not longer generated if you choose to use CLOB?