Batch BTREE Index Modification to Merge via OpenRoad

Hello again!  Today I'm going to share a minor utility I use at least once a month to optimize BTREE indices in our databases.  Quoting from the INGRES help file:

"In order to maintain good concurrency and performance, the btree index is not rebuilt after deletions. Deletions occur at the leaf and data page level, but an empty leaf page is not released. If your environment is one where many deletions are performed, you should occasionally issue the modify to merge statement.
Modify to merge is also important for users with incremental keys, which may incur lopsided indexes after heavy appends to the end of the table.

Not updating the index to reflect unused leaf pages can cause the index to be larger than it might be. For example, if the employee table is keyed on empno, (ranging from 1 to 31) and you fire all employees with empnos less than 16, the btree index would not shrink but would be unbalanced.....

Modify to merge can be more convenient to run than the modify statement. The index is rebuilt, and empty leaf pages are marked as free, but otherwise leaf and data pages remain untouched; therefore this statement is neither as time-consuming nor as disk-space intensive as the modify to btree statement. Modify to merge can be used only with btree tables."

For a database with hundreds of tables it would be time consuming to run the command for each existing BTREE table so I made a simple image which runs the "execute immediate" SQL on each qualified table it finds.  Below are screenshots of the image at work:

Main Window

Click on the ellipsis button to open the Node selection widow.

Node Selection Window

In the frame select the node you want then click on "Select".  If connection to the selected node is successful the previous frame will be populated with a list of existing databases as shown next.

Populated Main Window

Select the database you want to optimize then click on the "Modify to Merge BTree Tables" button.  A new frame will open showing all the available BTREE tables in your selected database as shown below.

To start the process click on the OK button.  The image will then run the modify to merge command on all the tables listed. 

And that's it.  In my personal experience it does speed up the database processes a bit.  You may want to try it too if you are experiencing lags in your frontend apps.  Fair warning though, I suggest running the image when no other user is logged-on.  And one more thing, you must have INGRES privilege to be able to run the actual modify procedure. :)

Here are the links to the actual images files: OR 3.5 OR 4.1

Create a shortcut link to the image using the following target:

OR 3.5: %II_SYSTEM%\ingres\bin\W4GLDEV.EXE runimage <PATH>\u_btree_mod35.img –Tno
OR 4.1 & Up: %II_SYSTEM%\ingres\bin\W4GRUN.EXE <PATH>\u_btree_mod41.img –Tno

<PATH> is where your image is located.  There is no need to specify the node::db pair in the link as the image will let you choose where to connect.

I'm sure there are far better images created for this purpose but for those who find mine useful please do leave a comment. LOL. Cheers!

Live INGRES Database Session Monitoring via OpenRoad

Have you ever wondered if it is possible to actually monitor your INGRES database for possible deadlocks, runaway sessions (with the <ANY> or <OTHER> lock status)?  Could it be possible to terminate locking sessions via an OpenRoad image Well, in that case you're in luck, LOL.

This project was mainly created for those two reasons stated above.   Anyone who has used IPM before will understand that with hundreds of open sessions in the database it is almost impossible to pinpoint a culprit session holding a lock and is causing havoc with your daily grind. :P 

With this utility you can actually locate the locking session, and decide whether to terminate it or inform the session user of the problem.  Well, actually you don't even need to look for the session using the image per se - the image itself determines the session. :)  And that makes all the difference!  Imagine - no more downtimes because of a long and determined search for that nuisance session.  No undue stress because of the pressure from higher-ups to make quick and do jumping jacks if by-all-means that solves the problem. LOL.  You don't have to do anything at all, the program has a built-in alarm system which will inform you via the frame and a literal alarm if you opt to use the audio component of the image. 

The audio component by the way is another creation of mine, it is actually a 3GL wapper DLL (WinAPI.DLL) for various Windows thingamagigs.  You may find it very useful if you want INGRES to do something else than the usual stuff. 

Now for the files you need:

1. The DLL along with the 3GL wrapper export file ang the manual are in WinAPI.ZIP.
2. The actual images for monitoring (dbmon35.exp and dbmon41.exp) as in Images.ZIP.

Note: I only compiled for OR 3.5 and 4.1 but if needed I can make images for OR 2006 and OR5 if I can get my hands on those. LOL.  Anyways the 4.1 image seems to work on higher versions thanks to backward compatibilites between these versions.

3. The manual to get you started - DBMonitor.PDF.

Don't take my word for it, if you have a spare server/database you can always test if the image actually works.  One important note though, you will have to do some tinkering with your IMADB to make this image work.  The procedure is there in the manual, and it slightly varies with different installations.  A little hassle in exchange for a life of INGRES DBA bliss and peace of mind. :D

So there... If you have any trouble making the thing work, you can always contact me for more info.

BTW, the DLL and images are completely free, but if you find them useful in your daily DBA life you may donate thru my Paypal Account:

Below is a screenshot of the images main frame, all others are in the manual.  Happy monitoring!