SQL statement

Hi TSM-ers!
I'm trying to create a SQL query to check whether all Oracle files
(Oracle databases are backed up by the TDP client) are excluded from the
standard BA client backup.
Our database admins recommends the follow exclude statements to be added
to the BA client:

So I want to check if the BA client repository does not contain these
files. Thus far I created this query, but it's not correct:

select node_name, filespace_name, hl_name, ll_name from backups where
(upper(ll_name) like '%LOG%' or upper(ll_name) like '%ARCHIVE%' or
upper(ll_name) like '%DBDATA%' or upper(ll_name) like '%DBINDEX%' or
upper(ll_name) like '%DUMP%' or upper(ll_name) like '%AUDIT%') and
upper(hl_name) like '%ORA_`%' and node_name='KL100AT0'

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

File system directory and file names may well contain underscore (_) characters, which is fine. What may not be realized, however, is that in a Select LIKE, an underscore is a wildcard for any single character.

An example of using this:
select NODE_NAME, PLATFORM_NAME from NODES where upper(PLATFORM_NAME) like 'LINUX__'
to report any Linux86 clients, versus LinuxPPC.

Unfortunately, TSM's Select does not conform to the convention that the backslash char () be an escape, to turn off the special meaning of the underscore; but you can define it to be the escape char.

By example...

You have a storage pool named STGP_ARCHIVE_3592
which can be verified by reporting via
select STGPOOL_NAME from STGPOOLS where STGPOOL_NAME='STGP_ARCHIVE_3592'

Now try to 'escape' the underscore in a LIKE:

select STGPOOL_NAME from STGPOOLS where STGPOOL_NAME like 'STGP_ARCHIVE_3592'
ANR2034E SELECT: No match found using this criteria.

So instead do:
select STGPOOL_NAME from STGPOOLS where STGPOOL_NAME like 'STGP_ARCHIVE_3592' ESCAPE ''
and it will be reported.

Life is seldom simple.

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

On Jun 4, 2010, at 7:31 AM, Loon, EJ van - SPLXM wrote:

[quote]Hi TSM-ers!
I'm trying to create a SQL query to check whether all Oracle files
(Oracle databases are backed up by the TDP client) are excluded from the
standard BA client backup.
Our database admins recommends the follow exclude statements to be added
to the BA client:

So I want to check if the BA client repository does not contain these
files. Thus far I created this query, but it's not correct:

select node_name, filespace_name, hl_name, ll_name from backups where
(upper(ll_name) like '%LOG%' or upper(ll_name) like '%ARCHIVE%' or
upper(ll_name) like '%DBDATA%' or upper(ll_name) like '%DBINDEX%' or
upper(ll_name) like '%DUMP%' or upper(ll_name) like '%AUDIT%') and
upper(hl_name) like '%ORA_`%' and node_name='KL100AT0'

Could some SQL wizard please help me out here?
Thank you VERY much in advance!!!
Kind regards,
Eric van Loon
KLM Royal Dutch Airlines
</pre>********************************************************<br>For information, services and offers, please visit our web site: http://www.klm.com. This e-mail and any attachment may contain confidential and privileged material intended for the addressee only. If you are not the addressee, you are notified that no part of the e-mail or any attachment may be disclosed, copied or distributed, and that any other action related to this e-mail or attachment is strictly prohibited, and may be unlawful. If you have received this e-mail by error, please notify the sender immediately by return e-mail, and delete this message.<br><br>Koninklijke Luchtvaart Maatschappij NV (KLM), its subsidiaries and/or its employees shall not be liable for the incorrect or incomplete transmission of this e-mail or any attachments, nor responsible for any delay in receipt.<br>Koninklijke Luchtvaart Maatschappij N.V. (also known as KLM Royal Dutch Airlines) is registered in Amstelveen, The Netherlands, with registered number 3014286 <br>********************************************************<pre>[/quote]

Wow... Richard, respect!
This one works, you again saved my day!!!!
Here is the final working statement, so it's 'logged' on the ADSM-L
list:

