Re: select on events table question

Yes, what you describe is documented in IC34609, which is related to
IC28825.

The workaround is to include a hard-coded date as the first filtering
criteria in your WHERE clause. Picking a hard-coded date will force the
EVENTS table to include all events from that date, after which additional
filtering criteria can be applied.

For example:

   select node_name, status, scheduled_start \
   from events \
   where scheduled_start>='2005-01-01' and \
         scheduled_start>=current_timestamp - 7 days and \
         (status='Failed' or status='Missed')

The first WHERE criterion forces the EVENTS table to include all events
following 1 Jan, 2005 to be included for consideration, thus satisfying
the use of a hard-coded date as described in IC28825. The other WHERE
criteria provide the actual filtering we want, which is to look at events
from up to a week ago that are either failed or missed.

Using your specific example:

   select count(*) \
   from events \
   where scheduled_start between \
      timestamp(current date - 30 day,'18:00:00') and \
      timestamp(current date,'18:00:00')

Change it to something like this:

   select count(*) \
   from events \
   where scheduled_start>'2005-01-01' and \
      scheduled_start between \
      timestamp(current date - 30 day,'18:00:00') and \
      timestamp(current date,'18:00:00')

The hard-coded date you use doesn't matter as long as it is as old (or
older) than the oldest records you want to view.

Regards,

Andy

Andy Raibeck
IBM Software Group
Tivoli Storage Manager Client Development
Internal Notes e-mail: Andrew Raibeck/Tucson/IBM@IBMUS
Internet e-mail: storman AT us.ibm DOT com

IBM Tivoli Storage Manager support web page:
http://www-306.ibm.com/software/sysmgmt/products/support/IBMTivoliStorageManager.html

The only dumb question is the one that goes unasked.
The command line is your friend.
"Good enough" is the enemy of excellence.

"ADSM: Dist Stor Manager" <ADSM-L AT VM.MARIST DOT EDU> wrote on 2005-10-14
14:59:52:

> I know that per APAR IC28825 if you try running select * from events
> you will only get the events for today.
>
>
>
> That APAR lists a work around where running select count(*) from
> events where scheduled_start between '2005-10-01 18:00:00' and
> '2005-10-15 06:00:00' will actually give you the events for a date
> range. With this I have to update the range each time I run it.
>
>
>
> I tried to use the timestamp with between as follows select count(*)
> from events where scheduled_start between timestamp(current date - 30
>
> day,'18:00:00') and timestamp(current date,'18:00:00'). But when I
> use timestamp it reverts back to the problem where it only shows you
> todays event data.
>
>
>
> Just wondering if Andy or any other SQL guru's out there might now
> if I am using timestamp incorrectly with between.
>
> Kyle
>
>
>
> ---------------------------------
>  Yahoo! Music Unlimited - Access over 1 million songs. Try it free.