Is there a way to automate the indexing of one field, at night, in AREV 2.12?

Problem:

Large file

24 indexes

It takes too long to rebuild even at night due to network/server backup interference.

I want to rebuild each index on a rotating basis at night. One per night.

I think that I am on to something with 'IX.REBUILD' but when I run the command-line:

IX.REBUILD MYFILE INDEXEDFIELD

or

IX.REBUILD MYFILE INDEXEDFIELD BTREE

I get the following error:

'F.DISTRIBUTOR' Line 422. B10 Variable has not been assigned a value. Zero used.

(In my limited experience this usually means that I am not feeding a proper variable at the TCL)

What I would like to do is write a simple R/Basic program that uses something like:

PERFORM "IX.REBUILD MYFILE ":MYFIELD

Hopefully, you get the idea. Any hints?

Thank, in advance.

Marty

Why are you rebuilding indexes? This only needs to be done in extremis.

The Sprezzatura Group

World Leaders in all things RevSoft

Sorry, please bear with me on this because AREV is not my expertise.

My background is SQL-based (SQL Server and Oracle) so the concepts might not carry over.

I inherited the AREV db as part of my job and, to the best of my knowledge, you are correct.

UPDATING the indexes as a part of regular maintenance should be sufficient but there have been a

couple of reasons why we have been occasionally rebuilding them.

- We have seen speed improvements with rebuilding vs.updating

- Also DUMP fixes have been significantly less using rebuild. The overall integrity of the db seems vastly improved when using REBUILD.

- The network is an older Novell which is slow and may have added to some problems. Rebuilding seems to help the response time.

Once again, however, I am still learning a lot with AREV but, in the past, with other db-engines, I have learned that a regularly, scheduled rebuild can be beneficial to the tables because, with an UPDATE, the pointers to the proper records are the only items that are being updated. But, with a REBUILD the records

are physically examined, tested and re-ordered to insure that they are optimized.

Don't get me wrong, I am not advocating rebuilding a table every day but our day-to-day usage has proven that once in a while it can help. If you have any alternative maintenance suggestions

I would greatly appreciate them. Also, please let me know if you can help with the original question.

Thanks again,

Marty

Martin:

I have a routine that does exactly this. I'll be happy to email you the source to the two modules if you send me your address.

We use this nightly to rebuild an index on a (gasp!) XLATE symbolic.

Jim

jim.jefferson@aegismtg.com

Marty - the clickable link in our original message WAS the answer to how to force an index rebuild programmatically .

We'd be interested in examining your system because whilst a rebuild will create the index in it's optimum form, routine updates shouldn't impact too badly on performance. However on LARGE databases (millions of rows) there are some issues in AREV which a rebuild will address.

Are you not using a Linear Hash NLM?

The Sprezzatura Group

World Leaders in all things RevSoft

A couple of things…

First, to Jim. Thanks for the reply. I will try the suggestion from the Sprezzatura msgs but if I have trouble I will be in contact.

Next, to the Sprezzatura folks. Thanks, also, for the help.

I missed the clickable-link the first time that I glanced at your reply. I followed the link and will give it try.

Also, the file that I am working with is not as "high" as it is "wide". Approx. 25000 recs but 1006 fields of data (not including symbolics).

Lastly, I am not sure about the NLM. I have read a couple of things about the NLMs in this forum but I have not yet found a "Beginner's guide to NLMs" on the website. Like I said, I inherited this db so I am kind of keeping my fingers crossed that my predecessor knew how to (a)install the NLM and (b)that it has been installed. How can I tell if it is installed? A good link (along with some advice) might be useful here.

Thanks again for the terrific help.

Marty

No problem. What I have is a production version of the code Sprezzatura pointed you to. Just let me known if/when you want it…

Jim

jim.jefferson@aegismtg.com

Sorry about that - we all have different posting styles in the Sprezz Borg and I'm not known for my prolixity. I can't touch type after ] 20 years with computers - shame!

The size shouldn't be creating a problem then unless the values being indexed are very large (length wise).

The simplest test for the NLM would be to go to TCL (F5) and EDIT FILES VOC (if this displays an error message EDIT SYSTABLES VOC.

You should see FFFFFF in the file handle. If you do it is there. If you don't it either

isn't there

isn't started

or you don't have the right network driver loaded to recognise it.

The Sprezzatura Group

World Leaders in all things RevSoft

I hate to be a pest but apparently the NLM is not there. The EDIT command showed the record with the file, account, volume, etc. but the filename was a 'normal' file name (something like…0000000000rev03.lk).

Also, I tried your code on a test-file and it did not work. I DEBUGged thru the code and everything went fine until it hit the last statement INDEX.FLUSH. The funny thing is that I get the exact same error that I mentioned in my first posting in this thread. The only thing that looks odd is the last variable in VOL_INFO. Does it matter that there are spaces in the volume.label (VOL_REC)? In other words, my (your) last line of code would execute as…

CALL INDEX.FLUSH("MYFILE*GLOBAL*HR1 V5.XX", "MYINDEX") ←–without the quotes.

Obviously, this is HR-1 that I am using but is the space in the label causing the problem?

Let me know what you think.

Thanks,

Marty

Matry

The NLM is an add-on that costs money so it isn't always there . Sort of a turbocharger for your network as it were. The thing is a well configured network with an NLM will almost NEVER get GFEs unless the server abends.

Anyway - the second parameter to the index flush should either be the column name OR a variable containing the column name - so

ColName=TITLE'

Call Index.Flush('PEOPLE', ColName)

Or

Call Index.Flush('PEOPLE','TITLE')

Note that the file name CAN just be the file name without the other guff - well it could in later releases of AREV!

The Sprezzatura Group

World Leaders in all things RevSoft

Spaces in the volume label do not matter. By default, the volume lables created are time date (with a space).

If you know how to change the network drivers, look and see if there's an NLM driver in the list. If it's not there, then you really don't have it. There's always the possibility that it was installed but no one changed the driver.

The Sprezzatura Group

World Leaders in all Things RevSoft

Believe it or not, I got tired of fighting with rebuild so I found an alternative.

Since the menu item entitled - "Rebuild one index on a file" has always worked, I

decide to use a macro. I programmed it using CATALYST. The code is something like…

CALL CATALYST('K','{F10}{A Whole Bunch of Keystrokes}'). I put the line inside of a timer loop so that it kicks-off around 7:30pm.

It is not exactly what I would call 'pure coding' but it works.

I am going to investigate the NLM issue some more when I have the time.

Thanks again for all of the help.

Marty

I posted the code to do a btree index with playback in this thread - http://www.revelation.com/WEBSITE/DISCUSS.NSF/f12696d31000b22a8525652b00831bb2/19bd939ff524cae785256d4e001f26b7?OpenDocument&Highlight=0,lynch,catalyst

There was this reply to the main topic from Sprezzatura - "Write numeric one onto field 6 of the dictionary item in question then call index.flush(FileName, ColumnName)."

That is a much cleaner solution which works on Arev 2.1 and 3.

The reply is the solution i have adopted for btree now, but i still use playback for creating xref and for deleting indices.

I have pulled the code for doing these playback sequences out of the source which manages indices - you can look at it here - http://patlynch.homelinux.org/manageix.htm

I have an index rebuilding utility that works in a variety of modes:

Interactively - displays a collector of all attached files with indexing. F9 the list will rebuild all the associated indexes to these files.

Command Line - filename*indexname (multiple file/indexes delimited by space)

Subroutine call - pass parameter same as command line above.

Works with all versions of ARev.

I can email it to you if you wish.

View this thread on the forum...