Recovering accidentally-deleted Acquisitions records

Description

Certain records (orders, invoices and vendors for specific journal titles and multivolume monograph sets) were accidentally deleted from Production.  Their bib records are identified by local note (590) = jstor print. We saved those in the test system as jstortest (saved in $alephe_scratch). The same search in the prod system yields 1067 records in the file jstorprod. 
Can they be recovered somehow?

Resolution

[Note:  If relatively few records are involved -- definitely fewer than 100 and, possibly, fewer than 500 --, then doing this manually, via the GUI, is probably best.  In any case, the batch procedure described below is complicated and should be done in consultation with Ex Libris Support.  Other records which could be involved but are not addressed below are:  z501 (Acq claims), z78 (arrival info), z71 (Order log), z20 (Serial claims), and z16 (Subscription).]

If the records are on the Test server, they might be recovered from there as described below.  If they aren't on Test, but are on a backup of Prod, the Test server could be refreshed with the data from Prod, and then proceed as described below....  

1. Do this Unix command to find the records which are on Test but not on Prod:

> comm -13 jstorprod jstortest > jsdiff 

2. Following the procedure described in the " Recovering records (such as z303) accidentally deleted via SQL " article, create /exlibris/aleph/a23_1/alephm/sql_tab/z68temp, ./z77temp, ./z75temp, ./z601temp, ./z70temp, and ./z72temp files. 

3. Add the following entries to the Test ./xxx50/file_list: 

TAB z68temp  4M 0K TS2D 
TAB z77temp  4M 0K TS2D 
TAB z75temp  4M 0K TS2D 
TAB z601temp 4M 0K TS2D 
TAB z70temp  4M 0K TS2D 
TAB z72temp  4M 0K TS2D 

4. Run util a/17/1 in xxx50 on Test to create the z68temp, z77temp, z75temp, z601temp, z70temp, and z72temp table in Oracle.   Be careful to do this for the znntemp tables, NOT the znn table! 

5. Run the print-03 Service in xxx01, using the jsdiff file from step 1 as input and specifying "Aleph Sequential" output; and ftp the output file to $alephe_scratch on Prod. 

6. Run the manage-70 Service in xxx01 with the BIB-to-ADM option, to read jsdiff (with the xxx01 numbers) as input and output a jsdiffadm with the corresponding ADM numbers. 

7. Run the print-03 Service in xxx50, using the jsdiffadm file as input and specifying "Aleph Sequential" output; and ftp the output file to $alephe_scratch on Prod 

8. Follow the article " How to use ret-01 (record-number) output in SQL processing " to load the jsdiffadm file into xxx50 (as "jerrytemp" and use it as input to SQL 

9. Do SQL like this: 

SQL> insert into z68temp select * from Z68 where substr(z68_rec_key,1,9) in (select substr (supkey,1,9) from jerrytemp); 

SQL> insert into z75temp select * from Z75 where substr(z75_rec_key,1,9) in (select substr (supkey,1,9) from jerrytemp); 

SQL> insert into z601temp select * from Z601 where substr(z601_rec_key_3,1,9) in (select substr (supkey,1,9) from jerrytemp); 

SQL> insert into z77temp select * from Z77 where z77_rec_key in (select substr (z601_rec_key_2,1,35) from z601 where substr (z601_rec_key_3,1,9) in (select substr (supkey,1,9) from jerrytemp) ); 

SQL> insert into z70temp select * from z70 where substr (z70_rec_key,1,20) in (select substr (z75_rec_key_2,1,20) from z75 where substr(z75_rec_key,1,9) in (select substr (supkey,1,9) from jerrytemp) ); 

SQL> insert into z72temp select * from z72 where substr (z72_rec_key,1,20) in (select substr (z75_rec_key_2,1,20) from z75 where substr(z75_rec_key,1,9) in (select substr (supkey,1,9) from jerrytemp) ); 

10. Export the z68temp, z77temp, z75temp, z601temp, z70temp, and z72temp tables using the file-03 Service in xxx50 

11. ftp the ./xxx50/files/znntemp.seqxx files from step 10 to the Prod ./xxx50/files directory 

The next steps would then be done on Prod: 

12. Run manage-18 to load the bib file in $alephe_scratch from the above step 5 into xxx01.   (Note that the b_manage_18_a program rejects updates to deleted records, so this may not work....)   The following parameters need to be specified in the manage-18 submission: 

    Indexing: Full 
    Procedure To Run: Update current records in the database 
    If Updating Current Records: Replace entire record 

The latter two are necessary so that the doc numbers remain the same and are in synch with each other and with the z68, z77, etc., ADM library records. 

13. Run manage-18 to load the adm file in $alephe_scratch from the above step 7 into xxx50.  Use the values shown in step 12 for the Bib submission in the ADM submission.  

14. Do the above steps 2, 3, and 4 on Prod. 

15.  Run the file-04 Service for z68temp, z77temp, z75temp, z601temp, z70temp, and z72temp in the xxx50 library on Prod, speciflying  znntemp as the "Table to Import" and  "replace" as the "Procedure to Run".  

16. Back up the z68, z77, z75, z601, z70, and z72 tables on Prod, running the file-03 Service in xxx50 

17. Do this SQL: 

SQL> insert into z68 select * from Z68temp where z68temp.z68_rec_key not in (select z68_rec_key from z68); 
SQL> insert into z77 select * from Z77temp where z77temp.z77_rec_key not in (select z77_rec_key from z77); 
SQL> insert into z75 select * from Z75temp where z75temp.z75_rec_key_2 not in (select z75_rec_key_2 from z75); 
SQL> insert into z601 select * from Z601temp where z601temp.z601_rec_key not in (select z601_rec_key from z601); 
SQL> insert into z70 select * from Z70temp where z70temp.z70_rec_key not in (select z70_rec_key from z70); 
SQL> insert into z72 select * from Z72temp where z72temp.z72_rec_key not in (select z72_rec_key from z72); 

18. Run the acq-04 Service in xxx50 on Prod to rebuild the z79 Order index.

• Article last edited: 29-May-2018