Skip to main content
ExLibris
  • Subscribe by RSS
  • Ex Libris Knowledge Center

    Recovering records (such as z303) accidentally deleted via SQL

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

    Description:
    In trying to fix an ALEPH profile problem, instead of deleting ALEPH from the profile field, I deleted the entire Z303 for 663 users!

    The users are still intact on the Test server - is there any way I can get their Z303 records and put back into Production?

    Resolution:
    The following SQL (in the $usr_library) will give you cases where the z303 was deleted but the z308 type 00 ID still exists:
    SQL> select substr(Z308_REC_KEY,3,10) from z308 where Z308_REC_KEY like '00%' minus select substr(z303_rec_key,1,10) from z303;
    SUBSTR(Z308_REC_KEY,3,10)
    ------------------------------
    000412799
    000412821
    000413142
    000413143
    000413145
    <etc.>


    Since you do not have any backup of the Prod z303, I have created a z303TEMP file on Prod with the following SQL. I copied the existing $alephm_root/sql_tab/z303_create.sql as z303temp_create.sql:
    DROP TABLE ABC50.Z303TEMP;
    CREATE TABLE ABC50.Z303TEMP (
    Z303_REC_KEY CHAR(12) NOT NULL,
    Z303_PROXY_FOR_ID CHAR(12),
    Z303_PRIMARY_ID VARCHAR2(12),
    Z303_NAME_KEY CHAR(50),
    Z303_USER_TYPE CHAR(5),
    Z303_USER_LIBRARY CHAR(5),
    Z303_OPEN_DATE NUMBER(8),
    Z303_UPDATE_DATE NUMBER(8),
    Z303_CON_LNG CHAR(3),
    Z303_ALPHA CHAR(1),
    Z303_NAME VARCHAR2(200),
    Z303_TITLE CHAR(10),
    Z303_DELINQ_1 NUMBER(2),
    Z303_DELINQ_N_1 VARCHAR2(200),
    Z303_DELINQ_1_UPDATE_DATE NUMBER(8),
    Z303_DELINQ_1_CAT_NAME CHAR(10),
    Z303_DELINQ_2 NUMBER(2),
    Z303_DELINQ_N_2 VARCHAR2(200),
    Z303_DELINQ_2_UPDATE_DATE NUMBER(8),
    Z303_DELINQ_2_CAT_NAME CHAR(10),
    Z303_DELINQ_3 NUMBER(2),
    Z303_DELINQ_N_3 VARCHAR2(200),
    Z303_DELINQ_3_UPDATE_DATE NUMBER(8),
    Z303_DELINQ_3_CAT_NAME CHAR(10),
    Z303_BUDGET VARCHAR2(50),
    Z303_PROFILE_ID VARCHAR2(12),
    Z303_ILL_LIBRARY VARCHAR2(20),
    Z303_HOME_LIBRARY CHAR(5),
    Z303_FIELD_1 VARCHAR2(200),
    Z303_FIELD_2 VARCHAR2(200),
    Z303_FIELD_3 VARCHAR2(200),
    Z303_NOTE_1 VARCHAR2(200),
    Z303_NOTE_2 VARCHAR2(200),
    Z303_SALUTATION VARCHAR2(100),
    Z303_ILL_TOTAL_LIMIT NUMBER(4),
    Z303_ILL_ACTIVE_LIMIT NUMBER(4),
    Z303_DISPATCH_LIBRARY CHAR(5),
    Z303_BIRTH_DATE NUMBER(8),
    Z303_EXPORT_CONSENT CHAR(1),
    Z303_PROXY_ID_TYPE NUMBER(2),
    Z303_SEND_ALL_LETTERS CHAR(1),
    Z303_PLAIN_HTML CHAR(1),
    Z303_WANT_SMS CHAR(1),
    Z303_PLIF_MODIFICATION VARCHAR2(50),
    Z303_TITLE_REQ_LIMIT NUMBER(4),
    Z303_GENDER CHAR(1),
    Z303_BIRTHPLACE VARCHAR2(30)
    )
    STORAGE (INITIAL 10M NEXT 10M MINEXTENTS 1 PCTINCREASE 0)
    PCTUSED 80
    PCTFREE 10
    TABLESPACE TS2D;



    And I have added the following to the abc50 file_list:
    TAB z303temp 20M 0K TS2D
    You can:
    On Prod:
    back up the z303 using p_file_03
    go to the abc50 $data_files directory and move the file from z303.seqaa to z303.seqaa.save.
    On Test:
    use p_file_03 to extract the z303 records
    go to the abc50 $data_files directory and move the file from z303.seqaa to z303temp.seqaa
    ftp the file from the abc50 $data_files directory
    on Test to the abc50 $data_files directory on Prod (as z303temp.seqaa).


    On Prod:
    load the z303temp using p_file_04. **Be sure to specify z303temp --NOT z303 -- as the file name.**
    use SQL to insert the z303temp records which are in the z308 but not in the z303 into the z303. That would be:
    insert into z303 select * from Z303temp where substr(z303temp.z303_rec_key,1,10) in (select substr(Z308_REC_KEY,3,10) from z308 where Z308_REC_KEY like '00%' minus select substr(z303_rec_key,1,10) from z303));
    The result of this insert should be something like 633. If not, do not do commit.

    The above is for the z303, but the principles:
      copy the $alephm_root/sql_tab/znnn_create.sql to znnntemp_create.sql,
      add the znnntemp table to the file_list,
      create the znnntemp table via util f/4 or SQL,
      import the znnn table from backup or Test into znnntemp using p_file_04, and
      copy the relevant records from znnntemp to znnn using SQL insert)
    are ones which could be applied to any Aleph table.

    Additional Information

    This is similar SQL which might used to restore accidentally deleted z30 records....

    SQL> insert into z30 select * from Z30temp where substr(z30temp.z30_rec_key,1,9) > ' 000123456' and substr(z30temp.z30_rec_key,1,9) < '000567890 where z30temp.z30_rec_key not in (select z30_rec_key from z30)'; 


    • Article last edited: 10/8/2013