select node_name, filespace_name, hl_name, ll_name from backups where
(upper(ll_name) like '%LOG%' or upper(ll_name) like '%ARCHIVE%' or
upper(ll_name) like '%DBDATA%' or upper(ll_name) like '%DBINDEX%' or
upper(ll_name) like '%DUMP%' or upper(ll_name) like '%AUDIT%') and
upper(hl_name) like '%ORA_%' ESCAPE
'' and node_name='KL100AT0'

[quote]Hi Richard!
Thanks, but still not what I expect, an example of one of the many
[/quote]files
[quote]returned:

KL100AT0,/home,/oracle/xt03js/,statspack_a064.log

Since I use upper(hl_name) like '%ORA_%' I would not expect a
/oracle/xt03js/ in the list...
[/quote]
No, you wouldn't. But... :-)
Your thoughts are keyed on dealing with your site's directory name being
like mount/data*/ora_*
where the architects decided to have an underscore in the name, which is
fine.
But you're now being defeated by that underscore because, in a Select
LIKE, an underscore is a wildcard for any single character!

An example of using this:
select NODE_NAME, PLATFORM_NAME from NODES where upper(PLATFORM_NAME)
like 'LINUX__'
to report any Linux86 clients, versus LinuxPPC.

Unfortunately, TSM's Select does not conform to the standard that the
backslash char be an escape, to turn off the special meaning of the
underscore as in like '%ORA_%'
but, fortunately, it allows you to define an escape character, as in
like '%ORA_%' ESCAPE ''
So, mull that over, and give it a try.

Life is seldom simple.

R.

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

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

File system directory and file names may well contain underscore (_)
characters, which is fine. What may not be realized, however, is that
in a Select LIKE, an underscore is a wildcard for any single character.

An example of using this:
select NODE_NAME, PLATFORM_NAME from NODES where upper(PLATFORM_NAME)
like 'LINUX__'
to report any Linux86 clients, versus LinuxPPC.

Unfortunately, TSM's Select does not conform to the convention that the
backslash char () be an escape, to turn off the special meaning of the
underscore; but you can define it to be the escape char.

By example...

You have a storage pool named STGP_ARCHIVE_3592
which can be verified by reporting via
select STGPOOL_NAME from STGPOOLS where
STGPOOL_NAME='STGP_ARCHIVE_3592'

Now try to 'escape' the underscore in a LIKE:

select STGPOOL_NAME from STGPOOLS where STGPOOL_NAME like
'STGP_ARCHIVE_3592'
ANR2034E SELECT: No match found using this criteria.

So instead do:
select STGPOOL_NAME from STGPOOLS where STGPOOL_NAME like
'STGP_ARCHIVE_3592' ESCAPE ''
and it will be reported.

So I want to check if the BA client repository does not contain these
files. Thus far I created this query, but it's not correct:

select node_name, filespace_name, hl_name, ll_name from backups where
(upper(ll_name) like '%LOG%' or upper(ll_name) like '%ARCHIVE%' or
upper(ll_name) like '%DBDATA%' or upper(ll_name) like '%DBINDEX%' or
upper(ll_name) like '%DUMP%' or upper(ll_name) like '%AUDIT%') and
upper(hl_name) like '%ORA_`%' and node_name='KL100AT0'

Could some SQL wizard please help me out here?
Thank you VERY much in advance!!!
Kind regards,
Eric van Loon
KLM Royal Dutch Airlines
</pre>********************************************************<br>For
[/quote]information, services and offers, please visit our web site:
http://www.klm.com. This e-mail and any attachment may contain
confidential and privileged material intended for the addressee only. If
you are not the addressee, you are notified that no part of the e-mail
or any attachment may be disclosed, copied or distributed, and that any
other action related to this e-mail or attachment is strictly
prohibited, and may be unlawful. If you have received this e-mail by
error, please notify the sender immediately by return e-mail, and delete
this message.<br><br>Koninklijke Luchtvaart Maatschappij NV (KLM), its
subsidiaries and/or its employees shall not be liable for the incorrect
or incomplete transmission of this e-mail or any attachments, nor
responsible for any delay in receipt.<br>Koninklijke Luchtvaart
Maatschappij N.V. (also known as KLM Royal Dutch Airlines) is registered
in Amstelveen, The Netherlands, with registered number 3014286
<br>********************************************************<pre>

