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

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.

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!

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

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.

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.

When 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:

%PS_CFG_HOME%/webserver/{domain}/applications/peoplesoft/PORTAL.war/

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.