Database Corruption

When a Keystone database becomes corrupt beyond repair and there are no suitable backups, one option is to "pump" the data from the corrupt database to a new, clean database. The GHIBDataPump utility is designed for this purpose.

Procedure

1. Ensure that no one else is accessing the database, then copy the corrupt database to a separate folder (i.e. C:\KSFIX) and give it the name "ccwsrc.ib".
2. Create a new, clean Keystone database using the following procedure:
   1. Open a command prompt.
   2. Change to the InterBase Bin directory (C:\Embarcadero\InterBase\Bin for IB XE.)
   3. Enter the following command:

      GBAK -r -user ccwin -password <password> "C:\Program Files\GivenHansco\Keystone\Template\ccwmco.ibt" 127.0.0.1:C:\KSFIX\ccwtgt.ib

3. Open IBConsole, connect to the C:\KSFIX\ccwtgt.ib database, and execute the following SQL command:

set term ^ ;

alter procedure ccpcompany_reassign_id
(
  old_id	VARCHAR(3),
  new_id	VARCHAR(3)
)
AS
BEGIN
  EXIT;
END
^

1. Close and re-open IBConsole, connect to the C:\KSFIX\ccwtgt.ib database, and execute the following SQL command:

   ALTER TABLE GLTACCT DROP CONSTRAINT GLTACCT_FKY_COMPANY;

2. Now execute the following SQL command:

set term ^ ;

alter PROCEDURE arpdiv_reassign_id
(
  old_id	VARCHAR(2),
  new_id	VARCHAR(2)
)
AS
BEGIN
EXIT;
END
^

1. Close and re-open IBConsole, connect to the C:\KSFIX\ccwtgt.ib database, and execute the following SQL command:

   ALTER TABLE GLTACCT DROP CONSTRAINT GLTACCT_FKY_DIVISION;

2. Unzip and run GHIBDataPump.zip.
3. For Source Database enter:

   127.0.0.1:C:\KSFIX\ccwsrc.ib

4. For Target Database enter:

   127.0.0.1:C:\KSFIX\ccwtgt.ib

5. For Log File enter:

   C:\KSFIX\datapump.log

6. Click the Refresh button, and enter the "ccwin" login and password.
7. Click the OK button, and again enter the "ccwin" login and password.
8. The program will now "pump" the data from the corrupt database's tables to the new database's tables.
9. Close the program when you receive the "Pump complete" message.
10. Open IBConsole, connect to the C:\KSFIX\ccwtgt.ib database, and execute the following SQL command:

    ALTER TABLE GLTACCT ADD CONSTRAINT "GLTACCT_FKY_COMPANY" FOREIGN KEY ("COMPANY_NO") REFERENCES "CCTCOMPANY" ("COMPANY_NO");

11. Execute the following SQL command:

set term ^ ;

alter procedure ccpcompany_reassign_id
(
  old_id	VARCHAR(3),
  new_id	VARCHAR(3)
)
AS
BEGIN
      UPDATE gltacct SET company_no = :new_id WHERE (company_no = :old_id);
END
^

1. Execute the following SQL command:

   ALTER TABLE GLTACCT ADD CONSTRAINT "GLTACCT_FKY_DIVISION" FOREIGN KEY ("DIVISION_NO") REFERENCES "ARTDIV" ("DIVISION_NO");

2. Execute the following SQL command:

set term ^ ;

alter PROCEDURE arpdiv_reassign_id
(
  old_id	VARCHAR(2),
  new_id	VARCHAR(2)
)
AS
BEGIN
      UPDATE artplant SET division_no = :new_id WHERE (division_no = :old_id);
      UPDATE gltacct SET division_no = :new_id WHERE (division_no = :old_id);
END
^

1. The final step is to set the database generators to the proper values. Set the following generators to the values given by the associated queries using the "SET GENERATOR <generator> TO <value>" SQL command.

Generator	Value Query
GEN_CCCONTKEY	SELECT MAX(RECKEY) FROM CCTCONT;
GEN_CCSESSION	SELECT MAX(SESSION_NO) FROM CCTSESSN;
GEN_ARTAXTRXKEY	SELECT MAX(SEQ_NO) FROM ARTTAXTRX;
GEN_QUOTEID	SELECT MAX(CAST(JOB_PO_LOC AS NUMERIC(15,0))) FROM QTTJOBHD WHERE (JOB_PO_LOC = LOWER(JOB_PO_LOC));
GEN_DIBWKEY	SELECT MAX(RECKEY) FROM DITBWHD;
GEN_DIORDERID	SELECT MAX(CAST(ORDER_CODE AS NUMERIC(15,0))) FROM ARTORDHD WHERE (ORDER_CODE = LOWER(ORDER_CODE));
GEN_APPONO	SELECT MAX(CAST(PO_NUMBER AS NUMERIC(15,0))) FROM APTPOHD WHERE (PO_NUMBER = LOWER(PO_NUMBER));
GEN_GPSEQNO	SELECT MAX(SEQ_NO) FROM GPTSTATUS;

You may now replace the original corrupt database with the C:\KSFIX\ccwtgt.ib file.