Rocket U2 | UniVerse & UniData

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
------------------------------

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.

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.

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.

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

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.

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.

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

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

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.

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,

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

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

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

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.

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.

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

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.

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.

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.

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

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.

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.

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.

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
------------------------------

Original Message:
Sent: 07-27-2024 10:51
From: Bob Lisle
Subject: 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
------------------------------