Hi,

I am a MS SQL administrator that is required to connect to a remote DB2 database to pull information via a linked server. I am not sure of the version of DB2 (I think 7?) and have no idea what it is running on.

I run multiple queries and when I search on a date or time field I get back sub-second responses. However if I search on a Timestamp field it is taking me 5 to 13 minutes to recieve a response. I am at a severe disadvantage to the fact that I can find out almost nothing about the system I have to pull from.

The actual statement that I am running against db2 looks like this.

select a.claim_nbr, case_nbr from mcdb.case a, mcdb.contact b
where (b.update_tstamp > TIMESTAMP(current date , (current time - 5000.0 seconds)))
and (a.case_nbr = b.case_nbr) and (a.case_nbr is not null)

I have narrowed the problem down to the timestamp field. Is there a performance consideration, or a better way of performing this that I am unaware of?

Thanks!
Radar

In your query , have lot of column manipulation , avoid column manipulation in where clause, as much as posible, that make much difference.

Hope it may help.

Lekharaju Ennam

I guess that is part of my issue, trying to figure out the best way to pull the data. I am not aware if there is a better way to manipulate it.

What I need to pull are all records that have been updated within the past 5000 seconds. Is there a better way to look at this in db2?

Thanks, Again

Do you have access to the db2 command processor? If yes, please run "DESCRIBE TABLE mcdb.contact" and "DESCRIBE INDEXES FOR TABLE mcdb.contact SHOW DETAIL" and post the output here.

I would try"

current timestamp - 5000 seconds

You will need an index on contact.update_tstamp