Re: SQL statement for multiple classes

131223 > > 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: vrijdag 12 maart 2021 16:49 > To: ADSM-L@VM.M

Re: SQL statement for multiple classes

Hi Marc, This was exactly what I was looking for, thank you VERY much!!! Kind regards, Eric van Loon Air France/KLM Storage & Backup -Original Message- From: ADSM: Dist Stor Manager On Behalf Of Marc Lanteigne Sent: vrijdag 12 maart 2021 17:10 To: ADSM-L@VM.MARIST.EDU Subject: Re:

Re: SQL statement for multiple classes

ject: Re: SQL statement for multiple classes Hi Eric, You can part of the way there with GROUP BY: SELECT node_name,class_name,COUNT(*) FROM archives GROUP BY node_name,class_name This will give you one row per (node_name,class_name) tuple. In order to get row values as columns, though, you need t

Re: SQL statement for multiple classes

Hi Eric, I was actually trying to something similar with different data and tables a few weeks ago. With the help of a co-worker, we figured it out. It has to be a 2 step process. First you get a list of all the values you want as the column headings, in your case, the class name: select

Re: SQL statement for multiple classes

Hi Eric, You can part of the way there with GROUP BY: SELECT node_name,class_name,COUNT(*) FROM archives GROUP BY node_name,class_name This will give you one row per (node_name,class_name) tuple. In order to get row values as columns, though, you need to do a pivot (aka crosstab), which I'm not

Re: SQL STATEMENT

You'll need to query the Events table for this info. Modify the current time stamp as needed but I strongly suggest using it when querying the Events (and Backups) table. Something along the lines of: select node_name,status from events where (scheduled_start>=current_timestamp - 24 hours)

Re: SQL STATEMENT

Hi James, There is probably an example like you are looking for here: http://thobias.org/tsm/sql/ - Thanks, Marc... Marc Lanteigne Accelerated Value Specialist for Spectrum Protect 416.478.0233 | marclantei...@ca.ibm.com Office Hours:

Re: SQL statement

hey are >>> not taking up space on the TSM server... >>> Thanks again for your help! >>> Kind regards, >>> Eric van Loon >>> Air France/KLM Storage Engineering >>> >>> -Original Message- >>> From: ADSM: Dist Stor Manag

Re: SQL statement

Original Message----- > > From: ADSM: Dist Stor Manager [mailto:ADSM-L@VM.MARIST.EDU] On Behalf Of > > Robert Talda > > Sent: maandag 11 april 2016 19:34 > > To: ADSM-L@VM.MARIST.EDU > > Subject: Re: SQL statement > > > > EJ: > > > > Are you sur

Re: SQL statement

Eric van Loon > Air France/KLM Storage Engineering > > -Original Message- > From: ADSM: Dist Stor Manager [mailto:ADSM-L@VM.MARIST.EDU] On Behalf Of > Robert Talda > Sent: maandag 11 april 2016 19:34 > To: ADSM-L@VM.MARIST.EDU > Subject: Re: SQL statement > >

Re: SQL statement

for your help! Kind regards, Eric van Loon Air France/KLM Storage Engineering -Original Message- From: ADSM: Dist Stor Manager [mailto:ADSM-L@VM.MARIST.EDU] On Behalf Of Robert Talda Sent: maandag 11 april 2016 19:34 To: ADSM-L@VM.MARIST.EDU Subject: Re: SQL statement EJ: Are you sure

Re: SQL statement

EJ: Are you sure the missing filespaces have data? if not, they won’t have associated occupancy records and thus won’t appear in the output. I trip over that from time to time myself Robert Talda EZ-Backup Systems Engineer Cornell University +1 607-255-8280 r...@cornell.edu > On Apr

Re: SQL statement

Unsubscribe. -Original Message- From: ADSM: Dist Stor Manager [mailto:ADSM-L@VM.MARIST.EDU] On Behalf Of Erwann SIMON Sent: Saturday, January 30, 2016 4:03 AM To: ADSM-L@VM.MARIST.EDU Subject: Re: SQL statement Hi Eric, You're absolutely right, the SQL resultas are just to be used

Re: SQL statement

SM-L@VM.MARIST.EDU Subject: Re: SQL statement Hi Eric, You maybe should add a condition if backup_end is null -- Best regards / Cordialement / مع تحياتي Erwann SIMON - Mail original - De: "EJ van Loon (ITOPT3) - KLM" <eric-van.l...@klm.com> À: ADSM-L@VM.MARIST.EDU Envoyé

Re: SQL statement

