To delete individual records by their RECID, the IDXFIX utility can be used by selecting:
Option 6. Delete one record and its index entries.
This Article compliments the information in the above articles when there are many records to be deleted quickly. It is advisable to:
- First test this method with the steps to engineer Unique key violation with a sample sports2000 database described above.
- Assure a valid backup before running record deletion on production.
- Using this method to delete records will not fire associated actions defined in delete-triggers
OPTION 1: ABL code to automate deleting duplicate records detected by IDXBUILD error 11271. Before running this ABL code:
- Extract the 1127 messages from the database lg file into a separate text file.
- The OpenEdge version must be known due to different database lg file formatting
- The location of the database .db file
- The storage area of each table
This ABL code will ask the following pieces of information:
1.
Input File : Enter the path of the text file created earlier with just the text file with 1127 errors pertaining to just one table.
2.
Area number : Area number where the table / duplicate records live.
3. DB path & Name :Enter the path of database.
4.Temp File Name : Name of the temp file (may be necessary to alter location where input entries are generated for each iteration of the loop)
5. Version # of log format : Enter the version of OpenEdge the database log was made by.
Run the following ABL code (which is also attached to the article) to resolve this issue :
DEFINE VARIABLE cInputFile AS CHAR NO-UNDO FORMAT "x(70)" init "d:\db\1127errors.lg".
DEFINE VARIABLE iAreaNumber AS INT NO-UNDO.
DEFINE VARIABLE cOutputFile AS CHAR INIT ".\tempfile" NO-UNDO.
DEFINE VARIABLE cDBPath-Name AS CHAR NO-UNDO FORMAT "x(70)" init "d:\db\sports2000".
DEFINE VARIABLE cInputLine AS CHAR NO-UNDO.
DEFINE VARIABLE cRecid AS CHAR NO-UNDO.
DEFINE VARIABLE cCommandString AS CHARACTER NO-UNDO.
DEFINE VARIABLE cTempFile AS CHAR NO-UNDO INIT ".\fifo.txt".
DEFINE VARIABLE iVersion as int no-undo label "Enter 9, 10, or 11 as version of log file.".
FORM
"Input File :" cInputFile AT 5 SKIP
"Area Number :" iAreaNumber AT 5 SKIP(1)
"DB Path&Name :" cDBPath-Name AT 5 SKIP(1)
"Temp File Name :" cTempFile at 5 SKIP(1)
"Version # of log format: " iVersion at 5
WITH FRAME idxfixframe CENTERED NO-LABELS TITLE "1127 IDXFIX Parser".
/* UPDATE cInputFile LABEL "Enter the log file to parse." */
/* UPDATE iAreaNumber LABEL "What area number do the records live in?" */
/* UPDATE cDBPath-Name LABEL "Enter path and dbname." */
/* UPDATE cTempFile LABEL "Enter the temp file to write recids to for idxfix." */
/* UPDATE iVersion with frame idxfixframe . */
UPDATE cInputFile
iAreaNumber
cDBPath-Name
cTempFile
iVersion with frame idxfixframe .
INPUT FROM value(cInputFile).
REPEAT:
IMPORT UNFORMATTED cInputLine NO-ERROR.
IF ERROR-STATUS:ERROR THEN LEAVE.
IF INDEX(cInputLine,"(1127)") > 0 THEN
DO:
RUN trimline(INPUT-OUTPUT cInputLine,INPUT " ", INPUT " ").
if iVersion = 9 then
cRecid=ENTRY(4, cInputLine, " ").
if iVersion = 10 then
cRecid=ENTRY(9, cInputLine, " ").
if iVersion = 11 then
cRecid=ENTRY(10, cInputLine, " ").
cRecid=SUBSTRING(cRecid,1, LENGTH(cRecid) - 1).
OUTPUT TO value(cTempFile).
PUT UNFORMATTED "6" SKIP
cRecid SKIP
iAreaNumber SKIP
"y" SKIP.
OUTPUT CLOSE.
cCommandString = "proutil " + cDBPath-Name + " -C idxfix<" + cTempFile.
OS-COMMAND SILENT VALUE(cCommandString).
END.
END.
PROCEDURE trimline:
DEFINE INPUT-OUTPUT PARAMETER cInputLine AS CHAR NO-UNDO.
DEFINE INPUT PARAMETER cFind AS CHAR NO-UNDO.
DEFINE INPUT PARAMETER cChange AS CHAR NO-UNDO.
DO WHILE INDEX(cInputLine,cFind) > 0:
cInputLine = REPLACE(cInputLine, cFind, cChange).
END.
RETURN cInputLine.
END.
OPTION 2: Script IDXFIX to delete duplicate records detected by IDXBUILD error 1127OS scripts can be used to achieve the same by feeding the IDXFIX utility with each RECID to delete. This method is particularly useful when a Development License is not available.
- Only the RECID of the record from error 1127 must be parsed to a text file, each on a new line. Assure there is one additional line after the last RECID entry
- When the tables are in different storage areas, then create a separate script with distinct parsed RECID for each area from the 1127 errors
UNIX: Script IDXFIX Option 6 to delete multiple records
AREANUM=9
DBNAME=sports
for i in `cat duprecidarea9.out`
do
echo 6 > /tmp/resp.txt
echo $i >> /tmp/resp.txt
echo $AREANUM >> /tmp/resp.txt
echo "y" >> /tmp/resp.txt
proutil $DBNAME -C idxfix < /tmp/resp.txt
done
WINDOWS: Script IDXFIX Option 6 to delete multiple records
@echo off
set AREANUM=9
set DBNAME=sports
SETLOCAL EnableDelayedExpansion
for /F "tokens=1" %%i in (duprecidarea9.out) do (
REM set %%i
echo 6 > .\resp.in
echo %%i >> .\resp.in
echo %AREANUM% >> .\resp.in
echo y >> .\resp.in
CALL proutil %DBNAME% -C idxfix < .\resp.in >> .\resp.out 2>&1
)
@echo on
