Monitor the Progress of OpenInsight 9 Index Update or Rebuild

When rebuilding or updating a large index in OpenInsight 9, the user interface can go into a Not Responding state even though the rebuild or update process continues to perform the task. This leaves the user uncertain if the process is working or when it might complete. This blog article provides a strategy for working around this issue so you can monitor the process.

Workaround Overview

OpenInsight uses the SEND_INFO routine to update the status line of the Database Manager and show the index maintenance progress. This article will explain how to intercept and redirect the SEND_INFO updates to a file so it can be inspected outside of OpenInsight.

Directions to Prepare Your System

Prior to building your index you must prepare your system. The following routine will act as a proxy which will receive calls to SEND_INFO, write them to a file, and then pass them on to the stock SEND_INFO routine to handle the normal tasks performed by the routine.

Add the following stored procedure and compile it. This can be compiled in any application, but if you want to use this in multiple applications then compile this within SYSPROG:

Function Send_Info_To(Text)
/*
    Proxy version of SEND_INFO subroutine that redirects the message to a file.

    Parameters  :
        Text    [in] -- The text being passed into the SEND_INFO routine.

*/

Declare function Send_Info_Stock

Common /SEND_INFO_TO/ sitFile@

If Unassigned(sitFile@) then sitFile@   = ''
If Unassigned(Text)     then Text       = ''

// Send this to the stock SEND_INFO function
Convert @RM:@FM:@VM:@SVM:@TM:@STM to '-' in Text

// Call the SEND_INFO alias so that the stock SEND_INFO routine can be called.
Ans = Send_Info_Stock(Text)

If sitFile@ NE '' then
    // Target file active, redirect output there.
    NewMsg = TimeDate() : ' ' : Text
    OSWrite NewMsg To sitFile@
end

Return Ans

Now we will add a routine to control the output of our proxy function and set up the required pointer redirections. For more information about the techniques used please see Hooking in BASIC+ and Hooking in BASIC+ Using Different Bait. In summary, the routine will do the following:

• Activates or Deactivates the redirection process.
• Creates the MD*SEND_INFO and MD*SEND_INFO_STOCK records if redirection is being activated.
• Verifies the redirection file can be created.

Create and compile the following procedure. Again, this can be done in any local application but use SYSPROG if you want to use this in all of your applications.

Subroutine Send_Info_Target(File)
/*
    Controls the output of SEND_INFO_TO subroutine.

    Parameters  :
        File        [in] -- The path to a file where output should be redirected. If path is empty then SEND_INFO will
                            stop being redirected.

*/

Declare function    RTI_OS_Directory
Declare subroutine  Send_Info, Set_Status, RTP27, Delete_Row, Write_Row

$insert Logical

// Set a named common so the SEND_INFO redirect knows which file to update.
Common /SEND_INFO_TO/ sitFile@

Error   = ''
If Unassigned(sitFile@) then sitFile@ = ''
If Unassigned(File) then File = ''

If File NE '' then
    // Create an MD pointer for our alias to the the stock SEND_INFO routine.
    TableName   = 'MD'
    AliasKey    = 'SEND_INFO_STOCK'
    Rec         = ''
    Rec<1>      = 'P'
    Rec<5>      = 'SYSOBJ'
    Rec<6>      = 'SEND_INFO'
    Set_Status(0)
    Write_Row(TableName, AliasKey, Rec, 0)
    If Get_Status(StatusCode) then
        Error   = 'Unable to setup hooking. Pointer record ' : TableName : '*' : AliasKey : ' could not be written.'
        Send_Info(Error)
    end else
        // Create an MD pointer to tell OpenInsight to call our redirect routine instead of the SEND_INFO routine.
        StockKey    = 'SEND_INFO'
        Rec         = ''
        Rec<1>      = 'P'
        Rec<5>      = 'SYSOBJ'
        Rec<6>      = 'SEND_INFO_TO'
        Set_Status(0)
        Write_Row(TableName, StockKey, Rec, 0)
        If Get_Status(StatusCode) then
            Error   = 'Unable to setup hooking. Pointer record ' : TableName : '*' : StockKey : ' could not be written.'
            Send_Info(Error)
            Set_Status(0)
            Delete_Row(TableName, AliasKey, 0)
        end
    end

    If Error EQ '' then
        // Both MD pointers have been created.

        // Verify the path and file of where we want to direct output and create if needed.
        FileName    = File[-1, 'B\']
        Path        = File[1, Len(File) - Len(FileName)]

        // Create the path to the redirect file if it doesn't already exist.
        If RTI_OS_Directory('EXISTS', Path) EQ -1 then
            Result  = RTI_OS_Directory('CREATE', Path, True$)
            If Result EQ False$ then
                Error   = 'Could not create ' : File : '.'
                Send_Info(Error)
            end
        end

        If Error EQ '' then
            // Update the named common with the path to the redirect file.
            sitFile@    = File

            // Touch the file so we know it's working.
            OSWrite 'SEND_INFO now directed to this file.' To sitFile@

            // Load the SEND_INFO alias and reload the SEND_INFO routine so OpenInsight will call our routine instead.
            RTP27('SEND_INFO_STOCK')
            RTP27('SEND_INFO')

            // Delete the MD pointers right away so this won't impact other users. This will also allow SEND_INFO
            // to be restored in case the current session aborts and has to be restarted.
            Delete_Row(TableName, StockKey, 0)
            Delete_Row(TableName, AliasKey, 0)
        end
    end
end else
    // For the reloading of the SEND_INFO routine. Since the MD pointers are removed, this will restore the original
    // SEND_INFO.
    RTP27('SEND_INFO')
end

Return

Your system is now ready.

Building Indexes and Monitoring the Progress

Prior to building or updating indexes, you need to instruct OpenInsight which file should receive the output of SEND_INFO. The file will be continually be overwritten whenever SEND_INFO attempts to update the status line.

1. Direct SEND_INFO to a file, e.g. c:\temp\progress.txt by executing the command from the system monitor:

   RUN SEND_INFO_TARGET "c:\temp\progress.txt"

2. Perform your index maintenance. While the task is running you can check the output file to view the progress. For easier monitoring the following powershell command will periodically display the content of the output file:

   Do {Get-Content c:\temp\progress.txt ; Start-Sleep -s 5} until ($False)

3. When the index maintenance is finished then stop redirection by redirecting the output to an empty file:

   RUN SEND_INFO_TARGET ""

You can leave your OpenInsight system configured with the routines above in case you need to do index maintenance in the future. Note, the MD redirect pointers are deleted immediately. Once the redirect has occurred, it is no longer necessary to keep them around. This will avoid problems with other sessions or situations when OpenInsight crashes.

Conclusion

I hope this technique better equips you to manage your OpenInsight system and feel confident that rebuilding an index while under pressure from management – or your users – is working. Looking forward to the future I’m pleased to see that OpenInsight 10’s index rebuild function continually updates the progress bar making this technique unnecessary. However, given the longevity of OpenInsight 9 applications, this should prove to be a viable solution to monitoring the progress of index maintenance.