Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Can you change the database schema? In that case you have the option of introducing a timestamp column, fill it from the two existing columns, create an index and feel pleased. –  dezso Nov 20 '12 at 21:02
1  
The easier would be WHERE (@START_ROSDAT,@START_ROSTIM) <= (ROSDAT,ROSTIM) AND (ROSDAT,ROSTIM) <= (@STOP_ROSDAT,@STOP_ROSTIM) but I'm not sure if DB2 supports this syntax. –  ypercube Nov 20 '12 at 21:02
    
And then a (ROSDAT,ROSTIM) index suggestion is rather obvious. –  ypercube Nov 20 '12 at 21:04
    
@Sam - what dezso is suggesting is what is the general solution for being able to support an index in DB2 versions prior to version 10.1. Version 10.1 supports indexes over expressions. –  Chris Aldrich Nov 20 '12 at 21:59
    
@dezso, That was my first hope as well, but unfortunately, no changes to the database schema are possible. This is a table that is used by a bunch of other code and any changes would have to result in massive revisions to other software. I would like to smack whoever thought up this schema in the first place though. –  Sam Nov 21 '12 at 21:32

2 Answers 2

up vote 5 down vote accepted

I'm not sure of db2 syntax, but how about this?

-- declare your 4 variables
@START_ROSDAT
@STOP_ROSDAT
@START_ROSTIM
@STOP_ROSTIM

select  *
from    PHONELOGTABLE
where   (   @start_rosdat <> @stop_rosdat                                    -- search covers more than 1 day
           and  (       (ROSDAT > @START_ROSDAT AND ROSDAT < @STOP_ROSDAT)   -- catches all full days between start and stop
                   or   (ROSDAT = @START_ROSDAT AND ROSTIM >= @START_ROSTIM) -- catches everything on the "start" day
                   or   (ROSDAT = @STOP_ROSDAT AND ROSTIM <= @STOP_ROSTIM)   -- catches everything on the "stop" day
                )
        )
  or    (   @start_rosdat = @stop_rosdat                                     -- only search a single "day"
           and  (       (ROSDAT = @START_ROSDAT AND ROSTIM >= @START_ROSTIM)
                   and  (ROSDAT = @STOP_ROSDAT AND ROSTIM <= @STOP_ROSTIM)
                )
        )

WHERE condition simplified a bit (the parentheses are there only for clarity, you can safely remove them):

where   ( ROSDAT > @start_rosdat   and  ROSDAT < @stop_rosdat )

   or   (  @start_rosdat < @stop_rosdat 
      and  ROSDAT = @start_rosdat  and  ROSTIM >= @start_rostim )

   or   (  @start_rosdat < @stop_rosdat 
      and  ROSDAT = @stop_rosdat   and  ROSTIM <= @stop_rostim  )

   or   (  @start_rosdat = @stop_rosdat 
      and  ROSDAT = @start_rosdat  and  ROSTIM >= @start_rostim
                                   and  ROSTIM <= @stop_rostim  )

and another version (parentheses are needed here):

where   (  ROSDAT > @start_rosdat  
       or  ROSDAT = @start_rosdat  and  ROSTIM >= @start_rostim )

   and  (  ROSDAT < @stop_rosdat
       or  ROSDAT = @stop_rosdat   and  ROSTIM <= @stop_rostim  )
share|improve this answer
    
I don't believe you can use @ in a select statement unless you are creating a stored procedure. –  Chris Aldrich Nov 20 '12 at 21:56
    
Ah, I think you're on to something... I tried just the first part of that query (the insides of the "@start_rosdat <> @stop_rosdat" block), and it looks like it works. And on thinking about it, it looks like it should still work with just that first part even if the start and end are the same day... I'm generating the SQL programatically with bind params so it'll be no problem putting in the START_ROSDAT/END_ROSDAT etc equivalents where they need to go. Thanks! –  Sam Nov 20 '12 at 22:17
    
@ChrisAldrich, I'm not familiar with the db2 syntax. Those are supposed to be variables. –  Eli Nov 20 '12 at 22:26
1  
@Sam, I'm pretty sure you need both cases. I only had the second case and somebody pointed out that it will return all rows for a day if the start and stop days are the same. –  Eli Nov 20 '12 at 22:28
1  
@Sam, in the 1st code bock, if the start/stop days are the same, the 2nd condition and the 3rd condition combined will return ALL rows for the day. Say the start time was 3p (150000) and the stop time was 5p (170000), the 2nd condition would return everything from 3p to 11:59:59p (150000 thru 235959) and the 3rd condition would return everything from midnight (000000 to 170000). Since the 2nd and 3rd conditions are OR'd, you get everything. That's the basic difference between the two code blocks. The 1st one is OR'd and the 2nd one is AND'd. –  Eli Nov 21 '12 at 0:59

I see you already have an answer, and I see that you can't change the table definition because of other programs that use the table, but can you add in new columns to the table? Are you also on DB2 for Linux/Unix/Windows (tested on 9.7)?

If you can, and you are, then I might suggest you create a GENERATED COLUMN to create a TIMESTAMP column, which you could then index for searching.

SET INTEGRITY FOR PHONELOGTABLE OFF;

ALTER TABLE PHONELOGTABLE ADD COLUMN
    tstamp TIMESTAMP GENERATED ALWAYS AS (
        TIMESTAMP(
            LPAD(dt, 8, '0') ||
            LPAD(tm, 6, '0')
        )
    );

SET INTEGRITY FOR PHONELOGTABLE IMMEDIATE CHECKED FORCE GENERATED;

CREATE INDEX PHONELOGTABLE_TSTAMP ON PHONELOGTABLE  (
    TSTAMP
);

The LPAD function will ensure that you'll always have at least 14 characters (it implicitly casts the first argument to VARCHAR, and then pads on the left with the last argument to at least the number of characters specified in the second arg), which the TIMESTAMP function can then cast to a TIMESTAMP.

This won't work if you're on the Mainframe DB2 (the only type of generated columns DB2 for z/OS supports is identity columns, row change timestamps, or rowids). I'm not sure about DB2 for iSeries.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.