During an R3load import getting error with one of the imports.
cd C:\Program Files\sapinst_instdir\BS2010\ERP605\LM\COPY\MSS\SYSTEM\DISTRIBUTED\AS-ABAP\DB and look for the management files (sort by date) you will see each of the load files as a pair, a text of the log and the task file (run)
Inside the text file you will find errors like:
(DB) ERROR: DDL statement failed
(DROP INDEX [ZRDS1O].[ZRDS1O~1])
DbSlExecute: rc = 103
(SQL error 3701)
error message returned by DbSl:
Cannot drop the index 'ZRDS1O.ZRDS1O~1', because it does not exist or you do not have permission.
(IMP) INFO: a failed DROP attempt is not necessarily a problem
(DB) ERROR: DDL statement failed
(CREATE UNIQUE INDEX [ZRDS1O~1] ON [ZRDS1O] ( [ACCT] , [CNTR] , [BUKRS] , [MANDT] , [ZZARTNR] , [PRCTR] ) WITH ( DATA_COMPRESSION = PAGE ) )
DbSlExecute: rc = 99
(SQL error 1505)
error message returned by DbSl:
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'tst.ZRDS1O' and the index name 'ZRDS1O~1'. The duplicate key value is (0000112040, , 7230, 210, R-106250-8, ).
(DB) INFO: disconnected from DB
So in this case the error was due to a duplicate key in a secondary index (rather than primary key index) of a Z table (ZRDS1O), as a humble SAP consultant I prefer not to find the duplicate row data problem (there are ways with using a SQL delete, but they take time) during the import, but leave the fix the import is finished, get the SAP instance system started and then ask functional guys to take a look at what has happened. Tip: if you are getting lots and lots of these errors sometimes codepage is wrong eg importing a unicode export into a non unicode system.
You can find the matching data and structure files in the export directory directory location
eg
The matching STR file is what you want eg in the case above in file SAP_APPL1_5.STR
ind: ZRDS1O~1
att: ZRDS1O APPL1 3 unique
fld: ACCT
fld: CNTR
fld: BUKRS
fld: MANDT
fld: ZZARTNR
fld: PRCTR
What to Change
Change the value of the index attribute from unique to non-unique, which of will be duplicate keys will be allowed and you can simply continue, the benefit of this over removing the index is that performance will only be marginally impacted, where as no index could result in your dw's hanging on full table scans.
Change line
att: ZRDS1O APPL1 3 unique
to
att: ZRDS1O APPL1 3 not_unique
After adjusting the STR file.
Restart your import with sapinst and if all goes will your import should now complete, unless of course you have more issues:
Next Steps.
So you or functional team with /nse16 may need to find and remove duplicate keys,
SQL is also your friend if you have a large number of duplicate keys in a table eg. select a.key1, a.key2,a.key3, count(*) rows from mytable , group by key1, key2, key3 having count(*) > 1
Eg in SQL Sever the display syntax that will show the duplicate rows and how many duplicates exist:
select b.ACCT,b.CNTR,b.BUKRS,b.MANDT,b.ZZARTNR,b.PRCTR, count(ACCT) drows
from [tst]. [ZRDS1O] b
group by
[ACCT], [CNTR] , [BUKRS], [MANDT], [ZZARTNR] , [PRCTR]
having count(ACCT) > 1
This will show you a list of the full row information that have duplicates
select a.* from
[tst].[ZRDS1O] a ,
(select b.ACCT,b.CNTR,b.BUKRS,b.MANDT,b.ZZARTNR,b.PRCTR, count(ACCT) drows
from [tst]. [ZRDS1O] b
group by
[ACCT], [CNTR] , [BUKRS], [MANDT], [ZZARTNR] , [PRCTR]
having count(ACCT) > 1) b
where a.[ACCT] = b.[ACCT]
and a.[CNTR] = b.[CNTR]
and a.[BUKRS] = b.[BUKRS]
and a.[MANDT] = b.[MANDT]
and a.[ZZARTNR] = b.[ZZARTNR]
and a.[PRCTR] = b.[PRCTR]
order by [ACCT], [CNTR] , [BUKRS], [MANDT], [ZZARTNR] , [PRCTR]
Q & A
Q: What do all these migration CMD, STR, TOC and EXT files mean and do?
A: Take a look here http://www.sapnwnewbie.com/2013/07/osdb-migration-cmd-str-toc-ext-r3load.html
Q: What happens if it is not a secondary index but the primary key index, in which the load will force a unique key. This is normally a more complex problem and a problem that needs more care in handling
A: Whilst I didn't encounter the problem in migration above that resulted in this log; I normally remove this table from the import, complete the import (with the offending table), normally this means making backup copies of the responsible STR file , the TOC file and the TSK files; then removing the table entry for the table from the STR (take care about the formatting ) , removing from the TOC file and TSK file and restarting. Post import you can use database tools to manual extract unique data if it is not a gigantic table. If you can do a production dress rehearsal then these problems can be sorted before cutover :smile: . Take care if this is a critical SAP table, it may not be a good idea to start the SAP system, until you fix this as you may suffer a short dump overload. I welcome suggestion and comments for this issue.
