Description

We want to locate and delete HOL records which have no items linked to them when the bib records they are linked to have other HOL records linked to them which *do* have items.

Resolution

The following SQL script can be used to locate such HOL records:

set echo off
set pause off
set term off
set show off
set feed off
set verify off
set heading off
set pagesize 58

spool z103-no-z30-link

SELECT /*+ DYNAMIC_SAMPLING(2) ALL_ROWS */ unique substr(a.z103_rec_key_1,6,9), substr(a.z103_rec_key,6,9)  from xxx01.z103 a, xxx01.z103 b where
a.z103_lkr_type = 'HOL' and substr(a.z103_rec_key,6,9) not in (SELECT /*+ DYNAMIC_SAMPLING(2) ALL_ROWS */ z30_hol_doc_number_x from xxx50.z30) and
a.z103_lkr_type = 'HOL' and substr(b.z103_rec_key,6,9) in (SELECT /*+ DYNAMIC_SAMPLING(2) ALL_ROWS */ z30_hol_doc_number_x from xxx50.z30) and
substr(a.z103_rec_key_1,6,9) = substr(b.z103_rec_key_1,6,9) order by substr(a.z103_rec_key_1,6,9), substr(a.z103_rec_key,6,9);

spool off
exit

The output from the  above script has the bib record number in the first column and the xxx60 HOL record number in the second. This makes it easier to analyze the records.

But in order to actually delete these HOL records you will need a file with just the HOL record number.  The following SQL SELECT will give you that:

SELECT /*+ DYNAMIC_SAMPLING(2) ALL_ROWS */ unique substr(a.z103_rec_key,6,9)  from xxx01.z103 a, xxx01.z103 b where
a.z103_lkr_type = 'HOL' and substr(a.z103_rec_key,6,9) not in (SELECT /*+ DYNAMIC_SAMPLING(2) ALL_ROWS */ z30_hol_doc_number_x from xxx50.z30) and
a.z103_lkr_type = 'HOL' and substr(b.z103_rec_key,6,9) in (SELECT /*+ DYNAMIC_SAMPLING(2) ALL_ROWS */ z30_hol_doc_number_x from xxx50.z30) and
substr(a.z103_rec_key_1,6,9) = substr(b.z103_rec_key_1,6,9) order by substr(a.z103_rec_key,6,9);

Then you would need to take this output and append "XXX60" to each line, as described in the article "  Appending library to each line in file of record numbers (make "p_ret_01 format"  ".

Then you would use that as input to the print-03 Service to create an export file with these records in it and use that file as input to the manage-18
Service (using its "Delete a record" function).  See the article " Batch HOL record deletion " for information on this.

• Article last edited: 8-Mar-2018