Skip to main content
  • Subscribe by RSS
  • Ex Libris Knowledge Center

    Deleting orphaned HOL records (HOL records whose items have been deleted)

    • Article Type: General
    • Product: Aleph
    • Product Version: 20, 21, 22, 23

    We are in the process of extensive deselection of materials, especially duplicate copies. One of our librarians has been deleting items and just was told today that she should also delete the holding at the same time.

    Is there a process to run to identify and delete orphaned holdings?


    We suggest that you run the following SQL to locate non-deleted HOL records which do not have any items linked to them:

    > s+ abc50
    SQL> select substr (z103_rec_key,6,9) from abc60.z103 where substr (z103_rec_key,1,5) = 'ABC60' minus select Z30_HOL_DOC_NUMBER_X from abc50.z30;


    a. spot-check to make sure that the HOL records retrieved by the above query *are* ones which should be deleted.   Note:  You could have HOL records, with 866 fields, for serials where there are *intentionally* and correctly no items.  If that is the case, you will need add additional logic to include only HOL records which had items but no longer do and which no longer serve any purpose.  

    b. The following is an SQL script for doing this....

    set echo off
    set pause off
    set feed off
    set heading off
    spool hol_spool.lst
    select substr (z103_rec_key,6,9) from abc60.z103 where substr (z103_rec_key,1,5) = 'ABC60' minus select Z30_HOL_DOC_NUMBER_X from abc50.z30;
    spool off;

    The preceding will create a file "hol_spool.lst".

    c. Use the method described in the article " Appending library to each line in file of record numbers (make "p_ret_01 format" " to append "ABC60" to each doc number in the hol_spool.lst file.

    d. connect to abc60 and run the print-03 Service, using the hol_spool file from step c as input, to create an output file containing the HOL records to be deleted

    e. still connected to abc60, run the manage-18 Service with the output from step d as the Input File.  For "Procedure to run" specify  "Update current records in the database"   and for "If Updating Current Records" specify "Delete a record".
    [Note:  this step e previously suggested using the manage-21 Service + ue_01, but we have not be able to get ue_01 to delete records which have "DEL $$aY".]

    See also, the articles " HOL record deletion "  and  " How to delete bib, item, and HOL records **MASTER RECORD**  ".


    • Article last edited:  9-Oct-2018
    • Was this article helpful?