ADSM-L

Re: [ADSM-L] SQL statement

2010-06-04 10:37:33
Subject: Re: [ADSM-L] SQL statement
From: Richard Sims <rbs AT BU DOT EDU>
To: ADSM-L AT VM.MARIST DOT EDU
Date: Fri, 4 Jun 2010 10:35:52 -0400
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>