MISSING INDEX:Index could not be created completely in theDB Long Text

[Former Member]

Hi,

We are trying to resolve a missing index in development system. We are

getting ZMM_TANK_ITEM-0 missing index in development system. We tried

resolving it Reorganization of database tables sap note 84081. But stillwe are not able to resolve at customer end.

When we try to create it with se14 we get following error:

***************************************************

The SQL statement was not executed

Index could not be created completely in the database

Index ZMM_TANK_ITEM-0 could not be created

***************************************************

Kindly provide your inputs on same as this table is productive but its

giving problem in development system

Regards,

Prasad

[christian_bartels]

Most likely there is another table in the database schema that has a primary key constraint with the same name. For example, if you have created a table with the same name and primary key constraint before and then renamed that table, the primary key name does not get renamed.

To identify the table that has the same primary key name, you can use interactive SQL (STRSQL, SQLUTIL or System i Navigator) and execute the following SQL statement:

SELECT TABLE_NAME, TABLE_SCHEMA

FROM R3E03DATA/SYSCST

WHERE CONSTRAINT_NAME = 'ZMM_TANK_ITEM+0'

After identifying the table, you can decide what to do with it: Drop the table or drop the primary key constraint. There is no rename function for the primary key, so the only way to "rename" it is to drop it and create a new one with a different name.

Kind regards,

Christian Bartels.

[Former Member]

Prasad,

You can either go through DB4COCKPIT or DB02. In DB02 you can click on "Missing Indexes', then click on "Create in DB", this should create those missing indexes for you.

[Former Member]

Hi Mike,

We tried that in db02/dbacockpit but as alreay intimated we are getting following error:

*********************************************************************************************************

Request: Create Index ZMM_TANK_ITEM-0 (SPLRFC/18.02.10/10:12)

Process: SPLDEVQA_2

sql:

ALTER TABLE R3E03DATA/"ZMM_TANK_ITEM"

ADD CONSTRAINT "ZMM_TANK_ITEM+0" primary key

("MANDT",

"ZTANKNO",

"ZMATERIAL",

"ZDATE")

"ZMM_TANK_ITEM+0" in R3E03DATA type *N already exists. MSGID= Jo

b=092590/E0303/WP02

DDL time(___1): .....9,848 milliseconds

The SQL statement was not executed

Index could not be created completely in the database

Index ZMM_TANK_ITEM-0 could not be created

Request for ZMM_TANK_ITEM could not be executed

*********************************************************************************************************

Regards,

Prasad