Unidata Indices and Triggers

[Jeff Kendrick]

I've got an issue that has me stumped. Any help or insights would be greatly
appreciated.

I have created a file specifically for reporting purposes. This reporting file
is maintained EXCLUSIVELY via a Unidata TRIGGER subroutine on a primary
application file. The reporting file is also indexed on a couple of fields.

I've written the trigger subroutine to perform the update to the reporting
file, and used CREATE.TRIGGER to establish it as an update trigger for the
primary file. The trigger subroutine is properly handling adds and updates to
the primary file. (Deletes will be next, but I haven't gotten there yet --
nor will I if I can't understand what's going on.)

What's happening is that while the reporting file is being properly updated,
the indices are not being updated AT ALL. This is confirmed by looking at the
Unix level -- the timestamp on the file is far behind the timestamp on the
actual data file. This is happening even though LIST.INDEX shows that indices
are enabled.

Now, if I use AE to update the reporting file DIRECTLY, then the indices are
updated for the one record that is modified. This proves that the indices are
created and enabled. -- but no updates via my trigger subroutine.

Finally, if I run a BUILD.INDEX on the reporting file, all of the indices are
built and all is well, but that solution is not acceptable. The whole point is
to avoid any and all maintenance on the reporting file, while maintaining its
synchronization with the primary file. ("Set it, and forget it," to borrow
a phrase.)

Has anyone had a similar experience with Unidata indices and triggers? If so,
can you help me to understand what is wrong with my setup?

Again, any help would be greatly appreciated.

Jeff

[Don Verhagen]

