Re: SQL query replnodes

[Loon, Eric van (ITOP DI) - KLM]

Hi Marc,

I tested your suggestion: a remove replnode doesn't remove the node from the 
REPLICATIONVIEW table. Also, the REPLICATIONVIEW table only contains node which 
are in replmode=send. I'm trying to create a script which also removes the 
replmode=receive nodes.
I guess there's no way to check if a replnode exists or not, so my script will 
have to execute the  command, no matter what.

Kind regards,
Eric van Loon
Core Infra

-Original Message-
From: ADSM: Dist Stor Manager  On Behalf Of Marc Lanteigne
Sent: donderdag 22 september 2022 17:07
To: ADSM-L@VM.MARIST.EDU
Subject: Re: SQL query replnodes

Bonjour Eric,

If you find the node in the REPLICATIONVIEW table, it means it has replicated 
at least once, which means it would also show up in Q REPLNODE.

Now, what I can't test at the moment is that once you do a REMOVE REPLNODE, 
does it clear the entries in the REPLICATIONVIEW right away or does it keep the 
history. If it keeps the history, then it would not be useful for you.

-
Thanks,
Marc...

Marc Lanteigne
Spectrum Protect Specialist AVP / SRT
IBM Systems, Spectrum Protect / Plus
+1-506-460-9074 
marclantei...@ca.ibm.com 
Office Hours:  Monday to Friday, 7:00 to 15:30 Eastern
 
IBM

-Original Message-
From: ADSM: Dist Stor Manager  On Behalf Of Loon, Eric 
van (ITOP DI) - KLM
Sent: Thursday, September 22, 2022 09:47 AM
To: ADSM-L@VM.MARIST.EDU
Subject: [EXTERNAL] Re: [ADSM-L] SQL query replnodes

Hi Dmitry,

That still doesn't show me if replnodes exist for a specific node...

Kind regards,
Eric van Loon
Air France/KLM Core Infra

-Original Message-
From: ADSM: Dist Stor Manager  On Behalf Of Dmitry Dukhov
Sent: donderdag 22 september 2022 13:20
To: ADSM-L@VM.MARIST.EDU
Subject: Re: SQL query replnodes

Hi all
Better to use

Select * from nodes where repl_state='NONE'
Repl_state can have three status: none, enabled, disabled

I think it's more correct
Dmitry Dukhov

https://www.tsmexplorer.copm  


> On 21 Sep 2022, at 19:28, Rick Adamson  wrote:
> 
> Eric,
> Are you looking for something along these lines.?
> 
> select node_name,repl_state,repl_mode from nodes where repl_state='ENABLED' 
> order by node_name
> 
> 
> -Rick Adamson
> 
> -Original Message-
> From: ADSM: Dist Stor Manager  On Behalf Of Oscar 
> Kolsteren
> Sent: Wednesday, September 21, 2022 11:04 AM
> To: ADSM-L@VM.MARIST.EDU
> Subject: Re: [ADSM-L] SQL query replnodes
> 
> * This email originated outside of the organization. Use caution when opening 
> attachments or clicking links. *
> 
> --
> Hey Eric,
> 
> Have a look in our data collector which is used to create our FREE IBM CSA 
> Health Check report.
> 
> If you don't have a copy left, let me know and I'll share one. In the script 
> are all the select statements we use.
> 
> Or just run a fresh collection and upload for me to create a new report.
> 
> Best Regards,
> 
> Oscar Kolsteren
> Lead Storage Consultant, IBM Client Storage Assessment Team (UKI)
> 
> -Original Message-
> From: ADSM: Dist Stor Manager  On Behalf Of Loon, Eric 
> van (ITOP DI) - KLM
> Sent: 21 September 2022 09:16
> To: ADSM-L@VM.MARIST.EDU
> Subject: [EXTERNAL] [ADSM-L] SQL query replnodes
> 
> Hi everybody,
> 
> I'm trying to find a way to list replnode(s) through a SQL query, does 
> anybody know where this information can be found? So something like QUERY 
> RPELNODE , but than through a SQL query.
> Thanks for any help in advance!
> 
> Kind regards,
> Eric van Loon
> Air France/KLM Core Infra
> 
> For information, services and offers, please visit our web site: 
> http://www.klm.com. This e-mail and any attachment may contain 
> confidential and privileged material intended for the addressee only. If you 
> are not the addressee, you are notified that no part of the e-mail or any 
> attachment may be disclosed, copied or distributed, and that any other action 
> related to this e-mail or attachment is strictly prohibited, and may be 
> unlawful. If you have received this e-mail by error, please notify the sender 
> immediately by return e-mail, and delete this message.
> 
> Koninklijke Luchtvaart Maatschappij NV (KLM), its subsidiaries and/or its 
> employees shall not be liable for the incorrect or incomplete transmission of 
> this e-mail or any attachments, nor responsible for any delay in receipt.
> Koninklijke Luchtvaart Maatschappij N.V. (also known as KLM Royal Dutch 
> Airlines) is registered in Amstelveen, The Netherlands, with registered 
> number 33014286
> 
> 
> Unless otherwise stated above:
> 
> IBM United Kingdom Limited
> Reg

Re: SQL query replnodes

[Loon, Eric van (ITOP DI) - KLM]

Hi Rick,

I'm using the command in a Spectrum Protect server script, so return codes are 
the only option...

Kind regards,
Eric van Loon
Core Infra

-Original Message-
From: ADSM: Dist Stor Manager  On Behalf Of Rick Saylor
Sent: maandag 26 september 2022 14:45
To: ADSM-L@VM.MARIST.EDU
Subject: Re: SQL query replnodes

Hi Eric,

I had the same issue with the q replnode always returning a RC 0. I worked 
around it by assuming the node was replicated UNLESS either ANR2726E or 
ANR1701E was found in the q replnode output. I know it is not the most elegant 
approach but it worked for me. As always you'll need to verify this will work 
in your environment.

Rick Saylor
Austin Community College

On 9/26/22 06:15, Loon, Eric van (ITOP DI) - KLM wrote:
> Hi Andy,
>
> The problem with using the q replnode command is that it always returns RC 0, 
> even if a replnode does not exist. I'm trying to create a script which should 
> only execute a remove replnode when one exists, but if(rc_notfound), nor 
> if(error) is working...
> Thanks for your help!
>
> Kind regards,
> Eric van Loon
> Core Infra
>
> -Original Message-
> From: ADSM: Dist Stor Manager  On Behalf Of 
> Andrew Raibeck
> Sent: donderdag 22 september 2022 17:09
> To: ADSM-L@VM.MARIST.EDU
> Subject: Re: SQL query replnodes
>
> Hi Eric,
>
> I do not think there is any trivial way to use SELECT, as I suspect the 
> output is computed rather than merely copied from an internal table. Is there 
> some special reason why the QUERY REPLNODE command is inadequate? If you want 
> a CSV format, you could use the -DATAONLY=YES option and either the 
> -COMMADELIMITED or -TABDELIMITED options.
>
> Example:
>
> dsmadmc -id=youradminid -pa=topsecret -tabdelimited -dataonly=yes 
> -outfile=qreplnode.csv "query replnode *"
>
> Regards,
>
> Andy
>
> Andrew Raibeck
> IBM Spectrum Protect Level 3
> IBM Systems, Storage
> stor...@us.ibm.com
>
> IBM
>
> -Original Message-
> From: ADSM: Dist Stor Manager  On Behalf Of 
> Griggs, James F. (JMD) (CTR)
> Sent: Thursday, 22 September, 2022 11:00
> To: ADSM-L@VM.MARIST.EDU
> Subject: [EXTERNAL] Re: SQL query replnodes
>
> Seems to me you could use the following fields from the "NODES" table:
>
> REPL_STATE
> REPL_MODE
>
> Repl_state indicates if replicate is "Enabled" for a node Repl_mode indicates 
> send or receive
>Where "Send" is the source node
>And where "Receive" is a destination replication node
>
> Should both the repl_state and repl_node = "None" it is not being 
> replicated
>
>
>
> -Original Message-
> From: ADSM: Dist Stor Manager  On Behalf Of 
> Loon, Eric van (ITOP DI) - KLM
> Sent: Thursday, September 22, 2022 8:47 AM
> To: ADSM-L@VM.MARIST.EDU
> Subject: [EXTERNAL] Re: [ADSM-L] SQL query replnodes
>
> Hi Dmitry,
>
> That still doesn't show me if replnodes exist for a specific node...
>
> Kind regards,
> Eric van Loon
> Air France/KLM Core Infra
>
> -Original Message-
> From: ADSM: Dist Stor Manager  On Behalf Of 
> Dmitry Dukhov
> Sent: donderdag 22 september 2022 13:20
> To: ADSM-L@VM.MARIST.EDU
> Subject: Re: SQL query replnodes
>
> Hi all
> Better to use
>
> Select * from nodes where repl_state='NONE'
> Repl_state can have three status: none, enabled, disabled
>
> I think it's more correct
> Dmitry Dukhov
>
> https://secure-web.cisco.com/1FVp1eRVN3hmrsXBijThbYxYig_Am3-GHBF3mjMh8
> hqKP6S3yoYkvUfae1_-jOy0Sxo5BwNk-ou-jEi_ZriYGh9qdfx6xpSjF51t135bX0HU-h1
> GsFwtqSsflfrNsiLl66cZ36slTnOuKXW9FlbV_9yxA-AZgypRfb_k92r7TNh5gMZ0CH5C-
> -mX3BQ4sTVjDPSyH1yyi65ZWoOpwAvQdc9WVYX2P4GVlnfL6J5BT4poAWcf-DcOi7qoNKJ
> hAiBcU5IcuXscl4C7Z9U6ma_FV-lqvX1Cxbf_wzEURDWDPtbhhGq-l_18U1e61r-vxuzjU
> cbJ-9Ga3xn5ouJdmzumqyNSalY-vlbJD4Zl1rAhfu6s/https%3A%2F%2Fprotect2.fir
> eeye.com%2Fv1%2Furl%3Fk%3D948d3f5d-cb1607ad-948a1bb8-ac1f6b01744c-e405
> eb27701fe333%26q%3D1%26e%3Df4eb6721-455e-4909-a32e-bf2a05bc2aca%26u%3D
> https%253A%252F%252Fwww.tsmexplorer.copm%252F
>
>
>> On 21 Sep 2022, at 19:28, Rick Adamson  wrote:
>>
>> Eric,
>> Are you looking for something along these lines.?
>>
>> select node_name,repl_state,repl_mode from nodes where 
>> repl_state='ENABLED' order by node_name
>>
>>
>> -Rick Adamson
>>
>> -Original Message-
>> From: ADSM: Dist Stor Manager  On Behalf Of 
>> Oscar Kolsteren
>> Sent: Wednesday, September 21, 2022 11:04 AM
>> To: ADSM-L@VM.MARIST.EDU
>> Subject: Re: [ADSM-L] SQL query replnodes
>>
>> * This email originated outside of the organization. Use caution when 
>> o

Re: SQL query replnodes

[Rick Saylor]

Hi Eric,

I had the same issue with the q replnode always returning a RC 0. I 
worked around it by assuming the node was replicated UNLESS either 
ANR2726E or ANR1701E was found in the q replnode output. I know it is 
not the most elegant approach but it worked for me. As always you'll 
need to verify this will work in your environment.


Rick Saylor
Austin Community College

On 9/26/22 06:15, Loon, Eric van (ITOP DI) - KLM wrote:

Hi Andy,

The problem with using the q replnode command is that it always returns RC 0, 
even if a replnode does not exist. I'm trying to create a script which should 
only execute a remove replnode when one exists, but if(rc_notfound), nor 
if(error) is working...
Thanks for your help!

Kind regards,
Eric van Loon
Core Infra

-Original Message-
From: ADSM: Dist Stor Manager  On Behalf Of Andrew Raibeck
Sent: donderdag 22 september 2022 17:09
To: ADSM-L@VM.MARIST.EDU
Subject: Re: SQL query replnodes

Hi Eric,

I do not think there is any trivial way to use SELECT, as I suspect the output 
is computed rather than merely copied from an internal table. Is there some 
special reason why the QUERY REPLNODE command is inadequate? If you want a CSV 
format, you could use the -DATAONLY=YES option and either the -COMMADELIMITED 
or -TABDELIMITED options.

Example:

dsmadmc -id=youradminid -pa=topsecret -tabdelimited -dataonly=yes -outfile=qreplnode.csv 
"query replnode *"

Regards,

Andy

Andrew Raibeck
IBM Spectrum Protect Level 3
IBM Systems, Storage
stor...@us.ibm.com

IBM

-Original Message-
From: ADSM: Dist Stor Manager  On Behalf Of Griggs, James 
F. (JMD) (CTR)
Sent: Thursday, 22 September, 2022 11:00
To: ADSM-L@VM.MARIST.EDU
Subject: [EXTERNAL] Re: SQL query replnodes

Seems to me you could use the following fields from the "NODES" table:

REPL_STATE
REPL_MODE

Repl_state indicates if replicate is "Enabled" for a node Repl_mode indicates 
send or receive
   Where "Send" is the source node
   And where "Receive" is a destination replication node

Should both the repl_state and repl_node = "None" it is not being replicated



-Original Message-
From: ADSM: Dist Stor Manager  On Behalf Of Loon, Eric 
van (ITOP DI) - KLM
Sent: Thursday, September 22, 2022 8:47 AM
To: ADSM-L@VM.MARIST.EDU
Subject: [EXTERNAL] Re: [ADSM-L] SQL query replnodes

Hi Dmitry,

That still doesn't show me if replnodes exist for a specific node...

Kind regards,
Eric van Loon
Air France/KLM Core Infra

-Original Message-
From: ADSM: Dist Stor Manager  On Behalf Of Dmitry Dukhov
Sent: donderdag 22 september 2022 13:20
To: ADSM-L@VM.MARIST.EDU
Subject: Re: SQL query replnodes

Hi all
Better to use

Select * from nodes where repl_state='NONE'
Repl_state can have three status: none, enabled, disabled

I think it's more correct
Dmitry Dukhov

https://secure-web.cisco.com/1FVp1eRVN3hmrsXBijThbYxYig_Am3-GHBF3mjMh8hqKP6S3yoYkvUfae1_-jOy0Sxo5BwNk-ou-jEi_ZriYGh9qdfx6xpSjF51t135bX0HU-h1GsFwtqSsflfrNsiLl66cZ36slTnOuKXW9FlbV_9yxA-AZgypRfb_k92r7TNh5gMZ0CH5C--mX3BQ4sTVjDPSyH1yyi65ZWoOpwAvQdc9WVYX2P4GVlnfL6J5BT4poAWcf-DcOi7qoNKJhAiBcU5IcuXscl4C7Z9U6ma_FV-lqvX1Cxbf_wzEURDWDPtbhhGq-l_18U1e61r-vxuzjUcbJ-9Ga3xn5ouJdmzumqyNSalY-vlbJD4Zl1rAhfu6s/https%3A%2F%2Fprotect2.fireeye.com%2Fv1%2Furl%3Fk%3D948d3f5d-cb1607ad-948a1bb8-ac1f6b01744c-e405eb27701fe333%26q%3D1%26e%3Df4eb6721-455e-4909-a32e-bf2a05bc2aca%26u%3Dhttps%253A%252F%252Fwww.tsmexplorer.copm%252F



On 21 Sep 2022, at 19:28, Rick Adamson  wrote:

Eric,
Are you looking for something along these lines.?

select node_name,repl_state,repl_mode from nodes where
repl_state='ENABLED' order by node_name


-Rick Adamson

-Original Message-
From: ADSM: Dist Stor Manager  On Behalf Of
Oscar Kolsteren
Sent: Wednesday, September 21, 2022 11:04 AM
To: ADSM-L@VM.MARIST.EDU
Subject: Re: [ADSM-L] SQL query replnodes

* This email originated outside of the organization. Use caution when
opening attachments or clicking links. *

--
Hey Eric,

Have a look in our data collector which is used to create our FREE IBM CSA 
Health Check report.

If you don't have a copy left, let me know and I'll share one. In the script 
are all the select statements we use.

Or just run a fresh collection and upload for me to create a new report.

Best Regards,

Oscar Kolsteren
Lead Storage Consultant, IBM Client Storage Assessment Team (UKI)

-Original Message-
From: ADSM: Dist Stor Manager  On Behalf Of
Loon, Eric van (ITOP DI) - KLM
Sent: 21 September 2022 09:16
To: ADSM-L@VM.MARIST.EDU
Subject: [EXTERNAL] [ADSM-L] SQL query replnodes

Hi everybody,

I'm trying to find a way to list replnode(s) through a SQL query, does anybody know 
where this information can be found? So something like QUERY RPELNODE 
, but than through a SQL query.
Thanks for any help in advance!

Kind regards,
Eric van Loon
Air France/KLM Core Infra

Re: SQL query replnodes

[Loon, Eric van (ITOP DI) - KLM]

Hi Andy,

The problem with using the q replnode command is that it always returns RC 0, 
even if a replnode does not exist. I'm trying to create a script which should 
only execute a remove replnode when one exists, but if(rc_notfound), nor 
if(error) is working...
Thanks for your help!

Kind regards,
Eric van Loon
Core Infra

-Original Message-
From: ADSM: Dist Stor Manager  On Behalf Of Andrew Raibeck
Sent: donderdag 22 september 2022 17:09
To: ADSM-L@VM.MARIST.EDU
Subject: Re: SQL query replnodes

Hi Eric,

I do not think there is any trivial way to use SELECT, as I suspect the output 
is computed rather than merely copied from an internal table. Is there some 
special reason why the QUERY REPLNODE command is inadequate? If you want a CSV 
format, you could use the -DATAONLY=YES option and either the -COMMADELIMITED 
or -TABDELIMITED options.

Example:

dsmadmc -id=youradminid -pa=topsecret -tabdelimited -dataonly=yes 
-outfile=qreplnode.csv "query replnode *"

Regards,

Andy

Andrew Raibeck
IBM Spectrum Protect Level 3
IBM Systems, Storage
stor...@us.ibm.com

IBM

-Original Message-
From: ADSM: Dist Stor Manager  On Behalf Of Griggs, James 
F. (JMD) (CTR)
Sent: Thursday, 22 September, 2022 11:00
To: ADSM-L@VM.MARIST.EDU
Subject: [EXTERNAL] Re: SQL query replnodes

Seems to me you could use the following fields from the "NODES" table:

REPL_STATE
REPL_MODE

Repl_state indicates if replicate is "Enabled" for a node Repl_mode indicates 
send or receive
  Where "Send" is the source node
  And where "Receive" is a destination replication node

Should both the repl_state and repl_node = "None" it is not being replicated



-Original Message-
From: ADSM: Dist Stor Manager  On Behalf Of Loon, Eric 
van (ITOP DI) - KLM
Sent: Thursday, September 22, 2022 8:47 AM
To: ADSM-L@VM.MARIST.EDU
Subject: [EXTERNAL] Re: [ADSM-L] SQL query replnodes

Hi Dmitry,

That still doesn't show me if replnodes exist for a specific node...

Kind regards,
Eric van Loon
Air France/KLM Core Infra

-Original Message-
From: ADSM: Dist Stor Manager  On Behalf Of Dmitry Dukhov
Sent: donderdag 22 september 2022 13:20
To: ADSM-L@VM.MARIST.EDU
Subject: Re: SQL query replnodes

Hi all
Better to use

Select * from nodes where repl_state='NONE'
Repl_state can have three status: none, enabled, disabled

I think it's more correct
Dmitry Dukhov

https://protect2.fireeye.com/v1/url?k=948d3f5d-cb1607ad-948a1bb8-ac1f6b01744c-e405eb27701fe333=1=f4eb6721-455e-4909-a32e-bf2a05bc2aca=https%3A%2F%2Fwww.tsmexplorer.copm%2F
  


> On 21 Sep 2022, at 19:28, Rick Adamson  wrote:
> 
> Eric,
> Are you looking for something along these lines.?
> 
> select node_name,repl_state,repl_mode from nodes where 
> repl_state='ENABLED' order by node_name
> 
> 
> -Rick Adamson
> 
> -Original Message-
> From: ADSM: Dist Stor Manager  On Behalf Of 
> Oscar Kolsteren
> Sent: Wednesday, September 21, 2022 11:04 AM
> To: ADSM-L@VM.MARIST.EDU
> Subject: Re: [ADSM-L] SQL query replnodes
> 
> * This email originated outside of the organization. Use caution when 
> opening attachments or clicking links. *
> 
> --
> Hey Eric,
> 
> Have a look in our data collector which is used to create our FREE IBM CSA 
> Health Check report.
> 
> If you don't have a copy left, let me know and I'll share one. In the script 
> are all the select statements we use.
> 
> Or just run a fresh collection and upload for me to create a new report.
> 
> Best Regards,
> 
> Oscar Kolsteren
> Lead Storage Consultant, IBM Client Storage Assessment Team (UKI)
> 
> -Original Message-----
> From: ADSM: Dist Stor Manager  On Behalf Of 
> Loon, Eric van (ITOP DI) - KLM
> Sent: 21 September 2022 09:16
> To: ADSM-L@VM.MARIST.EDU
> Subject: [EXTERNAL] [ADSM-L] SQL query replnodes
> 
> Hi everybody,
> 
> I'm trying to find a way to list replnode(s) through a SQL query, does 
> anybody know where this information can be found? So something like QUERY 
> RPELNODE , but than through a SQL query.
> Thanks for any help in advance!
> 
> Kind regards,
> Eric van Loon
> Air France/KLM Core Infra
> 
> For information, services and offers, please visit our web site: 
> http://www.klm.com. This e-mail and any attachment may contain 
> confidential and privileged material intended for the addressee only. If you 
> are not the addressee, you are notified that no part of the e-mail or any 
> attachment may be disclosed, copied or distributed, and that any other action 
> related to this e-mail or attachment is strictly prohibited, and may be 
> unlawful. If you have received this e-mail by error, please notify the 

Re: SQL query replnodes

[J. Pohlmann]

Hi Eric. This is what I use in an OC report to report daily on nodes that
have NOT replicated for a couple of days. My concern is finding out if nodes
have been unsuccessful in replicating. The lines can just be put into the OC
custom report panel as title and select statement ( two pairs):


List of Nodes with filespaces NOT replicated within two days


select unique(filespaces.node_name)  from filespaces, nodes  where cast
(day(current_timestamp-last_repl_comp) as decimal) >=  2 and
nodes.repl_state = 'ENABLED' and  filespaces.node_name = nodes.node_name



List of Nodes, Filespaces, and last replication completion

select filespaces.node_name, filespaces.filespace_name,
filespaces.last_repl_comp  from filespaces, nodes where cast
(day(current_timestamp-last_repl_comp) as decimal) >=  2 and
nodes.repl_state = 'ENABLED' and filespaces.node_name = nodes.node_name
order by filespaces.node_name, filespaces.filespace_name

Hope this helps.
Regards,
Joerg Pohlmann




-Original Message-
From: ADSM: Dist Stor Manager  On Behalf Of Loon, Eric
van (ITOP DI) - KLM
Sent: Thursday, September 22, 2022 05:47
To: ADSM-L@VM.MARIST.EDU
Subject: Re: [ADSM-L] SQL query replnodes

Hi Dmitry,

That still doesn't show me if replnodes exist for a specific node...

Kind regards,
Eric van Loon
Air France/KLM Core Infra

-Original Message-
From: ADSM: Dist Stor Manager  On Behalf Of Dmitry
Dukhov
Sent: donderdag 22 september 2022 13:20
To: ADSM-L@VM.MARIST.EDU
Subject: Re: SQL query replnodes

Hi all
Better to use

Select * from nodes where repl_state='NONE'
Repl_state can have three status: none, enabled, disabled

I think it's more correct
Dmitry Dukhov

https://www.tsmexplorer.copm


> On 21 Sep 2022, at 19:28, Rick Adamson  wrote:
>
> Eric,
> Are you looking for something along these lines.?
>
> select node_name,repl_state,repl_mode from nodes where
repl_state='ENABLED' order by node_name
>
>
> -Rick Adamson
>
> -Original Message-
> From: ADSM: Dist Stor Manager  On Behalf Of Oscar
Kolsteren
> Sent: Wednesday, September 21, 2022 11:04 AM
> To: ADSM-L@VM.MARIST.EDU
> Subject: Re: [ADSM-L] SQL query replnodes
>
> * This email originated outside of the organization. Use caution when
opening attachments or clicking links. *
>
> --
> Hey Eric,
>
> Have a look in our data collector which is used to create our FREE IBM CSA
Health Check report.
>
> If you don't have a copy left, let me know and I'll share one. In the
script are all the select statements we use.
>
> Or just run a fresh collection and upload for me to create a new report.
>
> Best Regards,
>
> Oscar Kolsteren
> Lead Storage Consultant, IBM Client Storage Assessment Team (UKI)
>
> -Original Message-
> From: ADSM: Dist Stor Manager  On Behalf Of Loon,
Eric van (ITOP DI) - KLM
> Sent: 21 September 2022 09:16
> To: ADSM-L@VM.MARIST.EDU
> Subject: [EXTERNAL] [ADSM-L] SQL query replnodes
>
> Hi everybody,
>
> I'm trying to find a way to list replnode(s) through a SQL query, does
anybody know where this information can be found? So something like QUERY
RPELNODE , but than through a SQL query.
> Thanks for any help in advance!
>
> Kind regards,
> Eric van Loon
> Air France/KLM Core Infra
> 
> For information, services and offers, please visit our web site:
https://urldefense.com/v3/__http://www.klm.com__;!!NGPcfA!JTtdb1Yf5ThxhIC1O3
cYrHeyiEIrgVH0_Rok2Ohg7oTNaDo4dqQjf-8SOIzzNI3XqQCML321Q7edDLu_uWAa3-n0Lfyc7Q
$. This e-mail and any attachment may contain confidential and
privileged material intended for the addressee only. If you are not the
addressee, you are notified that no part of the e-mail or any attachment may
be disclosed, copied or distributed, and that any other action related to
this e-mail or attachment is strictly prohibited, and may be unlawful. If
you have received this e-mail by error, please notify the sender immediately
by return e-mail, and delete this message.
>
> Koninklijke Luchtvaart Maatschappij NV (KLM), its subsidiaries and/or its
employees shall not be liable for the incorrect or incomplete transmission
of this e-mail or any attachments, nor responsible for any delay in receipt.
> Koninklijke Luchtvaart Maatschappij N.V. (also known as KLM Royal Dutch
Airlines) is registered in Amstelveen, The Netherlands, with registered
number 33014286
> 
>
> Unless otherwise stated above:
>
> IBM United Kingdom Limited
> Registered in England and Wales with number 741598 Registered office: PO
Box 41, North Harbour, Portsmouth, Hants. PO6 3AU
>
> **CONFIDENTIALITY NOTICE** This electronic message contains information
from Southeastern Grocers, Inc and is intended on

Re: SQL query replnodes

[Andrew Raibeck]

Hi Eric,

I do not think there is any trivial way to use SELECT, as I suspect the output 
is computed rather than merely copied from an internal table. Is there some 
special reason why the QUERY REPLNODE command is inadequate? If you want a CSV 
format, you could use the -DATAONLY=YES option and either the -COMMADELIMITED 
or -TABDELIMITED options.

Example:

dsmadmc -id=youradminid -pa=topsecret -tabdelimited -dataonly=yes 
-outfile=qreplnode.csv "query replnode *"

Regards,

Andy

Andrew Raibeck
IBM Spectrum Protect Level 3
IBM Systems, Storage
stor...@us.ibm.com

IBM

-Original Message-
From: ADSM: Dist Stor Manager  On Behalf Of Griggs, James 
F. (JMD) (CTR)
Sent: Thursday, 22 September, 2022 11:00
To: ADSM-L@VM.MARIST.EDU
Subject: [EXTERNAL] Re: SQL query replnodes