[quote]Ow.
Richard, do you know if it works this way still in V6?
[/quote]
Hi, Wanda -

We remain a v5 shop, pending a lot of infrastructure decisions to be made by a new VP, so I haven't that experience. I would expect DB2 to be much more conformant to SQL standards than the Select accommodations in "TSM Classic". Perhaps a customer with v6 can lend to this.

[quote]Ow.
Richard, do you know if it works this way still in V6?
[/quote]
Hi, Wanda -

We remain a v5 shop, pending a lot of infrastructure decisions to be
made by a new VP, so I haven't that experience. I would expect DB2 to
be much more conformant to SQL standards than the Select accommodations
in "TSM Classic". Perhaps a customer with v6 can lend to this.

Richard

The information contained in this transmission may contain privileged and confidential information.
It is intended only for the use of the person(s) named above. If you are not the intended
recipient, you are hereby notified that any review, dissemination, distribution or
duplication of this communication is strictly prohibited. If you are not the intended recipient,
please contact the sender by reply email and destroy all copies of the original message.
To reply to our email administrator directly, please send an email to postmaster < at > sbsplanet.com.

[quote]Ow.
Richard, do you know if it works this way still in V6?
[/quote]
Hi, Wanda -

We remain a v5 shop, pending a lot of infrastructure decisions to be
made by a new VP, so I haven't that experience. I would expect DB2 to
be much more conformant to SQL standards than the Select accommodations
in "TSM Classic". Perhaps a customer with v6 can lend to this.

Richard

The information contained in this transmission may contain privileged and confidential information.
It is intended only for the use of the person(s) named above. If you are not the intended
recipient, you are hereby notified that any review, dissemination, distribution or
duplication of this communication is strictly prohibited. If you are not the intended recipient,
please contact the sender by reply email and destroy all copies of the original message.
To reply to our email administrator directly, please send an email to postmaster < at > sbsplanet.com.

[quote]Ow.
Richard, do you know if it works this way still in V6?
[/quote]
Hi, Wanda -

We remain a v5 shop, pending a lot of infrastructure decisions to be
made by a new VP, so I haven't that experience. I would expect DB2 to
be much more conformant to SQL standards than the Select accommodations
in "TSM Classic". Perhaps a customer with v6 can lend to this.

Richard

The information contained in this transmission may contain privileged
and confidential information.
It is intended only for the use of the person(s) named above. If you are
not the intended
recipient, you are hereby notified that any review, dissemination,
distribution or
duplication of this communication is strictly prohibited. If you are not
the intended recipient,
please contact the sender by reply email and destroy all copies of the
original message.
To reply to our email administrator directly, please send an email to
postmaster < at > sbsplanet.com.

[quote]Could anyone provide me a sql statement which can check if any copy
storage pool volume is mounted in tape drive at the moment? I need
this to configure script for my DRP.
[/quote]
select volume_name from drives where volune_name in
(select volume_name from stgpools where stgpool_name in
(select stgpool_name from stgpools where pooltype='COPY'))

I am not at all sure this will work if your TSM server runs under
z/OS or one of its ancesters; z/OS handles a lot of the tape and
library support that is built into TSM on other platforms.

There seem to be some timing issues in TSM reporting of tape
status. We used to have an automation script that detected the
dismounting of the last copy pool tape by executing a 'query
mount' command each time a message reporting that a tape had
been dismounted appeared in the consoled message stream. The
'query mount' command would routinely report a tape volume as
still mounted for a fraction of a second after TSM displayed
a message reporting that the volume had been dismounted. We
had to put a 'sleep' command in the script to delay execution
of 'query mount' by one second.