UniData index is not updating when records change or are added to the file being indexed.

I am indexing a 21,000,000 record file on the invoice date.  It is a single value field, and when I build the index it works correctly based on the records in the file at the time of the build.  After that, new records or changed records are NOT added to the index.

Is there a setting related to indexing that must be set to auto update the index?

I have read all the UniData documentation on indexing that I can find.

I have been using indexes in other versions of Pick for decades and this is the first time I have hit this problem.

------------------------------
Bob Lisle
President
Information Technology Systems
Dallas TX US
------------------------------

Bob,

Indexes are automatocally updated and downdated as the file on which they are built is updated and downdated.

I recommend by starting to examine the file and its indexes for:

• Permissions - if UNIX then make sure the file and the indexes have compatible permissions for the users concerned.
• If a dynamic file, ensure that the directory holding the dynamic file has the group ownership bit set and is owned by the group that represents all UniData users (chmod g+x) - also chown, chmod  and chgrp if needed,
• Check ulimit and also whether a 32-bit or 64-bit file. Remember that 32-bit files have a 2GB size limitation.
• Run LIST.INDEX ... ALL - does the index show what you would expect?
• On the off chance - were there  ANY processes that had the file open running while the index creation and build was run? These processes could have the file handle as it was before the creation and build and ignore the index. This can also result in the index being demaged. Exclusive access is needed for index creation and building - an this includes ALL processes, even current file handles including COMMON if EXECUTE-ing the index creation and build from a  program.
  • On UNIX you can use fuser -u <unix_file> on each of the UNIX files - data and index - concerned. You should see NO processes having the file(s) open.
  • On Windows you can use handle - one of the Microsoft sysinternals tools similarly. The suite of programs including handle can be obtained from Microsoft here:  https://learn.microsoft.com/en-us/sysinternals/
  • Use guide_ndx to check the index - see https://my.rocketsoftware.com/RocketCommunity/s/article/How-to-recognize-Alternate-Index-corruption-and-how-to-fix-it-1478611835811

For preferences:

• ulimit file limit:  uniimited
• 64-bit dynamic file
• REALLY check any existing file parts are not in  use for the duration.

Assumptions I've made:

1. These are UniData native indexes, not SB/XA or SB+ indexes - which are actually application-based invert-lookup data files used by SB/XA and SB+.
2. If running on an older version, assuming that the release notices have been checked in case of any known and documented / resolved index-related issues - see https://rbc.rocketsoftware.com/matrix.asp and look at later versions of UniData for your platform.

Hoping this helps

Regards

JJ

------------------------------
John Jenkins
Thame, Oxfordshire
------------------------------

I am indexing a 21,000,000 record file on the invoice date.  It is a single value field, and when I build the index it works correctly based on the records in the file at the time of the build.  After that, new records or changed records are NOT added to the index.

Is there a setting related to indexing that must be set to auto update the index?

I have read all the UniData documentation on indexing that I can find.

I have been using indexes in other versions of Pick for decades and this is the first time I have hit this problem.

------------------------------
Bob Lisle
President
Information Technology Systems
Dallas TX US
------------------------------

Is the Invoice Date on the file an I-Descriptor or Virtual Attribute?  Indexes won't update when data changes in other files.

Your option would be to add an after trigger to update the index.

------------------------------
David Green
Computer Programmer
Rocket Forum Shared Account
------------------------------

Is the Invoice Date on the file an I-Descriptor or Virtual Attribute?  Indexes won't update when data changes in other files.

Your option would be to add an after trigger to update the index.

------------------------------
David Green
Computer Programmer
Rocket Forum Shared Account
------------------------------

David,

Good catch - Bob, please post the dictionary entry that is being indexed and any related dictionary or subroutine details if any.

Also check if the dsta is single-valued, multi-valued or multi-subvalued.

Regards

JJ

------------------------------
John Jenkins
Thame, Oxfordshire
------------------------------

David,

Good catch - Bob, please post the dictionary entry that is being indexed and any related dictionary or subroutine details if any.

Also check if the dsta is single-valued, multi-valued or multi-subvalued.

Regards

JJ

------------------------------
John Jenkins
Thame, Oxfordshire
------------------------------

David,

The problem is the dictionary is a  derived field.  There are a number of indexes in the application with the same problem, that were assumed to be working correctly, that are in fact not working.

I'm going to look into adding an after trigger to update the index. 

Thanks to all who responded.  It is most appreciated.

Bob

------------------------------
Bob Lisle
President
Information Technology Systems
Dallas TX US
------------------------------

David,

The problem is the dictionary is a  derived field.  There are a number of indexes in the application with the same problem, that were assumed to be working correctly, that are in fact not working.

I'm going to look into adding an after trigger to update the index. 

Thanks to all who responded.  It is most appreciated.

Bob

------------------------------
Bob Lisle
President
Information Technology Systems
Dallas TX US
------------------------------

Bob,

If this were UnIVerse, Indexing derived fields per se should not be a problem. What is guaranteed to cause a problem  is an index whose expression, either directly or indirectly, contains  a TRANS(), or ICONV() or OCONV() with a Tfile, or one that is temporally-dependent, e.g., an index whose expression includes DATE(), TIME(), or NOW().

That being said, there are several techniques that can be employed to avoid requiring a trigger-based index, for example, if you have a header file and a detail file, where the header has the date, you don't want to index the detail file on the date of the header file, rather, use the index of the header file from the detail file or vice versa when querying it.

If you provide more details about the dependencies and the problem you are trying to solve, perhaps I or others could point you in a direction that would minimize your effort in solving the problem.

Henry

------------------------------
Henry Unger
President
Hitech Systems Inc
Encino CA US
------------------------------

Bob,

If this were UnIVerse, Indexing derived fields per se should not be a problem. What is guaranteed to cause a problem  is an index whose expression, either directly or indirectly, contains  a TRANS(), or ICONV() or OCONV() with a Tfile, or one that is temporally-dependent, e.g., an index whose expression includes DATE(), TIME(), or NOW().

That being said, there are several techniques that can be employed to avoid requiring a trigger-based index, for example, if you have a header file and a detail file, where the header has the date, you don't want to index the detail file on the date of the header file, rather, use the index of the header file from the detail file or vice versa when querying it.

If you provide more details about the dependencies and the problem you are trying to solve, perhaps I or others could point you in a direction that would minimize your effort in solving the problem.

Henry

------------------------------
Henry Unger
President
Hitech Systems Inc
Encino CA US
------------------------------

Henry,

The files are ORDER and ORDER.LINE, (don’t get me started on that…) did not include the Invoice Date in the detail lines.
However we need to select specific order lines using the Invoice Date.
The application is built in System Builder, which adds another layer of complexity to the problem.

Now that I know what the problem is, I have a couple of ideas how to work around the problem. I have not had the opportunity to try them but hopefully will in the coming few days.

I appreciate your interest in helping me resolve the problem.

Thanks again.

Bob

Bob Lisle
Information Technology Systems Inc.
bob@itspick.com
Mobile: (214) 957-7561

David,

Good catch - Bob, please post the dictionary entry that is being indexed and any related dictionary or subroutine details if any.

Also check if the dsta is single-valued, multi-valued or multi-subvalued.

Regards

JJ

------------------------------
John Jenkins
Thame, Oxfordshire
------------------------------

The trigger approach will not work in UniVerse, and I assume the technology is similar in UniData.

Determining the need for an index file change requires comparing the value with the before-record image and the after-record image. An index based upon a TRANS (or TFile) becomes out of date if the target value of the TRANS changes. Reading the record and simply rewriting it would calculate the TRANS with the before-record and use the changed TRANS value. The process then uses the after-record image and the TRANS again uses the changed TRANS value. The before and after values from the TRANS match, so no update occurs. The same issue occurs with DATE() in a virtual field. 

An interesting dilemma might happen with TIME() that might happen to change between the before and after record image evaluations. (It could also happen for DATE(), but it would only occur if the update crossed a midnight boundary.)  The original record writes at 8am. The trigger fires at 9am. The before-record image calculates 9am and the after-record image calculates 9:00:01am. The index update process detects a change, so it attempts to move the record from the 9am index value to the 9:00:01am index value. The problem is that the record ID is stored at 8am, not 9am, and is not found, and is not removed from the 8am index. It is added to the 9:00:01am index value. Now the record exists in the index twice: once at the 8am index value, and once at the 9:00:01am index value.

------------------------------
Mark A Baldridge
Principal Consultant
Thought Mirror
Nacogdoches, Texas United States
------------------------------

The trigger approach will not work in UniVerse, and I assume the technology is similar in UniData.

Determining the need for an index file change requires comparing the value with the before-record image and the after-record image. An index based upon a TRANS (or TFile) becomes out of date if the target value of the TRANS changes. Reading the record and simply rewriting it would calculate the TRANS with the before-record and use the changed TRANS value. The process then uses the after-record image and the TRANS again uses the changed TRANS value. The before and after values from the TRANS match, so no update occurs. The same issue occurs with DATE() in a virtual field. 

An interesting dilemma might happen with TIME() that might happen to change between the before and after record image evaluations. (It could also happen for DATE(), but it would only occur if the update crossed a midnight boundary.)  The original record writes at 8am. The trigger fires at 9am. The before-record image calculates 9am and the after-record image calculates 9:00:01am. The index update process detects a change, so it attempts to move the record from the 9am index value to the 9:00:01am index value. The problem is that the record ID is stored at 8am, not 9am, and is not found, and is not removed from the 8am index. It is added to the 9:00:01am index value. Now the record exists in the index twice: once at the 8am index value, and once at the 9:00:01am index value.

------------------------------
Mark A Baldridge
Principal Consultant
Thought Mirror
Nacogdoches, Texas United States
------------------------------

Mark,

Yesterday I was running tests and found that to force an update to the index in my specific circumstances, I could read the record in, delete the record, and write the record back out and the index updated, so no net change to the record.

Your description of the problem matched to what I had determined from my testing.

Thanks to you, and to everyone who responded to my question.

Cheers

Bob

Bob Lisle
Information Technology Systems Inc.
bob@itspick.com
Mobile: (214) 957-7561

I am indexing a 21,000,000 record file on the invoice date.  It is a single value field, and when I build the index it works correctly based on the records in the file at the time of the build.  After that, new records or changed records are NOT added to the index.

Is there a setting related to indexing that must be set to auto update the index?

I have read all the UniData documentation on indexing that I can find.

I have been using indexes in other versions of Pick for decades and this is the first time I have hit this problem.

------------------------------
Bob Lisle
President
Information Technology Systems
Dallas TX US
------------------------------

Bob,

This may be a basic question, but after you did the CREATE.INDEX did you do the ENABLE.INDEX?

Normally when I create an index on a file I run the ENABLE.INDEX before I run the BUILD.INDEX.

Sam

------------------------------
Samuel A Powell
President/Developer
Advanced Transportation Systems Inc
Colorado Springs CO US
------------------------------

Bob,

This may be a basic question, but after you did the CREATE.INDEX did you do the ENABLE.INDEX?

Normally when I create an index on a file I run the ENABLE.INDEX before I run the BUILD.INDEX.

Sam

------------------------------
Samuel A Powell
President/Developer
Advanced Transportation Systems Inc
Colorado Springs CO US
------------------------------

Sam,

I did not perform an ENABLE.INDEX command. At the tine of the BUILD.INDEX the index worked correctly.
This is a problem with an indexed field that is a translate to another file.

If I select records that need to be updated, read the record in, delete the record, then write the record the index is updated and selects work.

Thanks to everyone that has responded to my question.

Cheers

Bob

Mark,

Yesterday I was running tests and found that to force an update to the index in my specific circumstances, I could read the record in, delete the record, and write the record back out and the index updated, so no net change to the record.

Your description of the problem matched to what I had determined from my testing.

Thanks to you, and to everyone who responded to my question.

Cheers

Bob

Bob Lisle
Information Technology Systems Inc.
bob@itspick.com
Mobile: (214) 957-7561

If the index value is already incorrect due to a change in a TRANS() value or DATE() calculation, the index update process will try to remove the ID from the wrong index value - and fail. That will leave the ID in the original index value.

------------------------------
Mark A Baldridge
Principal Consultant
Thought Mirror
Nacogdoches, Texas United States
------------------------------

If the index value is already incorrect due to a change in a TRANS() value or DATE() calculation, the index update process will try to remove the ID from the wrong index value - and fail. That will leave the ID in the original index value.

------------------------------
Mark A Baldridge
Principal Consultant
Thought Mirror
Nacogdoches, Texas United States
------------------------------

Just a small note ... there is a warning in the Using UniData manual about this situation.

"Although UniData supports virtual attribute alternate key indexes, care should be taken when determining which virtual attributes are indexed. A virtual attribute that contains a TRANS statement to another file, or the same file, is not a recommended use of an alternate key index, unless the file being translated to does not change. If the file being translated to does change, the alternate key index is not updated. Therefore, to obtain accurate results from a UniQuery statement, the alternate key index would need to be rebuilt each time the associated file is updated."

UniData also provides a level 3 checking option for guide_ndx, which will check the logical integerity of an index and report any mismatches.

------------------------------
Jonathan Smith
UniData ATS
Rocket Support
------------------------------