Thursday, February 17, 2011

Unlocking Temporary Table Instances from Deleted Process Requests

In a previous blog entry, I discussed how to detect whether an Application Engine process had run out of non-shared Temporary Table instances by querying the message log table for the entries written when this happens.

This can happen because processes that have locked all available non-shared table instances are currently processing. However, if a restartable Application Engine program terminates with an error, the tables remain locked to that process instance so that it can be restarted. If that process instance is neither cancelled nor restarted so that it subsequently runs to success then the tables will remain locked, and in time the Process Scheduler archive process may purge the process request record.

However, that archive process does not unlock the temporary table instances (by deleting the registration of the table with the instance from the record AETEMPTBLMGR), and once the process request has been deleted it cannot be cancelled in the Process Monitor. Therefore, there is no way for an operator to unlock the tables via the web interface.  Over time this can build up and new processes are forced to use the shared instance.  In my experience this is more common on development and test environments than production.

The problem is that is a silent and insidious problem that can gradually degrade batch performance.  Forcing processes onto the shared instance forces them to delete rather than truncate working storage tables.  So there is additional redo, and high water marks are not reset.  Concurrent processes can contend as they share the same physical table.  There may be more work to preserve read consistency, and more activity on the undo segment.

The way to prevent this problem is, of course, to make sure you have sufficient instance of temporary table, but before you create new instances check that there an no existing instances that should not still be locked. 

Update 7.3.2011: Phil's comment below correctly reminds me that PeopleSoft provided a way to remove locks without issuing SQL directly at PeopleTools -> Application Engine -> Review Temp Table Usage.  This component clears records from PS_AETEMPTBLMGR, PS_AERUNCONTROL and PS_AERUNCONTROLPC.  This feature is also described at PeopleSoft Tips & Notes: PS_AERUNCONTROL.

However, you can only remove locks for one process instance at a time.  If the problem has got out of hand, you might still chose to delete the rows from these three table where there is no longer a process request record. 

DELETE FROM ps_aetemptblmgr t
WHERE NOT EXISTS(
 SELECT 'x'
 FROM psprcsrqst r
 WHERE r.prcsinstance = t.process_instance)
/
DELETE FROM ps_aeruncontrol t
WHERE NOT EXISTS(
 SELECT 'x'
 FROM psprcsrqst r
 WHERE r.prcsinstance = t.process_instance)
/
DELETE FROM ps_aeruncontrolpc t
WHERE NOT EXISTS(
 SELECT 'x'
 FROM psprcsrqst r
 WHERE r.prcsinstance = t.process_instance)
/ 

5 comments :

Unknown said...

Why would you not use 'manage abends'
to achieve this rather than submitting SQL to update a production database? I have used this successfully ever since it was made available (can't rememeber which release of PeopleTools this became available ).
After a process is purged from the process scheduler, the 'manage abends' page will allow you to clean the temp table locks, without the need to update tables directly. It will not allow you to do that if the process is still available via process monitor as you should cancel it from there instead.

David Kurtz said...

Absolutely correct, posting updated.

Anonymous said...

to answer Phil's comment: We have had issues where the many processes in our batch schedules can quickly use up the temp spawned instances and the performance using the base table is unacceptable. So we created a process that we insert into the schedule to "manage" the temp tables dynamically. We incorporate the RUNSTATUS = '9' to ensure that we are operating only on successfully completed processes that still have locked tables.

John

Anonymous said...

responding to John's comment - 'We incorporate the RUNSTATUS = '9' to ensure that we are operating only on successfully completed processes that still have locked tables.'

We are exactly having that scenarios, successful processing leaving the temp tables locked on the GL_JEDIT2 process(Edits called from JGen). Why should happen in the first place. I have just started researching but any information will be appreciated.

Thanks,
Arvind.

Anonymous said...

We had an issue yesterday where the grid wouldn't display the abends on the manage abends page. I have view only access to the page and our administrator has update/all access to the page but neither of us were able to see the grid. We were both able to see it in our development instance.

What would cause this issue?