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)

/wp-content/uploads/2015/10/sapinst_tsk_810569.png

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

/wp-content/uploads/2015/10/expdir_810418.png

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:

/wp-content/uploads/2015/10/impok_810570.png

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 🙂 . 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.

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply