Select statement question
Folks, I have a select statement that I use to determine the amount of MB backed up during a specific time period: (e.g.) SELECT entity AS Node name,CAST(sum(bytes/1024/1024) AS decimal(8,2)) AS MB xfer FROM summary WHERE activity='BACKUP' AND start_time= '2005-04-01 06:00' AND end_time= '2005-04-07 06:00' GROUP BY entity How would I include the Policy Domain Name? Thanks! DaveZ
Re: Select statement question
Here's what I use... select nodes.domain_name,summary.activity,sum(cast(summary.bytes/1024/1024/1024 as decimal(6,2))) as GB from nodes, summary where (end_time between current_timestamp - 24 hours and current_timestamp) and (activity='BACKUP' or activity='RESTORE' or activity='ARCHIVE' or activity='RETRIEVE') and ((nodes.node_name=summary.entity)) group by domain_name,summary.activity order by activity,domain_name asc [EMAIL PROTECTED] Dave Zarnoch [EMAIL PROTECTED] Sent by: ADSM: Dist Stor Manager ADSM-L@VM.MARIST.EDU 05/04/2005 07:26 AM Please respond to ADSM: Dist Stor Manager ADSM-L@VM.MARIST.EDU To ADSM-L@VM.MARIST.EDU cc Subject Select statement question Folks, I have a select statement that I use to determine the amount of MB backed up during a specific time period: (e.g.) SELECT entity AS Node name,CAST(sum(bytes/1024/1024) AS decimal(8,2)) AS MB xfer FROM summary WHERE activity='BACKUP' AND start_time= '2005-04-01 06:00' AND end_time= '2005-04-07 06:00' GROUP BY entity How would I include the Policy Domain Name? Thanks! DaveZ
Re: Select statement question
Very nice! Just what I needed! DaveZ Curtis Stewart [EMAIL PROTECTED]To: ADSM-L@VM.MARIST.EDU AWSON.COM cc: Sent by: ADSM: Subject: Re: [ADSM-L] Select statement question Dist Stor Manager [EMAIL PROTECTED] .EDU 05/04/2005 08:34 AM Please respond to ADSM: Dist Stor Manager Here's what I use... select nodes.domain_name,summary.activity,sum(cast(summary.bytes/1024/1024/1024 as decimal(6,2))) as GB from nodes, summary where (end_time between current_timestamp - 24 hours and current_timestamp) and (activity='BACKUP' or activity='RESTORE' or activity='ARCHIVE' or activity='RETRIEVE') and ((nodes.node_name=summary.entity)) group by domain_name,summary.activity order by activity,domain_name asc [EMAIL PROTECTED] Dave Zarnoch [EMAIL PROTECTED] Sent by: ADSM: Dist Stor Manager ADSM-L@VM.MARIST.EDU 05/04/2005 07:26 AM Please respond to ADSM: Dist Stor Manager ADSM-L@VM.MARIST.EDU To ADSM-L@VM.MARIST.EDU cc Subject Select statement question Folks, I have a select statement that I use to determine the amount of MB backed up during a specific time period: (e.g.) SELECT entity AS Node name,CAST(sum(bytes/1024/1024) AS decimal(8,2)) AS MB xfer FROM summary WHERE activity='BACKUP' AND start_time= '2005-04-01 06:00' AND end_time= '2005-04-07 06:00' GROUP BY entity How would I include the Policy Domain Name? Thanks! DaveZ
Re: Select statement question
Thought I had it How would I need to adjust the macro to include information between two dates? I tried the following and got no info: select nodes.domain_name,summary.activity,sum(cast(summary.bytes/1024/1024/1024 as decimal(6,2))) as GB from nodes, summary where start_time= '2005-05-09 06:00' AND end_time= '2005-05-11 06:00' and (activity='BACKUP' or activity='RESTORE' or activity='ARCHIVE' or activity='RETRIEVE') and ((nodes.node_name=summary.entity)) group by domain_name,summary.activity order by activity,domain_name asc Thanks! DaveZ Curtis Stewart [EMAIL PROTECTED]To: ADSM-L@VM.MARIST.EDU AWSON.COM cc: Sent by: ADSM: Subject: Re: [ADSM-L] Select statement question Dist Stor Manager [EMAIL PROTECTED] .EDU 05/04/2005 08:34 AM Please respond to ADSM: Dist Stor Manager Here's what I use... select nodes.domain_name,summary.activity,sum(cast(summary.bytes/1024/1024/1024 as decimal(6,2))) as GB from nodes, summary where (end_time between current_timestamp - 24 hours and current_timestamp) and (activity='BACKUP' or activity='RESTORE' or activity='ARCHIVE' or activity='RETRIEVE') and ((nodes.node_name=summary.entity)) group by domain_name,summary.activity order by activity,domain_name asc [EMAIL PROTECTED] Dave Zarnoch [EMAIL PROTECTED] Sent by: ADSM: Dist Stor Manager ADSM-L@VM.MARIST.EDU 05/04/2005 07:26 AM Please respond to ADSM: Dist Stor Manager ADSM-L@VM.MARIST.EDU To ADSM-L@VM.MARIST.EDU cc Subject Select statement question Folks, I have a select statement that I use to determine the amount of MB backed up during a specific time period: (e.g.) SELECT entity AS Node name,CAST(sum(bytes/1024/1024) AS decimal(8,2)) AS MB xfer FROM summary WHERE activity='BACKUP' AND start_time= '2005-04-01 06:00' AND end_time= '2005-04-07 06:00' GROUP BY entity How would I include the Policy Domain Name? Thanks! DaveZ
Re: Select statement question
Thought I had it How would I need to adjust the macro to include information between two dates? Try this... select nodes.domain_name,summary.activity,sum(cast(summary.bytes/1024/1024/1024 as decimal(6,2))) as GB from nodes, summary where (end_time between current_timestamp - $1 hours and current_timestamp - $2 hours) and (activity='BACKUP' or activity='RESTORE' or activity='ARCHIVE' or activity='RETRIEVE') and ((nodes.node_name=summary.entity)) group by domain_name,summary.activity $1 is the start time in hour past... like 72 for three days $2 is the end time [EMAIL PROTECTED]
Re: Select statement question
Curtis, Thanks! I made a little change, though where (end_time between '2005-05-01' and '2005-05-04') DaveZ Curtis Stewart [EMAIL PROTECTED]To: ADSM-L@VM.MARIST.EDU AWSON.COM cc: Sent by: ADSM: Subject: Re: [ADSM-L] Select statement question Dist Stor Manager [EMAIL PROTECTED] .EDU 05/04/2005 12:10 PM Please respond to ADSM: Dist Stor Manager Thought I had it How would I need to adjust the macro to include information between two dates? Try this... select nodes.domain_name,summary.activity,sum(cast(summary.bytes/1024/1024/1024 as decimal(6,2))) as GB from nodes, summary where (end_time between current_timestamp - $1 hours and current_timestamp - $2 hours) and (activity='BACKUP' or activity='RESTORE' or activity='ARCHIVE' or activity='RETRIEVE') and ((nodes.node_name=summary.entity)) group by domain_name,summary.activity $1 is the start time in hour past... like 72 for three days $2 is the end time [EMAIL PROTECTED]
Select Statement question
Hello All! I have this finally working, but I was wondering if there is a way to shorten the width of the columns so that they can all fit on one line? And also, the customer has requested that the successful column be changed to completed, missed or failed. I didn't think this was possible unless I join 2 tables and then produce the same output. Is this a correct assumption? Thanks! ANS8000I Server command: '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 start_time=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, node_name' NODE_NAMEDATE ACTIVITY START END MEGABYTES FILES SUCCESSFUL -- -- -- - - -- HMCH1021 2005-02-09 BACKUP 19:12:14 19:22:03 572 2266YES HMCH1028 2005-02-09 BACKUP 19:08:53 19:13:37 381 2063YES HMCH1147 2005-02-09 BACKUP 19:12:13 19:42:06 384 2157YES HMCH1150 2005-02-09 BACKUP 19:10:29 19:17:45 70 329YES HMCH1160 2005-02-09 BACKUP 19:12:18 19:15:14 281 1987YES HMCH1161 2005-02-09 BACKUP 19:09:25 19:13:47 187 221YES Joni Moyer Highmark Storage Systems Work:(717)302-6603 Fax:(717)302-5974 [EMAIL PROTECTED]
Re: Select Statement question
On Feb 11, 2005, at 9:19 AM, Joni Moyer wrote: I have this finally working, but I was wondering if there is a way to shorten the width of the columns so that they can all fit on one line?... Joni - The following SQL function is most commonly used to limit the width of a column: CHARSQL function to return a string of optionally limited length, left-aligned. Syntax: CHAR(Expression[,Len]) Your users should also be aware that Xwindows managers and most Web browsers allow changing the size of displayed text, so that wider stuff can fit in a window. Richard Sims
Re: SQL Select Statement Question Please HELP!
Hi Andrew, Thank you so much for resolving my questions. Really appreciate the in depth answer you have provided me. I decided to query the TSM server to see how many rows I have stored in my actlog. Below is the output. tsm: TSM_SERVER_01select count(*) from actlog ANR2963W This SQL query may produce a very large result table, or may require a significant amount of time to compute. Do you wish to proceed? (Yes (Y)/No (N)) y Unnamed[1] --- 6,212,914 Guess I can scratch out the idea on using the TSM Reporting tools for sending alerts. =( Thanks again! -Original Message- From: Andrew Raibeck [mailto:[EMAIL PROTECTED] Sent: Sunday, July 11, 2004 8:55 PM To: [EMAIL PROTECTED] Subject: Re: SQL Select Statement Question Please HELP! If you are getting actual results (and better yet, correct results) then there is nothing wrong per se with your syntax. The tables presented by the SELECT statement do not really exist as such in the TSM server database. Rather, these tables are constructed on the fly during SELECT processing. For very large tables, the processing time can be substantial. On the other hand, the built-in QUERY commands are optimized and read directly from the actual TSM database structures. If your activity log contains a large number of records, then this is the most likely explanation for the difference in run times between SELECT and QUERY. Try running this: select count(*) from actlog How long does it take to run? What is the count (number of records in the ACTLOG table)? If even this simple SELECT statement takes a while to run and there are a lot of records, then I think the explanation I provided above is the answer. In this case, there isn't much you can do other than try to reduce the size of your activity log by reducing the length of time you keep activity log records via the SET ACTLOGRETENTION command. Reducing the retention will reduce the size of the table and that *might* improve performance. But do not reduce the retention merely to improve SELECT performance unless you truly do not require keeping the records for as long as they are being kept today. 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 07/09/2004 12:48:50: Hello Everyone, I have a quick question in reference to using select statements to query the activity log. For some reason the regular select statement would take forever to run a simple query? How does TSM query the activity log? I'm still quite a novice at selects statements so maybe I'm issuing the wrong syntax. Reason why I would like to use the select statement vs. q act is because TSM Reporting tool allows me to generate alerts by setting up notification rules which queries the server using select statements. Examples below: TSM QUERY: (Takes about a blink of an eye) TSM_SERVER_01q act msg=0986 begintime=14:30:00 endtime=15:00:00 Date/TimeMessage -- 07/09/04 14:35:21 ANR0986I Process 7544 for SPACE RECLAMATION running in the BACKGROUND processed 36047 items for a total of 4,279,628,603 bytes with a completion state of SUCCESS at 14:35:21. 07/09/04 14:42:05 ANR0986I Process 7545 for SPACE RECLAMATION running in the BACKGROUND processed 33537 items for a total of 8,644,875,214 bytes with a completion state of SUCCESS at 14:42:05. SQL QUERY: (Takes about 20 mins!) TSM_SERVER_01select date_time,message from actlog where date_time between '07/09/04 14:30:00' and ' 07/09/04 15:00:00' and msgno=0986 ANR2963W This SQL query may produce a very large result table, or may require a significant amount of time to compute. Do you wish to proceed? (Yes (Y)/No (N)) y DATE_TIME MESSAGE -- -- 2004-07-09 ANR0986I Process 14:35:21.00 7544 for SPACE RECLAMATION running in the BACKGROUND processed 36047 items for a total of 4,279,628,603 bytes with a completion state of SUCCESS at 14:35:21. 2004-07-09 ANR0986I Process 14:42:05.00 7545 for SPACE RECLAMATION running in the BACKGROUND
Re: SQL Select Statement Question Please HELP!
Hi Calvin, Yes, it doesn't surprise me that activity log rows numbering in the millions will take several minutes to process (though 20 minutes does seem like a long time, but that can also depend on other server activity). Without knowing more about your intentions and time constraints, I'm not convinced that you necessarily need to scrap the idea; though I agree that for real-time alerts this may not be feasible. 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 07/12/2004 09:28:51: Hi Andrew, Thank you so much for resolving my questions. Really appreciate the in depth answer you have provided me. I decided to query the TSM server to see how many rows I have stored in my actlog. Below is the output. tsm: TSM_SERVER_01select count(*) from actlog ANR2963W This SQL query may produce a very large result table, or may require a significant amount of time to compute. Do you wish to proceed? (Yes (Y)/No (N)) y Unnamed[1] --- 6,212,914 Guess I can scratch out the idea on using the TSM Reporting tools for sending alerts. =( Thanks again!
Re: SQL Select Statement Question Please HELP!
If you are getting actual results (and better yet, correct results) then there is nothing wrong per se with your syntax. The tables presented by the SELECT statement do not really exist as such in the TSM server database. Rather, these tables are constructed on the fly during SELECT processing. For very large tables, the processing time can be substantial. On the other hand, the built-in QUERY commands are optimized and read directly from the actual TSM database structures. If your activity log contains a large number of records, then this is the most likely explanation for the difference in run times between SELECT and QUERY. Try running this: select count(*) from actlog How long does it take to run? What is the count (number of records in the ACTLOG table)? If even this simple SELECT statement takes a while to run and there are a lot of records, then I think the explanation I provided above is the answer. In this case, there isn't much you can do other than try to reduce the size of your activity log by reducing the length of time you keep activity log records via the SET ACTLOGRETENTION command. Reducing the retention will reduce the size of the table and that *might* improve performance. But do not reduce the retention merely to improve SELECT performance unless you truly do not require keeping the records for as long as they are being kept today. 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 07/09/2004 12:48:50: Hello Everyone, I have a quick question in reference to using select statements to query the activity log. For some reason the regular select statement would take forever to run a simple query? How does TSM query the activity log? I'm still quite a novice at selects statements so maybe I'm issuing the wrong syntax. Reason why I would like to use the select statement vs. q act is because TSM Reporting tool allows me to generate alerts by setting up notification rules which queries the server using select statements. Examples below: TSM QUERY: (Takes about a blink of an eye) TSM_SERVER_01q act msg=0986 begintime=14:30:00 endtime=15:00:00 Date/TimeMessage -- 07/09/04 14:35:21 ANR0986I Process 7544 for SPACE RECLAMATION running in the BACKGROUND processed 36047 items for a total of 4,279,628,603 bytes with a completion state of SUCCESS at 14:35:21. 07/09/04 14:42:05 ANR0986I Process 7545 for SPACE RECLAMATION running in the BACKGROUND processed 33537 items for a total of 8,644,875,214 bytes with a completion state of SUCCESS at 14:42:05. SQL QUERY: (Takes about 20 mins!) TSM_SERVER_01select date_time,message from actlog where date_time between '07/09/04 14:30:00' and ' 07/09/04 15:00:00' and msgno=0986 ANR2963W This SQL query may produce a very large result table, or may require a significant amount of time to compute. Do you wish to proceed? (Yes (Y)/No (N)) y DATE_TIME MESSAGE -- -- 2004-07-09 ANR0986I Process 14:35:21.00 7544 for SPACE RECLAMATION running in the BACKGROUND processed 36047 items for a total of 4,279,628,603 bytes with a completion state of SUCCESS at 14:35:21. 2004-07-09 ANR0986I Process 14:42:05.00 7545 for SPACE RECLAMATION running in the BACKGROUND processed 33537 items for a total of 8,644,875,214 bytes with a completion state of SUCCESS at 14:42:05. My DB Maximum Reduction space is well over 3 gigs so I know that's not an issue. Any suggestions and comments would be deeply appreciated! Thanks in advance! Calvin --- 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
SQL Select Statement Question Please HELP!
Hello Everyone, I have a quick question in reference to using select statements to query the activity log. For some reason the regular select statement would take forever to run a simple query? How does TSM query the activity log? I'm still quite a novice at selects statements so maybe I'm issuing the wrong syntax. Reason why I would like to use the select statement vs. q act is because TSM Reporting tool allows me to generate alerts by setting up notification rules which queries the server using select statements. Examples below: TSM QUERY: (Takes about a blink of an eye) TSM_SERVER_01q act msg=0986 begintime=14:30:00 endtime=15:00:00 Date/TimeMessage -- 07/09/04 14:35:21 ANR0986I Process 7544 for SPACE RECLAMATION running in the BACKGROUND processed 36047 items for a total of 4,279,628,603 bytes with a completion state of SUCCESS at 14:35:21. 07/09/04 14:42:05 ANR0986I Process 7545 for SPACE RECLAMATION running in the BACKGROUND processed 33537 items for a total of 8,644,875,214 bytes with a completion state of SUCCESS at 14:42:05. SQL QUERY: (Takes about 20 mins!) TSM_SERVER_01select date_time,message from actlog where date_time between '07/09/04 14:30:00' and ' 07/09/04 15:00:00' and msgno=0986 ANR2963W This SQL query may produce a very large result table, or may require a significant amount of time to compute. Do you wish to proceed? (Yes (Y)/No (N)) y DATE_TIME MESSAGE -- -- 2004-07-09 ANR0986I Process 14:35:21.00 7544 for SPACE RECLAMATION running in the BACKGROUND processed 36047 items for a total of 4,279,628,603 bytes with a completion state of SUCCESS at 14:35:21. 2004-07-09 ANR0986I Process 14:42:05.00 7545 for SPACE RECLAMATION running in the BACKGROUND processed 33537 items for a total of 8,644,875,214 bytes with a completion state of SUCCESS at 14:42:05. My DB Maximum Reduction space is well over 3 gigs so I know that's not an issue. Any suggestions and comments would be deeply appreciated! Thanks in advance! Calvin --- 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 question...
The column names you refer to in your select list need to be included in the GROUP BY clause. 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] (change eye to i to reply) The only dumb question is the one that goes unasked. The command line is your friend. Good enough is the enemy of excellence. Coats, Jack [EMAIL PROTECTED] Sent by: ADSM: Dist Stor Manager [EMAIL PROTECTED] 02/25/2003 16:03 Please respond to ADSM: Dist Stor Manager To: [EMAIL PROTECTED] cc: Subject:Select statement question... The first of these queries works. What is wrong with the second one? I've been scratching my balding head and getting nothing but an itching scalp. Thanks for you help! ... Jack select platform_name as OS, - client_os_level as OS_VER, - node_name as Node, - cast(cast(client_version as char(2)) || '.' - || cast(client_release as char(2)) || '.' - || cast(client_level as char(2)) || '.' - || cast(client_sublevel as char(2)) - as char(15)) as Client - from nodes - order by platform_name, Client, Node select platform_name as OS, - client_os_level as OS_VER, - cast(cast(client_version as char(2)) || '.' - || cast(client_release as char(2)) || '.' - || cast(client_level as char(2)) || '.' - || cast(client_sublevel as char(2)) - as char(15)) as Client, - count(*) - from nodes - group by platform_name, - client_os_level - order by OS, OS_VER, Client
Re: [summary] Select statement question...
Thanks, I got the answer. Paul hit it on the head. My summary of clients is given by the SQL query: select platform_name as OS, - client_os_level as OS_VER, - cast( cast(client_version as char(2)) || '.' - || cast(client_release as char(2)) || '.' - || cast(client_level as char(2)) || '.' - || cast(client_sublevel as char(2)) - as char(15)) as Client, - count(*) as Count - from nodes - group by platform_name, - client_os_level, client_version, client_release, client_level, client_sublevel - order by OS, OS_VER, Client My main issue was trying to put the Client aggergate in the order by clause. Instead, I had to put each of its components in the group by clause, and then order by clause can contain the name of the casted column (Client) -Original Message- From: Paul Ripke [SMTP:[EMAIL PROTECTED] [Coats, Jack] [sip]
Select statement question...
The first of these queries works. What is wrong with the second one? I've been scratching my balding head and getting nothing but an itching scalp. Thanks for you help! ... Jack select platform_name as OS, - client_os_level as OS_VER, - node_name as Node, - cast(cast(client_version as char(2)) || '.' - || cast(client_release as char(2)) || '.' - || cast(client_level as char(2)) || '.' - || cast(client_sublevel as char(2)) - as char(15)) as Client - from nodes - order by platform_name, Client, Node select platform_name as OS, - client_os_level as OS_VER, - cast(cast(client_version as char(2)) || '.' - || cast(client_release as char(2)) || '.' - || cast(client_level as char(2)) || '.' - || cast(client_sublevel as char(2)) - as char(15)) as Client, - count(*) - from nodes - group by platform_name, - client_os_level - order by OS, OS_VER, Client
Re: Select statement question
Henk, in most cases your select will product good enough results. But if a shop have many sessions with small ammounts of data to backup (RDBMS logs or workstations for example) your query results might become inaccurate. Instead of select sum(cast(bytes/1024/1024/1024 as decimal)) use select cast(sum(bytes)/1024/1024/1024 as decimal). Zlatko Krastev IT Consultant 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 question Wayne, Can anyone help me with a select query that will tell me how much data I've backed up in the last 24 hours? select sum(cast(bytes/1024/1024/1024 as decimal(6,3))) Total GB Backup - from summary where start_time=current_timestamp - 1 day and activity='BACKUP' Cheers, Henk ten Have
Re: Select statement question
Wayne, Can anyone help me with a select query that will tell me how much data I've backed up in the last 24 hours? select sum(cast(bytes/1024/1024/1024 as decimal(6,3))) Total GB Backup - from summary where start_time=current_timestamp - 1 day and activity='BACKUP' Cheers, Henk ten Have
