Skip to main content
ExLibris
  • Subscribe by RSS
  • ExLibris Dev

    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 ^= '02' 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';

    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 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
    //doorbell.io feedback widged