ON > Sent: donderdag 28 januari 2016 16:46 > To: ADSM-L@VM.MARIST.EDU > Subject: Re: SQL statement > > Hi Eric, > > You maybe should add a condition if backup_end is null > > -- > Best regards / Cordialement / > Erwann SIMON > > --

Re: SQL statement

lto:ADSM-L@VM.MARIST.EDU] On Behalf Of Erwann SIMON Sent: donderdag 28 januari 2016 16:46 To: ADSM-L@VM.MARIST.EDU Subject: Re: SQL statement Hi Eric, You maybe should add a condition if backup_end is null -- Best regards / Cordialement / مع تحياتي Erwann SIMON - Mail original - De: &q

Re: SQL statement

If you get to a definitive sql statement, would love to see it! Rick -Original Message- From: ADSM: Dist Stor Manager [mailto:ADSM-L@VM.MARIST.EDU] On Behalf Of Skylar Thompson Sent: Friday, January 29, 2016 10:14 AM To: ADSM-L@VM.MARIST.EDU Subject: Re: SQL statement You could get

Re: SQL statement

This is what I came up with: SELECT - f.node_name, - f.filespace_name, - f.backup_end, - CAST(ROUND(o.physical_mb/1024) as int) as "GB Stored" - FROM - filespaces f, - occupancy o - WHERE - o.node_name=f.node_name - AND o.filespace_name=f.filespace_name - AND

Re: SQL statement

To: ADSM-L@VM.MARIST.EDU Subject: Re: SQL statement Hi EJ, I think this will do the trick: SELECT - f.node_name, - f.filespace_name, - f.backup_end, - CAST(ROUND(o.physical_mb/1024) as int) as "GB Stored" - FROM - filespaces f, - occupancy o - WHERE - o.node_name=f

Re: SQL statement

@VM.MARIST.EDU Subject: Re: SQL statement Eric, One other line you can add to the select section is: DAYS(current_timestamp)-DAYS(f.BACKUP_END) AS DAYS_SINCE_BACKUP - Which will list the number of days since this filespace completed a backup. I use that in our query to list old filespaces due for cleanup

Re: SQL statement

You could do that but the query does not fail for filespaces with a BACKUP_END of null it just lists a blank value for DAYS_SINCE_BACKUP On 1/28/2016 10:46 AM, Erwann SIMON wrote: Hi Eric, You maybe should add a condition if backup_end is null -- William Sefranek University of Buffalo

Re: SQL statement

28 januari 2016 15:59 To: ADSM-L@VM.MARIST.EDU Subject: Re: SQL statement Hi EJ, I think this will do the trick: SELECT - f.node_name, - f.filespace_name, - f.backup_end, - CAST(ROUND(o.physical_mb/1024) as int) as "GB Stored" - FROM - filespaces f, - occupancy

Re: SQL statement

r! > This is exactly what I was looking for. Thank you very much for your help!!! > 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 > Skylar Thompson > Se

Re: SQL statement

Bill where are you putting the “DAYS” part of the query? I’m a bit confused and can’t reproduce your statement. -- Hal On 1/28/16, 10:11 AM, "ADSM: Dist Stor Manager on behalf of William Sefranek" wrote: >Eric, > >One other line

Re: SQL statement

It would actually be in the table expression at the top: SELECT ... DAYS(current_timestamp)-DAYS(f.BACKUP_END) AS DAYS_SINCE_BACKUP, - FROM - ... On Thu, Jan 28, 2016 at 03:46:43PM +, King, Harold Clyde (Hal) wrote: > Bill where are you putting the ???DAYS??? part of the query? I???m a

Re: SQL statement

@VM.MARIST.EDU Subject: Re: SQL statement Hi Eric, You maybe should add a condition if backup_end is null -- Best regards / Cordialement / مع تحياتي Erwann SIMON - Mail original - De: "EJ van Loon (ITOPT3) - KLM" <eric-van.l...@klm.com> À: ADSM-L@VM.MARIST.EDU Envoyé: Jeudi

Re: SQL statement

Hi,I'm looking for some help on SQL statement as well. Would you have a statement that lists just the amount of active data a node has stored - not the active and inactive... Thank youMichael On Thursday, January 28, 2016 10:08 AM, William Sefranek wrote: Eric,

Re: SQL statement

Hi EJ, I think this will do the trick: SELECT - f.node_name, - f.filespace_name, - f.backup_end, - CAST(ROUND(o.physical_mb/1024) as int) as "GB Stored" - FROM - filespaces f, - occupancy o - WHERE - o.node_name=f.node_name - AND o.filespace_name=f.filespace_name - AND

Re: SQL statement

