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
another sql query problem
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