Impossible to drop a partition after the ILM switch

Products

CA Automic Workload Automation - Automation Engine

Issue/Introduction

It is impossible to drop a partition after the ILM switch and the following errors can be found in the PWP logs

20191121/104022.152 - U00005346 ILM: IDS has been released.

20191121/104022.161 - U00005367 Checking consistency of table IPH.

20191121/104022.165 - U00005368 Detected difference in table 'AFC' partition '000224' (Position: '0005'). DB-MINVALUE '0074085000', IPH-MINVALUE '0074085000', DB-MAXVALUE '0099469999', IPH-MAXVALUE '0099469999', DB-TABLESPACE 'UC4_DATA', IPH-TABLESPACE 'UC4_IBM15'

20191121/104022.166 - U00005368 Detected difference in table 'AFC' partition '000226' (Position: '0007'). DB-MINVALUE '0135099000', IPH-MINVALUE '0135099000', DB-MAXVALUE '0162550999', IPH-MAXVALUE '0162550999', DB-TABLESPACE 'UC4_DATA', IPH-TABLESPACE 'UC4_IBM02'

.....

20191121/104022.184 - U00005368 Detected difference in table 'AJPOP' partition '000232' (Position: '0013'). DB-MINVALUE '0296488000', IPH-MINVALUE '0296488000', DB-MAXVALUE '0302096999', IPH-MAXVALUE '0302096999', DB-TABLESPACE 'UC4_DATA', IPH-TABLESPACE 'UC4_IBM08'

20191121/104022.188 - U00005368 Detected difference in table 'AJPVA' partition '000224' (Position: '0005'). DB-MINVALUE '0074085000', IPH-MINVALUE '0074085000', DB-MAXVALUE '0099469999', IPH-MAXVALUE '0099469999', DB-TABLESPACE 'UC4_DATA', IPH-TABLESPACE 'UC4_IBM15'

Environment

AE Environment 12.2 on Oracle

Cause

In the system view UC_TAB_PARTITIONS the Tables for 224 is referenced for UC4_DATA, which is the default Tablespace of UC4, which is normally not configured for partition!?).
In IPH table the same partition UC4_DATA has been assigned to one of the Tablespaces dedicated to ILM UC4_ILM15 ()
ISTMT table is empty

The problem is that a RunID turnaround was done by the system:

20191127/000028.036 - U00005305 ILM: 'ALTER TABLE UC4.AH SPLIT PARTITION "233" AT (323378000) INTO (PARTITION "233", PARTITION "234" TABLESPACE UC4_ILM10)'
20191127/000028.332 - U00005305 ILM: 'ALTER TABLE UC4.AH SPLIT PARTITION "218" AT (2035900000) INTO (PARTITION "TEMP" TABLESPACE UC4_ILM10, PARTITION "218")'
20191127/000028.548 - U00005305 ILM: 'ALTER TABLE UC4.AH MERGE PARTITIONS "234", "TEMP" INTO PARTITION "TEMP"'
20191127/000029.186 - U00005305 ILM: 'ALTER TABLE UC4.AH RENAME PARTITION "TEMP" TO "234"'
20191127/000029.216 - U00005305 ILM: 'UPDATE IPH SET IPH_MaxValue = 0000000323377999 WHERE IPH_Partition = 0000000233 AND IPH_Table = 'AH''
20191127/000029.231 - U00005346 ILM: IDS has been released.

The problem is following SQL:
ALTER TABLE UC4.AH MERGE PARTITIONS "234", "TEMP" INTO PARTITION "TEMP"
... because no tablespace is used the default tablespace is taken -> UC4_DATA -> because of the missing tablespace clause the ILM check leads to:

20191127/000029.426 - U00005368 Detected difference in table 'AH' partition '000224' (Position: '0005'). DB-MINVALUE '0074085000', IPH-MINVALUE '0074085000', DB-MAXVALUE '0099469999', IPH-MAXVALUE '0099469999', DB-TABLESPACE 'UC4_DATA', IPH-TABLESPACE 'UC4_ILM15'
20191127/000029.426 - U00005368 Detected difference in table 'AH' partition '000226' (Position: '0007'). DB-MINVALUE '0135099000', IPH-MINVALUE '0135099000', DB-MAXVALUE '0162550999', IPH-MAXVALUE '0162550999', DB-TABLESPACE 'UC4_DATA', IPH-TABLESPACE 'UC4_ILM02'

Resolution

Workaround: If you suspect that you are affected by this issue please open an incident with Broadcom Support

A problem has been fixed where in ILM change/drop partition when we were in the middle and created a new TEMP partition and merged this, only in this case missing the tablespace name for TEMP partition.

The fix is expected to delivered in the following version.

Automation Engine 12.4.0 is expected to be delivered in September 2020
Automation Engine 12.1.8 is expected to be delivered in May 2020
Automation Engine 12.2.5 - Available
Automation Engine 13.3.2 - Avaialble

Additional Information

In the system view UC_TAB_PARTITIONS the Tables for 221 is referenced for UC4_DATA (the default TBS of UC4, which is normally not configured for partition!?).
In IPH table the same partition has been assigned to one of the TBs dedicated to ILM UC4_ILM15 ()
ISTMT table is empty