Eric, One other line you can add to the select section is: DAYS(current_timestamp)-DAYS(f.BACKUP_END) AS DAYS_SINCE_BACKUP - Which will list the number of days since this filespace completed a backup. I use that in our query to list old filespaces due for cleanup. Bill On 1/28/2016 9:58 AM,

Re: SQL statement not working

@VM.MARIST.EDU Subject: Re: SQL statement not working you’re missing the from clause of your sql statement. Op 1 jul. 2015, om 09:49 heeft Loon, EJ van (ITOPT3) - KLM eric-van.l...@klm.com het volgende geschreven: Hi guys! I'm trying to select all ANE4987E messages for all nodes with the text

Re: SQL statement not working

you’re missing the from clause of your sql statement. Op 1 jul. 2015, om 09:49 heeft Loon, EJ van (ITOPT3) - KLM eric-van.l...@klm.com het volgende geschreven: Hi guys! I'm trying to select all ANE4987E messages for all nodes with the text 'Component Team Windows' in the contact field.

Re: SQL statement to list fs not backed up sorted on size

ORDER BY o.physical_mb DESC 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 Skylar Thompson Sent: donderdag 13 maart 2014 17:37 To: ADSM-L@VM.MARIST.EDU Subject: Re: SQL statement You'll want

Re: SQL statement to list fs not backed up sorted on size

] On Behalf Of Skylar Thompson Sent: donderdag 13 maart 2014 17:37 To: ADSM-L@VM.MARIST.EDU Subject: Re: SQL statement You'll want to do a join across both tables on the node name. Something like this: SELECT f.node_name,f.filespace_name,o.physical_mb - FROM filespaces f - INNER JOIN

Re: SQL statement

Be aware that this does not work for snapdiff backups. RFE 13145 : snapdiff to update last backup fields in filespace data Grant On 14/03/14 03:37, Skylar Thompson wrote: You'll want to do a join across both tables on the node name. Something like this: SELECT

Re: SQL statement

Can't you combine a node name and a filespace name to a unique combination? Cheers, Rick On Thu, Mar 13, 2014 at 5:16 PM, Loon, EJ van (SPLXM) - KLM eric-van.l...@klm.com wrote: Dear TSM-ers, I'm trying to generate a SQL statement to create a list of filespaces which are not backed up for

Re: SQL statement

You'll want to do a join across both tables on the node name. Something like this: SELECT f.node_name,f.filespace_name,o.physical_mb - FROM filespaces f - INNER JOIN occupancy o ON f.node_name=o.node_name - WHERE - (days(f.backup_end)

Re: SQL Statement

