ETL9.1: TL_TIMEADMIN Deadlock In TL_VALD_MAIN With Payroll Advice Print DDP003 On TL_COMPLEAV_TBL. (Doc ID 2024489.1)

Last updated on FEBRUARY 09, 2017

Applies to:

PeopleSoft Enterprise HCM Time and Labor - Version 9.1 to 9.1 [Release 9]
Information in this document applies to any platform.

Goal

TL_TIMEADMIN deadlocks with DDP003 (Payroll Advice Print SQR) on TL_COMPLEAV_TBL. DDP003 has a read-only lock on the table and causes the -911 abend of Time Admin. This issue began after applying Bundles 11 – 15 and upgrading from PeopleTools 8.52.11 to 8.53.13.Need a resolution.


2015-04-15-12.14.17.036723: Starting TL_VALD_MAIN - Time Validation. (13507,13)
Validation Stream (461026, 703085) Initiated. (13504,203)
File: /vob/peopletools/src/psappeng/aedebug.hSQL error. Stmt #: 1625 Error Position: 0 Return: 8601 - [IBM][CLI Driver][DB2/AIX64] SQL0911N The current transaction has been rolled back because of a deadlock or timeout. Reason code "68". SQLSTATE=40001
 (SQLSTATE 40001) -911
Failed SQL stmt: DELETE FROM PS_TL_COMPLEAV_TBL WHERE COMP_LEAV_IND IN ('CERN','CTKN') AND DUR >=( SELECT MIN(START_DT) FROM PS_TL_TA_BATCH4 I WHERE I.PROCESS_INSTANCE=2177714 AND I.EMPLID=PS_TL_COMPLEAV_TBL.EMPLID AND I.EMPL_RCD=PS_TL_COMPLEAV_TBL.EMPL_RCD) AND EXISTS ( SELECT 1 FROM PS_TL_TA_BATCH4 I WHERE I.PROCESS_INSTANCE=2177714 AND I.EMPLID=PS_TL_COMPLEAV_TBL.EMPLID AND I.EMPL_RCD=PS_TL_COMPLEAV_TBL.EMPL_RCD) AND EXISTS ( SELECT 1 FROM PS_TL_IPT14 A WHERE A.PROCESS_INSTANCE = 2177714 AND A.EMPLID = PS_TL_COMPLEAV_TBL.EMPLID AND A.EMPL_RCD = PS_TL_COMPLEAV_TBL.EMPL_RCD AND A.TRC IN ( SELECT B.TRC FROM PS_TL_TRC_TBL B WHERE B.COMP_LEAV_IND IN ('CERN', 'CTKN') AND B.EFFDT = ( SELECT MAX(T1.EFFDT) FROM PS_TL_TRC_TBL T1 WHERE T1.TRC = B.TRC AND T1.EFFDT Process 2177714 ABENDED at Step TL_TA000900.DD000.Step010 (SQL) -- RC = 8601 (108,524)
 

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms