Recently a Sprezz client's client experienced server issues and commissioned a new server to host the client's application. Shortly thereafter an index failure occurred and the client tried to rebuild the indexes in question. They were thwarted in their efforts however by the fact that no tables would show in the DM_INDEXES_ADD window. They tried everything, removing the indexes and adding back programmatically, rebuilding system indexes, recreating DBTs but nothing would work.

A Sprezz technician remoted into the system and performed some initial checks. This rapidly became an object lesson in the pursuit of red herrings. An LH3 log was generated and briefly checked for errors - but none were found. An engine profile log was generated and briefly checked for errors - but none were found. The files were detached and reattached but still they wouldn't show up.

The files were attached from Sysprog and the indexes were there for all to see. In addition a strange anomaly was found. At one of the locations being attached there was a table called "0" - yes, that's ZERO.

The technician was delighted - this must be the cause. Obviously this table showed up first in any list and the system must be using boolean checks to assemble a list of tables to display, you know the sort of thing :-



instead of the safer :-



So the file was renamed (well the media map entries were copied around and deleted) and the location was reattached and the system indexes rebuilt. But still the issue persisted.

A new application was created and the files were attached and again indexes were present. So now the speculation was that a promoted event was interfering with the (now OI based) database manager window. A more thorough analysis of the engine profile log revealed that the only promoted event in use during the execution of the DM_INDEXES_ADD routine was a CLOSE event. So back to the drawing board again.

Next up the LH3 log file was examined in greater detail and the individual index files could be seen being opened and the *INDEX records being read. So there wasn't an issue with the files themselves - they were plainly there.

Finally the technician stopped and reasoning that the problem had to be associated with the move to the new server, he undertook a more detailed analysis of the processes associated with the DM_INDEXES_ADD dialog. With this new focus the technician observed that the DM_INDEXES_ADD called V119 and a light went off in his brain. Moving back to the Database Manager he called up the environment settings and noted that the sort path referenced there had not been created on the new server. Create the directory, log out and back in and all was "sorted".

Sometimes the easiest solution is just that - the easiest solution.

Labels: index, sort file, v119