DB2 – How to Remove Specific Item from Query

I am not sure if I understand your problem. You say that the database hangs on a query, that means that all tables, views, etc, are blocked. However, you say that other tables are accessible, that means that the database is not hung, it is just an access to one table.

You used "db2 restart db", however it is for other things, for example when you need to restart the database once it has crashed, but I think your db has not crashed, just it is blocked.

I think your problem is a concurrency problem, the database has to be processing a big query on the table, putting an exclusive lock (or something similar) and the other queries cannot read the data with the current isolation level.

In order to see if this is the problem, try to get snapshots to see which table are accessed and check the lock list.

You can change the isolation level in the query, for example adding "WITH UR" at the end, if this isolation level is appropriated.

Other problems "could" be related to transaction logs (flush issue), busy or not enough agents, prefetchers issue, small bufferpool, expensive access plan. Well, you have to see the db2diag to have more clues about your problem.

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  )