Issue creating BTREE Index

SRPSupport

I am wanting to create a BTREE index on a field. But when I try to create it. I receive a pop-up stating that I can't create a index while I have a rightdex or quickdex installed on the table.

How do I go about creating a BTREE index?

I have thought about removing rightdex and quickdex but I am afraid that I will do some damage to the data or forms already in use.

Please guide me in the right direction.

Thank you

User: Mark Mayfield

DonBakke

Hi Mark,

That is the first I have heard of a BTREE index refusing to be created because of an existing quick/rightdex. What version of OpenInsight are you using?

In my opinion something else is going on so I would tread carefully. At least make sure you have a backup of this table just to be able to quickly recover. I am not worried that you will lose data, but the database pointer system could be messed up enough to cause OpenInsight to fail to access the table without some additional surgery.

If you are comfortable moving forward then you could attempt to remove the quick/rightdex and then add the BTREE index. Here is what you would need to do:

1. Use the Table Builder tool to open the dictionary for this table.
   
2. There should be a top level menu labelled Indexes.
   
3. Under this menu there are two options: Add/Remove Quickdex and Add/Remove Rightdex. Unfortunately, this tool does not make it clear which one you have installed. In theory it could even be both but that is rare. All you can do is to try both of them, starting with Add/Remove Quickdex and following the prompts. These prompts have changed in the different versions of OpenInsight but the latest version gives the following options: Update DBT, Ignore DBT, and Cancel. Select Update DBT. If the Quickdex already exists then you will get a warning confirming that you want to remove it. Say Yes. If the Quickdex does not already exist then you will have to go back and repeat the above steps and remove it. Then do the same thing for Add/Remove Rightdex.
   
4. Once you have confirmed that the quick/rightdex has been removed you can then go to the Database Manager tool and use the Utilities->Indexes->Add->Btree menu option to try and add the BTREE index.
   
5. If this works out then you can always go back to the Table Builder tool and add the quick/rightdex back to the table in case the application depends on this.

Let us know how this works out or if you have any more questions.

SRPSupport

Hi Don,

We are running version 9.2.1.

I did as you directed and clicked on the Update DBT for both Quickdex and Rightdex. For both of them I installed the index. This tells me that we were not using either one right?

After uninstalling after I installed them. I was still unable to create a Btree index for the same reason. I believe this table was in arev based on the location of the table location. I don't know if this is an issue.

What do you recommend now.

Thank you

User: Mark Mayfield

DonBakke

Mark,

Correct, your results suggest that neither one existed on the table. Thus, as I suspected, something unusual is going on with this table.

It should not matter that this was originally an AREV table, unless the AREV table is pre-AREV 3.x. I doubt this is the case or you would have had other problems within OpenInsight by now.

There is another possibility that I did not originally consider. Usually a quick/rightdex is added to the data portion of a table. This is what the Table Builder tool does. However, on occasion developers will create a quickdex on the dictionary portion of the table. This can be done/undone using the Set_MFS subroutine.

Before you delve into exploring this subroutine and writing code, there is a simple way to confirm the existence of a quick/rightdex: a row named %RECORDS% is added to the table. So you might want to use a system editor to see if this row exists either in the data table (unlikely based on what you reported) or the dictionary table. If you find it then we still need to remove the quick/rightdex. If you do not find it then we are back to square one.

SRPSupport

By looking at the table attributes I coped the 6 files associated to the table indicated by the filename, dictionary, and Index.

I opened all 6 files in note pad and found one that had the following data.
%RECORDS%%FIELD.COUNTER%þ%FIELDS%þ%INVOICE.NO*SEQ.NO%þ%LOCAL.GROUP%þ%PROTECT.SPEC%þ%SK%þ%WINDOWS%...

It was in the .OV file for the filename.

Is this what you were looking for?

User: Mark Mayfield

DonBakke

Mark,

Yes, this is what I was looking for. However, I think you mistook my suggestion to use a "system editor" for a "text editor". I meant the OpenInsight editor (which there are three: System Editor, Editor+, and Editor++.) Then there is our own SRP Editor which we obviously consider to be better than all of them. ;-)

Well the information you posted tells me enough. It is clear that the quickdex was added to the dictionary table. The other items listed - %FIELD.COUNTER%, %FIELDS%, %SEQ.NO%, etc. - are all dictionary specific rows.

Okay, well the good news is that I think we have found the cause of your BTREE index problem. The bad news is that you will have to delve into Basic+ code to remove the quickdex. As mentioned above, you can use the Set_MFS routine to do this. Actually, you can do this without actually writing code. You can enter this single command in the System Monitor as such:

RUN SET_MFS 'DICT.foo', 'QUICKDEX.MFS', 5

Replace foo with the actual name of your table. I think it is probably optional for you to add the quickdex back since it is unlikely this was done for the end user application. This is usually done for the benefit of the developer. But if you *did* want to add it back then just enter this:

RUN SET_MFS 'DICT.foo', 'QUICKDEX.MFS', 1

SRPSupport

Don,

That fixed it. :-) I am not going to re-install Quickdex unless we see some side effects.

Thank you for your quick response.

User: Mark Mayfield