We are trying to create an index on a table which has just 4,400 rows, but each one can be massively multivalued. We want the first column to be btree-indexed, case insensitive.

run create_index 1,'PRICE_HISTORY','CODE',0,1

It takes an awfully long time to create in each of the test environments (approx 30 hours), but it's refusing to work in the production environment. (There's one major difference between test and prod - the prod environment is set to have the database split over multiple drives)

The first time we tried it we got this output:

run create_index 1,'PRICE_HISTORY','CODE',0,1

%INIT%

1, PRICE_HISTORY, CODE, 0, 1, 1, , , , 

FS220

We haven't been able to find any hints on what FS220 refers to.

We looked at the dictionary and found a calculated column which referred to the CODE column. Even though there shouldn't have been any impact, we did remove it. Attempting to create the index again didn't return the FS220 message, but still didn't work (it just sat there, system monitor fully responsive).

I've just tried deleting and re-creating the %FIELDS% record, and ensuring there was no pre-existing !PRICE_HISTORY table. I'm seeing the FS220 message again (with the calc column still completely removed)

The next step will be detach and re-attach the volume, but as it's the production environment that has to wait for an out-of-hours maintenance period. And we're not altogether confident it'll make any difference.

Has anyone else seen any similar behaviour?

Does anyone know what FS220 is?

Any help would be much appreciated!

Ross

If it is taking that long then there is something else going on.

The FS220 is defined thusly

EQUATE FS_REL_NEED_REBUILD_ERR$ TO 220 ;* relational index needs rebuilt; field_name.

so it sounds as though it is attempting to update a relational index which may in turn point to a much larger file that the system needs to read each row of to ensure that it is up to date. Is the PRICE_HISTORY file relationally indexed to or from?

The Sprezzatura Group

The Sprezzatura Blog

World leaders in all things RevSoft

Thanks for the reply: there aren't any relational indexes referring to this table… we've only got 6 in the whole application.

Given that you're doing this programmatically gave you used get_status to check for any error codes?

The Sprezzatura Group

The Sprezzatura Blog

World leaders in all things RevSoft

Is there any stray relational information in the production dictionary?

If you place an empty record named !! in the ! file, then add the index, it should show you the source for the indexing code, that might help ( I'm sure Sprezz published a REVMEDIA article on this technique)

Are you in 9.21?

If yes and you have run the client install, look at the SYSENV record named IDX_SETS, set it to IDX_SETS2, restart OI. That causes OI to use a .net based sets function instead of the legacy C based sets dll. We put in that sets function for another customer who had a similar problem building huge indexes. I don't recall if the error was FS220, but I believe it allowed the index builds to succeed.

View this thread on the forum...