Seems to me you could use the following fields from the "NODES" table:

REPL_STATE
REPL_MODE

Repl_state indicates if replicate is "Enabled" for a node Repl_mode indicates 
send or receive
  Where "Send" is the source node
  And where "Receive" is a destination replication node

Should both the repl_state and repl_node = "None" it is not being replicated



-Original Message-
From: ADSM: Dist Stor Manager  On Behalf Of Loon, Eric 
van (ITOP DI) - KLM
Sent: Thursday, September 22, 2022 8:47 AM
To: ADSM-L@VM.MARIST.EDU
Subject: [EXTERNAL] Re: [ADSM-L] SQL query replnodes

Hi Dmitry,

That still doesn't show me if replnodes exist for a specific node...

Kind regards,
Eric van Loon
Air France/KLM Core Infra

-Original Message-
From: ADSM: Dist Stor Manager  On Behalf Of Dmitry Dukhov
Sent: donderdag 22 september 2022 13:20
To: ADSM-L@VM.MARIST.EDU
Subject: Re: SQL query replnodes

Hi all
Better to use

Select * from nodes where repl_state='NONE'
Repl_state can have three status: none, enabled, disabled

I think it's more correct
Dmitry Dukhov

https://protect2.fireeye.com/v1/url?k=948d3f5d-cb1607ad-948a1bb8-ac1f6b01744c-e405eb27701fe333=1=f4eb6721-455e-4909-a32e-bf2a05bc2aca=https%3A%2F%2Fwww.tsmexplorer.copm%2F
  


> On 21 Sep 2022, at 19:28, Rick Adamson  wrote:
> 
> Eric,
> Are you looking for something along these lines.?
> 
> select node_name,repl_state,repl_mode from nodes where 
> repl_state='ENABLED' order by node_name
> 
> 
> -Rick Adamson
> 
> -Original Message-
> From: ADSM: Dist Stor Manager  On Behalf Of 
> Oscar Kolsteren
> Sent: Wednesday, September 21, 2022 11:04 AM
> To: ADSM-L@VM.MARIST.EDU
> Subject: Re: [ADSM-L] SQL query replnodes
> 
> * This email originated outside of the organization. Use caution when 
> opening attachments or clicking links. *
> 
> --
> Hey Eric,
> 
> Have a look in our data collector which is used to create our FREE IBM CSA 
> Health Check report.
> 
> If you don't have a copy left, let me know and I'll share one. In the script 
> are all the select statements we use.
> 
> Or just run a fresh collection and upload for me to create a new report.
> 
> Best Regards,
> 
> Oscar Kolsteren
> Lead Storage Consultant, IBM Client Storage Assessment Team (UKI)
> 
> -Original Message-----
> From: ADSM: Dist Stor Manager  On Behalf Of 
> Loon, Eric van (ITOP DI) - KLM
> Sent: 21 September 2022 09:16
> To: ADSM-L@VM.MARIST.EDU
> Subject: [EXTERNAL] [ADSM-L] SQL query replnodes
> 
> Hi everybody,
> 
> I'm trying to find a way to list replnode(s) through a SQL query, does 
> anybody know where this information can be found? So something like QUERY 
> RPELNODE , but than through a SQL query.
> Thanks for any help in advance!
> 
> Kind regards,
> Eric van Loon
> Air France/KLM Core Infra
> 
> For information, services and offers, please visit our web site: 
> http://www.klm.com. This e-mail and any attachment may contain 
> confidential and privileged material intended for the addressee only. If you 
> are not the addressee, you are notified that no part of the e-mail or any 
> attachment may be disclosed, copied or distributed, and that any other action 
> related to this e-mail or attachment is strictly prohibited, and may be 
> unlawful. If you have received this e-mail by error, please notify the sender 
> immediately by return e-mail, and delete this message.
> 
> Koninklijke Luchtvaart Maatschappij NV (KLM), its subsidiaries and/or its 
> employees shall not be liable for the incorrect or incomplete transmission of 
> this e-mail or any attachments, nor responsible for any delay in receipt.
> Koninklijke Luchtvaart Maatschappij N.V. (also known as KLM Royal 
> Dutch Airlines) is registered in Amstelveen, The Netherlands, with 
> registered number 33014286
> *

Re: SQL query replnodes

[Marc Lanteigne]

Bonjour Eric,

If you find the node in the REPLICATIONVIEW table, it means it has replicated 
at least once, which means it would also show up in Q REPLNODE.

Now, what I can't test at the moment is that once you do a REMOVE REPLNODE, 
does it clear the entries in the REPLICATIONVIEW right away or does it keep the 
history. If it keeps the history, then it would not be useful for you.

-
Thanks,
Marc...

Marc Lanteigne
Spectrum Protect Specialist AVP / SRT
IBM Systems, Spectrum Protect / Plus
+1-506-460-9074 
marclantei...@ca.ibm.com 
Office Hours:  Monday to Friday, 7:00 to 15:30 Eastern
 
IBM

-Original Message-
From: ADSM: Dist Stor Manager  On Behalf Of Loon, Eric 
van (ITOP DI) - KLM
Sent: Thursday, September 22, 2022 09:47 AM
To: ADSM-L@VM.MARIST.EDU
Subject: [EXTERNAL] Re: [ADSM-L] SQL query replnodes

Hi Dmitry,

That still doesn't show me if replnodes exist for a specific node...

Kind regards,
Eric van Loon
Air France/KLM Core Infra

-Original Message-
From: ADSM: Dist Stor Manager  On Behalf Of Dmitry Dukhov
Sent: donderdag 22 september 2022 13:20
To: ADSM-L@VM.MARIST.EDU
Subject: Re: SQL query replnodes

Hi all
Better to use

Select * from nodes where repl_state='NONE'
Repl_state can have three status: none, enabled, disabled

I think it's more correct
Dmitry Dukhov

https://www.tsmexplorer.copm  


> On 21 Sep 2022, at 19:28, Rick Adamson  wrote:
> 
> Eric,
> Are you looking for something along these lines.?
> 
> select node_name,repl_state,repl_mode from nodes where repl_state='ENABLED' 
> order by node_name
> 
> 
> -Rick Adamson
> 
> -Original Message-
> From: ADSM: Dist Stor Manager  On Behalf Of Oscar 
> Kolsteren
> Sent: Wednesday, September 21, 2022 11:04 AM
> To: ADSM-L@VM.MARIST.EDU
> Subject: Re: [ADSM-L] SQL query replnodes
> 
> * This email originated outside of the organization. Use caution when opening 
> attachments or clicking links. *
> 
> --
> Hey Eric,
> 
> Have a look in our data collector which is used to create our FREE IBM CSA 
> Health Check report.
> 
> If you don't have a copy left, let me know and I'll share one. In the script 
> are all the select statements we use.
> 
> Or just run a fresh collection and upload for me to create a new report.
> 
> Best Regards,
> 
> Oscar Kolsteren
> Lead Storage Consultant, IBM Client Storage Assessment Team (UKI)
> 
> -Original Message-
> From: ADSM: Dist Stor Manager  On Behalf Of Loon, Eric 
> van (ITOP DI) - KLM
> Sent: 21 September 2022 09:16
> To: ADSM-L@VM.MARIST.EDU
> Subject: [EXTERNAL] [ADSM-L] SQL query replnodes
> 
> Hi everybody,
> 
> I'm trying to find a way to list replnode(s) through a SQL query, does 
> anybody know where this information can be found? So something like QUERY 
> RPELNODE , but than through a SQL query.
> Thanks for any help in advance!
> 
> Kind regards,
> Eric van Loon
> Air France/KLM Core Infra
> 
> For information, services and offers, please visit our web site: 
> http://www.klm.com. This e-mail and any attachment may contain 
> confidential and privileged material intended for the addressee only. If you 
> are not the addressee, you are notified that no part of the e-mail or any 
> attachment may be disclosed, copied or distributed, and that any other action 
> related to this e-mail or attachment is strictly prohibited, and may be 
> unlawful. If you have received this e-mail by error, please notify the sender 
> immediately by return e-mail, and delete this message.
> 
> Koninklijke Luchtvaart Maatschappij NV (KLM), its subsidiaries and/or its 
> employees shall not be liable for the incorrect or incomplete transmission of 
> this e-mail or any attachments, nor responsible for any delay in receipt.
> Koninklijke Luchtvaart Maatschappij N.V. (also known as KLM Royal Dutch 
> Airlines) is registered in Amstelveen, The Netherlands, with registered 
> number 33014286
> 
> 
> Unless otherwise stated above:
> 
> IBM United Kingdom Limited
> Registered in England and Wales with number 741598 Registered office: PO Box 
> 41, North Harbour, Portsmouth, Hants. PO6 3AU
> 
> **CONFIDENTIALITY NOTICE** This electronic message contains information from 
> Southeastern Grocers, Inc and is intended only for the use of the addressee. 
> This message may contain information that is privileged, confidential and/or 
> exempt from disclosure under applicable Law. This message may not be read, 
> used, distributed, forwarded, reproduced or stored by any other than the 
> intended recipient. If you are not the intended recipient, please delete

Re: SQL query replnodes

[Griggs, James F. (JMD) (CTR)]

Seems to me you could use the following fields from the "NODES" table:

REPL_STATE
REPL_MODE

Repl_state indicates if replicate is "Enabled" for a node
Repl_mode indicates send or receive 
  Where "Send" is the source node
  And where "Receive" is a destination replication node

Should both the repl_state and repl_node = "None" it is not being replicated



-Original Message-
From: ADSM: Dist Stor Manager  On Behalf Of Loon, Eric 
van (ITOP DI) - KLM
Sent: Thursday, September 22, 2022 8:47 AM
To: ADSM-L@VM.MARIST.EDU
Subject: [EXTERNAL] Re: [ADSM-L] SQL query replnodes

Hi Dmitry,

That still doesn't show me if replnodes exist for a specific node...

Kind regards,
Eric van Loon
Air France/KLM Core Infra

-Original Message-
From: ADSM: Dist Stor Manager  On Behalf Of Dmitry Dukhov
Sent: donderdag 22 september 2022 13:20
To: ADSM-L@VM.MARIST.EDU
Subject: Re: SQL query replnodes

Hi all
Better to use

Select * from nodes where repl_state='NONE'
Repl_state can have three status: none, enabled, disabled

I think it's more correct
Dmitry Dukhov

https://protect2.fireeye.com/v1/url?k=948d3f5d-cb1607ad-948a1bb8-ac1f6b01744c-e405eb27701fe333=1=f4eb6721-455e-4909-a32e-bf2a05bc2aca=https%3A%2F%2Fwww.tsmexplorer.copm%2F


> On 21 Sep 2022, at 19:28, Rick Adamson  wrote:
> 
> Eric,
> Are you looking for something along these lines.?
> 
> select node_name,repl_state,repl_mode from nodes where repl_state='ENABLED' 
> order by node_name
> 
> 
> -Rick Adamson
> 
> -Original Message-
> From: ADSM: Dist Stor Manager  On Behalf Of Oscar 
> Kolsteren
> Sent: Wednesday, September 21, 2022 11:04 AM
> To: ADSM-L@VM.MARIST.EDU
> Subject: Re: [ADSM-L] SQL query replnodes
> 
> * This email originated outside of the organization. Use caution when opening 
> attachments or clicking links. *
> 
> --
> Hey Eric,
> 
> Have a look in our data collector which is used to create our FREE IBM CSA 
> Health Check report.
> 
> If you don't have a copy left, let me know and I'll share one. In the script 
> are all the select statements we use.
> 
> Or just run a fresh collection and upload for me to create a new report.
> 
> Best Regards,
> 
> Oscar Kolsteren
> Lead Storage Consultant, IBM Client Storage Assessment Team (UKI)
> 
> -Original Message-
> From: ADSM: Dist Stor Manager  On Behalf Of Loon, Eric 
> van (ITOP DI) - KLM
> Sent: 21 September 2022 09:16
> To: ADSM-L@VM.MARIST.EDU
> Subject: [EXTERNAL] [ADSM-L] SQL query replnodes
> 
> Hi everybody,
> 
> I'm trying to find a way to list replnode(s) through a SQL query, does 
> anybody know where this information can be found? So something like QUERY 
> RPELNODE , but than through a SQL query.
> Thanks for any help in advance!
> 
> Kind regards,
> Eric van Loon
> Air France/KLM Core Infra
> 
> For information, services and offers, please visit our web site: 
> https://urldefense.com/v3/__http://www.klm.com__;!!NGPcfA!JTtdb1Yf5ThxhIC1O3cYrHeyiEIrgVH0_Rok2Ohg7oTNaDo4dqQjf-8SOIzzNI3XqQCML321Q7edDLu_uWAa3-n0Lfyc7Q$
> . This e-mail and any attachment may contain confidential and privileged 
> material intended for the addressee only. If you are not the addressee, you 
> are notified that no part of the e-mail or any attachment may be disclosed, 
> copied or distributed, and that any other action related to this e-mail or 
> attachment is strictly prohibited, and may be unlawful. If you have received 
> this e-mail by error, please notify the sender immediately by return e-mail, 
> and delete this message.
> 
> Koninklijke Luchtvaart Maatschappij NV (KLM), its subsidiaries and/or its 
> employees shall not be liable for the incorrect or incomplete transmission of 
> this e-mail or any attachments, nor responsible for any delay in receipt.
> Koninklijke Luchtvaart Maatschappij N.V. (also known as KLM Royal Dutch 
> Airlines) is registered in Amstelveen, The Netherlands, with registered 
> number 33014286
> 
> 
> Unless otherwise stated above:
> 
> IBM United Kingdom Limited
> Registered in England and Wales with number 741598 Registered office: PO Box 
> 41, North Harbour, Portsmouth, Hants. PO6 3AU
> 
> **CONFIDENTIALITY NOTICE** This electronic message contains information from 
> Southeastern Grocers, Inc and is intended only for the use of the addressee. 
> This message may contain information that is privileged, confidential and/or 
> exempt from disclosure under applicable Law. This message may not be read, 
> used, distributed, forwarded, reproduced or stored by any ot

Re: SQL query replnodes

[Loon, Eric van (ITOP DI) - KLM]

Hi Dmitry,

That still doesn't show me if replnodes exist for a specific node...

Kind regards,
Eric van Loon
Air France/KLM Core Infra

-Original Message-
From: ADSM: Dist Stor Manager  On Behalf Of Dmitry Dukhov
Sent: donderdag 22 september 2022 13:20
To: ADSM-L@VM.MARIST.EDU
Subject: Re: SQL query replnodes

Hi all
Better to use

Select * from nodes where repl_state='NONE'
Repl_state can have three status: none, enabled, disabled

I think it's more correct
Dmitry Dukhov

https://www.tsmexplorer.copm


> On 21 Sep 2022, at 19:28, Rick Adamson  wrote:
> 
> Eric,
> Are you looking for something along these lines.?
> 
> select node_name,repl_state,repl_mode from nodes where repl_state='ENABLED' 
> order by node_name
> 
> 
> -Rick Adamson
> 
> -Original Message-
> From: ADSM: Dist Stor Manager  On Behalf Of Oscar 
> Kolsteren
> Sent: Wednesday, September 21, 2022 11:04 AM
> To: ADSM-L@VM.MARIST.EDU
> Subject: Re: [ADSM-L] SQL query replnodes
> 
> * This email originated outside of the organization. Use caution when opening 
> attachments or clicking links. *
> 
> --
> Hey Eric,
> 
> Have a look in our data collector which is used to create our FREE IBM CSA 
> Health Check report.
> 
> If you don't have a copy left, let me know and I'll share one. In the script 
> are all the select statements we use.
> 
> Or just run a fresh collection and upload for me to create a new report.
> 
> Best Regards,
> 
> Oscar Kolsteren
> Lead Storage Consultant, IBM Client Storage Assessment Team (UKI)
> 
> -Original Message-
> From: ADSM: Dist Stor Manager  On Behalf Of Loon, Eric 
> van (ITOP DI) - KLM
> Sent: 21 September 2022 09:16
> To: ADSM-L@VM.MARIST.EDU
> Subject: [EXTERNAL] [ADSM-L] SQL query replnodes
> 
> Hi everybody,
> 
> I'm trying to find a way to list replnode(s) through a SQL query, does 
> anybody know where this information can be found? So something like QUERY 
> RPELNODE , but than through a SQL query.
> Thanks for any help in advance!
> 
> Kind regards,
> Eric van Loon
> Air France/KLM Core Infra
> 
> For information, services and offers, please visit our web site: 
> https://urldefense.com/v3/__http://www.klm.com__;!!NGPcfA!JTtdb1Yf5ThxhIC1O3cYrHeyiEIrgVH0_Rok2Ohg7oTNaDo4dqQjf-8SOIzzNI3XqQCML321Q7edDLu_uWAa3-n0Lfyc7Q$
> . This e-mail and any attachment may contain confidential and privileged 
> material intended for the addressee only. If you are not the addressee, you 
> are notified that no part of the e-mail or any attachment may be disclosed, 
> copied or distributed, and that any other action related to this e-mail or 
> attachment is strictly prohibited, and may be unlawful. If you have received 
> this e-mail by error, please notify the sender immediately by return e-mail, 
> and delete this message.
> 
> Koninklijke Luchtvaart Maatschappij NV (KLM), its subsidiaries and/or its 
> employees shall not be liable for the incorrect or incomplete transmission of 
> this e-mail or any attachments, nor responsible for any delay in receipt.
> Koninklijke Luchtvaart Maatschappij N.V. (also known as KLM Royal Dutch 
> Airlines) is registered in Amstelveen, The Netherlands, with registered 
> number 33014286
> 
> 
> Unless otherwise stated above:
> 
> IBM United Kingdom Limited
> Registered in England and Wales with number 741598 Registered office: PO Box 
> 41, North Harbour, Portsmouth, Hants. PO6 3AU
> 
> **CONFIDENTIALITY NOTICE** This electronic message contains information from 
> Southeastern Grocers, Inc and is intended only for the use of the addressee. 
> This message may contain information that is privileged, confidential and/or 
> exempt from disclosure under applicable Law. This message may not be read, 
> used, distributed, forwarded, reproduced or stored by any other than the 
> intended recipient. If you are not the intended recipient, please delete and 
> notify the sender.

For information, services and offers, please visit our web site: 
http://www.klm.com. This e-mail and any attachment may contain confidential and 
privileged material intended for the addressee only. If you are not the 
addressee, you are notified that no part of the e-mail or any attachment may be 
disclosed, copied or distributed, and that any other action related to this 
e-mail or attachment is strictly prohibited, and may be unlawful. If you have 
received this e-mail by error, please notify the sender immediately by return 
e-mail, and delete this message.

Koninklijke Luchtvaart Maatschappij

Re: SQL query replnodes

[Dmitry Dukhov]

Hi all
Better to use

Select * from nodes where repl_state='NONE'
Repl_state can have three status: none, enabled, disabled

I think it's more correct
Dmitry Dukhov

https://www.tsmexplorer.copm


> On 21 Sep 2022, at 19:28, Rick Adamson  wrote:
> 
> Eric,
> Are you looking for something along these lines.?
> 
> select node_name,repl_state,repl_mode from nodes where repl_state='ENABLED' 
> order by node_name
> 
> 
> -Rick Adamson
> 
> -Original Message-
> From: ADSM: Dist Stor Manager  On Behalf Of Oscar 
> Kolsteren
> Sent: Wednesday, September 21, 2022 11:04 AM
> To: ADSM-L@VM.MARIST.EDU
> Subject: Re: [ADSM-L] SQL query replnodes
> 
> * This email originated outside of the organization. Use caution when opening 
> attachments or clicking links. *
> 
> --
> Hey Eric,
> 
> Have a look in our data collector which is used to create our FREE IBM CSA 
> Health Check report.
> 
> If you don't have a copy left, let me know and I'll share one. In the script 
> are all the select statements we use.
> 
> Or just run a fresh collection and upload for me to create a new report.
> 
> Best Regards,
> 
> Oscar Kolsteren
> Lead Storage Consultant, IBM Client Storage Assessment Team (UKI)
> 
> -Original Message-
> From: ADSM: Dist Stor Manager  On Behalf Of Loon, Eric 
> van (ITOP DI) - KLM
> Sent: 21 September 2022 09:16
> To: ADSM-L@VM.MARIST.EDU
> Subject: [EXTERNAL] [ADSM-L] SQL query replnodes
> 
> Hi everybody,
> 
> I'm trying to find a way to list replnode(s) through a SQL query, does 
> anybody know where this information can be found? So something like QUERY 
> RPELNODE , but than through a SQL query.
> Thanks for any help in advance!
> 
> Kind regards,
> Eric van Loon
> Air France/KLM Core Infra
> 
> For information, services and offers, please visit our web site: 
> https://urldefense.com/v3/__http://www.klm.com__;!!NGPcfA!JTtdb1Yf5ThxhIC1O3cYrHeyiEIrgVH0_Rok2Ohg7oTNaDo4dqQjf-8SOIzzNI3XqQCML321Q7edDLu_uWAa3-n0Lfyc7Q$
> . This e-mail and any attachment may contain confidential and privileged 
> material intended for the addressee only. If you are not the addressee, you 
> are notified that no part of the e-mail or any attachment may be disclosed, 
> copied or distributed, and that any other action related to this e-mail or 
> attachment is strictly prohibited, and may be unlawful. If you have received 
> this e-mail by error, please notify the sender immediately by return e-mail, 
> and delete this message.
> 
> Koninklijke Luchtvaart Maatschappij NV (KLM), its subsidiaries and/or its 
> employees shall not be liable for the incorrect or incomplete transmission of 
> this e-mail or any attachments, nor responsible for any delay in receipt.
> Koninklijke Luchtvaart Maatschappij N.V. (also known as KLM Royal Dutch 
> Airlines) is registered in Amstelveen, The Netherlands, with registered 
> number 33014286
> 
> 
> Unless otherwise stated above:
> 
> IBM United Kingdom Limited
> Registered in England and Wales with number 741598 Registered office: PO Box 
> 41, North Harbour, Portsmouth, Hants. PO6 3AU
> 
> **CONFIDENTIALITY NOTICE** This electronic message contains information from 
> Southeastern Grocers, Inc and is intended only for the use of the addressee. 
> This message may contain information that is privileged, confidential and/or 
> exempt from disclosure under applicable Law. This message may not be read, 
> used, distributed, forwarded, reproduced or stored by any other than the 
> intended recipient. If you are not the intended recipient, please delete and 
> notify the sender.

Re: SQL query replnodes

[Loon, Eric van (ITOP DI) - KLM]

Hi Rick,

Actually no. This query shows if replication is enabled and the replication 
mode, not if replnode(s) exists.
I'm trying to create a script which only issues the DELETE REPLNODE command if 
a replnode exists. You can have nodes with replication disabled which do have a 
replnode, so just checking for the repl_state won't always do.
Thanks for your help!

Kind regards,
Eric van Loon
Core Infra

-Original Message-
From: ADSM: Dist Stor Manager  On Behalf Of Rick Adamson
Sent: woensdag 21 september 2022 19:29
To: ADSM-L@VM.MARIST.EDU
Subject: Re: SQL query replnodes

Eric,
Are you looking for something along these lines.?

select node_name,repl_state,repl_mode from nodes where repl_state='ENABLED' 
order by node_name


 -Rick Adamson

-Original Message-
From: ADSM: Dist Stor Manager  On Behalf Of Oscar 
Kolsteren
Sent: Wednesday, September 21, 2022 11:04 AM
To: ADSM-L@VM.MARIST.EDU
Subject: Re: [ADSM-L] SQL query replnodes

* This email originated outside of the organization. Use caution when opening 
attachments or clicking links. *

--
Hey Eric,

Have a look in our data collector which is used to create our FREE IBM CSA 
Health Check report.

If you don't have a copy left, let me know and I'll share one. In the script 
are all the select statements we use.

Or just run a fresh collection and upload for me to create a new report.

Best Regards,

Oscar Kolsteren
Lead Storage Consultant, IBM Client Storage Assessment Team (UKI)

-Original Message-
From: ADSM: Dist Stor Manager  On Behalf Of Loon, Eric 
van (ITOP DI) - KLM
Sent: 21 September 2022 09:16
To: ADSM-L@VM.MARIST.EDU
Subject: [EXTERNAL] [ADSM-L] SQL query replnodes

Hi everybody,

I'm trying to find a way to list replnode(s) through a SQL query, does anybody 
know where this information can be found? So something like QUERY RPELNODE 
, but than through a SQL query.
Thanks for any help in advance!

Kind regards,
Eric van Loon
Air France/KLM Core Infra

For information, services and offers, please visit our web site: 
https://urldefense.com/v3/__http://www.klm.com__;!!NGPcfA!JTtdb1Yf5ThxhIC1O3cYrHeyiEIrgVH0_Rok2Ohg7oTNaDo4dqQjf-8SOIzzNI3XqQCML321Q7edDLu_uWAa3-n0Lfyc7Q$
. This e-mail and any attachment may contain confidential and privileged 
material intended for the addressee only. If you are not the addressee, you are 
notified that no part of the e-mail or any attachment may be disclosed, copied 
or distributed, and that any other action related to this e-mail or attachment 
is strictly prohibited, and may be unlawful. If you have received this e-mail 
by error, please notify the sender immediately by return e-mail, and delete 
this message.

Koninklijke Luchtvaart Maatschappij NV (KLM), its subsidiaries and/or its 
employees shall not be liable for the incorrect or incomplete transmission of 
this e-mail or any attachments, nor responsible for any delay in receipt.
Koninklijke Luchtvaart Maatschappij N.V. (also known as KLM Royal Dutch 
Airlines) is registered in Amstelveen, The Netherlands, with registered number 
33014286


Unless otherwise stated above:

IBM United Kingdom Limited
Registered in England and Wales with number 741598 Registered office: PO Box 
41, North Harbour, Portsmouth, Hants. PO6 3AU

**CONFIDENTIALITY NOTICE** This electronic message contains information from 
Southeastern Grocers, Inc and is intended only for the use of the addressee. 
This message may contain information that is privileged, confidential and/or 
exempt from disclosure under applicable Law. This message may not be read, 
used, distributed, forwarded, reproduced or stored by any other than the 
intended recipient. If you are not the intended recipient, please delete and 
notify the sender.

For information, services and offers, please visit our web site: 
http://www.klm.com. This e-mail and any attachment may contain confidential and 
privileged material intended for the addressee only. If you are not the 
addressee, you are notified that no part of the e-mail or any attachment may be 
disclosed, copied or distributed, and that any other action related to this 
e-mail or attachment is strictly prohibited, and may be unlawful. If you have 
received this e-mail by error, please notify the sender immediately by return 
e-mail, and delete this message.

Koninklijke Luchtvaart Maatschappij NV (KLM), its subsidiaries and/or its 
employees shall not be liable for the incorrect or incomplete transmission of 
this e-mail or any attachments, nor responsible for any delay in receipt.
Koninklijke Luchtvaart Maatschappij N.V. (also known as KLM Royal Dutch 
Airlines) is registered in Amstelveen, The Netherlands, with registered number 
33014286

Re: SQL query replnodes

[Oscar Kolsteren]

Understood Eric,

will ask around for an equivalent "select" command structure

Best Regards,

Oscar Kolsteren
Lead Storage Consultant, IBM Client Storage Assessment Team (UKI)

-Original Message-
From: ADSM: Dist Stor Manager  On Behalf Of Loon, Eric 
van (ITOP DI) - KLM
Sent: 21 September 2022 16:21
To: ADSM-L@VM.MARIST.EDU
Subject: [EXTERNAL] Re: [ADSM-L] SQL query replnodes

Hi Oscar,

The only statement related to replication in the script is a select * from 
REPLICATIONVIEW with ur, which is not really what I'm looking for. I'm hoping 
to find a SQL query with basically the same output as a q replnode .
By the way, for us, this is definitely not the best time to rerun the CSA 
Health Check Report: we're still debugging quite a few replication issues with 
support. If we receive stable code again from IBM, maybe I will give it another 
try.

