I ran the query @ 10 am and received 386 records... I ran the same query (no changes) @ 10:23 and received 8,691 records...
What could cause this phenomenon??? Here is the query If the table/view is being loaded by the system and I try to use it, my program should wait correct? why would it return two different results
Select VORD.ORDER_NO, VORD.BTN_NPA || VORD.BTN_NXX || VORD.BTN_Line_NO AS BTN , VORD.Sales_Rep_ID, VORD.MOD_DT_TM, VORD.TYPE_CD, VSOSIREF.SRC_STS_IND_E_DESC From VORD , VBCHPROD, VORDMTR, VORDITM, VORDITMD , VSOSIREF Where VORD.EVENT_ID = VORDITM.EVENT_ID AND VORDITM.ITEM_ID = VORDITMD.ITEM_ID AND VORDMTR.EVENT_ID = VORD.EVENT_ID AND VORDITMD.PRODUCT_ID = VBCHPROD.PRODUCT_ID AND VORD.STATUS_IND = VSOSIREF.STATUS_IND AND VORD.SRC_CO_CD = VSOSIREF.SRC_CO_CD AND MOD_DT_TM > '2006/08/11 00:00:00' AND MOD_DT_TM < '2006/08/18 00:00:00' AND VORD.sRC_CO_CD = '06' AND VORD.so_region_cd in ('1','2') AND PRODUCT_E_DESC = 'BID' AND VORD.STATUS_IND IN ( '34', '29')
Your query will wait only if blocked by a "write" lock on one of the tables. Depending on the method being used to update the data (i.e. Tpump, Multiload, etc.), you may or may not be locked out of the entire table. Some utilities, such as Tpump, lock at the row level, so your read lock would be able to eventually get in to read the table (in fact, your read lock will probably be blocking the updates in this case).
Another possibility is if you are referencing views in your query, rather than tables, the views could have a "LOCKING ROW FOR ACCESS" or similar access-locking clause in the view. If this is the case, then your query will not wait on the update process, even if the update process has a write lock on the table. It will simply read the un-committed data as is.
A third possibility is that one of the views utilizes one of the OLAP functions and the code that references the OLAP function is not uniquely identifying each row, thereby allowing the function to randomly pick a different row or rows each time it executes. I have seen this happen many times when an OLAP function is being used to pick a row (the row with the maximum combination of fields, for instance).