Re: Select statement for deduplication & compression statistics ...

[PAC Brion Arnaud]

Hi Anders,

Whished it would be that simple ...

Unfortunately, there are quite a lot of discrepancies between the data reported 
by our query, and the output from "q stg", like demonstrated  here :

Output for "q stg xxx f=d"

DIR_DB2 :  Deduplication Savings: 7,018 G (27.69%)
   Compression Savings: 10,696 G (58.36%)
DIR_EXCH : Deduplication Savings: 40,039 G (71.34%)
   Compression Savings: 6,369 G (39.59%)
DIR_INF :  Deduplication Savings: 0 (0%)
   Compression Savings: 1,695 G (71.90%)
DIR_ORA :  Deduplication Savings: 871 G (42.30%)
   Compression Savings: 959 G (80.74%)
DIR_SQL :  Deduplication Savings: 2,438 G (55.50%)
   Compression Savings: 1,616 G (82.63%)
DIR_UNIX : Deduplication Savings: 2,070 G (8.29%)
   Compression Savings: 17,350 G (75.75%)
DIR_VM :   Deduplication Savings: 16,347 G (45.92%)
   Compression Savings: 10,787 G (56.04%)
DIR_WIN :  Deduplication Savings: 7,018 G (27.69%)
   Compression Savings: 10,697 G (58.35%)

Output of your query :


STGPOOL_NAME Dedup savings   
Compression savings
 --- 
---
DIR_DB2  29.3500%
63.3200%
DIR_EXCH 71.8600%
40.6300%
DIR_INF  .%  
35.4000%
DIR_ORA  17.7000%
23.6800%
DIR_SQL  34.1200%
34.3200%
DIR_UNIX 8.0800% 
73.6800%
DIR_VM   44.8700%
53.7100%
DIR_WIN  3.1800% 
2.9900%

Some results are relatively close, but some other ones (dir_inf, dir_ora, 
dir_sql, dir_win) are totally divergent ...

This is exactly my problem !