Kind regards,
Eric van Loon
Core Infra

-Original Message-
From: ADSM: Dist Stor Manager  On Behalf Of Oscar 
Kolsteren
Sent: woensdag 21 september 2022 17:04
To: ADSM-L@VM.MARIST.EDU
Subject: Re: SQL query replnodes

Hey Eric,

Have a look in our data collector which is used to create our FREE IBM CSA 
Health Check report.

If you don't have a copy left, let me know and I'll share one. In the script 
are all the select statements we use.

Or just run a fresh collection and upload for me to create a new report.

Best Regards,

Oscar Kolsteren
Lead Storage Consultant, IBM Client Storage Assessment Team (UKI)

-Original Message-
From: ADSM: Dist Stor Manager  On Behalf Of Loon, Eric 
van (ITOP DI) - KLM
Sent: 21 September 2022 09:16
To: ADSM-L@VM.MARIST.EDU
Subject: [EXTERNAL] [ADSM-L] SQL query replnodes

Hi everybody,

I'm trying to find a way to list replnode(s) through a SQL query, does anybody 
know where this information can be found? So something like QUERY RPELNODE 
, but than through a SQL query.
Thanks for any help in advance!

Kind regards,
Eric van Loon
Air France/KLM Core Infra

For information, services and offers, please visit our web site: 
http://www.klm.com. This e-mail and any attachment may contain confidential 
and privileged material intended for the addressee only. If you are not the 
addressee, you are notified that no part of the e-mail or any attachment may be 
disclosed, copied or distributed, and that any other action related to this 
e-mail or attachment is strictly prohibited, and may be unlawful. If you have 
received this e-mail by error, please notify the sender immediately by return 
e-mail, and delete this message.

Koninklijke Luchtvaart Maatschappij NV (KLM), its subsidiaries and/or its 
employees shall not be liable for the incorrect or incomplete transmission of 
this e-mail or any attachments, nor responsible for any delay in receipt.
Koninklijke Luchtvaart Maatschappij N.V. (also known as KLM Royal Dutch 
Airlines) is registered in Amstelveen, The Netherlands, with registered number 
33014286


Unless otherwise stated above:

IBM United Kingdom Limited
Registered in England and Wales with number 741598 Registered office: PO Box 
41, North Harbour, Portsmouth, Hants. PO6 3AU

For information, services and offers, please visit our web site: 
http://www.klm.com  . This e-mail and any attachment may contain confidential 
and privileged material intended for the addressee only. If you are not the 
addressee, you are notified that no part of the e-mail or any attachment may be 
disclosed, copied or distributed, and that any other action related to this 
e-mail or attachment is strictly prohibited, and may be unlawful. If you have 
received this e-mail by error, please notify the sender immediately by return 
e-mail, and delete this message.

Koninklijke Luchtvaart Maatschappij NV (KLM), its subsidiaries and/or its 
employees shall not be liable for the incorrect or incomplete transmission of 
this e-mail or any attachments, nor responsible for any delay in receipt.
Koninklijke Luchtvaart Maatschappij N.V. (also known as KLM Royal Dutch 
Airlines) is registered in Amstelveen, The Netherlands, with registered number 
33014286


Unless otherwise stated above:

IBM United Kingdom Limited
Registered in England and Wales with number 741598
Registered office: PO Box 41, North Harbour, Portsmouth, Hants. PO6 3AU

Re: SQL query replnodes

[Rick Adamson]

Eric,
Are you looking for something along these lines.?

select node_name,repl_state,repl_mode from nodes where repl_state='ENABLED' 
order by node_name


 -Rick Adamson

-Original Message-
From: ADSM: Dist Stor Manager  On Behalf Of Oscar 
Kolsteren
Sent: Wednesday, September 21, 2022 11:04 AM
To: ADSM-L@VM.MARIST.EDU
Subject: Re: [ADSM-L] SQL query replnodes

* This email originated outside of the organization. Use caution when opening 
attachments or clicking links. *

--
Hey Eric,

Have a look in our data collector which is used to create our FREE IBM CSA 
Health Check report.

If you don't have a copy left, let me know and I'll share one. In the script 
are all the select statements we use.

Or just run a fresh collection and upload for me to create a new report.

Best Regards,

Oscar Kolsteren
Lead Storage Consultant, IBM Client Storage Assessment Team (UKI)

-Original Message-
From: ADSM: Dist Stor Manager  On Behalf Of Loon, Eric 
van (ITOP DI) - KLM
Sent: 21 September 2022 09:16
To: ADSM-L@VM.MARIST.EDU
Subject: [EXTERNAL] [ADSM-L] SQL query replnodes

Hi everybody,

I'm trying to find a way to list replnode(s) through a SQL query, does anybody 
know where this information can be found? So something like QUERY RPELNODE 
, but than through a SQL query.
Thanks for any help in advance!

Kind regards,
Eric van Loon
Air France/KLM Core Infra

For information, services and offers, please visit our web site: 
https://urldefense.com/v3/__http://www.klm.com__;!!NGPcfA!JTtdb1Yf5ThxhIC1O3cYrHeyiEIrgVH0_Rok2Ohg7oTNaDo4dqQjf-8SOIzzNI3XqQCML321Q7edDLu_uWAa3-n0Lfyc7Q$
. This e-mail and any attachment may contain confidential and privileged 
material intended for the addressee only. If you are not the addressee, you are 
notified that no part of the e-mail or any attachment may be disclosed, copied 
or distributed, and that any other action related to this e-mail or attachment 
is strictly prohibited, and may be unlawful. If you have received this e-mail 
by error, please notify the sender immediately by return e-mail, and delete 
this message.

Koninklijke Luchtvaart Maatschappij NV (KLM), its subsidiaries and/or its 
employees shall not be liable for the incorrect or incomplete transmission of 
this e-mail or any attachments, nor responsible for any delay in receipt.
Koninklijke Luchtvaart Maatschappij N.V. (also known as KLM Royal Dutch 
Airlines) is registered in Amstelveen, The Netherlands, with registered number 
33014286


Unless otherwise stated above:

IBM United Kingdom Limited
Registered in England and Wales with number 741598 Registered office: PO Box 
41, North Harbour, Portsmouth, Hants. PO6 3AU

**CONFIDENTIALITY NOTICE** This electronic message contains information from 
Southeastern Grocers, Inc and is intended only for the use of the addressee. 
This message may contain information that is privileged, confidential and/or 
exempt from disclosure under applicable Law. This message may not be read, 
used, distributed, forwarded, reproduced or stored by any other than the 
intended recipient. If you are not the intended recipient, please delete and 
notify the sender.

Re: SQL query replnodes

[Loon, Eric van (ITOP DI) - KLM]

Hi Oscar,

The only statement related to replication in the script is a select * from 
REPLICATIONVIEW with ur, which is not really what I'm looking for. I'm hoping 
to find a SQL query with basically the same output as a q replnode .
By the way, for us, this is definitely not the best time to rerun the CSA 
Health Check Report: we're still debugging quite a few replication issues with 
support. If we receive stable code again from IBM, maybe I will give it another 
try.

Kind regards,
Eric van Loon
Core Infra

-Original Message-
From: ADSM: Dist Stor Manager  On Behalf Of Oscar 
Kolsteren
Sent: woensdag 21 september 2022 17:04
To: ADSM-L@VM.MARIST.EDU
Subject: Re: SQL query replnodes

Hey Eric,

Have a look in our data collector which is used to create our FREE IBM CSA 
Health Check report.

If you don't have a copy left, let me know and I'll share one. In the script 
are all the select statements we use.

Or just run a fresh collection and upload for me to create a new report.

Best Regards,

Oscar Kolsteren
Lead Storage Consultant, IBM Client Storage Assessment Team (UKI)

-Original Message-
From: ADSM: Dist Stor Manager  On Behalf Of Loon, Eric 
van (ITOP DI) - KLM
Sent: 21 September 2022 09:16
To: ADSM-L@VM.MARIST.EDU
Subject: [EXTERNAL] [ADSM-L] SQL query replnodes

Hi everybody,

I'm trying to find a way to list replnode(s) through a SQL query, does anybody 
know where this information can be found? So something like QUERY RPELNODE 
, but than through a SQL query.
Thanks for any help in advance!

Kind regards,
Eric van Loon
Air France/KLM Core Infra

For information, services and offers, please visit our web site: 
http://www.klm.com  . This e-mail and any attachment may contain confidential 
and privileged material intended for the addressee only. If you are not the 
addressee, you are notified that no part of the e-mail or any attachment may be 
disclosed, copied or distributed, and that any other action related to this 
e-mail or attachment is strictly prohibited, and may be unlawful. If you have 
received this e-mail by error, please notify the sender immediately by return 
e-mail, and delete this message.

Koninklijke Luchtvaart Maatschappij NV (KLM), its subsidiaries and/or its 
employees shall not be liable for the incorrect or incomplete transmission of 
this e-mail or any attachments, nor responsible for any delay in receipt.
Koninklijke Luchtvaart Maatschappij N.V. (also known as KLM Royal Dutch 
Airlines) is registered in Amstelveen, The Netherlands, with registered number 
33014286


Unless otherwise stated above:

IBM United Kingdom Limited
Registered in England and Wales with number 741598 Registered office: PO Box 
41, North Harbour, Portsmouth, Hants. PO6 3AU

For information, services and offers, please visit our web site: 
http://www.klm.com. This e-mail and any attachment may contain confidential and 
privileged material intended for the addressee only. If you are not the 
addressee, you are notified that no part of the e-mail or any attachment may be 
disclosed, copied or distributed, and that any other action related to this 
e-mail or attachment is strictly prohibited, and may be unlawful. If you have 
received this e-mail by error, please notify the sender immediately by return 
e-mail, and delete this message.

Koninklijke Luchtvaart Maatschappij NV (KLM), its subsidiaries and/or its 
employees shall not be liable for the incorrect or incomplete transmission of 
this e-mail or any attachments, nor responsible for any delay in receipt.
Koninklijke Luchtvaart Maatschappij N.V. (also known as KLM Royal Dutch 
Airlines) is registered in Amstelveen, The Netherlands, with registered number 
33014286

Re: SQL query replnodes

[Oscar Kolsteren]

Hey Eric,

Have a look in our data collector which is used to create our FREE IBM CSA 
Health Check report.

If you don't have a copy left, let me know and I'll share one. In the script 
are all the select statements we use.

Or just run a fresh collection and upload for me to create a new report.

Best Regards,

Oscar Kolsteren
Lead Storage Consultant, IBM Client Storage Assessment Team (UKI)

-Original Message-
From: ADSM: Dist Stor Manager  On Behalf Of Loon, Eric 
van (ITOP DI) - KLM
Sent: 21 September 2022 09:16
To: ADSM-L@VM.MARIST.EDU
Subject: [EXTERNAL] [ADSM-L] SQL query replnodes

Hi everybody,

I'm trying to find a way to list replnode(s) through a SQL query, does anybody 
know where this information can be found? So something like QUERY RPELNODE 
, but than through a SQL query.
Thanks for any help in advance!

Kind regards,
Eric van Loon
Air France/KLM Core Infra

For information, services and offers, please visit our web site: 
http://www.klm.com  . This e-mail and any attachment may contain confidential 
and privileged material intended for the addressee only. If you are not the 
addressee, you are notified that no part of the e-mail or any attachment may be 
disclosed, copied or distributed, and that any other action related to this 
e-mail or attachment is strictly prohibited, and may be unlawful. If you have 
received this e-mail by error, please notify the sender immediately by return 
e-mail, and delete this message.

Koninklijke Luchtvaart Maatschappij NV (KLM), its subsidiaries and/or its 
employees shall not be liable for the incorrect or incomplete transmission of 
this e-mail or any attachments, nor responsible for any delay in receipt.
Koninklijke Luchtvaart Maatschappij N.V. (also known as KLM Royal Dutch 
Airlines) is registered in Amstelveen, The Netherlands, with registered number 
33014286


Unless otherwise stated above:

IBM United Kingdom Limited
Registered in England and Wales with number 741598
Registered office: PO Box 41, North Harbour, Portsmouth, Hants. PO6 3AU

SQL query replnodes

[Loon, Eric van (ITOP DI) - KLM]

Hi everybody,

I'm trying to find a way to list replnode(s) through a SQL query, does anybody 
know where this information can be found? So something like QUERY RPELNODE 
, but than through a SQL query.
Thanks for any help in advance!

Kind regards,
Eric van Loon
Air France/KLM Core Infra

For information, services and offers, please visit our web site: 
http://www.klm.com. This e-mail and any attachment may contain confidential and 
privileged material intended for the addressee only. If you are not the 
addressee, you are notified that no part of the e-mail or any attachment may be 
disclosed, copied or distributed, and that any other action related to this 
e-mail or attachment is strictly prohibited, and may be unlawful. If you have 
received this e-mail by error, please notify the sender immediately by return 
e-mail, and delete this message.

Koninklijke Luchtvaart Maatschappij NV (KLM), its subsidiaries and/or its 
employees shall not be liable for the incorrect or incomplete transmission of 
this e-mail or any attachments, nor responsible for any delay in receipt.
Koninklijke Luchtvaart Maatschappij N.V. (also known as KLM Royal Dutch 
Airlines) is registered in Amstelveen, The Netherlands, with registered number 
33014286

Re: SQL query to identify all nodegroup members

[Martin Janosik]

Hi Eric,

Table 'nodes' has column 'nodegroup' so just use
select node_name from nodes where nodegroup='specific-large-nodegroup'

It's not possible to memorize the database schema so I always use these 2:
# to get list of available tables
select tabname from syscat.tables
# to see what colums are present in that table
select colname from syscat.columns where tabname='NODES'

Easiest is to query for a single object from table using 'fetch...'
select * from nodes fetch first 1 row only
however it is useless  if you run it against an empty table:
Protect: SP01>select * from COLLOCGROUP
ANR2034E SELECT: No match found using this criteria.
ANS8001I Return code 11.
In that case use syscat.columns.

Martin Janosik

-Original Message-
From: ADSM: Dist Stor Manager  On Behalf Of Loon, Eric 
van (ITOP DI) - KLM
Sent: piatok 12. augusta 2022 9:24
To: ADSM-L@VM.MARIST.EDU
Subject: [EXTERNAL] [ADSM-L] SQL query to identify all nodegroup members

Hi everybody,

I'm trying to create a SQL query to identify all nodegroup members for a 
specific nodegroup. The problem is that a select node_name from nodegroup 
returns only the first 2048 characters, so for large nodegroups, the output is 
incomplete...
Does anybody know a solution for this issue? Thanks in advance!

Kind regards,
Eric van Loon
Core Infra

For information, services and offers, please visit our web site: 
http://www.klm.com . This e-mail and any attachment may contain confidential 
and privileged material intended for the addressee only. If you are not the 
addressee, you are notified that no part of the e-mail or any attachment may be 
disclosed, copied or distributed, and that any other action related to this 
e-mail or attachment is strictly prohibited, and may be unlawful. If you have 
received this e-mail by error, please notify the sender immediately by return 
e-mail, and delete this message.

Koninklijke Luchtvaart Maatschappij NV (KLM), its subsidiaries and/or its 
employees shall not be liable for the incorrect or incomplete transmission of 
this e-mail or any attachments, nor responsible for any delay in receipt.
Koninklijke Luchtvaart Maatschappij N.V. (also known as KLM Royal Dutch 
Airlines) is registered in Amstelveen, The Netherlands, with registered number 
33014286


Unless stated otherwise above:
Kyndryl Česká republika, spol. s r. o.
Sídlo: V Parku 2294/4, Chodov, 148 00  Praha 4,
IČ: 096 28 886
Zapsaná v obchodním rejstříku, vedeném Městským soudem v Praze (oddíl C, vložka 
339277)
Registered address: V Parku, 2294/4, Chodov, 148 00 Prague 4
Company ID: 096 28 886
Registered in the Commercial Register maintained by the Municipal Court in 
Prague (Part C, Entry 339277

SQL query for outstanding requests?

[Bent Christensen (BVC)]

Hi,

Does anyone know how to query the TSM DB2 for outstanding requests with a 
SELECT statement?

I basically need to do much the same as QUERY REQUEST does, but I only need a 
Yes or No and would like to avoid parsing the output of QUERY REQUEST.


 - Bent


COWI handles personal data as stated in our Privacy 
Notice.

Re: SQL query

[Skylar Thompson]

You're welcome! Glad that ended up working, despite not having a great way
to test it here. :)

On Tue, Apr 14, 2020 at 12:07:33PM +, Loon, Eric van (ITOP NS) - KLM wrote:
> Hi Skylar,
>
> I had to change your suggestion a little bit, but this one is working:
>
> select a.node_name, case when b.count is null then 0 else b.count end as 
> count from nodes a left join (select node_name,count(*) as count from backups 
> where (days(current_date) - days(backup_date) >= 30) and 
> state='ACTIVE_VERSION' group by node_name) b on a.node_name=b.node_name
>
> Thank you VERY much for your help, I really appreciate it!
>
> Kind regards,
> Eric van Loon
> Air France/KLM Storage & Backup
>
>
> -Original Message-
> From: ADSM: Dist Stor Manager  On Behalf Of Skylar 
> Thompson
> Sent: donderdag 9 april 2020 20:21
> To: ADSM-L@VM.MARIST.EDU
> Subject: Re: SQL query
>
> I forgot that GROUP BY depended on having an entry in the result table.
> Unfortunately I don't have a TSM server with a reasonably-sized backups table 
> to test on (production ones are 1+ billion entries), so I'm kind of in 
> thought experiment territory right now, but what if you did an outer join 
> from the nodes table against a sub-query on the backups table? That would let 
> you replace the count for nodes without an entry in the sub-query with 0 with 
> CASE:
>
> select
>   a.node_name,
>   case when b.count is null
>   then 0
>   else b.count
>   end as count
> from nodes a
> left join (select node_name,count(*) from backups where
>   (days(current_date) - days(a.backup_date) >= 30)
>   and a.state='ACTIVE_VERSION'
>   group by node_name) b on a.node_name=b.node_name
>
>
> On Thu, Apr 09, 2020 at 10:53:09AM +, Loon, Eric van (ITOP NS) - KLM 
> wrote:
> > Hi Skylar,
> >
> > Sorry, but this one doesn't work either, it returns the same results as all 
> > others. I don't think the NULL result is the issue here, it seems to be the 
> > way the results are returned as soon as you select multiple columns. In the 
> > following example ,when I select just one, the result is 0:
> >
> > select count(*) from backups where node_name='RAC_098-ORC' and
> > days(current_date) - days(backup_date) >= 3000
> >
> >   Unnamed[1]
> > 
> >0
> >
> > But as soon as you select multiple columns, the result is not 0, but "no 
> > match found":
> >
> > select node_name, count(*) from backups where node_name='RAC_098-ORC'
> > and days(current_date) - days(backup_date) >= 3000 group by node_name 
> > ANR2034E SELECT: No match found using this criteria.
> > ANS8001I Return code 11.
> >
> > Thanks again for your help!
> >
> > Kind regards,
> > Eric van Loon
> > Air France/KLM Storage & Backup
> >
> >
> >
> > -Original Message-
> > From: ADSM: Dist Stor Manager  On Behalf Of
> > Skylar Thompson
> > Sent: woensdag 8 april 2020 16:03
> > To: ADSM-L@VM.MARIST.EDU
> > Subject: Re: SQL query
> >
> > Ah, I think the problem is that comparing anything with NULL will be NULL 
> > (except comparing NULL with NULL, which is true). Try this:
> >
> > select b.node_name, count(*)
> > from backups a
> > right join nodes b on a.node_name=b.node_name and b.node_name like '%-ORC'
> > where
> > (a.backup_date is null or ((days(current_date) - 
> > days(a.backup_date) >= 30)))
> > and (a.state is null or a.state='ACTIVE_VERSION') group by
> > b.node_name
> >
> > Note that I also changed the "group by" and projection to use node_name 
> > from the nodes table since that's guaranteed to be set, rather than backups 
> > which would only be set for nodes with entries in the backups table.
> >
> > On Wed, Apr 08, 2020 at 08:26:42AM +, Loon, Eric van (ITOP NS) - KLM 
> > wrote:
> > > Hi Skylar,
> > >
> > > I tried your query, but it also returns just one node with a number > 0, 
> > > all other nodes (which have 0 files) are not listed.
> > > Thanks for your help!
> > >
> > > Kind regards,
> > > Eric van Loon
> > > Air France/KLM Storage & Backup
> > >
> > > -Original Message-
> > > From: ADSM: Dist Stor Manager  On Behalf Of
> > > Skylar Thompson
> > > Sent: dinsdag 7 april 2020 23:42
> > > To: ADSM-L@VM.MARIST.EDU
> > > Subject: Re: SQL query
> > >
> > > I think what you're looking for is an outer join:
> > >
&g

Re: SQL query

[Loon, Eric van (ITOP NS) - KLM]

Hi Skylar,

I had to change your suggestion a little bit, but this one is working:

select a.node_name, case when b.count is null then 0 else b.count end as count 
from nodes a left join (select node_name,count(*) as count from backups where 
(days(current_date) - days(backup_date) >= 30) and state='ACTIVE_VERSION' group 
by node_name) b on a.node_name=b.node_name

Thank you VERY much for your help, I really appreciate it!

Kind regards,
Eric van Loon
Air France/KLM Storage & Backup


-Original Message-
From: ADSM: Dist Stor Manager  On Behalf Of Skylar 
Thompson
Sent: donderdag 9 april 2020 20:21
To: ADSM-L@VM.MARIST.EDU
Subject: Re: SQL query

I forgot that GROUP BY depended on having an entry in the result table.
Unfortunately I don't have a TSM server with a reasonably-sized backups table 
to test on (production ones are 1+ billion entries), so I'm kind of in thought 
experiment territory right now, but what if you did an outer join from the 
nodes table against a sub-query on the backups table? That would let you 
replace the count for nodes without an entry in the sub-query with 0 with CASE:

select
a.node_name,
case when b.count is null
then 0
else b.count
end as count
from nodes a
left join (select node_name,count(*) from backups where
(days(current_date) - days(a.backup_date) >= 30)
and a.state='ACTIVE_VERSION'
group by node_name) b on a.node_name=b.node_name


On Thu, Apr 09, 2020 at 10:53:09AM +, Loon, Eric van (ITOP NS) - KLM wrote:
> Hi Skylar,
>
> Sorry, but this one doesn't work either, it returns the same results as all 
> others. I don't think the NULL result is the issue here, it seems to be the 
> way the results are returned as soon as you select multiple columns. In the 
> following example ,when I select just one, the result is 0:
>
> select count(*) from backups where node_name='RAC_098-ORC' and 
> days(current_date) - days(backup_date) >= 3000
>
>   Unnamed[1]
> 
>0
>
> But as soon as you select multiple columns, the result is not 0, but "no 
> match found":
>
> select node_name, count(*) from backups where node_name='RAC_098-ORC' 
> and days(current_date) - days(backup_date) >= 3000 group by node_name 
> ANR2034E SELECT: No match found using this criteria.
> ANS8001I Return code 11.
>
> Thanks again for your help!
>
> Kind regards,
> Eric van Loon
> Air France/KLM Storage & Backup
>
>
>
> -Original Message-
> From: ADSM: Dist Stor Manager  On Behalf Of 
> Skylar Thompson
> Sent: woensdag 8 april 2020 16:03
> To: ADSM-L@VM.MARIST.EDU
> Subject: Re: SQL query
>
> Ah, I think the problem is that comparing anything with NULL will be NULL 
> (except comparing NULL with NULL, which is true). Try this:
>
> select b.node_name, count(*)
> from backups a
> right join nodes b on a.node_name=b.node_name and b.node_name like '%-ORC'
> where
> (a.backup_date is null or ((days(current_date) - days(a.backup_date) 
> >= 30)))
> and (a.state is null or a.state='ACTIVE_VERSION') group by 
> b.node_name
>
> Note that I also changed the "group by" and projection to use node_name from 
> the nodes table since that's guaranteed to be set, rather than backups which 
> would only be set for nodes with entries in the backups table.
>
> On Wed, Apr 08, 2020 at 08:26:42AM +, Loon, Eric van (ITOP NS) - KLM 
> wrote:
> > Hi Skylar,
> >
> > I tried your query, but it also returns just one node with a number > 0, 
> > all other nodes (which have 0 files) are not listed.
> > Thanks for your help!
> >
> > Kind regards,
> > Eric van Loon
> > Air France/KLM Storage & Backup
> >
> > -Original Message-
> > From: ADSM: Dist Stor Manager  On Behalf Of 
> > Skylar Thompson
> > Sent: dinsdag 7 april 2020 23:42
> > To: ADSM-L@VM.MARIST.EDU
> > Subject: Re: SQL query
> >
> > I think what you're looking for is an outer join:
> >
> > select a.node_name, count(*)
> > from backups a
> > right join nodes b on a.node_name=b.node_name and a.node_name like '%-ORC'
> > where
> > ((days(current_date) - days(backup_date) >= 30))
> > and a.state='ACTIVE_VERSION'
> > and b.contact like '%Oracle%'
> > group by a.node_name
> >
> > On Tue, Apr 07, 2020 at 09:08:58AM +, Loon, Eric van (ITOP NS) - KLM 
> > wrote:
> > > Hi guys,
> > >
> > > It must be something very easy, but I can't seem find the solution 
> > > myself... This is the query I use to list the total amount of Oracle 
> > > backup files older than 30 days:
> > >
> > > select c

Re: SQL query

[Skylar Thompson]

I forgot that GROUP BY depended on having an entry in the result table.
Unfortunately I don't have a TSM server with a reasonably-sized backups
table to test on (production ones are 1+ billion entries), so I'm kind of
in thought experiment territory right now, but what if you did an outer
join from the nodes table against a sub-query on the backups table? That
would let you replace the count for nodes without an entry in the sub-query
with 0 with CASE:

select
a.node_name,
case when b.count is null
then 0
else b.count
end as count
from nodes a
left join (select node_name,count(*) from backups where
(days(current_date) - days(a.backup_date) >= 30)
and a.state='ACTIVE_VERSION'
group by node_name) b on a.node_name=b.node_name


