PeopleBooks Guides: The Search for Install & Upgrade Guides

Almost on a daily bases questions arise as to what upgrade paths are supported and often the answer to that question changes.  My Oracle Support (MOS) has the answer, however, that answer can be extremely difficult to find.

A little hidden gem of the PeopleBooks Online Reference homepage is they have a link to the current PeopleBooks Guides for Install and Upgrade pages.

Simply expand the category you want and it will link you to the MOS support page associated the guide you are looking for.

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 a PeopleTools upgrade using Change Assistant from 8.44 to 8.54 recently, and have found that upgrading to 8.54 can be a pinch problematic mainly because of the 32bit to 64bit change-over.  So I have found it easier to do two upgrades:  8.44 to 8.53 and then 8.53 to 8.54.

One interesting problem I ran into was I reverted a failed attempt and missed a step and I had an upgrade template thinking I was going from 8.44 to 8.53 but the environment thought my database was at 8.54, so as change assistant went through the script it started skipping steps.

I ended up having to go back to the database level and set the TOOLSREL on PSSTATUS to 8.44 and rebuild the environment in Change Assistant.  Then create a new job with the updated environment and the upgrade template, then I changed the database back to the correct level it was at and marked all the completed steps in the template complete and ran the steps that were being skipped. Change Assistant will read the Tools Release level from PTSTATUS table when the Change Assistant job builds from the template and if that level is different then when the job starts any step is not at the right level will be skipped in when Change Assistant processes through the job.

Today, I was working on a PeopleTools upgrade for a client from 8.51 to 8.53.08 and ran into a failure when doing the Coverting PeopleTools Objects step of the upgrade.  When running the PeopleTools Data Conversion process I got a strange series of errors and then an error messaging that looked like UPGPTHASH failed. The messages appeared to be orphaned Application Engine PeopleCode, but the failure appeared to be with the Hashing.  I ran UPGPTHASH from the command line and it completed successfully but interestingly enough reported the invalid objects just like when PTUPGCONVERT ran it.

I decided to run a sysaudit on the database and found that I had PeopleCode-1, PeopleCode-2, PeopleCode-3, PeopleCode-6 and PeopleCode-7 errors in the database.  I will work on figuring out why they were there tomorrow but for today, I executed the steps from the data integrity guide from PeopleBooks to delete the invalid entries:

PeopleCode-1:

DELETE FROM PSPCMNAME WHERE NOT EXISTS (SELECT ‘X’FROM PSPCMPROG B WHERE B.OBJECTID1 = PSPCMNAME.OBJECTID1 AND B.OBJECTVALUE1 = PSPCMNAME.OBJECTVALUE1 AND B.OBJECTID2 = PSPCMNAME.OBJECTID2 AND B.OBJECTVALUE2 = PSPCMNAME.OBJECTVALUE2 AND B.OBJECTID3 = PSPCMNAME.OBJECTID3 AND B.OBJECTVALUE3 = PSPCMNAME.OBJECTVALUE3 AND B.OBJECTID4 = PSPCMNAME.OBJECTID4 AND B.OBJECTVALUE4 = PSPCMNAME.OBJECTVALUE4 AND B.OBJECTID5 = PSPCMNAME.OBJECTID5 AND B.OBJECTVALUE5 = PSPCMNAME.OBJECTVALUE5 AND B.OBJECTID6 = PSPCMNAME.OBJECTID6 AND B.OBJECTVALUE6 = PSPCMNAME.OBJECTVALUE6)

PeopleCode-2:

DELETE FROM PSPCMPROG WHERE NAMECOUNT <> 0 AND NOT EXISTS (SELECT ‘X’FROM PSPCMNAME B WHERE PSPCMPROG.OBJECTID1 = B.OBJECTID1 AND PSPCMPROG.OBJECTVALUE1 = B.OBJECTVALUE1 AND PSPCMPROG.OBJECTID2 = B.OBJECTID2 AND PSPCMPROG.OBJECTVALUE2 = B.OBJECTVALUE2 AND PSPCMPROG.OBJECTID3 = B.OBJECTID3 AND PSPCMPROG.OBJECTVALUE3 = B.OBJECTVALUE3 AND PSPCMPROG.OBJECTID4 = B.OBJECTID4 AND PSPCMPROG.OBJECTVALUE4 = B.OBJECTVALUE4 AND PSPCMPROG.OBJECTID5 = B.OBJECTID5 AND PSPCMPROG.OBJECTVALUE5 = B.OBJECTVALUE5 AND PSPCMPROG.OBJECTID6 = B.OBJECTID6 AND PSPCMPROG.OBJECTVALUE6 = B.OBJECTVALUE6)

PeopleCode-3:

UPDATE PSPCMPROG SET NAMECOUNT = (SELECT COUNT(*) FROM PSPCMNAME C WHERE C.OBJECTID1 = PSPCMPROG.OBJECTID1 AND C.OBJECTVALUE1 = PSPCMPROG.OBJECTVALUE1 AND C.OBJECTID2 = PSPCMPROG.OBJECTID2 AND C.OBJECTVALUE2 = PSPCMPROG.OBJECTVALUE2 AND C.OBJECTID3 = PSPCMPROG.OBJECTID3 AND C.OBJECTVALUE3 = PSPCMPROG.OBJECTVALUE3 AND C.OBJECTID4 = PSPCMPROG.OBJECTID4 AND C.OBJECTVALUE4 = PSPCMPROG.OBJECTVALUE4 AND C.OBJECTID5 = PSPCMPROG.OBJECTID5 AND C.OBJECTVALUE5 = PSPCMPROG.OBJECTVALUE5 AND C.OBJECTID6 = PSPCMPROG.OBJECTID6 AND C.OBJECTVALUE6 = PSPCMPROG.OBJECTVALUE6)

PeopleCode-4, PeopleCode-5, PeopleCode-6:

Open the PeopleCode program in Application Designer and correct the invalid reference

PeopleCode-7:

DELETE FROM PSPCMPROG WHERE OBJECTID1 = 104 AND OBJECTID2 = 107 AND OBJECTVALUE2 NOT IN (SELECT APPCLASSID FROM PSAPPCLASSDEFN P WHERE P.PACKAGEROOT = OBJECTVALUE1 AND P.APPCLASSID = OBJECTVALUE2)

Restarted the PTUPGCONVERT Step (F9) and the Conversion process ran without issue.

I also found several cases where it reported that UPGPTHASH will fail where the conversion process will fail because of a license code issue.  I can’t remember when PeopleSoft made the switch over but now when you install the software you are to use the license codes from PeopleSoft License Code Page. Often if your database has been around awhile the license code stored on PSOPTIONS.LICENSE_CODE will be different then whats on the License Code Page.  You can update the license code on PSOPTIONS, using SQL:  just note that the license code should be in lower case, no spaces, and no dashes.

I have been working on a couple of upgrades for some clients and have found that doing PeopleTools Upgrades to 8.53 from pre 8.48 that I have encountered an error while using SQL Server 2012 and the SQLNCLI11 native client during the upgrade.  The error occurs in the PTUPGCONVERT Application Engine while it is generating new Services for Integration Broker.

I reported the case to PeopleSoft but got nowhere with the problem.  After looking around with google, I found a common theme that made me try something that shouldn’t work but it did.  I changed the ODBC connection to the database to use the SQL Server 2008 Native Client SQLNCLI10.  I do not encounter this issue when I am running the upgrade on SQL Server 2008.  Once the ODBC is changed I reran the upgrade process and everything worked!

It reports a conversion error which appears to be a NULL gets converted to a long int 2097184 which it is trying to insert into a small integer field and therefore generated an error when it tries to insert the data into a table.