PeopleSoft – Kill Application Engine (Gently)

Smart Panda - Oracle Development

Smart Panda - PeopleSoft in the CloudKill Application Engine (AE)

It amazes me that in the world of these high end databases that simple data handling can go for a crap simply because data was bulk loaded into the system.  Usually during big changes to an environment: data conversions, upgrades,  archiving or other aggressive changes, data will become out of sync with the statistics that are stored on the tables.

What are Database Statistics? In a nut shell it is data about data or  simply metadata. Oracle statistics is metadata about your data. There are several kinds of statistics in Oracle mainly: Object statistics, System statistics and fixed table statistics.

So if you have bad statistics your Application Engine may not be able to execute code correctly.  It will simply appear to be hung.  I have seen processes run for days and it will never return a result because of bad statistics.  After updating the statistics those exact same processes that ran for days will run in seconds, sometimes faster.  Okay, so now the catch is you have to “kill” the process in order for the process to be re-ran and pick up the correct statistics. This isn’t the easiest process in the world to do, because if you try to just cancel the job through the process scheduler it may or may not cancel nicely. However, if you “kill” the SQL at the database level that is hung the application engine for go to ERROR, and you can restart the AE from the last committed point that it executed to.  This is much cleaner than any other method I have found, especially if you have a huge amount of stuff already done by the application engine prior to running into this issue.  Upgrades are notorious for this, which is why they have “update statististics” steps all throughout the upgrade process now.

So in Oracle SQL we need to find the SQL that is causing the issue:

select * from v$session where program like ‘%PSAESRV%’;  –See SQL running by Application Engine

select * from v$sql where sql_id = ‘SQL_ID from v$Session’;  — Confirm you have the write SQL

alter system kill session ‘{sid},{serial#}’;

It may take a few minutes to find the SQL causing the problems, but don’t worry, it will still be running after you find it.  Once it is killed, the AE will go to error, and you can restart it.  It will start up at the last commit point, which you can usually see if you query the AE Control table:

select * from ps_aeruncontrol;

Rerun your statistics for the schema or for the tables in question.

exec dbms_stats.gather_schema_stats(‘SYSADM’, cascade=>true);

Good luck out there!

PeopleBooks Guides: Install & Upgrade

Smart Panda - PeopleBooks

PeopleBooks Guides: The Search for Install & Upgrade Guides

Smart Panda - PeopleBooks GuidesAlmost 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.

PeopleBooks Guides Install Upgrade Guides

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.

Change Assistant – Skipping Steps

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.

Change Assistant Fails to Run SQR – Upgrades

As most of you know, we maintain and patch many systems.  As such we have to constantly keep upgrading our systems to be able to upgrade our clients.  We tend to setup servers that can be used for patching and maintenance to multiple systems at the same time.  For example the latest PeopleTools release just came out and we were patching a Microsoft SQL Server setup and then switched over to patching an Oracle based system.

When using the PUM you must have the Oracle Clients installed, and so I typically say to customers that are on Microsoft SQL Server to install the PeopleTools with both the MSS & Oracle license codes as each install will give you different code.  I typically install them in separate directories so that unique files like “rdms.sqc.”  However sometimes I cut corners in the interest of saving time and space and I ran into an issue with Change Assistant the other day.

When running SQR from change assistant I was getting failures that didn’t appear to make any sense.  I went to the %PS_HOME%\bin\sqr\ora\binw and ran sqrw.exe individually and got a missing dll error:  bclw64.dll not found.  As it turned out I had installed the MSS 8.54 toolset to my PS_HOME, and then installed the 8.54.09 minor MSS & ORA files to my PS_HOME.  Thus I was missing several ORA unique system files.  After a couple re-installs I have 8.54 base ORA&MSS and 8.54.09 ORA&MSS filesets in my PS_HOME and SQR now runs from the ORA & MSS folders without an issue.  (Yes, I manually adjust the rdms.sqc and other files as necessary).

Happy Patching…..