Re: SQL Query find nodes associated with management class
Thanks for All your replies! I was able to come up with SQL query to display the nodes and their Management Classes Best Regards! Richard Sims wrote: On Jan 28, 2010, at 11:16 AM, Timothy Hughes wrote: Hi all, I am trying to find all nodes that are associated with a particular management class, does anyone have a SQL statement that will produce this information? A frequently asked question: see the ADSM-L archives for the many responses. Nodes are not assigned to management classes: individual file system objects are, where such binding can vary by Include spec and TSM directory processing rules. The SQL query to scan millions of files to determine this fully is infeasible. The only realistic thing to do is to sample a few representative files. Richard Sims
Re: SQL Query find nodes associated with management class
Hello Timothy,
I am using next scripts:
define script F3_Node_Backup_Copy_Groups description=" Active backup copy
groups for node"
update script F3_Node_Backup_Copy_Groups \
"select b.node_name, a.domain_name, a.class_name as Management_Class,
a.destination as Destination_Pool, \
a.verexists as MAX_Versions, a.verdeleted as Max_Deleted, a.retextra as
Days_Inactive, a.retonly Days_Last_Deleted \
from bu_copygroups a, backups b \
where a.class_name = b.class_name and a.set_name = 'ACTIVE' and b.node_name =
upper('\$1') \
group by b.node_name, a.domain_name, a.class_name, a.destination, a.verexists,
a.verdeleted, a.retextra, a.retonly"
define script F4_Node_Archive_Copy_Groups description=" Active archive
copy groups for node"
update script F4_Node_Archive_Copy_Groups \
"select b.node_name, a.domain_name, a.class_name as Management_Class,
a.destination as Destination_Pool, a.retver as Retention \
from ar_copygroups a, backups b \
where a.class_name = b.class_name and a.set_name = 'ACTIVE' and b.node_name =
upper('\$1') \
group by b.node_name, a.domain_name, a.class_name, a.destination, a.retver"
If you are ready to wait some time (sometimes huge), run
Re: SQL Query find nodes associated with management class
From: ADSM: Dist Stor Manager [ads...@vm.marist.edu] On Behalf Of Timothy Hughes [timothy.hug...@oit.state.nj.us] Sent: Thursday, January 28, 2010 7:16 PM To: ADSM-L@VM.MARIST.EDU Subject: [ADSM-L] SQL Query find nodes associated with management class Hi all, I am trying to find all nodes that are associated with a particular management class, does anyone have a SQL statement that will produce this information? Thanks Please consider the environment before printing this Email. "This email message and any attachments transmitted with it may contain confidential and proprietary information, intended only for the named recipient(s). If you have received this message in error, or if you are not the named recipient(s), please delete this email after notifying the sender immediately. BKME cannot guarantee the integrity of this communication and accepts no liability for any damage caused by this email or its attachments due to viruses, any other defects, interception or unauthorized modification. The information, views, opinions and comments of this message are those of the individual and not necessarily endorsed by BKME."
Re: SQL Query find nodes associated with management class
Right, we did some magic on the dsmc client so it can masquerade as other clients. Then, from our ART restore-testing appliance, we can run "dsmc query backup.." masquerading as each production node in turn, and do a restore test on a randomly selected file from that node. So ART can do random-sample restore testing on all 500 machines you back up. (Sorry. Marketing hat off.) But I see this question here repeatedly: "how can I list all of my backup files?" And the answer is always "Don't do that, or tread carefully" like Richard just said. But our ART tool DOES have a way to list all the backed-up files, and their sizes, and their management class.. ... for ALL the machines you back up with all your TSM servers ... without hurting your TSM server's performance. So why are people wanting to do this? Hey, you lurkers who think about doing this: will you speak up and say why, please? What are you really after, reducing wasted storage? tuning up retention policies? What? Lindsay Morris CEO, TSMworks Tel. 1-859-539-9900 lind...@tsmworks.com On Thu, Jan 28, 2010 at 11:51 AM, Huebschman, George J. < gjhuebsch...@lmus.leggmason.com> wrote: > That is true. The dsmc q backup IS much more efficient. Some server > admins don't have access to clients though. In his case he wants to > look at all of his clients. I don't know how many he has, but the time > to go into each could be considerable. You mention a way of doing it > without log on to each client. You got me there. > > Richard's point is also very important. The query should be limited to > certain objects or filespaces that you would expect to be using that MC, > or the opposite if you want to be sure that if you want to be sure that > the MC is not being used where it shouldn't. For example, if you have > one long retention for ComplianceData and another for everything else, > you might care if system files are being bound to the Outrageos_Retn MC. > > > -Original Message- > From: ADSM: Dist Stor Manager [mailto:ads...@vm.marist.edu] On Behalf Of > Lindsay Morris > Sent: Thursday, January 28, 2010 11:34 AM > To: ADSM-L@VM.MARIST.EDU > Subject: Re: [ADSM-L] SQL Query find nodes associated with management > class > > We deal with this problem by using the dsmc client, rather than the > dsmadmc client. > "dsmc query backup..." seems to be more efficient that "dsmadmc select > ... > from backups.." > > Requires some clever setup, though, so you don't have to go log on to > the client itself to run dsmc. > > Lindsay Morris > CEO, TSMworks > Tel. 1-859-539-9900 > lind...@tsmworks.com > > > On Thu, Jan 28, 2010 at 11:28 AM, Huebschman, George J. < > gjhuebsch...@lmus.leggmason.com> wrote: > > > The only way I know to get that directly is from the backups table. > > Running a select against the backups table for all nodes on a server > > is not a good thing...believe me (don't ask). > > > > If you want to do it, make the select as precise as possible and run > > it for one node at a time. > > > > select distinct node_name, filespace_name, class_name from backups > > where node_name='NODENAMEXYZ' and filespace_id=x (or > > filespace_name='whateveryerlookinfer') and type='FILE' and > > state='ACTIVE' > > > > Wanda advised me to limit such queries with index key paramaters as > > much as possible to keep the select processing as light as possible. > > > > George Huebschman > > > > -Original Message- > > From: ADSM: Dist Stor Manager [mailto:ads...@vm.marist.edu] On Behalf > > Of Timothy Hughes > > Sent: Thursday, January 28, 2010 11:16 AM > > To: ADSM-L@VM.MARIST.EDU > > Subject: [ADSM-L] SQL Query find nodes associated with management > > class > > > > Hi all, > > > > > > I am trying to find all nodes that are associated with a particular > > management class, does anyone have a SQL statement that will produce > > this information? > > > > > > Thanks > > > > IMPORTANT: E-mail sent through the Internet is not secure. Legg Mason > > > therefore recommends that you do not send any confidential or > > sensitive information to us via electronic mail, including social > > security numbers, account numbers, or personal identification numbers. > > > Delivery, and or timely delivery of Internet mail is not guaranteed. > > Legg Mason therefore recommends that you do not send time sensitive or > > > action-oriented messages to us via electronic mail. > > > > This message is intended for the addressee only and may contain > > privileged or confidential information. Unless you are the intended > > recipient, you may not use, copy or disclose to anyone any information > > > contained in this message. If you have received this message in error, > > > please notify the author by replying to this message and then kindly > > delete the message. Thank you. > > > > IMPORTANT: E-mail sent through the Internet is not secure. Legg Mason > therefore recommends that you do not send any confidential or sensitive >
Re: SQL Query find nodes associated with management class
That is true. The dsmc q backup IS much more efficient. Some server admins don't have access to clients though. In his case he wants to look at all of his clients. I don't know how many he has, but the time to go into each could be considerable. You mention a way of doing it without log on to each client. You got me there. Richard's point is also very important. The query should be limited to certain objects or filespaces that you would expect to be using that MC, or the opposite if you want to be sure that if you want to be sure that the MC is not being used where it shouldn't. For example, if you have one long retention for ComplianceData and another for everything else, you might care if system files are being bound to the Outrageos_Retn MC. -Original Message- From: ADSM: Dist Stor Manager [mailto:ads...@vm.marist.edu] On Behalf Of Lindsay Morris Sent: Thursday, January 28, 2010 11:34 AM To: ADSM-L@VM.MARIST.EDU Subject: Re: [ADSM-L] SQL Query find nodes associated with management class We deal with this problem by using the dsmc client, rather than the dsmadmc client. "dsmc query backup..." seems to be more efficient that "dsmadmc select ... from backups.." Requires some clever setup, though, so you don't have to go log on to the client itself to run dsmc. Lindsay Morris CEO, TSMworks Tel. 1-859-539-9900 lind...@tsmworks.com On Thu, Jan 28, 2010 at 11:28 AM, Huebschman, George J. < gjhuebsch...@lmus.leggmason.com> wrote: > The only way I know to get that directly is from the backups table. > Running a select against the backups table for all nodes on a server > is not a good thing...believe me (don't ask). > > If you want to do it, make the select as precise as possible and run > it for one node at a time. > > select distinct node_name, filespace_name, class_name from backups > where node_name='NODENAMEXYZ' and filespace_id=x (or > filespace_name='whateveryerlookinfer') and type='FILE' and > state='ACTIVE' > > Wanda advised me to limit such queries with index key paramaters as > much as possible to keep the select processing as light as possible. > > George Huebschman > > -Original Message- > From: ADSM: Dist Stor Manager [mailto:ads...@vm.marist.edu] On Behalf > Of Timothy Hughes > Sent: Thursday, January 28, 2010 11:16 AM > To: ADSM-L@VM.MARIST.EDU > Subject: [ADSM-L] SQL Query find nodes associated with management > class > > Hi all, > > > I am trying to find all nodes that are associated with a particular > management class, does anyone have a SQL statement that will produce > this information? > > > Thanks > > IMPORTANT: E-mail sent through the Internet is not secure. Legg Mason > therefore recommends that you do not send any confidential or > sensitive information to us via electronic mail, including social > security numbers, account numbers, or personal identification numbers. > Delivery, and or timely delivery of Internet mail is not guaranteed. > Legg Mason therefore recommends that you do not send time sensitive or > action-oriented messages to us via electronic mail. > > This message is intended for the addressee only and may contain > privileged or confidential information. Unless you are the intended > recipient, you may not use, copy or disclose to anyone any information > contained in this message. If you have received this message in error, > please notify the author by replying to this message and then kindly > delete the message. Thank you. > IMPORTANT: E-mail sent through the Internet is not secure. Legg Mason therefore recommends that you do not send any confidential or sensitive information to us via electronic mail, including social security numbers, account numbers, or personal identification numbers. Delivery, and or timely delivery of Internet mail is not guaranteed. Legg Mason therefore recommends that you do not send time sensitive or action-oriented messages to us via electronic mail. This message is intended for the addressee only and may contain privileged or confidential information. Unless you are the intended recipient, you may not use, copy or disclose to anyone any information contained in this message. If you have received this message in error, please notify the author by replying to this message and then kindly delete the message. Thank you.
Re: SQL Query find nodes associated with management class
We deal with this problem by using the dsmc client, rather than the dsmadmc client. "dsmc query backup..." seems to be more efficient that "dsmadmc select ... from backups.." Requires some clever setup, though, so you don't have to go log on to the client itself to run dsmc. Lindsay Morris CEO, TSMworks Tel. 1-859-539-9900 lind...@tsmworks.com On Thu, Jan 28, 2010 at 11:28 AM, Huebschman, George J. < gjhuebsch...@lmus.leggmason.com> wrote: > The only way I know to get that directly is from the backups table. > Running a select against the backups table for all nodes on a server is > not a good thing...believe me (don't ask). > > If you want to do it, make the select as precise as possible and run it > for one node at a time. > > select distinct node_name, filespace_name, class_name from backups where > node_name='NODENAMEXYZ' and filespace_id=x (or > filespace_name='whateveryerlookinfer') and type='FILE' and > state='ACTIVE' > > Wanda advised me to limit such queries with index key paramaters as much > as possible to keep the select processing as light as possible. > > George Huebschman > > -Original Message- > From: ADSM: Dist Stor Manager [mailto:ads...@vm.marist.edu] On Behalf Of > Timothy Hughes > Sent: Thursday, January 28, 2010 11:16 AM > To: ADSM-L@VM.MARIST.EDU > Subject: [ADSM-L] SQL Query find nodes associated with management class > > Hi all, > > > I am trying to find all nodes that are associated with a particular > management class, does anyone have a SQL statement that will produce > this information? > > > Thanks > > IMPORTANT: E-mail sent through the Internet is not secure. Legg Mason > therefore recommends that you do not send any confidential or sensitive > information to us via electronic mail, including social security numbers, > account numbers, or personal identification numbers. Delivery, and or timely > delivery of Internet mail is not guaranteed. Legg Mason therefore recommends > that you do not send time sensitive > or action-oriented messages to us via electronic mail. > > This message is intended for the addressee only and may contain privileged > or confidential information. Unless you are the intended recipient, you may > not use, copy or disclose to anyone any information contained in this > message. If you have received this message in error, please notify the > author by replying to this message and then kindly delete the message. Thank > you. >
Re: SQL Query find nodes associated with management class
The only way I know to get that directly is from the backups table. Running a select against the backups table for all nodes on a server is not a good thing...believe me (don't ask). If you want to do it, make the select as precise as possible and run it for one node at a time. select distinct node_name, filespace_name, class_name from backups where node_name='NODENAMEXYZ' and filespace_id=x (or filespace_name='whateveryerlookinfer') and type='FILE' and state='ACTIVE' Wanda advised me to limit such queries with index key paramaters as much as possible to keep the select processing as light as possible. George Huebschman -Original Message- From: ADSM: Dist Stor Manager [mailto:ads...@vm.marist.edu] On Behalf Of Timothy Hughes Sent: Thursday, January 28, 2010 11:16 AM To: ADSM-L@VM.MARIST.EDU Subject: [ADSM-L] SQL Query find nodes associated with management class Hi all, I am trying to find all nodes that are associated with a particular management class, does anyone have a SQL statement that will produce this information? Thanks IMPORTANT: E-mail sent through the Internet is not secure. Legg Mason therefore recommends that you do not send any confidential or sensitive information to us via electronic mail, including social security numbers, account numbers, or personal identification numbers. Delivery, and or timely delivery of Internet mail is not guaranteed. Legg Mason therefore recommends that you do not send time sensitive or action-oriented messages to us via electronic mail. This message is intended for the addressee only and may contain privileged or confidential information. Unless you are the intended recipient, you may not use, copy or disclose to anyone any information contained in this message. If you have received this message in error, please notify the author by replying to this message and then kindly delete the message. Thank you.
Re: SQL Query find nodes associated with management class
On Jan 28, 2010, at 11:16 AM, Timothy Hughes wrote: > Hi all, > > > I am trying to find all nodes that are associated with a particular > management class, does anyone have a SQL statement that will produce > this information? A frequently asked question: see the ADSM-L archives for the many responses. Nodes are not assigned to management classes: individual file system objects are, where such binding can vary by Include spec and TSM directory processing rules. The SQL query to scan millions of files to determine this fully is infeasible. The only realistic thing to do is to sample a few representative files. Richard Sims
