PeopleTools: Long/Lob & DataTime Datatype Issues

Recently, I was working on a PeopleTools 8.44 to 8.54 Upgrade and ran into some major issues with the fact that the database so far behind in so many areas.  On the Oracle platform PeopleSoft changed the way it handled Long datatype fields and also how it handled Date & Time datatype fields.  The  part that I found was very difficult was some of the scripts that are more current don’t seem to take into account that coming from 8.44 might be an issue in handling these different datatypes.

Basically, in order to make this upgrade work I did a 8.44 to 8.53 upgrade first, and made sure that the LONG TO LOB datatype conversion was ran before the upgrade and that the DATABASE_OPTIONS field on PSSTATUS was set to 34 datatype setting.  Otherwise I ran into all sorts of weird issues with datatypes.

DATE and DATETIME, LONG and LOB fields in PeopleSoft and Oracle database

In PeopleTools 8.49 and earlier, Datetime and time fields are stored as DATE datatype in Oracle database. For example, you could see a DATETIME or a TIME field in Application designer might show as a DATE field in the Oracle database. Similar issues could be seen with LONG, CLOB and LOB datatype fields as well.From PeopleSoft PeopleTools 8.50 and higher, the TIMESTAMP datatype is now supported for TIME and DATETIME field types. These datatype changes are mandatory, and the DATE datatype will no longer be used for the  DATETIME and DATE datatype fields.Updating the Database for Timestamp:
This is a separate step performed during an application or tools upgrade. This step invokes a SQL statement called UPGDBOPTIONS_ENABLETIMESTAMP.SQL from PS_HOME/scripts folder. This DMS script updates the database to indicate that the new TIMESTAMP datatype is now enabled. If the upgrade is performed through PeopleSoft Change Assistant(as it should be), the step is displayed and can be run only if the upgrade is from PeopleSoft PeopleTools 8.49 or earlier.

What does this step do?
Why the DATE, TIME and DATETIME formats chosen in PeopleSoft application designer show as DATE field in the database and how is this controlled?
This feature is controlled by the DATABASE_OPTIONS field in  PSSTATUS record.
DATABASE_OPTIONS field can store four different values:

  1. 0 – PeopleSoft is using the old datatypes (Long fields and Legacy Unicode implementation).
  2. 2 – PeopleSoft is using the new Oracle datatypes supported with PT8.48 (LOBs and CLS Unicode implementation).
  3. 32 – PeopleSoft is using old datatypes, (LONGs and Legacy Unicode implementation) with TIMESTAMP post PT8.50 conversion enabled.
  4. 34 – PeopleSoft is using the new Oracle datatypes supported with PT8.48 (LONGs and Legacy Unicode implementation) with TIMESTAMP post PT8.50 conversion enabled.
PeopleTools Version and Features supported:
  1. Datatypes supported with PT8.48 – LOBs and CLS Unicode implementation
  2. Datatypes supported with PT8.50 – TIMESTAMP enabled
Depending upon which version of PeopleTools is installed and what features are enabled, an appropriate number from 0, 2, 32 and 34 will be updated while running this step.

Copy PeopleSoft File Attachments

I was doing some work with file attachments in PeopleSoft over the past couple of weeks and I have to say PeopleSoft’s ability to handle attachments is flat out bad.  However, it does work.  The client I was working with found that storing documents in the database was starting to consume an extreme amount of space which made the database have issues.

In PeopleTools 8.54 there is a batch copy attachment feature that lets you move attachments from one URL to another.  So you can define a URL for sftp and another for the database record and copy attachments to/from those URLs. In 8.53 there is just an online page for this, however, if you are doing a copy of more than about 1000 documents you will want to switch to a batch mode.

I was also fascinated with the orphan cleanup process that looks at the referencing record and the storage record and clears out any non-referenced storage record documents.  PeopleSoft does this by looking for the FILE_ATTACH_SBR subrecord in the record definitions and generates a list of attached file names and then compares that to the records associated with FILE_ATTDET_SBR subrecord.  This process cleared out about 25,000 documents that were not referenced.

When doing the actual copy from the database to the sftp (which was local), I did it in chunks by identifying the correct reference record and specifying that in the file record array for the copyattachment function.  As I had one record that had almost 200,000 documents, which if you don’t specify the reference records it will look at all the records with the FILE_ATTACH_SBR subrecord which in the Finance 9.2 environment I am in was 108 records, however only 8 had references.

I also built an index for the record with the actual document which was unique on the fields:  ATTACHSYSFILENAME, VERSION, FILE_SEQ, which is different then the delivered index thus allowing the look up to be much more efficient.

App Designer prompts for record tablespace name

PeopleSoft is a cross platform application, and some databases use tablespaces and some do not.  This error will commonly occur when using Microsoft SQL Server, and to fix this navigate to:

PeopleTools > Utilities > Administration > PeopleTools Options

Uncheck Platform Compatibility Mode

You should now be able to save your record without being prompted for tablespaces what are not present in your world!

Data Mover – Invalid Signon User

Just working on another upgrade today and ran into one of those gotcha’s that can make you scratch your head for hours.  The upgrade job failed trying to do a datamover script:

PeopleTools 8.53.05 – Data Mover
Copyright (c) 2013 PeopleSoft, Inc.
All Rights Reserved
Message Set Number: 0
Message Number: 0
Message Reason: Invalid User ID and password for signon. (0,0)
PSDMTX Error: signon

PeopleTools Permission List Tab
Funny thing is I can log into Application Designer, and I have the application server and process schedulers all running with the same user.  I check to make sure that my connect id still has the correct access and make sure that no accounts are locked, and everything looks great.  I even have PeopleSoft Administrator as one of my roles and still no dice.

If you have ever ventured to look at the PeopleTools Tab in the Permission List Component, you will see there is an option for Data Mover Access.  Make sure you have a permission list with this option turned on.  I have a custom permission list just for the upgrade and I added it to that, and bingo I was back in business.

Compare – Generating Output

When doing an upgrade or any kind of patching you will often times want to compare the object you are importing with what is currently housed within your database.  PeopleSoft standard compare will generate a list of everything that is different between the data being imported and the data within the database.  You can get creative with these settings, but one thing that is SUPER helpful is to generate the output so that the compare reports can be viewed via the internet browser.

compare_upgrade_optionsWhen in the compare options, if you select the Report Options tab you can select Generate Browser Reports, and the compare reports will be generated to the Report Output Directory which in the example is d:\temp\compares\{PROJECTNAME}.  If you want to change this directory you need to cancel out of the compare and going into the Tools Menu -> Options, and change the output directory.

After the compare is complete you will have a full set of output files in your output directory which you can put onto your PIA webservers public html document home directory:


I typically make a compare folder here and insert the various compares there and then in the compare folder make a basic index page to link to the CompareViewer.html that was originally in the compare reports output folder root.

Lastly, you might find these reports don’t show up in the browser,  I find they are best viewed using Firefox, but IE works however, your security settings may cause you some issues.