cir-78 doesn't delete the z309 records it should
- Product: Aleph
- Product Version: 20, 21, 22, 23
- Relevant for Installation Type: Dedicated-Direct, Direct, Local, Total Care
Description
After running cir-78 to delete z309 records older than yyyymmdd, you find that many records which should have been deleted have not been.
Resolution
Please consult the articles
* Which z309 records does cir-78 delete?
* v21/v22 cir-78 runs longer than previously, not removing z309 records it should
* z309 action types 01 and 51 are not deleted by p_cir_78 -- or by anything else
If, after consulting those articles, you feel that more z309's should have been deleted, then consider the following SQL which deletes the z309 records in a more logical fashion....
Almost all z309 log transactions (renewal, return, recall, letter, etc.) stem from a particular loan transaction. When the item is returned as a type '03' "regular return", the associated z309 log entries will not be of interest for very long. (When the item is returned as a type '06' Late return or type '07' Late recall return, the situation could be more complicated.)
The z36_number (the "loan number"), which is included in the z309 record as the z309_rec_key_2, is a unique number for each loan transaction. The following SQL looks for z309's connected -- via their z309_rec_key_2 -- to a z309 action type '03' regular return transaction older than a particular date (in this case, "20120301"). It deletes those z309's. Then it is followed by an SQL which deletes the z309_action type '03' z309 itself....
SQL> delete from z309 where z309_action ^= '03' and z309_action < '50' and z309_rec_key_2 in (select z309_rec_key_2 from z309 where z309_action = '03' and z309_date_x < '20120301');
SQL> delete from z309 where z309_action = '03' and z309_date_x < '20120301';
The offline loan (type '02') and return (type '04') logic is similar:
SQL> delete from z309 where z309_action ^= '04' and z309_action < '50' and z309_rec_key_2 in (select z309_rec_key_2 from z309 where z309_action = '04' and z309_date_x < '20120301');
SQL> delete from z309 where z309_action = '04' and z309_date_x < '20120301';
And the self-check loan (type '09') and return (type '10') logic is also similar:
SQL> delete from z309 where z309_action ^= '10' and z309_rec_key_2 in (select z309_rec_key_2 from z309 where z309_action = '10' and z309_date_x < '20120301');
SQL> delete from z309 where z309_action = '10' and z309_date_x < '20120301';
As you can see, the above '03' SQL excludes "cash" transactions (those >= '50'). In the case of those transactions, different logic needs to be used: If the transaction has been fully paid or waived, its log entry should be considered removable; if it hasn't, it perhaps shouldn't be....
SQL> delete from z309 where z309_action = '51' and z309_rec_key_2 in (select z309_rec_key_2 from z309 where z309_action = '52' and z309_date_x < '20120301');
SQL> delete from z309 where z309_action = '51' and z309_rec_key_2 in (select z309_rec_key_2 from z309 where z309_action = '54' and z309_date_x < '20120301');
SQL> delete from z309 where (z309_action = '52' or z309_action = '54') and z309_date_x < '20120301';
Though z309's for hold requests are usually far less numerous than the preceding types, you may want to delete them for privacy reasons. The following will do that:
Hold request deleted
SQL> delete from z309 where z309_action ^= '72' and z309_action > '69' and z309_request_number in (select z309_request_number from z309 where z309_action = '72' and z309_date_x < '20120301');
SQL> delete from z309 where z309_action = '72' and z309_date_x < '20120301';
And Hold request picked up:
SQL> delete from z309 where z309_action ^= '77' and z309_action > '69' and z309_request_number in (select z309_request_number from z309 where z309_action = '77' and z309_date_x < '20120301');
SQL> delete from z309 where z309_action = '77' and z309_date_x < '20120301';
Though we believe these SQLs will remove the vast majority of delete-able z309's, we suggest running it *in combination* with cir-78, which may delete some others. (Note: Since cir-78 deletes the action 52 and 54 records without deleting the corresponding 51's, this SQL should be run *before* the cir-78 for "cash" records.)
- Article last edited: 16-Mar-2017