Comment Solaris - RHEL Migration horror story ...

RealHeavyDude

Well-Known Member
Did anybody encounter tables without a single index yet in an OpenEdge database?

By chance, only after the migration, I stumbled over 15 indexes which were residing in the schema area on the target system where they reside in the respective type II storage areas in the source system. Dammit - how can this happen?

Luckily - these are test systems with the same schema and roughly the same amount of data like the production systems.

It turns out, those 15 indexes are all named "default" and are missing from the DF file that has been dumped on the source system - meaning there are 15 tables in the source database which do not have a single index associated with them in the DF ...
So, when the DF was loaded on the target system those "default" indexes have been been created automagically along the way - and, as a bonus - reside in the schema area, with all sorts of negative side effects.

As a result I need to find a desperate solution to enhance the load script to cope with the index move of those "default" indexes before the binary load is invoked ...

Sometimes I really feel like a garbage man. Why in the world would anybody be so irresponsible to have a database table with no index ( not even speaking about uniqueness )?
 

TomBascom

Curmudgeon
Unfortunately you are not the first person to run into this :(
 

TomBascom

Curmudgeon
Tables without indexes are a blight upon the world best dealt with by taking care to ensure that the guilty parties are applying their dB design “skills” elsewhere.
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
Do your applications read the data in those 15 tables? Do you have CRUD stats and access to source? If so, now is the time to create at least one index for each table. Then you can assign them to areas and not have to deal with special load procedures.
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
Glad to hear you're moving to RHEL though :)
 

RealHeavyDude

Well-Known Member
Do your applications read the data in those 15 tables? Do you have CRUD stats and access to source? If so, now is the time to create at least one index for each table. Then you can assign them to areas and not have to deal with special load procedures.
I would love to. But, since I am not allowed to touch the production systems whatsoever and the time window for delivering the software package closes this Friday - I am hardly in the position to fix them now. But it is on m to-do list for beginning of next year.

Glad to hear you're moving to RHEL though
Me too.
Database backup ( roughly 500 GB ) on Solaris with ZFS file systems residing on a SAN: 9 to 11 hours.
On RHEL with XFS file systems residing on the same SAN: 2 hours.
 

RealHeavyDude

Well-Known Member
Forgot to add, I am really mad and I am really inclined to pillory somebody.
To me it is unacceptable that anybody can deliver schema changes without having to pass a review or quality toll gate.
 

RealHeavyDude

Well-Known Member
Just in case: Does anybody know how to identify such an index which was created automatically due to the table not containing an index definition?

I know their name is "default" - but I don't think using the name is sufficient as somebody could deliberately name an index that way.
 

TomBascom

Curmudgeon
I don't have a db handy that has a default index on a table and I'm too lazy to create one but as I recall there will be no fields associated with that index. So you could query:

find first _index-field no-lock where _index-field._index-recid = recid( _index ) no-error.

(Or CAN-FIND...)
 

RealHeavyDude

Well-Known Member
I think I found a solution: The default index does have an _Index-Field which actually points to an _Field with a blank name!

OE11.7.3 that is.

Code:
/* Inspect each index for the current table*/
for each _Index no-lock where _Index._File-Recid = recid ( _File ),
first _Index-Field no-lock where _Index-Field._Index-recid = recid ( _Index ),
_Field no-lock where recid ( _Field ) = _Index-Field._Field-recid:

    /* Default index automatically created due to table does not contain an index definition */
    if _Index._Index-Name = "default" and _Field._Field-Name = "" then
        assign defaultIndex = true.
               
end.
Thanks!
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
Does anybody know how to identify such an index which was created automatically due to the table not containing an index definition?
There are a couple of ways:
if _index._num-comp = 0 then // it's default or
if _index._index-name = 'default' then // it's default

It turns out 'default' is a reserved name. An index created by a user can't be named 'default', so if its name is default it's because it was created by the data dictionary code.
 
Top