On Thu, Apr 09, 2020 at 10:53:09AM +, Loon, Eric van (ITOP NS) - KLM wrote:
> Hi Skylar,
>
> Sorry, but this one doesn't work either, it returns the same results as all 
> others. I don't think the NULL result is the issue here, it seems to be the 
> way the results are returned as soon as you select multiple columns. In the 
> following example ,when I select just one, the result is 0:
>
> select count(*) from backups where node_name='RAC_098-ORC' and 
> days(current_date) - days(backup_date) >= 3000
>
>   Unnamed[1]
> 
>0
>
> But as soon as you select multiple columns, the result is not 0, but "no 
> match found":
>
> select node_name, count(*) from backups where node_name='RAC_098-ORC' and 
> days(current_date) - days(backup_date) >= 3000 group by node_name
> ANR2034E SELECT: No match found using this criteria.
> ANS8001I Return code 11.
>
> Thanks again for your help!
>
> Kind regards,
> Eric van Loon
> Air France/KLM Storage & Backup
>
>
>
> -Original Message-
> From: ADSM: Dist Stor Manager  On Behalf Of Skylar 
> Thompson
> Sent: woensdag 8 april 2020 16:03
> To: ADSM-L@VM.MARIST.EDU
> Subject: Re: SQL query
>
> Ah, I think the problem is that comparing anything with NULL will be NULL 
> (except comparing NULL with NULL, which is true). Try this:
>
> select b.node_name, count(*)
> from backups a
> right join nodes b on a.node_name=b.node_name and b.node_name like '%-ORC'
> where
> (a.backup_date is null or ((days(current_date) - days(a.backup_date) 
> >= 30)))
> and (a.state is null or a.state='ACTIVE_VERSION') group by b.node_name
>
> Note that I also changed the "group by" and projection to use node_name from 
> the nodes table since that's guaranteed to be set, rather than backups which 
> would only be set for nodes with entries in the backups table.
>
> On Wed, Apr 08, 2020 at 08:26:42AM +, Loon, Eric van (ITOP NS) - KLM 
> wrote:
> > Hi Skylar,
> >
> > I tried your query, but it also returns just one node with a number > 0, 
> > all other nodes (which have 0 files) are not listed.
> > Thanks for your help!
> >
> > Kind regards,
> > Eric van Loon
> > Air France/KLM Storage & Backup
> >
> > -Original Message-
> > From: ADSM: Dist Stor Manager  On Behalf Of
> > Skylar Thompson
> > Sent: dinsdag 7 april 2020 23:42
> > To: ADSM-L@VM.MARIST.EDU
> > Subject: Re: SQL query
> >
> > I think what you're looking for is an outer join:
> >
> > select a.node_name, count(*)
> > from backups a
> > right join nodes b on a.node_name=b.node_name and a.node_name like '%-ORC'
> > where
> > ((days(current_date) - days(backup_date) >= 30))
> > and a.state='ACTIVE_VERSION'
> > and b.contact like '%Oracle%'
> > group by a.node_name
> >
> > On Tue, Apr 07, 2020 at 09:08:58AM +, Loon, Eric van (ITOP NS) - KLM 
> > wrote:
> > > Hi guys,
> > >
> > > It must be something very easy, but I can't seem find the solution 
> > > myself... This is the query I use to list the total amount of Oracle 
> > > backup files older than 30 days:
> > >
> > > select count(*) as OBSOLETE_BACKUPS from backups a,nodes b where 
> > > ((days(current_date) - days(backup_date) >= 30)) and a.node_name like 
> > > '%-ORC' and a.state='ACTIVE_VERSION' and a.node_name=b.node_name and 
> > > b.contact like '%Oracle%'
> > >
> > > I also use this statement to list the files per node:
> > >
> > > select a.node_name, count(*) from backups a,nodes b where
> > > ((days(current_date) - days(backup_date) >= 30)) and a.node_name
> > > like '%-ORC' and a.state='ACTIVE_VERSION' and
> > > a.node_name=b.node_name and b.contact like '%Oracle%' group by
> > > 

Re: SQL query

[Loon, Eric van (ITOP NS) - KLM]

Hi Skylar,

Sorry, but this one doesn't work either, it returns the same results as all 
others. I don't think the NULL result is the issue here, it seems to be the way 
the results are returned as soon as you select multiple columns. In the 
following example ,when I select just one, the result is 0:

select count(*) from backups where node_name='RAC_098-ORC' and 
days(current_date) - days(backup_date) >= 3000

  Unnamed[1]

   0

But as soon as you select multiple columns, the result is not 0, but "no match 
found":

select node_name, count(*) from backups where node_name='RAC_098-ORC' and 
days(current_date) - days(backup_date) >= 3000 group by node_name
ANR2034E SELECT: No match found using this criteria.
ANS8001I Return code 11.

Thanks again for your help!

Kind regards,
Eric van Loon
Air France/KLM Storage & Backup



-Original Message-
From: ADSM: Dist Stor Manager  On Behalf Of Skylar 
Thompson
Sent: woensdag 8 april 2020 16:03
To: ADSM-L@VM.MARIST.EDU
Subject: Re: SQL query

Ah, I think the problem is that comparing anything with NULL will be NULL 
(except comparing NULL with NULL, which is true). Try this:

select b.node_name, count(*)
from backups a
right join nodes b on a.node_name=b.node_name and b.node_name like '%-ORC'
where
(a.backup_date is null or ((days(current_date) - days(a.backup_date) >= 
30)))
and (a.state is null or a.state='ACTIVE_VERSION') group by b.node_name

Note that I also changed the "group by" and projection to use node_name from 
the nodes table since that's guaranteed to be set, rather than backups which 
would only be set for nodes with entries in the backups table.

On Wed, Apr 08, 2020 at 08:26:42AM +, Loon, Eric van (ITOP NS) - KLM wrote:
> Hi Skylar,
>
> I tried your query, but it also returns just one node with a number > 0, all 
> other nodes (which have 0 files) are not listed.
> Thanks for your help!
>
> Kind regards,
> Eric van Loon
> Air France/KLM Storage & Backup
>
> -Original Message-
> From: ADSM: Dist Stor Manager  On Behalf Of 
> Skylar Thompson
> Sent: dinsdag 7 april 2020 23:42
> To: ADSM-L@VM.MARIST.EDU
> Subject: Re: SQL query
>
> I think what you're looking for is an outer join:
>
> select a.node_name, count(*)
> from backups a
> right join nodes b on a.node_name=b.node_name and a.node_name like '%-ORC'
> where
>   ((days(current_date) - days(backup_date) >= 30))
>   and a.state='ACTIVE_VERSION'
>   and b.contact like '%Oracle%'
> group by a.node_name
>
> On Tue, Apr 07, 2020 at 09:08:58AM +, Loon, Eric van (ITOP NS) - KLM 
> wrote:
> > Hi guys,
> >
> > It must be something very easy, but I can't seem find the solution 
> > myself... This is the query I use to list the total amount of Oracle backup 
> > files older than 30 days:
> >
> > select count(*) as OBSOLETE_BACKUPS from backups a,nodes b where 
> > ((days(current_date) - days(backup_date) >= 30)) and a.node_name like 
> > '%-ORC' and a.state='ACTIVE_VERSION' and a.node_name=b.node_name and 
> > b.contact like '%Oracle%'
> >
> > I also use this statement to list the files per node:
> >
> > select a.node_name, count(*) from backups a,nodes b where
> > ((days(current_date) - days(backup_date) >= 30)) and a.node_name 
> > like '%-ORC' and a.state='ACTIVE_VERSION' and 
> > a.node_name=b.node_name and b.contact like '%Oracle%' group by 
> > a.node_name
> >
> > The statement works fine, but it only shows the nodes with an amount 
> > of files > 0. I'm looking for the same command which shows all %-ORC nodes. 
> > So when the amount is 0, it should display the node_name along with the 
> > value 0. I can't figure out how to accomplish it. :( Thanks for any help in 
> > advance!
> >
> > Kind regards,
> > Eric van Loon
> > Air France/KLM Storage & Backup
> > 
> > For information, services and offers, please visit our web site: 
> > http://www.klm.com. This e-mail and any attachment may contain confidential 
> > and privileged material intended for the addressee only. If you are not the 
> > addressee, you are notified that no part of the e-mail or any attachment 
> > may be disclosed, copied or distributed, and that any other action related 
> > to this e-mail or attachment is strictly prohibited, and may be unlawful. 
> > If you have received this e-mail by error, please notify the sender 
> > immediately by return e-mail, and delete this message.
> >
> > Koninklijke Luchtvaart Maatschappij NV (KLM), its subsidiaries and/or its 
> > employees shall not be liable for the incorrect or incomple

Re: SQL query

[Skylar Thompson]

Ah, I think the problem is that comparing anything with NULL will be NULL
(except comparing NULL with NULL, which is true). Try this:

select b.node_name, count(*)
from backups a
right join nodes b on a.node_name=b.node_name and b.node_name like '%-ORC'
where
(a.backup_date is null or ((days(current_date) - days(a.backup_date) >= 
30)))
and (a.state is null or a.state='ACTIVE_VERSION')
group by b.node_name

Note that I also changed the "group by" and projection to use node_name
from the nodes table since that's guaranteed to be set, rather than backups
which would only be set for nodes with entries in the backups table.

On Wed, Apr 08, 2020 at 08:26:42AM +, Loon, Eric van (ITOP NS) - KLM wrote:
> Hi Skylar,
>
> I tried your query, but it also returns just one node with a number > 0, all 
> other nodes (which have 0 files) are not listed.
> Thanks for your help!
>
> Kind regards,
> Eric van Loon
> Air France/KLM Storage & Backup
>
> -Original Message-
> From: ADSM: Dist Stor Manager  On Behalf Of Skylar 
> Thompson
> Sent: dinsdag 7 april 2020 23:42
> To: ADSM-L@VM.MARIST.EDU
> Subject: Re: SQL query
>
> I think what you're looking for is an outer join:
>
> select a.node_name, count(*)
> from backups a
> right join nodes b on a.node_name=b.node_name and a.node_name like '%-ORC'
> where
>   ((days(current_date) - days(backup_date) >= 30))
>   and a.state='ACTIVE_VERSION'
>   and b.contact like '%Oracle%'
> group by a.node_name
>
> On Tue, Apr 07, 2020 at 09:08:58AM +, Loon, Eric van (ITOP NS) - KLM 
> wrote:
> > Hi guys,
> >
> > It must be something very easy, but I can't seem find the solution 
> > myself... This is the query I use to list the total amount of Oracle backup 
> > files older than 30 days:
> >
> > select count(*) as OBSOLETE_BACKUPS from backups a,nodes b where 
> > ((days(current_date) - days(backup_date) >= 30)) and a.node_name like 
> > '%-ORC' and a.state='ACTIVE_VERSION' and a.node_name=b.node_name and 
> > b.contact like '%Oracle%'
> >
> > I also use this statement to list the files per node:
> >
> > select a.node_name, count(*) from backups a,nodes b where
> > ((days(current_date) - days(backup_date) >= 30)) and a.node_name like
> > '%-ORC' and a.state='ACTIVE_VERSION' and a.node_name=b.node_name and
> > b.contact like '%Oracle%' group by a.node_name
> >
> > The statement works fine, but it only shows the nodes with an amount
> > of files > 0. I'm looking for the same command which shows all %-ORC nodes. 
> > So when the amount is 0, it should display the node_name along with the 
> > value 0. I can't figure out how to accomplish it. :( Thanks for any help in 
> > advance!
> >
> > Kind regards,
> > Eric van Loon
> > Air France/KLM Storage & Backup
> > 
> > For information, services and offers, please visit our web site: 
> > http://www.klm.com. This e-mail and any attachment may contain confidential 
> > and privileged material intended for the addressee only. If you are not the 
> > addressee, you are notified that no part of the e-mail or any attachment 
> > may be disclosed, copied or distributed, and that any other action related 
> > to this e-mail or attachment is strictly prohibited, and may be unlawful. 
> > If you have received this e-mail by error, please notify the sender 
> > immediately by return e-mail, and delete this message.
> >
> > Koninklijke Luchtvaart Maatschappij NV (KLM), its subsidiaries and/or its 
> > employees shall not be liable for the incorrect or incomplete transmission 
> > of this e-mail or any attachments, nor responsible for any delay in receipt.
> > Koninklijke Luchtvaart Maatschappij N.V. (also known as KLM Royal
> > Dutch Airlines) is registered in Amstelveen, The Netherlands, with
> > registered number 33014286
> > 
>
> --
> -- Skylar Thompson (skyl...@u.washington.edu)
> -- Genome Sciences Department, System Administrator
> -- Foege Building S046, (206)-685-7354
> -- University of Washington School of Medicine
> 
> For information, services and offers, please visit our web site: 
> http://www.klm.com. This e-mail and any attachment may contain confidential 
> and privileged material intended for the addressee only. If you are not the 
> addressee, you are notified that no part of the e-mail or any attachment may 
> be disclosed, copied or distributed, and that any other action related to 
> this e-mail or attac

Re: SQL query

[Loon, Eric van (ITOP NS) - KLM]

Hi Skylar,

I tried your query, but it also returns just one node with a number > 0, all 
other nodes (which have 0 files) are not listed.
Thanks for your help!

Kind regards,
Eric van Loon
Air France/KLM Storage & Backup

-Original Message-
From: ADSM: Dist Stor Manager  On Behalf Of Skylar 
Thompson
Sent: dinsdag 7 april 2020 23:42
To: ADSM-L@VM.MARIST.EDU
Subject: Re: SQL query

I think what you're looking for is an outer join:

select a.node_name, count(*)
from backups a
right join nodes b on a.node_name=b.node_name and a.node_name like '%-ORC'
where
((days(current_date) - days(backup_date) >= 30))
and a.state='ACTIVE_VERSION'
and b.contact like '%Oracle%'
group by a.node_name

On Tue, Apr 07, 2020 at 09:08:58AM +, Loon, Eric van (ITOP NS) - KLM wrote:
> Hi guys,
>
> It must be something very easy, but I can't seem find the solution myself... 
> This is the query I use to list the total amount of Oracle backup files older 
> than 30 days:
>
> select count(*) as OBSOLETE_BACKUPS from backups a,nodes b where 
> ((days(current_date) - days(backup_date) >= 30)) and a.node_name like '%-ORC' 
> and a.state='ACTIVE_VERSION' and a.node_name=b.node_name and b.contact like 
> '%Oracle%'
>
> I also use this statement to list the files per node:
>
> select a.node_name, count(*) from backups a,nodes b where 
> ((days(current_date) - days(backup_date) >= 30)) and a.node_name like 
> '%-ORC' and a.state='ACTIVE_VERSION' and a.node_name=b.node_name and 
> b.contact like '%Oracle%' group by a.node_name
>
> The statement works fine, but it only shows the nodes with an amount 
> of files > 0. I'm looking for the same command which shows all %-ORC nodes. 
> So when the amount is 0, it should display the node_name along with the value 
> 0. I can't figure out how to accomplish it. :( Thanks for any help in advance!
>
> Kind regards,
> Eric van Loon
> Air France/KLM Storage & Backup
> 
> For information, services and offers, please visit our web site: 
> http://www.klm.com. This e-mail and any attachment may contain confidential 
> and privileged material intended for the addressee only. If you are not the 
> addressee, you are notified that no part of the e-mail or any attachment may 
> be disclosed, copied or distributed, and that any other action related to 
> this e-mail or attachment is strictly prohibited, and may be unlawful. If you 
> have received this e-mail by error, please notify the sender immediately by 
> return e-mail, and delete this message.
>
> Koninklijke Luchtvaart Maatschappij NV (KLM), its subsidiaries and/or its 
> employees shall not be liable for the incorrect or incomplete transmission of 
> this e-mail or any attachments, nor responsible for any delay in receipt.
> Koninklijke Luchtvaart Maatschappij N.V. (also known as KLM Royal 
> Dutch Airlines) is registered in Amstelveen, The Netherlands, with 
> registered number 33014286
> 

--
-- Skylar Thompson (skyl...@u.washington.edu)
-- Genome Sciences Department, System Administrator
-- Foege Building S046, (206)-685-7354
-- University of Washington School of Medicine

For information, services and offers, please visit our web site: 
http://www.klm.com. This e-mail and any attachment may contain confidential and 
privileged material intended for the addressee only. If you are not the 
addressee, you are notified that no part of the e-mail or any attachment may be 
disclosed, copied or distributed, and that any other action related to this 
e-mail or attachment is strictly prohibited, and may be unlawful. If you have 
received this e-mail by error, please notify the sender immediately by return 
e-mail, and delete this message.

Koninklijke Luchtvaart Maatschappij NV (KLM), its subsidiaries and/or its 
employees shall not be liable for the incorrect or incomplete transmission of 
this e-mail or any attachments, nor responsible for any delay in receipt.
Koninklijke Luchtvaart Maatschappij N.V. (also known as KLM Royal Dutch 
Airlines) is registered in Amstelveen, The Netherlands, with registered number 
33014286

Re: SQL query

[Skylar Thompson]

I think what you're looking for is an outer join:

select a.node_name, count(*)
from backups a
right join nodes b on a.node_name=b.node_name and a.node_name like '%-ORC'
where
((days(current_date) - days(backup_date) >= 30))
and a.state='ACTIVE_VERSION'
and b.contact like '%Oracle%'
group by a.node_name

On Tue, Apr 07, 2020 at 09:08:58AM +, Loon, Eric van (ITOP NS) - KLM wrote:
> Hi guys,
>
> It must be something very easy, but I can't seem find the solution myself... 
> This is the query I use to list the total amount of Oracle backup files older 
> than 30 days:
>
> select count(*) as OBSOLETE_BACKUPS from backups a,nodes b where 
> ((days(current_date) - days(backup_date) >= 30)) and a.node_name like '%-ORC' 
> and a.state='ACTIVE_VERSION' and a.node_name=b.node_name and b.contact like 
> '%Oracle%'
>
> I also use this statement to list the files per node:
>
> select a.node_name, count(*) from backups a,nodes b where 
> ((days(current_date) - days(backup_date) >= 30)) and a.node_name like '%-ORC' 
> and a.state='ACTIVE_VERSION' and a.node_name=b.node_name and b.contact like 
> '%Oracle%' group by a.node_name
>
> The statement works fine, but it only shows the nodes with an amount of files 
> > 0. I'm looking for the same command which shows all %-ORC nodes. So when 
> the amount is 0, it should display the node_name along with the value 0. I 
> can't figure out how to accomplish it. :(
> Thanks for any help in advance!
>
> Kind regards,
> Eric van Loon
> Air France/KLM Storage & Backup
> 
> For information, services and offers, please visit our web site: 
> http://www.klm.com. This e-mail and any attachment may contain confidential 
> and privileged material intended for the addressee only. If you are not the 
> addressee, you are notified that no part of the e-mail or any attachment may 
> be disclosed, copied or distributed, and that any other action related to 
> this e-mail or attachment is strictly prohibited, and may be unlawful. If you 
> have received this e-mail by error, please notify the sender immediately by 
> return e-mail, and delete this message.
>
> Koninklijke Luchtvaart Maatschappij NV (KLM), its subsidiaries and/or its 
> employees shall not be liable for the incorrect or incomplete transmission of 
> this e-mail or any attachments, nor responsible for any delay in receipt.
> Koninklijke Luchtvaart Maatschappij N.V. (also known as KLM Royal Dutch 
> Airlines) is registered in Amstelveen, The Netherlands, with registered 
> number 33014286
> 

--
-- Skylar Thompson (skyl...@u.washington.edu)
-- Genome Sciences Department, System Administrator
-- Foege Building S046, (206)-685-7354
-- University of Washington School of Medicine

SQL query

[Loon, Eric van (ITOP NS) - KLM]

Hi guys,

It must be something very easy, but I can't seem find the solution myself... 
This is the query I use to list the total amount of Oracle backup files older 
than 30 days:

select count(*) as OBSOLETE_BACKUPS from backups a,nodes b where 
((days(current_date) - days(backup_date) >= 30)) and a.node_name like '%-ORC' 
and a.state='ACTIVE_VERSION' and a.node_name=b.node_name and b.contact like 
'%Oracle%'

I also use this statement to list the files per node:

select a.node_name, count(*) from backups a,nodes b where ((days(current_date) 
- days(backup_date) >= 30)) and a.node_name like '%-ORC' and 
a.state='ACTIVE_VERSION' and a.node_name=b.node_name and b.contact like 
'%Oracle%' group by a.node_name

The statement works fine, but it only shows the nodes with an amount of files > 
0. I'm looking for the same command which shows all %-ORC nodes. So when the 
amount is 0, it should display the node_name along with the value 0. I can't 
figure out how to accomplish it. :(
Thanks for any help in advance!

Kind regards,
Eric van Loon
Air France/KLM Storage & Backup

For information, services and offers, please visit our web site: 
http://www.klm.com. This e-mail and any attachment may contain confidential and 
privileged material intended for the addressee only. If you are not the 
addressee, you are notified that no part of the e-mail or any attachment may be 
disclosed, copied or distributed, and that any other action related to this 
e-mail or attachment is strictly prohibited, and may be unlawful. If you have 
received this e-mail by error, please notify the sender immediately by return 
e-mail, and delete this message.

Koninklijke Luchtvaart Maatschappij NV (KLM), its subsidiaries and/or its 
employees shall not be liable for the incorrect or incomplete transmission of 
this e-mail or any attachments, nor responsible for any delay in receipt.
Koninklijke Luchtvaart Maatschappij N.V. (also known as KLM Royal Dutch 
Airlines) is registered in Amstelveen, The Netherlands, with registered number 
33014286

Re: SQL QUERY FOR AMOUNT OF ACTIVE VS INACTIVE DATA

[Roger Deschner]

start the session with -virtualnodename.  It works with dsmc
>> or dsmj, and avoids the need for the proxy setting.
>>
>> Jim Schneider
>> Essendant
>>
>> -Original Message-
>> From: ADSM: Dist Stor Manager [mailto:ADSM-L@VM.MARIST.EDU] On Behalf Of
>> Skylar Thompson
>> Sent: Friday, April 22, 2016 9:30 AM
>> To: ADSM-L@VM.MARIST.EDU
>> Subject: Re: [ADSM-L] SQL QUERY FOR AMOUNT OF ACTIVE VS INACTIVE DATA
>>
>> You can also GRANT PROXY and then use -ASNODE from one of your own nodes,
>> using your node's password. I think the general node type has to match
>> (i.e. any UNIX can proxy to any UNIX, but not Windows).
>>
>> On Fri, Apr 22, 2016 at 02:20:38PM +, Schneider, Jim wrote:
>> > Use a server you can access and modify the nodename in the options file,
>> assuming you know the password.
>> >
>> > Jim Schneider
>> > Essendant
>> >
>> > -Original Message-
>> > From: ADSM: Dist Stor Manager [mailto:ADSM-L@VM.MARIST.EDU] On Behalf
>> > Of Lee, Gary
>> > Sent: Friday, April 22, 2016 9:11 AM
>> > To: ADSM-L@VM.MARIST.EDU
>> > Subject: Re: [ADSM-L] SQL QUERY FOR AMOUNT OF ACTIVE VS INACTIVE DATA
>> >
>> > Wish I could do that.  This comes from three levels above me in
>> management.
>> > Trying to buy more storage to sell to departments.
>> > Don't ask me, I have no clue what they are doing.
>> >
>> > I'll look into the q backup on client side, but don't have access to all
>> of them.
>> >
>> >
>> > -Original Message-
>> > From: ADSM: Dist Stor Manager [mailto:ADSM-L@VM.MARIST.EDU] On Behalf
>> > Of Skylar Thompson
>> > Sent: Friday, April 22, 2016 10:00 AM
>> > To: ADSM-L@VM.MARIST.EDU
>> > Subject: Re: [ADSM-L] SQL QUERY FOR AMOUNT OF ACTIVE VS INACTIVE DATA
>> >
>> > If you have access to the clients, you can use QUERY BACKUP and parse
>> the A/I column.
>> >
>> > Honestly, though, when we've gotten this query, I've managed to push
>> this back on the customers; it's not TSM's problem what's active or
>> inactive, it's the customers' applications that are actually responsible
>> for it.
>> > Obviously you need a pretty good relationship with your customers to
>> make that case, but in the end it's caused our customers to think more
>> carefully about workflow in general.
>> >
>> > On Fri, Apr 22, 2016 at 01:51:20PM +, Lee, Gary wrote:
>> > > Just got a request for the amount of active versus inactive data on
>> our tsm servers.
>> > >
>> > > Is there a better way than traversing the backups table and summing?
>> > > That would be a mighty long query.
>> > >
>> > > We have three servers, and approximately 300 clients about 200 tB
>> total data.
>> > >
>> > > Thanks for any suggestions.
>> >
>> > --
>> > -- Skylar Thompson (skyl...@u.washington.edu)
>> > -- Genome Sciences Department, System Administrator
>> > -- Foege Building S046, (206)-685-7354
>> > -- University of Washington School of Medicine
>> >
>> > **
>> > Information contained in this e-mail message and in any attachments
>> thereto is confidential. If you are not the intended recipient, please
>> destroy this message, delete any copies held on your systems, notify the
>> sender immediately, and refrain from using or disclosing all or any part of
>> its content to any other person.
>>
>> --
>> -- Skylar Thompson (skyl...@u.washington.edu)
>> -- Genome Sciences Department, System Administrator
>> -- Foege Building S046, (206)-685-7354
>> -- University of Washington School of Medicine
>>
>

Re: SQL QUERY FOR AMOUNT OF ACTIVE VS INACTIVE DATA

[Maurice van 't Loo]

Hello Gary,

Just guessing the actual reason, it might be that they want to know the
amount of TSM storage compared with the amount of storage on the clients.
While counting the number of objects doesn't give you much information,
maybe it's best to compare the filespaces with occupancy. That is what I
call the "abuse factor"

NODE
FS
FS_GB  OCC_GB ABUSE
---
-
- --- -
XX
/csminstall/AIX/images
35.06  149.36   4.2
XXX
/nim/aix54
39.13   39.15   1.0

/csminstall/AIX/aix610
37.07   36.99   0.9

/home/dvpt
14.07   35.39   2.5
X
/build
24.28   33.24   1.3

/db2/dvptdb
1.57   30.48  19.3
XX
/home/dvpt
30.57   30.42   0.9
X
/csminstall/AIX/products
25.65   25.77   1.0
XX
/build
22.53   21.89   0.9

/build
22.53   21.89   0.9

Copy/paste this in mono-type font (notepad) to get it better readable.
I use this to hunt for missing excludes (mssql databases not excluded) but
I can also use it to calculate how much space active and inactive I roughly
have. Of course filespaces with excludes gives some mismatch.

SQL used for above output:
select cast(substr(f.NODE_NAME,1,30) as char(30)) as
NODE,cast(substr(f.FILESPACE_NAME,1,30) as char(30)) as
FS,dec(f.CAPACITY*f.PCT_UTIL/100/1024,14,2) as
FS_GB,dec(sum(o.PHYSICAL_MB)/1024,12,2) as
OCC_GB,dec(dec(sum(o.PHYSICAL_MB),14,1)/dec(f.CAPACITY*f.PCT_UTIL/100,16,1),14,1)
as ABUSE from filespaces as f,occupancy as o where f.NODE_NAME=o.NODE_NAME
and f.FILESPACE_NAME=o.FILESPACE_NAME and f.CAPACITY>0 and f.PCT_UTIL>0 and
o.STGPOOL_NAME in (select stgpool_name from stgpools where
pooltype='PRIMARY') and o.TYPE='Bkup' group by
o.NODE_NAME,o.FILESPACE_NAME,f.NODE_NAME,f.FILESPACE_NAME,f.CAPACITY,f.PCT_UTIL
order by 4 desc fetch first 10 rows only

For all nodes ordered by nodename:
select cast(substr(f.NODE_NAME,1,30) as char(30)) as
NODE,cast(substr(f.FILESPACE_NAME,1,30) as char(30)) as
FS,dec(f.CAPACITY*f.PCT_UTIL/100/1024,14,2) as
FS_GB,dec(sum(o.PHYSICAL_MB)/1024,12,2) as
OCC_GB,dec(dec(sum(o.PHYSICAL_MB),14,1)/dec(f.CAPACITY*f.PCT_UTIL/100,16,1),14,1)
as ABUSE from filespaces as f,occupancy as o where f.NODE_NAME=o.NODE_NAME
and f.FILESPACE_NAME=o.FILESPACE_NAME and f.CAPACITY>0 and f.PCT_UTIL>0 and
o.STGPOOL_NAME in (select stgpool_name from stgpools where
pooltype='PRIMARY') and o.TYPE='Bkup' group by
o.NODE_NAME,o.FILESPACE_NAME,f.NODE_NAME,f.FILESPACE_NAME,f.CAPACITY,f.PCT_UTIL
order by 1

Regards,
Maurice van 't Loo

http://mvantloo.nl/maupack.php
Personal pack of selects (in scripts)


2016-04-22 16:35 GMT+02:00 Schneider, Jim <jschnei...@essendant.com>:

> You can also start the session with -virtualnodename.  It works with dsmc
> or dsmj, and avoids the need for the proxy setting.
>
> Jim Schneider
> Essendant
>
> -Original Message-
> From: ADSM: Dist Stor Manager [mailto:ADSM-L@VM.MARIST.EDU] On Behalf Of
> Skylar Thompson
> Sent: Friday, April 22, 2016 9:30 AM
> To: ADSM-L@VM.MARIST.EDU
> Subject: Re: [ADSM-L] SQL QUERY FOR AMOUNT OF ACTIVE VS INACTIVE DATA
>
> You can also GRANT PROXY and then use -ASNODE from one of your own nodes,
> using your node's password. I think the general node type has to match
> (i.e. any UNIX can proxy to any UNIX, but not Windows).
>
> On Fri, Apr 22, 2016 at 02:20:38PM +, Schneider, Jim wrote:
> > Use a server you can access and modify the nodename in the options file,
> assuming you know the password.
> >
> > Jim Schneider
> > Essendant
> >
> > -Original Message-
> > From: ADSM: Dist Stor Manager [mailto:ADSM-L@VM.MARIST.EDU] On Behalf
> > Of Lee, Gary
> > Sent: Friday, April 22, 2016 9:11 AM
> > To: ADSM-L@VM.MARIST.EDU
> > Subject: Re: [ADSM-L] SQL QUERY FOR AMOUNT OF ACTIVE VS INACTIVE DATA
> >
> > Wish I could do that.  This comes from three levels above me in
> management.
> > Trying to buy more storage to sell to departments.
> > Don't ask me, I have no clue what they are doing.
> >
> > I'll look into the q backup on client side, but don't have access to all
> of them.
> >
> >
> > -Original Message-
> > From: ADSM: Dist Stor Manager [mailto:ADSM-L@VM.MARIST.EDU] On Behalf
> > Of Skylar Thompson
> > Sent: Friday, April 22, 2016 10:00 AM
> > To: ADSM-L@VM.MARIST.EDU
> > S

Re: SQL QUERY FOR AMOUNT OF ACTIVE VS INACTIVE DATA

[Schneider, Jim]

You can also start the session with -virtualnodename.  It works with dsmc or 
dsmj, and avoids the need for the proxy setting.

Jim Schneider
Essendant

-Original Message-
From: ADSM: Dist Stor Manager [mailto:ADSM-L@VM.MARIST.EDU] On Behalf Of Skylar 
Thompson
Sent: Friday, April 22, 2016 9:30 AM
To: ADSM-L@VM.MARIST.EDU
Subject: Re: [ADSM-L] SQL QUERY FOR AMOUNT OF ACTIVE VS INACTIVE DATA

You can also GRANT PROXY and then use -ASNODE from one of your own nodes, using 
your node's password. I think the general node type has to match (i.e. any UNIX 
can proxy to any UNIX, but not Windows).

On Fri, Apr 22, 2016 at 02:20:38PM +, Schneider, Jim wrote:
> Use a server you can access and modify the nodename in the options file, 
> assuming you know the password.
>
> Jim Schneider
> Essendant
>
> -Original Message-
> From: ADSM: Dist Stor Manager [mailto:ADSM-L@VM.MARIST.EDU] On Behalf 
> Of Lee, Gary
> Sent: Friday, April 22, 2016 9:11 AM
> To: ADSM-L@VM.MARIST.EDU
> Subject: Re: [ADSM-L] SQL QUERY FOR AMOUNT OF ACTIVE VS INACTIVE DATA
>
> Wish I could do that.  This comes from three levels above me in management.
> Trying to buy more storage to sell to departments.
> Don't ask me, I have no clue what they are doing.
>
> I'll look into the q backup on client side, but don't have access to all of 
> them.
>
>
> -Original Message-
> From: ADSM: Dist Stor Manager [mailto:ADSM-L@VM.MARIST.EDU] On Behalf 
> Of Skylar Thompson
> Sent: Friday, April 22, 2016 10:00 AM
> To: ADSM-L@VM.MARIST.EDU
> Subject: Re: [ADSM-L] SQL QUERY FOR AMOUNT OF ACTIVE VS INACTIVE DATA
>
> If you have access to the clients, you can use QUERY BACKUP and parse the A/I 
> column.
>
> Honestly, though, when we've gotten this query, I've managed to push this 
> back on the customers; it's not TSM's problem what's active or inactive, it's 
> the customers' applications that are actually responsible for it.
> Obviously you need a pretty good relationship with your customers to make 
> that case, but in the end it's caused our customers to think more carefully 
> about workflow in general.
>
> On Fri, Apr 22, 2016 at 01:51:20PM +, Lee, Gary wrote:
> > Just got a request for the amount of active versus inactive data on our tsm 
> > servers.
> >
> > Is there a better way than traversing the backups table and summing?
> > That would be a mighty long query.
> >
> > We have three servers, and approximately 300 clients about 200 tB total 
> > data.
> >
> > Thanks for any suggestions.
>
> --
> -- Skylar Thompson (skyl...@u.washington.edu)
> -- Genome Sciences Department, System Administrator
> -- Foege Building S046, (206)-685-7354
> -- University of Washington School of Medicine
>
> **
> Information contained in this e-mail message and in any attachments thereto 
> is confidential. If you are not the intended recipient, please destroy this 
> message, delete any copies held on your systems, notify the sender 
> immediately, and refrain from using or disclosing all or any part of its 
> content to any other person.

--
-- Skylar Thompson (skyl...@u.washington.edu)
-- Genome Sciences Department, System Administrator
-- Foege Building S046, (206)-685-7354
-- University of Washington School of Medicine

Re: SQL QUERY FOR AMOUNT OF ACTIVE VS INACTIVE DATA

[Skylar Thompson]

You can also GRANT PROXY and then use -ASNODE from one of your own nodes,
using your node's password. I think the general node type has to match
(i.e. any UNIX can proxy to any UNIX, but not Windows).

On Fri, Apr 22, 2016 at 02:20:38PM +, Schneider, Jim wrote:
> Use a server you can access and modify the nodename in the options file, 
> assuming you know the password.
>
> Jim Schneider
> Essendant
>
> -Original Message-
> From: ADSM: Dist Stor Manager [mailto:ADSM-L@VM.MARIST.EDU] On Behalf Of Lee, 
> Gary
> Sent: Friday, April 22, 2016 9:11 AM
> To: ADSM-L@VM.MARIST.EDU
> Subject: Re: [ADSM-L] SQL QUERY FOR AMOUNT OF ACTIVE VS INACTIVE DATA
>
> Wish I could do that.  This comes from three levels above me in management.
> Trying to buy more storage to sell to departments.
> Don't ask me, I have no clue what they are doing.
>
> I'll look into the q backup on client side, but don't have access to all of 
> them.
>
>
> -Original Message-
> From: ADSM: Dist Stor Manager [mailto:ADSM-L@VM.MARIST.EDU] On Behalf Of 
> Skylar Thompson
> Sent: Friday, April 22, 2016 10:00 AM
> To: ADSM-L@VM.MARIST.EDU
> Subject: Re: [ADSM-L] SQL QUERY FOR AMOUNT OF ACTIVE VS INACTIVE DATA
>
> If you have access to the clients, you can use QUERY BACKUP and parse the A/I 
> column.
>
> Honestly, though, when we've gotten this query, I've managed to push this 
> back on the customers; it's not TSM's problem what's active or inactive, it's 
> the customers' applications that are actually responsible for it.
> Obviously you need a pretty good relationship with your customers to make 
> that case, but in the end it's caused our customers to think more carefully 
> about workflow in general.
>
> On Fri, Apr 22, 2016 at 01:51:20PM +, Lee, Gary wrote:
> > Just got a request for the amount of active versus inactive data on our tsm 
> > servers.
> >
> > Is there a better way than traversing the backups table and summing?
> > That would be a mighty long query.
> >
> > We have three servers, and approximately 300 clients about 200 tB total 
> > data.
> >
> > Thanks for any suggestions.
>
> --
> -- Skylar Thompson (skyl...@u.washington.edu)
> -- Genome Sciences Department, System Administrator
> -- Foege Building S046, (206)-685-7354
> -- University of Washington School of Medicine
>
> **
> Information contained in this e-mail message and in any attachments thereto 
> is confidential. If you are not the intended recipient, please destroy this 
> message, delete any copies held on your systems, notify the sender 
> immediately, and refrain from using or disclosing all or any part of its 
> content to any other person.

--
-- Skylar Thompson (skyl...@u.washington.edu)
-- Genome Sciences Department, System Administrator
-- Foege Building S046, (206)-685-7354
-- University of Washington School of Medicine

Re: SQL QUERY FOR AMOUNT OF ACTIVE VS INACTIVE DATA

[Skylar Thompson]

Thanks for reminding me about this, I had completely forgotten. At the
time, the customers wanted per-directory stats so it wasn't an option for
us.

On Fri, Apr 22, 2016 at 10:11:27AM -0400, Zoltan Forray wrote:
> Per this document, IBM's suggestion/recommendation is to perform EXPORT
> NODE  FILED=BACKUPActive PREVIEW=YES
>
> http://www-01.ibm.com/support/docview.wss?uid=swg21267260
>
> On Fri, Apr 22, 2016 at 10:00 AM, Skylar Thompson 
> wrote:
>
> > If you have access to the clients, you can use QUERY BACKUP and parse the
> > A/I column.
> >
> > Honestly, though, when we've gotten this query, I've managed to push this
> > back on the customers; it's not TSM's problem what's active or inactive,
> > it's the customers' applications that are actually responsible for it.
> > Obviously you need a pretty good relationship with your customers to make
> > that case, but in the end it's caused our customers to think more carefully
> > about workflow in general.
> >
> > On Fri, Apr 22, 2016 at 01:51:20PM +, Lee, Gary wrote:
> > > Just got a request for the amount of active versus inactive data on our
> > tsm servers.
> > >
> > > Is there a better way than traversing the backups table and summing?
> > > That would be a mighty long query.
> > >
> > > We have three servers, and approximately 300 clients about 200 tB total
> > data.
> > >
> > > Thanks for any suggestions.
> >
> > --
> > -- Skylar Thompson (skyl...@u.washington.edu)
> > -- Genome Sciences Department, System Administrator
> > -- Foege Building S046, (206)-685-7354
> > -- University of Washington School of Medicine
> >
>
>
>
> --
> *Zoltan Forray*
> TSM Software & Hardware Administrator
> Xymon Monitor Administrator
> VMware Administrator (in training)
> Virginia Commonwealth University
> UCC/Office of Technology Services
> www.ucc.vcu.edu
> zfor...@vcu.edu - 804-828-4807
> Don't be a phishing victim - VCU and other reputable organizations will
> never use email to request that you reply with your password, social
> security number or confidential personal information. For more details
> visit http://infosecurity.vcu.edu/phishing.html

--
-- Skylar Thompson (skyl...@u.washington.edu)
-- Genome Sciences Department, System Administrator
-- Foege Building S046, (206)-685-7354
-- University of Washington School of Medicine

Re: SQL QUERY FOR AMOUNT OF ACTIVE VS INACTIVE DATA

[Schneider, Jim]

Use a server you can access and modify the nodename in the options file, 
assuming you know the password.

Jim Schneider
Essendant

-Original Message-
From: ADSM: Dist Stor Manager [mailto:ADSM-L@VM.MARIST.EDU] On Behalf Of Lee, 
Gary
Sent: Friday, April 22, 2016 9:11 AM
To: ADSM-L@VM.MARIST.EDU
Subject: Re: [ADSM-L] SQL QUERY FOR AMOUNT OF ACTIVE VS INACTIVE DATA

Wish I could do that.  This comes from three levels above me in management.
Trying to buy more storage to sell to departments.
Don't ask me, I have no clue what they are doing.

I'll look into the q backup on client side, but don't have access to all of 
them.


-Original Message-
From: ADSM: Dist Stor Manager [mailto:ADSM-L@VM.MARIST.EDU] On Behalf Of Skylar 
Thompson
Sent: Friday, April 22, 2016 10:00 AM
To: ADSM-L@VM.MARIST.EDU
Subject: Re: [ADSM-L] SQL QUERY FOR AMOUNT OF ACTIVE VS INACTIVE DATA

If you have access to the clients, you can use QUERY BACKUP and parse the A/I 
column.

Honestly, though, when we've gotten this query, I've managed to push this back 
on the customers; it's not TSM's problem what's active or inactive, it's the 
customers' applications that are actually responsible for it.
Obviously you need a pretty good relationship with your customers to make that 
case, but in the end it's caused our customers to think more carefully about 
workflow in general.

On Fri, Apr 22, 2016 at 01:51:20PM +, Lee, Gary wrote:
> Just got a request for the amount of active versus inactive data on our tsm 
> servers.
>
> Is there a better way than traversing the backups table and summing?
> That would be a mighty long query.
>
> We have three servers, and approximately 300 clients about 200 tB total data.
>
> Thanks for any suggestions.

--
-- Skylar Thompson (skyl...@u.washington.edu)
-- Genome Sciences Department, System Administrator
-- Foege Building S046, (206)-685-7354
-- University of Washington School of Medicine

**
Information contained in this e-mail message and in any attachments thereto is 
confidential. If you are not the intended recipient, please destroy this 
message, delete any copies held on your systems, notify the sender immediately, 
and refrain from using or disclosing all or any part of its content to any 
other person.

Re: SQL QUERY FOR AMOUNT OF ACTIVE VS INACTIVE DATA

[Zoltan Forray]

Per this document, IBM's suggestion/recommendation is to perform EXPORT
NODE  FILED=BACKUPActive PREVIEW=YES

http://www-01.ibm.com/support/docview.wss?uid=swg21267260

On Fri, Apr 22, 2016 at 10:00 AM, Skylar Thompson 
wrote:

> If you have access to the clients, you can use QUERY BACKUP and parse the
> A/I column.
>
> Honestly, though, when we've gotten this query, I've managed to push this
> back on the customers; it's not TSM's problem what's active or inactive,
> it's the customers' applications that are actually responsible for it.
> Obviously you need a pretty good relationship with your customers to make
> that case, but in the end it's caused our customers to think more carefully
> about workflow in general.
>
> On Fri, Apr 22, 2016 at 01:51:20PM +, Lee, Gary wrote:
> > Just got a request for the amount of active versus inactive data on our
> tsm servers.
> >
> > Is there a better way than traversing the backups table and summing?
> > That would be a mighty long query.
> >
> > We have three servers, and approximately 300 clients about 200 tB total
> data.
> >
> > Thanks for any suggestions.
>
> --
> -- Skylar Thompson (skyl...@u.washington.edu)
> -- Genome Sciences Department, System Administrator
> -- Foege Building S046, (206)-685-7354
> -- University of Washington School of Medicine
>



--
*Zoltan Forray*
TSM Software & Hardware Administrator
Xymon Monitor Administrator
VMware Administrator (in training)
Virginia Commonwealth University
UCC/Office of Technology Services
www.ucc.vcu.edu
zfor...@vcu.edu - 804-828-4807
Don't be a phishing victim - VCU and other reputable organizations will
never use email to request that you reply with your password, social
security number or confidential personal information. For more details
visit http://infosecurity.vcu.edu/phishing.html

Re: SQL QUERY FOR AMOUNT OF ACTIVE VS INACTIVE DATA

[Lee, Gary]

Wish I could do that.  This comes from three levels above me in management.
Trying to buy more storage to sell to departments.
Don't ask me, I have no clue what they are doing.

I'll look into the q backup on client side, but don't have access to all of 
them.


-Original Message-
From: ADSM: Dist Stor Manager [mailto:ADSM-L@VM.MARIST.EDU] On Behalf Of Skylar 
Thompson
Sent: Friday, April 22, 2016 10:00 AM
To: ADSM-L@VM.MARIST.EDU
Subject: Re: [ADSM-L] SQL QUERY FOR AMOUNT OF ACTIVE VS INACTIVE DATA

If you have access to the clients, you can use QUERY BACKUP and parse the
A/I column.

Honestly, though, when we've gotten this query, I've managed to push this
back on the customers; it's not TSM's problem what's active or inactive,
it's the customers' applications that are actually responsible for it.
Obviously you need a pretty good relationship with your customers to make
that case, but in the end it's caused our customers to think more carefully
about workflow in general.

On Fri, Apr 22, 2016 at 01:51:20PM +, Lee, Gary wrote:
> Just got a request for the amount of active versus inactive data on our tsm 
> servers.
>
> Is there a better way than traversing the backups table and summing?
> That would be a mighty long query.
>
> We have three servers, and approximately 300 clients about 200 tB total data.
>
> Thanks for any suggestions.

--
-- Skylar Thompson (skyl...@u.washington.edu)
-- Genome Sciences Department, System Administrator
-- Foege Building S046, (206)-685-7354
-- University of Washington School of Medicine

Re: SQL QUERY FOR AMOUNT OF ACTIVE VS INACTIVE DATA

[Skylar Thompson]

If you have access to the clients, you can use QUERY BACKUP and parse the
A/I column.

Honestly, though, when we've gotten this query, I've managed to push this
back on the customers; it's not TSM's problem what's active or inactive,
it's the customers' applications that are actually responsible for it.
Obviously you need a pretty good relationship with your customers to make
that case, but in the end it's caused our customers to think more carefully
about workflow in general.

On Fri, Apr 22, 2016 at 01:51:20PM +, Lee, Gary wrote:
> Just got a request for the amount of active versus inactive data on our tsm 
> servers.
>
> Is there a better way than traversing the backups table and summing?
> That would be a mighty long query.
>
> We have three servers, and approximately 300 clients about 200 tB total data.
>
> Thanks for any suggestions.

--
-- Skylar Thompson (skyl...@u.washington.edu)
-- Genome Sciences Department, System Administrator
-- Foege Building S046, (206)-685-7354
-- University of Washington School of Medicine

SQL QUERY FOR AMOUNT OF ACTIVE VS INACTIVE DATA

[Lee, Gary]

Just got a request for the amount of active versus inactive data on our tsm 
servers.

Is there a better way than traversing the backups table and summing?
That would be a mighty long query.

We have three servers, and approximately 300 clients about 200 tB total data.

Thanks for any suggestions.

SQL Query for replstate

[Vandeventer, Harold [BS]]

I need to run a query (such as SELECT * FROM tablename WHERE something = 
'ENABLED') to list nodes and their replstate status But I don't find 
REPLSTATE as a column in the NODES table.

And I don't see a column name that seems related.

Q REPLNODE * returns multiple rows for each node; but REPLNODE is not a table.

Does anyone know where the replication status is stored?

Thanks.

Harold Vandeventer
Systems Programmer
State of Kansas - Office of Information Technology Services
STE 751-S
910 SW Jackson
(785) 296-0631


[Confidentiality notice:]
***
This e-mail message, including attachments, if any, is intended for the
person or entity to which it is addressed and may contain confidential
or privileged information.  Any unauthorized review, use, or disclosure
is prohibited.  If you are not the intended recipient, please contact
the sender and destroy the original message, including all copies,
Thank you.
***

Re: SQL Query for replstate

[Erwann Simon]

Hi Harold,

It may be diffrent with another version of TSM, but this is from a 6.3.4 :

TSM select distinct repl_state from nodes

REPL_STATE 
---
ENABLED
NONE   


-- 
Best regards / Cordialement / مع تحياتي
Erwann SIMON

- Mail original -
De: Harold Vandeventer [BS] harold.vandeven...@ks.gov
À: ADSM-L@VM.MARIST.EDU
Envoyé: Mardi 20 Mai 2014 19:18:06
Objet: [ADSM-L] SQL Query for replstate

I need to run a query (such as SELECT * FROM tablename WHERE something = 
'ENABLED') to list nodes and their replstate status But I don't find 
REPLSTATE as a column in the NODES table.

And I don't see a column name that seems related.

Q REPLNODE * returns multiple rows for each node; but REPLNODE is not a table.

Does anyone know where the replication status is stored?

Thanks.

Harold Vandeventer
Systems Programmer
State of Kansas - Office of Information Technology Services
STE 751-S
910 SW Jackson
(785) 296-0631


[Confidentiality notice:]
***
This e-mail message, including attachments, if any, is intended for the
person or entity to which it is addressed and may contain confidential
or privileged information.  Any unauthorized review, use, or disclosure
is prohibited.  If you are not the intended recipient, please contact
the sender and destroy the original message, including all copies,
Thank you.
***

Re: SQL query in v6 server

[Neil Schofield]

Eric

I'm guessing from your email that you've managed to change the packaged
SQL to work with v6 and it's only your bespoke customisations that are
posing a challenge.

For anyone else attempting a similar change this is a great resource for
'tweaking' the vanilla Operational Reporting to work with v6 servers.

http://www-01.ibm.com/support/docview.wss?uid=swg27019794

Regards
Neil


Neil Schofield
Infrastructure Architect, Hardware Engineering
Yorkshire Water

Phone: 01274 372085
Email: neil.schofi...@yorkshirewater.co.uk

 

Spotted a leak?
If you spot a leak please report it immediately. Call us on 0800 57 3553 or go 
to http://www.yorkshirewater.com/leaks

Get a free water saving pack
Don't forget to request your free water and energy saving pack, it could save 
you money on your utility bills and help you conserve water. 
http://www.yorkshirewater.com/savewater

The information in this e-mail, and any files transmitted with it, is 
confidential and may also be legally privileged. The contents are intended 
solely for the addressee only and are subject to the legal notice available at 
http://www.keldagroup.com/email.htm. This email does not constitute a binding 
offer, acceptance, amendment, waiver or other agreement, or create any 
obligation whatsoever, unless such intention is clearly stated in the body of 
the email. If you are not the intended recipient, please return the message by 
replying to it and then delete the message from your computer. Any disclosure, 
copying, distribution or action taken in reliance on its contents is prohibited 
and may be unlawful.

Yorkshire Water Services Limited
Registered Office Western House, Halifax Road, Bradford, BD6 2SZ
Registered in England and Wales No 2366682

SQL query in v6 server

[Loon, EJ van - SPLXM]

Hi TSM-ers!

We just migrated a second server to v6 and now I need to 'patch' TSM
Operational Reporter. Among others, the following SQL statement no
longer works:

 

select node_name, platform_name, date(lastacc_time) from nodes where
cast((current_timestamp-lastacc_time)days as decimal) =2 and contact
like 'Component Team Linux%%'

 

It must have something to do with the cast part, because when I leave
that out it works fine.

I have a hard time finding the correct information about rewriting your
SQL queries, so if somebody could help me out, I'll appreciate it!

Kind regards,

Eric van Loon

AF/KLM Storage Engineering


For information, services and offers, please visit our web site: 
http://www.klm.com. This e-mail and any attachment may contain confidential and 
privileged material intended for the addressee only. If you are not the 
addressee, you are notified that no part of the e-mail or any attachment may be 
disclosed, copied or distributed, and that any other action related to this 
e-mail or attachment is strictly prohibited, and may be unlawful. If you have 
received this e-mail by error, please notify the sender immediately by return 
e-mail, and delete this message.

Koninklijke Luchtvaart Maatschappij NV (KLM), its subsidiaries and/or its 
employees shall not be liable for the incorrect or incomplete transmission of 
this e-mail or any attachments, nor responsible for any delay in receipt.
Koninklijke Luchtvaart Maatschappij N.V. (also known as KLM Royal Dutch 
Airlines) is registered in Amstelveen, The Netherlands, with registered number 
33014286

Re: SQL query in v6 server

[Ben Bullock]

I recently upgraded the server from 5.5 to 6.3.4 and am also working at 
changing out the failing scripts. Having to add a lot of cast statements to get 
the column formatting to be nicely formatted. The date arithmetic is also the 
one that has me the most  stymied. 

I have used the, cast((current_timestamp-lastacc_time)days as decimal) 
statement, but I also haven't found a workaround. 

Any enlightenment would be appreciated here also. ;-)

Thanks,
Ben

-Original Message-
From: ADSM: Dist Stor Manager [mailto:ADSM-L@VM.MARIST.EDU] On Behalf Of Loon, 
EJ van - SPLXM
Sent: Wednesday, November 20, 2013 8:40 AM
To: ADSM-L@VM.MARIST.EDU
Subject: [ADSM-L] SQL query in v6 server

Hi TSM-ers!

We just migrated a second server to v6 and now I need to 'patch' TSM 
Operational Reporter. Among others, the following SQL statement no longer works:

 

select node_name, platform_name, date(lastacc_time) from nodes where 
cast((current_timestamp-lastacc_time)days as decimal) =2 and contact like 
'Component Team Linux%%'

 

It must have something to do with the cast part, because when I leave that out 
it works fine.

I have a hard time finding the correct information about rewriting your SQL 
queries, so if somebody could help me out, I'll appreciate it!

Kind regards,

Eric van Loon

AF/KLM Storage Engineering


For information, services and offers, please visit our web site: 
https://urldefense.proofpoint.com/v1/url?u=http://www.klm.com/k=Kv4nkNfjdxVgeJz6Pg57qw%3D%3D%0Ar=I1HLMFJ6m%2BiVcavWgCBtVd78uShy4GoDLiStkJAJ6wk%3D%0Am=G9r2kCIrUOtvvigSp472sNCJ4VDABm3GAlVoP9E3%2Bls%3D%0As=65584791f33ccdc2e7085e6c09f287f08e5c7afceb3b4e8e2cef7303e2e05a1a.
 This e-mail and any attachment may contain confidential and privileged 
material intended for the addressee only. If you are not the addressee, you are 
notified that no part of the e-mail or any attachment may be disclosed, copied 
or distributed, and that any other action related to this e-mail or attachment 
is strictly prohibited, and may be unlawful. If you have received this e-mail 
by error, please notify the sender immediately by return e-mail, and delete 
this message.

Koninklijke Luchtvaart Maatschappij NV (KLM), its subsidiaries and/or its 
employees shall not be liable for the incorrect or incomplete transmission of 
this e-mail or any attachments, nor responsible for any delay in receipt.
Koninklijke Luchtvaart Maatschappij N.V. (also known as KLM Royal Dutch 
Airlines) is registered in Amstelveen, The Netherlands, with registered number 
33014286


--
NOTICE: This email message is for the sole use of the intended recipient(s) and 
may contain confidential and privileged information. Any unauthorized review, 
use, disclosure or distribution is prohibited. If you are not the intended 
recipient, please contact the sender by reply email and destroy all copies of 
the original message.
Blue Cross of Idaho, 3000 E. Pine Ave, Meridian, ID 83642

Re: Re: SQL query in v6 server

[David Bronder]

I'm working on the same task as I prepare for a 5.5 to 6.3 upgrade.  Here's a
good starting point for updating select statements for TSM 6:


https://pic.dhe.ibm.com/infocenter/tsminfo/v6r3/index.jsp?topic=%2Fcom.ibm.itsm.srv.upgrd.doc%2Fr_srv_upgrd_cmd_select.html

There's also an IBM tech note about it, but I don't have the link handy.

=Dave


On 11/20/2013 10:13 AM, Ben Bullock wrote:

I recently upgraded the server from 5.5 to 6.3.4 and am also working at
changing out the failing scripts. Having to add a lot of cast statements
to get the column formatting to be nicely formatted. The date arithmetic
is also the one that has me the most  stymied.

I have used the, cast((current_timestamp-lastacc_time)days as decimal) 
statement, but I also haven't found a workaround.

Any enlightenment would be appreciated here also. ;-)

Thanks,
Ben

-Original Message-
From: ADSM: Dist Stor Manager [mailto:ADSM-L@VM.MARIST.EDU] On Behalf Of Loon, 
EJ van - SPLXM
Sent: Wednesday, November 20, 2013 8:40 AM
To: ADSM-L@VM.MARIST.EDU
Subject: [ADSM-L] SQL query in v6 server

Hi TSM-ers!

We just migrated a second server to v6 and now I need to 'patch' TSM
Operational Reporter. Among others, the following SQL statement no longer
works:


select node_name, platform_name, date(lastacc_time) from nodes where 
cast((current_timestamp-lastacc_time)days as decimal) =2 and contact like 
'Component Team Linux%%'


It must have something to do with the cast part, because when I leave that
out it works fine.

I have a hard time finding the correct information about rewriting your
SQL queries, so if somebody could help me out, I'll appreciate it!

