Migration Error - SQL1505 -Create unique index aborted on duplicate key

[Former Member]

Hello Gurus,

We are doing a test OS/DB migration for SAP ERP 6.0 EHP6 on Windows 2008 R2 / Oracle 11.2.0.3 to  RHEL 6.4 / SAP ASE 16.0.

During the DB Import on the target system , ABAP DB import [ package import] is getting failed with the below error  during the COEP-Post [ index creation] part.

==========================================================================================================

/usr/sap/DEV/SYS/exe/run/R3load -i COEP__DT.cmd -dbcodepage 4103 -k 1G5fdEM50DwIrBzjt6TE5Pae -l COEP__DT.log -c 99000000 -loadprocedure fast

(DB) INFO: connected to DB

(RTF) ########## WARNING ###########

        Without ORDER BY PRIMARY KEY the exported data may be unusable for some databases

(SQL) INFO: Searching for SQL file SQLFiles.LST

(SQL) INFO: SQLFiles.LST not found

(SQL) INFO: Searching for SQL file /mediadump/DEV_Export/ABAP/DB/SQLFiles.LST

(SQL) INFO: found /mediadump/DEV_Export/ABAP/DB/SQLFiles.LST

(SQL) INFO: Trying to open /mediadump/DEV_Export/ABAP/DB/SQLFiles.LST

(SQL) INFO: /mediadump/DEV_Export/ABAP/DB/SQLFiles.LST opened

(SQL) INFO: Searching for SQL file APPL1.SQL

(SQL) INFO: APPL1.SQL not found

(SQL) INFO: Searching for SQL file /mediadump/DEV_Export/ABAP/DB/SYB/APPL1.SQL

(SQL) INFO: /mediadump/DEV_Export/ABAP/DB/SYB/APPL1.SQL not found

(DB) ERROR: DDL statement failed

(DROP INDEX "COEP"."COEP~0")

DbSlExecute: rc = 103

  (SQL error 3701)

  error message returned by DbSl:

[ASE Error SQL3701][SAP][ASE ODBC Driver][Adaptive Server Enterprise]Cannot drop the index 'COEP.COEP~0', because it doesn't exist in the system catalogs.

(IMP) INFO: a failed DROP attempt is not necessarily a problem

(DB) ERROR: DDL statement failed

(CREATE UNIQUE  INDEX "COEP~0" ON "COEP" ( "MANDT", "KOKRS", "BELNR", "BUZEI" ) with consumers = 3 )

DbSlExecute: rc = 99

  (SQL error 1505)

  error message returned by DbSl:

[ASE Error SQL1505][SAP][ASE ODBC Driver][Adaptive Server Enterprise]Create unique index aborted on duplicate key.  Primary key is '"400", "1000", "0000000001", "001"'

(DB) INFO: disconnected from DB

================================================================================================

This is one of the largest tables in our DB, and the table itself was split into 30 packages and all of them were imported successfully.

Since this is a critical table,  we do not want to proceed without creating the index by marking the indexes in the .TSK files with ign and then continuing the DB Import.

Also, we have also updated the Kernel & DBSL to the latest 721_EXT PL 300 and the R3load /R3trans  disp+work etc to the latest 311 PL.

Additional information -

As we were getting the error - Create index with sorted_data was aborted because of row out of order. Primary key of first out of order row is '"400", "SPOOL0000010924", 1, 36' [ similar errors for few other packages ]

As per SAP Note 1836598 - SYB: Heterogenous system copy with target database ASE fails with SQL error 1530 , we already copied the

DDLSYB_LRG.TPL to DDLSYB.TPL in the installation directory. So now this error has also been rectified and now the Import is stared without sort order.

Also , after following this approach also, we continued to get errors only for 2 packages  [ both indexes ]  i.e. SOFFCONT1-post & CMFP-post packages .  We checked the packages and these are 2 indexes were failing, So we chose to mark the .TSK file with the IGN [ignore] option for these packages and proceed with the DB Import and build them once the import is completed.

Is this a viable option / recommended approach ?

Please advise.

Warm Regards,

Prashant Vijaydas

[Sriram2009 · Accepted solution]

Hi Prashant

Could you refer the SAP Note 1828873 - SYB: Index corruption during system copy

BR

SS

[Former Member · Accepted solution]

Thanks Sriram,

The Note suggested by you matches our error symptoms exactly.

However I had tried the solutions mentioned in the SAP Note , without success.

We tried to remove the ' with consumers'  clause from the DDLSYB.TPL and once this didnt work, we also tried to place the -T549 \ trace parameter in the RUN_<ASESID> script as well.

Tried to restart the ASE Server as well, but neither option worked for us and we still continue to get the Error.

Should we reboot the whole server so that the ASE parameter takes effect

[Former Member]

Hi RB,

Thanks for your suggestions, we did consider creating non unique index for this table..

However the application team decided against skipping the creation of unique index and creation of the non unique index.

As this was our largest functional and financially important customer table , we decided against skipping creation of the unique index .

Moreover on the source system , the table size was over 280 GB and the Primary index was around 55 GB, this mean that a later attempt to create the unique index would possibly take a similarly large amount of time for creation [ May take lesser time as well, but this risk was higher].

Instead  SAP support advised us that this happened due to our earlier failed attempts during import of the COEP table , which were cancelled due to procedure cache shortage [ which was later rectified]

As a result of this failed attempt, there was a possibility that the index  creation for COEP was already initiated. SAP advised us to drop/ truncate the table  and retry the import for the concerned table.

We tried this option and it worked for us.

Regards

Prashant

[Reagan]

As far as I know it is not really a recommended practice if it is not suggested by SAP.

I would consider updating the dbsl and R3* files (Eg: R3load) and see if that helps.

If the index creation still fails and the I would consider the same approach like you did by modifying the .TSK files to ignore the index creation task. After all I know indexes can be created once the import has been completed.

Regards

RB

[Former Member]

Thank you RB,

We had already updated our DBSL and R3* files to the latest as per SAP notes and this was helpful to fix some of the earlier errors [ like DB connectivity issues] , however the Unique index creation failure due to the Duplicate Key error persists.

As I previously mentioned , we already tried the ign option in the .TSK files for some of the smaller tables already, but since this is the most important and the also the largest functional table used , we were  not comfortable in doing this after the DB Import , hence our dilemma [ whether to skip this and create the indexes later  and if we do skip this , what is the guarantee that this Unique index creation failure due to duplicate key problem may not occur once the DB import has finished as well ]

Regards

Prashant

[Reagan]

If the index creation fails after the import with the same error then I would consider creating a non-unique index after consulting with the application team.