I have a legacy IBM DB2 database table that contains lots of records of phone calls. It has columns for ID, customer number, employee who took the call, etc. The date/time for each call is kept in a pair of columns, ROSDAT and ROSTIM. ROSDAT is an 8 byte numeric column, and ROSTIM is a 6 byte numeric. Example:
ROSDAT ROSTIM 20111006 163243 20111007 012335
So for the first row the date is 2011-10-06, and the time is 16:32:43. There are millions of records in this table, and I'm trying to make a query that will search it by time and date. The only way I've found to do that so far is to concatenate the time/date columns into one big number like so:
select * from PHONELOGTABLE
where ROSDAT * power(10, 6) + ROSTIM >= 20111015124500
and ROSDAT * power(10, 6) + ROSTIM <= 20111116013000
This searches for everything from 2011-10-15 12:45:00 to 2012-11-16 01:30:00. It works, but the concatenation means (as far as I know) that the indexes on the ROSDAT and ROSTIM columns cannot be used, so the query is slower than it should be. It seems to me that there should be a way to do this with a join or something.
I tried this but couldn't get it to work, and I know I'm missing something:
select a.* from PHONELOGTABLE a join PHONELOGTABLE b on a.ID = b.ID
where a.ROSDAT >= 20111015 and b.ROSTIM >= 124500
and a.ROSDAT <= 20111116 and b.ROSTIM <= 013000
Anybody have an idea?