Re: Select Statement Help
This probably isn't completely right, but it might be a start: select node_name,hl_name,min(backup_date) from backups group by node_name,hl_name -- Cameron Hanover chano...@umich.edu Let's get dangerous. --Darkwing Duck On Mar 9, 2015, at 3:42 PM, Kamp, Bruce (Ext) bruce.k...@alcon.com wrote: I am found a couple TDP SQL nodes that aren't inactivating there backups so TSM isn't expiring them... What I am trying to find is the oldest backup date for each server with a name like _TDP. I can get this: Node Name HL_NAME BACKUP DATE STATE - - -- XYZ_TDP // 2009-08-17 ACTIVE_VERSION XYZ_TDP // 2009-09-13 ACTIVE_VERSION XYZ_TDP // 2009-09-14 ACTIVE_VERSION XYZ_TDP // 2009-09-15 ACTIVE_VERSION XYZ_TDP // 2009-09-16 ACTIVE_VERSION What I really want is something like this: Node Name HL_NAME BACKUP DATE STATE - - -- XYZ_TDP // 2009-08-17 ACTIVE_VERSION ABC_TDP // 2009-09-13 ACTIVE_VERSION 123_TDP // 2009-09-14 ACTIVE_VERSION Is this possible ? Thanks, Bruce Kamp TSM Administrator (817) 568-7331
Re: Select Statement Help
Thanks for the help! This what I ended up with. SELECT CAST((NODE_NAME) AS CHAR(20)) AS Node Name,CAST(MIN(BACKUP_DATE) AS DATE) AS BACKUP DATE FROM BACKUPS WHERE NODE_NAME LIKE '%_TDP' AND STATE='ACTIVE_VERSION' AND CLASS_NAME LIKE '%DB%' AND BACKUP_DATE '2015-02-01' AND FILESPACE_NAME NOT LIKE '%$%' GROUP BY NODE_NAME Bruce Kamp TSM Administrator (817) 568-7331 -Original Message- From: ADSM: Dist Stor Manager [mailto:ADSM-L@VM.MARIST.EDU] On Behalf Of Hanover, Cameron Sent: Tuesday, March 10, 2015 11:05 AM To: ADSM-L@VM.MARIST.EDU Subject: Re: [ADSM-L] Select Statement Help This probably isn't completely right, but it might be a start: select node_name,hl_name,min(backup_date) from backups group by node_name,hl_name -- Cameron Hanover chano...@umich.edu Let's get dangerous. --Darkwing Duck On Mar 9, 2015, at 3:42 PM, Kamp, Bruce (Ext) bruce.k...@alcon.com wrote: I am found a couple TDP SQL nodes that aren't inactivating there backups so TSM isn't expiring them... What I am trying to find is the oldest backup date for each server with a name like _TDP. I can get this: Node Name HL_NAME BACKUP DATE STATE - - -- XYZ_TDP // 2009-08-17 ACTIVE_VERSION XYZ_TDP // 2009-09-13 ACTIVE_VERSION XYZ_TDP // 2009-09-14 ACTIVE_VERSION XYZ_TDP // 2009-09-15 ACTIVE_VERSION XYZ_TDP // 2009-09-16 ACTIVE_VERSION What I really want is something like this: Node Name HL_NAME BACKUP DATE STATE - - -- XYZ_TDP // 2009-08-17 ACTIVE_VERSION ABC_TDP // 2009-09-13 ACTIVE_VERSION 123_TDP // 2009-09-14 ACTIVE_VERSION Is this possible ? Thanks, Bruce Kamp TSM Administrator (817) 568-7331
Re: Select Statement Help
Bruce, You could do a group by node_name at the end of your select statement. Best Regards, _ email: ron.delaw...@us.ibm.com Storage Services Offerings From: Kamp, Bruce (Ext) bruce.k...@alcon.com To: ADSM-L@VM.MARIST.EDU Date: 03/09/15 12:46 Subject:[ADSM-L] Select Statement Help Sent by:ADSM: Dist Stor Manager ADSM-L@VM.MARIST.EDU I am found a couple TDP SQL nodes that aren't inactivating there backups so TSM isn't expiring them... What I am trying to find is the oldest backup date for each server with a name like _TDP. I can get this: Node Name HL_NAME BACKUP DATE STATE - - -- XYZ_TDP // 2009-08-17 ACTIVE_VERSION XYZ_TDP // 2009-09-13 ACTIVE_VERSION XYZ_TDP // 2009-09-14 ACTIVE_VERSION XYZ_TDP // 2009-09-15 ACTIVE_VERSION XYZ_TDP // 2009-09-16 ACTIVE_VERSION What I really want is something like this: Node Name HL_NAME BACKUP DATE STATE - - -- XYZ_TDP // 2009-08-17 ACTIVE_VERSION ABC_TDP // 2009-09-13 ACTIVE_VERSION 123_TDP // 2009-09-14 ACTIVE_VERSION Is this possible ? Thanks, Bruce Kamp TSM Administrator (817) 568-7331
Select Statement Help
I am found a couple TDP SQL nodes that aren't inactivating there backups so TSM isn't expiring them... What I am trying to find is the oldest backup date for each server with a name like _TDP. I can get this: Node Name HL_NAME BACKUP DATE STATE - - -- XYZ_TDP // 2009-08-17 ACTIVE_VERSION XYZ_TDP // 2009-09-13 ACTIVE_VERSION XYZ_TDP // 2009-09-14 ACTIVE_VERSION XYZ_TDP // 2009-09-15 ACTIVE_VERSION XYZ_TDP // 2009-09-16 ACTIVE_VERSION What I really want is something like this: Node Name HL_NAME BACKUP DATE STATE - - -- XYZ_TDP // 2009-08-17 ACTIVE_VERSION ABC_TDP // 2009-09-13 ACTIVE_VERSION 123_TDP // 2009-09-14 ACTIVE_VERSION Is this possible ? Thanks, Bruce Kamp TSM Administrator (817) 568-7331
Select Statement Help
Hello Everyone - I am attempting miserably I might add to write a select statement that will produce a bunch of message found in the actlog. The problem I am having is grabbing the data from the last 24 hours.. I will schedule this to produce an email each day.. Any thoughts on how to construct this Thank you SELECT * FROM ACTLOG WHERE (MSGNO IN (406,4952,4954,4958,4960,4957,4970,4959,4961,4965,4963,4966,4967,4968,4969,4964, 4976, 4981)) and start_time=current_timestamp - 24 hours James Lepre --- Confidentiality Notice: The information in this e-mail and any attachments thereto is intended for the named recipient(s) only. This e-mail, including any attachments, may contain information that is privileged and confidential and subject to legal restrictions and penalties regarding its unauthorized disclosure or other use. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or the taking of any action or inaction in reliance on the contents of this e-mail and any of its attachments is STRICTLY PROHIBITED. If you have received this e-mail in error, please immediately notify the sender via return e-mail; delete this e-mail and all attachments from your e-mail system and your computer system and network; and destroy any paper copies you may have in your possession. Thank you for your cooperation.
Re: Select Statement Help
James, What version of TSM? Your select statement will work in TSM 6.3 if you change start_time to date_time. Please note that I did not verify the output, only that I got output. Rick At 01:39 PM 6/24/2014, you wrote: Hello Everyone - I am attempting miserably I might add to write a select statement that will produce a bunch of message found in the actlog. The problem I am having is grabbing the data from the last 24 hours.. I will schedule this to produce an email each day.. Any thoughts on how to construct this Thank you SELECT * FROM ACTLOG WHERE (MSGNO IN (406,4952,4954,4958,4960,4957,4970,4959,4961,4965,4963,4966,4967,4968,4969,4964, 4976, 4981)) and start_time=current_timestamp - 24 hours James Lepre --- Confidentiality Notice: The information in this e-mail and any attachments thereto is intended for the named recipient(s) only. This e-mail, including any attachments, may contain information that is privileged and confidential and subject to legal restrictions and penalties regarding its unauthorized disclosure or other use. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or the taking of any action or inaction in reliance on the contents of this e-mail and any of its attachments is STRICTLY PROHIBITED. If you have received this e-mail in error, please immediately notify the sender via return e-mail; delete this e-mail and all attachments from your e-mail system and your computer system and network; and destroy any paper copies you may have in your possession. Thank you for your cooperation. Rick Saylor Austin Community College Voice: (512)223-1182 Director of System Services 9101 Tuscany Way Fax: (512)223-1211 Information Technology Austin, Texas 78754
Re: Select Statement Help
Version 7.1 James Lepre Infrastructure Support Specialist Solix, Inc. | 30 Lanidex Plaza West | Parsippany, NJ 07054 T: 973.581.5362 | F: 973.599.6544 www.solixinc.com | Solix on Facebook | Solix on Twitter -Original Message- From: ADSM: Dist Stor Manager [mailto:ADSM-L@VM.MARIST.EDU] On Behalf Of Rick Saylor Sent: Tuesday, June 24, 2014 3:19 PM To: ADSM-L@VM.MARIST.EDU Subject: Re: [ADSM-L] Select Statement Help James, What version of TSM? Your select statement will work in TSM 6.3 if you change start_time to date_time. Please note that I did not verify the output, only that I got output. Rick At 01:39 PM 6/24/2014, you wrote: Hello Everyone - I am attempting miserably I might add to write a select statement that will produce a bunch of message found in the actlog. The problem I am having is grabbing the data from the last 24 hours.. I will schedule this to produce an email each day.. Any thoughts on how to construct this Thank you SELECT * FROM ACTLOG WHERE (MSGNO IN (406,4952,4954,4958,4960,4957,4970,4959,4961,4965,4963,4966,4967,4968,4 969,4964, 4976, 4981)) and start_time=current_timestamp - 24 hours James Lepre --- Confidentiality Notice: The information in this e-mail and any attachments thereto is intended for the named recipient(s) only. This e-mail, including any attachments, may contain information that is privileged and confidential and subject to legal restrictions and penalties regarding its unauthorized disclosure or other use. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or the taking of any action or inaction in reliance on the contents of this e-mail and any of its attachments is STRICTLY PROHIBITED. If you have received this e-mail in error, please immediately notify the sender via return e-mail; delete this e-mail and all attachments from your e-mail system and your computer system and network; and destroy any paper copies you may have in your possession. Thank you for your cooperation. Rick Saylor Austin Community College Voice: (512)223-1182 Director of System Services 9101 Tuscany Way Fax: (512)223-1211 Information Technology Austin, Texas 78754
Re: Select Statement Help
SELECT * FROM ACTLOG WHERE MSGNO IN (406,4952,4954,4958,4960,4957, 4970,4959,4961,4965,4963,4966,4967,4968,4969,4964, 4976, 4981) and start_timecurrent_timestamp - 24 hours On 24 June 2014 19:39, Lepre, James james.le...@solixinc.com wrote: Hello Everyone - I am attempting miserably I might add to write a select statement that will produce a bunch of message found in the actlog. The problem I am having is grabbing the data from the last 24 hours.. I will schedule this to produce an email each day.. Any thoughts on how to construct this Thank you SELECT * FROM ACTLOG WHERE (MSGNO IN (406,4952,4954,4958,4960,4957,4970,4959,4961,4965,4963,4966,4967,4968,4969,4964, 4976, 4981)) and start_time=current_timestamp - 24 hours James Lepre --- Confidentiality Notice: The information in this e-mail and any attachments thereto is intended for the named recipient(s) only. This e-mail, including any attachments, may contain information that is privileged and confidential and subject to legal restrictions and penalties regarding its unauthorized disclosure or other use. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or the taking of any action or inaction in reliance on the contents of this e-mail and any of its attachments is STRICTLY PROHIBITED. If you have received this e-mail in error, please immediately notify the sender via return e-mail; delete this e-mail and all attachments from your e-mail system and your computer system and network; and destroy any paper copies you may have in your possession. Thank you for your cooperation.
Re: select statement: help!
Hey! I have one other simple question. Is there a way that the output of this select statement will be able to go on one line so that it isn't so hard to read? The output currently looks like this, but I would rather have it all fit on one line. Thanks! NODE_NAME: VMSNW05 DATE: 2005-01-18 ACTIVITY: BACKUP START: 10:31:15 END: 12:05:51 MEGABYTES: 12 FILES: 140 SUCCESSFUL: NO Joni Moyer Highmark Storage Systems Work:(717)302-6603 Fax:(717)302-5974 [EMAIL PROTECTED] Andrew Raibeck [EMAIL PROTECTED] OMTo Sent by: ADSM: ADSM-L@VM.MARIST.EDU Dist Stor cc Manager [EMAIL PROTECTED] Subject .EDU Re: select statement: help! 01/18/2005 12:42 PM Please respond to ADSM: Dist Stor Manager [EMAIL PROTECTED] .EDU Maybe something like: select entity as node_name, date(start_time) as date, cast(activity as - varchar(10)) as activity, time(start_time) as start, time(end_time) as end, - cast(bytes/1024/1024 as decimal(6,0)) as megabytes, cast(affected as - decimal(7,0)) as files, successful - from summary - where date(start_time) date(current_timestamp - 1 day) - and (entity like 'HM%' or entity like 'GEO%') - order by successful Andy Raibeck IBM Software Group Tivoli Storage Manager Client Development Internal Notes e-mail: Andrew Raibeck/Tucson/[EMAIL PROTECTED] Internet e-mail: [EMAIL PROTECTED] The only dumb question is the one that goes unasked. The command line is your friend. Good enough is the enemy of excellence. ADSM: Dist Stor Manager ADSM-L@VM.MARIST.EDU wrote on 01/18/2005 10:33:11: Hello All! I have been trying, what seems like forever, to get this select statement to work and I am just not sure what I am doing wrong... Any suggestions? I am trying to get a list of the backups of all of the servers that begin with hm* and geo* within the past 24 hours and if they were successful, missed or failed. Thank you in advance! Here is what I have so far, but it doesn't seem to work: select entity as node_name, date(start_time) as date, cast(activity as varchar(10)) as activity, time(start_time) as start, time(end_time) as end, cast(bytes/1024/1024 as decimal(6,0)) as megabytes, cast(affected as decimal(7,0)) as files, successful from summary where date(start_time) current_timestamp - 1 day and entity like 'hm*,geo*' group by entity order by successful Joni Moyer Highmark Storage Systems Work:(717)302-6603 Fax:(717)302-5974 [EMAIL PROTECTED]
Re: select statement: help!
Joni, Maybe this http://msgs.adsm.org/cgi-bin/get/adsm0109/730.html will help you ! Cheers. Arnaud ** Panalpina Management Ltd., Basle, Switzerland, CIT Department Viadukstrasse 42, P.O. Box 4002 Basel/CH Phone: +41 (61) 226 11 11, FAX: +41 (61) 226 17 01 Direct: +41 (61) 226 19 78 e-mail: [EMAIL PROTECTED] ** -Original Message- From: ADSM: Dist Stor Manager [mailto:[EMAIL PROTECTED] On Behalf Of Joni Moyer Sent: Wednesday, 19 January, 2005 14:00 To: ADSM-L@VM.MARIST.EDU Subject: Re: select statement: help! Hey! I have one other simple question. Is there a way that the output of this select statement will be able to go on one line so that it isn't so hard to read? The output currently looks like this, but I would rather have it all fit on one line. Thanks! NODE_NAME: VMSNW05 DATE: 2005-01-18 ACTIVITY: BACKUP START: 10:31:15 END: 12:05:51 MEGABYTES: 12 FILES: 140 SUCCESSFUL: NO Joni Moyer Highmark Storage Systems Work:(717)302-6603 Fax:(717)302-5974 [EMAIL PROTECTED] Andrew Raibeck [EMAIL PROTECTED] OM To Sent by: ADSM: ADSM-L@VM.MARIST.EDU Dist Stor cc Manager [EMAIL PROTECTED] Subject .EDU Re: select statement: help! 01/18/2005 12:42 PM Please respond to ADSM: Dist Stor Manager [EMAIL PROTECTED] .EDU Maybe something like: select entity as node_name, date(start_time) as date, cast(activity as - varchar(10)) as activity, time(start_time) as start, time(end_time) as end, - cast(bytes/1024/1024 as decimal(6,0)) as megabytes, cast(affected as - decimal(7,0)) as files, successful - from summary - where date(start_time) date(current_timestamp - 1 day) - and (entity like 'HM%' or entity like 'GEO%') - order by successful Andy Raibeck IBM Software Group Tivoli Storage Manager Client Development Internal Notes e-mail: Andrew Raibeck/Tucson/[EMAIL PROTECTED] Internet e-mail: [EMAIL PROTECTED] The only dumb question is the one that goes unasked. The command line is your friend. Good enough is the enemy of excellence. ADSM: Dist Stor Manager ADSM-L@VM.MARIST.EDU wrote on 01/18/2005 10:33:11: Hello All! I have been trying, what seems like forever, to get this select statement to work and I am just not sure what I am doing wrong... Any suggestions? I am trying to get a list of the backups of all of the servers that begin with hm* and geo* within the past 24 hours and if they were successful, missed or failed. Thank you in advance! Here is what I have so far, but it doesn't seem to work: select entity as node_name, date(start_time) as date, cast(activity as varchar(10)) as activity, time(start_time) as start, time(end_time) as end, cast(bytes/1024/1024 as decimal(6,0)) as megabytes, cast(affected as decimal(7,0)) as files, successful from summary where date(start_time) current_timestamp - 1 day and entity like 'hm*,geo*' group by entity order by successful Joni Moyer Highmark Storage Systems Work:(717)302-6603 Fax:(717)302-5974 [EMAIL PROTECTED]
Re: select statement: help!
On Jan 19, 2005, at 8:00 AM, Joni Moyer wrote: I have one other simple question. Is there a way that the output of this select statement will be able to go on one line ... See my notes in http://people.bu.edu/rbs/ADSM.QuickFacts topic SELECT output, columnar instead of keyword list. Richard Sims
Re: select statement: help!
Hello, I went out and tried the following: dsmadmc -servername=server -displaymode=table select statement and it didn't work. Do you have to put the statement within single quotes? Thanks! Joni Moyer Highmark Storage Systems Work:(717)302-6603 Fax:(717)302-5974 [EMAIL PROTECTED] PAC Brion Arnaud [EMAIL PROTECTED] To ALPINA.COM ADSM-L@VM.MARIST.EDU Sent by: ADSM:cc Dist Stor Manager Subject [EMAIL PROTECTED] Re: select statement: help! .EDU 01/19/2005 08:16 AM Please respond to ADSM: Dist Stor Manager [EMAIL PROTECTED] .EDU Joni, Maybe this http://msgs.adsm.org/cgi-bin/get/adsm0109/730.html will help you ! Cheers. Arnaud ** Panalpina Management Ltd., Basle, Switzerland, CIT Department Viadukstrasse 42, P.O. Box 4002 Basel/CH Phone: +41 (61) 226 11 11, FAX: +41 (61) 226 17 01 Direct: +41 (61) 226 19 78 e-mail: [EMAIL PROTECTED] ** -Original Message- From: ADSM: Dist Stor Manager [mailto:[EMAIL PROTECTED] On Behalf Of Joni Moyer Sent: Wednesday, 19 January, 2005 14:00 To: ADSM-L@VM.MARIST.EDU Subject: Re: select statement: help! Hey! I have one other simple question. Is there a way that the output of this select statement will be able to go on one line so that it isn't so hard to read? The output currently looks like this, but I would rather have it all fit on one line. Thanks! NODE_NAME: VMSNW05 DATE: 2005-01-18 ACTIVITY: BACKUP START: 10:31:15 END: 12:05:51 MEGABYTES: 12 FILES: 140 SUCCESSFUL: NO Joni Moyer Highmark Storage Systems Work:(717)302-6603 Fax:(717)302-5974 [EMAIL PROTECTED] Andrew Raibeck [EMAIL PROTECTED] OM To Sent by: ADSM: ADSM-L@VM.MARIST.EDU Dist Stor cc Manager [EMAIL PROTECTED] Subject .EDU Re: select statement: help! 01/18/2005 12:42 PM Please respond to ADSM: Dist Stor Manager [EMAIL PROTECTED] .EDU Maybe something like: select entity as node_name, date(start_time) as date, cast(activity as - varchar(10)) as activity, time(start_time) as start, time(end_time) as end, - cast(bytes/1024/1024 as decimal(6,0)) as megabytes, cast(affected as - decimal(7,0)) as files, successful - from summary - where date(start_time) date(current_timestamp - 1 day) - and (entity like 'HM%' or entity like 'GEO%') - order by successful Andy Raibeck IBM Software Group Tivoli Storage Manager Client Development Internal Notes e-mail: Andrew Raibeck/Tucson/[EMAIL PROTECTED] Internet e-mail: [EMAIL PROTECTED] The only dumb question is the one that goes unasked. The command line is your friend. Good enough is the enemy of excellence. ADSM: Dist Stor Manager ADSM-L@VM.MARIST.EDU wrote on 01/18/2005 10:33:11: Hello All! I have been trying, what seems like forever, to get this select statement to work and I am just not sure what I am doing wrong... Any suggestions? I am trying to get a list of the backups of all of the servers that begin with hm* and geo* within the past 24 hours and if they were successful, missed or failed. Thank you in advance! Here is what I have so far, but it doesn't seem to work: select entity as node_name, date(start_time) as date, cast(activity as varchar(10)) as activity, time(start_time) as start, time(end_time) as end, cast(bytes/1024/1024 as decimal(6,0)) as megabytes, cast(affected as decimal(7,0)) as files, successful from summary where date(start_time) current_timestamp - 1 day and entity like 'hm*,geo*' group by entity order by successful Joni Moyer Highmark Storage Systems Work:(717)302-6603 Fax:(717)302-5974 [EMAIL PROTECTED]
Re: select statement: help!
Hi. Use -tabdelimited to get selectresult on one line with TAB between each column. See this example: [EMAIL PROTECTED] tmp]# dsmadmc -server= -id= -password= -tabdelimited andycap:select '*' from libraries IBM Tivoli Storage Manager Command Line Administrative Interface - Version 5, Release 2, Level 3.0 (c) Copyright by IBM Corporation and other(s) 1990, 2004. All Rights Reserved. Session established with server DSM0B: AIX-RS/6000 Server Version 5, Release 1, Level 9.3 Server date/time: 19-01-2005 14:58:45 Last access: 19-01-2005 14:50:01 ANS8000I Server command: ':select * from libraries' ANR1699I Resolved to 1 server(s) - issuing command SELECT against server(s). ANR1687I Output for command 'SELECT ' issued against server follows: ACS_9940B ACSLS 0 NO OOF 2004-07-19 13:11:08.00 ANR1688I Output for command 'SELECT ' issued against server completed. ANR1694I Server processed command 'SELECT ' and completed successfully. ANR1697I Command 'SELECT ' processed by 1 server(s): 1 successful, 0 with warnings, and 0 with errors. ANS8002I Highest return code was 0. |-+-| | | | | Eivind Birkeland | E-mail: [EMAIL PROTECTED] | | Unix/DBA Manager | Phone: +47 55 14 00 00 / 22 51 | | Statoil Norway | Mobile: +47 907 76 942 | | | | |-+-| Joni Moyer [EMAIL PROTECTED]To: ADSM-L@VM.MARIST.EDU HMARK.COM cc: (bcc: Eivind Birkeland) Sent by: ADSM:Subject: Re: select statement: help! Dist Stor Manager [EMAIL PROTECTED] ST.EDU 19.01.2005 14:54 Please respond to ADSM: Dist Stor Manager Hello, I went out and tried the following: dsmadmc -servername=server -displaymode=table select statement and it didn't work. Do you have to put the statement within single quotes? Thanks! Joni Moyer Highmark Storage Systems Work:(717)302-6603 Fax:(717)302-5974 [EMAIL PROTECTED] PAC Brion Arnaud [EMAIL PROTECTED] To ALPINA.COM ADSM-L@VM.MARIST.EDU Sent by: ADSM:cc Dist Stor Manager Subject [EMAIL PROTECTED] Re: select statement: help! .EDU 01/19/2005 08:16 AM Please respond to ADSM: Dist Stor Manager [EMAIL PROTECTED] .EDU Joni, Maybe this http://msgs.adsm.org/cgi-bin/get/adsm0109/730.html will help you ! Cheers. Arnaud ** Panalpina Management Ltd., Basle, Switzerland, CIT Department Viadukstrasse 42, P.O. Box 4002 Basel/CH Phone: +41 (61) 226 11 11, FAX: +41 (61) 226 17 01 Direct: +41 (61) 226 19 78 e-mail: [EMAIL PROTECTED] ** -Original Message- From: ADSM: Dist Stor Manager [mailto:[EMAIL PROTECTED] On Behalf Of Joni Moyer Sent: Wednesday, 19 January, 2005 14:00 To: ADSM-L@VM.MARIST.EDU Subject: Re: select statement: help! Hey! I have one other simple question. Is there a way that the output of this select statement will be able to go on one line so that it isn't so hard to read? The output currently looks like this, but I would rather have it all fit on one line. Thanks! NODE_NAME: VMSNW05 DATE: 2005-01-18 ACTIVITY: BACKUP START: 10:31:15 END: 12:05:51 MEGABYTES: 12 FILES: 140 SUCCESSFUL: NO Joni Moyer Highmark Storage Systems Work:(717)302-6603 Fax:(717)302-5974 [EMAIL PROTECTED] Andrew Raibeck [EMAIL PROTECTED] OM To Sent by: ADSM: ADSM-L@VM.MARIST.EDU Dist Stor cc Manager [EMAIL PROTECTED] Subject .EDU Re: select statement: help! 01/18/2005 12:42 PM Please respond to ADSM: Dist Stor Manager [EMAIL PROTECTED] .EDU Maybe something like: select entity
Re: select statement: help!
Joni, I usually use single quotes, but have some scrits where double quotes are used, all without problem ! This an example of the scripts we're using : CMD=dsmadmc -id=xxx -password=xxx -datao=yes $CMD select process_num from processes where process='Backup Storage Pool' Note the -datao=yes parameter, which avoids having TSM header in your output (works with TSM version 5.2) Hope this helped ! Arnaud ** Panalpina Management Ltd., Basle, Switzerland, CIT Department Viadukstrasse 42, P.O. Box 4002 Basel/CH Phone: +41 (61) 226 11 11, FAX: +41 (61) 226 17 01 Direct: +41 (61) 226 19 78 e-mail: [EMAIL PROTECTED] ** -Original Message- From: ADSM: Dist Stor Manager [mailto:[EMAIL PROTECTED] On Behalf Of Joni Moyer Sent: Wednesday, 19 January, 2005 14:55 To: ADSM-L@VM.MARIST.EDU Subject: Re: select statement: help! Hello, I went out and tried the following: dsmadmc -servername=server -displaymode=table select statement and it didn't work. Do you have to put the statement within single quotes? Thanks! Joni Moyer Highmark Storage Systems Work:(717)302-6603 Fax:(717)302-5974 [EMAIL PROTECTED] PAC Brion Arnaud [EMAIL PROTECTED] To ALPINA.COM ADSM-L@VM.MARIST.EDU Sent by: ADSM: cc Dist Stor Manager Subject [EMAIL PROTECTED] Re: select statement: help! .EDU 01/19/2005 08:16 AM Please respond to ADSM: Dist Stor Manager [EMAIL PROTECTED] .EDU Joni, Maybe this http://msgs.adsm.org/cgi-bin/get/adsm0109/730.html will help you ! Cheers. Arnaud ** Panalpina Management Ltd., Basle, Switzerland, CIT Department Viadukstrasse 42, P.O. Box 4002 Basel/CH Phone: +41 (61) 226 11 11, FAX: +41 (61) 226 17 01 Direct: +41 (61) 226 19 78 e-mail: [EMAIL PROTECTED] ** -Original Message- From: ADSM: Dist Stor Manager [mailto:[EMAIL PROTECTED] On Behalf Of Joni Moyer Sent: Wednesday, 19 January, 2005 14:00 To: ADSM-L@VM.MARIST.EDU Subject: Re: select statement: help! Hey! I have one other simple question. Is there a way that the output of this select statement will be able to go on one line so that it isn't so hard to read? The output currently looks like this, but I would rather have it all fit on one line. Thanks! NODE_NAME: VMSNW05 DATE: 2005-01-18 ACTIVITY: BACKUP START: 10:31:15 END: 12:05:51 MEGABYTES: 12 FILES: 140 SUCCESSFUL: NO Joni Moyer Highmark Storage Systems Work:(717)302-6603 Fax:(717)302-5974 [EMAIL PROTECTED] Andrew Raibeck [EMAIL PROTECTED] OM To Sent by: ADSM: ADSM-L@VM.MARIST.EDU Dist Stor cc Manager [EMAIL PROTECTED] Subject .EDU Re: select statement: help! 01/18/2005 12:42 PM Please respond to ADSM: Dist Stor Manager [EMAIL PROTECTED] .EDU Maybe something like: select entity as node_name, date(start_time) as date, cast(activity as - varchar(10)) as activity, time(start_time) as start, time(end_time) as end, - cast(bytes/1024/1024 as decimal(6,0)) as megabytes, cast(affected as - decimal(7,0)) as files, successful - from summary - where date(start_time) date(current_timestamp - 1 day) - and (entity like 'HM%' or entity like 'GEO%') - order by successful Andy Raibeck IBM Software Group Tivoli Storage Manager Client Development Internal Notes e-mail: Andrew Raibeck/Tucson/[EMAIL PROTECTED] Internet e-mail: [EMAIL PROTECTED] The only dumb question is the one that goes unasked. The command line is your friend. Good enough is the enemy of excellence. ADSM: Dist Stor Manager ADSM-L@VM.MARIST.EDU wrote on 01/18/2005 10:33:11: Hello All! I have been trying, what seems like forever, to get this select statement to work and I am just not sure what I am doing wrong... Any suggestions? I am trying to get a list of the backups of all of the servers that begin with hm* and geo* within the past 24 hours and if they were successful, missed or failed. Thank you in advance! Here is what I have so far, but it doesn't seem to work: select entity as node_name, date(start_time) as date, cast(activity as varchar(10)) as activity, time(start_time) as start, time(end_time) as end, cast(bytes/1024/1024
Re: select statement: help!
Joni, The -displaymode=table option should give you the desired format (that is the specific purpose of this option). Your note suggests you omitted the -ID and -PASSWORD options. It would help if you would post (via copy paste) the actual command you issued along with the results, so we can see what you are seeing. Regards, Andy Andy Raibeck IBM Software Group Tivoli Storage Manager Client Development Internal Notes e-mail: Andrew Raibeck/Tucson/[EMAIL PROTECTED] Internet e-mail: [EMAIL PROTECTED] The only dumb question is the one that goes unasked. The command line is your friend. Good enough is the enemy of excellence. ADSM: Dist Stor Manager ADSM-L@VM.MARIST.EDU wrote on 2005-01-19 06:54:49: Hello, I went out and tried the following: dsmadmc -servername=server -displaymode=table select statement and it didn't work. Do you have to put the statement within single quotes? Thanks! Joni Moyer Highmark Storage Systems Work:(717)302-6603 Fax:(717)302-5974 [EMAIL PROTECTED] PAC Brion Arnaud [EMAIL PROTECTED] To ALPINA.COM ADSM-L@VM.MARIST.EDU Sent by: ADSM: cc Dist Stor Manager Subject [EMAIL PROTECTED] Re: select statement: help! .EDU 01/19/2005 08:16 AM Please respond to ADSM: Dist Stor Manager [EMAIL PROTECTED] .EDU Joni, Maybe this http://msgs.adsm.org/cgi-bin/get/adsm0109/730.html will help you ! Cheers. Arnaud ** Panalpina Management Ltd., Basle, Switzerland, CIT Department Viadukstrasse 42, P.O. Box 4002 Basel/CH Phone: +41 (61) 226 11 11, FAX: +41 (61) 226 17 01 Direct: +41 (61) 226 19 78 e-mail: [EMAIL PROTECTED] ** -Original Message- From: ADSM: Dist Stor Manager [mailto:[EMAIL PROTECTED] On Behalf Of Joni Moyer Sent: Wednesday, 19 January, 2005 14:00 To: ADSM-L@VM.MARIST.EDU Subject: Re: select statement: help! Hey! I have one other simple question. Is there a way that the output of this select statement will be able to go on one line so that it isn't so hard to read? The output currently looks like this, but I would rather have it all fit on one line. Thanks! NODE_NAME: VMSNW05 DATE: 2005-01-18 ACTIVITY: BACKUP START: 10:31:15 END: 12:05:51 MEGABYTES: 12 FILES: 140 SUCCESSFUL: NO Joni Moyer Highmark Storage Systems Work:(717)302-6603 Fax:(717)302-5974 [EMAIL PROTECTED] Andrew Raibeck [EMAIL PROTECTED] OM To Sent by: ADSM: ADSM-L@VM.MARIST.EDU Dist Stor cc Manager [EMAIL PROTECTED] Subject .EDU Re: select statement: help! 01/18/2005 12:42 PM Please respond to ADSM: Dist Stor Manager [EMAIL PROTECTED] .EDU Maybe something like: select entity as node_name, date(start_time) as date, cast(activity as - varchar(10)) as activity, time(start_time) as start, time(end_time) as end, - cast(bytes/1024/1024 as decimal(6,0)) as megabytes, cast(affected as - decimal(7,0)) as files, successful - from summary - where date(start_time) date(current_timestamp - 1 day) - and (entity like 'HM%' or entity like 'GEO%') - order by successful Andy Raibeck IBM Software Group Tivoli Storage Manager Client Development Internal Notes e-mail: Andrew Raibeck/Tucson/[EMAIL PROTECTED] Internet e-mail: [EMAIL PROTECTED] The only dumb question is the one that goes unasked. The command line is your friend. Good enough is the enemy of excellence. ADSM: Dist Stor Manager ADSM-L@VM.MARIST.EDU wrote on 01/18/2005 10:33:11: Hello All! I have been trying, what seems like forever, to get this select statement to work and I am just not sure what I am doing wrong... Any suggestions? I am trying to get a list of the backups of all of the servers that begin with hm* and geo* within the past 24 hours and if they were successful, missed or failed. Thank you in advance! Here is what I have so far, but it doesn't seem to work: select entity as node_name, date(start_time) as date, cast(activity as varchar(10)) as activity, time(start_time) as start, time(end_time) as end, cast(bytes/1024/1024 as decimal(6,0)) as megabytes, cast(affected as decimal(7,0)) as files, successful from summary where date(start_time) current_timestamp
Re: select statement: help!
Hi Andy!
You have a very good point of showing my command. Without it is kind of
difficult. Here is the exact command that I am entering (I'm just going to
use admin admin as id and password and x as the servername):
dsmadmc -server=x -id=admin -password=admin -displaymode=table 'select
entity as node_name, date(start_time) as date, -
cast(activity as varchar(10)) as activity, time(start_time) as
start, time(end_time)as end, cast(bytes/1024/1024 as decimal(6,0)) as
megabytes, -
cast(affected as decimal(7,0)) as files, successful -
from summary
where date(start_time) date(current_timestamp - 1 day) -
and (entity like 'HM%' or entity like 'PAB%' or entity like 'VMS%'
or entity like 'GEOHMKLG%') -
and activity='BACKUP' order by successful'
I've just been entering this on the command-line, so I don't have any -
within the command. That's when I receive the following:
ksh: 0403-057 Syntax error: `(' is not expected.
/home/lidzr8v (chrs144:lidzr8v)
Do I not need the quotes around the select statement? Thank you in
advance!
Joni Moyer
Highmark
Storage Systems
Work:(717)302-6603
Fax:(717)302-5974
[EMAIL PROTECTED]
Andrew Raibeck
[EMAIL PROTECTED]
OMTo
Sent by: ADSM: ADSM-L@VM.MARIST.EDU
Dist Stor cc
Manager
[EMAIL PROTECTED] Subject
.EDU Re: select statement: help!
01/19/2005 11:34
AM
Please respond to
ADSM: Dist Stor
Manager
[EMAIL PROTECTED]
.EDU
Joni,
The -displaymode=table option should give you the desired format (that is
the specific purpose of this option).
Your note suggests you omitted the -ID and -PASSWORD options.
It would help if you would post (via copy paste) the actual command you
issued along with the results, so we can see what you are seeing.
Regards,
Andy
Andy Raibeck
IBM Software Group
Tivoli Storage Manager Client Development
Internal Notes e-mail: Andrew Raibeck/Tucson/[EMAIL PROTECTED]
Internet e-mail: [EMAIL PROTECTED]
The only dumb question is the one that goes unasked.
The command line is your friend.
Good enough is the enemy of excellence.
ADSM: Dist Stor Manager ADSM-L@VM.MARIST.EDU wrote on 2005-01-19
06:54:49:
Hello,
I went out and tried the following:
dsmadmc -servername=server -displaymode=table select statement and it
didn't work. Do you have to put the statement within single quotes?
Thanks!
Joni Moyer
Highmark
Storage Systems
Work:(717)302-6603
Fax:(717)302-5974
[EMAIL PROTECTED]
PAC Brion
Arnaud
[EMAIL PROTECTED] To
ALPINA.COM ADSM-L@VM.MARIST.EDU
Sent by: ADSM: cc
Dist Stor
Manager Subject
[EMAIL PROTECTED] Re: select statement: help!
.EDU
01/19/2005 08:16
AM
Please respond to
ADSM: Dist Stor
Manager
[EMAIL PROTECTED]
.EDU
Joni,
Maybe this http://msgs.adsm.org/cgi-bin/get/adsm0109/730.html will help
you !
Cheers.
Arnaud
**
Panalpina Management Ltd., Basle, Switzerland, CIT Department
Viadukstrasse 42, P.O. Box 4002 Basel/CH
Phone: +41 (61) 226 11 11, FAX: +41 (61) 226 17 01
Direct: +41 (61) 226 19 78
e-mail: [EMAIL PROTECTED]
**
-Original Message-
From: ADSM: Dist Stor Manager [mailto:[EMAIL PROTECTED] On Behalf Of
Joni Moyer
Sent: Wednesday, 19 January, 2005 14:00
To: ADSM-L@VM.MARIST.EDU
Subject: Re: select statement: help!
Hey!
I have one other simple question. Is there a way that the output of
this select statement will be able to go on one line so that it isn't so
hard to read? The output currently looks like this, but I would rather
have it all fit on one line. Thanks!
NODE_NAME: VMSNW05
DATE: 2005-01-18
ACTIVITY: BACKUP
START: 10:31:15
END: 12:05:51
MEGABYTES: 12
FILES: 140
SUCCESSFUL: NO
Joni Moyer
Highmark
Storage Systems
Work:(717)302-6603
Fax:(717)302-5974
[EMAIL PROTECTED]
Andrew Raibeck
[EMAIL PROTECTED]
OM
To
Sent by: ADSM: ADSM-L@VM.MARIST.EDU
Dist Stor
cc
Manager
[EMAIL
Re: select statement: help!
Joni - Match your quotes and you'll quickly see what's wrong.
Richard Sims
On Jan 19, 2005, at 12:31 PM, Joni Moyer wrote:
Hi Andy!
You have a very good point of showing my command. Without it is kind
of
difficult. Here is the exact command that I am entering (I'm just
going to
use admin admin as id and password and x as the servername):
dsmadmc -server=x -id=admin -password=admin -displaymode=table
'select
entity as node_name, date(start_time) as date, -
cast(activity as varchar(10)) as activity, time(start_time) as
start, time(end_time)as end, cast(bytes/1024/1024 as decimal(6,0)) as
megabytes, -
cast(affected as decimal(7,0)) as files, successful -
from summary
where date(start_time) date(current_timestamp - 1 day) -
and (entity like 'HM%' or entity like 'PAB%' or entity like
'VMS%'
or entity like 'GEOHMKLG%') -
and activity='BACKUP' order by successful'
I've just been entering this on the command-line, so I don't have any -
within the command. That's when I receive the following:
ksh: 0403-057 Syntax error: `(' is not expected.
/home/lidzr8v (chrs144:lidzr8v)
Do I not need the quotes around the select statement? Thank you in
advance!
Re: select statement: help!
Hi Joni,
You don't need the quotes around the SELECT statement. Also, when
specifying the complete command in batch mode, I don't think using the
hyphen (-) for command continuation will work, since it is the OS command
line processor that sees the hyphen, not the admin client.
Try putting your SELECT statement into a separate text file called, for
example, select.macro:
select entity as node_name, date(start_time) as date, -
cast(activity as varchar(10)) as activity, -
time(start_time) as start, time(end_time)as end, -
cast(bytes/1024/1024 as decimal(6,0)) as megabytes, -
cast(affected as decimal(7,0)) as files, successful -
from summary -
where date(start_time) date(current_timestamp - 1 day) and -
(entity like 'HM%' or entity like 'PAB%' or -
entity like 'VMS%' or entity like 'GEOHMKLG%') and -
activity='BACKUP' order by successful
Then invoke the macro like this:
dsmadmc -se=blah -id=admin -pa=xxx -displaymode=table macro
select.macro
Regards,
Andy
Andy Raibeck
IBM Software Group
Tivoli Storage Manager Client Development
Internal Notes e-mail: Andrew Raibeck/Tucson/[EMAIL PROTECTED]
Internet e-mail: [EMAIL PROTECTED]
The only dumb question is the one that goes unasked.
The command line is your friend.
Good enough is the enemy of excellence.
ADSM: Dist Stor Manager ADSM-L@VM.MARIST.EDU wrote on 2005-01-19
10:31:17:
Hi Andy!
You have a very good point of showing my command. Without it is kind of
difficult. Here is the exact command that I am entering (I'm just going
to
use admin admin as id and password and x as the servername):
dsmadmc -server=x -id=admin -password=admin -displaymode=table
'select
entity as node_name, date(start_time) as date, -
cast(activity as varchar(10)) as activity, time(start_time) as
start, time(end_time)as end, cast(bytes/1024/1024 as decimal(6,0)) as
megabytes, -
cast(affected as decimal(7,0)) as files, successful -
from summary
where date(start_time) date(current_timestamp - 1 day) -
and (entity like 'HM%' or entity like 'PAB%' or entity like
'VMS%'
or entity like 'GEOHMKLG%') -
and activity='BACKUP' order by successful'
I've just been entering this on the command-line, so I don't have any -
within the command. That's when I receive the following:
ksh: 0403-057 Syntax error: `(' is not expected.
/home/lidzr8v (chrs144:lidzr8v)
Do I not need the quotes around the select statement? Thank you in
advance!
Joni Moyer
Highmark
Storage Systems
Work:(717)302-6603
Fax:(717)302-5974
[EMAIL PROTECTED]
Andrew Raibeck
[EMAIL PROTECTED]
OM To
Sent by: ADSM: ADSM-L@VM.MARIST.EDU
Dist Stor cc
Manager
[EMAIL PROTECTED] Subject
.EDU Re: select statement: help!
01/19/2005 11:34
AM
Please respond to
ADSM: Dist Stor
Manager
[EMAIL PROTECTED]
.EDU
Joni,
The -displaymode=table option should give you the desired format (that
is
the specific purpose of this option).
Your note suggests you omitted the -ID and -PASSWORD options.
It would help if you would post (via copy paste) the actual command
you
issued along with the results, so we can see what you are seeing.
Regards,
Andy
Andy Raibeck
IBM Software Group
Tivoli Storage Manager Client Development
Internal Notes e-mail: Andrew Raibeck/Tucson/[EMAIL PROTECTED]
Internet e-mail: [EMAIL PROTECTED]
The only dumb question is the one that goes unasked.
The command line is your friend.
Good enough is the enemy of excellence.
ADSM: Dist Stor Manager ADSM-L@VM.MARIST.EDU wrote on 2005-01-19
06:54:49:
Hello,
I went out and tried the following:
dsmadmc -servername=server -displaymode=table select statement and it
didn't work. Do you have to put the statement within single quotes?
Thanks!
Joni Moyer
Highmark
Storage Systems
Work:(717)302-6603
Fax:(717)302-5974
[EMAIL PROTECTED]
PAC Brion
Arnaud
[EMAIL PROTECTED] To
ALPINA.COM ADSM-L@VM.MARIST.EDU
Sent by: ADSM: cc
Dist Stor
Manager Subject
[EMAIL PROTECTED] Re: select statement: help!
.EDU
01/19/2005 08:16
AM
Please respond to
ADSM: Dist Stor
Manager
[EMAIL PROTECTED]
.EDU
Joni,
Maybe this http://msgs.adsm.org/cgi-bin/get/adsm0109/730.html will
help
you !
Cheers.
Arnaud
select statement: help!
Hello All! I have been trying, what seems like forever, to get this select statement to work and I am just not sure what I am doing wrong... Any suggestions? I am trying to get a list of the backups of all of the servers that begin with hm* and geo* within the past 24 hours and if they were successful, missed or failed. Thank you in advance! Here is what I have so far, but it doesn't seem to work: select entity as node_name, date(start_time) as date, cast(activity as varchar(10)) as activity, time(start_time) as start, time(end_time) as end, cast(bytes/1024/1024 as decimal(6,0)) as megabytes, cast(affected as decimal(7,0)) as files, successful from summary where date(start_time) current_timestamp - 1 day and entity like 'hm*,geo*' group by entity order by successful Joni Moyer Highmark Storage Systems Work:(717)302-6603 Fax:(717)302-5974 [EMAIL PROTECTED]
Re: select statement: help!
Maybe something like: select entity as node_name, date(start_time) as date, cast(activity as - varchar(10)) as activity, time(start_time) as start, time(end_time) as end, - cast(bytes/1024/1024 as decimal(6,0)) as megabytes, cast(affected as - decimal(7,0)) as files, successful - from summary - where date(start_time) date(current_timestamp - 1 day) - and (entity like 'HM%' or entity like 'GEO%') - order by successful Andy Raibeck IBM Software Group Tivoli Storage Manager Client Development Internal Notes e-mail: Andrew Raibeck/Tucson/[EMAIL PROTECTED] Internet e-mail: [EMAIL PROTECTED] The only dumb question is the one that goes unasked. The command line is your friend. Good enough is the enemy of excellence. ADSM: Dist Stor Manager ADSM-L@VM.MARIST.EDU wrote on 01/18/2005 10:33:11: Hello All! I have been trying, what seems like forever, to get this select statement to work and I am just not sure what I am doing wrong... Any suggestions? I am trying to get a list of the backups of all of the servers that begin with hm* and geo* within the past 24 hours and if they were successful, missed or failed. Thank you in advance! Here is what I have so far, but it doesn't seem to work: select entity as node_name, date(start_time) as date, cast(activity as varchar(10)) as activity, time(start_time) as start, time(end_time) as end, cast(bytes/1024/1024 as decimal(6,0)) as megabytes, cast(affected as decimal(7,0)) as files, successful from summary where date(start_time) current_timestamp - 1 day and entity like 'hm*,geo*' group by entity order by successful Joni Moyer Highmark Storage Systems Work:(717)302-6603 Fax:(717)302-5974 [EMAIL PROTECTED]
Re: Select Statement Help for Volume Breakdown
Hello all, Related to this in a way then, can anyone help me with how to determine which of all my defined tapes are scratch, for audit purposes ? Matt Chan Stapleton, Mark [EMAIL PROTECTED] Sent by: ADSM: Dist Stor Manager [EMAIL PROTECTED] 23/11/2004 07:23 AM Please respond to ADSM: Dist Stor Manager [EMAIL PROTECTED] To [EMAIL PROTECTED] cc Subject Re: Select Statement Help for Volume Breakdown There are no scratch tapes in storage pools. A tape volume only exists in a storage pool when it contains client data (and is therefore no longer a scratch tape). -- Mark Stapleton ([EMAIL PROTECTED]) Berbee Information Networks Office 262.521.5627
Re: Select Statement Help for Volume Breakdown
Matt - Do select * from volumes where scratch='Yes' That is, a volume which is in use in a storage pool, which came from the Scratch tape pool, will have its SCRATCH column set to Yes. A storage pool volume which is in the storage pool because of a Define Volume will have a SCRATCH column value of No. Richard Sims On Nov 30, 2004, at 7:21 AM, Matt Chan wrote: Hello all, Related to this in a way then, can anyone help me with how to determine which of all my defined tapes are scratch, for audit purposes ? Matt Chan Stapleton, Mark [EMAIL PROTECTED] Sent by: ADSM: Dist Stor Manager [EMAIL PROTECTED] 23/11/2004 07:23 AM Please respond to ADSM: Dist Stor Manager [EMAIL PROTECTED] To [EMAIL PROTECTED] cc Subject Re: Select Statement Help for Volume Breakdown There are no scratch tapes in storage pools. A tape volume only exists in a storage pool when it contains client data (and is therefore no longer a scratch tape). -- Mark Stapleton ([EMAIL PROTECTED]) Berbee Information Networks Office 262.521.5627
Re: Select Statement Help for Volume Breakdown
Matt, The scratch column is case sensitive. Looks like the yes no are capitalized. The status column is YES|NO|NULL james From: ADSM: Dist Stor Manager on behalf of Richard Sims Sent: Tue 11/30/2004 6:13 AM To: [EMAIL PROTECTED] Subject: Re: Select Statement Help for Volume Breakdown Matt - Do select * from volumes where scratch='Yes' That is, a volume which is in use in a storage pool, which came from the Scratch tape pool, will have its SCRATCH column set to Yes. A storage pool volume which is in the storage pool because of a Define Volume will have a SCRATCH column value of No. Richard Sims On Nov 30, 2004, at 7:21 AM, Matt Chan wrote: Hello all, Related to this in a way then, can anyone help me with how to determine which of all my defined tapes are scratch, for audit purposes ? Matt Chan Stapleton, Mark [EMAIL PROTECTED] Sent by: ADSM: Dist Stor Manager [EMAIL PROTECTED] 23/11/2004 07:23 AM Please respond to ADSM: Dist Stor Manager [EMAIL PROTECTED] To [EMAIL PROTECTED] cc Subject Re: Select Statement Help for Volume Breakdown There are no scratch tapes in storage pools. A tape volume only exists in a storage pool when it contains client data (and is therefore no longer a scratch tape). -- Mark Stapleton ([EMAIL PROTECTED]) Berbee Information Networks Office 262.521.5627
Re: Select Statement Help for Volume Breakdown
On Nov 30, 2004, at 8:40 AM, James Choate wrote: The scratch column is case sensitive. Looks like the yes no are capitalized. The status column is YES|NO|NULL Such things are prone to change over time: in some releases only the initial character is capitalized. We often have to find out the particulars by trial and error, or getting a syscat columns listing. Richard Sims
Re: Select Statement Help for Volume Breakdown
The use of upper/lower functions in SQL statements can save a lot of grief! [EMAIL PROTECTED] 11/30/2004 8:47:29 AM On Nov 30, 2004, at 8:40 AM, James Choate wrote: The scratch column is case sensitive. Looks like the yes no are capitalized. The status column is YES|NO|NULL Such things are prone to change over time: in some releases only the initial character is capitalized. We often have to find out the particulars by trial and error, or getting a syscat columns listing. Richard Sims
Select Statement Help for Volume Breakdown
How do I combine these two scripts? I want to be able to see a breakdown by stg pools breakdown of scratch/privates in the stgpools. select status, count(*) from libvolumes where library_name = 'LTOLIB' group by status I have this..but, I really want to see the scratch/private breakdown? select stgpool_name, status, scratch, count(*) as Volumes - from volumes - group by status, scratch, stgpool_name STGPOOL_NAME STATUS SCRATCH Volumes -- -- -- --- APIDR1 EMPTY YES 2 BCBSDR2EMPTY YES 1 APIDR1 FILLINGYES13 APIDR2 FILLINGYES12 Background: Operating Sys - AIX 5.2.2.0 TSM- 5.2.3.3 Nancy Backhaus Enterprise Systems [EMAIL PROTECTED] Office: (716) 887-7979 Cell: (716) 609-2138 CONFIDENTIALITY NOTICE: This email message and any attachments are for the sole use of the intended recipient(s) and may contain proprietary, confidential, trade secret or privileged information. Any unauthorized review, use, disclosure or distribution is prohibited and may be a violation of law. If you are not the intended recipient or a person responsible for delivering this message to an intended recipient, please contact the sender by reply email and destroy all copies of the original message.
Re: Select Statement Help for Volume Breakdown
There are no scratch tapes in storage pools. A tape volume only exists in a storage pool when it contains client data (and is therefore no longer a scratch tape). -- Mark Stapleton ([EMAIL PROTECTED]) Berbee Information Networks Office 262.521.5627 -Original Message- From: ADSM: Dist Stor Manager [mailto:[EMAIL PROTECTED] On Behalf Of Nancy L Backhaus Sent: Monday, November 22, 2004 12:59 PM To: [EMAIL PROTECTED] Subject: Select Statement Help for Volume Breakdown How do I combine these two scripts? I want to be able to see a breakdown by stg pools breakdown of scratch/privates in the stgpools. select status, count(*) from libvolumes where library_name = 'LTOLIB' group by status I have this..but, I really want to see the scratch/private breakdown? select stgpool_name, status, scratch, count(*) as Volumes - from volumes - group by status, scratch, stgpool_name STGPOOL_NAME STATUS SCRATCH Volumes -- -- -- --- APIDR1 EMPTY YES 2 BCBSDR2EMPTY YES 1 APIDR1 FILLINGYES13 APIDR2 FILLINGYES12 Background: Operating Sys - AIX 5.2.2.0 TSM- 5.2.3.3 Nancy Backhaus Enterprise Systems [EMAIL PROTECTED] Office: (716) 887-7979 Cell: (716) 609-2138 CONFIDENTIALITY NOTICE: This email message and any attachments are for the sole use of the intended recipient(s) and may contain proprietary, confidential, trade secret or privileged information. Any unauthorized review, use, disclosure or distribution is prohibited and may be a violation of law. If you are not the intended recipient or a person responsible for delivering this message to an intended recipient, please contact the sender by reply email and destroy all copies of the original message.
Re: Select Statement Help for Volume Breakdown
There are no scratch tapes in storage pools. A tape volume only exists in a storage pool when it contains client data (and is therefore no longer a scratch tape). Just a footnote on this: Though less commonly seen, an empty tape volume can also exist in a storage pool if it is manually defined to that pool by the administrator (but even then, it is not the same as a scratch tape). Regards, Andy Andy Raibeck IBM Software Group Tivoli Storage Manager Client Development Internal Notes e-mail: Andrew Raibeck/Tucson/[EMAIL PROTECTED] Internet e-mail: [EMAIL PROTECTED] The only dumb question is the one that goes unasked. The command line is your friend. Good enough is the enemy of excellence. ADSM: Dist Stor Manager [EMAIL PROTECTED] wrote on 11/22/2004 13:23:37: There are no scratch tapes in storage pools. A tape volume only exists in a storage pool when it contains client data (and is therefore no longer a scratch tape). -- Mark Stapleton ([EMAIL PROTECTED]) Berbee Information Networks Office 262.521.5627 -Original Message- From: ADSM: Dist Stor Manager [mailto:[EMAIL PROTECTED] On Behalf Of Nancy L Backhaus Sent: Monday, November 22, 2004 12:59 PM To: [EMAIL PROTECTED] Subject: Select Statement Help for Volume Breakdown How do I combine these two scripts? I want to be able to see a breakdown by stg pools breakdown of scratch/privates in the stgpools. select status, count(*) from libvolumes where library_name = 'LTOLIB' group by status I have this..but, I really want to see the scratch/private breakdown? select stgpool_name, status, scratch, count(*) as Volumes - from volumes - group by status, scratch, stgpool_name STGPOOL_NAME STATUS SCRATCH Volumes -- -- -- --- APIDR1 EMPTY YES 2 BCBSDR2EMPTY YES 1 APIDR1 FILLINGYES13 APIDR2 FILLINGYES12 Background: Operating Sys - AIX 5.2.2.0 TSM- 5.2.3.3 Nancy Backhaus Enterprise Systems [EMAIL PROTECTED] Office: (716) 887-7979 Cell: (716) 609-2138 CONFIDENTIALITY NOTICE: This email message and any attachments are for the sole use of the intended recipient(s) and may contain proprietary, confidential, trade secret or privileged information. Any unauthorized review, use, disclosure or distribution is prohibited and may be a violation of law. If you are not the intended recipient or a person responsible for delivering this message to an intended recipient, please contact the sender by reply email and destroy all copies of the original message.
select statement help
What is the select statement to tell what node is backing up certain filespaces if the customer does not know the node name? The filespace is: /cagvsp1/entp/prod/ Thanks. Jason
Re: select statement help
? Doesnt the customers filespace containt the nodename(servername)? If not, a q filespace should show you which filespace that belongs to which node. Best Regards Daniel Sparrman --- Daniel Sparrman Chef Utveckling Drift Exist i Stockholm AB Propellervägen 6B 183 62 TÄBY Växel: 08 - 754 98 00 Mobil: 070 - 399 27 51 Cain, Jason (Corporate) [EMAIL PROTECTED] Sent by: ADSM: Dist Stor Manager [EMAIL PROTECTED] 2004-10-07 16:32 Please respond to ADSM: Dist Stor Manager [EMAIL PROTECTED] To [EMAIL PROTECTED] cc Subject select statement help What is the select statement to tell what node is backing up certain filespaces if the customer does not know the node name? The filespace is: /cagvsp1/entp/prod/ Thanks. Jason
Re: select statement help
select node_name from occupancy where filespace_name='/cagvsp1/entp/prod' [EMAIL PROTECTED] Cain, Jason (Corporate) [EMAIL PROTECTED] Sent by: ADSM: Dist Stor Manager [EMAIL PROTECTED] 10/07/2004 09:32 AM Please respond to ADSM: Dist Stor Manager [EMAIL PROTECTED] To [EMAIL PROTECTED] cc Subject select statement help What is the select statement to tell what node is backing up certain filespaces if the customer does not know the node name? The filespace is: /cagvsp1/entp/prod/ Thanks. Jason
Re: SQL Select statement help
Thanks for this. Interesting way of attacking it :) Matt. -Original Message- From: ADSM: Dist Stor Manager [mailto:[EMAIL PROTECTED] On Behalf Of Johnson, Milton Sent: Tuesday, August 31, 2004 10:19 PM To: [EMAIL PROTECTED] Subject: Re: SQL Select statement help You can create a script with the following select statements: select stgpool_name,'1' as Days Pending,count(*) as Total from volumes where status='PENDING' - and cast((current_timestamp-PENDING_DATE)days as decimal)=1 group by stgpool_name where status='PENDING' - and cast((current_timestamp-PENDING_DATE)days as decimal)=1 group by stgpool_name select stgpool_name,'2' as Days Pending,count(*) as Total from volumes where status='PENDING' - and cast((current_timestamp-PENDING_DATE)days as decimal)=2 group by stgpool_name select stgpool_name,'3' as Days Pending,count(*) as Total from volumes where status='PENDING' - and cast((current_timestamp-PENDING_DATE)days as decimal)=3 group by stgpool_name select stgpool_name,'4' as Days Pending,count(*) as Total from volumes where status='PENDING' - and cast((current_timestamp-PENDING_DATE)days as decimal)=4 group by stgpool_name select stgpool_name,'=5' as Days Pending,count(*) as Total from volumes where status='PENDING' - and cast((current_timestamp-PENDING_DATE)days as decimal)=5 group by stgpool_name When you run it you get something like: STGPOOL_NAME Days Pending TOTAL -- --- COPYPOOL 1 10 TAPEPOOL 1 9 STGPOOL_NAME Days Pending TOTAL -- --- COPYPOOL 2 10 TAPEPOOL 2 13 STGPOOL_NAME Days Pending TOTAL -- --- COPYPOOL 3 10 TAPEPOOL 3 12 STGPOOL_NAME Days Pending TOTAL -- --- COPYPOOL 4 10 TAPEPOOL 4 11 STGPOOL_NAME Days Pending TOTAL -- --- COPYPOOL =50 TAPEPOOL =50 H. Milton Johnson Voice: (210) 677-6728 -Original Message- From: ADSM: Dist Stor Manager [mailto:[EMAIL PROTECTED] On Behalf Of Prather, Wanda Sent: Tuesday, August 31, 2004 1:52 PM To: [EMAIL PROTECTED] Subject: Re: SQL Select statement help I hope I'm wrong, but I don't think it's possible to do that. -Original Message- From: ADSM: Dist Stor Manager [mailto:[EMAIL PROTECTED] On Behalf Of Warren, Matthew (Retail) Sent: Tuesday, August 31, 2004 10:51 AM To: [EMAIL PROTECTED] Subject: SQL Select statement help Hallo ([I]T/AD)SM'ers Can any wise and clever SQL types help with the following?; I am trying to get TSM to show the number of tapes that went pending by date. I can get this; ANS8000I Server command: 'select cast(pending_date as char(10)),count(*) from volumes where status='PENDING' group by pending_date' Unnamed[1] Unnamed[2] -- --- 2004-08-27 1 2004-08-27 1 2004-08-27 1 2004-08-28 1 2004-08-28 1 2004-08-28 1 2004-08-28 1 2004-08-28 1 2004-08-29 1 2004-08-29 1 etc.. but what I would like is for the count(*) to be totaling per day. I hoped perhaps the cast() would force this to happen, but it appears the underlying timestamp is still being taken into account. I have tried various other permutations, along the lines of; ANS8000I Server command: 'select cast(pending_date as char(10)) as pending,count(*) from volumes where status='PENDING' group by pending' ANR2940E The reference 'PENDING' is an unknown SQL column name. | V.. g,count(*) from volumes where status='PENDING' group by pending but, alas, no joy. I'm wondering if it is actually possible? - I know a little Ksh script that can do it, but I was hoping to achieve it just within TSM - either with a select as I am trying or via some other method. Thanks, Matt. ___ Disclaimer Notice __ This message and any attachments are confidential and should only be read by those to whom they are addressed. If you are not the intended recipient, please contact us, delete the message from your computer and destroy any copies. Any distribution or copying without our prior permission is prohibited. Internet communications are not always secure
SQL Select statement help
Hallo ([I]T/AD)SM'ers Can any wise and clever SQL types help with the following?; I am trying to get TSM to show the number of tapes that went pending by date. I can get this; ANS8000I Server command: 'select cast(pending_date as char(10)),count(*) from volumes where status='PENDING' group by pending_date' Unnamed[1] Unnamed[2] -- --- 2004-08-27 1 2004-08-27 1 2004-08-27 1 2004-08-28 1 2004-08-28 1 2004-08-28 1 2004-08-28 1 2004-08-28 1 2004-08-29 1 2004-08-29 1 etc.. but what I would like is for the count(*) to be totaling per day. I hoped perhaps the cast() would force this to happen, but it appears the underlying timestamp is still being taken into account. I have tried various other permutations, along the lines of; ANS8000I Server command: 'select cast(pending_date as char(10)) as pending,count(*) from volumes where status='PENDING' group by pending' ANR2940E The reference 'PENDING' is an unknown SQL column name. | V.. g,count(*) from volumes where status='PENDING' group by pending but, alas, no joy. I'm wondering if it is actually possible? - I know a little Ksh script that can do it, but I was hoping to achieve it just within TSM - either with a select as I am trying or via some other method. Thanks, Matt. ___ Disclaimer Notice __ This message and any attachments are confidential and should only be read by those to whom they are addressed. If you are not the intended recipient, please contact us, delete the message from your computer and destroy any copies. Any distribution or copying without our prior permission is prohibited. Internet communications are not always secure and therefore Powergen Retail Limited does not accept legal responsibility for this message. The recipient is responsible for verifying its authenticity before acting on the contents. Any views or opinions presented are solely those of the author and do not necessarily represent those of Powergen Retail Limited. Registered addresses: Powergen Retail Limited, Westwood Way, Westwood Business Park, Coventry, CV4 8LG. Registered in England and Wales No: 3407430 Telephone +44 (0) 2476 42 4000 Fax +44 (0) 2476 42 5432
Re: SQL Select statement help
I hope I'm wrong, but I don't think it's possible to do that. -Original Message- From: ADSM: Dist Stor Manager [mailto:[EMAIL PROTECTED] On Behalf Of Warren, Matthew (Retail) Sent: Tuesday, August 31, 2004 10:51 AM To: [EMAIL PROTECTED] Subject: SQL Select statement help Hallo ([I]T/AD)SM'ers Can any wise and clever SQL types help with the following?; I am trying to get TSM to show the number of tapes that went pending by date. I can get this; ANS8000I Server command: 'select cast(pending_date as char(10)),count(*) from volumes where status='PENDING' group by pending_date' Unnamed[1] Unnamed[2] -- --- 2004-08-27 1 2004-08-27 1 2004-08-27 1 2004-08-28 1 2004-08-28 1 2004-08-28 1 2004-08-28 1 2004-08-28 1 2004-08-29 1 2004-08-29 1 etc.. but what I would like is for the count(*) to be totaling per day. I hoped perhaps the cast() would force this to happen, but it appears the underlying timestamp is still being taken into account. I have tried various other permutations, along the lines of; ANS8000I Server command: 'select cast(pending_date as char(10)) as pending,count(*) from volumes where status='PENDING' group by pending' ANR2940E The reference 'PENDING' is an unknown SQL column name. | V.. g,count(*) from volumes where status='PENDING' group by pending but, alas, no joy. I'm wondering if it is actually possible? - I know a little Ksh script that can do it, but I was hoping to achieve it just within TSM - either with a select as I am trying or via some other method. Thanks, Matt. ___ Disclaimer Notice __ This message and any attachments are confidential and should only be read by those to whom they are addressed. If you are not the intended recipient, please contact us, delete the message from your computer and destroy any copies. Any distribution or copying without our prior permission is prohibited. Internet communications are not always secure and therefore Powergen Retail Limited does not accept legal responsibility for this message. The recipient is responsible for verifying its authenticity before acting on the contents. Any views or opinions presented are solely those of the author and do not necessarily represent those of Powergen Retail Limited. Registered addresses: Powergen Retail Limited, Westwood Way, Westwood Business Park, Coventry, CV4 8LG. Registered in England and Wales No: 3407430 Telephone +44 (0) 2476 42 4000 Fax +44 (0) 2476 42 5432
Re: SQL Select statement help
I went in to Excel used OBDC to easily select the criteria, and here's the sql statement it produces. I had to add devclass criteria as we have some old tapes around. SELECT VOLUMES.VOLUME_NAME, VOLUMES.STATUS, VOLUMES.PENDING_DATE, VOLUMES.DEVCLASS_NAME FROM VOLUMES VOLUMES WHERE (VOLUMES.DEVCLASS_NAME='3592TAPE') AND (VOLUMES.PENDING_DATE Is Not Null) ORDER BY VOLUMES.PENDING_DATE Results VOLUME_NAME STATUS PENDING_DATEDEVCLASS_NAME U00936 PENDING 8/23/2004 19:21 3592TAPE U00051 PENDING 8/23/2004 19:24 3592TAPE U00203 PENDING 8/23/2004 19:25 3592TAPE U00087 PENDING 8/23/2004 19:27 3592TAPE U00821 PENDING 8/23/2004 19:30 3592TAPE U00031 PENDING 8/23/2004 19:51 3592TAPE U00421 PENDING 8/23/2004 19:51 3592TAPE U00720 PENDING 8/23/2004 19:52 3592TAPE U00943 PENDING 8/23/2004 19:53 3592TAPE -Original Message- From: ADSM: Dist Stor Manager [mailto:[EMAIL PROTECTED] Behalf Of Prather, Wanda Sent: Tuesday, August 31, 2004 1:52 PM To: [EMAIL PROTECTED] Subject: Re: SQL Select statement help I hope I'm wrong, but I don't think it's possible to do that. -Original Message- From: ADSM: Dist Stor Manager [mailto:[EMAIL PROTECTED] On Behalf Of Warren, Matthew (Retail) Sent: Tuesday, August 31, 2004 10:51 AM To: [EMAIL PROTECTED] Subject: SQL Select statement help Hallo ([I]T/AD)SM'ers Can any wise and clever SQL types help with the following?; I am trying to get TSM to show the number of tapes that went pending by date. I can get this; ANS8000I Server command: 'select cast(pending_date as char(10)),count(*) from volumes where status='PENDING' group by pending_date' Unnamed[1] Unnamed[2] -- --- 2004-08-27 1 2004-08-27 1 2004-08-27 1 2004-08-28 1 2004-08-28 1 2004-08-28 1 2004-08-28 1 2004-08-28 1 2004-08-29 1 2004-08-29 1 etc.. but what I would like is for the count(*) to be totaling per day. I hoped perhaps the cast() would force this to happen, but it appears the underlying timestamp is still being taken into account. I have tried various other permutations, along the lines of; ANS8000I Server command: 'select cast(pending_date as char(10)) as pending,count(*) from volumes where status='PENDING' group by pending' ANR2940E The reference 'PENDING' is an unknown SQL column name. | V.. g,count(*) from volumes where status='PENDING' group by pending but, alas, no joy. I'm wondering if it is actually possible? - I know a little Ksh script that can do it, but I was hoping to achieve it just within TSM - either with a select as I am trying or via some other method. Thanks, Matt. ___ Disclaimer Notice __ This message and any attachments are confidential and should only be read by those to whom they are addressed. If you are not the intended recipient, please contact us, delete the message from your computer and destroy any copies. Any distribution or copying without our prior permission is prohibited. Internet communications are not always secure and therefore Powergen Retail Limited does not accept legal responsibility for this message. The recipient is responsible for verifying its authenticity before acting on the contents. Any views or opinions presented are solely those of the author and do not necessarily represent those of Powergen Retail Limited. Registered addresses: Powergen Retail Limited, Westwood Way, Westwood Business Park, Coventry, CV4 8LG. Registered in England and Wales No: 3407430 Telephone +44 (0) 2476 42 4000 Fax +44 (0) 2476 42 5432
Re: SQL Select statement help
Using Excel and TSM ODBC I selected the criteria, and here's the sql statement it produces. I had to add devclass criteria as we have some old tapes around. SELECT VOLUMES.VOLUME_NAME, VOLUMES.STATUS, VOLUMES.PENDING_DATE, VOLUMES.DEVCLASS_NAME FROM VOLUMES VOLUMES WHERE (VOLUMES.DEVCLASS_NAME='3592TAPE') AND (VOLUMES.PENDING_DATE Is Not Null) ORDER BY VOLUMES.PENDING_DATE Results VOLUME_NAME STATUS PENDING_DATEDEVCLASS_NAME U00936 PENDING 8/23/2004 19:21 3592TAPE U00051 PENDING 8/23/2004 19:24 3592TAPE U00203 PENDING 8/23/2004 19:25 3592TAPE U00087 PENDING 8/23/2004 19:27 3592TAPE U00821 PENDING 8/23/2004 19:30 3592TAPE U00031 PENDING 8/23/2004 19:51 3592TAPE U00421 PENDING 8/23/2004 19:51 3592TAPE U00720 PENDING 8/23/2004 19:52 3592TAPE U00943 PENDING 8/23/2004 19:53 3592TAPE -Original Message- From: ADSM: Dist Stor Manager [mailto:[EMAIL PROTECTED] Behalf Of Prather, Wanda Sent: Tuesday, August 31, 2004 1:52 PM To: [EMAIL PROTECTED] Subject: Re: SQL Select statement help I hope I'm wrong, but I don't think it's possible to do that. -Original Message- From: ADSM: Dist Stor Manager [mailto:[EMAIL PROTECTED] On Behalf Of Warren, Matthew (Retail) Sent: Tuesday, August 31, 2004 10:51 AM To: [EMAIL PROTECTED] Subject: SQL Select statement help Hallo ([I]T/AD)SM'ers Can any wise and clever SQL types help with the following?; I am trying to get TSM to show the number of tapes that went pending by date. I can get this; ANS8000I Server command: 'select cast(pending_date as char(10)),count(*) from volumes where status='PENDING' group by pending_date' Unnamed[1] Unnamed[2] -- --- 2004-08-27 1 2004-08-27 1 2004-08-27 1 2004-08-28 1 2004-08-28 1 2004-08-28 1 2004-08-28 1 2004-08-28 1 2004-08-29 1 2004-08-29 1 etc.. but what I would like is for the count(*) to be totaling per day. I hoped perhaps the cast() would force this to happen, but it appears the underlying timestamp is still being taken into account. I have tried various other permutations, along the lines of; ANS8000I Server command: 'select cast(pending_date as char(10)) as pending,count(*) from volumes where status='PENDING' group by pending' ANR2940E The reference 'PENDING' is an unknown SQL column name. | V.. g,count(*) from volumes where status='PENDING' group by pending but, alas, no joy. I'm wondering if it is actually possible? - I know a little Ksh script that can do it, but I was hoping to achieve it just within TSM - either with a select as I am trying or via some other method. Thanks, Matt. ___ Disclaimer Notice __ This message and any attachments are confidential and should only be read by those to whom they are addressed. If you are not the intended recipient, please contact us, delete the message from your computer and destroy any copies. Any distribution or copying without our prior permission is prohibited. Internet communications are not always secure and therefore Powergen Retail Limited does not accept legal responsibility for this message. The recipient is responsible for verifying its authenticity before acting on the contents. Any views or opinions presented are solely those of the author and do not necessarily represent those of Powergen Retail Limited. Registered addresses: Powergen Retail Limited, Westwood Way, Westwood Business Park, Coventry, CV4 8LG. Registered in England and Wales No: 3407430 Telephone +44 (0) 2476 42 4000 Fax +44 (0) 2476 42 5432
Re: SQL Select statement help
You can create a script with the following select statements: select stgpool_name,'1' as Days Pending,count(*) as Total from volumes where status='PENDING' - and cast((current_timestamp-PENDING_DATE)days as decimal)=1 group by stgpool_name select stgpool_name,'2' as Days Pending,count(*) as Total from volumes where status='PENDING' - and cast((current_timestamp-PENDING_DATE)days as decimal)=2 group by stgpool_name select stgpool_name,'3' as Days Pending,count(*) as Total from volumes where status='PENDING' - and cast((current_timestamp-PENDING_DATE)days as decimal)=3 group by stgpool_name select stgpool_name,'4' as Days Pending,count(*) as Total from volumes where status='PENDING' - and cast((current_timestamp-PENDING_DATE)days as decimal)=4 group by stgpool_name select stgpool_name,'=5' as Days Pending,count(*) as Total from volumes where status='PENDING' - and cast((current_timestamp-PENDING_DATE)days as decimal)=5 group by stgpool_name When you run it you get something like: STGPOOL_NAME Days Pending TOTAL -- --- COPYPOOL 1 10 TAPEPOOL 1 9 STGPOOL_NAME Days Pending TOTAL -- --- COPYPOOL 2 10 TAPEPOOL 2 13 STGPOOL_NAME Days Pending TOTAL -- --- COPYPOOL 3 10 TAPEPOOL 3 12 STGPOOL_NAME Days Pending TOTAL -- --- COPYPOOL 4 10 TAPEPOOL 4 11 STGPOOL_NAME Days Pending TOTAL -- --- COPYPOOL =50 TAPEPOOL =50 H. Milton Johnson Voice: (210) 677-6728 -Original Message- From: ADSM: Dist Stor Manager [mailto:[EMAIL PROTECTED] On Behalf Of Prather, Wanda Sent: Tuesday, August 31, 2004 1:52 PM To: [EMAIL PROTECTED] Subject: Re: SQL Select statement help I hope I'm wrong, but I don't think it's possible to do that. -Original Message- From: ADSM: Dist Stor Manager [mailto:[EMAIL PROTECTED] On Behalf Of Warren, Matthew (Retail) Sent: Tuesday, August 31, 2004 10:51 AM To: [EMAIL PROTECTED] Subject: SQL Select statement help Hallo ([I]T/AD)SM'ers Can any wise and clever SQL types help with the following?; I am trying to get TSM to show the number of tapes that went pending by date. I can get this; ANS8000I Server command: 'select cast(pending_date as char(10)),count(*) from volumes where status='PENDING' group by pending_date' Unnamed[1] Unnamed[2] -- --- 2004-08-27 1 2004-08-27 1 2004-08-27 1 2004-08-28 1 2004-08-28 1 2004-08-28 1 2004-08-28 1 2004-08-28 1 2004-08-29 1 2004-08-29 1 etc.. but what I would like is for the count(*) to be totaling per day. I hoped perhaps the cast() would force this to happen, but it appears the underlying timestamp is still being taken into account. I have tried various other permutations, along the lines of; ANS8000I Server command: 'select cast(pending_date as char(10)) as pending,count(*) from volumes where status='PENDING' group by pending' ANR2940E The reference 'PENDING' is an unknown SQL column name. | V.. g,count(*) from volumes where status='PENDING' group by pending but, alas, no joy. I'm wondering if it is actually possible? - I know a little Ksh script that can do it, but I was hoping to achieve it just within TSM - either with a select as I am trying or via some other method. Thanks, Matt. ___ Disclaimer Notice __ This message and any attachments are confidential and should only be read by those to whom they are addressed. If you are not the intended recipient, please contact us, delete the message from your computer and destroy any copies. Any distribution or copying without our prior permission is prohibited. Internet communications are not always secure and therefore Powergen Retail Limited does not accept legal responsibility for this message. The recipient is responsible for verifying its authenticity before acting on the contents. Any views or opinions presented are solely those of the author and do not necessarily represent those of Powergen Retail Limited. Registered addresses: Powergen Retail Limited, Westwood Way, Westwood Business
Re: Select statement help.
Ben Bullock wrote :- Ok, this should be simple, but it's driving me nuts. Ben, Try this one ... (caters for mulitple libraries) * */ /* Script Name: Q_STORAGE_UTIL */ /* Description: Display utilisation of all */ /* TAPE Storage Pools */ /* Parameters: None*/ /* Example: run Q_STORAGE_UTIL */ /* */ set sqldisplaymode wide select - cast (volumes.stgpool_name as varchar(10)) as TAPE_STORAGE_POOL, - cast ((select library_name from devclasses where stgpools.devclass = devclasses.devclass_name ) as varchar(9)) as LIBRARY, - cast (count(volume_name) as integer) as #_VOLUMES, - cast (maxscratch as integer) as MAX_VOLUMES, - cast (sum(volumes.est_capacity_mb)/1024 as integer) as CAPACITY_GB, - cast (avg(volumes.pct_utilized) as integer) as %_UTILIZATION - from volumes, stgpools - where volumes.stgpool_name = stgpools.stgpool_name - and devclass_name 'DISK' - and devclass_name 'FILE' - group by volumes.stgpool_name, maxscratch Bruce Mitchell Storage Management (AABR Project) Barclays Capital Canary Wharf, London * +44 (020) 7773 4010 * [EMAIL PROTECTED] For more information about Barclays Capital, please visit our web site at http://www.barcap.com. Internet communications are not secure and therefore the Barclays Group does not accept legal responsibility for the contents of this message. Although the Barclays Group operates anti-virus programmes, it does not accept responsibility for any damage whatsoever that is caused by viruses being passed. Any views or opinions presented are solely those of the author and do not necessarily represent those of the Barclays Group. Replies to this email may be monitored by the Barclays Group for operational or business reasons.
Re: Select statement help.
Thank you, That is right up the ally of what I was looking for. Thanks for your assistance. Ben Micron Technology Inc. Boise Id -Original Message- From: ADSM: Dist Stor Manager [mailto:[EMAIL PROTECTED] On Behalf Of Bruce Mitchell Sent: Friday, August 13, 2004 2:09 AM To: [EMAIL PROTECTED] Subject: Re: Select statement help. Ben Bullock wrote :- Ok, this should be simple, but it's driving me nuts. Ben, Try this one ... (caters for mulitple libraries) * */ /* Script Name: Q_STORAGE_UTIL */ /* Description: Display utilisation of all */ /* TAPE Storage Pools */ /* Parameters: None*/ /* Example: run Q_STORAGE_UTIL */ /* */ set sqldisplaymode wide select - cast (volumes.stgpool_name as varchar(10)) as TAPE_STORAGE_POOL, - cast ((select library_name from devclasses where stgpools.devclass = devclasses.devclass_name ) as varchar(9)) as LIBRARY, - cast (count(volume_name) as integer) as #_VOLUMES, - cast (maxscratch as integer) as MAX_VOLUMES, - cast (sum(volumes.est_capacity_mb)/1024 as integer) as CAPACITY_GB, - cast (avg(volumes.pct_utilized) as integer) as %_UTILIZATION - from volumes, stgpools - where volumes.stgpool_name = stgpools.stgpool_name - and devclass_name 'DISK' - and devclass_name 'FILE' - group by volumes.stgpool_name, maxscratch Bruce Mitchell Storage Management (AABR Project) Barclays Capital Canary Wharf, London * +44 (020) 7773 4010 * [EMAIL PROTECTED] For more information about Barclays Capital, please visit our web site at http://www.barcap.com. Internet communications are not secure and therefore the Barclays Group does not accept legal responsibility for the contents of this message. Although the Barclays Group operates anti-virus programmes, it does not accept responsibility for any damage whatsoever that is caused by viruses being passed. Any views or opinions presented are solely those of the author and do not necessarily represent those of the Barclays Group. Replies to this email may be monitored by the Barclays Group for operational or business reasons.
Select statement help.
Ok, this should be simple, but it's driving me nuts. I have 2 simple select statements that I'm trying to combine into 1 but it's not working. Here are the 2 scripts and their output: tsm: TSMSERV1Aselect STGPOOL_NAME, MAXSCRATCH from stgpools where DEVCLASS like '3590DEV' STGPOOL_NAMEMAXSCRATCH -- --- A_COPYPOOL 200 A_TAPEPOOL 500 COL_FS_TAPEPOOL500 COPYPOOL 1000 DB_TAPEPOOL 1000 I_TAPEPOOL 500 tsm: TSMSERV1Aselect STGPOOL_NAME, (count(VOLUME_NAME))as # of tapes from volumes where DEVCLASS_NAME like '3590DEV' group by STGPOOL_NAME STGPOOL_NAME# of tapes -- --- A_COPYPOOL 7 A_TAPEPOOL 9 COL_FS_TAPEPOOL222 COPYPOOL 401 DB_TAPEPOOL262 I_TAPEPOOL 106 I would simply like to combine them into 1 script so that it is easier to compare the Maxscratch value with the actual number of tapes being used. Trying various things like: select vo.STGPOOL_NAME, st.STGPOOL_NAME, (count(vo.VOLUME_NAME))as # of tapes, st.MAXSCRATCH from volumes vo, stgpools st where vo.DEVCLASS_NAME like '3590DEV' and vo.STGPOOL_NAME=st.STGPOOL_NAME group by vo.STGPOOL_NAME But it's not working... any help? Thanks, Ben
Re: Select statement help.
I took your code and added a max function. Not sure if this is the best SQL way to deal with it but it makes the group by happy and seems to work: select max(st.STGPOOL_NAME) as STGPOOL, max(st.maxscratch) as MAXSCRATCH, (count(vo.VOLUME_NAME))as # of tapes from volumes vo, stgpools st where vo.DEVCLASS_NAME like 'LTO%' and vo.STGPOOL_NAME=st.STGPOOL_NAME group by vo.STGPOOL_NAME -Original Message- From: ADSM: Dist Stor Manager [mailto:[EMAIL PROTECTED] On Behalf Of Ben Bullock Sent: Thursday, August 12, 2004 11:46 AM To: [EMAIL PROTECTED] Subject: Select statement help. Ok, this should be simple, but it's driving me nuts. I have 2 simple select statements that I'm trying to combine into 1 but it's not working. Here are the 2 scripts and their output: tsm: TSMSERV1Aselect STGPOOL_NAME, MAXSCRATCH from stgpools where DEVCLASS like '3590DEV' STGPOOL_NAMEMAXSCRATCH -- --- A_COPYPOOL 200 A_TAPEPOOL 500 COL_FS_TAPEPOOL500 COPYPOOL 1000 DB_TAPEPOOL 1000 I_TAPEPOOL 500 tsm: TSMSERV1Aselect STGPOOL_NAME, (count(VOLUME_NAME))as # of tapes from volumes where DEVCLASS_NAME like '3590DEV' group by STGPOOL_NAME STGPOOL_NAME# of tapes -- --- A_COPYPOOL 7 A_TAPEPOOL 9 COL_FS_TAPEPOOL222 COPYPOOL 401 DB_TAPEPOOL262 I_TAPEPOOL 106 I would simply like to combine them into 1 script so that it is easier to compare the Maxscratch value with the actual number of tapes being used. Trying various things like: select vo.STGPOOL_NAME, st.STGPOOL_NAME, (count(vo.VOLUME_NAME))as # of tapes, st.MAXSCRATCH from volumes vo, stgpools st where vo.DEVCLASS_NAME like '3590DEV' and vo.STGPOOL_NAME=st.STGPOOL_NAME group by vo.STGPOOL_NAME But it's not working... any help? Thanks, Ben
select statement help
I am in need of some help here I have been asked to come up a way to do a select to find active and inactive file dates on files. Help would be appreciated very much. Thanks Justin Case
Re: select statement help
I am in need of some help here I have been asked to come up a way to do a select to find active and inactive file dates on files. Help would be appreciated very much. The following should help you in general: TSM Technical Guide redbook, appendix A TSM SQL. Sample SQL Select Statements: http://www.ibm.com/support/entdocview.wss?uid=swg21049808 Beyond that, tell us more about what you specifically need. Richard Sims
Re: select statement help
Thanks Richard that is what I was needing to do the select that was required by a customer. Justin Richard Sims [EMAIL PROTECTED]@VM.MARIST.EDU on 06/03/2004 01:34:12 PM Please respond to ADSM: Dist Stor Manager [EMAIL PROTECTED] Sent by:ADSM: Dist Stor Manager [EMAIL PROTECTED] To:[EMAIL PROTECTED] cc: Subject:Re: select statement help I am in need of some help here I have been asked to come up a way to do a select to find active and inactive file dates on files. Help would be appreciated very much. The following should help you in general: TSM Technical Guide redbook, appendix A TSM SQL. Sample SQL Select Statements: http://www.ibm.com/support/entdocview.wss?uid=swg21049808 Beyond that, tell us more about what you specifically need. Richard Sims
Select Statement Help
q act search=0986 begintime=-1 How can I issue this query above using a select statement? Thanks! --- The information contained in this e-mail message, and any attachment thereto, is confidential and may not be disclosed without our express permission. If you are not the intended recipient or an employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that you have received this message in error and that any review, dissemination, distribution or copying of this message, or any attachment thereto, in whole or in part, is strictly prohibited. If you have received this message in error, please immediately notify us by telephone, fax or e-mail and delete the message and all of its attachments. Thank you. Every effort is made to keep our network free from viruses. You should, however, review this e-mail message, as well as any attachment thereto, for viruses. We take no responsibility and have no liability for any computer virus which may be transferred via this e-mail message.
Re: Select Statement Help
select message from actlog where msgno=0986 and date_time(current_timestamp-(1 hour)) Michael French Savvis Communications Enterprise Storage Engineer (314)628-7392 -- desk (408)239-9913 -- mobile -Original Message- From: Chang, Calvin [mailto:[EMAIL PROTECTED] Sent: Thursday, May 20, 2004 11:47 AM To: [EMAIL PROTECTED] Subject: Select Statement Help q act search=0986 begintime=-1 How can I issue this query above using a select statement? Thanks! --- The information contained in this e-mail message, and any attachment thereto, is confidential and may not be disclosed without our express permission. If you are not the intended recipient or an employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that you have received this message in error and that any review, dissemination, distribution or copying of this message, or any attachment thereto, in whole or in part, is strictly prohibited. If you have received this message in error, please immediately notify us by telephone, fax or e-mail and delete the message and all of its attachments. Thank you. Every effort is made to keep our network free from viruses. You should, however, review this e-mail message, as well as any attachment thereto, for viruses. We take no responsibility and have no liability for any computer virus which may be transferred via this e-mail message.
tsm select statement help
Does anyone have an sql syntax/statement example on how to join two sql tables in adsm/tsm? I have two tables in that adsm schema which both contain info that the other doesn't respectively. So I want to join them to get the desired results. I'm looking at Oracle examples on the web none really good. Any assistance would be appreciated. I know I could figure it out but if someone could save me trial and error time. I would really appreciate it.. Thanks in advance. --Justin Richard Bleistein Unix/TSM Systems Administrator
Re: tsm select statement help
Here ya go. This joins the occupancy table and the filespaces table to give me a list of the filespaces that haven't been backed up in over 60 days, and the SIZE they occupy on the server in my pool named BAKUPTAPE. the oc and fs are made up shorthand tags that let you distinguish between the column in one table and the column of the same name in a different table. Not intuitively obvious. Good to make friends with your local DBA, they know how to do this (I married mine, but that solution is probably too extreme for most people ;) select current_date,oc.node_name, oc.filespace_name, physical_mb as mbytes , stgpool_name, - date(fs.backup_start) as bkup_date from occupancy oc, filespaces fs - where oc.node_name=fs.node_name and - oc.filespace_name=fs.filespace_name and - stgpool_name='BACKUPTAPE' and- cast((current_timestamp-backup_start)days as integer)=60 - order by stgpool_name, oc.node_name, oc.filespace_name -Original Message- From: Justin Bleistein [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 12:44 PM To: [EMAIL PROTECTED] Subject: tsm select statement help Does anyone have an sql syntax/statement example on how to join two sql tables in adsm/tsm? I have two tables in that adsm schema which both contain info that the other doesn't respectively. So I want to join them to get the desired results. I'm looking at Oracle examples on the web none really good. Any assistance would be appreciated. I know I could figure it out but if someone could save me trial and error time. I would really appreciate it.. Thanks in advance. --Justin Richard Bleistein Unix/TSM Systems Administrator
Re: tsm select statement help
thanks you da man!. --Justin Richard Bleistein Unix/TSM Systems Administrator |-+ | | Prather, Wanda | | | [EMAIL PROTECTED]| | | UAPL.EDU| | | Sent by: ADSM: | | | Dist Stor| | | Manager | | | [EMAIL PROTECTED]| | | .EDU| | || | || | | 12/30/2003 01:55 | | | PM | | | Please respond to| | | ADSM: Dist Stor | | | Manager | | || |-+ ---| | | | To: [EMAIL PROTECTED] | | cc: | | Subject: Re: tsm select statement help | ---| Here ya go. This joins the occupancy table and the filespaces table to give me a list of the filespaces that haven't been backed up in over 60 days, and the SIZE they occupy on the server in my pool named BAKUPTAPE. the oc and fs are made up shorthand tags that let you distinguish between the column in one table and the column of the same name in a different table. Not intuitively obvious. Good to make friends with your local DBA, they know how to do this (I married mine, but that solution is probably too extreme for most people ;) select current_date,oc.node_name, oc.filespace_name, physical_mb as mbytes , stgpool_name, - date(fs.backup_start) as bkup_date from occupancy oc, filespaces fs - where oc.node_name=fs.node_name and - oc.filespace_name=fs.filespace_name and - stgpool_name='BACKUPTAPE' and- cast((current_timestamp-backup_start)days as integer)=60 - order by stgpool_name, oc.node_name, oc.filespace_name -Original Message- From: Justin Bleistein [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 12:44 PM To: [EMAIL PROTECTED] Subject: tsm select statement help Does anyone have an sql syntax/statement example on how to join two sql tables in adsm/tsm? I have two tables in that adsm schema which both contain info that the other doesn't respectively. So I want to join them to get the desired results. I'm looking at Oracle examples on the web none really good. Any assistance would be appreciated. I know I could figure it out but if someone could save me trial and error time. I would really appreciate it.. Thanks in advance. --Justin Richard Bleistein Unix/TSM Systems Administrator
Re: tsm select statement help
You're welcome! -Original Message- From: Justin Bleistein [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 1:46 PM To: [EMAIL PROTECTED] Subject: Re: tsm select statement help thanks you da man!. --Justin Richard Bleistein Unix/TSM Systems Administrator |-+ | | Prather, Wanda | | | [EMAIL PROTECTED]| | | UAPL.EDU| | | Sent by: ADSM: | | | Dist Stor| | | Manager | | | [EMAIL PROTECTED]| | | .EDU| | || | || | | 12/30/2003 01:55 | | | PM | | | Please respond to| | | ADSM: Dist Stor | | | Manager | | || |-+ --- | | | | To: [EMAIL PROTECTED] | | cc: | | Subject: Re: tsm select statement help | --- | Here ya go. This joins the occupancy table and the filespaces table to give me a list of the filespaces that haven't been backed up in over 60 days, and the SIZE they occupy on the server in my pool named BAKUPTAPE. the oc and fs are made up shorthand tags that let you distinguish between the column in one table and the column of the same name in a different table. Not intuitively obvious. Good to make friends with your local DBA, they know how to do this (I married mine, but that solution is probably too extreme for most people ;) select current_date,oc.node_name, oc.filespace_name, physical_mb as mbytes , stgpool_name, - date(fs.backup_start) as bkup_date from occupancy oc, filespaces fs - where oc.node_name=fs.node_name and - oc.filespace_name=fs.filespace_name and - stgpool_name='BACKUPTAPE' and- cast((current_timestamp-backup_start)days as integer)=60 - order by stgpool_name, oc.node_name, oc.filespace_name -Original Message- From: Justin Bleistein [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 12:44 PM To: [EMAIL PROTECTED] Subject: tsm select statement help Does anyone have an sql syntax/statement example on how to join two sql tables in adsm/tsm? I have two tables in that adsm schema which both contain info that the other doesn't respectively. So I want to join them to get the desired results. I'm looking at Oracle examples on the web none really good. Any assistance would be appreciated. I know I could figure it out but if someone could save me trial and error time. I would really appreciate it.. Thanks in advance. --Justin Richard Bleistein Unix/TSM Systems Administrator
Re: tsm select statement help
Hi Justin, If I understood you correctly, try something like this: select a.schedule_name, a.node_name, b.contact from associations a, nodes b where a.node_name = b.node_name order by b.contact,a.node_name Good luck Joe Crnjanski Infinity Network Solutions Inc. Phone: 416-235-0931 x26 Fax: 416-235-0265 Web: www.infinitynetwork.com -Original Message- From: Justin Bleistein [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 12:44 PM To: [EMAIL PROTECTED] Subject: tsm select statement help Does anyone have an sql syntax/statement example on how to join two sql tables in adsm/tsm? I have two tables in that adsm schema which both contain info that the other doesn't respectively. So I want to join them to get the desired results. I'm looking at Oracle examples on the web none really good. Any assistance would be appreciated. I know I could figure it out but if someone could save me trial and error time. I would really appreciate it.. Thanks in advance. --Justin Richard Bleistein Unix/TSM Systems Administrator