Kind regards,

Eric van Loon

AF/KLM Storage Engineering



--
Hello World.David Bronder - Systems Architect
Segmentation Fault  ITS-EI, Univ. of Iowa
Core dumped, disk trashed, quota filled, soda warm.   david-bron...@uiowa.edu

Re: SQL query in v6 server

[Colwell, William F.]

Hi Eric,

the timestampdiff function will do what you need.  This works -

select node_name, platform_name, date(lastacc_time) -
 from nodes -
  where cast(timestampdiff(16, current_timestamp - lastacc_time) as 
decimal(4,1)) 2

The first number in timestampdiff can be -

1 Fractions of a second 
2 Seconds 
4 Minutes 
8 Hours 
16 Days 
32 Weeks 
64 Months 
128 Quarters 
256 Years

For full details on this and other functions, download the db2 9.7 sql 
reference volume 1.


Bill Colwell
Draper Lab

-Original Message-
From: ADSM: Dist Stor Manager [mailto:ADSM-L@VM.MARIST.EDU] On Behalf Of Loon, 
EJ van - SPLXM
Sent: Wednesday, November 20, 2013 10:40 AM
To: ADSM-L@VM.MARIST.EDU
Subject: SQL query in v6 server

Hi TSM-ers!

We just migrated a second server to v6 and now I need to 'patch' TSM
Operational Reporter. Among others, the following SQL statement no
longer works:

 

select node_name, platform_name, date(lastacc_time) from nodes where
cast((current_timestamp-lastacc_time)days as decimal) =2 and contact
like 'Component Team Linux%%'

 

It must have something to do with the cast part, because when I leave
that out it works fine.

I have a hard time finding the correct information about rewriting your
SQL queries, so if somebody could help me out, I'll appreciate it!

Kind regards,

Eric van Loon

AF/KLM Storage Engineering


For information, services and offers, please visit our web site: 
http://www.klm.com. This e-mail and any attachment may contain confidential and 
privileged material intended for the addressee only. If you are not the 
addressee, you are notified that no part of the e-mail or any attachment may be 
disclosed, copied or distributed, and that any other action related to this 
e-mail or attachment is strictly prohibited, and may be unlawful. If you have 
received this e-mail by error, please notify the sender immediately by return 
e-mail, and delete this message.

Koninklijke Luchtvaart Maatschappij NV (KLM), its subsidiaries and/or its 
employees shall not be liable for the incorrect or incomplete transmission of 
this e-mail or any attachments, nor responsible for any delay in receipt.
Koninklijke Luchtvaart Maatschappij N.V. (also known as KLM Royal Dutch 
Airlines) is registered in Amstelveen, The Netherlands, with registered number 
33014286

Re: SQL query in v6 server

[Chavdar Cholev]

Hi Eric,
give me you mail and I will send you ready for use operational reporting
.hml for TSM 6.x
You can use also IBM Tivoli monitoring for TSM and cognos to
set/schedule some reports to be sent

Regards
Chavdar
On 11/20/2013 18:40, Colwell, William F. wrote:

Hi Eric,

the timestampdiff function will do what you need.  This works -

select node_name, platform_name, date(lastacc_time) -
  from nodes -
   where cast(timestampdiff(16, current_timestamp - lastacc_time) as 
decimal(4,1)) 2

The first number in timestampdiff can be -

1 Fractions of a second
2 Seconds
4 Minutes
8 Hours
16 Days
32 Weeks
64 Months
128 Quarters
256 Years

For full details on this and other functions, download the db2 9.7 sql 
reference volume 1.


Bill Colwell
Draper Lab

-Original Message-
From: ADSM: Dist Stor Manager [mailto:ADSM-L@VM.MARIST.EDU] On Behalf Of Loon, 
EJ van - SPLXM
Sent: Wednesday, November 20, 2013 10:40 AM
To: ADSM-L@VM.MARIST.EDU
Subject: SQL query in v6 server

Hi TSM-ers!

We just migrated a second server to v6 and now I need to 'patch' TSM
Operational Reporter. Among others, the following SQL statement no
longer works:



select node_name, platform_name, date(lastacc_time) from nodes where
cast((current_timestamp-lastacc_time)days as decimal) =2 and contact
like 'Component Team Linux%%'



It must have something to do with the cast part, because when I leave
that out it works fine.

I have a hard time finding the correct information about rewriting your
SQL queries, so if somebody could help me out, I'll appreciate it!

Kind regards,

Eric van Loon

AF/KLM Storage Engineering


For information, services and offers, please visit our web site: 
http://www.klm.com. This e-mail and any attachment may contain confidential and 
privileged material intended for the addressee only. If you are not the 
addressee, you are notified that no part of the e-mail or any attachment may be 
disclosed, copied or distributed, and that any other action related to this 
e-mail or attachment is strictly prohibited, and may be unlawful. If you have 
received this e-mail by error, please notify the sender immediately by return 
e-mail, and delete this message.

Koninklijke Luchtvaart Maatschappij NV (KLM), its subsidiaries and/or its 
employees shall not be liable for the incorrect or incomplete transmission of 
this e-mail or any attachments, nor responsible for any delay in receipt.
Koninklijke Luchtvaart Maatschappij N.V. (also known as KLM Royal Dutch 
Airlines) is registered in Amstelveen, The Netherlands, with registered number 
33014286

Re: SQL query in v6 server

[Loon, EJ van - SPLXM]

Thank you all for your help, I really appreciate it!
Kind regards,
Eric van Loon
AF/KLM Storage Engineering


-Original Message-
From: ADSM: Dist Stor Manager [mailto:ADSM-L@VM.MARIST.EDU] On Behalf Of
Colwell, William F.
Sent: woensdag 20 november 2013 17:40
To: ADSM-L@VM.MARIST.EDU
Subject: Re: SQL query in v6 server

Hi Eric,

the timestampdiff function will do what you need.  This works -

select node_name, platform_name, date(lastacc_time) -  from nodes -
  where cast(timestampdiff(16, current_timestamp - lastacc_time) as
decimal(4,1)) 2

The first number in timestampdiff can be -

1 Fractions of a second
2 Seconds
4 Minutes
8 Hours
16 Days
32 Weeks
64 Months
128 Quarters
256 Years

For full details on this and other functions, download the db2 9.7 sql
reference volume 1.


Bill Colwell
Draper Lab

-Original Message-
From: ADSM: Dist Stor Manager [mailto:ADSM-L@VM.MARIST.EDU] On Behalf Of
Loon, EJ van - SPLXM
Sent: Wednesday, November 20, 2013 10:40 AM
To: ADSM-L@VM.MARIST.EDU
Subject: SQL query in v6 server

Hi TSM-ers!

We just migrated a second server to v6 and now I need to 'patch' TSM
Operational Reporter. Among others, the following SQL statement no
longer works:

 

select node_name, platform_name, date(lastacc_time) from nodes where
cast((current_timestamp-lastacc_time)days as decimal) =2 and contact
like 'Component Team Linux%%'

 

It must have something to do with the cast part, because when I leave
that out it works fine.

I have a hard time finding the correct information about rewriting your
SQL queries, so if somebody could help me out, I'll appreciate it!

Kind regards,

Eric van Loon

AF/KLM Storage Engineering


For information, services and offers, please visit our web site:
http://www.klm.com. This e-mail and any attachment may contain
confidential and privileged material intended for the addressee only. If
you are not the addressee, you are notified that no part of the e-mail
or any attachment may be disclosed, copied or distributed, and that any
other action related to this e-mail or attachment is strictly
prohibited, and may be unlawful. If you have received this e-mail by
error, please notify the sender immediately by return e-mail, and delete
this message.

Koninklijke Luchtvaart Maatschappij NV (KLM), its subsidiaries and/or
its employees shall not be liable for the incorrect or incomplete
transmission of this e-mail or any attachments, nor responsible for any
delay in receipt.
Koninklijke Luchtvaart Maatschappij N.V. (also known as KLM Royal Dutch
Airlines) is registered in Amstelveen, The Netherlands, with registered
number 33014286


For information, services and offers, please visit our web site: 
http://www.klm.com. This e-mail and any attachment may contain confidential and 
privileged material intended for the addressee only. If you are not the 
addressee, you are notified that no part of the e-mail or any attachment may be 
disclosed, copied or distributed, and that any other action related to this 
e-mail or attachment is strictly prohibited, and may be unlawful. If you have 
received this e-mail by error, please notify the sender immediately by return 
e-mail, and delete this message. 

Koninklijke Luchtvaart Maatschappij NV (KLM), its subsidiaries and/or its 
employees shall not be liable for the incorrect or incomplete transmission of 
this e-mail or any attachments, nor responsible for any delay in receipt. 
Koninklijke Luchtvaart Maatschappij N.V. (also known as KLM Royal Dutch 
Airlines) is registered in Amstelveen, The Netherlands, with registered number 
33014286

sql query question

[Lee, Gary]

I want a list of all top level directories on a windows file system; 
specifically, c:.

How can I get this from tsm?


The problem is I need to restore several directories, back to a machine with a 
replaced hard disk.
However, I am totally blind, and unable to use the gui; so thought I would 
create a macro to restore each top level directory, and then call it with a 
schedule.

Thanks for any assistance.


Gary Lee
Senior System Programmer
Ball State University
phone: 765-285-1310

 

Re: sql query question

[Erwann Simon]

Hi Gary,

From a client perspective, I would simply use a query backup command: dsmc q 
ba c:\ -subdir=no

-- 
Best regards / Cordialement / مع تحياتي
Erwann SIMON

- Mail original -
De: Gary Lee g...@bsu.edu
À: ADSM-L@VM.MARIST.EDU
Envoyé: Jeudi 7 Novembre 2013 15:14:57
Objet: [ADSM-L] sql query question

I want a list of all top level directories on a windows file system; 
specifically, c:.

How can I get this from tsm?


The problem is I need to restore several directories, back to a machine with a 
replaced hard disk.
However, I am totally blind, and unable to use the gui; so thought I would 
create a macro to restore each top level directory, and then call it with a 
schedule.

Thanks for any assistance.


Gary Lee
Senior System Programmer
Ball State University
phone: 765-285-1310

 

Re: sql query question

[Ehresman,David E.]

Gary,

I think this will give you want you want.  Change the node_name  for your 
environment:

select filespace_name,hl_name,ll_name from backups where node_name='DEEHRE01' 
and state='ACTIVE_VERSION' and type='DIR' and hl_name='\'

David



-Original Message-
From: ADSM: Dist Stor Manager [mailto:ADSM-L@VM.MARIST.EDU] On Behalf Of Lee, 
Gary
Sent: Thursday, November 07, 2013 9:15 AM
To: ADSM-L@VM.MARIST.EDU
Subject: [ADSM-L] sql query question

I want a list of all top level directories on a windows file system; 
specifically, c:.

How can I get this from tsm?


The problem is I need to restore several directories, back to a machine with a 
replaced hard disk.
However, I am totally blind, and unable to use the gui; so thought I would 
create a macro to restore each top level directory, and then call it with a 
schedule.

Thanks for any assistance.


Gary Lee
Senior System Programmer
Ball State University
phone: 765-285-1310

 

Re: sql query question

[Dwight Cook]

Select distinct node_name,type,filespace_name,hl_name,ll_name from backups
where node_name='desirednodename' and type=DIR and
filespace_name='\\nodename\c$' and hl_name='\' order by node_name,
type,filespace_name,hl_name,ll_name

It will run a while though...

-Original Message-
From: ADSM: Dist Stor Manager [mailto:ADSM-L@VM.MARIST.EDU] On Behalf Of
Lee, Gary
Sent: Thursday, November 07, 2013 8:15 AM
To: ADSM-L@VM.MARIST.EDU
Subject: [ADSM-L] sql query question

I want a list of all top level directories on a windows file system;
specifically, c:.

How can I get this from tsm?


The problem is I need to restore several directories, back to a machine with
a replaced hard disk.
However, I am totally blind, and unable to use the gui; so thought I would
create a macro to restore each top level directory, and then call it with a
schedule.

Thanks for any assistance.


Gary Lee
Senior System Programmer
Ball State University
phone: 765-285-1310

Re: another sql query problem

[Andrew Raibeck]

Hi Gary,

What time of day does the script run?

What time of day did the backup start?

If you look at the dsmsched.log file, what was the time frame when most of
the files were backed up?

One possibility occur to me: Let's say the backup started on July 30 at
23:00 and most of the files were backed up between 23:00 and midnight. If
the script is run at 06:00 July 31, then subtracting 20 hours from that and
extracting only the date part from the result yields July 30, the same as
the backup start date. Thus the WHERE clause would look like this:

   [where ...] 2013-07-30 20103-07-30

which of course is not true, so the results are not included in the output.

- Andy



Andrew Raibeck | Tivoli Storage Manager Level 3 Technical Lead |
stor...@us.ibm.com

IBM Tivoli Storage Manager links:
Product support:
http://www.ibm.com/support/entry/portal/Overview/Software/Tivoli/Tivoli_Storage_Manager

Online documentation:
https://www.ibm.com/developerworks/mydeveloperworks/wikis/home/wiki/Tivoli
+Documentation+Central/page/Tivoli+Storage+Manager
Product Wiki:
https://www.ibm.com/developerworks/mydeveloperworks/wikis/home/wiki/Tivoli
+Storage+Manager/page/Home

ADSM: Dist Stor Manager ADSM-L@vm.marist.edu wrote on 2013-07-31
13:55:09:

 From: Lee, Gary g...@bsu.edu
 To: ADSM-L@vm.marist.edu,
 Date: 2013-07-31 13:56
 Subject: another sql query problem
 Sent by: ADSM: Dist Stor Manager ADSM-L@vm.marist.edu

 If I run the following script with the command

 Run listback node_name 20

 I receive a list of 128 objects.

 However, a check of the dsmsched.log file shows the following:

 07/31/2013 07:53:26 Total number of objects inspected:  634,241
 07/31/2013 07:53:26 Total number of objects assigned:61,461
 07/31/2013 07:53:26 Total number of objects backed up:   13,732

 What is up with this?
 I am trying to get a report of all files backed up for this run.

 Script follows:




 /* parms:  node_name hours from today */
 /* sample command: */
 /* run listbackfiles libdi 24 */
 /* */
 /* lists files backed up for node libdi 1 day before today */
 /* */

 set sqldisp wide
 select filespace_name, concat(hl_name, ll_name) as name, -
 backup_date -
 from backups where -
 date(backup_date) date(current_timestamp - $2 hours) -
 and node_name = upper('$1') -
 order by backup_date

Re: another sql query problem

[Lee, Gary]

Mr. Raibeck:

I suspect you have nailed the problem.  
So do I get around it by replacing date( with timestamp) essentially casting 
backup_date and the compare date as a timestamp?

-Original Message-
From: ADSM: Dist Stor Manager [mailto:ADSM-L@VM.MARIST.EDU] On Behalf Of Andrew 
Raibeck
Sent: Thursday, August 01, 2013 8:14 AM
To: ADSM-L@VM.MARIST.EDU
Subject: Re: [ADSM-L] another sql query problem

Hi Gary,

What time of day does the script run?

What time of day did the backup start?

If you look at the dsmsched.log file, what was the time frame when most of
the files were backed up?

One possibility occur to me: Let's say the backup started on July 30 at
23:00 and most of the files were backed up between 23:00 and midnight. If
the script is run at 06:00 July 31, then subtracting 20 hours from that and
extracting only the date part from the result yields July 30, the same as
the backup start date. Thus the WHERE clause would look like this:

   [where ...] 2013-07-30 20103-07-30

which of course is not true, so the results are not included in the output.

- Andy



Andrew Raibeck | Tivoli Storage Manager Level 3 Technical Lead |
stor...@us.ibm.com

IBM Tivoli Storage Manager links:
Product support:
http://www.ibm.com/support/entry/portal/Overview/Software/Tivoli/Tivoli_Storage_Manager

Online documentation:
https://www.ibm.com/developerworks/mydeveloperworks/wikis/home/wiki/Tivoli
+Documentation+Central/page/Tivoli+Storage+Manager
Product Wiki:
https://www.ibm.com/developerworks/mydeveloperworks/wikis/home/wiki/Tivoli
+Storage+Manager/page/Home

ADSM: Dist Stor Manager ADSM-L@vm.marist.edu wrote on 2013-07-31
13:55:09:

 From: Lee, Gary g...@bsu.edu
 To: ADSM-L@vm.marist.edu,
 Date: 2013-07-31 13:56
 Subject: another sql query problem
 Sent by: ADSM: Dist Stor Manager ADSM-L@vm.marist.edu

 If I run the following script with the command

 Run listback node_name 20

 I receive a list of 128 objects.

 However, a check of the dsmsched.log file shows the following:

 07/31/2013 07:53:26 Total number of objects inspected:  634,241
 07/31/2013 07:53:26 Total number of objects assigned:61,461
 07/31/2013 07:53:26 Total number of objects backed up:   13,732

 What is up with this?
 I am trying to get a report of all files backed up for this run.

 Script follows:




 /* parms:  node_name hours from today */
 /* sample command: */
 /* run listbackfiles libdi 24 */
 /* */
 /* lists files backed up for node libdi 1 day before today */
 /* */

 set sqldisp wide
 select filespace_name, concat(hl_name, ll_name) as name, -
 backup_date -
 from backups where -
 date(backup_date) date(current_timestamp - $2 hours) -
 and node_name = upper('$1') -
 order by backup_date

Re: another sql query problem

[Andrew Raibeck]

Gary,

You might get closer to what you want by dropping the date() function
from both sides of the comparison operator, e.g.:

   backup_date=(current_timestamp - 20 hours)

If you really want to target objects backed up by yesterday's scheduled
backup, it gets trickier (maybe someone else knows an easier way). In my
example, I actually concatenate into a single string the file space name,
HL name, and LL name:


select concat(filespace_name, concat(hl_name, ll_name)) as FILE NAME, -
   backup_date -
   from backups -
   where -
  backup_date=(select actual_start -
 from events -
 where scheduled_start'2013-01-01' and -
   date(scheduled_start)=date(current_timestamp - 1 day) and -
   node_name='RAIBECK_PROD' and -
   status='Completed') -
 and -
  backup_date=(select completed -
 from events -
 where scheduled_start'2013-01-01' and -
   date(scheduled_start)=date(current_timestamp - 1 day) and -
   node_name='RAIBECK_PROD' and -
   status='Completed') -
 order by backup_date



Best regards,

- Andy



Andrew Raibeck | Tivoli Storage Manager Level 3 Technical Lead |
stor...@us.ibm.com

IBM Tivoli Storage Manager links:
Product support:
http://www.ibm.com/support/entry/portal/Overview/Software/Tivoli/Tivoli_Storage_Manager

Online documentation:
https://www.ibm.com/developerworks/mydeveloperworks/wikis/home/wiki/Tivoli
+Documentation+Central/page/Tivoli+Storage+Manager
Product Wiki:
https://www.ibm.com/developerworks/mydeveloperworks/wikis/home/wiki/Tivoli
+Storage+Manager/page/Home

ADSM: Dist Stor Manager ADSM-L@vm.marist.edu wrote on 2013-08-01
08:21:59:

 From: Lee, Gary g...@bsu.edu
 To: ADSM-L@vm.marist.edu,
 Date: 2013-08-01 08:23
 Subject: Re: another sql query problem
 Sent by: ADSM: Dist Stor Manager ADSM-L@vm.marist.edu

 Mr. Raibeck:

 I suspect you have nailed the problem.
 So do I get around it by replacing date( with timestamp) essentially
 casting backup_date and the compare date as a timestamp?

 -Original Message-
 From: ADSM: Dist Stor Manager [mailto:ADSM-L@VM.MARIST.EDU] On
 Behalf Of Andrew Raibeck
 Sent: Thursday, August 01, 2013 8:14 AM
 To: ADSM-L@VM.MARIST.EDU
 Subject: Re: [ADSM-L] another sql query problem

 Hi Gary,

 What time of day does the script run?

 What time of day did the backup start?

 If you look at the dsmsched.log file, what was the time frame when most
of
 the files were backed up?

 One possibility occur to me: Let's say the backup started on July 30 at
 23:00 and most of the files were backed up between 23:00 and midnight. If
 the script is run at 06:00 July 31, then subtracting 20 hours from that
and
 extracting only the date part from the result yields July 30, the same as
 the backup start date. Thus the WHERE clause would look like this:

[where ...] 2013-07-30 20103-07-30

 which of course is not true, so the results are not included in the
output.

 - Andy





 Andrew Raibeck | Tivoli Storage Manager Level 3 Technical Lead |
 stor...@us.ibm.com

 IBM Tivoli Storage Manager links:
 Product support:
 http://www.ibm.com/support/entry/portal/Overview/Software/Tivoli/
 Tivoli_Storage_Manager

 Online documentation:

https://www.ibm.com/developerworks/mydeveloperworks/wikis/home/wiki/Tivoli
 +Documentation+Central/page/Tivoli+Storage+Manager
 Product Wiki:

https://www.ibm.com/developerworks/mydeveloperworks/wikis/home/wiki/Tivoli
 +Storage+Manager/page/Home

 ADSM: Dist Stor Manager ADSM-L@vm.marist.edu wrote on 2013-07-31
 13:55:09:

  From: Lee, Gary g...@bsu.edu
  To: ADSM-L@vm.marist.edu,
  Date: 2013-07-31 13:56
  Subject: another sql query problem
  Sent by: ADSM: Dist Stor Manager ADSM-L@vm.marist.edu
 
  If I run the following script with the command
 
  Run listback node_name 20
 
  I receive a list of 128 objects.
 
  However, a check of the dsmsched.log file shows the following:
 
  07/31/2013 07:53:26 Total number of objects inspected:  634,241
  07/31/2013 07:53:26 Total number of objects assigned:61,461
  07/31/2013 07:53:26 Total number of objects backed up:   13,732
 
  What is up with this?
  I am trying to get a report of all files backed up for this run.
 
  Script follows:
 
 
 
 
  /* parms:  node_name hours from today */
  /* sample command: */
  /* run listbackfiles libdi 24 */
  /* */
  /* lists files backed up for node libdi 1 day before today */
  /* */
 
  set sqldisp wide
  select filespace_name, concat(hl_name, ll_name) as name, -
  backup_date -
  from backups where -
  date(backup_date) date(current_timestamp - $2 hours) -
  and node_name = upper('$1') -
  order by backup_date
 

another sql query problem

[Lee, Gary]

If I run the following script with the command

Run listback node_name 20

I receive a list of 128 objects.

However, a check of the dsmsched.log file shows the following:

07/31/2013 07:53:26 Total number of objects inspected:  634,241
07/31/2013 07:53:26 Total number of objects assigned:61,461
07/31/2013 07:53:26 Total number of objects backed up:   13,732

What is up with this?
I am trying to get a report of all files backed up for this run.

Script follows:




/* parms:  node_name hours from today */
/* sample command: */
/* run listbackfiles libdi 24 */
/* */
/* lists files backed up for node libdi 1 day before today */
/* */

set sqldisp wide
select filespace_name, concat(hl_name, ll_name) as name, -
backup_date -
from backups where -
date(backup_date) date(current_timestamp - $2 hours) -
and node_name = upper('$1') -
order by backup_date

sql query of objects backed up

[Lee, Gary]

Forgot to mention tsm server 6.2.4 under RHEL 6.

sql query from tsm end.......please help me out.....

[Yuvaraja Shivarama]

Hi all,

can any one knows how to extract the occupancy report along with
domain name and node together

regards
Yuvaraj

Re: sql query from tsm end.......please help me out.....

[Richard Rhodes]

Not exactly sure what you are looking for, but here is a report I run
daily on all our tsm servers.  It gives a single line occupancy per node
along with the domain.  It might be a starting place for you to work with.


select -
 occupancy.node_name, -
 nodes.domain_name, -
 sum(occupancy.num_files), -
 sum(occupancy.physical_mb), -
 sum(occupancy.logical_mb) -
  from occupancy, nodes -
where occupancy.node_name =  nodes.node_name -
group by occupancy.node_name,  nodes.domain_name





From:   Yuvaraja Shivarama yuvaraja.dee...@gmail.com
To: ADSM-L@VM.MARIST.EDU
Date:   03/20/2012 07:17 AM
Subject:sql query from tsm end...please help me out.
Sent by:ADSM: Dist Stor Manager ADSM-L@VM.MARIST.EDU



Hi all,

can any one knows how to extract the occupancy report along with
domain name and node together

regards
Yuvaraj




-
The information contained in this message is intended only for the
personal and confidential use of the recipient(s) named above. If
the reader of this message is not the intended recipient or an
agent responsible for delivering it to the intended recipient, you
are hereby notified that you have received this document in error
and that any review, dissemination, distribution, or copying of
this message is strictly prohibited. If you have received this
communication in error, please notify us immediately, and delete
the original message.

Re: sql query from tsm end.......please help me out.....

[Grigori Solonovitch]

Try next query:

select b.domain_name, a.node_name as Node_Name, count(*) as File_Spaces, 
sum(a.physical_mb) as Physical_MB, sum(a.logical_mb) as Logical_MB, 
sum(a.num_files) as Files -
from occupancy a, nodes b -
where a.stgpool_name = STGNAME -
  and a.node_name = b.node_name -
group by b.domain_name, a.node_name

Do not forget to specify correct storage pool name!

Grigori G. Solonovitch
Senior Technical Architect  Ahli United Bank Kuwait  www.ahliunited.com.kw

Please consider the environment before printing this E-mail

-Original Message-
From: ADSM: Dist Stor Manager [mailto:ADSM-L@VM.MARIST.EDU] On Behalf Of 
Yuvaraja Shivarama
Sent: 20 03 2012 2:16 PM
To: ADSM-L@VM.MARIST.EDU
Subject: [ADSM-L] sql query from tsm end...please help me out.

Hi all,

can any one knows how to extract the occupancy report along with domain name 
and node together

regards
Yuvaraj


Please consider the environment before printing this Email.

CONFIDENTIALITY AND WAIVER: The information contained in this electronic mail 
message and any attachments hereto may be legally privileged and confidential. 
The information is intended only for the recipient(s) named in this message. If 
you are not the intended recipient you are notified that any use, disclosure, 
copying or distribution is prohibited. If you have received this in error 
please contact the sender and delete this message and any attachments from your 
computer system. We do not guarantee that this message or any attachment to it 
is secure or free from errors, computer viruses or other conditions that may 
damage or interfere with data, hardware or software.

Sql query problem in tsm 6.2

[Lee, Gary D.]

TSM folks,

tsm server 6.2.2 on redhat enterprise linux 6.0
I submit the following file as a macro and recieve the errors below.

This query works in tsm 5.5


file c:\tsmscripts\pl3
-

select events.node_name, events.status, nodes.email_address from events, -
nodes where events.node_name = nodes.node_name and -
events.status != 'Completed' and -
events.scheduled_start '2011-09-06 08:00:00' and -
events.scheduled_start '2011-09-07 07:00:00'

tsm screen cut below.



tsm: TSM01macro c:\tsmscripts\pl3
ANS8000I Server command: 'select events.node_name, events.status, nodes.email_ 
ad
dress from events, nodes where events.node_name = nodes.node_name and events.s 
ta
tus != 'Completed' and events.scheduled_start '2011-09-06 08:00:00' and event 
s.
scheduled_start '2011-09-07 07:00:00''
ANR0162W Supplemental database diagnostic information:  -1:42S22:-206
([ IBM][CLI Driver][DB2/LINUXX8664] SQL0206N  NODES.NODE_NAME is not valid in
the context where it is used.  SQLSTATE=42703
).
ANR2940E The command 'select events.node_name , events.status ,
nodes.email_address from events , NODESVIEW where events.node_name =
nodes.node_name and events.status != 'Completed' and events.scheduled_start 
' 2011-09-06 08:00:00' and events.scheduled_start  '2011-09-07 07:00:00' '
contains an unknown SQL column name.
ANS8001I Return code 3.
ANS8 29E Macro processing terminated.
tsm: TSM01


