ADSM-L

Re: [ADSM-L] SQL statement

2010-06-04 10:57:55
Subject: Re: [ADSM-L] SQL statement
From: "Prather, Wanda" <wPrather AT ICFI DOT COM>
To: ADSM-L AT VM.MARIST DOT EDU
Date: Fri, 4 Jun 2010 09:41:25 -0500
Ow.
Richard, do you know if it works this way still in V6?  

-----Original Message-----
From: ADSM: Dist Stor Manager [mailto:ADSM-L AT VM.MARIST DOT EDU] On Behalf Of
Richard Sims
Sent: Friday, June 04, 2010 10:36 AM
To: ADSM-L AT VM.MARIST DOT EDU
Subject: Re: [ADSM-L] SQL statement

I worked with Eric on this.  In case anyone else runs into similar...

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

An example 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:

> 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:
> 
> /mount/data*/ora_*/log
> /mount/data*/ora_*/archive[12]
> /mount/data*/ora_*/dbdata
> /mount/data*/ora_*/dbindex 
> /mount/data*/ora_*/*dump 
> /mount/data*/ora_*/audit 
> /mount/appl00001/.../network/log/*
> 
> 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>

<Prev in Thread] Current Thread [Next in Thread>