Best practices - retention of item/circ history tables

• Article Type: General
• Product: Aleph
• Product Version: 20, 21, 22, 23

Description:
Are there recommendations with regard to the various history tables in Aleph.  Should there ever be a purge or removal of older data in tables such as z30h, z36h, z37h, z35, etc?  What do other sites usually do in regard to these tables?


Resolution:

The z35 is used for batch circulation statistics (the cir-30 and cir-31 services); as described in Article " SQL to locate items with fewer (or more) than x loans ", it might also be used to locate items for weeding. The z36h displays the loan history for a particular patron or item; the z37h, the request history; the z30h, the history of item updates.  util x/13 (" Clean Events and Historic Information tables") was introduced with version 22.1.    It deletes records from all of these tables older than xxx days.  (As described in the article " Unnecessary z30h history records ", in the case of the z30h, you may want to delete only records older than xxx days with certain z30h_h_reason values.)  

The only reason to purge the z35 and z37h data would be space considerations. In the case of the z36h and z30h, an additional consideration is slowness in checking out the item (See Article "Slow check-out of items which have been loaned many times " ) and in displaying the item list (Article " Slow display of GUI Items List when there are many items with many loans " ).

If you run the patron scrubbing routines, the closed z31 cash records are pretty much useless after being scrubbed. And the z36h, z37h, and z309 circ logger records are of limited use. (They can no longer be used to display information on the patron's loan/request history, but can be used for the item's loan/request history.) cir-78 can be used to physically delete z309 circ logger records.

There could be an advantage to saving the z35 despite the scrubbing of the patron ID from it, since the circ statistics produced from it do not require or involve the patron ID.  Also, as described in Article " SQL to locate items with fewer (or more) than x loans ", it might be used to produce a weeding report.  Also, the z35 is smaller than the z36h and the z309.

The item history could continue to have value even after several years. (If you want to reduce its size you might want to delete z30h records older than x years where the z30h_h_reason is "All fields". See Article " Unnecessary z30h history records " in this regard.)

You could use the Z30H_H_DATE for locating records to delete. It would be better to use this in combination with the Z30H_H_REASON (--preserving records of item deletion, for instance).

(The "Other" history records generally relate to less important changes --those not covered by an explicit ./xxx50/tab/tab_item_history type -- but may comprise 2/3 or more of the table.).

Note:  It's a good practice to back up Oracle tables you are purging prior to purging them.  You can use the GUI file-03 Service for this purpose.