Troubleshooting guide - specific record not being removed from ah/rh table and should be
Article ID: 193270
Updated On:
Products
Issue/Introduction
Sometimes it looks like a record should be removed from the database during maintenance (oftentimes called the reorg process). This guide should show you how to start troubleshooting these whether the utilities are run from a maintenance job, workflow, or from the UI or REST API beginning in 24.3.0.
Resolution
Should it be deleted - determine which record you think should be deleted?
Get the runid of the record that it is believed should be deleted. Then run the following against the AWA database:
select ah_timestamp4, ah_archiveflag, ah_deleteflag from ah where ah_idnr = [runid that is believed should be deleted]
ah_timestamp4 is when this task was fully finished and perhaps removed from the activities window (deactivated) - please note if it is still in the activities window, it will not be marked for deletion
ah_archiveflag set to 0 means the task has not been archived (1 means it has been archived)
ah_reorgflag set to 0 means the task has not be set for deletion (1 means it has been flagged for deletion)
Archive
Is archive supposed to be run?
Check for no_archive_check on reorg and unload ini. If no_archive_check is set to 0 in either, the answer is "yes" and go on to "Is archive run for this client? Otherwise (when no_archive_check is set to 1 in both), move to "Reorg" section
Is archive run for this client?
Check archive logs (ucybdbar_log_xx.txt) - note that if there are multiple clients, the utilities may be run for multiple clients. Check for U00031113 Archiving in client '[client]' started. If there is a log with this, then the archive is run for the client (also check that it ends normally - no errors in log)
If there is no log, then it is possible the archive is not running for this client and the runid will not be removed from the database
Should this record (runid) have archive flag set to 1 (does it meet the parameters to be archived?)
Look at archive log for a line like "Statistics (AH/RH): '[x]' days ('[Date in format YYYY.MM.DD HH:MM:SS]')"
Using the ah_timestamp4 entry for the runid collected at the intro to this section, is ah_timestamp4 less than ": '[number of days to keep]' days ('[date]')
If not, the runid should not be archived
If yes, then run the following against the db:
select count(*) from oh where ah_oh_idnr in (select ah_oh_idnr from ah where ah_idnr = [run number you think should be deleted])
if the result is less than "keep last [x] Reports per object", the runid should not be archived
if the results is greater than "keep last [x] Reports per object": then run:
select ah_timestamp4, ah_idnr from ah where ah_oh_idnr in (select ah_oh_idnr from ah where ah_idnr = [run number you think should be deleted]) order by ah_timestamp4
if this record is in the top [x], then it should not be archived
If it still should have been archived, go to "Further investigation" section
Reorg
Is the task still active?
Run the following select statement:
select eh_name, eh_client from eh where eh_ah_idnr = [runid that is believed should be deleted]
If any rows are returned from the eh table, the task is still active and will not be set to be deleted.
Is reorg run for this client?
Check reorg logs (ucybdbre_log_xx.txt) - note that if there are multiple clients, the utilities may be run for multiple clients. Check for U00032189 Reorganization started in client '[client]' started. If there is a log with this, then the reorg is run for the client (also check that it ends normally - no errors in log)
If there is no log, then it is possible that the reorg is not running for this client and the runid will not be removed from the database
Was archive flag set to 1 if record should have been archived (see "Archive" section)
Check database for ah_archiveflag on this runid
If ah_archiveflag is set to 0, runid should not be reorg'd
Should this record (runid) have delete flag set to 1 (does it meet the parameters to be reorged?)
Look a the reorg log for a line like "Reports (RH/RT): '[x]' days ('[Date in format YYYY.MM.DD]')"
Using the ah_timestamp4 entry for the runid collected at the intro to this section, is ah_timestamp4 less than ": '[number of days to keep]' days ('[date]')
If not, the runid should not be deleted
If yes, then run the following against the db:
select count(*) from oh where ah_oh_idnr in (select ah_oh_idnr from ah where ah_idnr = [run number you think should be deleted])
if the result is less than "keep last [x] Reports per object", the runid should not be deleted
if the result is greater than "keep last [x] Reports per object": then run:
select ah_timestamp4, ah_idnr from ah where ah_oh_idnr in (select ah_oh_idnr from ah where ah_idnr = [run number you think should be deleted]) order by ah_timestamp4
if this record is in the top [x], then it should not be deleted
If it still should have been archived, go to "Further investigation" section
Unload
Is unload run for reorganization?
Check the unload logs (ucybdbun_log_xx.txt) - note that if the unload is run for other functions than just reorganization, the log for reorg may have been overwritten. Check for line like:
U00037111 Starting reorganization for client: 'ALL' If there is a log with this, then the unload is run (also check that it ends normally - no errors in log)
If there is no log, then it is possible that the unload for reorganization is not running and the runid will not be removed from the database
Further Investigation - still think it should be removed?
run select ah_archiveflag, ah_deleteflag, ah_timestamp4, ah_client from ah where ah_idnr = [run id] on this record
set db=4 trace on archive utility
run archive utility
run select ah_archiveflag, ah_deleteflag, ah_timestamp4, ah_client from ah where ah_idnr = [run id] on this record
set db=4 trace on reorg utility
run reorg utility
run select ah_archiveflag, ah_deleteflag, ah_timestamp4, ah_client from ah where ah_idnr = [run id] on this record
set db=4 trace on unload utility
run unload utility
run select ah_archiveflag, ah_deleteflag, ah_timestamp4, ah_client from ah where ah_idnr = [run id] on this record
if the record is still there gather and send to support:
archive log and trace (if applicable)
reorg log and trace
unload log and trace
results of sql above
Feedback
