cir-17 Does Not Delete Certain Hold Requests

• Article Type: General
• Product: Aleph
• Product Version: 21

Problem Symptoms:
* Report / Delete Expired Hold Requests (cir-17) doesn't delete certain hold requests
* There are no entries in the Z370 (Title Requests) table.
* There are more than 800 entries with past end dates in the Z37 (Requests).

Cause:
The required jobs were not being run in some of the customer sublibraries.

Resolution:
Analysis of this situation showed

1. This SQL showed that only 504 of the more than 800 expired requests had Z37_END_REQUEST_DATE's:
SQL> select count(*) from z37 where Z37_END_REQUEST_DATE < '20091022' and Z37_END_REQUEST_DATE ^= 0;

2. This SQL showed that there were not any type "B" or "T" requests, but that there were 341 z37's with z37_status "S":
SQL> select z37_request_type, z37_status, count(*) from z37 where Z37_END_REQUEST_DATE < '20091022' and Z37_END_REQUEST_DATE ^= 0 group by z37_request_type, z37_status;
Z Z COUNT(*)
H A 141
H S 341
H W 22
That left 141 status "A" and 22 status "W" requests which should have been deleted.

3. This SQL showed the sublibraries connected to the requests for these items:
SQL> select z30_sub_library, count(*) from z30, z37 where z37_status ^= 'S' and Z37_END_REQUEST_DATE < '20091022' and Z37_END_REQUEST_DATE ^= 0 and substr (z37_rec_key,1,15) = z30_rec_key group by z30_sub_library;
Z30_S COUNT(*)
BAFC 17
BRES 15
NIFA 109

4. grep's for "p_sub_library" in the $alephe_scratch p_cir_17 logs showed that cir-17 was not being run for these sublibraries.
>> grep p_sub_library *p_cir_17.*

5. Required action: run these jobs for the sublibraries found by the grep
a. Hold Shelf Report (cir-06)
b. cir-17