In news:***@posting.google.com,
Jeff Kendrick <***@healthaxis.com> typed:
: I've got an issue that has me stumped. Any help or insights would be
: greatly appreciated.
:
: I have created a file specifically for reporting purposes. This
: reporting file is maintained EXCLUSIVELY via a Unidata TRIGGER
: subroutine on a primary application file. The reporting file is also
: indexed on a couple of fields.
:
: I've written the trigger subroutine to perform the update to the
: reporting file, and used CREATE.TRIGGER to establish it as an update
: trigger for the primary file. The trigger subroutine is properly
: handling adds and updates to the primary file. (Deletes will be next,
: but I haven't gotten there yet -- nor will I if I can't understand
: what's going on.)
:
: What's happening is that while the reporting file is being properly
: updated, the indices are not being updated AT ALL. This is confirmed
: by looking at the Unix level -- the timestamp on the file is far
: behind the timestamp on the actual data file. This is happening even
: though LIST.INDEX shows that indices are enabled.
:
: Now, if I use AE to update the reporting file DIRECTLY, then the
: indices are updated for the one record that is modified. This proves
: that the indices are created and enabled. -- but no updates via my
: trigger subroutine.
:
: Finally, if I run a BUILD.INDEX on the reporting file, all of the
: indices are built and all is well, but that solution is not
: acceptable. The whole point is to avoid any and all maintenance on
: the reporting file, while maintaining its synchronization with the
: primary file. ("Set it, and forget it," to borrow
: a phrase.)
:
: Has anyone had a similar experience with Unidata indices and
: triggers? If so, can you help me to understand what is wrong with my
: setup?
:

The subroutine called by the trigger to update the file is just using a
normal WRITE statement? I can't see why this wouldn't update the index. What
platform/version of Unidata are you using?

UD5.x on HPUX 11.11i

Donald Verhagen
Tandem Staffing Solutions, Inc.

[Jeff Kendrick]

Post by Don Verhagen
The subroutine called by the trigger to update the file is just using a
normal WRITE statement? I can't see why this wouldn't update the index. What
platform/version of Unidata are you using?
UD5.x on HPUX 11.11i
Donald Verhagen
Tandem Staffing Solutions, Inc.

We're on UD5.2 on AIX 5.1:

:VERSION

Module Name Version Licensed

UniData RDBMS............ 5.2 Yes
UniData OFS/NFA.......... 5.2 Yes
UniServer for ObjectCall. 5.2 Yes
RFS/TP................... 5.2 Yes
Device License........... 5.2 Yes
ODBC/UniOLEDB............ 5.2 Yes
UniObjects............... 5.2 Yes
5227
:!oslevel
5.1.0.0
:

[Jeff Kendrick]

Post by Don Verhagen
The subroutine called by the trigger to update the file is just using a
normal WRITE statement? I can't see why this wouldn't update the index. What
platform/version of Unidata are you using?
UD5.x on HPUX 11.11i
Donald Verhagen
Tandem Staffing Solutions, Inc.

Forgot to add in previous post...

Yes, it's just a normal sequence of READU/update/WRITE statments in
the trigger subroutine.

[Colin Alfke]

If you're doing the readu/update/write in the trigger to the same data file
then you're simply overwriting your changes.

It sounds like your making your changes to the record in the "regular"
program which when it goes to write it out calls the trigger. Your trigger
then reads the record again (losing the changes you've made) you make the
"trigger" changes to that record and the trigger writes it out. Then the
original write occurs overwriting your "trigger" changes with your "regular"
changes.

Wait a minute... That might cause a recursive loop....But it may be smart
enough not to.

If this is your problem then you need to forget about
reading/updating/writing the record in the trigger (unless you write it to a
different file) and just update the record variable that is passed into the
trigger. Once you update that (and make sure the other flags are set right)
then it will incorporate your changes into the record that it then writes
out.

hth
Colin

Post by Jeff Kendrick

Post by Don Verhagen
The subroutine called by the trigger to update the file is just using a
normal WRITE statement? I can't see why this wouldn't update the index. What
platform/version of Unidata are you using?
UD5.x on HPUX 11.11i
Donald Verhagen
Tandem Staffing Solutions, Inc.

Forgot to add in previous post...
Yes, it's just a normal sequence of READU/update/WRITE statments in
the trigger subroutine.

[Don Verhagen]

Post by Colin Alfke
If you're doing the readu/update/write in the trigger to the same data file
then you're simply overwriting your changes.
It sounds like your making your changes to the record in the "regular"
program which when it goes to write it out calls the trigger. Your trigger
then reads the record again (losing the changes you've made) you make the
"trigger" changes to that record and the trigger writes it out. Then the
original write occurs overwriting your "trigger" changes with your "regular"
changes.

Colin,

He's is updating a secondary report file with the trigger not the file that
has the trigger on. He is stating that the index on the secondary report
file isn't being updated, but if he edits the record and saves changes the
index *is* updated. Very strange behaviour indeed.

Don

[Colin Alfke]

[snip]

Post by Don Verhagen
Colin,
He's is updating a secondary report file with the trigger not the file that
has the trigger on. He is stating that the index on the secondary report
file isn't being updated, but if he edits the record and saves changes the
index *is* updated. Very strange behaviour indeed.
Don

Oops. you're right. It was late and the end of a long day...

We have a couple of sites with triggers on data files updating reporting
files (both windows and Aix) and they have been working well. The only time
I saw an index not being updated on the reporting file was when it was
turned off. The index was enabled but the specific one we were trying to use
was not built.

About the only difference between his setup and ours that I can see is that
our triggers are only responsible for the reporting files. They don't effect
the data files at all.

Colin

[Jeff Kendrick]

Post by Colin Alfke
About the only difference between his setup and ours that I can see is that
our triggers are only responsible for the reporting files. They don't effect
the data files at all.
Colin

It sounds like your setup is exactly like mine. My triggers only update
the reporting files, but they are attached to the primary application files
because the reporting file needs to reflect changes to those primary files.

Basically, the purpose of the triggers is to watch for changes to the
application data. When something of interest changes, the summary data
in the reporting file needs to be updated. The triggers never change
the data being written to the main files; they only maintain the
reporting files.

So, from this thread, it sounds like what I'm trying to do is being
done successfully elsewhere. I guess I'll head back into the details
and see if I can figure out what's wrong with my situation.

If I get it figured out, I'll let you all know what it was... Thanks
for the help, guys.

Jeff

[Jeff Kendrick]

Post by Jeff Kendrick
So, from this thread, it sounds like what I'm trying to do is being
done successfully elsewhere. I guess I'll head back into the details
and see if I can figure out what's wrong with my situation.
If I get it figured out, I'll let you all know what it was... Thanks
for the help, guys.
Jeff

I have figured out my issue. It's something I learned several years
ago, but for some reason escaped me during this process...

In Unidata, the indices established on a file are only checked when a
file is OPENED, and not every time a WRITE is performed. For
efficiency's sake, I coded my trigger routines to store the opened
file variable in a named COMMON area. It turns out that somehow,
during my testing, I had run the test program, which invoked the
trigger routines, which opened the files, and cached the file
variables in named COMMON when the indices were not yet created. Then,
without logging off, I created the indices, and then ran the test
program again to update the application files. This invoked the
trigger routines (again), which were still using the file variables
that were cached before the indices were established. Thus, they were
not aware of the indices on the reporting file, and did not update
them.

An AE update, on the other hand, knows nothing about my cached file
variables, and re-opens the reporting file, with indices established,
and updates them properly.

When I finally had my "a-ha" moment, and logged in to a new Unidata
session AFTER the indices on the reporting file were opened, and THEN
ran my test program, the triggers opened the reporting file (for the
first time in the new session) with full knowledge of the indices, and
everything worked as expected.

This is a case of re-learning an old lesson the hard way... I guess
that sometimes just knowing that something can be done can get you to
re-examine what's really happening.

Thanks for the whack on the head.

Jeff.

[Albino Timberwolf]

Another nice trick is to pass the data (however you choose) and use a
PHANTOM to do the updates. This solves the problem of a complex trigger not
returning quickly and allowing the user to move on. Since the phantom is a
background process it can run once and terminate but take as much time as it
needs to complete complex algorithms. Yes, the como file can fill up quite
quickly this way. The phantom process could include a select to clean up
anything older than today's date.

Also, if you're not the only developer, you could put an update trigger on
the CTLGTB to create backups automatically. That way you can back out
changes incrementally until the latest "fix" is gone and you can correct the
problem the right way. Put a delete trigger on it too to archive old code.

Post by Jeff Kendrick

Post by Jeff Kendrick
So, from this thread, it sounds like what I'm trying to do is being
done successfully elsewhere. I guess I'll head back into the details
and see if I can figure out what's wrong with my situation.
If I get it figured out, I'll let you all know what it was... Thanks
for the help, guys.
Jeff

I have figured out my issue. It's something I learned several years
ago, but for some reason escaped me during this process...
In Unidata, the indices established on a file are only checked when a
file is OPENED, and not every time a WRITE is performed. For
efficiency's sake, I coded my trigger routines to store the opened
file variable in a named COMMON area. It turns out that somehow,
during my testing, I had run the test program, which invoked the
trigger routines, which opened the files, and cached the file
variables in named COMMON when the indices were not yet created. Then,
without logging off, I created the indices, and then ran the test
program again to update the application files. This invoked the
trigger routines (again), which were still using the file variables
that were cached before the indices were established. Thus, they were
not aware of the indices on the reporting file, and did not update
them.
An AE update, on the other hand, knows nothing about my cached file
variables, and re-opens the reporting file, with indices established,
and updates them properly.
When I finally had my "a-ha" moment, and logged in to a new Unidata
session AFTER the indices on the reporting file were opened, and THEN
ran my test program, the triggers opened the reporting file (for the
first time in the new session) with full knowledge of the indices, and
everything worked as expected.
This is a case of re-learning an old lesson the hard way... I guess
that sometimes just knowing that something can be done can get you to
re-examine what's really happening.
Thanks for the whack on the head.
Jeff.