SQL Query help.

[Gormag]

I am new to SQL query and need a hand.

If I run this: q act begind=-1 s=ANR1420

I obviousley get something like this,

09/18/06 15:35:44 ANR1420W Read access denied for volume QB3164 - volume access mode = "unavailable". (SESSION: 91400)
09/18/06 15:36:18 ANR1420W Read access denied for volume QB2887 - volume access mode = "unavailable". (SESSION: 91103)

The thing is, there can be thousands of lines like this, and I just want a simple query to take this data from a specific day and show me the list of tapes.

How would this be done?

 

[gsavy]

is this helpfull ?

select volume_name, access, status from volumes where access='UNAVAILABLE'

 

[Xenocide911]

also try "q vol access=unav" if you want to change the status after that "upd vol XXXXXX access=readw/reado

 

[Gormag]

I gave up on the sql query, in the end just did this.

dsmadmc -id=YOU -pass=YOURPASS "q act begind=-3 s=ANR1420" | grep "Read access denied for volume" | awk '{print $9}' | sort -u

Provides a nice short list..

 

[ProbablyDown]

Here you go:
select volume_name,stgpool_name,pct_utilized,access, write_errors,read_errors from volumes where access='UNAVAILABLE' and volume_name in (select volume_name from libvolumes)

and here for the ReadOnly tapes:
select volume_name,stgpool_name,pct_utilized,access, write_errors,read_errors from volumes where access='READONLY' order by stgpool_name