2706367 - Default Deletion job and Archiving job is slow on JAVA PO PI systems - SAP ASE for Business Suite

• Archiving and "Default Deletion" jobs are slow on SAP JAVA AS - PO PI systems.
• "IdocDBTableCleanup" and/or "IdocDBTableCleanupPeriodic" jobs which run as part of  / alongside the "Default Deletion" job are slow.
• The underlying slow sql queries are:

SELECT XI_MSG_NO FROM XI_IDOC_IN_MSG WHERE OUT_TIME <= convert(datetime,'10/10/18',3)
AND XI_MSG_NO NOT IN (SELECT MSG_ID FROM BC_MSG WHERE DIRECTION= 'OUTBOUND')
AND XI_MSG_NO NOT IN (SELECT PARENT_MSG_ID FROM BC_MSG WHERE DIRECTION = 'OUTBOUND' AND "PARENT_MSG_ID" IS NOT NULL)

SELECT XI_MSG_NO FROM XI_IDOC_IN_MSG m
WHERE m.OUT_TIME between '2018-07-18T00:00:00.000' and '2018-07-18T23:59:59.000'
AND XI_MSG_NO NOT IN (SELECT PARENT_MSG_ID FROM BC_MSG WHERE DIRECTION = 'OUTBOUND' AND PARENT_MSG_ID IS NOT NULL)
AND XI_MSG_NO NOT IN (SELECT MSG_ID FROM BC_MSG WHERE DIRECTION = 'OUTBOUND')

SELECT "OTX"."AUTO_NUMBER" "OTX_AUTO_NUMBER_autoNumber" FROM "XI_IDOC_OUT_TREX" "OTX" WHERE "OTX"."AUTO_NUMBER" NOT IN (SELECT DISTINCT "OMSG"."REF_NUMBER" FROM "XI_IDOC_OUT_MSG" "OMSG") AND "OTX"."AUTO_NUMBER" NOT IN (SELECT DISTINCT "OACK"."REF_NUMBER_TREX" FROM "XI_IDOC_OUT_ACK" "OACK")

• On the ASE database, processes are seen to block on queries accessing tables BC_MSG and BC_MID_CON_TRFC .
• NW JAVA PO/PI tables grow large in size if the Deletion jobs do not run in a timely manner, as expected.

Read more...

• SAP Adaptive Server Enterprise (ASE) 15.7 for Business Suite
• SAP Adaptive Server Enterprise (ASE) 16.x for Business Suite
• SAP NetWeaver JAVA AS

SAP Adaptive Server Enterprise 15.7 ; SAP Adaptive Server Enterprise 16.0 ; SAP Adaptive Server Enterprise 16.1 ; SAP NetWeaver Application Server for Java 7.1 ; SAP NetWeaver Application Server for Java 7.2

BC_MID_CON_TRFC, BC_MSG, JAVA, PO, PI, XI, archiving, "Default deletion job", "deletion job", IdocDBTableCleanup, IdocDBTableCleanupPeriodic , XI_IDOC_IN_MSG, iDoc , KBA , BC-DB-SYB , Business Suite on Adaptive Server Enterprise , BC-XI-CON-IDO , Idoc Adapter J2EE , BC-XI-CON-MSG , Messaging System , Problem