cir-78 doesn't delete the z309 records it should

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