Skip to main content
ExLibris

Knowledge Assistant

BETA
  • Subscribe by RSS
  • Back
    Aleph

     

    Ex Libris Knowledge Center
    1. Search site
      Go back to previous article
      1. Sign in
        • Sign in
        • Forgot password
    1. Home
    2. Aleph
    3. Knowledge Articles
    4. cir-78 doesn't delete the z309 records it should

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

    1. Last updated
    2. Save as PDF
    3. Share
      1. Share
      2. Tweet
      3. Share
    1. Description
    2. Resolution

     

    • 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
    View article in the Exlibris Knowledge Center
    1. Back to top
      • cir-51: Replacement charge not included on Lost Summary bill
      • cir-78 doesn't give any indication of its progress
    • Was this article helpful?

    Recommended articles

    1. Article type
      Topic
      Language
      English
      Product
      Aleph
    2. Tags
      1. cir-78
      2. contype:kba
      3. Prod:Aleph
      4. SQL
      5. z309
    1. © Copyright 2025 Ex Libris Knowledge Center
    2. Powered by CXone Expert ®
    • Term of Use
    • Privacy Policy
    • Contact Us
    2025 Ex Libris. All rights reserved