Gary Lee
Senior System Programmer
Ball State University
phone: 765-285-1310

 

Re: Sql query problem in tsm 6.2

[Richard Sims]

Do select * from syscat.columns where tabname='NODES'
and see if the column name is still NODE_NAME, or perhaps changed to NODENAME.
Beyond that: There were a bunch of APARs involving TSM tables, such as IC70346, 
which may be impacting your attempted Select.
Unfortunately, TSM version 6 has been chaotic.

Richard Sims

Antwort: [ADSM-L] Sql query problem in tsm 6.2

[Thorsten Burghardt]

Hi,

use following Syntax:

select ev.node_name, ev.status, no.email_address from events ev, -
nodes no where ev.node_name = no.node_name and -
ev.status != 'Completed' and -
ev.scheduled_start '2011-09-06 08:00:00' and -
ev.scheduled_start '2011-09-07 07:00:00'

Thorsten



Von:Lee, Gary D. g...@bsu.edu
An: ADSM-L@VM.MARIST.EDU
Datum:  07.09.11 15:07
Betreff:[ADSM-L] Sql query problem in tsm 6.2
Gesendet von:   ADSM: Dist Stor Manager ADSM-L@VM.MARIST.EDU



TSM folks,

tsm server 6.2.2 on redhat enterprise linux 6.0
I submit the following file as a macro and recieve the errors below.

This query works in tsm 5.5


file c:\tsmscripts\pl3
-

select events.node_name, events.status, nodes.email_address from events, -
nodes where events.node_name = nodes.node_name and -
events.status != 'Completed' and -
events.scheduled_start '2011-09-06 08:00:00' and -
events.scheduled_start '2011-09-07 07:00:00'

tsm screen cut below.



tsm: TSM01macro c:\tsmscripts\pl3
ANS8000I Server command: 'select events.node_name, events.status,
nodes.email_ ad
dress from events, nodes where events.node_name = nodes.node_name and
events.s ta
tus != 'Completed' and events.scheduled_start '2011-09-06 08:00:00' and
event s.
scheduled_start '2011-09-07 07:00:00''
ANR0162W Supplemental database diagnostic information:  -1:42S22:-206
([ IBM][CLI Driver][DB2/LINUXX8664] SQL0206N  NODES.NODE_NAME is not
valid in
the context where it is used.  SQLSTATE=42703
).
ANR2940E The command 'select events.node_name , events.status ,
nodes.email_address from events , NODESVIEW where events.node_name =
nodes.node_name and events.status != 'Completed' and
events.scheduled_start 
' 2011-09-06 08:00:00' and events.scheduled_start  '2011-09-07 07:00:00'
'
contains an unknown SQL column name.
ANS8001I Return code 3.
ANS8 29E Macro processing terminated.
tsm: TSM01


Gary Lee
Senior System Programmer
Ball State University
phone: 765-285-1310






+ 750 Jahre Knappschaft. Soziale Verantwortung zu jeder Zeit. 
www.750jahre.info +

Re: Sql query problem in tsm 6.2

[Lee, Gary D.]

Well, column name is still node_name. listed as column 0.

Next to lookup the apar.  Thanks for the help.

This is disconcerting.  Sure effects my reporting and error tracking.
 


Gary Lee
Senior System Programmer
Ball State University
phone: 765-285-1310

 
-Original Message-
From: ADSM: Dist Stor Manager [mailto:ADSM-L@VM.MARIST.EDU] On Behalf Of 
Richard Sims
Sent: Wednesday, September 07, 2011 9:28 AM
To: ADSM-L@VM.MARIST.EDU
Subject: Re: [ADSM-L] Sql query problem in tsm 6.2

Do select * from syscat.columns where tabname='NODES'
and see if the column name is still NODE_NAME, or perhaps changed to NODENAME.
Beyond that: There were a bunch of APARs involving TSM tables, such as IC70346, 
which may be impacting your attempted Select.
Unfortunately, TSM version 6 has been chaotic.

Richard Sims

Re: Sql query problem in tsm 6.2

[Fiske, Daniel]

For some reason 6.2 does not like linking tables in the old way. You need to 
use aliases. 

Try this select statement:

select ev.node_name, ev.status, nd.email_address from events ev, nodes nd where 
ev.node_name = nd.node_name and ev.status != 'Completed' and 
ev.scheduled_start '2011-09-06 08:00:00' and ev.scheduled_start '2011-09-07 
07:00:00'

Dan Fiske

-Original Message-
From: ADSM: Dist Stor Manager [mailto:ADSM-L@VM.MARIST.EDU] On Behalf Of Lee, 
Gary D.
Sent: Wednesday, September 07, 2011 9:02 AM
To: ADSM-L@VM.MARIST.EDU
Subject: [ADSM-L] Sql query problem in tsm 6.2

TSM folks,

tsm server 6.2.2 on redhat enterprise linux 6.0
I submit the following file as a macro and recieve the errors below.

This query works in tsm 5.5


file c:\tsmscripts\pl3
-

select events.node_name, events.status, nodes.email_address from events, -
nodes where events.node_name = nodes.node_name and -
events.status != 'Completed' and -
events.scheduled_start '2011-09-06 08:00:00' and -
events.scheduled_start '2011-09-07 07:00:00'

tsm screen cut below.



tsm: TSM01macro c:\tsmscripts\pl3
ANS8000I Server command: 'select events.node_name, events.status, nodes.email_ 
ad
dress from events, nodes where events.node_name = nodes.node_name and events.s 
ta
tus != 'Completed' and events.scheduled_start '2011-09-06 08:00:00' and event 
s.
scheduled_start '2011-09-07 07:00:00''
ANR0162W Supplemental database diagnostic information:  -1:42S22:-206
([ IBM][CLI Driver][DB2/LINUXX8664] SQL0206N  NODES.NODE_NAME is not valid in
the context where it is used.  SQLSTATE=42703
).
ANR2940E The command 'select events.node_name , events.status ,
nodes.email_address from events , NODESVIEW where events.node_name =
nodes.node_name and events.status != 'Completed' and events.scheduled_start 
' 2011-09-06 08:00:00' and events.scheduled_start  '2011-09-07 07:00:00' '
contains an unknown SQL column name.
ANS8001I Return code 3.
ANS8 29E Macro processing terminated.
tsm: TSM01


Gary Lee
Senior System Programmer
Ball State University
phone: 765-285-1310

 

Re: Antwort: [ADSM-L] Sql query problem in tsm 6.2

[Lee, Gary D.]

Thanks all.  Adding the alias names fixed it.

 


Gary Lee
Senior System Programmer
Ball State University
phone: 765-285-1310

 
-Original Message-
From: ADSM: Dist Stor Manager [mailto:ADSM-L@VM.MARIST.EDU] On Behalf Of 
Thorsten Burghardt
Sent: Wednesday, September 07, 2011 9:36 AM
To: ADSM-L@VM.MARIST.EDU
Subject: [ADSM-L] Antwort: [ADSM-L] Sql query problem in tsm 6.2

Hi,

use following Syntax:

select ev.node_name, ev.status, no.email_address from events ev, -
nodes no where ev.node_name = no.node_name and -
ev.status != 'Completed' and -
ev.scheduled_start '2011-09-06 08:00:00' and -
ev.scheduled_start '2011-09-07 07:00:00'

Thorsten



Von:Lee, Gary D. g...@bsu.edu
An: ADSM-L@VM.MARIST.EDU
Datum:  07.09.11 15:07
Betreff:[ADSM-L] Sql query problem in tsm 6.2
Gesendet von:   ADSM: Dist Stor Manager ADSM-L@VM.MARIST.EDU



TSM folks,

tsm server 6.2.2 on redhat enterprise linux 6.0
I submit the following file as a macro and recieve the errors below.

This query works in tsm 5.5


file c:\tsmscripts\pl3
-

select events.node_name, events.status, nodes.email_address from events, -
nodes where events.node_name = nodes.node_name and -
events.status != 'Completed' and -
events.scheduled_start '2011-09-06 08:00:00' and -
events.scheduled_start '2011-09-07 07:00:00'

tsm screen cut below.



tsm: TSM01macro c:\tsmscripts\pl3
ANS8000I Server command: 'select events.node_name, events.status,
nodes.email_ ad
dress from events, nodes where events.node_name = nodes.node_name and
events.s ta
tus != 'Completed' and events.scheduled_start '2011-09-06 08:00:00' and
event s.
scheduled_start '2011-09-07 07:00:00''
ANR0162W Supplemental database diagnostic information:  -1:42S22:-206
([ IBM][CLI Driver][DB2/LINUXX8664] SQL0206N  NODES.NODE_NAME is not
valid in
the context where it is used.  SQLSTATE=42703
).
ANR2940E The command 'select events.node_name , events.status ,
nodes.email_address from events , NODESVIEW where events.node_name =
nodes.node_name and events.status != 'Completed' and
events.scheduled_start 
' 2011-09-06 08:00:00' and events.scheduled_start  '2011-09-07 07:00:00'
'
contains an unknown SQL column name.
ANS8001I Return code 3.
ANS8 29E Macro processing terminated.
tsm: TSM01


Gary Lee
Senior System Programmer
Ball State University
phone: 765-285-1310






+ 750 Jahre Knappschaft. Soziale Verantwortung zu jeder Zeit. 
www.750jahre.info +

SQL query - large files

[Paul_Dudley]

Is it possible to interrogate the TSM database with an SQL query to display the 
list of backed up files whose size is larger than - for example - 160 Mbs?

I understand that this will be a query that will take a while to run so I am 
prepared to run it on the weekend. While the total number of files is large the 
number of files larger than 160 Mb is quite small.



Thanks  Regards

Paul



Paul Dudley

Senior IT Systems Administrator

ANL Container Line Pty Limited

Email:  mailto:pdud...@anl.com.au pdud...@anl.com.au








ANL DISCLAIMER

This e-mail and any file attached is confidential, and intended solely to the 
named addressees. Any unauthorised dissemination or use is strictly prohibited. 
If you received this e-mail in error, please immediately notify the sender by 
return e-mail from your system. Please do not copy, use or make reference to it 
for any purpose, or disclose its contents to any person.

Re: SQL query - large files

[Nick Laflamme]

On Jul 17, 2011, at 9:07 PM, Paul_Dudley wrote:

 Is it possible to interrogate the TSM database with an SQL query to display 
 the list of backed up files whose size is larger than - for example - 160 Mbs?
 
 I understand that this will be a query that will take a while to run so I am 
 prepared to run it on the weekend. While the total number of files is large 
 the number of files larger than 160 Mb is quite small.

V5 or V6? 

With version 5, the CONTENTS table and the BACKUPS tables are both indexed 
reasonably well. BACKUPS has the size and might be all you need. 

Version 6? The BACKUPS view (table?) doesn't have enough indices on it to be 
useful. A colleague at work is trying to dare me to use DB2 to look at the real 
internal tables, but I haven't quite that ambitious yet.

Nick

Re: SQL query - large files

[Paul_Dudley]

 -Original Message-

 On Jul 17, 2011, at 9:07 PM, Paul_Dudley wrote:

  Is it possible to interrogate the TSM database with an SQL query to display 
  the
 list of backed up files whose size is larger than - for example - 160 Mbs?
 
  I understand that this will be a query that will take a while to run so I 
  am prepared
 to run it on the weekend. While the total number of files is large the number 
 of files
 larger than 160 Mb is quite small.

 V5 or V6?

 With version 5, the CONTENTS table and the BACKUPS tables are both indexed
 reasonably well. BACKUPS has the size and might be all you need.

 Version 6? The BACKUPS view (table?) doesn't have enough indices on it to be
 useful. A colleague at work is trying to dare me to use DB2 to look at the 
 real
 internal tables, but I haven't quite that ambitious yet.

 Nick

It is version 5.5

Regards
Paul






ANL DISCLAIMER

This e-mail and any file attached is confidential, and intended solely to the 
named addressees. Any unauthorised dissemination or use is strictly prohibited. 
If you received this e-mail in error, please immediately notify the sender by 
return e-mail from your system. Please do not copy, use or make reference to it 
for any purpose, or disclose its contents to any person.

回复: [ADSM-L] SQL query - large files

[Zhou Tianxiang]

Zhou Tianxiang
Sent from HTC mobile!

- Reply message -
发件人： Paul_Dudley pdud...@anl.com.au
日期： 周一, 7 月 18 日, 2011 年 12:00 下午
主题： [ADSM-L] SQL query - large files
收件人： ADSM-L@VM.MARIST.EDU

 -Original Message-

 On Jul 17, 2011, at 9:07 PM, Paul_Dudley wrote:

  Is it possible to interrogate the TSM database with an SQL query to display 
  the
 list of backed up files whose size is larger than - for example - 160 Mbs?
 
  I understand that this will be a query that will take a while to run so I 
  am prepared
 to run it on the weekend. While the total number of files is large the number 
 of files
 larger than 160 Mb is quite small.

 V5 or V6?

 With version 5, the CONTENTS table and the BACKUPS tables are both indexed
 reasonably well. BACKUPS has the size and might be all you need.

 Version 6? The BACKUPS view (table?) doesn't have enough indices on it to be
 useful. A colleague at work is trying to dare me to use DB2 to look at the 
 real
 internal tables, but I haven't quite that ambitious yet.

 Nick

It is version 5.5

Regards
Paul






ANL DISCLAIMER

This e-mail and any file attached is confidential, and intended solely to the 
named addressees. Any unauthorised dissemination or use is strictly prohibited. 
If you received this e-mail in error, please immediately notify the sender by 
return e-mail from your system. Please do not copy, use or make reference to it 
for any purpose, or disclose its contents to any person.

Re: Sql query help

[David E Ehresman]

Does this do what you want?

tsm: ULTSMq script q_stgsum f=l

Name   Line   Command
   Number
-- -- 
Q_STGSUM   1  select stgpool_name, -
   5 devclass, -
   10maxscratch, -
   15numscratchused, -
   20cast(maxscratch-numscratchused as decimal(4,0)) as
   SCR_AVAIL -
   25from stgpools -
   30where stgpool_name like '%VTL' -
   35order by devclass,stgpool_name

 Lee, Gary D. g...@bsu.edu 4/6/2010 3:10 PM 
Tsm server 5.5.4 running on suse 9 linux under zvm 5.3.

Trying to create a query which will give me the count of volumes in a storage 
pool, and its maxscratch setting on a single line.
Nice to watch for filling pools which need a larger maxscratch value.
Query follows:

---

 select a.stgpool_name as Storage Pool Name, -
a.devclass as Device Class Name, -
count( b.volume_name) as   # VOLUMES, -
a.maxscratch as volumes available -
from stgpools a, volumes b -
where a.devclass 'DISK'  -
and a.devclass = b.devclass_name -
group by a.stgpool_name, a.devclass

- query ends. 

Thanks for any help.



Gary Lee
Senior System Programmer
Ball State University
phone: 765-285-1310

 

Sql query help

[Lee, Gary D.]

Tsm server 5.5.4 running on suse 9 linux under zvm 5.3.

Trying to create a query which will give me the count of volumes in a storage 
pool, and its maxscratch setting on a single line.
Nice to watch for filling pools which need a larger maxscratch value.
Query follows:

---

 select a.stgpool_name as Storage Pool Name, -
a.devclass as Device Class Name, -
count( b.volume_name) as   # VOLUMES, -
a.maxscratch as volumes available -
from stgpools a, volumes b -
where a.devclass 'DISK'  -
and a.devclass = b.devclass_name -
group by a.stgpool_name, a.devclass

- query ends. 

Thanks for any help.



Gary Lee
Senior System Programmer
Ball State University
phone: 765-285-1310

 

Re: Sql query help

[Shawn Drew]

There is a field in the stgpools table that shows how many tapes are used,
so I don't think you need a join.

select MAXSCRATCH, NUMSCRATCHUSED from stgpools


The way I monitor maxscratch is with this:

select STGPOOL_NAME, MAXSCRATCH, NUMSCRATCHUSED from stgpools where
MAXSCRATCH is not null and (MAXSCRATCH - NUMSCRATCHUSED)10

If that command returns succesfully, then the Storage Pool will accept
less than 10 new scratch tapes.  (the not null is to exclude Random
pools)


Regards,
Shawn

Shawn Drew





Internet
g...@bsu.edu

Sent by: ADSM-L@VM.MARIST.EDU
04/06/2010 03:10 PM
Please respond to
ADSM-L@VM.MARIST.EDU


To
ADSM-L
cc

Subject
[ADSM-L] Sql query help






Tsm server 5.5.4 running on suse 9 linux under zvm 5.3.

Trying to create a query which will give me the count of volumes in a
storage pool, and its maxscratch setting on a single line.
Nice to watch for filling pools which need a larger maxscratch value.
Query follows:

---

 select a.stgpool_name as Storage Pool Name, -
a.devclass as Device Class Name, -
count( b.volume_name) as   # VOLUMES, -
 a.maxscratch as volumes available -
from stgpools a, volumes b -
 where a.devclass 'DISK'  -
and a.devclass = b.devclass_name -
group by a.stgpool_name, a.devclass

- query ends. 

Thanks for any help.



Gary Lee
Senior System Programmer
Ball State University
phone: 765-285-1310




This message and any attachments (the message) is intended solely for
the addressees and is confidential. If you receive this message in error,
please delete it and immediately notify the sender. Any use not in accord
with its purpose, any dissemination or disclosure, either whole or partial,
is prohibited except formal approval. The internet can not guarantee the
integrity of this message. BNP PARIBAS (and its subsidiaries) shall (will)
not therefore be liable for the message if modified. Please note that certain
functions and services for BNP Paribas may be performed by BNP Paribas RCC, Inc.

Re: Sql query help

[Lee, Gary D.]

Thanks Shawn.  That did it.  Don't know how I missed that field.
 


Gary Lee
Senior System Programmer
Ball State University
phone: 765-285-1310

 
-Original Message-
From: ADSM: Dist Stor Manager [mailto:ads...@vm.marist.edu] On Behalf Of Shawn 
Drew
Sent: Tuesday, April 06, 2010 3:18 PM
To: ADSM-L@VM.MARIST.EDU
Subject: Re: [ADSM-L] Sql query help

There is a field in the stgpools table that shows how many tapes are used,
so I don't think you need a join.

select MAXSCRATCH, NUMSCRATCHUSED from stgpools


The way I monitor maxscratch is with this:

select STGPOOL_NAME, MAXSCRATCH, NUMSCRATCHUSED from stgpools where
MAXSCRATCH is not null and (MAXSCRATCH - NUMSCRATCHUSED)10

If that command returns succesfully, then the Storage Pool will accept
less than 10 new scratch tapes.  (the not null is to exclude Random
pools)


Regards,
Shawn

Shawn Drew





Internet
g...@bsu.edu

Sent by: ADSM-L@VM.MARIST.EDU
04/06/2010 03:10 PM
Please respond to
ADSM-L@VM.MARIST.EDU


To
ADSM-L
cc

Subject
[ADSM-L] Sql query help






Tsm server 5.5.4 running on suse 9 linux under zvm 5.3.

Trying to create a query which will give me the count of volumes in a
storage pool, and its maxscratch setting on a single line.
Nice to watch for filling pools which need a larger maxscratch value.
Query follows:

---

 select a.stgpool_name as Storage Pool Name, -
a.devclass as Device Class Name, -
count( b.volume_name) as   # VOLUMES, -
 a.maxscratch as volumes available -
from stgpools a, volumes b -
 where a.devclass 'DISK'  -
and a.devclass = b.devclass_name -
group by a.stgpool_name, a.devclass

- query ends. 

Thanks for any help.



Gary Lee
Senior System Programmer
Ball State University
phone: 765-285-1310




This message and any attachments (the message) is intended solely for
the addressees and is confidential. If you receive this message in error,
please delete it and immediately notify the sender. Any use not in accord
with its purpose, any dissemination or disclosure, either whole or partial,
is prohibited except formal approval. The internet can not guarantee the
integrity of this message. BNP PARIBAS (and its subsidiaries) shall (will)
not therefore be liable for the message if modified. Please note that certain
functions and services for BNP Paribas may be performed by BNP Paribas RCC, Inc.

does anyone know of an SQL Query Client (like MS Query Editor) that works without having to install MS office?

[Erwin Zavala]

does anyone know of an SQL Query Client (like MS Query Editor) that works
without having to install MS office?

Re: SQL Query find nodes associated with management class

[Grigori Solonovitch]

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

[Grigori Solonovitch]

Hello Timothy,

I am using next scripts:

define script F3_Node_Backup_Copy_Groups description=Node 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=Node 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 script name node 
name to see complete information about management class and copy group used by 
node.

Regards,

Grigori


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 ...associated w/ management class

[James R Owen]

Lindsay Morris wrote:

Right, we did some magic on the dsmc client...

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.
...
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?

lind...@tsmworks.com


A.Lurker responds Yes to both of the above, e.g.,

Upon finding DONT_DOIT policy in effect @ (inherited) NoClue TSM:

tsm: NOCLUEq co dont_doit act*

PolicyPolicyMgmt  Copy  Versions Versions   Retain  Retain
DomainSet Name  Class Group Data DataExtraOnly
NameName  NameExists  Deleted Versions Version
- - - -    ---
DONT_DOIT ACTIVESPECIAL   STANDARD30   30  999 999
DONT_DOIT ACTIVESTANDARD  STANDARD 42   30  60

tsm: NOCLUE [names changed, but unfortunately, this is not imaginary!]

The now responsible TSM v5.5 SysAdm wants to know (who,what,howmuch) will
be affected if we eliminate SPECIAL Mgmt Class, but would *not* dare to

select node_name, filespace_name, filespace_id, HL_name, LL_name,-
count(LL_name) as #InActive, sum(file_size) as NoAggr(MB)-
from backups,contents where state='Inactive'-
and days(current_time)-days(deactivate_date)30-
and backups.object_id=contents.object_id-
and aggregated='No'-
/* because actual filesize isn't always available, but it should be! */
group by node_name, filespace_name, filespace_id, HL_name, LL_name

