1 / 34

Where is your Aleph Data?

Where is your Aleph Data?. Billy Rawles, Senior Database Engineer. Church History Library.

enrico
Download Presentation

Where is your Aleph Data?

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Where is your Aleph Data? Billy Rawles, Senior Database Engineer

  2. Church History Library Collections consist of manuscripts, books, Church records, photographs, oral histories, architectural drawings, pamphlets, newspapers, periodicals, maps, microforms, and audiovisual materials. The collection continues to grow annually and is a prime resource for the study of Church History. Our collection contains approximately: Library, Archives, and Museum collections

  3. What are we going to talk about? (Limit to Catalog and Item data) Where tables are stored in Oracle. What tables contain the Aleph catalog data. What are the relationships between tables.

  4. Aleph Libraries • Each Aleph Library creates an account (schema) in Oracle. (We created all of our libraries with LDS) • BIB Library – LDS01 • ADM Library – LDS50 • HOLDING Library – LDS60 • AUTHORITY Library – LDS10

  5. Catalog Data Aleph stores a catalog record in long data type. It takes the Aleph program to compress and uncompress this data. Not available using SQL. Stored in the Z00 table.

  6. Catalog Data – Z13 • Z13 and Z13U tables • Makes selected data available. • Limited number of columns Z13 has 5 fixed data columns Z13U has 15 data columns

  7. Setup of Z13 and Z13U tables YEAR 1 008## 0008 260## c CALL-NO 1 090## a AUTHOR 1 1#### TITLE 1 245## a 245## k 249## a IMPRINT 1 260## 261## 262## ISBN-ISSN 1 020## 022## USER-DEF-2 1 PST# 4 USER-DEF-3 1 PST# 5 USER-DEF-4 1 245## b USER-6 1 900## a USER-8 1 008## • Tag22 Tells Aleph what data to load into the tables

  8. Catalog Data – Z00R Z00R table Makes Tag data available from SQL Created by default only in the BIB Library Contains all the tags, one row per tag

  9. Setup of Z00R table Contains additional tables to create during library creation. TAB z00r 100K 0K TS4D IND z00r_id 100K 0K TS3X IND z00r_id1 100K 0K TS3X Found in folder: /opt/exlibris/aleph/u20_1/xxx10 file_list

  10. Setup of Z00R table Execute “util a 17 1” to create the table

  11. Setup of Z00R table Contains a parameter to tell Aleph to load the Z00R table. CREATE-Z00R=Y Found in folder /opt/exlibris/aleph/u20_1/xxx10/tab tab100

  12. Setup of Z00R table • Aleph Service • Manage_07 • Update Short Bibliographic Records • Record Type - “Update only Z00R” Load Z00R table

  13. Setup of Z00R table 000003 000028414 001 L 000028414 000004 000028414 003 L org.ldschurch.hmms 000005 000028414 005 L 20100930 000006 000028414 008 L ^^^^^^s1987^^^^xx^^^^^^^^^^^^|^^^^^eng^^ 000008 000028414 090 L $$aCR 571 2 000009 000028414 1102 L $$aPacific Area (Church unit : 1984-1998; 2008- ) 000010 000028414 24500 L $$aRarotonga Cyclone Sally scrapbook$$f1987 000011 000028414 5060 L $$aOpen for research. 000012 000028414 520 L $$aContains color photographs, accompanied by newspaper clippings and chronological account, relating to the destruction caused by hurricane that hit Rarotonga in the Cook Islands on 1 January 1987. Volume also details the subsequent rebuilding efforts initiated under the direction of Elder John Sonnenberg, Pacific Area president, and John R. Lasater, president of the New Zealand Auckland Mission. Scrapbook covers period from December 1986 through August 1987. 000018 000028414 60014 L $$aLasater, John Roger$$d1931- 000017 000028414 60014 L $$aSonnenberg, John$$d1922- 000019 000028414 61024 L $$aNew Zealand Auckland Mission 000020 000028414 650 4 L $$aDisaster relief 000021 000028414 650 4 L $$aDisasters 000040 000028414 CAT L $$aDVB-2858$$b00$$c20120905$$lLDS01$$h1704 000039 000028414 CAT L $$aBATCH-UPD$$b00$$c20120901$$lLDS01$$h0152 000036 000028414 CAT L $$aCONV$$b00$$c20101005$$lLDS01$$h0258 000001 000028414 FMT L MX 000002 000028414 LDR L ^^^^^npm^a22^^^^^^a^4500 000032 000028414 OWN L $$aARCHIVES 000033 000028414 STA L $$aFINAL

  14. Setup of Z00R table The tag data in the Z00R table is a single string of all the subfields of the tag $$aSonnenberg, John$$d1922- To enable the extract of a single subfield we created a SQL function ldsxx.aseq_subfield(z00r_text,’a’) Will extract the “a” subfield from the z00r_text string The code will be at the end of the power point

  15. LKR tag ADM record HOLDING record BIB record Enables to link catalog records

  16. LKR tag LKR tag goes in the child record pointing to the parent 000028414 LKR L $$aADM$$lLDS01$$b000028414 $$a - Type – ADM $$l - Library – LDS01 $$b - DOC Number - 000028414

  17. LKR tag select z00r_doc_number adm_doc_number, ldsxx.aseq_subfield(z00r_text,'b') bib_doc_number from lds50.z00r where z00r_field_code = 'LKR' and ldsxx.aseq_subfield(z00r_text,'l') = 'LDS01'; The following code will identify the ADM and BIB doc numbers

  18. LKR tag • The following code will give you the Z13 data from the ADM and the BIB select b.*, c.* from lds50.z00r a, LDS01.Z13 b, lds50.z13 c where a.z00r_field_code = 'LKR' and ldsxx.aseq_subfield(a.z00r_text,'l') = 'LDS01' and b.z13_rec_key = ldsxx.aseq_subfield(a.z00r_text,'b') and C.Z13_REC_KEY = a.z00r_doc_number;

  19. Z103 table The Z103 table contains links that Aleph creates from the LKR tags z103_rec_key z103_rec_key_1 z103_lkr_library z103_lkr_doc_number z103_lkr_type LDS5000010372201 LDS50000103722 LDS01 103722 ADM LDS5000010372202 LDS10000103722 LDS50 103722 ADM Z103_rec_key – Where the LKR tag is that created the link, library and doc number Z103_rec_key_1 – Contains the “from” library and doc number Z103_lkr_library – Contains the “to” library Z103_lkr_doc_number – Contains the “to” doc number, not left padded with zeros Z103_lkr_type – Contains the type of link

  20. Z103 table Each library has a Z103 table Only entries that effect records in the library are in the table. Either on the “from” or the “to” side The BIB library Z103 table have both ADM and HOL type links

  21. Authority Headings Headings in the BIB records tab11.eng defines headings

  22. Authority Headings • Z01 table contains a row for each unique heading z01_display_text is the tag data z01_rec_key_4 links to authority z01_aut_tag identifies the tag in the authority

  23. Authority Heading • Z02 identifies the BIB that the authority is in • Z02_rec_key identifies the heading in z01

  24. Authority Heading Bib Record Authority Record Z01_rec_key_4 Unique Heading From BIB Record Z02

  25. Authority Heading Select authority tag and the bib tag that references it select d.z00r_doc_number lds10_doc_number, d.z00r_field_code lds10_field_code, d.z00r_text lds10_text, c.z00r_doc_number lds01_doc_number, c.z00r_field_code lds01_field_code, c.z00r_text lds01_text from lds01.z01 a, lds01.z02 b, lds01.z00r c, lds10.z00r d where a.z01_rec_key_4 like 'LDS10%' and B.Z02_REC_KEY like A.Z01_ACC_SEQUENCE||'%' and c.z00r_doc_number = b.z02_doc_number and c.z00r_text = a.z01_display_text and d.z00r_doc_number = substr(a.z01_rec_key_4,6,9) and d.z00r_field_code = a.z01_aut_tag and d.z00r_text = a.z01_display_text;

  26. Items Z30 table in the ADM library Z30_rec_key – ADM number and a sequence number Z30_hol_doc_number_x – HOLDING doc number

  27. Items ADM record BIB record HOLDING record LKR LKR Z30_hol_doc_number_x ITEM Z30_rec_key

  28. Items select ldsxx.aseq_subfield(a.z00r_text,'b') LDS01_doc_number, substr(b.z30_rec_key,1,9) LDS50_doc_number, b.z30_hol_doc_number_x LDS60_doc_number, b.* from lds50.z00r a, lds50.z30 b where a.z00r_field_code like 'LKR%' and ldsxx.aseq_subfield(a.z00r_text,'l') = 'LDS01' and substr(b.z30_rec_key,1,9) = a.z00r_doc_number;

  29. Patrons Z303 table Z303_rec_key

  30. Circulation • Z37 – Hold z37_rec_key - z30_rec_key and sequence number z37_id - Patron z303_rec_key

  31. Circulation • Z36 – Loan z36_rec_key - z30_rec_key z36_id - Patron z303_rec_key

  32. Exlibris Documentation Aleph 20 Collected Oracle Tables Aleph Entity Relationship Diagram-20

  33. Questions and Answers Billy Rawles rawlesba@ldschurch.org

  34. ASEQ_SUBFIELD code • CREATE OR REPLACE FUNCTION LDSXX.aseq_subfield • ( • p_string in varchar2, • p_subfield in varchar2 • ) • RETURN VARCHAR2 • IS • v_return varchar2(1000); • v_pos number; • v_moveboolean; • BEGIN • v_move := false; • v_pos := 1; • loop • exit when v_pos > length(p_string); • if substr(p_string,v_pos,2) = '$$' then • if v_move then • exit; • else • v_move := false; • if upper(substr(p_string,v_pos,3)) = upper('$$'||p_subfield) then • v_move := true; • end if; • end if; • end if; • if v_move then • v_return := v_return||substr(p_string,v_pos,1); • end if; • v_pos := v_pos + 1; • end loop; • -- Remove the subfield identifier • v_return := substr(v_return,4,4000); • return(v_return); • END aseq_subfield; • /

More Related