Where is your Aleph Data?

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?

• Where tables are stored in Oracle.
• What tables contain the Aleph catalog data.
• What are the relationships between tables.

(Limit to Catalog and Item data)
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

• Tag22
• Tells Aleph what data to load into the 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
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

• file_list

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
10
Setup of Z00R table

• Execute util a 17 1 to create the table

11
Setup of Z00R table

• tab100

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

• Load Z00R table

• Aleph Service
• Manage_07
• Update Short Bibliographic Records
• Record Type - Update only Z00R

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 s1987xx
  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 scrapbookf1987
• 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
  Rogerd1931-
• 000017 000028414 60014 L aSonnenberg,
  Johnd1922-
• 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-2858b00c2012
  0905lLDS01h1704
• 000039 000028414 CAT L aBATCH-UPDb00c201
  20901lLDS01h0152
• 000036 000028414 CAT L aCONVb00c20101005
  lLDS01h0258
• 000001 000028414 FMT L MX
• 000002 000028414 LDR L npma22a450
  0

14
Setup of Z00R table

• The tag data in the Z00R table is a single string
  of all the subfields of the tag
• 
  aSonnenberg, Johnd1922-
• To enable the extract of a single subfield we
  created a SQL function
• ldsxx.aseq_subfield(z0
  0r_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

• Enables to link catalog records

ADM record
HOLDING record
BIB record
16
LKR tag
LKR tag goes in the child record pointing to the
parent 000028414
LKR L aADMlLDS01b000028414

• a - Type ADM
• l - Library LDS01
• b - DOC Number - 000028414

17
LKR tag

• The following code will identify the ADM and BIB
  doc numbers

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(z0
0r_text,'l') 'LDS01'
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_tex
  t,'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

• Z02

Z01_rec_key_4
Unique Heading From BIB Record
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_rec_key

Z30_hol_doc_number_x
ITEM
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_at_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_move boolean
• BEGIN
• v_move false
• v_pos 1
• loop
• exit when v_pos gt length(p_string)
• if substr(p_string,v_pos,2) '' then
• if v_move then
• exit
• else