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:

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?

  • 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. Nov 20, 2012 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. Nov 20, 2012 at 21:02
  • And then a (ROSDAT,ROSTIM) index suggestion is rather obvious. Nov 20, 2012 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. Nov 20, 2012 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, 2012 at 21:32

2 Answers 2


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

-- declare your 4 variables

select  *
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  )
  • I don't believe you can use @ in a select statement unless you are creating a stored procedure. Nov 20, 2012 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, 2012 at 22:17
  • @ChrisAldrich, I'm not familiar with the db2 syntax. Those are supposed to be variables.
    – Eli
    Nov 20, 2012 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, 2012 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, 2012 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.


            LPAD(dt, 8, '0') ||
            LPAD(tm, 6, '0')



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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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