WikiAdmin: 1 revision imported

2023-07-14T12:58:48Z

1 revision imported

← Older revision	Revision as of 12:58, 14 July 2023
(No difference)

Intra>User: 1 revision imported

2017-01-10T00:01:31Z

1 revision imported

New page

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===

# 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".
# Create a new, clean Keystone database using the following procedure:
## Open a command prompt.
## Change to the InterBase Bin directory (C:\Embarcadero\InterBase\Bin for IB XE.)
## Enter the following command:<pre>GBAK -r -user ccwin -password <password> "C:\Program Files\GivenHansco\Keystone\Template\ccwmco.ibt" 127.0.0.1:C:\KSFIX\ccwtgt.ib</pre>
# Open IBConsole, connect to the C:\KSFIX\ccwtgt.ib database, and execute the following SQL command:
<blockquote>
<pre style="white-space: pre-wrap;
white-space: -moz-pre-wrap;
white-space: -pre-wrap;
white-space: -o-pre-wrap;
word-wrap: break-word;">
set term ^ ;

alter procedure ccpcompany_reassign_id
(
old_id VARCHAR(3),
new_id VARCHAR(3)
)
AS
BEGIN
EXIT;
END
^</pre>
</blockquote>
# Close and re-open IBConsole, connect to the C:\KSFIX\ccwtgt.ib database, and execute the following SQL command:<pre>ALTER TABLE GLTACCT DROP CONSTRAINT GLTACCT_FKY_COMPANY;</pre>
# Now execute the following SQL command:
<blockquote>
<pre style="white-space: pre-wrap;
white-space: -moz-pre-wrap;
white-space: -pre-wrap;
white-space: -o-pre-wrap;
word-wrap: break-word;">
set term ^ ;

alter PROCEDURE arpdiv_reassign_id
(
old_id VARCHAR(2),
new_id VARCHAR(2)
)
AS
BEGIN
EXIT;
END
^</pre>
</blockquote>
# Close and re-open IBConsole, connect to the C:\KSFIX\ccwtgt.ib database, and execute the following SQL command:<pre>ALTER TABLE GLTACCT DROP CONSTRAINT GLTACCT_FKY_DIVISION;</pre>
# Unzip and run [[Media:GHIBDataPump.zip|GHIBDataPump.zip]].
# For Source Database enter:<pre>127.0.0.1:C:\KSFIX\ccwsrc.ib</pre>
# For Target Database enter:<pre>127.0.0.1:C:\KSFIX\ccwtgt.ib</pre>
# For Log File enter:<pre>C:\KSFIX\datapump.log</pre>
# Click the Refresh button, and enter the "ccwin" login and password.
# Click the OK button, and again enter the "ccwin" login and password.
# The program will now "pump" the data from the corrupt database's tables to the new database's tables.
# Close the program when you receive the "Pump complete" message.
# Open IBConsole, connect to the C:\KSFIX\ccwtgt.ib database, and execute the following SQL command:<pre>ALTER TABLE GLTACCT ADD CONSTRAINT "GLTACCT_FKY_COMPANY" FOREIGN KEY ("COMPANY_NO") REFERENCES "CCTCOMPANY" ("COMPANY_NO");</pre>
# Execute the following SQL command:
<blockquote>
<pre style="white-space: pre-wrap;
white-space: -moz-pre-wrap;
white-space: -pre-wrap;
white-space: -o-pre-wrap;
word-wrap: break-word;">
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
^</pre>
</blockquote>
# Execute the following SQL command:<pre>ALTER TABLE GLTACCT ADD CONSTRAINT "GLTACCT_FKY_DIVISION" FOREIGN KEY ("DIVISION_NO") REFERENCES "ARTDIV" ("DIVISION_NO");</pre>
# Execute the following SQL command:
<blockquote>
<pre style="white-space: pre-wrap;
white-space: -moz-pre-wrap;
white-space: -pre-wrap;
white-space: -o-pre-wrap;
word-wrap: break-word;">
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
^</pre>
</blockquote>
# 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.
{| border="2" cellspacing="0" cellpadding="5" align="center"
! 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.

Database Corruption - Revision history

WikiAdmin: 1 revision imported

Intra>User: 1 revision imported