Data Selection re-queries after initial select?!

Hello list,

Here is my scenario, I would appreciate any help:

I have a custom UBE, my primary section uses Set Selection Append Flag <N0> and selects on a business view join, where Shipped Date is in the prior month and Date Updated is in the current month. This report is run say on the 3rd of March - at 2:35 pm. Assuming that like all business views on sections, this report selects 100 records matching this criteria - it should process these 100 records, and produce the PDF once finished. However, this month (no mods were made since last month when it worked perfectly), instead of taking those 100 records, it also took records that had a Date Updated AFTER the run date and time of the report. The Date Updated field being used is an audit field, these records were inserted AFTER the initial launch of the report (which took days to finish, obviously because it kept grabbing any new records being added!!!). I actually save these records to a workfile - I don't update them ever so what's in my workfile is what was initially read from the data selection, and I can see Date Updated values of March 4th and March 5th. This goes against the very basic functionality of data selection - the selected records in the Do Section reflect only the data fitting the criteria at the time of the initialize section's select statement. I'm at a complete loss, I know I could add a statement to keep the report from selecting records with a Date Updated and Time Updated greater than the report run date and time but why should I ever have to when it is absolutely impossible that these records even existed at the time the report did it's initial selection? (Proof being that I save the records with the Date Updated found without ever updating it again and I'm seeing later dates than my report run date). This report took 56 hours to finally finish...

Thanks for any help!

Jennifer.
Heavily modified OW Xe SP19
AS400 (Business Data)
Oracle/Enterprise (Control Tables/Objects)
Citrix/Fats

Most RDBs don't actually cache an entire SELECT for a huge record set at the time of SELECTion - in fact they cache some block of records at a time before getting the next set. The strategy the RDB uses is dependant on a lot of factors. For instance, most MS SQL servers in SS2k cache 100 record blocks in their "pre fetch" stage - this behavior was causing me some consternation a few months back, and MS said, "Change your code, that's just how it is." I'm sure other RDBs have similar issues.

Really, do you expect it to make a cache of the entire table for every active cursor? It is possible that the behavior you desire would happen if you set up the report to run inside a transaction. I haven't played with that, but transaction processing has a different guarantee of what you see in that the DB has to be able to be rolled back, so it might be forced to cache the behavior of the SELECT for all of the items in the table.

Just my few cents. And you know how much a few cents are worth...

Seg,

At least on a 400 - when a query or select is generated, an access path is
generated. The database is not cached - rather, the path to the individual
records is stored.

Thus, the first select can be costly. Sequential selects (using the same
access path) are less costly. The costs can be further diminished by
verifying that there are proper indexes...

YMMV

Daniel Bohner
ExistingLight, LLC
www.existinglight.net
[email protected]
208.250.1917





Looking for work out west
Daniel Bohner | JDE Developer | Freelance-JDE/Web/Photo
www.ExistingLight.Net | [email protected]
JDE XE | SP20 | AS/400 | V5R1