Re: [ADSM-L] another sql query problem

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
>