The Aleph integrated Library System made by ExLibris is widely used around the world. The aim of this article is explain the relationship between a bibliographic records and item records in the Aleph database, even more, show how to query the database to make a statistics report about the number of loans in Aleph.

The Aleph database is the Oracle Database, the version of the database version change according with the Aleph version. The query that we will study here was tested against Aleph version 20, littles adaptations can be necessary if you are using another version of Aleph. Please, look the query below, this query select title, item barcode and count the total of loans by item, sorting from the item with more loans to the item with less loans.

SELECT Z13_TITLE "Title", Z30.Z30_BARCODE "Barcode", count(Z36H.Z36H_rec_key) "Total"  
 FROM Z103, Z30, USM01.Z13, Z36H
  WHERE SUBSTR(Z30.Z30_REC_KEY,1,9)=SUBSTR(Z103.Z103_REC_KEY,6,9) 
    AND(Z103.Z103_REC_KEY_1 LIKE 'USM01%') 
    AND(SUBSTR(Z103.Z103_REC_KEY_1,6,9)=Z13_REC_KEY) 
    AND(Z30.Z30_REC_KEY = Z36H.Z36H_REC_KEY) 
     GROUP BY Z13_TITLE, Z30.Z30_BARCODE
       ORDER BY count(Z36H.Z36H_rec_key) DESC; 
 
 

In the line 4 of the SQL query you can see that this query consult four tables:

  • Z13 (Short Bibliographic Record)
  • Z103 (Link Between Records)
  • Z30 (Items)
  • Z36H (Loans History)

The use of the table Z13 is the first thing that you should be aware if you are new in Aleph’s database. The record metadata in Aleph are stored in the table Z00, so you should be thinking, why don’t we query for the information in the table Z00 instead use the table z13? Because in the table Z00 all metadata of the records are stored in the column Z00-DATA, in this column the metadata is compressed to an internal Aleph algorithm (yes, all metadata is stored in one line), so you can not query the Z00 and display the metadata in nice way.

The table Z13 permits us to query the bibliographic information and display it in a nice way through SQL query. The tab22 table of the library’s tab directory defines which fields will be included in the Z13 record, when you change the tab22 you have to use (UTIL G/1/22) to load the changes. The record is build automatically by the system when the records are uploaded into the database (when the indexing parameter is set to ‘Full’), or when records are added or updated through the Cataloging module, alternatively the batch procedure p_manage_07 updates all lines in the Z13.

Once we understood the Z13, it is time for explain the first relationship. Z13 is related with Z103, the primary key of each table are called REC_KEY in Aleph, by convention the Aleph use the name of the table in its fields, so the Z13_REC_KEY and Z103_REC_KEY are the primary key of Z13 and Z103. However the Z103 is called the Link Between Records tables for a reason, it have three rec key: Z103_REC_KEY, Z103_REC_KEY_1 and Z103_REC_KEY_2, indeed the rec key fields in the Z103 are foreign keys.

The rec key field that create the relationship between Z13 and Z103 is the Z103_REC_KEY. In the line 7 of the SQL query above, you can see that the match between the two tables is done using the sub string function, we need that because the Z103_REC_KEY_1 is a composite field. The first 5 position of the Z103_REC_KEY_1 have library code of library that the record belongs and from the position 6 to 15 of the Z103_REC_KEY_1 it stores the system number of the record. In the line 6 of the query above, it selects the library of the record, in this case USM01.

The next relationship that we need to do is between Z103 and Z30. Z30 is the table that store the information of the item in Aleph. For the propose of the this post we need the Z30 to get the barcode of the item, Z30 is also necessary to retrieve the information about the loans in the table Z36H, the relationship between Z103, Z30 and Z36H are present in the lines 5 and 8 of the SQL above, you can note that instead of Z103_REC_KEY_1, in the line 5 the field it uses the Z103_REC_KEY. The line 8 show the relationship between the Z30 and Z36H.

Finally, in the last two line of the query, it uses the “GROUP BY” and “ORDER BY” to count the number of loans. When you get your SQL done and working well you can use that in the sqlplus in the Aleph server console, the spool directive in the SQL allows to export the output of the query to file, even more, there are other SQL directive in ORACLE that can help to display the query output in a nice way, in the SQL you can see some of these directives that I use and how the SQL looks like when I add them:

SET PAUSE OFF
SET MARKUP HTML ON
SET ECHO OFF
SET HEADING OFF
SPOOL REPORT.HTML
SELECT Z13_TITLE "Title", Z30.Z30_BARCODE "Barcode", count(Z36H.Z36H_rec_key) "Total"  
 FROM Z103, Z30, USM01.Z13, Z36H
  WHERE SUBSTR(Z30.Z30_REC_KEY,1,9)=SUBSTR(Z103.Z103_REC_KEY,6,9) 
    AND(Z103.Z103_REC_KEY_1 LIKE 'USM01%') 
    AND(SUBSTR(Z103.Z103_REC_KEY_1,6,9)=Z13_REC_KEY) 
    AND(Z30.Z30_REC_KEY = Z36H.Z36H_REC_KEY) 
     GROUP BY Z13_TITLE, Z30.Z30_BARCODE
       ORDER BY count(Z36H.Z36H_rec_key) DESC; 
 
 

Leave a Reply

Your email address will not be published. Required fields are marked *