Logging Redux

Sometimes you come across a situation in the wild that seemingly defies all logic. This week was one of those.

A European client (you can tell this is being written by a Brit as we tend not to see ourselves as European by default - even though we patently are) running a new hybrid AREV32/OI system reported a most unusual scenario.

It manifested itself during a batch process - rows would be altered in AREV32, then the table would be selected on an indexed column and the resulting row set would omit the altered rows. If the rows were selected in OI then the altered rows would reappear as if by magic.Obviously, the client had forgotten to set the "Update before select flag" in the environment. Except they hadn't - it was there.

So we span it on its head and created new indexes in AREV32, made alterations and retried selections. Now they worked in AREV32 but the same process failed in OI. To sum up, indexes would only be updated in the environment in which they were created. Something was definitely amiss.

The first thing we did was to carefully observe what happened when the rows were created in AREV32 - index transaction rows appeared in row 0 in the ! file. Then the select was run and the row 0 was blanked down. So the indexes had been updated right? Not so fast. Closer examination revealed that whilst the 0 row was clear then INDEXNAME* row was not. So the rows had been moved to the next stage by F.DISTRIBUTOR but F.INDEXER had not done its work.

This just made no sense at all. So we did what we normally do under these circumstances and invoked the help of LH4.LOG. We installed the log, started OpenInsight and launched AREV32 - only to find that all accesses to linear hash files from OI were recorded but none from AREV32. Well of course - AREV32 spawns a new engine and the LH log only records the transactions for the first engine that accesses the subdirectory. So we had to move back to the LHSRVC log, installed in the UD subdirectory and tracking ALL access to linear hash.

In conjunction, we launched OI, created an OEProfile log and recorded what happened for both a successful and an unsuccessful index update.

Now with four logs (a profile and an LH for successful and unsuccessful) we could begin to piece together what was happening. We found the following:-

successful update, F.INDEXER called OPEN.INDEX, performed an LH operation reading the index definition row from the ! table then went off and did the index update.

unsuccessful update, F.INDEXER called OPEN.INDEX, performed an LH operation reading the index definition row from the ! table, called SET_STATUS and returned. So something after the read of the index column definition triggered the failure.

I'd like to say that armed with this we went straight to the solution but it took several iterations before all became clear. The AREV32 was set up to use a foreign language set - call it ESPERANTO. When indexes were created in AREV32, column 8 of the index definition row was set to ESPERANTO. OI was not set up to use a foreign language set, so in indexes created in OI field 8 was blank.

When F.INDEXER starts to move transactions from the transaction row into the BTREE it must first check that the language set of the updating process matches that of the index. The reason for this is obvious - language sets define collation sequences (the order in which things are sorted) so to ensure correct sorting the same collation sequence must always be used. If the languages don't match then no update can take place.

Now it is likely a fair assumption that if you're using language sets you'd be aware of why they're being used and be sensitive to this. But the reality is that these would have been put in place 2 decades ago or more and this sort of knowledge is esoteric. So when the AREV32 conversion was undertaken, OI was used out of the box without setting the language set in OI. So when the option was taken to recreate the tables and indexes they would be recreated using no language set. However, AREV32 kept the language set in the environment thereby introducing a mismatch preventing indexes from being updated.

There are multiple resolutions to this issue depending on client requirements. The simplest is to remove all language set references from AREV32. Alternatively you could add language set references to OI. Unless collation sequences is a deal breaker we'd be tempted to go with removing the language set references from AREV32 and ensuring that any indexes added within AREV32 are removed and readded.


This is something to be aware of if undertaking foreign language AREV32 conversions