Hi Jeff! Thank you VERY much for your reply! You did put me on the right track. It resulted in the most complex SQL statement I ever created: select a.node_name as Nodename,count(*) as - Amount of backup files older than 30 days from backups a,nodes b - where ((days(current_date) -

Re: SQL statement

Hi, Eric. You need to define how to join the tables. In this case, a where condition like... nodes.nodename = backups.nodename Without that, you'll end up with a join table that has every combination of the rows of both tables. Sent from my phone, so please excuse typos. - Reply

Re: SQL statement generates an ANR9999D

Eric, I can execute the sql without receiving the ANR I run it on V 6.3.3.0 Kind regards, Ronald Le Large Information technology officer Storage Management | Dir. 2.8.4.1 European Patent Office -Original Message- From: ADSM: Dist Stor Manager [mailto:ADSM-L@VM.MARIST.EDU] On

Re: SQL statement generates an ANR9999D

Le Large Sent: donderdag 5 december 2013 14:17 To: ADSM-L@VM.MARIST.EDU Subject: Re: SQL statement generates an ANRD Eric, I can execute the sql without receiving the ANR I run it on V 6.3.3.0 Kind regards, Ronald Le Large Information technology officer Storage Management | Dir

Re: Re: SQL statement generates an ANR9999D

... 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 Ronald Le Large Sent: donderdag 5 december 2013 14:17 To: ADSM-L@VM.MARIST.EDU Subject: Re: SQL statement generates an ANRD Eric, I can

Re: SQL statement

I worked with Eric on this. In case anyone else runs into similar... File system directory and file names may well contain underscore (_) characters, which is fine. What may not be realized, however, is that in a Select LIKE, an underscore is a wildcard for any single character. An example

Re: SQL statement

Sims [mailto:r...@bu.edu] Sent: vrijdag 4 juni 2010 16:07 To: Loon, EJ van - SPLXM Subject: Re: SQL statement On Jun 4, 2010, at 9:23 AM, Loon, EJ van - SPLXM wrote: Hi Richard! Thanks, but still not what I expect, an example of one of the many files returned: KL100AT0,/home,/oracle/xt03js

Re: SQL statement

Ow. Richard, do you know if it works this way still in V6? -Original Message- From: ADSM: Dist Stor Manager [mailto:ads...@vm.marist.edu] On Behalf Of Richard Sims Sent: Friday, June 04, 2010 10:36 AM To: ADSM-L@VM.MARIST.EDU Subject: Re: [ADSM-L] SQL statement I worked with Eric on

Re: SQL statement

On Jun 4, 2010, at 10:41 AM, Prather, Wanda wrote: Ow. Richard, do you know if it works this way still in V6? Hi, Wanda - We remain a v5 shop, pending a lot of infrastructure decisions to be made by a new VP, so I haven't that experience. I would expect DB2 to be much more conformant to

Re: SQL statement

Connecting directly to DB2: Database Connection Information Database server= DB2/AIX64 9.7.1 SQL authorization ID = TSMTEST1 Local database alias = TSM1 select tabschema,tabname,owner from syscat.tables where tabname='STGPOOLS' TABSCHEMA TABNAME OWNER

Re: SQL statement

Storage Management Server for AIX - Version 6, Release 2, Level 0.0 (AIX v5.3) tsm: TSMTEST1select stgpool_name from stgpools where stgpool_name like '%\_%' escape '\' STGPOOL_NAME TEST_POOL tsm: TSMTEST1select stgpool_name from stgpools where stgpool_name like

Re: sql statement

-Ashish Sharma wrote: - Could anyone provide me a sql statement which can check if any copy storage pool volume is mounted in tape drive at the moment? I need this to configure script for my DRP. select volume_name from drives where volune_name in (select volume_name from stgpools

Re: SQL statement

Loon, E.J. van - SPLXM wrote: Hi *SM-ers! My management would like to know how much they can save by changing our retention periods. Currently, we store most of our backups for 14 days and I will have to find out how much we save by changing this to 7 days. I'm trying to create a SQL statement

Re: SQL statement

On Nov 23, 2005, at 4:44 AM, Loon, E.J. van - SPLXM wrote: Can anybody help me with creating a SQL statement which tells me how much inactive backup data is stored on the TSM server for a specific node? Eric - See Inactive files, number and bytes in the TSM Wiki or ADSM QuickFacts. Richard

Re: SQL statement

- Original Message - From: Loon, E.J. van - SPLXM [EMAIL PROTECTED] To: ADSM-L@VM.MARIST.EDU Sent: Wednesday, November 23, 2005 10:44 AM Subject: SQL statement Hi *SM-ers! My management would like to know how much they can save by changing our retention periods. Currently, we store most

Re: SQL statement

Joni, Try order by date Leigh -Original Message- From: ADSM: Dist Stor Manager [mailto:[EMAIL PROTECTED] On Behalf Of Joni Moyer Sent: 15 September 2005 14:53 To: ADSM-L@VM.MARIST.EDU Subject: [ADSM-L] SQL statement Hi Everyone! Does anyone know what I am doing wrong with this

Re: SQL statement help

Andy, Thank you very much for your help, the results are exactly what I was looking for. Andrew Raibeck [EMAIL PROTECTED] Sent by: ADSM: Dist Stor Manager ADSM-L@VM.MARIST.EDU 02/28/2005 04:06 PM Please respond to ADSM: Dist Stor Manager ADSM-L@VM.MARIST.EDU To ADSM-L@VM.MARIST.EDU cc

Re: SQL statement help

See http://msgs.adsm.org/cgi-bin/get/adsm0501/349/1.html for a suggested workaround. Refer to APARs IC28825 and IC34609 (at http://www.ibm.com) for more info describing limitations of querying the EVENTS table. Regards, Andy Andy Raibeck IBM Software Group Tivoli Storage Manager Client

Re: SQL statement help

JR, What would be wrong with just doing : q eve * * begindd=-7 ennd=today exceptionsonly=yes Unless your question is just an SQL concern I think this will give you just what you are looking for! -- Regards, Mark D. Rodriguez President MDR Consulting, Inc.

Re: sql statement that shows the active files for a client

See the BACKUPS table. You should find the answer to your question by querying the COLUMNS table for information about the fields in the BACKUPS table: select * from columns where tabname='BACKUPS' or, more briefly, select colname, remarks from columns where tabname='BACKUPS' Regards,

Re: SQL statement

To: [EMAIL PROTECTED] Subject: Re: SQL statement Hi Eric, There is no such table that gives you detailed information on which files were restored. The SUMMARY table will give you basic statistics about restores (SELECT * FROM SUMMARY WHERE ACTIVITY='RESTORE') but that's about it. Regards, Andy Andy

Re: SQL statement

Eric, 'select node_name, filespace_name, filespec from restores' should give the wanted information. best regards, Kurt - Oorspronkelijk bericht - Van : Loon, E.J. van - SPLXM [mailto:[EMAIL PROTECTED] Verzonden : woensdag , december 31, 2003 12:33 PM Aan : [EMAIL PROTECTED]

Re: SQL statement

[mailto:[EMAIL PROTECTED] Sent: Wednesday, December 31, 2003 13:54 To: [EMAIL PROTECTED] Subject: Re: SQL statement Eric, 'select node_name, filespace_name, filespec from restores' should give the wanted information. best regards, Kurt - Oorspronkelijk bericht - Van : Loon, E.J. van - SPLXM

Re: SQL statement

I'm looking for an SQL statement which returns all restores (nodes and filenames) done by my clients. I'm not much of an SQL wizard, so maybe someone is willing to help me or give me a hint? Eric - I don't believe that such capability exists. Certainly, storing minutiae like that would be

Re: SQL statement

-Original Message- From: Richard Sims [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 31, 2003 14:27 To: [EMAIL PROTECTED] Subject: Re: SQL statement I'm looking for an SQL statement which returns all restores (nodes and filenames) done by my clients. I'm not much of an SQL wizard

Re: SQL statement

'Do you really think storing this information will have such a large impact on the database? Storing restore information shouldn't use more database space than storing backup information? Yes...because restorals can be performed over and over again on the same data, and because of the nature of

Re: SQL statement

Manager Subject [EMAIL PROTECTED] Re: SQL statement .EDU 12/31/2003 08:11 AM Please respond to ADSM: Dist Stor Manager [EMAIL

Re: SQL statement

Hi Eric, There is no such table that gives you detailed information on which files were restored. The SUMMARY table will give you basic statistics about restores (SELECT * FROM SUMMARY WHERE ACTIVITY='RESTORE') but that's about it. Regards, Andy Andy Raibeck IBM Software Group Tivoli Storage

Re: SQL statement

Raymond, there are different aspects to your question: 1. If your question is where will the data go if my node were to back up/archive/space manage something is almost impossible to answer with select statements. It depends on the design of your TSM environment - which management classes are

Re: SQL Statement

The issue is a node connects to a policy domain that connects to a management class and in the management class is the copy group, but the decision as to what management class is used in the policy domain is a client include/exclude or a clientopt decision unless the default management class is

Re: SQL statement

Fuhrman [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 29, 2002 5:27 PM To: [EMAIL PROTECTED] Subject: Re: SQL statement search www.adsm.org and will find hundreds of SQL examples. On Wed, 29 May 2002, Bernard Rosenbloom wrote: I have an immediate need to create a list of all my registered nodes

Re: SQL statement

if you would like. Paul D. Seay, Jr. Technical Specialist Naptheon, INC 757-688-8180 -Original Message- From: Joel Fuhrman [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 29, 2002 5:27 PM To: [EMAIL PROTECTED] Subject: Re: SQL statement search www.adsm.org and will find hundreds

Re: SQL statement

I hope this is what you are looking for. select node_name,stgpool_name from occupancy similar to q occup Sung Y. Lee 512-823-1012 Bernard Rosenbloom To: [EMAIL PROTECTED] brosenbl@OPTONLIcc:

Re: SQL statement

Hi, If you install the TSM ODBC Driver and point it at your TSM Server's IP Address and Port 1500, you can do this query in Excel or MS Access. In MS Excel click on Data, Get external data, new database query, Click on the Data Source name that is your ODBC connection Name, and log in to your

Re: SQL statement

Here's an alternate . . . select distinct node_name,stgpool_name from occupancy Also, if you type . . . select * from occupancy . . . you'll get the entire table. Hope this helps. Mahesh [EMAIL PROTECTED] 05/29/02 11:18AM I hope this is what you are looking for. select

Re: SQL statement

search www.adsm.org and will find hundreds of SQL examples. On Wed, 29 May 2002, Bernard Rosenbloom wrote: I have an immediate need to create a list of all my registered nodes and the storage pool(s) each node backs up to. I know almost nothing about creating an SQL statement so if anyone

Re: SQL STATEMENT TO RELATE TABLES

On 25 July 2001, Gerardo Zapata wrote: The objective is to determine the name of a volume containing a specific archive from a specifice node. I use the following SQL to get the data: select volume_name,node_name,type from contents where node_name='MYNODE' and type='Arch' select