Re: 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
Re: 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@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
Re: another sql query problem
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 |
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-08-01
08:21:59:
From: Lee, Gary g...@bsu.edu
To: ADSM-L@vm.marist.edu,
Date: 2013-08-01 08:23
Subject: Re: another sql query problem
Sent by: ADSM: Dist Stor Manager ADSM-L@vm.marist.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@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
