Hello All,

My query is i have to create an additional index on one of the standard readily used table directly in the test environment...

So i want to know what are the things do i have to keep in mind before and after adding the index so that it does not give any fatal error wherever this table is used...

Cheers,
Abattoir

One point to be considered:

If you add a new Index the datamodell has to be considered, e.g. the primary key for the table has to be in your new index otherwise data loss can happen.

Example:

Standard Table:
Index 1:
Field: item (no duplicates -> primary Index)

Your new Index 2:
Field: myf1 (with duplicates) (could be some kind of text string).
Field: item (without duplicates)

If you would only chosse Index2 with field myf1 (without duplicates) and you run an reorg Table, it could happen that the system clears your table because of the wrong index definition, so it will realy use myf1 as a primary index.

You can check this procedure e.g. with tiitm001 (having multiple indices). The primary field "item" is included in all indices (but in different order what is ok). The first index of the standardtable can be used as a guideline what are the primary key fields to identify one datapoint (e.g. tdinv001, here you have the combination of warehouse and item, so item itself would be not enough.

Try it on the testsystem, make a seqdump before, and check with count numbers of records before and after if everything is ok.

In Baan 4 Tools, you can't choose which index to use on a table reorg, can you?
It will just use the primary index (obviously with no duplicates)

As stated already above, all fields of the primary key should always be included. This ensures that your index is not more restrictive then the Baan standard, which would most certainly lead to the software crashing.

@EddHubard:

No, when you define additional indices, the create index command will be passed to the database by the database driver of baan for all defined indices e.g. in oracle, therefore if you define a wrong additional index (only a not unique customized field, without the primary key fields), it can happen that you have data loss ...

Example:

Item master
Define additonal field containing a number or not
Define additional index only with this customized field and option "no duplicates"
Convert to runtime with reorg table
Only records containing a number in this field exists now. Item recordsets without having a number or duplicates are lost.

Well I think I understand ... you mean if you, in error, add an index without including the key fields and do not tick duplicates, then you may lose data?


.... we have added an index on tdsls045 of invoice date (invd) only. Duplicates = yes.

This has worked fine in SQL Server on for some years so I presume my interpretation above is correct.

thanks
Ed

Dear Ed,

you are totaly right, with your proper setting it is no problem.

But if it is the first time you add an index and you are not experienced with it, such things can occur (and have occured some years ago, of course with the item master table ... we finished 3 a.m. with the backup procedure) ... so I just wanted to point on this possible error, that abatoir don't get trapped.
Don't know if he is more programmer or dba.

Best regards to Malvern,

erpengineer