Have you tried a subselect rather than a join? I am assuming that the
table function only provides a single value.

with pulldate (last_processed) as
(select TS_LAST_PROCESSED from AI.PROCESS_CONTROL

where AI.PROCESS_CONTROL.PROCESS = 'NBOHist1')
SELECT
AI.NBO_HISTORY.SUMMARY_ID,
AI.NBO_HISTORY.UI,
AI.NBO_HISTORY.LOCATION_ID,
AI.NBO_HISTORY.SERVICE_AREA_ID,
AI.NBO_HISTORY.CONTACT_CHANNEL_ID,
AI.NBO_HISTORY.NBO_CODE,
AI.NBO_HISTORY.MODIFIED_TS
FROM
AI.NBO_HISTORY
WHERE
AI.NBO_HISTORY.MODIFIED_TS >
(select last_processed
from pulldate
)


Al Glinter ? Lead Database Administrator ? I.T. Solution Center
Lord & Taylor ? 500 North Broadway, Suite 1250 ? Saint Louis, MO 63102
phone (314) 552-3752 email [Email address protected]
www.lordandtaylor.com



"Doug Kostelnik "
<db2udbdba-ezmlmshield-x70871772.[Email address protected]
11/29/2007 10:24 AM

To
"LazyDBA Discussion" <[Email address protected]
cc

Subject
Processing Date Workaround.






We are using an ETL tool to archive and prune a number of tables in our
OLTP database (moving data to our OLAP database).



The scenario is that we have a "process control table" that contains a
list of these pruning processes and the "last processed timestamp". This
methodology will not change as it is deeply instantiated in our
operational environment. Indexing is in place further indexes will have
no impact.



Basically a query runs and joins to this "process control table" to
compare a timestamp on the table due to be pruned to the "lasted
processed timestamp". This means that the two tables are going to create
a lengthy and costly join.



Just as an experiment I replaced the predicate in where clause in one of
the queries with a "hard timestamp" like '2007-10-01-12.00.00.000000'
and got an immediate result set from the query. The query plan topped
out at 90 with this test. Using the current method the plan topped out
at 11,000+.



I don't think it can be done, but I am looking for a way to get the
query to work more like it is comparing to a hard timestamp rather than
doing a table join to compare a timestamp in a column.



The issue is this and other queries run every 5, 10, or 15 minutes and I
want to reduce the impact on the system. Here is an example a query that
result in a costlier plan:



with pulldate (last_processed) as
(select TS_LAST_PROCESSED from AI.PROCESS_CONTROL

where AI.PROCESS_CONTROL.PROCESS = 'NBOHist1')
SELECT
AI.NBO_HISTORY.SUMMARY_ID,
AI.NBO_HISTORY.UI,
AI.NBO_HISTORY.LOCATION_ID,
AI.NBO_HISTORY.SERVICE_AREA_ID,
AI.NBO_HISTORY.CONTACT_CHANNEL_ID,
AI.NBO_HISTORY.NBO_CODE,
AI.NBO_HISTORY.MODIFIED_TS
FROM
AI.NBO_HISTORY ,pulldate
WHERE
AI.NBO_HISTORY.MODIFIED_TS>pulldate.last_processed

WITH UR;



Douglas Kostelnik

Senior Database Administrator/Architect




------------------------------------------------------------------------
The information transmitted is intended only for the person(s) or entity
to which it is addressed
and may contain confidential and or privileged material and should be
treated as a confidential
AAA Auto Club South communication. If the reader of this message is not
the intended recipient, you
are hereby notified that your access is unauthorized, and any review,
dissemination,distribution, or
copying of this message including any attachments is strictly prohibited.
========================================================================


---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html



DB2 & UDB email list listserv db2-l LazyDBA home page