The potential TSM v6.1.5 SysAdm wonders if, with the new DB2 basis,
this/similar selection could actually return accurate information within
the time that a reasonable person would be willing to wait?
--
jim.o...@yale.edu   (w#203.432.6693, c#203.494.9201, h#203.387.3030)

Re: SQL Query find nodes associated with management class

[Timothy Hughes]

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

SQL Query find nodes associated with management class

[Timothy Hughes]

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

Re: SQL Query find nodes associated with management class

[Richard Sims]

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

[Huebschman, George J.]

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

[Lindsay Morris]

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

[Huebschman, George J.]

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

[Lindsay Morris]

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
 information to us via electronic mail, including social security numbers,
 account numbers, or personal identification numbers. Delivery, and or timely
 delivery

Formating SQL query

[Grigori Solonovitch]

I am not very cool in SQL and I need help.
I have query like select distict a,b from c group by a,b
Response on this SQL query in  TSM Server is:
A1 B1
A1 B2
A2 B3
A2 B4
A2 B5
I would like to have:
A1 B1
 B2
A2 B3
 B4
 B5
I know exactly it is possible in Oracle SQL*Plus.
Is it possible in TSM Server 5.5.3?
Is it possible in TSM Server 6.1.2 (DB2)?
What is the way, if possible?



Grigori G. Solonovitch

Senior Technical Architect

Information Technology  Bank of Kuwait and Middle East  http://www.bkme.com

Phone: (+965) 2231-2274  Mobile: (+965) 99798073  E-Mail: 
g.solonovi...@bkme.commailto:g.solonovi...@bkme.com

Please consider the environment before printing this Email


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: Formating SQL query

[Michael Green]

A year ago I attended an introductory 5-day SQL*Plus course where we
were told that usage of 'distinct' is discouraged, use 'group' by
instead.

My 5¢.
--
Warm regards,
Michael Green



On Thu, Oct 15, 2009 at 2:49 PM, Grigori Solonovitch
g.solonovi...@bkme.com wrote:
 I am not very cool in SQL and I need help.
 I have query like select distict a,b from c group by a,b
 Response on this SQL query in  TSM Server is:
 A1 B1
 A1 B2
 A2 B3
 A2 B4
 A2 B5
 I would like to have:
 A1 B1
     B2
 A2 B3
     B4
     B5
 I know exactly it is possible in Oracle SQL*Plus.
 Is it possible in TSM Server 5.5.3?
 Is it possible in TSM Server 6.1.2 (DB2)?
 What is the way, if possible?

Re: Formating SQL query

[Grigori Solonovitch]

Thank you very much, but this an answer on my question? Is it enough to remove 
distinct?

Grigori G. Solonovitch

Senior Technical Architect

Information Technology  Bank of Kuwait and Middle East  http://www.bkme.com

Phone: (+965) 2231-2274  Mobile: (+965) 99798073  E-Mail: g.solonovi...@bkme.com

Please consider the environment before printing this Email


-Original Message-
From: ADSM: Dist Stor Manager [mailto:ads...@vm.marist.edu] On Behalf Of 
Michael Green
Sent: Thursday, October 15, 2009 3:58 PM
To: ADSM-L@VM.MARIST.EDU
Subject: Re: [ADSM-L] Formating SQL query

A year ago I attended an introductory 5-day SQL*Plus course where we
were told that usage of 'distinct' is discouraged, use 'group' by
instead.

My 5¢.
--
Warm regards,
Michael Green



On Thu, Oct 15, 2009 at 2:49 PM, Grigori Solonovitch
g.solonovi...@bkme.com wrote:
 I am not very cool in SQL and I need help.
 I have query like select distict a,b from c group by a,b
 Response on this SQL query in  TSM Server is:
 A1 B1
 A1 B2
 A2 B3
 A2 B4
 A2 B5
 I would like to have:
 A1 B1
 B2
 A2 B3
 B4
 B5
 I know exactly it is possible in Oracle SQL*Plus.
 Is it possible in TSM Server 5.5.3?
 Is it possible in TSM Server 6.1.2 (DB2)?
 What is the way, if possible?



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: Formating SQL query

[Colwell, William F.]

Grigori,

 

I assume the sql*plus feature you use is the break statement which

by default does outlines on break columns.

 

Besides submitting sql and retrieving results sets, Sql*plus includes

a lot of report writer functions which are not strictly SQL.

 

So I don't know any way to do outlining with just sql.

 

In version 6 you can make the db2 databases visible to other tools

using jdbc or odbc.  See the wiki for directions.  I use a free tool -
DB visualizer

(http://www.minq.se/products/dbvis/download/index.jsp)

to examine tables to attempt to understand what is going on.  All the

views on the tsm db are there.  I haven't looked for a tool which does

outlining but I am sure there is one.

 

Bill Colwell

Draper Lab.

 

 

 

-Original Message-
From: ADSM: Dist Stor Manager [mailto:ads...@vm.marist.edu] On Behalf Of
Grigori Solonovitch
Sent: Thursday, October 15, 2009 8:49 AM
To: ADSM-L@VM.MARIST.EDU
Subject: Formating SQL query

 

I am not very cool in SQL and I need help.

I have query like select distict a,b from c group by a,b

Response on this SQL query in  TSM Server is:

A1 B1

A1 B2

A2 B3

A2 B4

A2 B5

I would like to have:

A1 B1

 B2

A2 B3

 B4

 B5

I know exactly it is possible in Oracle SQL*Plus.

Is it possible in TSM Server 5.5.3?

Is it possible in TSM Server 6.1.2 (DB2)?

What is the way, if possible?

 

 

 

Grigori G. Solonovitch

 

Senior Technical Architect

 

Information Technology  Bank of Kuwait and Middle East
http://www.bkme.com

 

Phone: (+965) 2231-2274  Mobile: (+965) 99798073  E-Mail:
g.solonovi...@bkme.commailto:g.solonovi...@bkme.com

 

Please consider the environment before printing this Email

 

 

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: Formating SQL query

[Grigori Solonovitch]

Bill,
Thank you very much. I do not think it is a good idea to use direct interface 
to DB2. I do not want to be DB2 database administrator. I had some not very 
good expirience with DB2-based IBM MERVA application in the past (similar to 
SWIFT). As far as I understatnd, IBM does not advise to work with DB2 directly 
in TSM as well becaose of very high risk. In my opinion, DB2 in TSM 6.1 is one 
of the main problems. We were planning to implement 6.1.2 in production mode, 
but project was posponed after testing (at least up to more stable release).
I have tried to find something more simple to format SQL query. No result is a 
result as well.
Grigori


From: ADSM: Dist Stor Manager [ads...@vm.marist.edu] On Behalf Of Colwell, 
William F. [bcolw...@draper.com]
Sent: Thursday, October 15, 2009 10:01 PM
To: ADSM-L@VM.MARIST.EDU
Subject: Re: [ADSM-L] Formating SQL query

Grigori,



I assume the sql*plus feature you use is the break statement which

by default does outlines on break columns.



Besides submitting sql and retrieving results sets, Sql*plus includes

a lot of report writer functions which are not strictly SQL.



So I don't know any way to do outlining with just sql.



In version 6 you can make the db2 databases visible to other tools

using jdbc or odbc.  See the wiki for directions.  I use a free tool -
DB visualizer

(http://www.minq.se/products/dbvis/download/index.jsp)

to examine tables to attempt to understand what is going on.  All the

views on the tsm db are there.  I haven't looked for a tool which does

outlining but I am sure there is one.



Bill Colwell

Draper Lab.







-Original Message-
From: ADSM: Dist Stor Manager [mailto:ads...@vm.marist.edu] On Behalf Of
Grigori Solonovitch
Sent: Thursday, October 15, 2009 8:49 AM
To: ADSM-L@VM.MARIST.EDU
Subject: Formating SQL query



I am not very cool in SQL and I need help.

I have query like select distict a,b from c group by a,b

Response on this SQL query in  TSM Server is:

A1 B1

A1 B2

A2 B3

A2 B4

A2 B5

I would like to have:

A1 B1

 B2

A2 B3

 B4

 B5

I know exactly it is possible in Oracle SQL*Plus.

Is it possible in TSM Server 5.5.3?

Is it possible in TSM Server 6.1.2 (DB2)?

What is the way, if possible?







Grigori G. Solonovitch



Senior Technical Architect



Information Technology  Bank of Kuwait and Middle East
http://www.bkme.com



Phone: (+965) 2231-2274  Mobile: (+965) 99798073  E-Mail:
g.solonovi...@bkme.commailto:g.solonovi...@bkme.com



Please consider the environment before printing this Email





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.

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.

Problem with SQL query in TSM 6.1

[Grigori Solonovitch]

I have query to TSM database which is working fine with TSM 5.5, but gives an 
error in TSM 6.1:

tsm: BKMEselect volume_name,state,location,upd_date from drmedia where 
volume_name like 'TSM%' and voltype = 'DBBackup' order by volume_name
ANR2944E The reference 'ORDER' is an unknown SQL scalar function name.

|
 
..V
 e_name like 'TSM%' and voltype ='DBBackup' order by volume_name

ANS8001I Return code 3.
tsm: BKME

What is the problem with query? Something came from DB2?


Grigori G. Solonovitch

Senior Technical Architect

Information Technology  Bank of Kuwait and Middle East  http://www.bkme.com

Phone: (+965) 2231-2274  Mobile: (+965) 99798073  E-Mail: 
g.solonovi...@bkme.commailto:g.solonovi...@bkme.com

Please consider the environment before printing this Email


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: Problem with SQL query in TSM 6.1

[Richard Sims]

This is what you run into when you are on the bleeding edge in going
with a very new version of software.
See IBM Technote 1380830.

   Richard Sims

Re: Problem with SQL query in TSM 6.1

[Grigori Solonovitch]

Thank you very much.
Is this problem fixed in TSM 6.1.1?

Grigori G. Solonovitch

Senior Technical Architect

Information Technology  Bank of Kuwait and Middle East  http://www.bkme.com

Phone: (+965) 2231-2274  Mobile: (+965) 99798073  E-Mail: g.solonovi...@bkme.com

Please consider the environment before printing this Email


-Original Message-
From: ADSM: Dist Stor Manager [mailto:ads...@vm.marist.edu] On Behalf Of 
Richard Sims
Sent: Sunday, May 10, 2009 1:35 PM
To: ADSM-L@VM.MARIST.EDU
Subject: Re: [ADSM-L] Problem with SQL query in TSM 6.1

This is what you run into when you are on the bleeding edge in going
with a very new version of software.
See IBM Technote 1380830.

Richard Sims

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 question

[Loon, EJ van - SPLXM]

Hi Andy!
It works great, thank you VERY much!!! 
Kind regards,
Eric van Loon
KLM Royal Dutch Airlines


-Original Message-
From: ADSM: Dist Stor Manager [mailto:ads...@vm.marist.edu] On Behalf Of
Andrew Raibeck
Sent: dinsdag 27 januari 2009 14:30
To: ADSM-L@VM.MARIST.EDU
Subject: Re: SQL query question

Hi Eric,

You need to add an WHERE additional criterion like this:

   occupancy.node_name = nodes.node_name

Best regards,

Andy

Andy Raibeck
IBM Software Group
Tivoli Storage Manager Client Product Development Level 3 Team Lead
Internal Notes e-mail: Andrew Raibeck/Tucson/i...@ibmus Internet e-mail:
stor...@us.ibm.com

IBM Tivoli Storage Manager support web page:
http://www.ibm.com/software/sysmgmt/products/support/IBMTivoliStorageMan
ager.html


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/27/2009
05:18:06 AM:

 [image removed]

 SQL query question

 Loon, EJ van - SPLXM

 to:

 ADSM-L

 01/27/2009 05:18 AM

 Sent by:

 ADSM: Dist Stor Manager ADSM-L@VM.MARIST.EDU

 Please respond to ADSM: Dist Stor Manager

 Hi *SM-ers!
 I'm trying to create an SQL query to list all filespaces with more 
 than 500 Gb. stored in TSM, but only for nodes belonging to a certain 
 department. I want to use the contact field for this, but I must be 
 doing something wrong, because it still lists all nodes. Here's what I

 created thus far:

 select occupancy.node_name as Node Name, occupancy.filespace_name as

 File System, cast(float(occupancy.physical_mb) as dec(10,0)) as 
 Megabytes from occupancy, nodes where occupancy.stgpool_name like 
 '%PRI%' and nodes.contact='Linux Department' and occupancy.physical_mb
 =50

 Could someone help me with creating the correct SQL statement?
 Thank you VERY much in advance!!
 Kindest regards,
 Eric van Loon
 KLM Royal Dutch Airlines


 **
 For information, services and offers, please visit our web site:
 http://www.klm.com. This e-mail and any attachment may contain 
 confidential and privileged material intended for the addressee only. 
 If you are not the addressee, you are notified that no part of the 
 e-mail or any attachment may be disclosed, copied or distributed, and 
 that any other action related to this e-mail or attachment is strictly

 prohibited, and may be unlawful. If you have received this e-mail by 
 error, please notify the sender immediately by return e-mail, and 
 delete this message.

 Koninklijke Luchtvaart Maatschappij NV (KLM), its subsidiaries and/or 
 its employees shall not be liable for the incorrect or incomplete 
 transmission of this e-mail or any attachments, nor responsible for 
 any delay in receipt.
 Koninklijke Luchtvaart Maatschappij N.V. (also known as KLM Royal 
 Dutch Airlines) is registered in Amstelveen, The Netherlands, with 
 registered number 33014286
 **
**
For information, services and offers, please visit our web site:
http://www.klm.com. This e-mail and any attachment may contain
confidential and privileged material intended for the addressee
only. If you are not the addressee, you are notified that no part
of the e-mail or any attachment may be disclosed, copied or
distributed, and that any other action related to this e-mail or
attachment is strictly prohibited, and may be unlawful. If you have
received this e-mail by error, please notify the sender immediately
by return e-mail, and delete this message. 

Koninklijke Luchtvaart Maatschappij NV (KLM), its subsidiaries
and/or its employees shall not be liable for the incorrect or
incomplete transmission of this e-mail or any attachments, nor
responsible for any delay in receipt.
Koninklijke Luchtvaart Maatschappij N.V. (also known as KLM Royal
Dutch Airlines) is registered in Amstelveen, The Netherlands, with
registered number 33014286 
**

SQL query question

[Loon, EJ van - SPLXM]

Hi *SM-ers!
I'm trying to create an SQL query to list all filespaces with more than
500 Gb. stored in TSM, but only for nodes belonging to a certain
department. I want to use the contact field for this, but I must be
doing something wrong, because it still lists all nodes. Here's what I
created thus far:

select occupancy.node_name as Node Name, occupancy.filespace_name as
File System, cast(float(occupancy.physical_mb) as dec(10,0)) as
Megabytes from occupancy, nodes where occupancy.stgpool_name like
'%PRI%' and nodes.contact='Linux Department' and occupancy.physical_mb
=50

Could someone help me with creating the correct SQL statement?
Thank you VERY much in advance!!
Kindest regards,
Eric van Loon
KLM Royal Dutch Airlines


**
For information, services and offers, please visit our web site:
http://www.klm.com. This e-mail and any attachment may contain
confidential and privileged material intended for the addressee
only. If you are not the addressee, you are notified that no part
of the e-mail or any attachment may be disclosed, copied or
distributed, and that any other action related to this e-mail or
attachment is strictly prohibited, and may be unlawful. If you have
received this e-mail by error, please notify the sender immediately
by return e-mail, and delete this message. 

Koninklijke Luchtvaart Maatschappij NV (KLM), its subsidiaries
and/or its employees shall not be liable for the incorrect or
incomplete transmission of this e-mail or any attachments, nor
responsible for any delay in receipt.
Koninklijke Luchtvaart Maatschappij N.V. (also known as KLM Royal
Dutch Airlines) is registered in Amstelveen, The Netherlands, with
registered number 33014286 
**

Re: SQL query question

[Andrew Raibeck]

Hi Eric,

You need to add an WHERE additional criterion like this:

   occupancy.node_name = nodes.node_name

Best regards,

Andy

Andy Raibeck
IBM Software Group
Tivoli Storage Manager Client Product Development
Level 3 Team Lead
Internal Notes e-mail: Andrew Raibeck/Tucson/i...@ibmus
Internet e-mail: stor...@us.ibm.com

IBM Tivoli Storage Manager support web page:
http://www.ibm.com/software/sysmgmt/products/support/IBMTivoliStorageManager.html


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/27/2009
05:18:06 AM:

 [image removed]

 SQL query question

 Loon, EJ van - SPLXM

 to:

 ADSM-L

 01/27/2009 05:18 AM

 Sent by:

 ADSM: Dist Stor Manager ADSM-L@VM.MARIST.EDU

 Please respond to ADSM: Dist Stor Manager

 Hi *SM-ers!
 I'm trying to create an SQL query to list all filespaces with more than
 500 Gb. stored in TSM, but only for nodes belonging to a certain
 department. I want to use the contact field for this, but I must be
 doing something wrong, because it still lists all nodes. Here's what I
 created thus far:

 select occupancy.node_name as Node Name, occupancy.filespace_name as
 File System, cast(float(occupancy.physical_mb) as dec(10,0)) as
 Megabytes from occupancy, nodes where occupancy.stgpool_name like
 '%PRI%' and nodes.contact='Linux Department' and occupancy.physical_mb
 =50

 Could someone help me with creating the correct SQL statement?
 Thank you VERY much in advance!!
 Kindest regards,
 Eric van Loon
 KLM Royal Dutch Airlines


 **
 For information, services and offers, please visit our web site:
 http://www.klm.com. This e-mail and any attachment may contain
 confidential and privileged material intended for the addressee
 only. If you are not the addressee, you are notified that no part
 of the e-mail or any attachment may be disclosed, copied or
 distributed, and that any other action related to this e-mail or
 attachment is strictly prohibited, and may be unlawful. If you have
 received this e-mail by error, please notify the sender immediately
 by return e-mail, and delete this message.

 Koninklijke Luchtvaart Maatschappij NV (KLM), its subsidiaries
 and/or its employees shall not be liable for the incorrect or
 incomplete transmission of this e-mail or any attachments, nor
 responsible for any delay in receipt.
 Koninklijke Luchtvaart Maatschappij N.V. (also known as KLM Royal
 Dutch Airlines) is registered in Amstelveen, The Netherlands, with
 registered number 33014286
 **

Speeding up my SQL query

[Loon, E.J. van - SPLXM]

Hi *SM-ers!
My TSM Operational Reporter creates a daily report for our Windows guys.
Part of this report is a SQL query which returns all missed files. This
is the query:

select -
   actlog.nodename as Node Name, -
   substr(char(actlog.date_time), 1, 16) as Date/Time, -
   substr(actlog.message, 26) as Message -
from actlog,nodes -
where -
actlog.nodename=nodes.node_name -
and (actlog.msgno=4005 or actlog.msgno=4007 or actlog.msgno=4018 or
actlog.msgno=4037 or actlog.msgno=4987) -
and (actlog.date_time between '%s' and '%s') -
and (nodes.contact like 'Windows%%') -
order by actlog.nodename

The problem is that the query is taking more that 3 hours to complete,
probably because it's using non-indexed database tables.
Does anyone know how to make this query more efficient?
Thank you VERY much for your help in advance!
Kindest regards,
Eric van Loon
KLM Royal Dutch Airlines


**
For information, services and offers, please visit our web site:
http://www.klm.com. This e-mail and any attachment may contain
confidential and privileged material intended for the addressee
only. If you are not the addressee, you are notified that no part
of the e-mail or any attachment may be disclosed, copied or
distributed, and that any other action related to this e-mail or
attachment is strictly prohibited, and may be unlawful. If you have
received this e-mail by error, please notify the sender immediately
by return e-mail, and delete this message. 

Koninklijke Luchtvaart Maatschappij NV (KLM), its subsidiaries
and/or its employees shall not be liable for the incorrect or
incomplete transmission of this e-mail or any attachments, nor
responsible for any delay in receipt.
Koninklijke Luchtvaart Maatschappij N.V. (also known as KLM Royal
Dutch Airlines) is registered in Amstelveen, The Netherlands, with
registered number 33014286 
**

Re: Speeding up my SQL query

[Richard Sims]

Eric -

One thing you can do is use the SQL clause IN rather than a succession
of OR conditions to improve the query performance of testing for
multiple values.

  Richard Sims

Re: Speeding up my SQL query

[Choudarapu, Ramakrishna (GTS)]

Eric,

Did you try Q ACTLOG instead, using NODE=NODE_NAME, MSG=MSGNUM BEGINTIME
and ENDTIME parameters, which may be faster than the select, but the
resluts may not be as straight and detailed as your select...

Regards, Rama

-Original Message-
From: ADSM: Dist Stor Manager [mailto:[EMAIL PROTECTED] On Behalf Of
Loon, E.J. van - SPLXM
Sent: Friday, May 16, 2008 9:01 AM
To: ADSM-L@VM.MARIST.EDU
Subject: [ADSM-L] Speeding up my SQL query


Hi *SM-ers!
My TSM Operational Reporter creates a daily report for our Windows guys.
Part of this report is a SQL query which returns all missed files. This
is the query:

select -
   actlog.nodename as Node Name, -
   substr(char(actlog.date_time), 1, 16) as Date/Time, -
   substr(actlog.message, 26) as Message -
from actlog,nodes -
where -
actlog.nodename=nodes.node_name -
and (actlog.msgno=4005 or actlog.msgno=4007 or actlog.msgno=4018 or
actlog.msgno=4037 or actlog.msgno=4987) -
and (actlog.date_time between '%s' and '%s') -
and (nodes.contact like 'Windows%%') -
order by actlog.nodename

The problem is that the query is taking more that 3 hours to complete,
probably because it's using non-indexed database tables.
Does anyone know how to make this query more efficient?
Thank you VERY much for your help in advance!
Kindest regards,
Eric van Loon
KLM Royal Dutch Airlines


**
For information, services and offers, please visit our web site:
http://www.klm.com. This e-mail and any attachment may contain
confidential and privileged material intended for the addressee
only. If you are not the addressee, you are notified that no part
of the e-mail or any attachment may be disclosed, copied or
distributed, and that any other action related to this e-mail or
attachment is strictly prohibited, and may be unlawful. If you have
received this e-mail by error, please notify the sender immediately
by return e-mail, and delete this message. 

Koninklijke Luchtvaart Maatschappij NV (KLM), its subsidiaries
and/or its employees shall not be liable for the incorrect or
incomplete transmission of this e-mail or any attachments, nor
responsible for any delay in receipt.
Koninklijke Luchtvaart Maatschappij N.V. (also known as KLM Royal
Dutch Airlines) is registered in Amstelveen, The Netherlands, with
registered number 33014286 
**


This message w/attachments (message) may be privileged, confidential or 
proprietary, and if you are not an intended recipient, please notify the 
sender, do not use or share it and delete it. Unless specifically indicated, 
this message is not an offer to sell or a solicitation of any investment 
products or other financial product or service, an official confirmation of any 
transaction, or an official statement of Merrill Lynch. Subject to applicable 
law, Merrill Lynch may monitor, review and retain e-communications (EC) 
traveling through its networks/systems. The laws of the country of each 
sender/recipient may impact the handling of EC, and EC may be archived, 
supervised and produced in countries other than the country in which you are 
located. This message cannot be guaranteed to be secure or error-free. This 
message is subject to terms available at the following link: 
http://www.ml.com/e-communications_terms/. By messaging with Merrill Lynch you 
consent to the foregoing.

Re: Speeding up my SQL query

[Loon, E.J. van - SPLXM]

Hi Rama!
TSM Operational Reporter only supports SQL queries...
Kindest regards,
Eric van Loon
KLM Royal Dutch Airlines

-Original Message-
From: ADSM: Dist Stor Manager [mailto:[EMAIL PROTECTED] On Behalf Of
Choudarapu, Ramakrishna (GTS)
Sent: vrijdag 16 mei 2008 15:17
To: ADSM-L@VM.MARIST.EDU
Subject: Re: Speeding up my SQL query

Eric,

Did you try Q ACTLOG instead, using NODE=NODE_NAME, MSG=MSGNUM BEGINTIME
and ENDTIME parameters, which may be faster than the select, but the
resluts may not be as straight and detailed as your select...

Regards, Rama

-Original Message-
From: ADSM: Dist Stor Manager [mailto:[EMAIL PROTECTED] On Behalf Of
Loon, E.J. van - SPLXM
Sent: Friday, May 16, 2008 9:01 AM
To: ADSM-L@VM.MARIST.EDU
Subject: [ADSM-L] Speeding up my SQL query


Hi *SM-ers!
My TSM Operational Reporter creates a daily report for our Windows guys.
Part of this report is a SQL query which returns all missed files. This
is the query:

select -
   actlog.nodename as Node Name, -
   substr(char(actlog.date_time), 1, 16) as Date/Time, -
   substr(actlog.message, 26) as Message - from actlog,nodes - where -
actlog.nodename=nodes.node_name -
and (actlog.msgno=4005 or actlog.msgno=4007 or actlog.msgno=4018 or
actlog.msgno=4037 or actlog.msgno=4987) - and (actlog.date_time between
'%s' and '%s') - and (nodes.contact like 'Windows%%') - order by
actlog.nodename

The problem is that the query is taking more that 3 hours to complete,
probably because it's using non-indexed database tables.
Does anyone know how to make this query more efficient?
Thank you VERY much for your help in advance!
Kindest regards,
Eric van Loon
KLM Royal Dutch Airlines


**
For information, services and offers, please visit our web site:
http://www.klm.com. This e-mail and any attachment may contain
confidential and privileged material intended for the addressee only. If
you are not the addressee, you are notified that no part of the e-mail
or any attachment may be disclosed, copied or distributed, and that any
other action related to this e-mail or attachment is strictly
prohibited, and may be unlawful. If you have received this e-mail by
error, please notify the sender immediately by return e-mail, and delete
this message. 

Koninklijke Luchtvaart Maatschappij NV (KLM), its subsidiaries and/or
its employees shall not be liable for the incorrect or incomplete
transmission of this e-mail or any attachments, nor responsible for any
delay in receipt.
Koninklijke Luchtvaart Maatschappij N.V. (also known as KLM Royal Dutch
Airlines) is registered in Amstelveen, The Netherlands, with registered
number 33014286
**


This message w/attachments (message) may be privileged, confidential or
proprietary, and if you are not an intended recipient, please notify the
sender, do not use or share it and delete it. Unless specifically
indicated, this message is not an offer to sell or a solicitation of any
investment products or other financial product or service, an official
confirmation of any transaction, or an official statement of Merrill
Lynch. Subject to applicable law, Merrill Lynch may monitor, review and
retain e-communications (EC) traveling through its networks/systems. The
laws of the country of each sender/recipient may impact the handling of
EC, and EC may be archived, supervised and produced in countries other
than the country in which you are located. This message cannot be
guaranteed to be secure or error-free. This message is subject to terms
available at the following link:
http://www.ml.com/e-communications_terms/. By messaging with Merrill
Lynch you consent to the foregoing.

**
For information, services and offers, please visit our web site:
http://www.klm.com. This e-mail and any attachment may contain
confidential and privileged material intended for the addressee
only. If you are not the addressee, you are notified that no part
of the e-mail or any attachment may be disclosed, copied or
distributed, and that any other action related to this e-mail or
attachment is strictly prohibited, and may be unlawful. If you have
received this e-mail by error, please notify the sender immediately
by return e-mail, and delete this message. 

Koninklijke Luchtvaart Maatschappij NV (KLM), its subsidiaries
and/or its employees shall not be liable for the incorrect or
incomplete transmission of this e-mail or any attachments, nor
responsible for any delay in receipt.
Koninklijke Luchtvaart Maatschappij N.V. (also known as KLM Royal
Dutch Airlines) is registered in Amstelveen, The Netherlands, with
registered number 33014286 
**

Re: Speeding up my SQL query

[Loon, E.J. van - SPLXM]

Hi Richard!
Thank you very much for your reply!
Since I'm not much of an SQL wizzard, could you please help met with
rebuilding the query?
Kindest regards,
Eric van Loon
KLM Royal Dutch Airlines

-Original Message-
From: ADSM: Dist Stor Manager [mailto:[EMAIL PROTECTED] On Behalf Of
Richard Sims
Sent: vrijdag 16 mei 2008 15:08
To: ADSM-L@VM.MARIST.EDU
Subject: Re: Speeding up my SQL query

Eric -

One thing you can do is use the SQL clause IN rather than a succession
of OR conditions to improve the query performance of testing for
multiple values.

   Richard Sims
**
For information, services and offers, please visit our web site:
http://www.klm.com. This e-mail and any attachment may contain
confidential and privileged material intended for the addressee
only. If you are not the addressee, you are notified that no part
of the e-mail or any attachment may be disclosed, copied or
distributed, and that any other action related to this e-mail or
attachment is strictly prohibited, and may be unlawful. If you have
received this e-mail by error, please notify the sender immediately
by return e-mail, and delete this message. 

Koninklijke Luchtvaart Maatschappij NV (KLM), its subsidiaries
and/or its employees shall not be liable for the incorrect or
incomplete transmission of this e-mail or any attachments, nor
responsible for any delay in receipt.
Koninklijke Luchtvaart Maatschappij N.V. (also known as KLM Royal
Dutch Airlines) is registered in Amstelveen, The Netherlands, with
registered number 33014286 
**

Re: Speeding up my SQL query

[Thomas Denier]

-Eric van Loon wrote: -

Hi *SM-ers!
My TSM Operational Reporter creates a daily report for our Windows
guys.
Part of this report is a SQL query which returns all missed files.
This
is the query:

select -
   actlog.nodename as Node Name, -
   substr(char(actlog.date_time), 1, 16) as Date/Time, -
   substr(actlog.message, 26) as Message -
from actlog,nodes -
where -
actlog.nodename=nodes.node_name -
and (actlog.msgno=4005 or actlog.msgno=4007 or actlog.msgno=4018 or
actlog.msgno=4037 or actlog.msgno=4987) -
and (actlog.date_time between '%s' and '%s') -
and (nodes.contact like 'Windows%%') -
order by actlog.nodename

The problem is that the query is taking more that 3 hours to
complete,
probably because it's using non-indexed database tables.
Does anyone know how to make this query more efficient?

In my experience, sub-queries perform better than joins. With
this approach, you would select appropriate fields from the
ACTLOG table, and one of the criteria in the WHERE clause would
be:

nodename in (select node_name from nodes where contact like 'Windows%')

Re: Speeding up my SQL query

[Choudarapu, Ramakrishna (GTS)]

Richard meant to say to update ur query to:

select -
   actlog.nodename as Node Name, -
   substr(char(actlog.date_time), 1, 16) as Date/Time, -
   substr(actlog.message, 26) as Message -
from actlog,nodes -
where -
actlog.nodename=nodes.node_name -
and actlog.msgno in (4005,4007,4018,4037,4987) -
and (actlog.date_time between '%s' and '%s') -
and (nodes.contact like 'Windows%%') -
order by actlog.nodename

Am I right Richard?




-Original Message-
From: ADSM: Dist Stor Manager [mailto:[EMAIL PROTECTED] On Behalf Of
Loon, E.J. van - SPLXM
Sent: Friday, May 16, 2008 9:52 AM
To: ADSM-L@VM.MARIST.EDU
Subject: Re: [ADSM-L] Speeding up my SQL query


Hi Richard!
Thank you very much for your reply!
Since I'm not much of an SQL wizzard, could you please help met with
rebuilding the query?
Kindest regards,
Eric van Loon
KLM Royal Dutch Airlines

-Original Message-
From: ADSM: Dist Stor Manager [mailto:[EMAIL PROTECTED] On Behalf Of
Richard Sims
Sent: vrijdag 16 mei 2008 15:08
To: ADSM-L@VM.MARIST.EDU
Subject: Re: Speeding up my SQL query

Eric -

One thing you can do is use the SQL clause IN rather than a succession
of OR conditions to improve the query performance of testing for
multiple values.

   Richard Sims
**
For information, services and offers, please visit our web site:
http://www.klm.com. This e-mail and any attachment may contain
confidential and privileged material intended for the addressee
only. If you are not the addressee, you are notified that no part
of the e-mail or any attachment may be disclosed, copied or
distributed, and that any other action related to this e-mail or
attachment is strictly prohibited, and may be unlawful. If you have
received this e-mail by error, please notify the sender immediately
by return e-mail, and delete this message. 

Koninklijke Luchtvaart Maatschappij NV (KLM), its subsidiaries
and/or its employees shall not be liable for the incorrect or
incomplete transmission of this e-mail or any attachments, nor
responsible for any delay in receipt.
Koninklijke Luchtvaart Maatschappij N.V. (also known as KLM Royal
Dutch Airlines) is registered in Amstelveen, The Netherlands, with
registered number 33014286 
**


This message w/attachments (message) may be privileged, confidential or 
proprietary, and if you are not an intended recipient, please notify the 
sender, do not use or share it and delete it. Unless specifically indicated, 
this message is not an offer to sell or a solicitation of any investment 
products or other financial product or service, an official confirmation of any 
transaction, or an official statement of Merrill Lynch. Subject to applicable 
law, Merrill Lynch may monitor, review and retain e-communications (EC) 
traveling through its networks/systems. The laws of the country of each 
sender/recipient may impact the handling of EC, and EC may be archived, 
supervised and produced in countries other than the country in which you are 
located. This message cannot be guaranteed to be secure or error-free. This 
message is subject to terms available at the following link: 
http://www.ml.com/e-communications_terms/. By messaging with Merrill Lynch you 
consent to the foregoing.