p_manage_05 doesn't generate expected index entries; duplicate keys

• Article Type: General
• Product: Aleph
• Product Version: 16.02

Description:
I have run p_manage_05 in ABC01. It appears to have completed, but we find that certain records are not included in the GPONO Direct SuDoc number index which should be, such as, "A 57.38:M 75/11".

Resolution:
Searching on the z11_text using SQL:

abc01@ALEPH0> select z11_rec_key from z11 where z11_rec_key like 'GPO%' and z11_text like '$$aA 57.38/13:M 75%';

shows that a z11 GPONO record for this call number *does* exist. A discrepancy between what we see in SQL and what we see in ALEPH is usually due to the lack of an Oracle index. util a/17/14 for the z11 showed this:

Enter Table Name : z11

Defined in file_list:
__________________________________________________________
IND z11_id 960M 0K TS4X
IND z11_id1 128M 0K TS4X
__________________________________________________________


Exist in the Database:

INDEX_NAME STATUS INDEX_TYPE UNIQUENESS COLUMN_NAME
--------------- ------- ---------- ----------- --------------------
Z11_ID1 VALID NORMAL NONUNIQUE Z11_DOC_NUMBER

As you can see, the z11_id was missing.

Looking back at the logs, we see:

$alephe_scratch/ABC01_p_manage_05.log has these messages very near the end:

Wed Apr 25 17:37:06 CDT 2007 Starting indexes creation for table z11
Wed Apr 25 17:37:06 CDT 2007 Indexes for table z11 are : z11_id z11_id1
Wed Apr 25 17:37:06 CDT 2007 Creating Index z11_id ...
Wed Apr 25 17:45:46 CDT 2007 Creating Index z11_id1 ...
Wed Apr 25 17:50:05 CDT 2007 Error : Only 1 indexes out of 2 for table z11 wer
e created
Wed Apr 25 17:50:05 CDT 2007 Error : index list for index creation was 'z11_id
','z11_id1'
Wed Apr 25 17:50:05 CDT 2007 Error : Please check logs /exlibris/aleph/u16_1/abc01/scratch/create_ora_tables_z11_id*.log


And $data_scratch/create_ora_tables_z11_id.log has this:

aleph_admin@ALEPH0> 17:37:07 aleph_admin@ALEPH0> CREATE UNIQUE INDEX ABC01.Z11
_ID ON ABC01.Z11 (Z11_REC_KEY ASC)
*
ERROR at line 1:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

<end logs>

Duplicate z11's can occur if people are creating/updating records while p_manage_05 is running.
(This can happen even if ue_01 isn't running, since the update of the Direct index occurs immediately upon update.)

util a/17/18 was used to correct this:

Enter Table Name : z11
Enter Index enter/1/2/3/4/5 :
index_name z11_id index_suffix
Enter Column Name : z11_rec_key
A NONUNIQUE INDEX z11_id will be created, enter Y to confirm y

[See KB 8192-424 for detailed info on running util a/17/18.]

The index is now OK.