Hi,

That doesn't work either. I have tried three different methods but could
only include one in my email because of the email-list character limit.

I have tried the sub-select, the temp table, and including the
AI.PROCESS_CONTROL in the FROM clause and in the predicate.

Douglas Kostelnik
Senior Database Administrator/Architect
-----Original Message-----
From: allen_glinter
[mailto:db2udbdba-ezmlmshield-x50395140.[Email address protected]
Sent: Thursday, November 29, 2007 11:53 AM
To: LazyDBA Discussion
Subject: Re: Processing Date Workaround.

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




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