Gary,

You might get closer to what you want by dropping the "date()" function
from both sides of the comparison operator, e.g.:

   backup_date>=(current_timestamp - 20 hours)

If you really want to target objects backed up by yesterday's scheduled
backup, it gets trickier (maybe someone else knows an easier way). In my
example, I actually concatenate into a single string the file space name,
HL name, and LL name:


select concat(filespace_name, concat(hl_name, ll_name)) as "FILE NAME", -
       backup_date -
   from backups -
   where -
      backup_date>=(select actual_start -
         from events -
         where scheduled_start>'2013-01-01' and -
               date(scheduled_start)=date(current_timestamp - 1 day) and -
               node_name='RAIBECK_PROD' and -
               status='Completed') -
         and -
      backup_date<=(select completed -
         from events -
         where scheduled_start>'2013-01-01' and -
               date(scheduled_start)=date(current_timestamp - 1 day) and -
               node_name='RAIBECK_PROD' and -
               status='Completed') -
         order by backup_date



Best regards,

- Andy

____________________________________________________________________________

Andrew Raibeck | Tivoli Storage Manager Level 3 Technical Lead |
storman AT us.ibm DOT com

IBM Tivoli Storage Manager links:
Product support:
http://www.ibm.com/support/entry/portal/Overview/Software/Tivoli/Tivoli_Storage_Manager

Online documentation:
https://www.ibm.com/developerworks/mydeveloperworks/wikis/home/wiki/Tivoli
+Documentation+Central/page/Tivoli+Storage+Manager
Product Wiki:
https://www.ibm.com/developerworks/mydeveloperworks/wikis/home/wiki/Tivoli
+Storage+Manager/page/Home

"ADSM: Dist Stor Manager" <ADSM-L AT vm.marist DOT edu> wrote on 2013-08-01
08:21:59:

> From: "Lee, Gary" <GLEE AT BSU DOT EDU>
> To: ADSM-L AT vm.marist DOT edu,
> Date: 2013-08-01 08:23
> Subject: Re: another sql query problem
> Sent by: "ADSM: Dist Stor Manager" <ADSM-L AT vm.marist DOT edu>
>
> Mr. Raibeck:
>
> I suspect you have nailed the problem.
> So do I get around it by replacing date( with timestamp) essentially
> casting backup_date and the compare date as a timestamp?
>
> -----Original Message-----
> From: ADSM: Dist Stor Manager [mailto:ADSM-L AT VM.MARIST DOT EDU] On
> Behalf Of Andrew Raibeck
> Sent: Thursday, August 01, 2013 8:14 AM
> To: ADSM-L AT VM.MARIST DOT EDU
> Subject: Re: [ADSM-L] another sql query problem
>
> Hi Gary,
>
> What time of day does the script run?
>
> What time of day did the backup start?
>
> If you look at the dsmsched.log file, what was the time frame when most
of
> the files were backed up?
>
> One possibility occur to me: Let's say the backup started on July 30 at
> 23:00 and most of the files were backed up between 23:00 and midnight. If
> the script is run at 06:00 July 31, then subtracting 20 hours from that
and
> extracting only the date part from the result yields July 30, the same as
> the backup start date. Thus the WHERE clause would look like this:
>
>    [where ...] 2013-07-30> 20103-07-30
>
> which of course is not true, so the results are not included in the
output.
>
> - Andy
>
>
____________________________________________________________________________

>
> Andrew Raibeck | Tivoli Storage Manager Level 3 Technical Lead |
> storman AT us.ibm DOT com
>
> IBM Tivoli Storage Manager links:
> Product support:
> http://www.ibm.com/support/entry/portal/Overview/Software/Tivoli/
> Tivoli_Storage_Manager
>
> Online documentation:
>
https://www.ibm.com/developerworks/mydeveloperworks/wikis/home/wiki/Tivoli
> +Documentation+Central/page/Tivoli+Storage+Manager
> Product Wiki:
>
https://www.ibm.com/developerworks/mydeveloperworks/wikis/home/wiki/Tivoli
> +Storage+Manager/page/Home
>
> "ADSM: Dist Stor Manager" <ADSM-L AT vm.marist DOT edu> wrote on 2013-07-31
> 13:55:09:
>
> > From: "Lee, Gary" <GLEE AT BSU DOT EDU>
> > To: ADSM-L AT vm.marist DOT edu,
> > Date: 2013-07-31 13:56
> > Subject: another sql query problem
> > Sent by: "ADSM: Dist Stor Manager" <ADSM-L AT vm.marist DOT edu>
> >
> > If I run the following script with the command
> >
> > Run listback node_name 20
> >
> > I receive a list of 128 objects.
> >
> > However, a check of the dsmsched.log file shows the following:
> >
> > 07/31/2013 07:53:26 Total number of objects inspected:      634,241
> > 07/31/2013 07:53:26 Total number of objects assigned:        61,461
> > 07/31/2013 07:53:26 Total number of objects backed up:       13,732
> >
> > What is up with this?
> > I am trying to get a report of all files backed up for this run.
> >
> > Script follows:
> >
> >
> >
> >
> > /* parms:  node_name hours from today */
> > /* sample command: */
> > /* run listbackfiles libdi 24 */
> > /* */
> > /* lists files backed up for node libdi 1 day before today */
> > /* */
> >
> > set sqldisp wide
> > select filespace_name, concat(hl_name, ll_name) as name, -
> > backup_date -
> > from backups where -
> > date(backup_date)> date(current_timestamp - $2 hours) -
> > and node_name = upper('$1') -
> > order by backup_date
> >
>