EGL: DB2 Performance Issue on Journal Edit with Combo Edit On (Doc ID 753221.1)

Last updated on SEPTEMBER 14, 2016

Applies to:

PeopleSoft Enterprise FIN General Ledger - Version 8.8 SP1 to 9 [Release 8.8 to 9]
Information in this document applies to any platform.
*** Checked for currency on 6 January 2016 ***

Symptoms

Editing 6 journals can take over 2 hours or not complete at all with combo edits on. The following query is the problem. It is generated  in the FS_CEDT_ECMB.4uAnchCT.uAnchCDT  AE program.
Using Fund Code as the anchor. On DB2UDB version 8. 3.7 million rows in the PS_JRNL_LN table and 600,000 in COMBO_DATA_TBL.

UPDATE PS_JRNL_LN
SET JRNL_LINE_STATUS = 'a'
WHERE BUSINESS_UNIT = 'XXXX'
AND PROCESS_INSTANCE = 126
AND LEDGER = 'ACTUALS'
AND JRNL_LINE_STATUS = '0'
AND EXISTS (SELECT 'X'
FROM PS_COMBO_DATA_TBL
WHERE SETID = 'XXXX'
AND PROCESS_GROUP = 'XXXX'
AND COMBINATION IN ('XXXX')
AND VALID_CODE= 'V'
AND PS_JRNL_LN.JOURNAL_DATE BETWEEN EFFDT_FROM AND EFFDT_TO
AND PS_JRNL_LN.FUND_CODE = FUND_CODE)

Cause

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