How to move item, order, etc., records to new ADM library

Description

Because it's in a different timezone we need to break off a particular sublibrary as a separate ADM library.   (See article How to operate with multiple time zones: sublibrary level? or multi-ADM? ).

How can we do this?

Resolution

Moving records for existing sublibrary(s) to a separate ADM is quite complicated.  The following grep will show all of the tables which might be involved:

> asm copy
> grep SUB-LIBRARY Z*

Z1003:          02 Z1003-SUB-LIBRARY            PICTURE X(5).
Z16:            03 Z16-SUB-LIBRARY          PICTURE X(5).
Z30:          02 Z30-SUB-LIBRARY               PICTURE X(5).                       S
Z301:            03 Z301-SUB-LIBRARY                    PICTURE X(5).
Z305:            03 Z305-SUB-LIBRARY         PICTURE X(5).
Z30H:          02 Z30H-SUB-LIBRARY             PICTURE X(5).
Z30_GROUP_DEFINITION:          02 GROUP-BY-SUB-LIBRARY         PICTURE X(5).
Z31:          02 Z31-SUB-LIBRARY            PICTURE X(5).
Z311:            03 Z311-SUB-LIBRARY         PICTURE X(5).
Z313:            03 Z313-SUB-LIBRARY            PICTURE X(5).
Z313:     02 Z313-ITEM-SUB-LIBRARY         PICTURE X(5).
Z321:          02 Z321-SUB-LIBRARY         PICTURE X(5).
Z35:          02 Z35-SUB-LIBRARY            PICTURE X(5).
Z36:          02 Z36-SUB-LIBRARY            PICTURE X(5).
Z36H:          02 Z36H-SUB-LIBRARY              PICTURE X(5).
Z37:          02 Z37-FILTER-SUB-LIBRARY     PICTURE X(5).
Z37H:          02 Z37H-FILTER-SUB-LIBRARY     PICTURE X(5).
Z38:          02 Z38-FILTER-SUB-LIBRARY     PICTURE X(5).
Z38H:          02 Z38H-FILTER-SUB-LIBRARY     PICTURE X(5).
Z39_HOLDINGS_STATEMENT:           02  Z39-HS-SUB-LIBRARY-CODE         PICTURE X(5).
Z39_HOLDINGS_STATEMENT:           02  Z39-HS-SUB-LIBRARY-NAME         PICTURE X(30).
Z39_HS_ITEM:           02  Z39-HSI-SUB-LIBRARY-NAME     PICTURE X(100).
Z40:          02 Z40-SUB-LIBRARY            PICTURE X(5).
Z403:               03 Z403-SUB-LIBRARY                 PICTURE X(5).
Z403:       03 Z403-RESTRICT-SUB-LIBRARY        PICTURE X(5).
Z43:            03 Z43-SUB-LIBRARY          PICTURE X(5).
Z602:          02 Z602-SUB-LIBRARY-X.
Z602:             03 Z602-SUB-LIBRARY  OCCURS 500 PICTURE X(5).
Z61:      02 Z61-BOR-SEARCH-SUB-LIBRARY PICTURE X(5).
Z67:          02 Z67-SUB-LIBRARY                PICTURE X(5).
Z68:          02 Z68-SUB-LIBRARY            PICTURE X(5).                          S
Z70:           03 Z70-SUB-LIBRARY           PICTURE X(5).
Z700:              04 Z700-CIRC-SUB-LIBRARY OCCURS 100          PICTURE X(5).

The 
z1003 is not used by North American sites 
Z301 is recreated dynamically (by cir-01 and certain online GUI programs from tab16 and tab17)
z311 and z313 are Inventory number tables which may not be used at certain sites
z321, the Advance Booking table, may not be used at certain sites
z40 is obsolete
z403 is "Object Data" which may or may not be present
z602 is sublibrary/ordering unit records

In addition, though they don't contain the sublibrary code, the ADM Z00 and z103 records would be involved.  (See #2 and #3 below.)   The first step is to back up all of the tables in the xxx50 and yyy50 ADM libraries using the file-03 Service or the Oracle datapump.

It is strongly suggested that you contact your Aleph Support team and/or Ex Libris Professional Services before doing this.

1. There is no Aleph Service which can make these changes.  They would need to be done, table by table, via SQL such as:

SQL> insert into yyy50.z30 select * from xxx50.Z30 where z30_sub_library = 'sssss';
 

2.  The yyy50 z00 would be a bit tricky.  These records do not contain the sublibrary.  Basically, one would need to insert the xxx50.z00 into the yyy50.z00 only when it's linked to an item with the sssss sublibrary:

SQL> insert into yyy50.z00 select * from xxx50.z00 where z00_doc_number in (select substr(z30_rec_key,1,9) from xxx50.z30  where z30_sub_library = 'sssss'); 

3.  Then delete the affected xxx50.z00 records:

SQL> delete from xxx50.Z00 where z00_doc_number in (select substr(z30_rec_key,1,9) from xxx50.z30  where z30_sub_library = 'sssss'); 

4.  Then delete the affected xxx50.z30 records:  

SQL> delete from xxx50.Z30 where z30_sub_library = 'sssss';

5.  Then delete the other xxx50.znn records:

SQL> delete from xxx50.Znn where znn_sub_library = 'sssss';

6.  Then the manage-12 service would need to be run to create proper z103 entries in the xxx50 and yyy50 ADM libraries and the xxx01 Bib library.

7.  If Acquisitions records have been moved, the acq-04 and manage-111 Services will need to be run in xxx50 and yyy50 to regenerate the z79 and z111 index tables.

The moving of records from one ADM library to another is similar to that described in the article "How to remove an ADM library". 

• Article last edited: 9-Jun-2017