This might be due to the fact that the total capacity of my storage array is 
quite huge (Estimated Capacity: 3,098,067 G), shared by all the storage pools, 
and that TSM reported Pct Util precision is not good enough (one decimal only), 
or even something else (reclamable space, additional data for replication ?)  
no idea ... But the values are not matching :(

I'm wondering if IBM could not be making use of the dedupstats table to get its 
values ... I' working on this at present time ...

Cheers.

Arnaud

**
Backup and Recovery Systems Administrator
Panalpina Management Ltd., Basle, Switzerland,
CIT Department Viadukstrasse 42, P.O. Box 4002 Basel/CH
Phone: +41 (61) 226 11 11, FAX: +41 (61) 226 17 01
Direct: +41 (61) 226 19 78
e-mail: arnaud.br...@panalpina.com
This electronic message transmission contains information from Panalpina and is 
confidential or privileged. This information is intended only for the person 
(s) named above. If you are not the intended recipient, any disclosure, 
copying, distribution or use or any other action based on the contents of this 
information is strictly prohibited.

If you receive this electronic transmission in error, please notify the sender 
by e-mail, telephone or fax at the numbers listed above. Thank you.
**


-Original Message-
From: ADSM: Dist Stor Manager [mailto:ADSM-L@VM.MARIST.EDU] On Behalf Of Anders 
Räntilä
Sent: Monday, December 11, 2017 10:30 AM
To: ADSM-L@VM.MARIST.EDU
Subject: Re: Select statement for deduplication & compression statistics ...

Hi

This is simple math

select 
stgpool_name,DEDUP_SPACE_SAVED_MB/(DEDUP_SPACE_SAVED_MB+COMP_SPACE_SAVED_MB+(EST_CAPACITY_MB*PCT_UTILIZED/100))*100||'%'
 as "Dedup savings" from stgpools

select 
stgpool_name,COMP_SPACE_SAVED_MB/(COMP_SPACE_SAVED_MB+(EST_CAPACITY_MB*PCT_UTILIZED/100))*100||'%'
 as "Compression savings" from stgpools


Best Regards
Anders Räntilä

Re: Select statement for deduplication & compression statistics ...

[Anders Räntilä]

Hi

This is simple math

select 
stgpool_name,DEDUP_SPACE_SAVED_MB/(DEDUP_SPACE_SAVED_MB+COMP_SPACE_SAVED_MB+(EST_CAPACITY_MB*PCT_UTILIZED/100))*100||'%'
 as "Dedup savings" from stgpools

select 
stgpool_name,COMP_SPACE_SAVED_MB/(COMP_SPACE_SAVED_MB+(EST_CAPACITY_MB*PCT_UTILIZED/100))*100||'%'
 as "Compression savings" from stgpools


Best Regards
Anders Räntilä

Re: Select statement for client occupancy by management class...???

[Dwight Cook]

select b.node_name,b.class_name,sum(c.file_size) from backups b,contents c 
where b.object_id=c.object_id and b.node_name and b.node_name in 
('','','')  group by b.node_name,b.class_name

(don't expect it to run fast though...)


Dwight E. Cook
Technical Services Prof. Sr. 
TSM Delivery Architect
IBM Cloud
(918) 493-4678



From:   Efim <aefim...@gmail.com>
To: ADSM-L@VM.MARIST.EDU
Date:   04/11/2016 08:20 AM
Subject:        Re: Select statement for client occupancy by management 
class...???
Sent by:"ADSM: Dist Stor Manager" <ADSM-L@VM.MARIST.EDU>



Do you use different storage pools for each management class?
If no - it is very very difficult to calculate occupancy for each stored 
object because you must select from backups table.
I don?t have select for do it.

Efim


> 11 апр. 2016 г., в 15:43, Dwight Cook <coo...@us.ibm.com> написал(а):
> 
> Does anyone have a select statement for client occupancy by management 
> class (so I don't have to recreate the wheel)?
> 
> 
> Dwight E. Cook
> Technical Services Prof. Sr. 
> TSM Delivery Architect
> IBM Cloud
> (918) 493-4678

Re: Select statement for client occupancy by management class...???

[Efim]

Do you use different storage pools for each management class?
If no - it is very very difficult to calculate occupancy for each stored object 
because you must select from backups table.
I don’t have select for do it.

Efim


> 11 апр. 2016 г., в 15:43, Dwight Cook  написал(а):
> 
> Does anyone have a select statement for client occupancy by management 
> class (so I don't have to recreate the wheel)?
> 
> 
> Dwight E. Cook
> Technical Services Prof. Sr. 
> TSM Delivery Architect
> IBM Cloud
> (918) 493-4678

Re: Select Statement Help

[Hanover, Cameron]

This probably isn't completely right, but it might be a start:

select node_name,hl_name,min(backup_date) from backups group by 
node_name,hl_name

--
Cameron Hanover
chano...@umich.edu

Let's get dangerous.
--Darkwing Duck

On Mar 9, 2015, at 3:42 PM, Kamp, Bruce (Ext) bruce.k...@alcon.com wrote:

 I am found a couple TDP SQL nodes that aren't inactivating there backups so 
 TSM isn't expiring them...
 What I am trying to find is the oldest backup date for each server with a 
 name like _TDP.
 
 I can get this:
 Node Name HL_NAME 
BACKUP DATE STATE
 - 
 -  
 --
 XYZ_TDP //
   2009-08-17 ACTIVE_VERSION
 XYZ_TDP //
   2009-09-13 ACTIVE_VERSION
 XYZ_TDP //
   2009-09-14 ACTIVE_VERSION
 XYZ_TDP //
   2009-09-15 ACTIVE_VERSION
 XYZ_TDP //
   2009-09-16 ACTIVE_VERSION
 
 What I really want is something like this:
 Node Name HL_NAME 
BACKUP DATE STATE
 - 
 -  
 --
 XYZ_TDP //
   2009-08-17 ACTIVE_VERSION
 ABC_TDP //
   2009-09-13 ACTIVE_VERSION
 123_TDP //
   2009-09-14 ACTIVE_VERSION
 
 Is this possible ?
 
 Thanks,
 Bruce Kamp
 TSM Administrator
 (817) 568-7331

Re: Select Statement Help

[Kamp, Bruce (Ext)]

Thanks for the help!
This what I ended up with.

SELECT CAST((NODE_NAME) AS CHAR(20)) AS Node Name,CAST(MIN(BACKUP_DATE) AS 
DATE) AS BACKUP DATE FROM BACKUPS WHERE NODE_NAME LIKE '%_TDP' AND 
STATE='ACTIVE_VERSION' AND CLASS_NAME LIKE 
'%DB%' AND BACKUP_DATE  '2015-02-01' AND FILESPACE_NAME NOT LIKE '%$%' GROUP 
BY NODE_NAME


Bruce Kamp
TSM Administrator
(817) 568-7331


-Original Message-
From: ADSM: Dist Stor Manager [mailto:ADSM-L@VM.MARIST.EDU] On Behalf Of 
Hanover, Cameron
Sent: Tuesday, March 10, 2015 11:05 AM
To: ADSM-L@VM.MARIST.EDU
Subject: Re: [ADSM-L] Select Statement Help

This probably isn't completely right, but it might be a start:

select node_name,hl_name,min(backup_date) from backups group by 
node_name,hl_name

--
Cameron Hanover
chano...@umich.edu

Let's get dangerous.
--Darkwing Duck

On Mar 9, 2015, at 3:42 PM, Kamp, Bruce (Ext) bruce.k...@alcon.com wrote:

 I am found a couple TDP SQL nodes that aren't inactivating there backups so 
 TSM isn't expiring them...
 What I am trying to find is the oldest backup date for each server with a 
 name like _TDP.
 
 I can get this:
 Node Name HL_NAME 
BACKUP DATE STATE
 - 
 -  
 --
 XYZ_TDP //
   2009-08-17 ACTIVE_VERSION
 XYZ_TDP //
   2009-09-13 ACTIVE_VERSION
 XYZ_TDP //
   2009-09-14 ACTIVE_VERSION
 XYZ_TDP //
   2009-09-15 ACTIVE_VERSION
 XYZ_TDP //
   2009-09-16 ACTIVE_VERSION
 
 What I really want is something like this:
 Node Name HL_NAME 
BACKUP DATE STATE
 - 
 -  
 --
 XYZ_TDP //
   2009-08-17 ACTIVE_VERSION
 ABC_TDP //
   2009-09-13 ACTIVE_VERSION
 123_TDP //
   2009-09-14 ACTIVE_VERSION
 
 Is this possible ?
 
 Thanks,
 Bruce Kamp
 TSM Administrator
 (817) 568-7331

Re: Select Statement Help

[Ron Delaware]

Bruce,

You could do a group by node_name at the end of your select statement.


Best Regards,
_

email: ron.delaw...@us.ibm.com
Storage Services Offerings






From:   Kamp, Bruce (Ext) bruce.k...@alcon.com
To: ADSM-L@VM.MARIST.EDU
Date:   03/09/15 12:46
Subject:[ADSM-L] Select Statement Help
Sent by:ADSM: Dist Stor Manager ADSM-L@VM.MARIST.EDU



I am found a couple TDP SQL nodes that aren't inactivating there backups
so TSM isn't expiring them...
What I am trying to find is the oldest backup date for each server with a
name like _TDP.

I can get this:
Node Name HL_NAME   BACKUP DATE STATE
-
- 
--
XYZ_TDP //  2009-08-17 ACTIVE_VERSION
XYZ_TDP //  2009-09-13 ACTIVE_VERSION
XYZ_TDP //  2009-09-14 ACTIVE_VERSION
XYZ_TDP //  2009-09-15 ACTIVE_VERSION
XYZ_TDP //  2009-09-16 ACTIVE_VERSION

What I really want is something like this:
Node Name HL_NAME   BACKUP DATE STATE
-
- 
--
XYZ_TDP //  2009-08-17 ACTIVE_VERSION
ABC_TDP //  2009-09-13 ACTIVE_VERSION
123_TDP //  2009-09-14 ACTIVE_VERSION

Is this possible ?

Thanks,
Bruce Kamp
TSM Administrator
(817) 568-7331

Re: Select Statement Help

[Rick Saylor]

James,

What version of TSM? Your select statement will work in TSM 6.3 if
you change start_time to date_time. Please note that I did not
verify the output, only that I got output.

Rick

At 01:39 PM 6/24/2014, you wrote:

Hello Everyone -

I am attempting miserably I might add to write a select statement
that will produce a bunch of message found in the actlog.  The
problem I am having is grabbing the data from the last 24 hours..  I
will schedule this to produce an email each day.. Any thoughts on
how to construct this

Thank you

SELECT * FROM ACTLOG WHERE (MSGNO IN
(406,4952,4954,4958,4960,4957,4970,4959,4961,4965,4963,4966,4967,4968,4969,4964,
4976, 4981)) and start_time=current_timestamp - 24 hours

James Lepre





---
Confidentiality Notice: The information in this e-mail and any
attachments thereto is intended for the named recipient(s)
only.  This e-mail, including any attachments, may contain
information that is privileged and confidential  and subject to
legal restrictions and penalties regarding its unauthorized
disclosure or other use.  If you are not the intended recipient, you
are hereby notified that any disclosure, copying, distribution, or
the taking of any action or inaction in reliance on the contents of
this e-mail and any of its attachments is STRICTLY PROHIBITED.  If
you have received this e-mail in error, please immediately notify
the sender via return e-mail; delete this e-mail and all attachments
from your e-mail  system and your computer system and network; and
destroy any paper copies you may have in your possession. Thank you
for your cooperation.




Rick Saylor  Austin Community College   Voice: (512)223-1182
Director of System Services  9101 Tuscany Way   Fax:   (512)223-1211
Information Technology   Austin, Texas  78754

Re: Select Statement Help

[Lepre, James]

Version 7.1

James Lepre
Infrastructure Support Specialist
Solix, Inc. | 30 Lanidex Plaza West | Parsippany, NJ 07054
T: 973.581.5362 | F: 973.599.6544
www.solixinc.com | Solix on Facebook | Solix on Twitter


-Original Message-
From: ADSM: Dist Stor Manager [mailto:ADSM-L@VM.MARIST.EDU] On Behalf Of Rick 
Saylor
Sent: Tuesday, June 24, 2014 3:19 PM
To: ADSM-L@VM.MARIST.EDU
Subject: Re: [ADSM-L] Select Statement Help

James,

What version of TSM? Your select statement will work in TSM 6.3 if you change 
start_time to date_time. Please note that I did not verify the output, only 
that I got output.

Rick

At 01:39 PM 6/24/2014, you wrote:
Hello Everyone -

I am attempting miserably I might add to write a select statement that 
will produce a bunch of message found in the actlog.  The problem I am 
having is grabbing the data from the last 24 hours..  I will schedule 
this to produce an email each day.. Any thoughts on how to construct 
this

Thank you

SELECT * FROM ACTLOG WHERE (MSGNO IN
(406,4952,4954,4958,4960,4957,4970,4959,4961,4965,4963,4966,4967,4968,4
969,4964, 4976, 4981)) and start_time=current_timestamp - 24 hours

James Lepre





---
Confidentiality Notice: The information in this e-mail and any 
attachments thereto is intended for the named recipient(s) only.  This 
e-mail, including any attachments, may contain information that is 
privileged and confidential  and subject to legal restrictions and 
penalties regarding its unauthorized disclosure or other use.  If you 
are not the intended recipient, you are hereby notified that any 
disclosure, copying, distribution, or the taking of any action or 
inaction in reliance on the contents of this e-mail and any of its 
attachments is STRICTLY PROHIBITED.  If you have received this e-mail 
in error, please immediately notify the sender via return e-mail; 
delete this e-mail and all attachments from your e-mail  system and 
your computer system and network; and destroy any paper copies you may 
have in your possession. Thank you for your cooperation.



Rick Saylor  Austin Community College   Voice: (512)223-1182
Director of System Services  9101 Tuscany Way   Fax:   (512)223-1211
Information Technology   Austin, Texas  78754

Re: Select Statement Help

[white jeff]

SELECT * FROM ACTLOG WHERE MSGNO IN (406,4952,4954,4958,4960,4957,
4970,4959,4961,4965,4963,4966,4967,4968,4969,4964, 4976, 4981) and
start_timecurrent_timestamp - 24 hours


On 24 June 2014 19:39, Lepre, James james.le...@solixinc.com wrote:

 Hello Everyone -

 I am attempting miserably I might add to write a select statement that
 will produce a bunch of message found in the actlog.  The problem I am
 having is grabbing the data from the last 24 hours..  I will schedule this
 to produce an email each day.. Any thoughts on how to construct this

 Thank you

 SELECT * FROM ACTLOG WHERE (MSGNO IN
 (406,4952,4954,4958,4960,4957,4970,4959,4961,4965,4963,4966,4967,4968,4969,4964,
 4976, 4981)) and start_time=current_timestamp - 24 hours

 James Lepre





 ---
 Confidentiality Notice: The information in this e-mail and any attachments
 thereto is intended for the named recipient(s) only.  This e-mail,
 including any attachments, may contain information that is privileged and
 confidential  and subject to legal restrictions and penalties regarding its
 unauthorized disclosure or other use.  If you are not the intended
 recipient, you are hereby notified that any disclosure, copying,
 distribution, or the taking of any action or inaction in reliance on the
 contents of this e-mail and any of its attachments is STRICTLY PROHIBITED.
  If you have received this e-mail in error, please immediately notify the
 sender via return e-mail; delete this e-mail and all attachments from your
 e-mail  system and your computer system and network; and destroy any paper
 copies you may have in your possession. Thank you for your cooperation.

Re: Select statement to find data archived total

[Erwann SIMON]

Hi Joni,

As you could see, size is not shown is archive table. It would need to select 
from the content table too. The select required will need a join between 
archive and content and would be impossible to execute on a real life TSM 
server.

I think that you may find an answer with export node for datatype archive in 
preview mode using the todate option.


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

-Original Message-
From: Moyer, Joni M joni.mo...@highmark.com
Sender: ADSM: Dist Stor Manager ADSM-L@VM.MARIST.EDU
Date: Thu, 15 Mar 2012 19:01:42 
To: ADSM-L@VM.MARIST.EDU
Reply-To: ADSM: Dist Stor Manager ADSM-L@VM.MARIST.EDU
Subject: [ADSM-L] Select statement to find data archived total

Hi Everyone,

I was asked to find how much space would be saved if there were no data 
retentions for archived data for more than 3 years.  I currently have archive 
management classes that go as high as 14 years.  Is there a way to figure out 
how much space is utilized per client that has data archived either per 
management class or by file?  When I did a select * from archives where 
node_name='TEST' it shows me all of the files and the management class name, 
but it doesn't show me the file size which is also what I'd need.

 NODE_NAME: TEST
FILESPACE_NAME: /export/home
  FILESPACE_ID: 22
  TYPE: FILE
   HL_NAME: /qips/dat/
   LL_NAME: qsesc20041.dsc
 OBJECT_ID: 149888292
  ARCHIVE_DATE: 2006-02-02 09:35:08.00
 OWNER: lidqips
   DESCRIPTION: archive.sh 0202060935
CLASS_NAME: ARC2655

Any thoughts?  Thanks!

Joni



This e-mail and any attachments to it are confidential and are intended solely 
for use of the individual or entity to whom they are addressed. If you have 
received this e-mail in error, please notify the sender immediately and then 
delete it. If you are not the intended recipient, you must not keep, use, 
disclose, copy or distribute this e-mail without the author's prior permission. 
The views expressed in this e-mail message do not necessarily represent the 
views of Highmark Inc., its subsidiaries, or affiliates.

Re: Select Statement

[Lepre, James]

Does anyone know how to take this select command which produces only one month 
of data and allow it to go back say 1 year if possible. I have tried to figure 
it out but nothing works

select date(start_time) as Start Date,time(start_time) as Start 
Time,date(end_time) as End Date,time(end_time) as End Time,number as 
Process #,activity,schedule_name,entity,bytes,successful from summary where 
schedule_name='SchedNAME'

Thanks Everyone and Happy Holidays

  
  
---
Confidentiality Notice: The information in this e-mail and any attachments 
thereto is intended for the named recipient(s) only.  This e-mail, including 
any attachments, may contain information that is privileged and confidential  
and subject to legal restrictions and penalties regarding its unauthorized 
disclosure or other use.  If you are not the intended recipient, you are hereby 
notified that any disclosure, copying, distribution, or the taking of any 
action or inaction in reliance on the contents of this e-mail and any of its 
attachments is STRICTLY PROHIBITED.  If you have received this e-mail in error, 
please immediately notify the sender via return e-mail; delete this e-mail and 
all attachments from your e-mail  system and your computer system and network; 
and destroy any paper copies you may have in your possession. Thank you for 
your cooperation.

Re: Select Statement

[Bob Levad]

James,

set summaryretention xx

Merry Christmas

Bob.

-Original Message-
From: ADSM: Dist Stor Manager [mailto:ADSM-L@VM.MARIST.EDU] On Behalf Of Lepre, 
James
Sent: Thursday, December 22, 2011 10:05 AM
To: ADSM-L@VM.MARIST.EDU
Subject: Re: [ADSM-L] Select Statement

Does anyone know how to take this select command which produces only one month 
of data and allow it to go back say 1 year if possible. I have tried to figure 
it out but nothing works

select date(start_time) as Start Date,time(start_time) as Start 
Time,date(end_time) as End Date,time(end_time) as End Time,number as 
Process #,activity,schedule_name,entity,bytes,successful from summary where 
schedule_name='SchedNAME'

Thanks Everyone and Happy Holidays



---
Confidentiality Notice: The information in this e-mail and any attachments 
thereto is intended for the named recipient(s) only.  This e-mail, including 
any attachments, may contain information that is privileged and confidential  
and subject to legal restrictions and penalties regarding its unauthorized 
disclosure or other use.  If you are not the intended recipient, you are hereby 
notified that any disclosure, copying, distribution, or the taking of any 
action or inaction in reliance on the contents of this e-mail and any of its 
attachments is STRICTLY PROHIBITED.  If you have received this e-mail in error, 
please immediately notify the sender via return e-mail; delete this e-mail and 
all attachments from your e-mail  system and your computer system and network; 
and destroy any paper copies you may have in your possession. Thank you for 
your cooperation.
This electronic transmission and any documents accompanying this electronic 
transmission contain confidential information belonging to the sender. This 
information may be legally privileged. The information is intended only for the 
use of the individual or entity named above. If you are not the intended 
recipient, you are hereby notified that any disclosure, copying, distribution, 
or the taking of any action in reliance on or regarding the contents of this 
electronically transmitted information is strictly prohibited.

Re: Select Statement

[Richard Sims]

Carefully consider extending the retention on the Summary table, as the amount 
of data can be large, taxing your database.  What I prefer to do is keep a 
smallish Summary table, given that the need to query old data is rare, where I 
keep 30 days of Activity Log data in the database and older days as text files 
stored in HSM, which I can grep or analyze for historic information.  The TSM 
accounting records are a further good source of historic statistical info on 
client sessions.

 Richard Simsstill plugging away at Boston University

Re: select statement to display readonly and filling tapes

[Timothy Hughes]

Thanks for this script

Regards
Tim

On 11/16/2010 6:49 AM, J. Pohlmann wrote:

For what it's worth, here is my reado script to display problem tapes:

issue message i Read Only Volumes
q vol acc=reado
issue message i Unavailable Volumes
q vol acc=unav
issue message i Destroyed Volumes
q vol acc=destroyed
issue message i The following volumes had I/O errors:
select volume_name as VOLUME   , stgpool_name,
write_errors, read_errors from volumes where write_errors0 or read_errors

0

Joerg Pohlmann
250-585-3711



-Original Message-
From: ADSM: Dist Stor Manager [mailto:ads...@vm.marist.edu] On Behalf Of
Guido Martínez
Sent: Monday, November 15, 2010 09:35
To: ADSM-L@VM.MARIST.EDU
Subject: Re: [ADSM-L] select statement to display readonly and filling tapes

Perhaps this would also work:

Select VOLUME_NAME from VOLUMES where status='FILLING' and access='READONLY'

El nov 15, 2010 11:30 a.m., Keith M Williamskwill...@us.ibm.com
escribió:

How about  this for TSM:

select VOLUME_NAME,ACCESS from volumes where access ='READONLY', STATUS from
media where VOLUME_NAME='FILLING'

Keith M Williams/Dubuque/IBM
Unix System Administrator, IBM Dubuque
ITDelivery, Global Technology Services
kwill...@us.ibm.com




From:

Timothy Hughestimothy.hug...@oit.state.nj.us
To:
ADSM-L@vm.marist.edu
Date:
11/15/2010 07:40 AM
Subject:

select statement to display readonly and filling tapes Sent by:
ADSM: Dist Stor ManagerADSM-L@vm.marist.edu




Hi

I am trying to add filling tapes to this select statement and I am having
no luck does anyo...

Re: select statement to display readonly and filling tapes

[Timothy Hughes]

Thanks Maurice, Thomas, Guido, keith,  Heinz, Richard and Steven for
your responses

I had to retype the command  there must have been a character  binary
character not visible that was the issue which cause the error. I
originally did a cut and paste.

Best
Regards

On 11/15/2010 12:00 PM, Maurice van 't Loo wrote:

Heey Timothy

Seems you accidently copied 2 lines, in stead of the command in 1
line.  status='FILLING' has been processed as an other command, so
both lines gave errors.

Try again the same command, but be sure it's in 1 line.
Or use a - at the end of each line to continue, but best is to just use 1 line.

Regards,
Maurice



2010/11/15 Timothy Hughestimothy.hug...@oit.state.nj.us:

thanks steve!

I tried that command failed, and also I want to select the readonly
tapes that say filling also.  So I replaced the or with and. this
command failed also

tsm:select VOLUME_NAME,ACCESS from volumes where access='READONLY' or
status='FILLING'

ANR0162W Supplemental database diagnostic information:  -1:42601:-104
([IBM][CLI Driver][DB2/AIX64] SQL0104N  An unexpected token
END-OF-STATEMENT
was found following cess = 'READONLY' or.  Expected tokens may include:
boolean_term.  SQLSTATE=42601
).
ANR0516E SQL processing for statement select VOLUME_NAME , ACCESS from
volumes
where access = 'READONLY' or  failed.
ANS8001I Return code 3.

tsm: status='FILLING'
ANS8001I Return code 3.

tsm: TSMCORE

tsm: TSMCORE


On 11/15/2010 8:48 AM, Steven Langdale wrote:

How about:

select VOLUME_NAME,ACCESS from volumes where access='READONLY' or
status='FILLING'

Steven




Timothy Hughestimothy.hug...@oit.state.nj.us
Sent by: ADSM: Dist Stor ManagerADSM-L@VM.MARIST.EDU
15/11/2010 13:39
Please respond to
ADSM: Dist Stor ManagerADSM-L@VM.MARIST.EDU


To
ADSM-L@VM.MARIST.EDU
cc

Subject
[ADSM-L] select statement to display readonly and filling tapes




Caterpillar: Confidential Green Retain Until: 15/12/2010



Hi

I am trying to add filling tapes to this select statement and I am
having no luck does anyone have a select statement that shows this?



I already have most of the statement below i just need to add filling
to the statement

select VOLUME_NAME,ACCESS from volumes where access ='READONLY'


thanks for any help

Re: select statement to display readonly and filling tapes

[J. Pohlmann]

For what it's worth, here is my reado script to display problem tapes:

issue message i Read Only Volumes
q vol acc=reado
issue message i Unavailable Volumes
q vol acc=unav
issue message i Destroyed Volumes
q vol acc=destroyed
issue message i The following volumes had I/O errors:
select volume_name as VOLUME   , stgpool_name,
write_errors, read_errors from volumes where write_errors 0 or read_errors
0

Joerg Pohlmann
250-585-3711



-Original Message-
From: ADSM: Dist Stor Manager [mailto:ads...@vm.marist.edu] On Behalf Of
Guido Martínez
Sent: Monday, November 15, 2010 09:35
To: ADSM-L@VM.MARIST.EDU
Subject: Re: [ADSM-L] select statement to display readonly and filling tapes

Perhaps this would also work:

Select VOLUME_NAME from VOLUMES where status='FILLING' and access='READONLY'

El nov 15, 2010 11:30 a.m., Keith M Williams kwill...@us.ibm.com
escribió:

How about  this for TSM:

select VOLUME_NAME,ACCESS from volumes where access ='READONLY', STATUS from
media where VOLUME_NAME='FILLING'

Keith M Williams/Dubuque/IBM
Unix System Administrator, IBM Dubuque
ITDelivery, Global Technology Services
kwill...@us.ibm.com




From:

Timothy Hughes timothy.hug...@oit.state.nj.us
To:
ADSM-L@vm.marist.edu
Date:
11/15/2010 07:40 AM
Subject:

select statement to display readonly and filling tapes Sent by:
ADSM: Dist Stor Manager ADSM-L@vm.marist.edu




Hi

I am trying to add filling tapes to this select statement and I am having
no luck does anyo...

Re: select statement to display readonly and filling tapes

[Steven Langdale]

How about:

select VOLUME_NAME,ACCESS from volumes where access='READONLY' or
status='FILLING'

Steven




Timothy Hughes timothy.hug...@oit.state.nj.us
Sent by: ADSM: Dist Stor Manager ADSM-L@VM.MARIST.EDU
15/11/2010 13:39
Please respond to
ADSM: Dist Stor Manager ADSM-L@VM.MARIST.EDU


To
ADSM-L@VM.MARIST.EDU
cc

Subject
[ADSM-L] select statement to display readonly and filling tapes




Caterpillar: Confidential Green Retain Until: 15/12/2010



Hi

I am trying to add filling tapes to this select statement and I am
having no luck does anyone have a select statement that shows this?



I already have most of the statement below i just need to add filling
to the statement

select VOLUME_NAME,ACCESS from volumes where access ='READONLY'


thanks for any help

Re: select statement to display readonly and filling tapes

[Timothy Hughes]

thanks steve!

I tried that command failed, and also I want to select the readonly
tapes that say filling also.  So I replaced the or with and. this
command failed also

tsm: select VOLUME_NAME,ACCESS from volumes where access='READONLY' or
status='FILLING'

ANR0162W Supplemental database diagnostic information:  -1:42601:-104
([IBM][CLI Driver][DB2/AIX64] SQL0104N  An unexpected token
END-OF-STATEMENT
was found following cess = 'READONLY' or.  Expected tokens may include:
boolean_term.  SQLSTATE=42601
).
ANR0516E SQL processing for statement select VOLUME_NAME , ACCESS from
volumes
where access = 'READONLY' or  failed.
ANS8001I Return code 3.

tsm: status='FILLING'
ANS8001I Return code 3.

tsm: TSMCORE

tsm: TSMCORE


On 11/15/2010 8:48 AM, Steven Langdale wrote:

How about:

select VOLUME_NAME,ACCESS from volumes where access='READONLY' or
status='FILLING'

Steven




Timothy Hughestimothy.hug...@oit.state.nj.us
Sent by: ADSM: Dist Stor ManagerADSM-L@VM.MARIST.EDU
15/11/2010 13:39
Please respond to
ADSM: Dist Stor ManagerADSM-L@VM.MARIST.EDU


To
ADSM-L@VM.MARIST.EDU
cc

Subject
[ADSM-L] select statement to display readonly and filling tapes




Caterpillar: Confidential Green Retain Until: 15/12/2010



Hi

I am trying to add filling tapes to this select statement and I am
having no luck does anyone have a select statement that shows this?



I already have most of the statement below i just need to add filling
to the statement

select VOLUME_NAME,ACCESS from volumes where access ='READONLY'


thanks for any help

Re: select statement to display readonly and filling tapes

[Guido Martínez]

If you only want to find tapes in FILLING this select should be more than
enough:

Select VOLUME_NAME from VOLUMES where status='FILLING'

El nov 15, 2010 10:50 a.m., Steven Langdale steven.langd...@cat.com
escribió:

How about:

select VOLUME_NAME,ACCESS from volumes where access='READONLY' or
status='FILLING'

Steven




Timothy Hughes timothy.hug...@oit.state.nj.us
Sent by: ADSM: Dist Stor Manager ADSM-L@VM.MARIST.EDU
15/11/2010 13:39
Please respond to
ADSM: Dist Stor Manager ADSM-L@VM.MARIST.EDU


To
ADSM-L@VM.MARIST.EDU
cc

Subject
[ADSM-L] select statement to display readonly and filling tapes




Caterpillar: Confidential Green Retain Until: 15/12/2010




Hi

I am trying to add filling tapes to this select statement and I am
having no luck does anyo...

Re: select statement to display readonly and filling tapes

[Heinz Flemming]

According to Timothy Hughes:
 thanks steve!

 I tried that command failed, and also I want to select the readonly
 tapes that say filling also.  So I replaced the or with and. this
 command failed also

 tsm: select VOLUME_NAME,ACCESS from volumes where access='READONLY' or
 status='FILLING'

 ANR0162W Supplemental database diagnostic information:  -1:42601:-104
 ([IBM][CLI Driver][DB2/AIX64] SQL0104N  An unexpected token
 END-OF-STATEMENT
 was found following cess = 'READONLY' or.  Expected tokens may include:
 boolean_term.  SQLSTATE=42601
 ).
 ANR0516E SQL processing for statement select VOLUME_NAME , ACCESS from
 volumes
 where access = 'READONLY' or  failed.
 ANS8001I Return code 3.

 tsm: status='FILLING'
 ANS8001I Return code 3.


select VOLUME_NAME,ACCESS from volumes where status in ('FILLING','FULL')



Heinz

--
Karlsruher Institut fuer Technologie (KIT)
Steinbuch Centre for Computing (SCC)
D-76131 Karlsruhe
--

Re: select statement to display readonly and filling tapes

[Keith M Williams]

How about  this for TSM:

select VOLUME_NAME,ACCESS from volumes where access ='READONLY', STATUS
from media where VOLUME_NAME='FILLING'

Keith M Williams/Dubuque/IBM
Unix System Administrator, IBM Dubuque
ITDelivery, Global Technology Services
kwill...@us.ibm.com




From:
Timothy Hughes timothy.hug...@oit.state.nj.us
To:
ADSM-L@vm.marist.edu
Date:
11/15/2010 07:40 AM
Subject:
select statement to display readonly and filling tapes
Sent by:
ADSM: Dist Stor Manager ADSM-L@vm.marist.edu



Hi

I am trying to add filling tapes to this select statement and I am
having no luck does anyone have a select statement that shows this?



I already have most of the statement below i just need to add filling
to the statement

select VOLUME_NAME,ACCESS from volumes where access ='READONLY'


thanks for any help

Re: select statement to display readonly and filling tapes

[Guido Martínez]

Perhaps this would also work:

Select VOLUME_NAME from VOLUMES where status='FILLING' and access='READONLY'

El nov 15, 2010 11:30 a.m., Keith M Williams kwill...@us.ibm.com
escribió:

How about  this for TSM:

select VOLUME_NAME,ACCESS from volumes where access ='READONLY', STATUS
from media where VOLUME_NAME='FILLING'

Keith M Williams/Dubuque/IBM
Unix System Administrator, IBM Dubuque
ITDelivery, Global Technology Services
kwill...@us.ibm.com




From:

Timothy Hughes timothy.hug...@oit.state.nj.us
To:
ADSM-L@vm.marist.edu
Date:
11/15/2010 07:40 AM
Subject:

select statement to display readonly and filling tapes
Sent by:
ADSM: Dist Stor Manager ADSM-L@vm.marist.edu




Hi

I am trying to add filling tapes to this select statement and I am
having no luck does anyo...

Re: select statement to display readonly and filling tapes

[Thomas Denier]

-Timothy Hughes wrote: -

I tried that command failed, and also I want to select the
readonly 
tapes that say filling also.  So I replaced the or with and.
this 
command failed also 
 
tsm: select VOLUME_NAME,ACCESS from volumes where access='READONLY'
or 
status='FILLING' 
 
ANR0162W Supplemental database diagnostic information: 
-1:42601:-104 
([IBM][CLI Driver][DB2/AIX64] SQL0104N  An unexpected token 
END-OF-STATEMENT 
was found following cess = 'READONLY' or.  Expected tokens may
include: 
boolean_term.  SQLSTATE=42601 
). 
ANR0516E SQL processing for statement select VOLUME_NAME , ACCESS
from 
volumes 
where access = 'READONLY' or  failed. 
ANS8001I Return code 3. 
 
tsm: status='FILLING' 
ANS8001I Return code 3. 

The error messages suggest that you tried to split a command into
two lines without using the appropriate continuation character
at the end of the first line (a hyphen for a TSM macro or an
interactive dsmadmc session, a backslash for the more commonly
used Unix shells, or a caret for the Windows command line
environment).

Re: select statement to display readonly and filling tapes

[Maurice van 't Loo]

Heey Timothy

Seems you accidently copied 2 lines, in stead of the command in 1
line.  status='FILLING' has been processed as an other command, so
both lines gave errors.

Try again the same command, but be sure it's in 1 line.
Or use a - at the end of each line to continue, but best is to just use 1 line.

Regards,
Maurice



2010/11/15 Timothy Hughes timothy.hug...@oit.state.nj.us:
 thanks steve!

 I tried that command failed, and also I want to select the readonly
 tapes that say filling also.  So I replaced the or with and. this
 command failed also

 tsm: select VOLUME_NAME,ACCESS from volumes where access='READONLY' or
 status='FILLING'

 ANR0162W Supplemental database diagnostic information:  -1:42601:-104
 ([IBM][CLI Driver][DB2/AIX64] SQL0104N  An unexpected token
 END-OF-STATEMENT
 was found following cess = 'READONLY' or.  Expected tokens may include:
 boolean_term.  SQLSTATE=42601
 ).
 ANR0516E SQL processing for statement select VOLUME_NAME , ACCESS from
 volumes
 where access = 'READONLY' or  failed.
 ANS8001I Return code 3.

 tsm: status='FILLING'
 ANS8001I Return code 3.

 tsm: TSMCORE

 tsm: TSMCORE


 On 11/15/2010 8:48 AM, Steven Langdale wrote:

 How about:

 select VOLUME_NAME,ACCESS from volumes where access='READONLY' or
 status='FILLING'

 Steven




 Timothy Hughestimothy.hug...@oit.state.nj.us
 Sent by: ADSM: Dist Stor ManagerADSM-L@VM.MARIST.EDU
 15/11/2010 13:39
 Please respond to
 ADSM: Dist Stor ManagerADSM-L@VM.MARIST.EDU


 To
 ADSM-L@VM.MARIST.EDU
 cc

 Subject
 [ADSM-L] select statement to display readonly and filling tapes




 Caterpillar: Confidential Green Retain Until: 15/12/2010



 Hi

 I am trying to add filling tapes to this select statement and I am
 having no luck does anyone have a select statement that shows this?



 I already have most of the statement below i just need to add filling
 to the statement

 select VOLUME_NAME,ACCESS from volumes where access ='READONLY'


 thanks for any help

Re: Select statement to only list backups with particular event status

[Richard Sims]

The EVENTS table has been an oddball in TSM, as I note in ADSM QuickFacts, 
because of the way it was engineered.  Using relative timestamp references 
traditionally doesn't work, so you need to employ an absolute timestamp, as in
 select * from events WHERE SCHEDULED_START = '2010-06-28'

Note also that there is no actual_time column in instances of the EVENTS table 
that I know of.  Use the following to verify column names:
select * from syscat.columns where tabname='EVENTS'
I haven't seen 'In Progress' as one of the possible status values; but things 
change over numerous releases.

Richard Sims

On Jun 29, 2010, at 11:05 AM, Moyer, Joni M wrote:

 Hello everyone,
 
 I am trying to create a select statement that will list all backups for 
 clients in the domain: windows  hmig that started after yesterday at 6PM 
 until today at the current date/time that had a status of: Missed, In 
 Progress, Fail%, Started.
 
 I tried the below select statement but it is not giving me everything that I 
 am looking for and it's also reporting future events which I don't want.  Can 
 anyone tell me what I'm doing wrong?
 
 Thanks in advance!
 
 select event as Event,date(actual_start) as Date,time(actual_start) as 
 Start,time(Completed) as End, node_name, domain_name,Status from events 
 where domain_name='WINDOWS' or domain_name='HMIG' and status like 'Fail%' or 
 status='Missed' or status='In Progres' or status='Started' and 
 actual_time=current_timestamp-1 day

Re: Select statement to only list backups with particular event status

[Moyer, Joni M]

Hi Richard,

Is there any other way to do a report that would list missed, failed, in 
progress and started backups then on a daily basis running it from the prior 
day at 6PM until the current time the script is run at 9AM?  I currently run q 
event at 9AM which catches the missed  failed backups, but nothing that 
currently accounts for anything that is in progress or has been started.

Any suggestions/ideas are appreciated!  This one has me stumped!

-Original Message-
From: ADSM: Dist Stor Manager [mailto:ads...@vm.marist.edu] On Behalf Of 
Richard Sims
Sent: Tuesday, June 29, 2010 12:09 PM
To: ADSM-L@VM.MARIST.EDU
Subject: Re: Select statement to only list backups with particular event status

The EVENTS table has been an oddball in TSM, as I note in ADSM QuickFacts, 
because of the way it was engineered.  Using relative timestamp references 
traditionally doesn't work, so you need to employ an absolute timestamp, as in
 select * from events WHERE SCHEDULED_START = '2010-06-28'

Note also that there is no actual_time column in instances of the EVENTS table 
that I know of.  Use the following to verify column names:
select * from syscat.columns where tabname='EVENTS'
I haven't seen 'In Progress' as one of the possible status values; but things 
change over numerous releases.

Richard Sims

On Jun 29, 2010, at 11:05 AM, Moyer, Joni M wrote:

 Hello everyone,

 I am trying to create a select statement that will list all backups for 
 clients in the domain: windows  hmig that started after yesterday at 6PM 
 until today at the current date/time that had a status of: Missed, In 
 Progress, Fail%, Started.

 I tried the below select statement but it is not giving me everything that I 
 am looking for and it's also reporting future events which I don't want.  Can 
 anyone tell me what I'm doing wrong?

 Thanks in advance!

 select event as Event,date(actual_start) as Date,time(actual_start) as 
 Start,time(Completed) as End, node_name, domain_name,Status from events 
 where domain_name='WINDOWS' or domain_name='HMIG' and status like 'Fail%' or 
 status='Missed' or status='In Progres' or status='Started' and 
 actual_time=current_timestamp-1 day


This e-mail and any attachments to it are confidential and are intended solely 
for use of the individual or entity to whom they are addressed.  If you have 
received this e-mail in error, please notify the sender immediately and then 
delete it.  If you are not the intended recipient, you must not keep, use, 
disclose, copy or distribute this e-mail without the author's prior permission. 
 The views expressed in this e-mail message do not necessarily represent the 
views of Highmark Inc., its subsidiaries, or affiliates.

Re: Select statement to only list backups with particular event status

[Richard Sims]

The only way to see status=Started jobs is if you perform a query while they 
are under way: if you really wanted that, a cron job capture would take care of 
it.  Running a query after all scheduled backups have finished will report 
final status of Completed (or Missed or Failed), which is probably what you get 
at 09:00.  And, of course, the Events table does not contain information for 
backups which are independently performed on the client.

Such event information is superficial, of course...  The client administrator 
should be reviewing backup logs (and the dsmerror.log!) for anomalies which may 
be delaying backups (e.g., retries) or be causing individual files or whole 
file systems to not get backed up (as in a Domain omission or forgotten, old 
Exclude).

Richard Sims   http://people.bu.edu/rbs

On Jun 29, 2010, at 1:28 PM, Moyer, Joni M wrote:

 Hi Richard,
 
 Is there any other way to do a report that would list missed, failed, in 
 progress and started backups then on a daily basis running it from the prior 
 day at 6PM until the current time the script is run at 9AM?  I currently run 
 q event at 9AM which catches the missed  failed backups, but nothing that 
 currently accounts for anything that is in progress or has been started.
 
 Any suggestions/ideas are appreciated!  This one has me stumped!

Re: Select statement to only list backups with particular event status

[Lindsay Morris]

Joni, Query Events has other problems, too:

-- You might have a machine that isn't on any schedule, for example.  Query
Events won't tell you that.
-- You might have include-exclude statements, or domain statements, that
cause a directory or an entire drive to be skipped.  It won't tell you about
that.
-- You might have retention policies that are way too short (so you can't
recover from last week), or way too long (so you're wasting tons of
storage).  Again, no indication from query events.
-- You might even have machines (VMs?) in production that nobody ever
registered to TSM.  Nothing in TSM will tell you about that.

One thing you can do is stop using query backups, and use the filespace
table's backup_end field to find backups that are older than a day or two.
That solves some (not all) of the above problems.
We have a more thorough solution here
goog_918866862http://www.tsmworks.com/art.


Hope this helps.

Lindsay Morris
CEO, TSMworks
Tel. 1-859-539-9900 skype:18595399900?call
lind...@tsmworks.com


On Tue, Jun 29, 2010 at 1:28 PM, Moyer, Joni M joni.mo...@highmark.comwrote:

 Hi Richard,

 Is there any other way to do a report that would list missed, failed, in
 progress and started backups then on a daily basis running it from the prior
 day at 6PM until the current time the script is run at 9AM?  I currently run
 q event at 9AM which catches the missed  failed backups, but nothing that
 currently accounts for anything that is in progress or has been started.

 Any suggestions/ideas are appreciated!  This one has me stumped!

 -Original Message-
 From: ADSM: Dist Stor Manager [mailto:ads...@vm.marist.edu] On Behalf Of
 Richard Sims
 Sent: Tuesday, June 29, 2010 12:09 PM
 To: ADSM-L@VM.MARIST.EDU
 Subject: Re: Select statement to only list backups with particular event
 status

 The EVENTS table has been an oddball in TSM, as I note in ADSM QuickFacts,
 because of the way it was engineered.  Using relative timestamp references
 traditionally doesn't work, so you need to employ an absolute timestamp, as
 in
 select * from events WHERE SCHEDULED_START = '2010-06-28'

 Note also that there is no actual_time column in instances of the EVENTS
 table that I know of.  Use the following to verify column names:
select * from syscat.columns where tabname='EVENTS'
 I haven't seen 'In Progress' as one of the possible status values; but
 things change over numerous releases.

Richard Sims

 On Jun 29, 2010, at 11:05 AM, Moyer, Joni M wrote:

  Hello everyone,
 
  I am trying to create a select statement that will list all backups for
 clients in the domain: windows  hmig that started after yesterday at 6PM
 until today at the current date/time that had a status of: Missed, In
 Progress, Fail%, Started.
 
  I tried the below select statement but it is not giving me everything
 that I am looking for and it's also reporting future events which I don't
 want.  Can anyone tell me what I'm doing wrong?
 
  Thanks in advance!
 
  select event as Event,date(actual_start) as Date,time(actual_start)
 as Start,time(Completed) as End, node_name, domain_name,Status from
 events where domain_name='WINDOWS' or domain_name='HMIG' and status like
 'Fail%' or status='Missed' or status='In Progres' or status='Started' and
 actual_time=current_timestamp-1 day


 This e-mail and any attachments to it are confidential and are intended
 solely for use of the individual or entity to whom they are addressed.  If
 you have received this e-mail in error, please notify the sender immediately
 and then delete it.  If you are not the intended recipient, you must not
 keep, use, disclose, copy or distribute this e-mail without the author's
 prior permission.  The views expressed in this e-mail message do not
 necessarily represent the views of Highmark Inc., its subsidiaries, or
 affiliates.

Re: Select statement to only list backups with particular event status

[Sheridan, Peter T.]

Here is a select statement that I use to get all of this info for the
last 24 hours: 

select * from events where status!='Completed' and status!='Future' and
status!='Pending' and status!='Started' and scheduled_start =
'2010-01-01' and scheduled_start = current_timestamp - 24 hours


 

-Original Message-
From: ADSM: Dist Stor Manager [mailto:ads...@vm.marist.edu] On Behalf Of
Richard Sims
Sent: Tuesday, June 29, 2010 12:45 PM
To: ADSM-L@vm.marist.edu
Subject: Re: [ADSM-L] Select statement to only list backups with
particular event status

The only way to see status=Started jobs is if you perform a query while
they are under way: if you really wanted that, a cron job capture would
take care of it.  Running a query after all scheduled backups have
finished will report final status of Completed (or Missed or Failed),
which is probably what you get at 09:00.  And, of course, the Events
table does not contain information for backups which are independently
performed on the client.

Such event information is superficial, of course...  The client
administrator should be reviewing backup logs (and the dsmerror.log!)
for anomalies which may be delaying backups (e.g., retries) or be
causing individual files or whole file systems to not get backed up (as
in a Domain omission or forgotten, old Exclude).

Richard Sims   http://people.bu.edu/rbs

On Jun 29, 2010, at 1:28 PM, Moyer, Joni M wrote:

 Hi Richard,
 
 Is there any other way to do a report that would list missed, failed,
in progress and started backups then on a daily basis running it from
the prior day at 6PM until the current time the script is run at 9AM?  I
currently run q event at 9AM which catches the missed  failed backups,
but nothing that currently accounts for anything that is in progress or
has been started.
 
 Any suggestions/ideas are appreciated!  This one has me stumped!

Re: Select statement to only list backups with particular event status

[Moyer, Joni M]

That worked like a charm!  Thanks so much!

-Original Message-
From: ADSM: Dist Stor Manager [mailto:ads...@vm.marist.edu] On Behalf Of 
Sheridan, Peter T.
Sent: Tuesday, June 29, 2010 1:49 PM
To: ADSM-L@VM.MARIST.EDU
Subject: Re: Select statement to only list backups with particular event status

Here is a select statement that I use to get all of this info for the
last 24 hours:

select * from events where status!='Completed' and status!='Future' and
status!='Pending' and status!='Started' and scheduled_start =
'2010-01-01' and scheduled_start = current_timestamp - 24 hours




-Original Message-
From: ADSM: Dist Stor Manager [mailto:ads...@vm.marist.edu] On Behalf Of
Richard Sims
Sent: Tuesday, June 29, 2010 12:45 PM
To: ADSM-L@vm.marist.edu
Subject: Re: [ADSM-L] Select statement to only list backups with
particular event status

The only way to see status=Started jobs is if you perform a query while
they are under way: if you really wanted that, a cron job capture would
take care of it.  Running a query after all scheduled backups have
finished will report final status of Completed (or Missed or Failed),
which is probably what you get at 09:00.  And, of course, the Events
table does not contain information for backups which are independently
performed on the client.

Such event information is superficial, of course...  The client
administrator should be reviewing backup logs (and the dsmerror.log!)
for anomalies which may be delaying backups (e.g., retries) or be
causing individual files or whole file systems to not get backed up (as
in a Domain omission or forgotten, old Exclude).

Richard Sims   http://people.bu.edu/rbs

On Jun 29, 2010, at 1:28 PM, Moyer, Joni M wrote:

 Hi Richard,

 Is there any other way to do a report that would list missed, failed,
in progress and started backups then on a daily basis running it from
the prior day at 6PM until the current time the script is run at 9AM?  I
currently run q event at 9AM which catches the missed  failed backups,
but nothing that currently accounts for anything that is in progress or
has been started.

 Any suggestions/ideas are appreciated!  This one has me stumped!


This e-mail and any attachments to it are confidential and are intended solely 
for use of the individual or entity to whom they are addressed.  If you have 
received this e-mail in error, please notify the sender immediately and then 
delete it.  If you are not the intended recipient, you must not keep, use, 
disclose, copy or distribute this e-mail without the author's prior permission. 
 The views expressed in this e-mail message do not necessarily represent the 
views of Highmark Inc., its subsidiaries, or affiliates.

Re: Select Statement

[Thomas Denier]

-Timothy Conway wrote: -

Ok, here.  Do a stgpool backup, disable sessions and do another
stgpool backup to eliminate and prevent new data.  While sessions
are still disabled, mark the volume in question destroyed, and do
a stgpool backup preview.  That should tell you every primary
volume that has data on the volume in question.  Mark the volume
readonly again, enable sessions, obtain and checkin the volumes it
said it needed, (search the appropriate actl slice for 1228s), and
do your reclamation.  Since you're talking about a single volume,
probably just a move data? Then again, if what's happenning is
that your reclamation is ending on a particular offsite volume,
you should first find out what all offsite volumes are below your
threshold, and do the ba stg preview with them all marked
destroyed.

The online help for 'update volume' states that 'access=destroyed'
is valid only primary storage pool volumes.

Re: Select Statement

[Conway, Timothy]

OK, then, back to my ugly select, shich I'm sure needs correction - No
way in heck I'm going to run it on my server.




-Original Message-
From: ADSM: Dist Stor Manager [mailto:ads...@vm.marist.edu] On Behalf Of
Thomas Denier
Sent: Friday, February 27, 2009 11:27 AM
To: ADSM-L@VM.MARIST.EDU
Subject: Re: [ADSM-L] Select Statement

-Timothy Conway wrote: -

Ok, here.  Do a stgpool backup, disable sessions and do another stgpool

backup to eliminate and prevent new data.  While sessions are still 
disabled, mark the volume in question destroyed, and do a stgpool 
backup preview.  That should tell you every primary volume that has 
data on the volume in question.  Mark the volume readonly again, enable

sessions, obtain and checkin the volumes it said it needed, (search the

appropriate actl slice for 1228s), and do your reclamation.  Since 
you're talking about a single volume, probably just a move data? Then

again, if what's happenning is that your reclamation is ending on a 
particular offsite volume, you should first find out what all offsite 
volumes are below your threshold, and do the ba stg preview with them 
all marked destroyed.

The online help for 'update volume' states that 'access=destroyed'
is valid only primary storage pool volumes.

Re: Select Statement

[Lepre, James]

Hello Everyone,

 

 Is there a select statement or preview to see which tapes will be
needed to reclaim an offsite volume. 

 

Thank you 

 

James 

 

 


  
  
---
Confidentiality Notice: The information in this e-mail and any attachments 
thereto is intended for the named recipient(s) only.  This e-mail, including 
any attachments, may contain information that is privileged and confidential  
and subject to legal restrictions and penalties regarding its unauthorized 
disclosure or other use.  If you are not the intended recipient, you are hereby 
notified that any disclosure, copying, distribution, or the taking of any 
action or inaction in reliance on the contents of this e-mail and any of its 
attachments is STRICTLY PROHIBITED.  If you have received this e-mail in error, 
please immediately notify the sender via return e-mail; delete this e-mail and 
all attachments from your e-mail  system and your computer system and network; 
and destroy any paper copies you may have in your possession. Thank you for 
your cooperation.

Re: Select Statement

[Remco Post]

Interesting question.

Why would you want to know? I hope your primary volumes are all on-line?

On Feb 24, 2009, at 21:48 , Lepre, James wrote:


Hello Everyone,



Is there a select statement or preview to see which tapes will be
needed to reclaim an offsite volume.



Thank you



James








---
Confidentiality Notice: The information in this e-mail and any
attachments thereto is intended for the named recipient(s) only.
This e-mail, including any attachments, may contain information that
is privileged and confidential  and subject to legal restrictions
and penalties regarding its unauthorized disclosure or other use.
If you are not the intended recipient, you are hereby notified that
any disclosure, copying, distribution, or the taking of any action
or inaction in reliance on the contents of this e-mail and any of
its attachments is STRICTLY PROHIBITED.  If you have received this e-
mail in error, please immediately notify the sender via return e-
mail; delete this e-mail and all attachments from your e-mail
system and your computer system and network; and destroy any paper
copies you may have in your possession. Thank you for your
cooperation.


--
Met vriendelijke groeten,

Remco Post
r.p...@plcs.nl
+31 6 248 21 622

Re: Select Statement

[Lepre, James]

Well The reason I am asking is that we do not have a copypool setup for
our offsite tapepool since we are using a VTL.  However, the physical
library is small and some of the tapes needed to reclaim may not be in
the system.  If I can generate a list of tapes needed to reclaim a
certain volume then I can check all of those volumes in and let it do
its thing, instead of constantly checking to see if it needs another
tape

James Lepre
Senior Server Specialist
Solix Inc
100 South Jefferson Road
Whippany NJ 07981
Phone 1-973-581-5362
Cell 1-973-223-1921
 
-Original Message-
From: ADSM: Dist Stor Manager [mailto:ads...@vm.marist.edu] On Behalf Of
Remco Post
Sent: Tuesday, February 24, 2009 3:55 PM
To: ADSM-L@VM.MARIST.EDU
Subject: Re: [ADSM-L] Select Statement

Interesting question.

Why would you want to know? I hope your primary volumes are all on-line?

On Feb 24, 2009, at 21:48 , Lepre, James wrote:

 Hello Everyone,



 Is there a select statement or preview to see which tapes will be
 needed to reclaim an offsite volume.



 Thank you



 James








 ---
 Confidentiality Notice: The information in this e-mail and any
 attachments thereto is intended for the named recipient(s) only.
 This e-mail, including any attachments, may contain information that
 is privileged and confidential  and subject to legal restrictions
 and penalties regarding its unauthorized disclosure or other use.
 If you are not the intended recipient, you are hereby notified that
 any disclosure, copying, distribution, or the taking of any action
 or inaction in reliance on the contents of this e-mail and any of
 its attachments is STRICTLY PROHIBITED.  If you have received this e-
 mail in error, please immediately notify the sender via return e-
 mail; delete this e-mail and all attachments from your e-mail
 system and your computer system and network; and destroy any paper
 copies you may have in your possession. Thank you for your
 cooperation.

--
Met vriendelijke groeten,

Remco Post
r.p...@plcs.nl
+31 6 248 21 622

Re: Select Statement

[Howard Coles]

There is a command to find out which volumes would be required to
restore a volume.  So I'd assume they'd be the same offsite volumes
that would be required for reclamation.  Right?

Restore vol volnum preview=yes

See Ya'
Howard


 -Original Message-
 From: ADSM: Dist Stor Manager [mailto:ads...@vm.marist.edu] On Behalf
 Of Remco Post
 Sent: Tuesday, February 24, 2009 2:55 PM
 To: ADSM-L@VM.MARIST.EDU
 Subject: Re: [ADSM-L] Select Statement
 
 Interesting question.
 
 Why would you want to know? I hope your primary volumes are all on-
 line?
 
 On Feb 24, 2009, at 21:48 , Lepre, James wrote:
 
  Hello Everyone,
 
 
 
  Is there a select statement or preview to see which tapes will be
  needed to reclaim an offsite volume.
 
 
 
  Thank you

Re: Select Statement

[Howard Coles]

Hmm.  I'm confused.  IF you don't have a copy pool then the volume
required for reclamation is the volume, or volumes that are set to be
reclaimed.

A good way to find out which volumes need reclamation is:

select volume_name as TAPEPOOL_VOL,est_capacity_mb as
CAPACITY,pct_utilized as UTILIZATION,pct_reclaim as RECLAIMABLE from
volumes where stgpool_name='TAPEPOOLNAME' and pct_reclaim=60 order by
pct_reclaim desc

Now if you want to put this in a script replace the pct_reclaim=60
part with pct_reclaim=%1 and use the first parameter to define what
level of reclamation you're looking for.  You'll also need to replace
the TAPEPOOL references with the pool(s) you have.  
This will print out a list something along the lines of:

TAPEPOOL_VOL   CAPACITY UTILIZATION RECLAIMABLE
--  ---
---
010389 381468.0 4.3
95.6
011392 381468.0 4.3
95.6
011146 381468.021.3
78.6
010209 381468.024.7
75.2
011278 381468.042.0
57.9

However, I would highly recommend a copy pool even with a VTL (unless
you're doing VTL to VTL replication to another site).

See Ya'
Howard


 -Original Message-
 From: ADSM: Dist Stor Manager [mailto:ads...@vm.marist.edu] On Behalf
 Of Lepre, James
 Sent: Tuesday, February 24, 2009 3:01 PM
 To: ADSM-L@VM.MARIST.EDU
 Subject: Re: [ADSM-L] Select Statement
 
 Well The reason I am asking is that we do not have a copypool setup
for
 our offsite tapepool since we are using a VTL.  However, the physical
 library is small and some of the tapes needed to reclaim may not be in
 the system.  If I can generate a list of tapes needed to reclaim a
 certain volume then I can check all of those volumes in and let it do
 its thing, instead of constantly checking to see if it needs another
 tape
 
 James Lepre
 Senior Server Specialist
 Solix Inc
 100 South Jefferson Road
 Whippany NJ 07981
 Phone 1-973-581-5362
 Cell 1-973-223-1921
 
 -Original Message-
 From: ADSM: Dist Stor Manager [mailto:ads...@vm.marist.edu] On Behalf
 Of
 Remco Post
 Sent: Tuesday, February 24, 2009 3:55 PM
 To: ADSM-L@VM.MARIST.EDU
 Subject: Re: [ADSM-L] Select Statement
 
 Interesting question.
 
 Why would you want to know? I hope your primary volumes are all on-
 line?
 
 On Feb 24, 2009, at 21:48 , Lepre, James wrote:
 
  Hello Everyone,
 
 
 
  Is there a select statement or preview to see which tapes will be
  needed to reclaim an offsite volume.
 
 
 
  Thank you
 
 
 
  James
 
 
 
 
 
 
 
 
  ---
  Confidentiality Notice: The information in this e-mail and any
  attachments thereto is intended for the named recipient(s) only.
  This e-mail, including any attachments, may contain information that
  is privileged and confidential  and subject to legal restrictions
  and penalties regarding its unauthorized disclosure or other use.
  If you are not the intended recipient, you are hereby notified that
  any disclosure, copying, distribution, or the taking of any action
  or inaction in reliance on the contents of this e-mail and any of
  its attachments is STRICTLY PROHIBITED.  If you have received this
e-
  mail in error, please immediately notify the sender via return e-
  mail; delete this e-mail and all attachments from your e-mail
  system and your computer system and network; and destroy any paper
  copies you may have in your possession. Thank you for your
  cooperation.
 
 --
 Met vriendelijke groeten,
 
 Remco Post
 r.p...@plcs.nl
 +31 6 248 21 622

Re: Select Statement

[Lepre, James]

I am confusing. What i am trying to say is I know the tape being reclaimed but 
that tape need other tapes to pull files from simce it is non collocated. Am i 
making sense

- Original Message -
From: ADSM: Dist Stor Manager ADSM-L@VM.MARIST.EDU
To: ADSM-L@VM.MARIST.EDU ADSM-L@VM.MARIST.EDU
Sent: Tue Feb 24 16:24:03 2009
Subject: Re: [ADSM-L] Select Statement

Hmm.  I'm confused.  IF you don't have a copy pool then the volume
required for reclamation is the volume, or volumes that are set to be
reclaimed.

A good way to find out which volumes need reclamation is:

select volume_name as TAPEPOOL_VOL,est_capacity_mb as
CAPACITY,pct_utilized as UTILIZATION,pct_reclaim as RECLAIMABLE from
volumes where stgpool_name='TAPEPOOLNAME' and pct_reclaim=60 order by
pct_reclaim desc

Now if you want to put this in a script replace the pct_reclaim=60
part with pct_reclaim=%1 and use the first parameter to define what
level of reclamation you're looking for.  You'll also need to replace
the TAPEPOOL references with the pool(s) you have.  
This will print out a list something along the lines of:

TAPEPOOL_VOL   CAPACITY UTILIZATION RECLAIMABLE
--  ---
---
010389 381468.0 4.3
95.6
011392 381468.0 4.3
95.6
011146 381468.021.3
78.6
010209 381468.024.7
75.2
011278 381468.042.0
57.9

However, I would highly recommend a copy pool even with a VTL (unless
you're doing VTL to VTL replication to another site).

See Ya'
Howard


 -Original Message-
 From: ADSM: Dist Stor Manager [mailto:ads...@vm.marist.edu] On Behalf
 Of Lepre, James
 Sent: Tuesday, February 24, 2009 3:01 PM
 To: ADSM-L@VM.MARIST.EDU
 Subject: Re: [ADSM-L] Select Statement
 
 Well The reason I am asking is that we do not have a copypool setup
for
 our offsite tapepool since we are using a VTL.  However, the physical
 library is small and some of the tapes needed to reclaim may not be in
 the system.  If I can generate a list of tapes needed to reclaim a
 certain volume then I can check all of those volumes in and let it do
 its thing, instead of constantly checking to see if it needs another
 tape
 
 James Lepre
 Senior Server Specialist
 Solix Inc
 100 South Jefferson Road
 Whippany NJ 07981
 Phone 1-973-581-5362
 Cell 1-973-223-1921
 
 -Original Message-
 From: ADSM: Dist Stor Manager [mailto:ads...@vm.marist.edu] On Behalf
 Of
 Remco Post
 Sent: Tuesday, February 24, 2009 3:55 PM
 To: ADSM-L@VM.MARIST.EDU
 Subject: Re: [ADSM-L] Select Statement
 
 Interesting question.
 
 Why would you want to know? I hope your primary volumes are all on-
 line?
 
 On Feb 24, 2009, at 21:48 , Lepre, James wrote:
 
  Hello Everyone,
 
 
 
  Is there a select statement or preview to see which tapes will be
  needed to reclaim an offsite volume.
 
 
 
  Thank you
 
 
 
  James
 
 
 
 
 
 
 
 
  ---
  Confidentiality Notice: The information in this e-mail and any
  attachments thereto is intended for the named recipient(s) only.
  This e-mail, including any attachments, may contain information that
  is privileged and confidential  and subject to legal restrictions
  and penalties regarding its unauthorized disclosure or other use.
  If you are not the intended recipient, you are hereby notified that
  any disclosure, copying, distribution, or the taking of any action
  or inaction in reliance on the contents of this e-mail and any of
  its attachments is STRICTLY PROHIBITED.  If you have received this
e-
  mail in error, please immediately notify the sender via return e-
  mail; delete this e-mail and all attachments from your e-mail
  system and your computer system and network; and destroy any paper
  copies you may have in your possession. Thank you for your
  cooperation.
 
 --
 Met vriendelijke groeten,
 
 Remco Post
 r.p...@plcs.nl
 +31 6 248 21 622

Re: Select Statement

[Huebschman, George J.]

I too am confused, not that that is so unusual.  I am not sure I
understand what you are saying.

There are offsite volumes, but no copypool.  So, the (physically ?)
offsite volumes are in the primary pool hierarchy?  Is that the case?
If so, and reclamation is really what is going on, then it is simply
whatever volumes are above the reclamation threshold you have decided
on.  The select below shows you what you want.


-Original Message-
From: ADSM: Dist Stor Manager [mailto:ads...@vm.marist.edu] On Behalf Of
Howard Coles
Sent: Tuesday, February 24, 2009 4:24 PM
To: ADSM-L@VM.MARIST.EDU
Subject: Re: [ADSM-L] Select Statement

Hmm.  I'm confused.  IF you don't have a copy pool then the volume
required for reclamation is the volume, or volumes that are set to be
reclaimed.

A good way to find out which volumes need reclamation is:

select volume_name as TAPEPOOL_VOL,est_capacity_mb as
CAPACITY,pct_utilized as UTILIZATION,pct_reclaim as RECLAIMABLE from
volumes where stgpool_name='TAPEPOOLNAME' and pct_reclaim=60 order by
pct_reclaim desc

Now if you want to put this in a script replace the pct_reclaim=60
part with pct_reclaim=%1 and use the first parameter to define what
level of reclamation you're looking for.  You'll also need to replace
the TAPEPOOL references with the pool(s) you have.
This will print out a list something along the lines of:

TAPEPOOL_VOL   CAPACITY UTILIZATION RECLAIMABLE
--  ---
---
010389 381468.0 4.3
95.6
011392 381468.0 4.3
95.6
011146 381468.021.3
78.6
010209 381468.024.7
75.2
011278 381468.042.0
57.9

However, I would highly recommend a copy pool even with a VTL (unless
you're doing VTL to VTL replication to another site).

See Ya'
Howard


 -Original Message-
 From: ADSM: Dist Stor Manager [mailto:ads...@vm.marist.edu] On Behalf
 Of Lepre, James
 Sent: Tuesday, February 24, 2009 3:01 PM
 To: ADSM-L@VM.MARIST.EDU
 Subject: Re: [ADSM-L] Select Statement

 Well The reason I am asking is that we do not have a copypool setup
for
 our offsite tapepool since we are using a VTL.  However, the physical
 library is small and some of the tapes needed to reclaim may not be in

 the system.  If I can generate a list of tapes needed to reclaim a
 certain volume then I can check all of those volumes in and let it do
 its thing, instead of constantly checking to see if it needs another
 tape

 James Lepre
 Senior Server Specialist
 Solix Inc
 100 South Jefferson Road
 Whippany NJ 07981
 Phone 1-973-581-5362
 Cell 1-973-223-1921

 -Original Message-
 From: ADSM: Dist Stor Manager [mailto:ads...@vm.marist.edu] On Behalf
 Of Remco Post
 Sent: Tuesday, February 24, 2009 3:55 PM
 To: ADSM-L@VM.MARIST.EDU
 Subject: Re: [ADSM-L] Select Statement

 Interesting question.

 Why would you want to know? I hope your primary volumes are all on-
 line?

 On Feb 24, 2009, at 21:48 , Lepre, James wrote:

  Hello Everyone,
 
 
 
  Is there a select statement or preview to see which tapes will be
  needed to reclaim an offsite volume.
 
 
 
  Thank you
 
 
 
  James
 
 
 
 
 
 
 
 
  ---
  Confidentiality Notice: The information in this e-mail and any
  attachments thereto is intended for the named recipient(s) only.
  This e-mail, including any attachments, may contain information that

  is privileged and confidential  and subject to legal restrictions
  and penalties regarding its unauthorized disclosure or other use.
  If you are not the intended recipient, you are hereby notified that
  any disclosure, copying, distribution, or the taking of any action
  or inaction in reliance on the contents of this e-mail and any of
  its attachments is STRICTLY PROHIBITED.  If you have received this
e-
  mail in error, please immediately notify the sender via return e-
  mail; delete this e-mail and all attachments from your e-mail system

  and your computer system and network; and destroy any paper copies
  you may have in your possession. Thank you for your cooperation.

 --
 Met vriendelijke groeten,

 Remco Post
 r.p...@plcs.nl
 +31 6 248 21 622

IMPORTANT: E-mail sent through the Internet is not secure and timely delivery 
of Internet mail is not guaranteed. Legg Mason therefore, recommends that you 
do not send any  action-oriented or time-sensitive information to us via 
electronic mail, or any confidential or sensitive information including:  
social security numbers, account numbers, or personal identification numbers.

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. 

Re: Select Statement

[Conway, Timothy]

Select volume_name fron contents where object_id in (select object_id from 
contents where volume_name='volumename') and stgpool='primarypoolname') group 
by volume_name

I think it would take a LONG freaking time to run, though.

-Original Message-
From: ADSM: Dist Stor Manager [mailto:ads...@vm.marist.edu] On Behalf Of Lepre, 
James
Sent: Tuesday, February 24, 2009 1:49 PM
To: ADSM-L@VM.MARIST.EDU
Subject: Re: [ADSM-L] Select Statement

Hello Everyone,

 

 Is there a select statement or preview to see which tapes will be needed to 
reclaim an offsite volume. 

 

Thank you 

 

James 

 

 


  
  
---
Confidentiality Notice: The information in this e-mail and any attachments 
thereto is intended for the named recipient(s) only.  This e-mail, including 
any attachments, may contain information that is privileged and confidential  
and subject to legal restrictions and penalties regarding its unauthorized 
disclosure or other use.  If you are not the intended recipient, you are hereby 
notified that any disclosure, copying, distribution, or the taking of any 
action or inaction in reliance on the contents of this e-mail and any of its 
attachments is STRICTLY PROHIBITED.  If you have received this e-mail in error, 
please immediately notify the sender via return e-mail; delete this e-mail and 
all attachments from your e-mail  system and your computer system and network; 
and destroy any paper copies you may have in your possession. Thank you for 
your cooperation.

Re: Select Statement

[Conway, Timothy]

What he means is no onsite copypool, and not all primary volumes are in
the library.  Our library's inadequate too.  

A restore volume preview isn't an option for a copypool volumes either
:(
Move data doesn't have a preview.


Ok, here.  Do a stgpool backup, disable sessions and do another stgpool
backup to eliminate and prevent new data.  While sessions are still
disabled, mark the volume in question destroyed, and do a stgpool backup
preview.  That should tell you every primary volume that has data on the
volume in question.  Mark the volume readonly again, enable sessions,
obtain and checkin the volumes it said it needed, (search the
appropriate actl slice for 1228s), and do your reclamation.  Since
you're talking about a single volume, probably just a move data?  Then
again, if what's happenning is that your reclamation is ending on a
particular offsite volume, you should first find out what all offsite
volumes are below your threshold, and do the ba stg preview with them
all marked destroyed.

Select volume_name from volumes where stgpool='offsitepoolname' and
pct_reclaim  whatever




-Original Message-
From: ADSM: Dist Stor Manager [mailto:ads...@vm.marist.edu] On Behalf Of
Huebschman, George J.
Sent: Tuesday, February 24, 2009 2:39 PM
To: ADSM-L@VM.MARIST.EDU
Subject: Re: [ADSM-L] Select Statement

I too am confused, not that that is so unusual.  I am not sure I
understand what you are saying.

There are offsite volumes, but no copypool.  So, the (physically ?)
offsite volumes are in the primary pool hierarchy?  Is that the case?
If so, and reclamation is really what is going on, then it is simply
whatever volumes are above the reclamation threshold you have decided
on.  The select below shows you what you want.


-Original Message-
From: ADSM: Dist Stor Manager [mailto:ads...@vm.marist.edu] On Behalf Of
Howard Coles
Sent: Tuesday, February 24, 2009 4:24 PM
To: ADSM-L@VM.MARIST.EDU
Subject: Re: [ADSM-L] Select Statement

Hmm.  I'm confused.  IF you don't have a copy pool then the volume
required for reclamation is the volume, or volumes that are set to be
reclaimed.

A good way to find out which volumes need reclamation is:

select volume_name as TAPEPOOL_VOL,est_capacity_mb as
CAPACITY,pct_utilized as UTILIZATION,pct_reclaim as RECLAIMABLE from
volumes where stgpool_name='TAPEPOOLNAME' and pct_reclaim=60 order by
pct_reclaim desc

Now if you want to put this in a script replace the pct_reclaim=60
part with pct_reclaim=%1 and use the first parameter to define what
level of reclamation you're looking for.  You'll also need to replace
the TAPEPOOL references with the pool(s) you have.
This will print out a list something along the lines of:

TAPEPOOL_VOL   CAPACITY UTILIZATION RECLAIMABLE
--  ---
---
010389 381468.0 4.3
95.6
011392 381468.0 4.3
95.6
011146 381468.021.3
78.6
010209 381468.024.7
75.2
011278 381468.042.0
57.9

However, I would highly recommend a copy pool even with a VTL (unless
you're doing VTL to VTL replication to another site).

See Ya'
Howard


 -Original Message-
 From: ADSM: Dist Stor Manager [mailto:ads...@vm.marist.edu] On Behalf 
 Of Lepre, James
 Sent: Tuesday, February 24, 2009 3:01 PM
 To: ADSM-L@VM.MARIST.EDU
 Subject: Re: [ADSM-L] Select Statement

 Well The reason I am asking is that we do not have a copypool setup
for
 our offsite tapepool since we are using a VTL.  However, the physical 
 library is small and some of the tapes needed to reclaim may not be in

 the system.  If I can generate a list of tapes needed to reclaim a 
 certain volume then I can check all of those volumes in and let it do 
 its thing, instead of constantly checking to see if it needs another 
 tape

 James Lepre
 Senior Server Specialist
 Solix Inc
 100 South Jefferson Road
 Whippany NJ 07981
 Phone 1-973-581-5362
 Cell 1-973-223-1921

 -Original Message-
 From: ADSM: Dist Stor Manager [mailto:ads...@vm.marist.edu] On Behalf 
 Of Remco Post
 Sent: Tuesday, February 24, 2009 3:55 PM
 To: ADSM-L@VM.MARIST.EDU
 Subject: Re: [ADSM-L] Select Statement

 Interesting question.

 Why would you want to know? I hope your primary volumes are all on- 
 line?

 On Feb 24, 2009, at 21:48 , Lepre, James wrote:

  Hello Everyone,
 
 
 
  Is there a select statement or preview to see which tapes will be 
  needed to reclaim an offsite volume.
 
 
 
  Thank you
 
 
 
  James
 
 
 
 
 
 
 
 
  ---
  Confidentiality Notice: The information in this e-mail and any 
  attachments thereto is intended for the named recipient(s) only.
  This e-mail, including any attachments, may contain information that

  is privileged and confidential  and 

Re: Select statement to output backup archive summary per domain

[Richard Rhodes]

Here is what I use for this info . . . from our morning_report.ksh scripts.


function r184 {
  Title=r184 backup objects and bytes per domain 
  PrintTitle

  # objects and MB for each domain over the last 24 hr

  # NOTE:  This is some overlap given the  25 hour period, but
  # that's necessary since you don't know exactly when this
  # function will run.

  dsmadmc -se=$tsm -id=$adminid -password=$adminpwd
-outfile=/tmp/tsmtmp/${tsm}/morning_report1$$ -tab -quiet -noc  EOD
select domain_name as DOMAIN, -
   cast(sum(affected) as decimal(9,0)) as OBJECTS, -
   cast(sum(bytes)/1024/1024 as decimal(9,0)) as MB -
  from summary, nodes -
  where -
 node_name = entity -
 and (activity = 'BACKUP' or activity = 'ARCHIVE') -
 and cast((current_timestamp - end_time)hours  as decimal(5,0)) 
25 -
  group by domain_name -
  order by domain_name
EOD

  print sum of sessions per domain that ENDED over the past 24 hours
  print  
  print DOMAIN   OBJECTS   MB
  print  

  cat /tmp/tsmtmp/${tsm}/morning_report1$$   | awk {print \$0, \
${stamp}\}

}






 Joni Moyer
 joni.mo...@highm
 ARK.COM   To
 Sent by: ADSM:   ADSM-L@VM.MARIST.EDU
 Dist Stor  cc
 Manager
 ads...@vm.marist Subject
 .EDU Select statement to output backup 
   archive summary per domain

 12/29/2008 03:51
 PM


 Please respond to
 ADSM: Dist Stor
 Manager
 ads...@vm.marist
   .EDU






Hi Everyone,

I am trying to figure out how to configure a script that will output the
total GB backed up and total GB archived per domain within a 24 hour time
period.  I already have something similar, but it totals all of the data
together and I would like to break it down more by the domain the server
belongs to.

Here are my current select statements.

select sum(cast(bytes/1024/1024/1024 as decimal(8,2))) Total GB Backed
Up from summary where start_time=current_timestamp - 1 day and
activity='BACKUP'

select sum(cast(bytes/1024/1024/1024 as decimal(8,2))) Total GB Archived
from summary where start_time=current_timestamp - 1 day and
activity='ARCHIVE'

Does anyone have any suggestions?  I would really appreciate the help.
Thanks in advance!


Joni Moyer
Highmark
Storage Systems, Storage Mngt Analyst III
Phone Number: (717)302-9966
Fax: (717) 302-9826
joni.mo...@highmark.com



-
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: Select statement to output backup archive summary per domain

[Joni Moyer]

Hi Richard,

That worked great!  Thank you so much for sharing that with me!


Joni Moyer
Highmark
Storage Systems, Storage Mngt Analyst III
Phone Number: (717)302-9966
Fax: (717) 302-9826
joni.mo...@highmark.com




Richard Rhodes rrho...@firstenergycorp.com
Sent by: ADSM: Dist Stor Manager ADSM-L@VM.MARIST.EDU
12/30/2008 07:20 AM
Please respond to
ADSM: Dist Stor Manager ADSM-L@VM.MARIST.EDU


To
ADSM-L@VM.MARIST.EDU
cc

Subject
Re: Select statement to output backup  archive summary per domain






Here is what I use for this info . . . from our morning_report.ksh
scripts.


function r184 {
  Title=r184 backup objects and bytes per domain 
  PrintTitle

  # objects and MB for each domain over the last 24 hr

  # NOTE:  This is some overlap given the  25 hour period, but
  # that's necessary since you don't know exactly when this
  # function will run.

  dsmadmc -se=$tsm -id=$adminid -password=$adminpwd
-outfile=/tmp/tsmtmp/${tsm}/morning_report1$$ -tab -quiet -noc  EOD
select domain_name as DOMAIN, -
   cast(sum(affected) as decimal(9,0)) as OBJECTS, -
   cast(sum(bytes)/1024/1024 as decimal(9,0)) as MB -
  from summary, nodes -
  where -
 node_name = entity -
 and (activity = 'BACKUP' or activity = 'ARCHIVE') -
 and cast((current_timestamp - end_time)hours  as decimal(5,0)) 
25 -
  group by domain_name -
  order by domain_name
EOD

  print sum of sessions per domain that ENDED over the past 24 hours
  print  
  print DOMAIN   OBJECTS   MB
  print  

  cat /tmp/tsmtmp/${tsm}/morning_report1$$   | awk {print \$0, \
${stamp}\}

}






 Joni Moyer
 joni.mo...@highm
 ARK.COM   To
 Sent by: ADSM:   ADSM-L@VM.MARIST.EDU
 Dist Stor  cc
 Manager
 ads...@vm.marist Subject
 .EDU Select statement to output backup 
   archive summary per domain

 12/29/2008 03:51
 PM


 Please respond to
 ADSM: Dist Stor
 Manager
 ads...@vm.marist
   .EDU






Hi Everyone,

I am trying to figure out how to configure a script that will output the
total GB backed up and total GB archived per domain within a 24 hour time
period.  I already have something similar, but it totals all of the data
together and I would like to break it down more by the domain the server
belongs to.

Here are my current select statements.

select sum(cast(bytes/1024/1024/1024 as decimal(8,2))) Total GB Backed
Up from summary where start_time=current_timestamp - 1 day and
activity='BACKUP'

select sum(cast(bytes/1024/1024/1024 as decimal(8,2))) Total GB Archived
from summary where start_time=current_timestamp - 1 day and
activity='ARCHIVE'

Does anyone have any suggestions?  I would really appreciate the help.
Thanks in advance!


Joni Moyer
Highmark
Storage Systems, Storage Mngt Analyst III
Phone Number: (717)302-9966
Fax: (717) 302-9826
joni.mo...@highmark.com



-
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: select statement

[Avy Wong]

Hmmm... I will take another look. Thanks for verifying it.

Avy Wong
Business Continuity Administrator
Mohegan Sun
1 Mohegan Sun Blvd
Uncasville, CT 06382
(860)862-8164
(cell) (860)961-6976



   
 Patel, Ankur
 [EMAIL PROTECTED] 
 ARVEYNORMAN.COM   To 
 Sent by: ADSM:   ADSM-L@VM.MARIST.EDU
 Dist Stor  cc 
 Manager  
 [EMAIL PROTECTED] Subject 
 .EDU Re: [ADSM-L] select statement   
   
   
 08/29/2008 01:09  
 AM
   
   
 Please respond to 
 ADSM: Dist Stor  
 Manager  
 [EMAIL PROTECTED] 
   .EDU   
   
   




Avy,

I tried your query on my machine and it has worked fine.
See results:

~~
 AUCFTSM01_PRODselect vu.node_name, ao.total_mb, count(distinct
vu.volume_name) as tapes, ao.total_mb/count(distinct vu.
volume_name) as AVG MB/tape from volumeusage vu, auditocc ao where
vu.node_name=ao.node_name group by vu.node_name,
ao.total_mb order by 4

Session established with server AUCFTSM01_PROD: AIX-RS/6000
  Server Version 5, Release 4, Level 3.0
  Server date/time: 08/29/08   15:00:01  Last access: 08/29/08
12:46:42

ANR2963W This SQL query may produce a very large result table, or may
require a significant amount of time to compute.

Do you wish to proceed? (Yes (Y)/No (N)) y

NODE_NAME TOTAL_MB   TAPES AVG MB/tape
-- --- --- ---
FLEMTMH2_FS 531344 1543450
FLEMXPS3_FS1122421 1666761
FLEMTDM2_FS 764482  50   15289
FLEMXPS2_FS2865024 172   16657
AUCFTDWH01_FS  4078174 186   21925
AUCFSAV01_FS   3604114 163   22111
AUDCCFOMX006_EXCH70208   3   23402
FLEMXPS0_FS7529458 213   35349
AUCFVSQ1_FS7168357 159   45084
AUCFNIM01_FS  14523030 190   76437
AUCFDW01_FS   48314333 233  207357
.
.
.
.
.





Regards,



Ankur Patel

TSM Administrator.
SYDNEY.
~~


-Original Message-
From: ADSM: Dist Stor Manager [mailto:[EMAIL PROTECTED] On Behalf Of
Avy Wong
Sent: Friday, 29 August 2008 5:36 AM
To: ADSM-L@VM.MARIST.EDU
Subject: [ADSM-L] select statement

Hello,
  The following is a select statement to provide a total data
stored and tapes used per node in all storage pools.
I have broken down the sql and double checked the tables and column
names are all there. Can anyone see where the hangs ups are?

select vu.node_name, ao.total_mb, count(distinct vu.volume_name) as
tapes, ao.total_mb/count(distinct vu.volume_name) as AVG MB/tape from
volumeusage vu, auditocc ao where vu.node_name=ao.node_name group by
vu.node_name, ao.total_mb order by 4

ANR2956E Unable to access SQL base table 'VOLUMEUSAGE'.

   |
 ..V
 me) as AVG MB/tape from volumeusage vu, auditocc ao where vu.

ANS8001I Return code 26.

thanks,
Avy Wong
Business Continuity Administrator
Mohegan Sun
1 Mohegan Sun Blvd
Uncasville, CT 06382
(860)862-8164
(cell) (860)961-6976


DISCLAIMER

Confidential Communication: This email and any attachments are intended for
the addressee(s)
only and are confidential. They may contain legally privileged or copyright
material. If you
are not the intended 

Re: select statement

[Andrew Raibeck]

Hi Avy,

If you look up message ANR2956E, you'll see it says the message might be 
preceded by other messages. If you didn't see anything else on your 
console, check the activity log from when you ran the SELECT command to 
see if there are any other anomalous messages.

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/[EMAIL PROTECTED]
Internet e-mail: [EMAIL PROTECTED]

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 08/29/2008 
05:46:37 AM:

 Hmmm... I will take another look. Thanks for verifying it.
 
 Avy Wong
 Business Continuity Administrator
 Mohegan Sun
 1 Mohegan Sun Blvd
 Uncasville, CT 06382
 (860)862-8164
 (cell) (860)961-6976
 
 
 
  
  Patel, Ankur  
  [EMAIL PROTECTED]  
  ARVEYNORMAN.COM To 
  Sent by: ADSM:   ADSM-L@VM.MARIST.EDU  
  Dist Stor cc 
  Manager  
  [EMAIL PROTECTED] Subject 
  .EDU Re: [ADSM-L] select statement  
  
  
  08/29/2008 01:09  
  AM  
  
  
  Please respond to  
  ADSM: Dist Stor  
  Manager  
  [EMAIL PROTECTED]  
.EDU  
  
  
 
 
 
 
 Avy,
 
 I tried your query on my machine and it has worked fine.
 See results:
 
 ~~
  AUCFTSM01_PRODselect vu.node_name, ao.total_mb, count(distinct
 vu.volume_name) as tapes, ao.total_mb/count(distinct vu.
 volume_name) as AVG MB/tape from volumeusage vu, auditocc ao where
 vu.node_name=ao.node_name group by vu.node_name,
 ao.total_mb order by 4
 
 Session established with server AUCFTSM01_PROD: AIX-RS/6000
   Server Version 5, Release 4, Level 3.0
   Server date/time: 08/29/08   15:00:01  Last access: 08/29/08
 12:46:42
 
 ANR2963W This SQL query may produce a very large result table, or may
 require a significant amount of time to compute.
 
 Do you wish to proceed? (Yes (Y)/No (N)) y
 
 NODE_NAME TOTAL_MB   TAPES AVG MB/tape
 -- --- --- ---
 FLEMTMH2_FS 531344 1543450
 FLEMXPS3_FS1122421 1666761
 FLEMTDM2_FS 764482  50   15289
 FLEMXPS2_FS2865024 172   16657
 AUCFTDWH01_FS  4078174 186   21925
 AUCFSAV01_FS   3604114 163   22111
 AUDCCFOMX006_EXCH70208   3   23402
 FLEMXPS0_FS7529458 213   35349
 AUCFVSQ1_FS7168357 159   45084
 AUCFNIM01_FS  14523030 190   76437
 AUCFDW01_FS   48314333 233  207357
 .
 .
 .
 .
 .
 
 
 
 
 
 Regards,
 
 
 
 Ankur Patel
 
 TSM Administrator.
 SYDNEY.
 ~~
 
 
 -Original Message-
 From: ADSM: Dist Stor Manager [mailto:[EMAIL PROTECTED] On Behalf Of
 Avy Wong
 Sent: Friday, 29 August 2008 5:36 AM
 To: ADSM-L@VM.MARIST.EDU
 Subject: [ADSM-L] select statement
 
 Hello,
   The following is a select statement to provide a total data
 stored and tapes used per node in all storage pools.
 I have broken down the sql and double checked the tables and column
 names are all there. Can anyone see where the hangs ups are?
 
 select vu.node_name, ao.total_mb, count(distinct vu.volume_name) as
 tapes, ao.total_mb/count(distinct vu.volume_name) as AVG MB/tape from
 volumeusage vu, auditocc ao where vu.node_name=ao.node_name group by
 vu.node_name, ao.total_mb order by 4
 
 ANR2956E Unable to access SQL base table 'VOLUMEUSAGE'.
 
|
  ..V
  me) as AVG MB/tape from volumeusage vu, auditocc ao where vu.
 
 ANS8001I Return code 26.
 
 thanks,
 Avy Wong
 Business Continuity Administrator
 Mohegan Sun
 1 Mohegan Sun Blvd
 Uncasville, CT 06382
 (860)862-8164
 (cell) (860)961-6976
 

 
 DISCLAIMER
 
 Confidential Communication: This email and any attachments are intended 
for
 the addressee(s)
 only and are confidential. They may contain legally privileged or 
copyright
 material. If you
 are not the intended recipient, please contact the sender immediately by
 reply email and
 delete this email and any attachments. You must not read, copy, use,
 distribute or disclose
 

Re: select statement

[Avy Wong]

Hi Andrew,

  Thank you for pointing me to take a look at the act logs, there sure
is a lot more than the error ANR2956E. The following link does not exactly
tell me the problem as my server version is 5.4.3.0 and I am just running a
select statement. I might submit a PMR to look further into this.

http://www-01.ibm.com/support/docview.wss?rs=663q1=ANR0102Euid=swg1PQ85760loc=en_UScs=utf-8lang=en

08/29/2008 13:46:33  ANR2017I Administrator WONGAV issued command:
select
  vu.node_name, ao.total_mb, count(distinct
vu.volume_name)
  as tapes, ao.total_mb/count(distinct
vu.volume_name) as
  AVG MB/tape from volumeusage vu, auditocc ao
where
  vu.node_name=ao.node_name group by
  vu.node_name,ao.total_mb order by 4 (SESSION:
183619)
08/29/2008 13:47:17  ANR0102E admfs.c(6035): Error 1 inserting row in
table
  Temp.SQL.VolumeUsage. (SESSION: 183619)
08/29/2008 13:47:17  ANR2032E SQL VOLUMEUSAGE: Command failed -
internal server
  error detected. (SESSION: 183619)
08/29/2008 13:47:17  ANRD ThreadId 22 issued message 2032 from:
(SESSION:
  183619)
08/29/2008 13:47:17  ANRD ThreadId 22  0x0001000261cc outMsgf
  (SESSION: 183619)
08/29/2008 13:47:17  ANRD ThreadId 22  0x000100643ca0
  AdmVOLUSAGE_Reset  (SESSION: 183619)
08/29/2008 13:47:17  ANRD ThreadId 22  0x0001001fec90
  [EMAIL PROTECTED]  (SESSION: 183619)
08/29/2008 13:47:17  ANRD ThreadId 22  0x000100201840
  BaseTableIterator_Reset  (SESSION: 183619)
08/29/2008 13:47:17  ANRD ThreadId 22  0x000100207234)
  JoinIterator_Reset  (SESSION: 183619)
08/29/2008 13:47:17  ANRD ThreadId 22  0x000100205158)
  GroupIterator_Execute  (SESSION: 183619)
08/29/2008 13:47:17  ANRD ThreadId 22  0x000100206754)
  SortIterator_Execute  (SESSION: 183619)
08/29/2008 13:47:17  ANRD ThreadId 22  0x0001001f4464
sqlQuery_Open
  (SESSION: 183619)
08/29/2008 13:47:17  ANRD ThreadId 22  0x000100612034
AdmSQLExecute
  (SESSION: 183619)
08/29/2008 13:47:17  ANRD ThreadId 22  0x00010015c77c
AdmCommandLocal
   (SESSION: 183619)
08/29/2008 13:47:17  ANRD ThreadId 22  0x00010015d5d8
admCommand
more...   (ENTER to continue, 'C' to cancel)

  (SESSION: 183619)
08/29/2008 13:47:17  ANRD ThreadId 22  0x0001006e8d40
  SmAdminCommandThread  (SESSION: 183619)
08/29/2008 13:47:17  ANRD ThreadId 22  0x0001fef4
StartThread
  (SESSION: 183619)
08/29/2008 13:47:17  ANRD ThreadId 22  0x0944b440
_pthread_body
  (SESSION: 183619)
08/29/2008 13:47:17  ANR2956E Unable to access SQL base table
'VOLUMEUSAGE'.
  (SESSION: 183619)
08/29/2008 13:47:17  ANR2017I Administrator WONGAV issued command:
ROLLBACK.
  (SESSION: 183619)



Avy Wong
Business Continuity Administrator
Mohegan Sun
1 Mohegan Sun Blvd
Uncasville, CT 06382
(860)862-8164
(cell) (860)961-6976



   
 Andrew Raibeck
 [EMAIL PROTECTED] 
 OMTo 
 Sent by: ADSM:   ADSM-L@VM.MARIST.EDU
 Dist Stor  cc 
 Manager  
 [EMAIL PROTECTED] Subject 
 .EDU Re: [ADSM-L] select statement   
   
   
 08/29/2008 10:21  
 AM
   
   
 Please respond to 
 ADSM: Dist Stor  
 Manager  
 [EMAIL PROTECTED] 
   .EDU  

Re: select statement

[Andrew Raibeck]

Hi Avy,

We've seen this when the node has a duplicate file space name. For 
example, if a Windows client had a non-unicode file space that was not 
converted to Unicode, but instead created a new Unicode file space might 
cause this.

While you could construct a SELECT statement to identify duplicate node 
names, I think it would probably run a long time if you have a lot of file 
spaces. Easiest thing to try:

   select node_name, filespace_name from filespaces
   select distinct node_name, filespace_name from filespaces

Redirect the output of each to a file. Then diff the files.

If that doesn't identify the nodes with duplicate file space names, then a 
PMR might be the best way to go.

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/[EMAIL PROTECTED]
Internet e-mail: [EMAIL PROTECTED]

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 08/29/2008 
11:34:02 AM:

 Hi Andrew,
 
   Thank you for pointing me to take a look at the act logs, there 
sure
 is a lot more than the error ANR2956E. The following link does not 
exactly
 tell me the problem as my server version is 5.4.3.0 and I am just 
running a
 select statement. I might submit a PMR to look further into this.
 
 http://www-01.ibm.com/support/docview.wss?
 rs=663q1=ANR0102Euid=swg1PQ85760loc=en_UScs=utf-8lang=en
 
 08/29/2008 13:46:33  ANR2017I Administrator WONGAV issued command:
 select
   vu.node_name, ao.total_mb, count(distinct
 vu.volume_name)
   as tapes, ao.total_mb/count(distinct
 vu.volume_name) as
   AVG MB/tape from volumeusage vu, auditocc ao
 where
   vu.node_name=ao.node_name group by
   vu.node_name,ao.total_mb order by 4 (SESSION:
 183619)
 08/29/2008 13:47:17  ANR0102E admfs.c(6035): Error 1 inserting row 
in
 table
   Temp.SQL.VolumeUsage. (SESSION: 183619)
 08/29/2008 13:47:17  ANR2032E SQL VOLUMEUSAGE: Command failed -
 internal server
   error detected. (SESSION: 183619)
 08/29/2008 13:47:17  ANRD ThreadId 22 issued message 2032 
from:
 (SESSION:
   183619)
 08/29/2008 13:47:17  ANRD ThreadId 22  0x0001000261cc 
outMsgf
   (SESSION: 183619)
 08/29/2008 13:47:17  ANRD ThreadId 22  0x000100643ca0
   AdmVOLUSAGE_Reset  (SESSION: 183619)
 08/29/2008 13:47:17  ANRD ThreadId 22  0x0001001fec90
   [EMAIL PROTECTED]  (SESSION: 183619)
 08/29/2008 13:47:17  ANRD ThreadId 22  0x000100201840
   BaseTableIterator_Reset  (SESSION: 183619)
 08/29/2008 13:47:17  ANRD ThreadId 22  0x000100207234)
   JoinIterator_Reset  (SESSION: 183619)
 08/29/2008 13:47:17  ANRD ThreadId 22  0x000100205158)
   GroupIterator_Execute  (SESSION: 183619)
 08/29/2008 13:47:17  ANRD ThreadId 22  0x000100206754)
   SortIterator_Execute  (SESSION: 183619)
 08/29/2008 13:47:17  ANRD ThreadId 22  0x0001001f4464
 sqlQuery_Open
   (SESSION: 183619)
 08/29/2008 13:47:17  ANRD ThreadId 22  0x000100612034
 AdmSQLExecute
   (SESSION: 183619)
 08/29/2008 13:47:17  ANRD ThreadId 22  0x00010015c77c
 AdmCommandLocal
(SESSION: 183619)
 08/29/2008 13:47:17  ANRD ThreadId 22  0x00010015d5d8
 admCommand
 more...   (ENTER to continue, 'C' to cancel)
 
   (SESSION: 183619)
 08/29/2008 13:47:17  ANRD ThreadId 22  0x0001006e8d40
   SmAdminCommandThread  (SESSION: 183619)
 08/29/2008 13:47:17  ANRD ThreadId 22  0x0001fef4
 StartThread
   (SESSION: 183619)
 08/29/2008 13:47:17  ANRD ThreadId 22  0x0944b440
 _pthread_body
   (SESSION: 183619)
 08/29/2008 13:47:17  ANR2956E Unable to access SQL base table
 'VOLUMEUSAGE'.
   (SESSION: 183619)
 08/29/2008 13:47:17  ANR2017I Administrator WONGAV issued command:
 ROLLBACK.
   (SESSION: 183619)
 
 
 
 Avy Wong
 Business Continuity Administrator
 Mohegan Sun
 1 Mohegan Sun Blvd
 Uncasville, CT 06382
 (860)862-8164
 (cell) (860)961-6976
 
 
 
  
  Andrew Raibeck  
  [EMAIL PROTECTED]  
  OM To 
  Sent by: ADSM:   ADSM-L@VM.MARIST.EDU  
   

Re: select statement

[Avy Wong]

Hi Andrew,
  Yes I found them, there are duplicate filespace on one node, at least
for now, there might be some more on others.
What do you do to resolve this? If I delete the dups, the filespace that
are empty, will that solve the problem?

Avy Wong
Business Continuity Administrator
Mohegan Sun
1 Mohegan Sun Blvd
Uncasville, CT 06382
(860)862-8164
(cell) (860)961-6976



   
 Andrew Raibeck
 [EMAIL PROTECTED] 
 OMTo 
 Sent by: ADSM:   ADSM-L@VM.MARIST.EDU
 Dist Stor  cc 
 Manager  
 [EMAIL PROTECTED] Subject 
 .EDU Re: [ADSM-L] select statement   
   
   
 08/29/2008 03:12  
 PM
   
   
 Please respond to 
 ADSM: Dist Stor  
 Manager  
 [EMAIL PROTECTED] 
   .EDU   
   
   




Hi Avy,

We've seen this when the node has a duplicate file space name. For
example, if a Windows client had a non-unicode file space that was not
converted to Unicode, but instead created a new Unicode file space might
cause this.

While you could construct a SELECT statement to identify duplicate node
names, I think it would probably run a long time if you have a lot of file
spaces. Easiest thing to try:

   select node_name, filespace_name from filespaces
   select distinct node_name, filespace_name from filespaces

Redirect the output of each to a file. Then diff the files.

If that doesn't identify the nodes with duplicate file space names, then a
PMR might be the best way to go.

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/[EMAIL PROTECTED]
Internet e-mail: [EMAIL PROTECTED]

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 08/29/2008
11:34:02 AM:

 Hi Andrew,

   Thank you for pointing me to take a look at the act logs, there
sure
 is a lot more than the error ANR2956E. The following link does not
exactly
 tell me the problem as my server version is 5.4.3.0 and I am just
running a
 select statement. I might submit a PMR to look further into this.

 http://www-01.ibm.com/support/docview.wss?
 rs=663q1=ANR0102Euid=swg1PQ85760loc=en_UScs=utf-8lang=en

 08/29/2008 13:46:33  ANR2017I Administrator WONGAV issued command:
 select
   vu.node_name, ao.total_mb, count(distinct
 vu.volume_name)
   as tapes, ao.total_mb/count(distinct
 vu.volume_name) as
   AVG MB/tape from volumeusage vu, auditocc ao
 where
   vu.node_name=ao.node_name group by
   vu.node_name,ao.total_mb order by 4 (SESSION:
 183619)
 08/29/2008 13:47:17  ANR0102E admfs.c(6035): Error 1 inserting row
in
 table
   Temp.SQL.VolumeUsage. (SESSION: 183619)
 08/29/2008 13:47:17  ANR2032E SQL VOLUMEUSAGE: Command failed -
 internal server
   error detected. (SESSION: 183619)
 08/29/2008 13:47:17  ANRD ThreadId 22 issued message 2032
from:
 (SESSION:
   183619)
 08/29/2008 13:47:17  ANRD ThreadId 22  0x0001000261cc
outMsgf
   (SESSION: 183619)
 08/29/2008 13:47:17  ANRD ThreadId 22  0x000100643ca0
   AdmVOLUSAGE_Reset  (SESSION: 183619)
 08/29/2008 13:47:17  ANRD ThreadId 22  0x0001001fec90
   [EMAIL PROTECTED]  (SESSION: 183619)
 08/29/2008 

Re: select statement

[Andrew Raibeck]

Avy,

If it is a case of having duplicates where one is Unicode and the other is 
non-Unicode, it is likely that the non-Unicode file space is no longer 
used. Use QUERY FILESPACE with F=D to see when each was last backed up. 
You can either delete the old file space (if no longer needed -- check 
with your user!) or you can rename it to something else, like 
\\storman\c$_old 

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/[EMAIL PROTECTED]
Internet e-mail: [EMAIL PROTECTED]

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 08/29/2008 
01:14:18 PM:

 Hi Andrew,
   Yes I found them, there are duplicate filespace on one node, at 
least
 for now, there might be some more on others.
 What do you do to resolve this? If I delete the dups, the filespace that
 are empty, will that solve the problem?
 
 Avy Wong
 Business Continuity Administrator
 Mohegan Sun
 1 Mohegan Sun Blvd
 Uncasville, CT 06382
 (860)862-8164
 (cell) (860)961-6976
 
 
 
  
  Andrew Raibeck  
  [EMAIL PROTECTED]  
  OM To 
  Sent by: ADSM:   ADSM-L@VM.MARIST.EDU  
  Dist Stor cc 
  Manager  
  [EMAIL PROTECTED] Subject 
  .EDU Re: [ADSM-L] select statement  
  
  
  08/29/2008 03:12  
  PM  
  
  
  Please respond to  
  ADSM: Dist Stor  
  Manager  
  [EMAIL PROTECTED]  
.EDU  
  
  
 
 
 
 
 Hi Avy,
 
 We've seen this when the node has a duplicate file space name. For
 example, if a Windows client had a non-unicode file space that was not
 converted to Unicode, but instead created a new Unicode file space might
 cause this.
 
 While you could construct a SELECT statement to identify duplicate node
 names, I think it would probably run a long time if you have a lot of 
file
 spaces. Easiest thing to try:
 
select node_name, filespace_name from filespaces
select distinct node_name, filespace_name from filespaces
 
 Redirect the output of each to a file. Then diff the files.
 
 If that doesn't identify the nodes with duplicate file space names, then 
a
 PMR might be the best way to go.
 
 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/[EMAIL PROTECTED]
 Internet e-mail: [EMAIL PROTECTED]
 
 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 08/29/2008
 11:34:02 AM:
 
  Hi Andrew,
 
Thank you for pointing me to take a look at the act logs, there
 sure
  is a lot more than the error ANR2956E. The following link does not
 exactly
  tell me the problem as my server version is 5.4.3.0 and I am just
 running a
  select statement. I might submit a PMR to look further into this.
 
  http://www-01.ibm.com/support/docview.wss?
  rs=663q1=ANR0102Euid=swg1PQ85760loc=en_UScs=utf-8lang=en
 
  08/29/2008 13:46:33  ANR2017I Administrator WONGAV issued command:
  select
vu.node_name, ao.total_mb, count(distinct
  vu.volume_name)
as tapes, ao.total_mb/count(distinct
  vu.volume_name) as
AVG MB/tape from volumeusage vu, auditocc 
ao
  where
vu.node_name=ao.node_name group by
vu.node_name,ao.total_mb order by 4 
(SESSION:
  183619)
  08/29/2008 13:47:17  ANR0102E admfs.c(6035): Error 1 inserting row
 in
  table
Temp.SQL.VolumeUsage. (SESSION: 183619)
  08/29/2008 13:47:17  ANR2032E SQL VOLUMEUSAGE: Command failed -
  internal server
error detected. (SESSION: 183619)
  08/29/2008 13:47:17  ANRD ThreadId 22 issued message 2032
 from:
  (SESSION:
183619)
  08/29/2008 13:47:17  ANRD ThreadId 22  0x0001000261cc
 outMsgf
(SESSION: 183619)
  08/29/2008 13:47:17  ANRD ThreadId 22  0x000100643ca0
AdmVOLUSAGE_Reset  (SESSION: 183619)
  08/29/2008 13:47:17  ANRD ThreadId 22  0x0001001fec90
[EMAIL PROTECTED]  (SESSION: 
183619)
  08/29/2008 13:47:17  ANRD ThreadId 22  0x000100201840
   

Re: select statement

[Patel, Ankur]

Avy,

I tried your query on my machine and it has worked fine.
See results:

~~
 AUCFTSM01_PRODselect vu.node_name, ao.total_mb, count(distinct
vu.volume_name) as tapes, ao.total_mb/count(distinct vu.
volume_name) as AVG MB/tape from volumeusage vu, auditocc ao where
vu.node_name=ao.node_name group by vu.node_name,
ao.total_mb order by 4

Session established with server AUCFTSM01_PROD: AIX-RS/6000
  Server Version 5, Release 4, Level 3.0
  Server date/time: 08/29/08   15:00:01  Last access: 08/29/08
12:46:42

ANR2963W This SQL query may produce a very large result table, or may
require a significant amount of time to compute.

Do you wish to proceed? (Yes (Y)/No (N)) y

NODE_NAME TOTAL_MB   TAPES AVG MB/tape
-- --- --- ---
FLEMTMH2_FS 531344 1543450
FLEMXPS3_FS1122421 1666761
FLEMTDM2_FS 764482  50   15289
FLEMXPS2_FS2865024 172   16657
AUCFTDWH01_FS  4078174 186   21925
AUCFSAV01_FS   3604114 163   22111
AUDCCFOMX006_EXCH70208   3   23402
FLEMXPS0_FS7529458 213   35349
AUCFVSQ1_FS7168357 159   45084
AUCFNIM01_FS  14523030 190   76437
AUCFDW01_FS   48314333 233  207357
.
.
.
.
.



 

Regards,

 

Ankur Patel

TSM Administrator.
SYDNEY.
~~


-Original Message-
From: ADSM: Dist Stor Manager [mailto:[EMAIL PROTECTED] On Behalf Of
Avy Wong
Sent: Friday, 29 August 2008 5:36 AM
To: ADSM-L@VM.MARIST.EDU
Subject: [ADSM-L] select statement

Hello,
  The following is a select statement to provide a total data
stored and tapes used per node in all storage pools.
I have broken down the sql and double checked the tables and column
names are all there. Can anyone see where the hangs ups are?

select vu.node_name, ao.total_mb, count(distinct vu.volume_name) as
tapes, ao.total_mb/count(distinct vu.volume_name) as AVG MB/tape from
volumeusage vu, auditocc ao where vu.node_name=ao.node_name group by
vu.node_name, ao.total_mb order by 4

ANR2956E Unable to access SQL base table 'VOLUMEUSAGE'.

   |
 ..V
 me) as AVG MB/tape from volumeusage vu, auditocc ao where vu.

ANS8001I Return code 26.

thanks,
Avy Wong
Business Continuity Administrator
Mohegan Sun
1 Mohegan Sun Blvd
Uncasville, CT 06382
(860)862-8164
(cell) (860)961-6976

DISCLAIMER

Confidential Communication: This email and any attachments are intended for the 
addressee(s)
only and are confidential. They may contain legally privileged or copyright 
material. If you
are not the intended recipient, please contact the sender immediately by reply 
email and 
delete this email and any attachments. You must not read, copy, use, distribute 
or disclose 
the contents of this email without consent and Harvey Norman Holdings Limited 
ACN 003 237 545
(and its related subsidiaries) (“Harvey Norman”) does not accept responsibility 
for any
unauthorised use or reliance on the contents of this email.

Harvey Norman does not represent or warrant that the integrity of this email 
has been maintained
or that it is free from errors, viruses, interceptions or interference. Any 
views expressed by
the sender do not necessarily represent the views of Harvey Norman.

This notice should not be removed from this email.

Re: select statement to detail a filespace

[Choudarapu, Ramakrishna (GTS)]

http://www-1.ibm.com/support/docview.wss?rs=1019context=SSSQWCcontext=
SSGSG7q1=ACTIVE+Data+pool+sizeuid=swg21267260loc=en_UScs=utf-8lang=
en

Regards, Rama

-Original Message-
From: ADSM: Dist Stor Manager [mailto:[EMAIL PROTECTED] On Behalf Of
Tim Brown
Sent: Wednesday, August 13, 2008 8:54 AM
To: ADSM-L@VM.MARIST.EDU
Subject: [ADSM-L] select statement to detail a filespace


Is it possible to query TSM with a select statement that will detail out
a particular
filespace. If so does any one have one.

Say a filespace \\NODE1\D$ for node NODE1. This particular drive has 5
folders
Can TSM show the percentage of storage for each or the number of files
active or inactive.

Tim Brown
Systems Specialist - Project Leader
Central Hudson Gas  Electric
284 South Ave
Poughkeepsie, NY 12601
Email: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED]
Phone: 845-486-5643
Fax: 845-486-5921
Cell: 845-235-4255


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: select statement to detail a filespace

[Richard Sims]

On Aug 13, 2008, at 8:53 AM, Tim Brown wrote:


Is it possible to query TSM with a select statement that will detail
out
a particular
filespace. If so does any one have one.

Say a filespace \\NODE1\D$ for node NODE1. This particular drive has 5
folders
Can TSM show the percentage of storage for each or the number of files
active or inactive.


Whereas the sizes of stored files cannot generally be determined from
TSM server queries, a storage percentage report is not doable.

Active/Inactive state is recorded by object, in the Backups and
Archives tables, meaning that a costly scan would have to be run.  I
would not approach doing that unless there was a serious need for such
a report, given what it does to the server and database.

  Richard Sims

Re: select statement for active sessions based on policy domain

[Choudarapu, Ramakrishna (GTS)]

select session_id,client_name from sessions where session_type='Node'
and client_name in (select node_name from nodes where
domain_name='DOMAIN_NAME')

-Original Message-
From: ADSM: Dist Stor Manager [mailto:[EMAIL PROTECTED] On Behalf Of
Tim Brown
Sent: Tuesday, June 03, 2008 4:48 PM
To: ADSM-L@VM.MARIST.EDU
Subject: [ADSM-L] select statement for active sessions based on policy
domain


Is there a select statement that would show only
client sessions for a particular donmain.

The domain field doesnt appear in the results of a q session or q
session f=d

Tim Brown
Systems Specialist - Project Leader
Central Hudson Gas  Electric
284 South Ave
Poughkeepsie, NY 12601
Email: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED]
Phone: 845-486-5643
Fax: 845-486-5921
Cell: 845-235-4255


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: select statement for active sessions based on policy domain

[Andrew Raibeck]

Well, you can check the SESSIONS table to answer the question as to
whether the DOMAIN appears in there (it does not).

So do a SELECT from the SESSIONS table and add a WHERE criterion that
includes a subquery from the NODES table. The subquery can return node
names that are in the desired domain.

Regards,

Andy

Andy Raibeck
IBM Software Group
Tivoli Storage Manager Client Product Development
Level 3 Team Lead
Internal Notes e-mail: Andrew Raibeck/Tucson/[EMAIL PROTECTED]
Internet e-mail: [EMAIL PROTECTED]

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 06/03/2008
01:48:15 PM:

 Is there a select statement that would show only
 client sessions for a particular donmain.

 The domain field doesnt appear in the results of a q session or q
 session f=d

 Tim Brown
 Systems Specialist - Project Leader
 Central Hudson Gas  Electric
 284 South Ave
 Poughkeepsie, NY 12601
 Email: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED]
 Phone: 845-486-5643
 Fax: 845-486-5921
 Cell: 845-235-4255

Re: Select statement

[Wanda Prather]

Q DRM * gives you the list of tapes in the COPY pool that aren't marked
OFFSITE.
(By default it also includes the DBBACKUPS, unless you specify something
else on the Q DRM)

Depending on when you run the Q DRM, it MAY be the same as the tapes
created that day.
It will USUALLY be the tapes created since the last time you did vaulting.


On 1/15/08, Matthew Warren [EMAIL PROTECTED] wrote:

 Would this be the count of tapes made mountable that day?,   q drm *
 wherestate=mountable?  - I don;'t have access to a TSM server to test a
 select for the same info. That would assume you are moving mountable tapes
 to state 'vault' each day.


 Matt.



   Internet
   [EMAIL PROTECTED]

   
   To
 ADSM-L
   Sent by: ADSM-L@VM.MARIST.EDU
 cc

   04/01/2008
 18:12
 Subject
 [ADSM-L] Select statement

 Please respond to
ADSM-L@VM.MARIST.EDU








 Hello,
 Does anyone has a select statement I can use to find out how many tapes
 I used on a daily basis?
 Thanks!





 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.
 Do not print this message unless it is necessary,
 consider the environment.

-

 Ce message et toutes les pieces jointes (ci-apres le
 message) sont etablis a l'intention exclusive de ses
 destinataires et sont confidentiels. Si vous recevez ce
 message par erreur, merci de le detruire et d'en avertir
 immediatement l'expediteur. Toute utilisation de ce
 message non conforme a sa destination, toute diffusion
 ou toute publication, totale ou partielle, est interdite, sauf
 autorisation expresse. L'internet ne permettant pas
 d'assurer l'integrite de ce message, BNP PARIBAS (et ses
 filiales) decline(nt) toute responsabilite au titre de ce
 message, dans l'hypothese ou il aurait ete modifie.
 N'imprimez ce message que si necessaire,
 pensez a l'environnement.

Re: Select statement

[Ribeiro, Ricardo]

Good idea, thanks
Ricardo Ribeiro
Storage Administrator
623-217-4139

- Original Message -
From: ADSM: Dist Stor Manager ADSM-L@VM.MARIST.EDU
To: ADSM-L@VM.MARIST.EDU ADSM-L@VM.MARIST.EDU
Sent: Fri Jan 04 16:38:41 2008
Subject: Re: [ADSM-L] Select statement

You could also look at volhistory where use(?)='STGNEW' and date =desired range.



From: ADSM: Dist Stor Manager on behalf of Ribeiro, Ricardo
Sent: Fri 1/4/2008 2:51 PM
To: ADSM-L@VM.MARIST.EDU
Subject: Re: [ADSM-L] Select statement



Thank you!

-Original Message-
From: ADSM: Dist Stor Manager [mailto:[EMAIL PROTECTED] On Behalf Of
Richard Sims
Sent: Friday, January 04, 2008 12:47 PM
To: ADSM-L@VM.MARIST.EDU
Subject: Re: [ADSM-L] Select statement

On Jan 4, 2008, at 1:12 PM, Ribeiro, Ricardo wrote:

 Hello,
 Does anyone has a select statement I can use to find out how many
 tapes I used on a daily basis?
 Thanks!

Most commonly, tapes automatically come from the scratches collection
and return to scratch when emptied (rather than being assigned to a
storage pool via DEFine Volume).  And, UPDate Volume is rarely performed
on volumes such that the administrative change timestamp remains that of
when the volume was taken from scratch to be used in a storage pool.
For basic reporting you can thus employ a variant of a Select statement
like:

select * from volumes where DAYS(CURRENT_DATE)-DAYS(CHG_TIME)2 and
DATE(LAST_WRITE_DATE)=DATE(CURRENT_TIMESTAMP)

 From that point of departure you can play with timestamps for desired
intervals, and further select on STATUS value as desired.

Richard Sims  http://people.bu.edu/rbs/

Re: Select statement

[Choudarapu, Ramakrishna (GTI)]

We use this to find out the scratch counts:

select library_name, status, count(*) as VOL_COUNT from libvolumes group
by library_name,status

Is this the one, you are looking for?

-Original Message-
From: ADSM: Dist Stor Manager [mailto:[EMAIL PROTECTED] On Behalf Of
Ribeiro, Ricardo
Sent: Friday, January 04, 2008 1:12 PM
To: ADSM-L@VM.MARIST.EDU
Subject: [ADSM-L] Select statement


Hello,
Does anyone has a select statement I can use to find out how many tapes
I used on a daily basis?
Thanks!


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: Select statement

[Ribeiro, Ricardo]

No, I need one that tells me how many I used and not how many I have...
Thanks! 

-Original Message-
From: ADSM: Dist Stor Manager [mailto:[EMAIL PROTECTED] On Behalf Of
Choudarapu, Ramakrishna (GTI)
Sent: Friday, January 04, 2008 11:17 AM
To: ADSM-L@VM.MARIST.EDU
Subject: Re: [ADSM-L] Select statement

We use this to find out the scratch counts:

select library_name, status, count(*) as VOL_COUNT from libvolumes group
by library_name,status

Is this the one, you are looking for?

-Original Message-
From: ADSM: Dist Stor Manager [mailto:[EMAIL PROTECTED] On Behalf Of
Ribeiro, Ricardo
Sent: Friday, January 04, 2008 1:12 PM
To: ADSM-L@VM.MARIST.EDU
Subject: [ADSM-L] Select statement


Hello,
Does anyone has a select statement I can use to find out how many tapes
I used on a daily basis?
Thanks!


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: Select statement

[Larry Peifer]

This is what I run to get that kind of information:

select library_name,volume_name from libvolumes where status='Scratch'

select stgpool_name,status,count(*) from volumes where scratch='YES' group
by stgpool_name,status

 select library_name, last_use, status, count(status) from libvolumes
group by library_name,last_use,status

select (sum(total_mb)/1024)/1024 TOTAL_TB_ON_TAPE FROM auditocc
( you need to have run 'audit licenses' just prior to this query in order
to update auditocc table)

select count(*) Tape Volumes OnLine from libvolumes

 select library_name,count(*) SCRATCH_VOLUMES, (count(*) * 200)
G_bytes_FREE from libvolumes where status='Scratch' group by library_name

Hope that helps.




Ribeiro, Ricardo [EMAIL PROTECTED]
Sent by: ADSM: Dist Stor Manager ADSM-L@VM.MARIST.EDU
01/04/2008 10:20 AM
Please respond to
ADSM: Dist Stor Manager ADSM-L@VM.MARIST.EDU


To
ADSM-L@VM.MARIST.EDU
cc

Subject
Re: [ADSM-L] Select statement






No, I need one that tells me how many I used and not how many I have...
Thanks!

-Original Message-
From: ADSM: Dist Stor Manager [mailto:[EMAIL PROTECTED] On Behalf Of
Choudarapu, Ramakrishna (GTI)
Sent: Friday, January 04, 2008 11:17 AM
To: ADSM-L@VM.MARIST.EDU
Subject: Re: [ADSM-L] Select statement

We use this to find out the scratch counts:

select library_name, status, count(*) as VOL_COUNT from libvolumes group
by library_name,status

Is this the one, you are looking for?

-Original Message-
From: ADSM: Dist Stor Manager [mailto:[EMAIL PROTECTED] On Behalf Of
Ribeiro, Ricardo
Sent: Friday, January 04, 2008 1:12 PM
To: ADSM-L@VM.MARIST.EDU
Subject: [ADSM-L] Select statement


Hello,
Does anyone has a select statement I can use to find out how many tapes
I used on a daily basis?
Thanks!


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: Select statement

[Richard Sims]

On Jan 4, 2008, at 1:12 PM, Ribeiro, Ricardo wrote:


Hello,
Does anyone has a select statement I can use to find out how many
tapes
I used on a daily basis?
Thanks!


Most commonly, tapes automatically come from the scratches collection
and return to scratch when emptied (rather than being assigned to a
storage pool via DEFine Volume).  And, UPDate Volume is rarely
performed on volumes such that the administrative change timestamp
remains that of when the volume was taken from scratch to be used in
a storage pool.  For basic reporting you can thus employ a variant of
a Select statement like:

select * from volumes where DAYS(CURRENT_DATE)-DAYS(CHG_TIME)2 and
DATE(LAST_WRITE_DATE)=DATE(CURRENT_TIMESTAMP)

From that point of departure you can play with timestamps for
desired intervals, and further select on STATUS value as desired.

   Richard Sims  http://people.bu.edu/rbs/

Re: Select statement

[Ribeiro, Ricardo]

Thank you! 

-Original Message-
From: ADSM: Dist Stor Manager [mailto:[EMAIL PROTECTED] On Behalf Of
Richard Sims
Sent: Friday, January 04, 2008 12:47 PM
To: ADSM-L@VM.MARIST.EDU
Subject: Re: [ADSM-L] Select statement

On Jan 4, 2008, at 1:12 PM, Ribeiro, Ricardo wrote:

 Hello,
 Does anyone has a select statement I can use to find out how many 
 tapes I used on a daily basis?
 Thanks!

Most commonly, tapes automatically come from the scratches collection
and return to scratch when emptied (rather than being assigned to a
storage pool via DEFine Volume).  And, UPDate Volume is rarely performed
on volumes such that the administrative change timestamp remains that of
when the volume was taken from scratch to be used in a storage pool.
For basic reporting you can thus employ a variant of a Select statement
like:

select * from volumes where DAYS(CURRENT_DATE)-DAYS(CHG_TIME)2 and
DATE(LAST_WRITE_DATE)=DATE(CURRENT_TIMESTAMP)

 From that point of departure you can play with timestamps for desired
intervals, and further select on STATUS value as desired.

Richard Sims  http://people.bu.edu/rbs/

Re: Select statement

[Fred Johanson]

You could also look at volhistory where use(?)='STGNEW' and date =desired range.



From: ADSM: Dist Stor Manager on behalf of Ribeiro, Ricardo
Sent: Fri 1/4/2008 2:51 PM
To: ADSM-L@VM.MARIST.EDU
Subject: Re: [ADSM-L] Select statement



Thank you!

-Original Message-
From: ADSM: Dist Stor Manager [mailto:[EMAIL PROTECTED] On Behalf Of
Richard Sims
Sent: Friday, January 04, 2008 12:47 PM
To: ADSM-L@VM.MARIST.EDU
Subject: Re: [ADSM-L] Select statement

On Jan 4, 2008, at 1:12 PM, Ribeiro, Ricardo wrote:

 Hello,
 Does anyone has a select statement I can use to find out how many
 tapes I used on a daily basis?
 Thanks!

Most commonly, tapes automatically come from the scratches collection
and return to scratch when emptied (rather than being assigned to a
storage pool via DEFine Volume).  And, UPDate Volume is rarely performed
on volumes such that the administrative change timestamp remains that of
when the volume was taken from scratch to be used in a storage pool.
For basic reporting you can thus employ a variant of a Select statement
like:

select * from volumes where DAYS(CURRENT_DATE)-DAYS(CHG_TIME)2 and
DATE(LAST_WRITE_DATE)=DATE(CURRENT_TIMESTAMP)

 From that point of departure you can play with timestamps for desired
intervals, and further select on STATUS value as desired.

Richard Sims  http://people.bu.edu/rbs/

Re: select statement

[goc]

try this --

select node_name, passexp from nodes where PASSEXP!=0

On Nov 27, 2007 4:06 PM, Herrmann, Boris [EMAIL PROTECTED] wrote:
 Hello,

 we would like to get a list of nodes where the option PASSEXP is unequal 0. 
 We used the following SQL statement:

 tsm: SA023ADDselect node_name, passexp from nodes where passexp0
 ANR2034E SELECT: No match found using this criteria.
 ANS8001I Rückkehrcode 11.

 But we definitely have nodes where the option passexp is not set. So this 
 field is complete empty. Why wouldn't our select statement show this nodes?

 Any help is appreciate.

 With kind regards,



 Boris Herrmann

 Produktion / Heterogene Systeme



 ARAG IT GmbH

 ARAG Platz 1, 40472 Düsseldorf



 Tel:  +49 (0)211 964-1137

 Fax: +49 (0)211 964-1155

 [EMAIL PROTECTED]

 www.ARAG.de http://www.arag.de/





 Geschäftsführer:  Ottmar Liebler, Hanno Petersen

 Sitz und Registergericht:  Düsseldorf,  HRB 10934

 USt-ID-Nr.:  DE 119 356 473

Re: select statement

[Rainer Schöpf]

I suspect the value of passexp can be NULL, so it should be

select node_name,passexp from nodes where not (passexp is null)

  Rainer
 

On Tue, 27 Nov 2007, goc wrote:

  try this --
  
  select node_name, passexp from nodes where PASSEXP!=0
  
  On Nov 27, 2007 4:06 PM, Herrmann, Boris [EMAIL PROTECTED] wrote:
   Hello,
  
   we would like to get a list of nodes where the option PASSEXP is unequal 
   0. We used the following SQL statement:
  
   tsm: SA023ADDselect node_name, passexp from nodes where passexp0
   ANR2034E SELECT: No match found using this criteria.
   ANS8001I Rückkehrcode 11.
  
   But we definitely have nodes where the option passexp is not set. So this 
   field is complete empty. Why wouldn't our select statement show this nodes?
  
   Any help is appreciate.
  
   With kind regards,
  
  
  
   Boris Herrmann
  
   Produktion / Heterogene Systeme
  
  
  
   ARAG IT GmbH
  
   ARAG Platz 1, 40472 Düsseldorf
  
  
  
   Tel:  +49 (0)211 964-1137
  
   Fax: +49 (0)211 964-1155
  
   [EMAIL PROTECTED]
  
   www.ARAG.de http://www.arag.de/
  
  
  
  
  
   Geschäftsführer:  Ottmar Liebler, Hanno Petersen
  
   Sitz und Registergericht:  Düsseldorf,  HRB 10934
  
   USt-ID-Nr.:  DE 119 356 473
  
  
  
  
  
  


ProteoSys AG
Carl-Zeiss-Straße 51
55129 Mainz

Dr. Rainer Schöpf
Leiter Software/Softwareentwicklung

Mail:   [EMAIL PROTECTED]
Phone:  +49-(0)6131-50192-41
Fax:+49-(0)6131-50192-11
WWW:http://www.proteosys.com/

ProteoSys AG - Carl-Zeiss-Str. 51 - D-55129 Mainz
Amtsgericht Mainz HRB 7508 - USt.-Id Nr.: DE213940570
Vorstand: Helmut Matthies (Vorsitzender), Prof. Dr. André Schrattenholz
Vorsitzender des Aufsichtsrates: Dr. Werner Zöllner

Re: Select statement for space occupied by type of file?

[Thach, Kevin G]

Thanks very much guys! 

-Original Message-
From: ADSM: Dist Stor Manager [mailto:[EMAIL PROTECTED] On Behalf Of
William Boyer
Sent: Friday, October 19, 2007 9:13 AM
To: ADSM-L@VM.MARIST.EDU
Subject: Re: Select statement for space occupied by type of file?

Maybe the easiest is to either from the original server or from another
Windows box with the TSM client, start the client CLI and run a QUERY
BACKUP for *.PST files with -SUBDIR=YES. If you want all, the include
the -INA flag to get the inactive version(s), too.
It's quick and easy. Pipe the output to a file and you might be able to
import it in to Excel.

Sure beats trying to query the BACKUPS table! :-)

Bill Boyer
I haven't lost my mind...it's backed up on tape somewhere! - ??


-Original Message-
From: ADSM: Dist Stor Manager [mailto:[EMAIL PROTECTED] On Behalf Of
Thach, Kevin G
Sent: Friday, October 19, 2007 8:20 AM
To: ADSM-L@VM.MARIST.EDU
Subject: Select statement for space occupied by type of file?

Hello-

I'm trying to determine the amount of space occupied by .pst files for a
certain node.  I cannot figure out a good way to gather this
information.  The backups table doesn't have size information, and the
occupancy table doesn't have file information.  Can anyone offer up a
select statement that would work?

Thanks so much!



-
This E-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended
only for the use of the Individual(s) named above.  If you are not the
intended recipient of this E-mail, or the employee or agent responsible
for delivering it to the intended recipient, you are hereby notified
that any dissemination or copying of this E-mail is strictly prohibited.
If you have received this E-mail in error, please immediately notify us
at (865)374-4900 or notify us by E-mail at [EMAIL PROTECTED]

Re: Select statement for space occupied by type of file?

[Thorneycroft, Doug]

dsmc q backup -nodename=node '{\\node\d$}\directory\*.pst' -subdir=yes 
c:\path\output.txt

Load the output into excel as fixed width text.

-Original Message-
From: ADSM: Dist Stor Manager [mailto:[EMAIL PROTECTED] Behalf Of
Thach, Kevin G
Sent: Friday, October 19, 2007 5:20 AM
To: ADSM-L@VM.MARIST.EDU
Subject: Select statement for space occupied by type of file?


Hello-
 
I'm trying to determine the amount of space occupied by .pst files for a
certain node.  I cannot figure out a good way to gather this
information.  The backups table doesn't have size information, and the
occupancy table doesn't have file information.  Can anyone offer up a
select statement that would work?  
 
Thanks so much!



-
This E-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION
intended only for the use of the Individual(s) named above.  If you
are not the intended recipient of this E-mail, or the employee or
agent responsible for delivering it to the intended recipient, you
are hereby notified that any dissemination or copying of this
E-mail is strictly prohibited.  If you have received this E-mail in
error, please immediately notify us at (865)374-4900 or notify us
by E-mail at [EMAIL PROTECTED]

Re: Select statement for space occupied by type of file?

[William Boyer]

Maybe the easiest is to either from the original server or from another Windows 
box with the TSM client, start the client CLI and
run a QUERY BACKUP for *.PST files with -SUBDIR=YES. If you want all, the 
include the -INA flag to get the inactive version(s), too.
It's quick and easy. Pipe the output to a file and you might be able to import 
it in to Excel.

Sure beats trying to query the BACKUPS table! :-)

Bill Boyer
I haven't lost my mind...it's backed up on tape somewhere! - ??


-Original Message-
From: ADSM: Dist Stor Manager [mailto:[EMAIL PROTECTED] On Behalf Of Thach, 
Kevin G
Sent: Friday, October 19, 2007 8:20 AM
To: ADSM-L@VM.MARIST.EDU
Subject: Select statement for space occupied by type of file?

Hello-

I'm trying to determine the amount of space occupied by .pst files for a 
certain node.  I cannot figure out a good way to gather
this information.  The backups table doesn't have size information, and the 
occupancy table doesn't have file information.  Can
anyone offer up a select statement that would work?

Thanks so much!



-
This E-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended only for 
the use of the Individual(s) named above.  If you are
not the intended recipient of this E-mail, or the employee or agent responsible 
for delivering it to the intended recipient, you are
hereby notified that any dissemination or copying of this E-mail is strictly 
prohibited.  If you have received this E-mail in error,
please immediately notify us at (865)374-4900 or notify us by E-mail at [EMAIL 
PROTECTED]

Re: select statement within administrative command

[Henrik Wahlstedt]

Hi,

No, but try:

Select 'upd node', node_name as node_name  ,
'clopt=win_optset' as clopt=win_optset from nodes where
platform_name='WinNT'

Copy or redirect the output to a text file ex. cloptset.mac.

And run the macro, macro c:\cloptset.mac.


//Henrik





-Original Message-
From: ADSM: Dist Stor Manager [mailto:[EMAIL PROTECTED] On Behalf Of
Michael Green
Sent: den 6 september 2007 12:10
To: ADSM-L@VM.MARIST.EDU
Subject: select statement within administrative command

I want to do the following:

upd node(select  node_name   from nodes   where
platform_name='WinNT')clopt=win_optset

Is it possible to integrate a select statement within an administrative
command?
--
Warm regards,
Michael Green


---
The information contained in this message may be CONFIDENTIAL and is
intended for the addressee only. Any unauthorised use, dissemination of the
information or copying of this message is prohibited. If you are not the
addressee, please notify the sender immediately by return e-mail and delete
this message.
Thank you.

Re: select statement within administrative command

[Michael Green]

On 9/6/07, Henrik Wahlstedt [EMAIL PROTECTED] wrote:
 Hi,

 No, but try:

 Select 'upd node', node_name as node_name  ,
 'clopt=win_optset' as clopt=win_optset from nodes where
 platform_name='WinNT'

 Copy or redirect the output to a text file ex. cloptset.mac.

 And run the macro, macro c:\cloptset.mac.

that will cut it, thanks!
--
Warm regards,
Michael Green

Re: Select Statement

[Thomas Denier]

-Brenda Collins wrote: -

Does anyone know of a way to determine what files existed under a
specific directory path for a node as of a certain date and time?

Your subject line suggests that you are looking for a select command,
but I think it will be easier to get the information using one of the
client interfaces. If need be you can use the '-virtualnodename'
option to run the interface on a system other than the one the files
came from.

If you use the command line client, you can execute a 'query backup'
command with '-subdir=y' and appropriate values for the '-pitdate'
and '-pittime' options.

If you use the GUI client, request a restore, click the 'Point In
Time' buton, and specify the appropriate date and time. You can then
drill down into the file system and directory structure to see what
files are listed. When you are done you can exit without going through
with the restore.

It should be noted that any method of using the TSM database to
determine past file populations has inherent limitations. TSM only
examines file populations when backups are run. It cannot tell you
exactly what happened between backups. For example, a file that came
and went between consectutive backups would be completely invisible
to TSM. Depending on how far back in time you want to look, TSM
expiration processing may have erased information about some of thefiles
present at the time of interest.

Re: SELECT Statement

[Andrew Raibeck]

 Now I want also a select statement which shows me all the nodes
 which haven't any associated Schedules. How could I do that?

select node_name from nodes \
   where node_name not in (select distinct node_name from associations)

Regards,

Andy

Andy Raibeck
IBM Software Group
Tivoli Storage Manager Client Development
Internal Notes e-mail: Andrew Raibeck/Tucson/[EMAIL PROTECTED]
Internet e-mail: [EMAIL PROTECTED]

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.

Re: SELECT Statement

[Hans-Jørgen Bergmann]

This should give you what your looking for:

select node_name as Nodes Not Associated -
 from nodes -
 where node_name not in -
 (select node_name from associations)

best regards
Hans-Jørgen Bergmann
Total Storage Solutions AS
[EMAIL PROTECTED]

ons, 07.02.2007 kl. 13.29 +0100, skrev Herrmann, Boris:
 Hi all,
  
 I have a select statement which shows me all nodes with corresponding 
 active (associated) Schedules:
 select n.node_name, n.domain_name, n.option_set, a.schedule_name from nodes 
 n, associations a where n.node_name=a.node_name
  
 Now I want also a select statement which shows me all the nodes which haven't 
 any associated Schedules. How could I do that?
  
 Any help are appreciate.
  
 Mit freundlichen Grüßen / With kind regard,
 
 __
 
  
 
 Boris Herrmann
 
 Produktion / Heterogene Systeme 
 
  
 
 ARAG IT GmbH
 
 ARAG Platz 1, 40472 Düsseldorf
 
  
 
 Tel:  +49 (0)211 964-1137
 
 Fax: +49 (0)211 964-1155
 
 [EMAIL PROTECTED]
 
 www.ARAG.de http://www.arag.de/ 
 
  
 
 
 
 Geschäftsführer:  Ottmar Liebler, Hanno Petersen 
 
 Sitz und Registergericht:  Düsseldorf,  HRB 10934
 
 USt-ID-Nr.:  DE 119 356 473
 
  
 

Re: Select statement for individual migration processes

[Meadows, Andrew]

Just a thought I am not sure if this will work but Attack it from another 
angle. It sounds like you are looking at the stg pool table from the select 
statement. Instead do the select statement against the activity log table 
looking for the migration processes.

-Original Message-
From: ADSM: Dist Stor Manager ADSM-L@VM.MARIST.EDU
To: ADSM-L@VM.MARIST.EDU ADSM-L@VM.MARIST.EDU
Sent: Fri Dec 08 05:19:43 2006
Subject: Select statement for individual migration processes

I'm trying to construct a select statement that returns the historic
details of individual migration processes.

The summary table regards migration (per stgpool) as a single entity and
returns the total time for all the processes (ie when the last process
completes) and the entire amount of data moved (ie summation of bytes
transferred for all the migration processes).

Is it possible to get the breakdown of each individual migration thread
via a select statement?

I am trying to avoid calculating it manually by interrogating the
activity log.

Thanks

Leigh


This message is intended only for the use of the Addressee and
may contain information that is PRIVILEGED and CONFIDENTIAL.

If you are not the intended recipient, you are hereby notified
that any dissemination of this communication is strictly prohibited.

If you have received this communication in error, please erase
all copies of the message and its attachments and notify us
immediately.

Thank you.

Re: select statement for All active files for a node

[Matthew Warren]

I'm not sure if you can do this with a select. One thing you could try
would be an export node, with filedata=ALLActive and preview=yes. 


Matt.

 -Original Message-
 From: ADSM: Dist Stor Manager [mailto:[EMAIL PROTECTED] 
 On Behalf Of Fawad Baig
 Sent: 07 November 2006 15:09
 To: ADSM-L@VM.MARIST.EDU
 Subject: [ADSM-L] select statement for All active files for a node
 
 Hi
 
 I need a select statement to calculate total GB for all the ACTIVE
 files backed up for a node. any thoughts anyone?
 
 --
 Regards,
 
 Fawad Baig
 
 
  
 


This email is confidential and may be privileged. If you are not the intended 
recipient please notify the sender immediately and delete the email from your 
computer. 

You should not copy the email, use it for any purpose or disclose its contents 
to any other person.
Please note that any views or opinions presented in this email may be personal 
to the author and do not necessarily represent the views or opinions of Digica.
It is the responsibility of the recipient to check this email for the presence 
of viruses. Digica accepts no liability for any damage caused by any virus 
transmitted by this email.

UK: Phoenix House, Colliers Way, Nottingham, NG8 6AT UK
Reception Tel: + 44 (0) 115 977 1177
Support Centre: 0845 607 7070
Fax: + 44 (0) 115 977 7000
http://www.digica.com

SOUTH AFRICA: Building 3, Parc du Cap, Mispel Road, Bellville, 7535, South 
Africa
Tel: + 27 (0) 21 957 4900
Fax: + 27 (0) 21 948 3135
http://www.digica.com

Re: Select Statement Syntax

[Jonathan Siegle]

Joni Moyer said the following on 10/5/06 9:00 AM:

Hello Everyone,

I have been trying to figure out how to have the syntax of the below
output so that it will all go on 1 line, but so far I have been very
unsuccessful.

Here is the statement I am issuing:

select node_name,filespace_name,backup_start,backup_end from filespaces
where node_name like 'NAS%' or node_name like 'SERVER%'

And here is the output:

NODE_NAME  FILESPACE_NAME   BACKUP_START
BACKUP_END
-- -- --
--
NAS_SERVER_2_OFFS- /  2006-10-04
2006-10-04
 ITE 21:20:32.00
21:23:03.00
NAS_SERVER_2_OFFS- /.etc_common   2006-10-04
2006-10-05
 ITE 21:20:32.00
02:34:44.00
NAS_SERVER_2_OFFS- /apache2006-10-04
2006-10-05
 ITE 21:20:32.00
02:21:08.00
NAS_SERVER_2_OFFS- /clearcase_prod2006-10-04
2006-10-04
 ITE 21:20:32.00
22:36:12.00

As you can see the node_name column wraps.  I tried to do a left justify
and I also tried to make it 20 characters wide, but I guess I am doing
something wrong because I continue to get the same output.



Hi Joni,
Try doing set sqldisplaymode wide before you run your select
statement. That should put each column in your select statement on one
line. You could also try using -tabd when you call dsmadmc and then also
set sqldisplaymode wide once the admin session has started. That might
 give you the whole ball of wax on one line.

-Jonathan

Re: Select Statement Syntax to view the schedule

[Gopinathan, Srinath]

Hi All,

I am able to see the node info using the following command

TSMSelect * from nodes

However, the same doesn't work as

TSMSelect * from schedules

Can anybody let me know the right command for getting info about
schedules using select statement

Regards,
Srinath G

This e-mail has been scanned for viruses by the Cable  Wireless e-mail 
security system - powered by MessageLabs. For more information on a proactive 
managed e-mail security service,  visit http://www.cw.com/uk/emailprotection/ 
 
The information contained in this e-mail is confidential and may also be 
subject to legal privilege. It is intended only for the recipient(s) named 
above. If you are not named above as a recipient, you must not read, copy, 
disclose, forward or otherwise use the information contained in this email. If 
you have received this e-mail in error, please notify the sender (whose contact 
details are above) immediately by reply e-mail and delete the message and any 
attachments without retaining any copies.

Re: Select Statement Syntax

[PAC Brion Arnaud]

Joni,

Node_name column is wrapping because the overall length of the output is
too long : you should try to reduce it.

You where on the right track for doing this while trying to reduce the
output for backup_start/end fields.
To achieve this, try to use : 

select node_name,filespace_name,time(backup_start),time(backup_end) from
filespaces where node_name like 'NAS%' or node_name like 'SERVER%' 

However, if you're having long node names or filespaces names, you'll
still get wrapping problems ...

HTH.

Cheers.


Arnaud 


**
Panalpina Management Ltd., Basle, Switzerland, 
CIT Department Viadukstrasse 42, P.O. Box 4002 Basel/CH
Phone:  +41 (61) 226 11 11, FAX: +41 (61) 226 17 01
Direct: +41 (61) 226 19 78
e-mail: [EMAIL PROTECTED]

**

-Original Message-
From: ADSM: Dist Stor Manager [mailto:[EMAIL PROTECTED] On Behalf Of
Joni Moyer
Sent: Thursday, 05 October, 2006 15:00
To: ADSM-L@VM.MARIST.EDU
Subject: Select Statement Syntax

Hello Everyone,

I have been trying to figure out how to have the syntax of the below
output so that it will all go on 1 line, but so far I have been very
unsuccessful.

Here is the statement I am issuing:

select node_name,filespace_name,backup_start,backup_end from filespaces
where node_name like 'NAS%' or node_name like 'SERVER%'

And here is the output:

NODE_NAME  FILESPACE_NAME   BACKUP_START
BACKUP_END
-- -- --
--
NAS_SERVER_2_OFFS- /  2006-10-04
2006-10-04
 ITE 21:20:32.00
21:23:03.00
NAS_SERVER_2_OFFS- /.etc_common   2006-10-04
2006-10-05
 ITE 21:20:32.00
02:34:44.00
NAS_SERVER_2_OFFS- /apache2006-10-04
2006-10-05
 ITE 21:20:32.00
02:21:08.00
NAS_SERVER_2_OFFS- /clearcase_prod2006-10-04
2006-10-04
 ITE 21:20:32.00
22:36:12.00

As you can see the node_name column wraps.  I tried to do a left justify
and I also tried to make it 20 characters wide, but I guess I am doing
something wrong because I continue to get the same output.

I was also wondering if there is a way to change the backup_start to go
on
1 line as well and to make it include the date and the time to be just
hh:mm:ss?

Thank you in advance for any pointers you might have!


Joni Moyer
Highmark
Storage Systems, Senior Systems Programmer Phone Number: (717)302-9966
Fax: (717) 302-9826
[EMAIL PROTECTED]

Re: Select Statement Syntax

[Andrew Raibeck]

Use:

   select node_name as NODE_NAME   ,
  filespace_name as FILESPACE_NAME, ...

padding the quoted titles with enough blank spaces to achieve what you
want.

Alternatively, consider using a Windows spreadsheet or database program to
access the TSM Server via ODBC driver, which can give you  an opportunity
customize the presentation of the data.

Andy Raibeck
IBM Software Group
Tivoli Storage Manager Client Development
Internal Notes e-mail: Andrew Raibeck/Tucson/[EMAIL PROTECTED]
Internet e-mail: [EMAIL PROTECTED]

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 10/05/2006
06:00:04 AM:

 Hello Everyone,

 I have been trying to figure out how to have the syntax of the below
 output so that it will all go on 1 line, but so far I have been very
 unsuccessful.

 Here is the statement I am issuing:

 select node_name,filespace_name,backup_start,backup_end from filespaces
 where node_name like 'NAS%' or node_name like 'SERVER%'

 And here is the output:

 NODE_NAME  FILESPACE_NAME   BACKUP_START
 BACKUP_END
 -- -- --
 --
 NAS_SERVER_2_OFFS- /  2006-10-04
 2006-10-04
  ITE 21:20:32.00
 21:23:03.00
 NAS_SERVER_2_OFFS- /.etc_common   2006-10-04
 2006-10-05
  ITE 21:20:32.00
 02:34:44.00
 NAS_SERVER_2_OFFS- /apache2006-10-04
 2006-10-05
  ITE 21:20:32.00
 02:21:08.00
 NAS_SERVER_2_OFFS- /clearcase_prod2006-10-04
 2006-10-04
  ITE 21:20:32.00
 22:36:12.00

 As you can see the node_name column wraps.  I tried to do a left justify
 and I also tried to make it 20 characters wide, but I guess I am doing
 something wrong because I continue to get the same output.

 I was also wondering if there is a way to change the backup_start to go
on
 1 line as well and to make it include the date and the time to be just
 hh:mm:ss?

 Thank you in advance for any pointers you might have!

 
 Joni Moyer
 Highmark
 Storage Systems, Senior Systems Programmer
 Phone Number: (717)302-9966
 Fax: (717) 302-9826
 [EMAIL PROTECTED]
 

Re: Select Statement Syntax

[Leigh Reed]

Joni,

Try this and see if it makes any difference, it will manipulate the
returned backup_start and backup_end to only be 16 chars and thus loose
the .00

I feel sure that there is tidier way to do this, but I have yet to find
it. Suggestions gladly appreciated. In the meantime this might help.

select
node_name,filespace_name,
substr(char(backup_start),1,16) as BACKUP_START,
substr(char(backup_end),1,16) as BACKUP_END
from filespaces where node_name like 'NAS%' or node_name like 'SERVER%'

Thanks

Leigh

-Original Message-
From: ADSM: Dist Stor Manager [mailto:[EMAIL PROTECTED] On Behalf Of
Joni Moyer
Sent: 05 October 2006 14:00
To: ADSM-L@VM.MARIST.EDU
Subject: [SPAM: 4.000] [ADSM-L] Select Statement Syntax

Hello Everyone,

I have been trying to figure out how to have the syntax of the below
output so that it will all go on 1 line, but so far I have been very
unsuccessful.

Here is the statement I am issuing:

select node_name,filespace_name,backup_start,backup_end from filespaces
where node_name like 'NAS%' or node_name like 'SERVER%'

And here is the output:

NODE_NAME  FILESPACE_NAME   BACKUP_START
BACKUP_END
-- -- --
--
NAS_SERVER_2_OFFS- /  2006-10-04
2006-10-04
 ITE 21:20:32.00
21:23:03.00
NAS_SERVER_2_OFFS- /.etc_common   2006-10-04
2006-10-05
 ITE 21:20:32.00
02:34:44.00
NAS_SERVER_2_OFFS- /apache2006-10-04
2006-10-05
 ITE 21:20:32.00
02:21:08.00
NAS_SERVER_2_OFFS- /clearcase_prod2006-10-04
2006-10-04
 ITE 21:20:32.00
22:36:12.00

As you can see the node_name column wraps.  I tried to do a left justify
and I also tried to make it 20 characters wide, but I guess I am doing
something wrong because I continue to get the same output.

I was also wondering if there is a way to change the backup_start to go
on
1 line as well and to make it include the date and the time to be just
hh:mm:ss?

Thank you in advance for any pointers you might have!


Joni Moyer
Highmark
Storage Systems, Senior Systems Programmer
Phone Number: (717)302-9966
Fax: (717) 302-9826
[EMAIL PROTECTED]

Re: Select Statement Syntax

[Choudarapu, Ramakrishna (GTI)]

dsmadmc -se=tsm server -id=admin id -pa=passwd -commad
-outfile=outputfilename select
node_name,filespace_name,date(backup_start) as
start_date,time(backup_start) as start_time,date(backup_end) as
end_date,time(backup_end) as end_time from filespaces where node_name
like 'NAS%' or node_name like 'SERVER%'

Regards,
Rama





-Original Message-
From: ADSM: Dist Stor Manager [mailto:[EMAIL PROTECTED] On Behalf Of
Joni Moyer
Sent: Thursday, October 05, 2006 9:00 AM
To: ADSM-L@VM.MARIST.EDU
Subject: [ADSM-L] Select Statement Syntax


Hello Everyone,

I have been trying to figure out how to have the syntax of the below
output so that it will all go on 1 line, but so far I have been very
unsuccessful.

Here is the statement I am issuing:

select node_name,filespace_name,backup_start,backup_end from filespaces
where node_name like 'NAS%' or node_name like 'SERVER%'

And here is the output:

NODE_NAME  FILESPACE_NAME   BACKUP_START
BACKUP_END
-- -- --
--
NAS_SERVER_2_OFFS- /  2006-10-04
2006-10-04
 ITE 21:20:32.00
21:23:03.00
NAS_SERVER_2_OFFS- /.etc_common   2006-10-04
2006-10-05
 ITE 21:20:32.00
02:34:44.00
NAS_SERVER_2_OFFS- /apache2006-10-04
2006-10-05
 ITE 21:20:32.00
02:21:08.00
NAS_SERVER_2_OFFS- /clearcase_prod2006-10-04
2006-10-04
 ITE 21:20:32.00
22:36:12.00

As you can see the node_name column wraps.  I tried to do a left justify
and I also tried to make it 20 characters wide, but I guess I am doing
something wrong because I continue to get the same output.

I was also wondering if there is a way to change the backup_start to go
on
1 line as well and to make it include the date and the time to be just
hh:mm:ss?

Thank you in advance for any pointers you might have!


Joni Moyer
Highmark
Storage Systems, Senior Systems Programmer
Phone Number: (717)302-9966
Fax: (717) 302-9826
[EMAIL PROTECTED]



If you are not an intended recipient of this e-mail, please notify the sender, 
delete it and do not read, act upon, print, disclose, copy, retain or 
redistribute it. Click here for important additional terms relating to this 
e-mail. http://www.ml.com/email_terms/

Re: Select Statement Syntax

[Richard Sims]

On Oct 5, 2006, at 9:00 AM, Joni Moyer wrote:


Hello Everyone,

I have been trying to figure out how to have the syntax of the below
output so that it will all go on 1 line, but so far I have been very
unsuccessful.
...


Joni -

Explore the capabilities of SQL in order to use it effectively, and
avoid frustration.  This subject has been covered many times, and is
immortalized in the ADSM QuickFacts and TSM Wiki.  The technique is
to define a column title using the AS clause, using spaces or other
characters to pad out your title width, and your column width will
then match that.  You can also do like:
   SELECT CHAR(NODE_NAME,10) As Node Name FROM FILESPACES
where the CHAR function specifies a definitive width.

   Richard Sims

Re: Select Statement Syntax

[Andrew Carlson]

I went through this same problem.  Never got a satisfactory answer,
totally.  It behaves differently depending on if you do it from a terminal,
or disconnected, like from cron.  Also it depends on the setting of
sqldisplaymode.  Try the command `set sqldisplaymode wide` and see if that
makes it look like you want.

On 10/5/06, Joni Moyer [EMAIL PROTECTED] wrote:


Hello Everyone,

I have been trying to figure out how to have the syntax of the below
output so that it will all go on 1 line, but so far I have been very
unsuccessful.

Here is the statement I am issuing:

select node_name,filespace_name,backup_start,backup_end from filespaces
where node_name like 'NAS%' or node_name like 'SERVER%'

And here is the output:

NODE_NAME  FILESPACE_NAME   BACKUP_START
BACKUP_END
-- -- --
--
NAS_SERVER_2_OFFS- /  2006-10-04
2006-10-04
ITE 21:20:32.00
21:23:03.00
NAS_SERVER_2_OFFS- /.etc_common   2006-10-04
2006-10-05
ITE 21:20:32.00
02:34:44.00
NAS_SERVER_2_OFFS- /apache2006-10-04
2006-10-05
ITE 21:20:32.00
02:21:08.00
NAS_SERVER_2_OFFS- /clearcase_prod2006-10-04
2006-10-04
ITE 21:20:32.00
22:36:12.00

As you can see the node_name column wraps.  I tried to do a left justify
and I also tried to make it 20 characters wide, but I guess I am doing
something wrong because I continue to get the same output.

I was also wondering if there is a way to change the backup_start to go on
1 line as well and to make it include the date and the time to be just
hh:mm:ss?

Thank you in advance for any pointers you might have!


Joni Moyer
Highmark
Storage Systems, Senior Systems Programmer
Phone Number: (717)302-9966
Fax: (717) 302-9826
[EMAIL PROTECTED]






--
Andy Carlson
---
Gamecube:$150,PSO:$50,Broadband Adapter: $35, Hunters License: $8.95/month,
The feeling of seeing the red box with the item you want in it:Priceless.

Re: Select Statement Syntax to view the schedule

[Andrew Raibeck]

Try

   select tabname, remarks from tables

to get information about available tables you can query. You should see
some table names that might be suitable.

Andy Raibeck
IBM Software Group
Tivoli Storage Manager Client Development
Internal Notes e-mail: Andrew Raibeck/Tucson/[EMAIL PROTECTED]
Internet e-mail: [EMAIL PROTECTED]

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 10/05/2006
07:33:02 AM:

 Hi All,

 I am able to see the node info using the following command

 TSMSelect * from nodes

 However, the same doesn't work as

 TSMSelect * from schedules

 Can anybody let me know the right command for getting info about
 schedules using select statement

 Regards,
 Srinath G

 This e-mail has been scanned for viruses by the Cable  Wireless e-
 mail security system - powered by MessageLabs. For more information
 on a proactive managed e-mail security service,  visit http://www.
 cw.com/uk/emailprotection/

 The information contained in this e-mail is confidential and may
 also be subject to legal privilege. It is intended only for the
 recipient(s) named above. If you are not named above as a recipient,
 you must not read, copy, disclose, forward or otherwise use the
 information contained in this email. If you have received this e-
 mail in error, please notify the sender (whose contact details are
 above) immediately by reply e-mail and delete the message and any
 attachments without retaining any copies.

Re: Select Statement Syntax to view the schedule

[PAC Brion Arnaud]

Srinath,

Schedules table doesn't exist !!!
You should try with CLIENT_SCHEDULES or ADMIN_SCHEDULES ...
To get an idea of existing tables : select * from tables.
Cheers.


Arnaud 


**
Panalpina Management Ltd., Basle, Switzerland, 
CIT Department Viadukstrasse 42, P.O. Box 4002 Basel/CH
Phone:  +41 (61) 226 11 11, FAX: +41 (61) 226 17 01
Direct: +41 (61) 226 19 78
e-mail: [EMAIL PROTECTED]

**

-Original Message-
From: ADSM: Dist Stor Manager [mailto:[EMAIL PROTECTED] On Behalf Of
Gopinathan, Srinath
Sent: Thursday, 05 October, 2006 16:33
To: ADSM-L@VM.MARIST.EDU
Subject: Re: Select Statement Syntax to view the schedule

Hi All,

I am able to see the node info using the following command

TSMSelect * from nodes

However, the same doesn't work as

TSMSelect * from schedules

Can anybody let me know the right command for getting info about
schedules using select statement

Regards,
Srinath G

This e-mail has been scanned for viruses by the Cable  Wireless e-mail
security system - powered by MessageLabs. For more information on a
proactive managed e-mail security service,  visit
http://www.cw.com/uk/emailprotection/ 
 
The information contained in this e-mail is confidential and may also be
subject to legal privilege. It is intended only for the recipient(s)
named above. If you are not named above as a recipient, you must not
read, copy, disclose, forward or otherwise use the information contained
in this email. If you have received this e-mail in error, please notify
the sender (whose contact details are above) immediately by reply e-mail
and delete the message and any attachments without retaining any copies.

Re: Select Statement Syntax to view the schedule

[Choudarapu, Ramakrishna (GTI)]

Srinath,

SELECT * FROM CLIENT_SCHEDULES
SELECT * FROM ADMIN_SCHEDULES

Regards,
Ramakrishna Choudarapu





-Original Message-
From: ADSM: Dist Stor Manager [mailto:[EMAIL PROTECTED] On Behalf Of
Gopinathan, Srinath
Sent: Thursday, October 05, 2006 10:33 AM
To: ADSM-L@VM.MARIST.EDU
Subject: Re: [ADSM-L] Select Statement Syntax to view the schedule


Hi All,

I am able to see the node info using the following command

TSMSelect * from nodes

However, the same doesn't work as

TSMSelect * from schedules

Can anybody let me know the right command for getting info about
schedules using select statement

Regards,
Srinath G

This e-mail has been scanned for viruses by the Cable  Wireless e-mail
security system - powered by MessageLabs. For more information on a
proactive managed e-mail security service,  visit
http://www.cw.com/uk/emailprotection/ 
 
The information contained in this e-mail is confidential and may also be
subject to legal privilege. It is intended only for the recipient(s)
named above. If you are not named above as a recipient, you must not
read, copy, disclose, forward or otherwise use the information contained
in this email. If you have received this e-mail in error, please notify
the sender (whose contact details are above) immediately by reply e-mail
and delete the message and any attachments without retaining any copies.


If you are not an intended recipient of this e-mail, please notify the sender, 
delete it and do not read, act upon, print, disclose, copy, retain or 
redistribute it. Click here for important additional terms relating to this 
e-mail. http://www.ml.com/email_terms/

Re: Select Statement Syntax

[Joni Moyer]

Hello Everyone,

I finally came up with a mixture of everyone's suggestions so that I now
have good output:

select node_name as NODE_NAME,filespace_name as
FILESPACE_NAME ,substr(char(backup_start),1,16) as
backup_start,substr(char(backup_end),1,16) as backup_end from filespaces
where node_name like 'NAS%' or node_name like 'SERVER%'

Which gave me:

NODE_NAME FILESPACE_NAME  BACKUP_START
  BACKUP_END
- ---
-- --
NAS_SERVER_2  /   2006-10-04
19:00   2006-10-04 19:01
NAS_SERVER_2  /.etc_common2006-10-04
19:00   2006-10-04 21:19
NAS_SERVER_2  /apache 2006-10-04
19:00   2006-10-04 21:08
NAS_SERVER_2  /clearcase_prod 2006-10-04
19:00   2006-10-04 20:16
NAS_SERVER_2  /clearcase_test 2006-10-04
19:00   2006-10-04 20:03
NAS_SERVER_2  /edi_test   2006-10-04
19:00   2006-10-04 20:24
NAS_SERVER_2  /home2  2006-10-04
19:00   2006-10-04 20:28
NAS_SERVER_2  /home2/home2dir12006-10-04
19:00   2006-10-04 21:02
NAS_SERVER_2  /home2/home2dir22006-10-04
19:00   2006-10-04 21:00
NAS_SERVER_2  /home2/home2dir32006-10-04
19:00   2006-10-04 21:17
NAS_SERVER_2  /home2/home2dir42006-10-04
19:00   2006-10-04 21:17
NAS_SERVER_2  /nas_mp22006-10-04
19:00   2006-10-04 20:23

My next challenge is to figure out how to find any filespaces where the
backup_start date/time is  the backup_end date/time.  It is hard for me
to find failed NDMP backups and this is one of the ways in which I can see
which filespaces failed/missed.  I have tried the following statement:

select node_name as NODE_NAME,filespace_name as
FILESPACE_NAME ,substr(char(backup_start),1,16) as
backup_start,substr(char(backup_end),1,16) as backup_end from filespaces
where node_name like 'NAS%' or node_name like 'SERVER%' and
date(BACKUP_START)=date(BACKUP_END) and
time(BACKUP_START)=time(BACKUP_END)

But this gives me incorrect output.  Does anyone know what I am doing
wrong?  I've even just tried the and backup_start  backup_end and I
still get incorrect data.  Any ideas?

Thanks again!


Joni Moyer
Highmark
Storage Systems, Senior Systems Programmer
Phone Number: (717)302-9966
Fax: (717) 302-9826
[EMAIL PROTECTED]




Andrew Carlson [EMAIL PROTECTED]
Sent by: ADSM: Dist Stor Manager ADSM-L@VM.MARIST.EDU
10/05/2006 10:45 AM
Please respond to
ADSM: Dist Stor Manager ADSM-L@VM.MARIST.EDU


To
ADSM-L@VM.MARIST.EDU
cc

Subject
Re: Select Statement Syntax






I went through this same problem.  Never got a satisfactory answer,
totally.  It behaves differently depending on if you do it from a
terminal,
or disconnected, like from cron.  Also it depends on the setting of
sqldisplaymode.  Try the command `set sqldisplaymode wide` and see if that
makes it look like you want.

On 10/5/06, Joni Moyer [EMAIL PROTECTED] wrote:

 Hello Everyone,

 I have been trying to figure out how to have the syntax of the below
 output so that it will all go on 1 line, but so far I have been very
 unsuccessful.

 Here is the statement I am issuing:

 select node_name,filespace_name,backup_start,backup_end from filespaces
 where node_name like 'NAS%' or node_name like 'SERVER%'

 And here is the output:

 NODE_NAME  FILESPACE_NAME   BACKUP_START
 BACKUP_END
 -- -- --
 --
 NAS_SERVER_2_OFFS- /  2006-10-04
 2006-10-04
 ITE 21:20:32.00
 21:23:03.00
 NAS_SERVER_2_OFFS- /.etc_common   2006-10-04
 2006-10-05
 ITE 21:20:32.00
 02:34:44.00
 NAS_SERVER_2_OFFS- /apache2006-10-04
 2006-10-05
 ITE 21:20:32.00
 02:21:08.00
 NAS_SERVER_2_OFFS- /clearcase_prod2006-10-04
 2006-10-04
 ITE 21:20:32.00
 22:36:12.00

 As you can see the node_name column wraps.  I tried to do a left justify
 and I also tried to make it 20 characters wide, but I guess I am doing
 something wrong because I continue to get the same output.

 I was also wondering if there is a way to change the backup_start to go
on
 1 line as well and to make it include the date and the time to be just
 hh:mm:ss?

 Thank you in advance

Re: Select Statement Syntax

[Aravind Kurapati]

Joni,
Run the command using dsmadmc executable and pass the command as a parameter

dsmadmc -id=admin -PASSWORD=admin -DISPL=LIS select
node_name,filespace_name,backup_start,backup_end from filespaces where
node_name like 'NAS%' or node_name like 'SERVER%'

Aravind

-Original Message-
From: ADSM: Dist Stor Manager [mailto:[EMAIL PROTECTED] On Behalf Of
Joni Moyer
Sent: Thursday, October 05, 2006 2:00 PM
To: ADSM-L@VM.MARIST.EDU
Subject: [ADSM-L] Select Statement Syntax

Hello Everyone,

I have been trying to figure out how to have the syntax of the below
output so that it will all go on 1 line, but so far I have been very
unsuccessful.

Here is the statement I am issuing:

select node_name,filespace_name,backup_start,backup_end from filespaces
where node_name like 'NAS%' or node_name like 'SERVER%'

And here is the output:

NODE_NAME  FILESPACE_NAME   BACKUP_START
BACKUP_END
-- -- --
--
NAS_SERVER_2_OFFS- /  2006-10-04
2006-10-04
 ITE 21:20:32.00
21:23:03.00
NAS_SERVER_2_OFFS- /.etc_common   2006-10-04
2006-10-05
 ITE 21:20:32.00
02:34:44.00
NAS_SERVER_2_OFFS- /apache2006-10-04
2006-10-05
 ITE 21:20:32.00
02:21:08.00
NAS_SERVER_2_OFFS- /clearcase_prod2006-10-04
2006-10-04
 ITE 21:20:32.00
22:36:12.00

As you can see the node_name column wraps.  I tried to do a left justify
and I also tried to make it 20 characters wide, but I guess I am doing
something wrong because I continue to get the same output.

I was also wondering if there is a way to change the backup_start to go on
1 line as well and to make it include the date and the time to be just
hh:mm:ss?

Thank you in advance for any pointers you might have!


Joni Moyer
Highmark
Storage Systems, Senior Systems Programmer
Phone Number: (717)302-9966
Fax: (717) 302-9826
[EMAIL PROTECTED]

Re: Select Statement Syntax

[Andrew Raibeck]

Remember your operator precedence: items are ANDed first, then ORed, so
without strategically placed parentheses around the ORed portion of your
SELECT statement, you will get the wrong results.

Also, why do you need to break down BACKUP_START and BACKUP_END into
comparison of the individual date and time components?

Andy Raibeck
IBM Software Group
Tivoli Storage Manager Client Development
Internal Notes e-mail: Andrew Raibeck/Tucson/[EMAIL PROTECTED]
Internet e-mail: [EMAIL PROTECTED]

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 10/05/2006
09:48:21 AM:

 Hello Everyone,

 I finally came up with a mixture of everyone's suggestions so that I now
 have good output:

 select node_name as NODE_NAME,filespace_name as
 FILESPACE_NAME ,substr(char(backup_start),1,16) as
 backup_start,substr(char(backup_end),1,16) as backup_end from filespaces
 where node_name like 'NAS%' or node_name like 'SERVER%'

 Which gave me:

 NODE_NAME FILESPACE_NAME BACKUP_START
   BACKUP_END
 - ---
 -- --
 NAS_SERVER_2  /   2006-10-04
 19:00   2006-10-04 19:01
 NAS_SERVER_2  /.etc_common2006-10-04
 19:00   2006-10-04 21:19
 NAS_SERVER_2  /apache 2006-10-04
 19:00   2006-10-04 21:08
 NAS_SERVER_2  /clearcase_prod 2006-10-04
 19:00   2006-10-04 20:16
 NAS_SERVER_2  /clearcase_test 2006-10-04
 19:00   2006-10-04 20:03
 NAS_SERVER_2  /edi_test   2006-10-04
 19:00   2006-10-04 20:24
 NAS_SERVER_2  /home2  2006-10-04
 19:00   2006-10-04 20:28
 NAS_SERVER_2  /home2/home2dir12006-10-04
 19:00   2006-10-04 21:02
 NAS_SERVER_2  /home2/home2dir22006-10-04
 19:00   2006-10-04 21:00
 NAS_SERVER_2  /home2/home2dir32006-10-04
 19:00   2006-10-04 21:17
 NAS_SERVER_2  /home2/home2dir42006-10-04
 19:00   2006-10-04 21:17
 NAS_SERVER_2  /nas_mp22006-10-04
 19:00   2006-10-04 20:23

 My next challenge is to figure out how to find any filespaces where the
 backup_start date/time is  the backup_end date/time.  It is hard for me
 to find failed NDMP backups and this is one of the ways in which I can
see
 which filespaces failed/missed.  I have tried the following statement:

 select node_name as NODE_NAME,filespace_name as
 FILESPACE_NAME ,substr(char(backup_start),1,16) as
 backup_start,substr(char(backup_end),1,16) as backup_end from filespaces
 where node_name like 'NAS%' or node_name like 'SERVER%' and
 date(BACKUP_START)=date(BACKUP_END) and
 time(BACKUP_START)=time(BACKUP_END)

 But this gives me incorrect output.  Does anyone know what I am doing
 wrong?  I've even just tried the and backup_start  backup_end and I
 still get incorrect data.  Any ideas?

 Thanks again!

 
 Joni Moyer
 Highmark
 Storage Systems, Senior Systems Programmer
 Phone Number: (717)302-9966
 Fax: (717) 302-9826
 [EMAIL PROTECTED]
 



 Andrew Carlson [EMAIL PROTECTED]
 Sent by: ADSM: Dist Stor Manager ADSM-L@VM.MARIST.EDU
 10/05/2006 10:45 AM
 Please respond to
 ADSM: Dist Stor Manager ADSM-L@VM.MARIST.EDU


 To
 ADSM-L@VM.MARIST.EDU
 cc

 Subject
 Re: Select Statement Syntax






 I went through this same problem.  Never got a satisfactory answer,
 totally.  It behaves differently depending on if you do it from a
 terminal,
 or disconnected, like from cron.  Also it depends on the setting of
 sqldisplaymode.  Try the command `set sqldisplaymode wide` and see if
that
 makes it look like you want.

 On 10/5/06, Joni Moyer [EMAIL PROTECTED] wrote:
 
  Hello Everyone,
 
  I have been trying to figure out how to have the syntax of the below
  output so that it will all go on 1 line, but so far I have been very
  unsuccessful.
 
  Here is the statement I am issuing:
 
  select node_name,filespace_name,backup_start,backup_end from
filespaces
  where node_name like 'NAS%' or node_name like 'SERVER%'
 
  And here is the output:
 
  NODE_NAME  FILESPACE_NAME   BACKUP_START
  BACKUP_END
  -- -- --
  --
  NAS_SERVER_2_OFFS- /  2006-10-04
  2006-10-04
  ITE 21:20

Re: Select Statement Syntax

[Joni Moyer]

Hi Andy,

I pretty much want to see the following:

NODE_NAME FILESPACE_NAME  BACKUP_START
  BACKUP_END
- ---
-- --
NAS_SERVER_2  /   2006-10-04
19:00   2006-10-03 19:01
NAS_SERVER_2  /.etc_common2006-10-04
19:00   2006-10-03 21:19
NAS_SERVER_2  /apache 2006-10-04
19:00   2006-10-03 21:08

This points me to the fact that on 10/4 a backup started, but the last
successful backup ended on the day before, so something must've gone
wrong...

I've been really struggling with getting a good report for my NAS NDMP
backups.  I tried a long time ago to join 2 tables so that I received a
good report, but it would never complete and always timed out.

I'd also like to be able to get a listing of any filesystems that haven't
backed up in over 5 days, but that's another battle.

Thanks again!


Joni Moyer
Highmark
Storage Systems, Senior Systems Programmer
Phone Number: (717)302-9966
Fax: (717) 302-9826
[EMAIL PROTECTED]




Andrew Raibeck [EMAIL PROTECTED]
Sent by: ADSM: Dist Stor Manager ADSM-L@VM.MARIST.EDU
10/05/2006 01:03 PM
Please respond to
ADSM: Dist Stor Manager ADSM-L@VM.MARIST.EDU


To
ADSM-L@VM.MARIST.EDU
cc

Subject
Re: Select Statement Syntax






Remember your operator precedence: items are ANDed first, then ORed, so
without strategically placed parentheses around the ORed portion of your
SELECT statement, you will get the wrong results.

Also, why do you need to break down BACKUP_START and BACKUP_END into
comparison of the individual date and time components?

Andy Raibeck
IBM Software Group
Tivoli Storage Manager Client Development
Internal Notes e-mail: Andrew Raibeck/Tucson/[EMAIL PROTECTED]
Internet e-mail: [EMAIL PROTECTED]

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 10/05/2006
09:48:21 AM:

 Hello Everyone,

 I finally came up with a mixture of everyone's suggestions so that I now
 have good output:

 select node_name as NODE_NAME,filespace_name as
 FILESPACE_NAME ,substr(char(backup_start),1,16) as
 backup_start,substr(char(backup_end),1,16) as backup_end from filespaces
 where node_name like 'NAS%' or node_name like 'SERVER%'

 Which gave me:

 NODE_NAME FILESPACE_NAME BACKUP_START
   BACKUP_END
 - ---
 -- --
 NAS_SERVER_2  /   2006-10-04
 19:00   2006-10-04 19:01
 NAS_SERVER_2  /.etc_common2006-10-04
 19:00   2006-10-04 21:19
 NAS_SERVER_2  /apache 2006-10-04
 19:00   2006-10-04 21:08
 NAS_SERVER_2  /clearcase_prod 2006-10-04
 19:00   2006-10-04 20:16
 NAS_SERVER_2  /clearcase_test 2006-10-04
 19:00   2006-10-04 20:03
 NAS_SERVER_2  /edi_test   2006-10-04
 19:00   2006-10-04 20:24
 NAS_SERVER_2  /home2  2006-10-04
 19:00   2006-10-04 20:28
 NAS_SERVER_2  /home2/home2dir12006-10-04
 19:00   2006-10-04 21:02
 NAS_SERVER_2  /home2/home2dir22006-10-04
 19:00   2006-10-04 21:00
 NAS_SERVER_2  /home2/home2dir32006-10-04
 19:00   2006-10-04 21:17
 NAS_SERVER_2  /home2/home2dir42006-10-04
 19:00   2006-10-04 21:17
 NAS_SERVER_2  /nas_mp22006-10-04
 19:00   2006-10-04 20:23

 My next challenge is to figure out how to find any filespaces where the
 backup_start date/time is  the backup_end date/time.  It is hard for me
 to find failed NDMP backups and this is one of the ways in which I can
see
 which filespaces failed/missed.  I have tried the following statement:

 select node_name as NODE_NAME,filespace_name as
 FILESPACE_NAME ,substr(char(backup_start),1,16) as
 backup_start,substr(char(backup_end),1,16) as backup_end from filespaces
 where node_name like 'NAS%' or node_name like 'SERVER%' and
 date(BACKUP_START)=date(BACKUP_END) and
 time(BACKUP_START)=time(BACKUP_END)

 But this gives me incorrect output.  Does anyone know what I am doing
 wrong?  I've even just tried the and backup_start  backup_end and I
 still get incorrect data.  Any ideas?

 Thanks again

Re: Select Statement Syntax

[Andrew Raibeck]

Hi Joni,

What I am driving at is, why not just do this:

  where (node_name like 'NAS%' or node_name like 'SERVER%') and \
BACKUP_START=BACKUP_END

instead of this, which seems labored:

  where (node_name like 'NAS%' or node_name like 'SERVER%') and \
date(BACKUP_START)=date(BACKUP_END) and \
time(BACKUP_START)=time(BACKUP_END)

You also might want to do a test for null, so maybe:

  where (node_name like 'NAS%' or node_name like 'SERVER%') and \
  (BACKUP_START is not null and BACKUP_END is null or \
   BACKUP_START=BACKUP_END)

Note that the above assumes that the backup was attempted but failed,
versus the backup never being attempted. To include the latter, you can
just reduce the above where cause slightly:

  where (node_name like 'NAS%' or node_name like 'SERVER%') and \
  (BACKUP_END is null or BACKUP_START=BACKUP_END)

For your other query, you can do date differences like this:

select ... where backup_end  current_timestamp - 5 days

For example (and I leave it to you to pad the column names with blanks):

select node_name, \
   filespace_name, \
   backup_end \
  from filespaces \
  where backup_end is null or \
backup_end  current_timestamp - 5 days

Regards,

Andy

Andy Raibeck
IBM Software Group
Tivoli Storage Manager Client Development
Internal Notes e-mail: Andrew Raibeck/Tucson/[EMAIL PROTECTED]
Internet e-mail: [EMAIL PROTECTED]

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 10/05/2006
10:15:18 AM:

 Hi Andy,

 I pretty much want to see the following:

 NODE_NAME FILESPACE_NAME BACKUP_START
   BACKUP_END
 - ---
 -- --
 NAS_SERVER_2  /   2006-10-04
 19:00   2006-10-03 19:01
 NAS_SERVER_2  /.etc_common2006-10-04
 19:00   2006-10-03 21:19
 NAS_SERVER_2  /apache 2006-10-04
 19:00   2006-10-03 21:08

 This points me to the fact that on 10/4 a backup started, but the last
 successful backup ended on the day before, so something must've gone
 wrong...

 I've been really struggling with getting a good report for my NAS NDMP
 backups.  I tried a long time ago to join 2 tables so that I received a
 good report, but it would never complete and always timed out.

 I'd also like to be able to get a listing of any filesystems that
haven't
 backed up in over 5 days, but that's another battle.

 Thanks again!

 
 Joni Moyer
 Highmark
 Storage Systems, Senior Systems Programmer
 Phone Number: (717)302-9966
 Fax: (717) 302-9826
 [EMAIL PROTECTED]
 



 Andrew Raibeck [EMAIL PROTECTED]
 Sent by: ADSM: Dist Stor Manager ADSM-L@VM.MARIST.EDU
 10/05/2006 01:03 PM
 Please respond to
 ADSM: Dist Stor Manager ADSM-L@VM.MARIST.EDU


 To
 ADSM-L@VM.MARIST.EDU
 cc

 Subject
 Re: Select Statement Syntax






 Remember your operator precedence: items are ANDed first, then ORed, so
 without strategically placed parentheses around the ORed portion of your
 SELECT statement, you will get the wrong results.

 Also, why do you need to break down BACKUP_START and BACKUP_END into
 comparison of the individual date and time components?

 Andy Raibeck
 IBM Software Group
 Tivoli Storage Manager Client Development
 Internal Notes e-mail: Andrew Raibeck/Tucson/[EMAIL PROTECTED]
 Internet e-mail: [EMAIL PROTECTED]

 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 10/05/2006
 09:48:21 AM:

  Hello Everyone,
 
  I finally came up with a mixture of everyone's suggestions so that I
now
  have good output:
 
  select node_name as NODE_NAME,filespace_name as
  FILESPACE_NAME ,substr(char(backup_start),1,16) as
  backup_start,substr(char(backup_end),1,16) as backup_end from
filespaces
  where node_name like 'NAS%' or node_name like 'SERVER%'
 
  Which gave me:
 
  NODE_NAME FILESPACE_NAME BACKUP_START
BACKUP_END
  - ---
  -- --
  NAS_SERVER_2  / 2006-10-04
  19:00   2006-10-04 19:01
  NAS_SERVER_2  /.etc_common 2006-10-04
  19:00   2006-10-04 21:19
  NAS_SERVER_2  /apache 2006-10-04
  19:00   2006-10-04 21:08
  NAS_SERVER_2  /clearcase_prod 2006

Re: Select statement to find what tape a particular file is on...

[Orville Lantto]

The onsite tape is probably is not readable.  Check to see if you have any 
volumes where access=unavailable.
 
Orville L. Lantto
Glasshouse Technologies, Inc.
 


From: ADSM: Dist Stor Manager on behalf of Joni Moyer
Sent: Wed 4/19/2006 1:52 PM
To: ADSM-L@VM.MARIST.EDU
Subject: [ADSM-L] Select statement to find what tape a particular file is on...



How would I be able to tell what onsite tape the following file is located
on?  /n03/data/penv/merc/restored/V110260.1.65s13m01.mi.16315970.Z

Someone is trying to restore this file and it keeps trying to go to the
offsite tape, but I do not see any reference to an onsite tape within the
activity log, so I thought I would try to figure out what tape it is on...
Any ideas?

Thanks in advance!


Joni Moyer
Highmark
Storage Systems, Senior Systems Programmer
Phone Number: (717)302-9966
Fax: (717) 302-9826
[EMAIL PROTECTED]

Re: Select statement to find what tape a particular file is on...

[Dennis, Melburn W IT743]

First you need to find out what filespace it is assigned to.  If you
don't already know the filespace that it came from, this can be
identified by running the following select statement in the
administrative command line:

select filespace_name,hl_name,ll_name where node_name='NAME_OF_NODE' and
ll_name='V110260.1.65S13M01.MI.16315970.Z'

This will identify the filespace name for you.  Make sure you use all
CAPS for the node_name as well as ll_name fields.  Next take that
filespace name and run the following select statement:

select filespace_name,volume_name from volumeusage where
node_name='NAME_OF_NODE' and filespace_name='NAME_OF_FILESPACE'

This will identify both onsite and offsite volumes that are assigned to
that filespace in particular.  Once again use all CAPS for boths fields.
Hope this helps.


Mel Dennis
Systems Engineer
Siemens Power Generation
Email:  [EMAIL PROTECTED]


-Original Message-
From: ADSM: Dist Stor Manager [mailto:[EMAIL PROTECTED] On Behalf Of
Joni Moyer
Sent: Wednesday, April 19, 2006 2:52 PM
To: ADSM-L@VM.MARIST.EDU
Subject: [ADSM-L] Select statement to find what tape a particular file
is on...

How would I be able to tell what onsite tape the following file is
located
on?  /n03/data/penv/merc/restored/V110260.1.65s13m01.mi.16315970.Z

Someone is trying to restore this file and it keeps trying to go to the
offsite tape, but I do not see any reference to an onsite tape within
the
activity log, so I thought I would try to figure out what tape it is
on...
Any ideas?

Thanks in advance!


Joni Moyer
Highmark
Storage Systems, Senior Systems Programmer
Phone Number: (717)302-9966
Fax: (717) 302-9826
[EMAIL PROTECTED]

Re: Select statement to find what tape a particular file is on...

[Andrew Raibeck]

Joni,

Have a look at the CONTENTS table, as that includes volume names, node
names and file names.

Another approach that I'd suggest you follow first: check for any volumes
or storage pools with access other than readwrite or readonly; any random
access disk storage pool volumes that are offline; and any volumes where
the error state is YES.

Regards,

Andy

Andy Raibeck
IBM Software Group
Tivoli Storage Manager Client Development
Internal Notes e-mail: Andrew Raibeck/Tucson/[EMAIL PROTECTED]
Internet e-mail: [EMAIL PROTECTED]

IBM Tivoli Storage Manager support web page:
http://www-306.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 04/19/2006
11:52:24:

 How would I be able to tell what onsite tape the following file is
located
 on?  /n03/data/penv/merc/restored/V110260.1.65s13m01.mi.16315970.Z

 Someone is trying to restore this file and it keeps trying to go to the
 offsite tape, but I do not see any reference to an onsite tape within
the
 activity log, so I thought I would try to figure out what tape it is
on...
 Any ideas?

 Thanks in advance!

 
 Joni Moyer
 Highmark
 Storage Systems, Senior Systems Programmer
 Phone Number: (717)302-9966
 Fax: (717) 302-9826
 [EMAIL PROTECTED]
 

Re: select statement for Archives

[Richard Sims]

On Jan 6, 2006, at 3:10 AM, De Keyser Bart wrote:


...
Within the retrieve module of the Backup-Archive Gui, you can view a
list of all available archives.. so it should be possible to generate
this list with a select, no?? ...


Yes, but fishing expeditions are very expensive in the TSM database,
no matter how they are conducted. You need to restrict your search to
limited areas so as to go after a reasonable number of items in each
request.

You can pursue the Select per the TSM doc, the TSM 5.1 Technical
Guide redbook appendix on SQL, and the available fields reported via
'SELECT * FROM SYSCAT.COLUMNS'. In your case, do: SELECT * FROM
SYSCAT.COLUMNS WHERE TABNAME='ARCHIVES' to see the column names you
can operate on.

  Richard Sims

Re: select statement for Archives

[Prather, Wanda]

When you do the SELECT that Richard suggests on SYSCAT.COLUMNS, you will
see that some columns have the field INDEX-KEYSEQ.
This field indicates how the ARCHIVES table is indexed.  

Like most data bases, if you use the index, the query on the TSM data
base runs much faster.
There are 4 indices on the ARCHIVES table:

The first index is on NODE_NAME, then FILESPACE_NAME, then FILESPACE_ID,
then TYPE.

So the first thing I would suggest is to try

SELECT DESCRIPTION FROM ARCHIVES WHERE NODE_NAME='PICKANODE'

If your query works fine that way and does not time out, pursue queries
using the NODE_NAME index.

To see how many different node_names have archives:

select distinct node_name from archives

If you don't have many different nodes with archives, you can just take
the resulting list of node_names, and run your SELECT for description
one node at a time, save the results in a file.

If there are too many, what I have done in the past is write a perl
script that selects the list of node names, then processes through the
list doing the selects one node_name at a time.  Because including the
WHERE on node_name uses the index, you still get done much faster than
if you do just 1 select without the index.

Wanda Prather
I/O, I/O, It's all about I/O  -(me)
  



-Original Message-
From: ADSM: Dist Stor Manager [mailto:[EMAIL PROTECTED] On Behalf Of
Richard Sims
Sent: Friday, January 06, 2006 7:56 AM
To: ADSM-L@VM.MARIST.EDU
Subject: Re: select statement for Archives


On Jan 6, 2006, at 3:10 AM, De Keyser Bart wrote:

 ...
 Within the retrieve module of the Backup-Archive Gui, you can view a
 list of all available archives.. so it should be possible to generate
 this list with a select, no?? ...

Yes, but fishing expeditions are very expensive in the TSM database,
no matter how they are conducted. You need to restrict your search to
limited areas so as to go after a reasonable number of items in each
request.

You can pursue the Select per the TSM doc, the TSM 5.1 Technical
Guide redbook appendix on SQL, and the available fields reported via
'SELECT * FROM SYSCAT.COLUMNS'. In your case, do: SELECT * FROM
SYSCAT.COLUMNS WHERE TABNAME='ARCHIVES' to see the column names you
can operate on.

   Richard Sims

Re: select statement for Archives

[John Monahan]

Try running this one (I can't remember if this is long running or not and I
don't have TSM DB available to me right now):

select distinct date (archive_date) as date, node_name, filespace_name,
description from archives


__
John Monahan
Senior Consultant Enterprise Solutions
Computech Resources, Inc.
Office: 952-833-0930 ext 109
Cell: 952-221-6938
http://www.computechresources.com




 De Keyser Bart
 [EMAIL PROTECTED]
 L.BE  To
 Sent by: ADSM:   ADSM-L@VM.MARIST.EDU
 Dist Stor  cc
 Manager
 [EMAIL PROTECTED] Subject
 .EDU select statement for Archives


 01/06/2006 02:10
 AM


 Please respond to
 ADSM: Dist Stor
 Manager
 [EMAIL PROTECTED]
   .EDU






Hi there,



We're trying to get an overview of all the archives we've already taken,
sorted on their annotation.



When we try the select description from archives statement, we get one
line per file within the archive...

Since we've got archives with about 30.000 files, our server falls on
timeouts and often no returns are given.



Within the retrieve module of the Backup-Archive Gui, you can view a
list of all available archives.. so it should be possible to generate
this list with a select, no??

Does anybody have an idea??



Thanks



This e-mail and any attachments may contain confidential and
privileged information. If you are not the intended
recipient,
please notify the sender immediately by return e-mail,
delete
this
e-mail and destroy any copies. Any dissemination or use of
this
information by a person other than the intended recipient is
unauthorized and may be illegal.

Re: Select Statement syntax?

[Andrew Raibeck]

Hi Joni,

The timestamp format is:

   '-mm-dd hh:mm:ss.nn'

where:

    = year
   mm = month
  dd = day
  hh = hours
  mm = minutes
  ss = seconds (optional)
  nn = fraction of a second (optional)

When referring to a timestamp, put it in single quotes and use the above 
format, i.e.:

   '2005-08-19 12:00:00'
  or
   '2005-08-19 12:00'

When comparing the date part, you can use:

   '2005-08-19'

When comparing the time part, you can use:

   '12:00:00'
  or
   '12:00'

(note that the fractions of a second are unnecessary and not required.)

So using your example, you could say:

   date(start_time) = '2005-08-19' and time(start_time) = '12:00'

But break this up into two tests when one will do?

   start_time = '2005-08-19 12:00'

Regards,

Andy

Andy Raibeck
IBM Software Group
Tivoli Storage Manager Client Development
Internal Notes e-mail: Andrew Raibeck/Tucson/[EMAIL PROTECTED]
Internet e-mail: [EMAIL PROTECTED]

The only dumb question is the one that goes unasked.
The command line is your friend.
Good enough is the enemy of excellence.

ADSM: Dist Stor Manager ADSM-L@VM.MARIST.EDU wrote on 2005-08-15 
09:49:17:

 
 Hello Everyone!
 
   I have this script that I run once/day, but we are moving servers 
and
   I would like to run this every 15 min. and have a starting point 
of
   Friday, 8/19 at noon.  How would I change the start_date and
   start_time parameters so that it would look at my specific 
criteria?
   Any help is appreciated.  Thanks!
 
   Current
   select left(entity,10) as node_name,date(start_time) as
   date,cast(activity as varchar(8)) as activity,time(start_time) as
   start,time(end_time) as end,cast(substr(cast(end_time-start_time 
as
   char(20)),3,8) as char(8)) as ?Duration?,cast(bytes/1024/1024 as
   decimal(6,0)) as mb,cast(affected as decimal(7,0)) as
   files,cast(successful as varchar(3)) as success from summary where
   start_time=current_timestamp-1 day and activity=?BACKUP? and
   schedule_name like ?DCMOVE_0819%? order by success,node_name
 
   Future
   select left(entity,10) as node_name,date(start_time) as
   date,cast(activity as varchar(8)) as activity,time(start_time) as
   start,time(end_time) as end,cast(substr(cast(end_time-start_time 
as
   char(20)),3,8) as char(8)) as ?Duration?,cast(bytes/1024/1024 as
   decimal(6,0)) as mb,cast(affected as decimal(7,0)) as
   files,cast(successful as varchar(3)) as success from summary where
   date(start_time)=08/19/2005 and time(start_time)=12:00 and
   activity=?BACKUP? and schedule_name like ?DCMOVE_0819%? order by
   success,node_name
 
 
 Joni Moyer
 Highmark
 Storage Systems
 Work:(717)302-6603
 Fax:(717)302-5974
 [EMAIL PROTECTED]
 

Re: Select statement syntax

[Sung Y Lee]

This command looks very familiar.

Here's ya go.  try this

select with duration

select left(entity,10) as node_name, date(start_time) as date,-
cast(activity as varchar(8)) as activity, time(start_time) as start, -
time(end_time) as end,cast(bytes/1024/1024 as decimal(6,0)) as mb, -
cast(substr(cast(end_time-start_time as char(20)),3,8) as char(8)) as
duration, -
cast(affected as decimal(7,0)) as files,cast(successful as varchar(3)) as -
success from summary where start_time=current_timestamp-1 day and -
activity='BACKUP' order by node_name

Sung Y. Lee

ADSM: Dist Stor Manager ADSM-L@VM.MARIST.EDU wrote on 07/21/2005
07:18:24 AM:

 Hello Everyone!

 I have the following select statement and I was wondering how to go about
 getting the duration of the backup?  I know that it would be the end_time
-
 start_time, but my syntax must be wrong because it just won't work.  Any
 help would be appreciated!

 select left(entity,10) as node_name, date(start_time) as date,
 cast(activity as varchar(8)) as activity, time(start_time) as start,
 time(end_time) as end,cast(bytes/1024/1024 as decimal(6,0)) as mb,
 cast(affected as decimal(7,0)) as files,cast(successful as varchar(3)) as
 success from summary where start_time=current_timestamp-1 day and
 activity='BACKUP' order by node_name

 
 Joni Moyer
 Highmark
 Storage Systems
 Work:(717)302-6603
 Fax:(717)302-5974
 [EMAIL PROTECTED]
 

Re: Select Statement Used for TSM Operational reporting

[William]

great,thanks.

On 6/23/05, Sung Y Lee [EMAIL PROTECTED] wrote:
 After spending pretty good deal of time trying to gather all the select
 statements used by TSM Operational reporting.. said to myself.. you know
 there must be a better way to gather this information.. I browsed under
 c:\progra~1\tivoli\tsm\console\  and I found files with *.in extension.
 Sure enough this file contains the select statements used generate the
 report.  I hope this information will be helpful to some of you who
 collects or works with vast maze of select statements.
 
 Thanks,
 
 Sung Y. Lee