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@VM.MARIST.EDU] On Behalf Of Andrew Raibeck Sent: Thursday, August 01, 2013 8:14 AM To: ADSM-L@VM.MARIST.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 | stor...@us.ibm.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@vm.marist.edu> wrote on 2013-07-31 13:55:09: > From: "Lee, Gary" <g...@bsu.edu> > To: ADSM-L@vm.marist.edu, > Date: 2013-07-31 13:56 > Subject: another sql query problem > Sent by: "ADSM: Dist Stor Manager" <ADSM-L@vm.marist.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 >