search cancel

Long running Select from RH during reorg appears to be redundant


Article ID: 227360


Updated On:


CA Automic One Automation


After reorg was finished with long running SELECT for the AH table, now the following statement is taking a long time and there are many of them:

SELECT RH_AH_Idnr,RH_Type, RH_TimeStamp4, RH_ArchiveFlag, AH_OType, AH_SType, AH_Status, AH_Idnr, AH_OH_Idnr,AH_TimeStamp4 FROM RH, AH WHERE RH_AH_Idnr = AH_Idnr AND AH_Client = ? and AH_TimeStamp4 IS NOT NULL and AH_TimeStamp4 < ? and RH_DeleteFlag = 0  ORDER BY AH_OH_Idnr, AH_TimeStamp4 DESC

Its running this repeatedly and if the AH records have already been flagged for deletion, isn't this now redundant?


Release : 12.3

Component :


The running of the RH SQL statement is often a redundant step in the reorg that is there by default, but can be avoided.  According to the docs:

Performance Improvements:
Only reorganize reports if they should be reorganized before the statistical records. If this option is deactivated, the AE DB Reorg utility reorganizes reports together with the statistical records.

Basically this means that if you are using the reorganization to keep the same amount of days of data for both statistics and reports, you don't need to reorganize reports.  The unload will automatically delete the same reports that are tied to the AH table.  This is because there's no way to view reports that do not have a related statistic/AH record.

The only reason to reorganize reports at the same time as statistics is if you were to keep something like 15 days of reports and 30 days of statistics.  So to speed up the reorg, set the reorg of the RH table to false (uncheck in the reorg GUI) and it will stop this redundant step from being done.

This can be done one of two ways:

GUI (Preferred) - in the reorg GUI, double-click on the row that has the Table value of "Reports (RH/RT)".  The window that opens, be sure that "Report reorganization" is unchecked

The other way which is not preferred, but may be necessary in a situation where the GUI is not available is to:
Log into AWI in the client where it should be turned off
Go to the <No Folder>
Open the UC_UTILITY_REORG variable object
Update the row that has the Key "RH" to something like:
FALSE 0030 FALSE 0030

This will do the equivalent of unchecking the checkbox in the GUI.