rc=-7055 (POS(57) Column(s) already indexed

[Former Member]

Hello,

I am trying to perform an import via R3load on MaxDB 7.7. So far everything works fine, except for one table/index.

I always get this error message:

ERROR : Execute for create index /BI0/E0APO_C02~P on /BI0/E0APO_C02 failed (dbrc=99).
  (SQL error -7055)
  error message returned by DbSl:
SQL-Statement: CREATE  INDEX "/BI0/E0APO_C02~P" ON "/BI0/E0APO_C02" ( "KEY_0APO_C02P" , "KEY_0APO_C02T" , "KEY_0APO_C02U" , "KEY_0APO_C021" , "KEY_0APO_C022" , "KEY_0APO_C023" , "KEY_0APO_C024" , "KEY_0APO_C026"  )
POS(57) Column(s) already indexed:/BI0/E0APO_C02~P

Via Google I found a discussion stating that in MaxDB, a primary key is something different than an index.

Consequently a primary key does not show up in system table INDEXES:

[http://bugs.mysql.com/bug.php?id=4848|http://bugs.mysql.com/bug.php?id=4848]

The strange thing is that in file SAPDFACT_1.TSK I can see that the problematic index is said to be ok:

SAPDFACT_1.TSK:I /BI0/E0APO_C02~P C ok

I have dropped the primary key of tabe /BI0/E0APO_C02, now the import fails with the same error message but this time on table /BI0/E0APO_C07.

Is it a bad idea to drop the primary keys? Or how could I fix this problem so that my import can finish successfully?

Regards,

Mark

[markus_doehr2 · Accepted solution]

Just as additional note:

those kind of errors usually arise if

SMIGR_CREATE_DDL

is not executed in the source system and hence there is no SAPDFACT.SQL available to create those kind of tables in a database specific manner.

However, I get his error too on every MaxDB import I'm doing despite having the .SQL-files - and I simply put "OK" in the corresponding .TSK file to proceed.

Markus

[Former Member · Accepted solution]

Hello Markus,

I searched the export dump, there is no SQL file at all. So it looks like SMIGR_CREATE_DDL wasn't executed.

The funny point about all these "primary key" indexes is that they already have the status "ok" in the TSK files.

There is nothing to override. The error about index /BI0/E0APO_C02~P shows up in six

different logfiles, not only the package which contains table /BI0/E0APO_C02:

SAPDDIM.log:DbSl Trace: EXECUTE on connection 0, rc=-7055 (POS(57) Column(s) already indexed:/BI0/E0APO_C02~P)
SAPDFACT_1.log:DbSl Trace: EXECUTE on connection 0, rc=-7055 (POS(57) Column(s) already indexed:/BI0/E0APO_C02~P)
SAPDFACT_2.log:DbSl Trace: EXECUTE on connection 0, rc=-7055 (POS(57) Column(s) already indexed:/BI0/E0APO_C02~P)
_BI0_PCS_ORDER.log:DbSl Trace: EXECUTE on connection 0, rc=-7055 (POS(57) Column(s) already indexed:/BI0/E0APO_C02~P)
_BIC_B0000527001.log:DbSl Trace: EXECUTE on connection 0, rc=-7055 (POS(57) Column(s) already indexed:/BI0/E0APO_C02~P)
_BIC_FZ0C031.log:DbSl Trace: EXECUTE on connection 0, rc=-7055 (POS(57) Column(s) already indexed:/BI0/E0APO_C02~P)

Regards,

Mark

[markus_doehr2 · Accepted solution]

> I searched the export dump, there is no SQL file at all. So it looks like SMIGR_CREATE_DDL wasn't executed.

This is not done by sapinst but has to be executed manually. Newer version of sapinst have a popup requesting that, older versions do not (see Note 888210 - NetWeaver 7.0/7.10: System copy (supplementary note))

> SAPDDIM.log:DbSl Trace: EXECUTE on connection 0, rc=-7055 (POS(57) Column(s) already indexed:/BI0/E0APO_C02~P)

> SAPDFACT_1.log:DbSl Trace: EXECUTE on connection 0, rc=-7055 (POS(57) Column(s) already indexed:/BI0/E0APO_C02~P)

> SAPDFACT_2.log:DbSl Trace: EXECUTE on connection 0, rc=-7055 (POS(57) Column(s) already indexed:/BI0/E0APO_C02~P)

> BI0PCS_ORDER.log:DbSl Trace: EXECUTE on connection 0, rc=-7055 (POS(57) Column(s) already indexed:/BI0/E0APO_C02~P)

> BICB0000527001.log:DbSl Trace: EXECUTE on connection 0, rc=-7055 (POS(57) Column(s) already indexed:/BI0/E0APO_C02~P)

> BICFZ0C031.log:DbSl Trace: EXECUTE on connection 0, rc=-7055 (POS(57) Column(s) already indexed:/BI0/E0APO_C02~P)

So I assume this is a BW system (seing all the cube names).

I'd really stop the efforts on the import here. Without the DDL files it's a tedious task to get this import through properly.

I suggest you start the export again by following above note (and executing the report). Especially for BW systems it's crucial to run that.

Markus

[lbreddemann]

>

> 
ERROR : Execute for create index /BI0/E0APO_C02~P on /BI0/E0APO_C02 failed (dbrc=99).
>   (SQL error -7055)
>   error message returned by DbSl:
> SQL-Statement: CREATE  INDEX "/BI0/E0APO_C02~P" ON "/BI0/E0APO_C02" ( "KEY_0APO_C02P" , "KEY_0APO_C02T" , "KEY_0APO_C02U" , "KEY_0APO_C021" , "KEY_0APO_C022" , "KEY_0APO_C023" , "KEY_0APO_C024" , "KEY_0APO_C026"  )
> POS(57) Column(s) already indexed:/BI0/E0APO_C02~P
> 
> Via Google I found a discussion stating that in MaxDB, a primary key is something different than an index.
> Consequently a primary key does not show up in system table INDEXES:
> [http://bugs.mysql.com/bug.php?id=4848|http://bugs.mysql.com/bug.php?id=4848]
>  

Wow, you managed to ignore the documentation but instead you had been able to cram up some super old bug DB entry... WOW!

Seriously - a primary key is never an index. In no DBMS.

Primary key are a semantical construct - not a physical.

Oracle e.g. uses indexes to enforce primary key constraints.

MaxDB does it different in so far as the B*tree structure used to store the data allows to enforce the primary key without an additional index. Pretty much like Oracle's IOTs (index organized tables).

>

> The strange thing is that in file SAPDFACT_1.TSK I can see that the problematic index is said to be ok:
> 
SAPDFACT_1.TSK:I /BI0/E0APO_C02~P C ok
> 
> I have dropped the primary key of tabe /BI0/E0APO_C02, now the import fails with the same error message but this time on table /BI0/E0APO_C07.
> 

No, you didn't drop the primary key.

You dropped the P-index, which is something completely different for our E-fact tables!

Basically what you should have looked for is whether there is another index already in place covering these columns.

Maybe this one just has a different name...

>

> Is it a bad idea to drop the primary keys? Or how could I fix this problem so that my import can finish successfully?
> 

Yes, dropping any db objects manually is not the best idea. Especially when you're unsure about what you're actually doing.

Better go and check for the root cause of this - it's likely that there is a bug in the DDL export or something else is not correct on the source database.

If in doubt, you may also open a support message and we'll have a look at this directly.

regards, Lars

[Former Member]

Hello Lars,

thanks for your reply, it is always a pleasure reading your contributions (blogs, forum posts etc.).

Wow, you managed to ignore the documentation but instead you had been able to cram up some super old bug DB entry... WOW! 

Honestly, I was searching for any hint what might cause the problem, but I couldn't find any documentation. Please help me, which documentation did I ignore?

Basically what you should have looked for is whether there is another index already in place covering these columns. Maybe this one just has a different name...

This was exactly what I was doing. I was searching in table INDEXCOLUMNS and didn't find anything which could explain the error-7055.

Yes, dropping any db objects manually is not the best idea. Especially when you're unsure about what you're actually doing. Better go and check for the root cause of this - it's likely that there is a bug in the DDL export or something else is not correct  on the source database.

The export wasn't done by me and currently I am not familiar enough with MaxDB to decide whether the DDL export looks correct or not. This is the very first test import, so if I break something in the DB, better now than later. Thanks to Oracle I believe I will see many more MaxDB migrations in the next years, so I am willing to learn ...

If in doubt, you may also open a support message and we'll have a look at this directly. 

The call is open (279025). However I chose to ask a question here in the forum because I would like to understand what was going wrong. With the support call it might happen I get a fix but no real insight.

Best regards,

Mark

[lbreddemann]

Hi Mark!

Thanks for the nice things you wrote about my SCN activity.

Lately I don't find too much time to put into this, which is the reason for the late reply.

So, let's see:

Honestly, I was searching for any hint what might cause the problem, but I couldn't find any documentation. Please help me, which documentation did I ignore?

Hmm.... [this one|http://maxdb.sap.com/doc/7_7/b3/a240ccfa3e4eb8b4c7927cb6d9d0a6/content.htm] for example

And, yes, the error message could (maybe should) be a bit more descriptive.

It can also be that the primary key definition prevents the creation of an index.

The export wasn't done by me and currently I am not familiar enough with MaxDB to decide whether the DDL export looks correct or not.  This is the very first test import, so if I break something in the DB, better now than later. Thanks to Oracle I believe I will see many more MaxDB migrations in the next years, so I am willing to learn ...

Well, many problems with the DDL statements for homogenous system copies of BW systems have been adressed by the recent (and not so recent) BW support packages. These of course need to be in place before the export is done.

Repairing the export files is rather not a way to go...

 The call is open (279025). However I chose to ask a question here in the forum because I would like to understand what was going wrong. With the support call it might happen I get a fix but no real insight.

Well, being a support guy myself (not DB anymore, but BW instead) I can only tell it from my point of view.

There are many customers who really don't want to be educated in any way.

What they want is a fix, a solution. Right now.

After a while, one stops to provide lengthly explanations about how stuff is supposed to work and simply hands out the requested solution (if possible).

Of course it's completely OK to post the question here as well and it even benefits the public as the discussion can be read by others as well.

On the other hand, you can also just ask your support consultant about the background - you really should get an answer then.

So, keep us updated on this and what solution you were given.

Best regards,

Lars