To add more (having two nodes "A_B" and "_B" defined for testing
purposes):
"select node_name from nodes where node_name like '\_%' escape '\'

NODE_NAME
------------------
_B

select node_name from nodes where node_name like '%\_%' escape '\'
ANR2034E SELECT: No match found using this criteria.
ANS8001I Return code 11.

select node_name from nodes where node_name like '%_\_%' escape '\'

NODE_NAME
------------------
A_B
"

Tested using TSM client 4.2.1.32 against servers v4.1.6/AIX &
v4.2.1.15/W2k

After check in DB2 manuals (my ultimate source for TSM SQL) there is a
statement "When escape characters are present in the pattern string, an 
underscore,
percent sign, or escape character can represent a literal occurrence of
itself. ". Using capital "A" as escape it works as with backslash:
"select node_name from nodes where node_name like 'A_%' escape 'A'

NODE_NAME
------------------
_B"

However using it to escape itself does not work:
"select node_name from nodes where node_name like 'AAA_%' escape 'A'
ANR2034E SELECT: No match found using this criteria.
ANS8001I Return code 11.
"
it ought to return "A_B". I know, I know ... this is not documented for
TSM (only for DB2) thus I cannot object it works or not. But this may
narrow the search for the root of the problem.

Zlatko Krastev
IT Consultant






Andrew Raibeck <storman AT US.IBM DOT COM>
Sent by: "ADSM: Dist Stor Manager" <ADSM-L AT VM.MARIST DOT EDU>
12.11.2002 20:36
Please respond to "ADSM: Dist Stor Manager"


        To:     ADSM-L AT VM.MARIST DOT EDU
        cc:
        Subject:        Re: not like underscore


You can use ESCAPE '<escape char>' at the end of the WHERE clause to make
this work. For example, to use '\' as the escape character:

   select node_name from nodes where node_name like '%\_%' escape '\'

Unfortunately I just discovered that this isn't working quite right. I can
use '%Y\_%' to find node names like 'ANDY_RAIBECK', but the '%\_%' pattern
produces no results for me (5.1.5.1 server version).

Oddly enough, this pattern: '%_\_%' does the trick. But that is a
circumvention only, and works only if the node name does not begin with an
underscore.

Sorry, I don't know enough about this problem to describe it better, as I
just discovered it a minute ago.

Regards,

Andy

Andy Raibeck
IBM Software Group
Tivoli Storage Manager Client Development
Internal Notes e-mail: Andrew Raibeck/Tucson/IBM@IBMUS
Internet e-mail: storman AT us.eyebm DOT com (change eye to i to reply)

The only dumb question is the one that goes unasked.
The command line is your friend.
"Good enough" is the enemy of excellence.




Kai Hintze <kai.hintze AT ALBERTSONS DOT COM>
Sent by: "ADSM: Dist Stor Manager" <ADSM-L AT VM.MARIST DOT EDU>
11/12/2002 11:15
Please respond to "ADSM: Dist Stor Manager"


        To:     ADSM-L AT VM.MARIST DOT EDU
        cc:
        Subject:        not like underscore



Oh wiseness of wizards of tsm-flavored SQL, how do you match an
underscore?

I need to find node names that do not contain an underscore ('_'). I
thought
that would be no problem, just "select node_name from nodes where
node_name
not like '%_%'", but that doesn't find anything. A bit more research
revealed that '_' is the single character match, so that expression parsed
out to something like "select node_names without any characters". Not what
I
wanted at all.

I've played with a couple of things to escape the underscore, but I
haven't
found anything that works yet. I thought I remembered reading something
about match and wildcards in this list, but all I can find in the archives
is the mention that underscore is the single character wildcard.

If I can't find an answer fairly quickly I will just dump everything to a
file and parse out the results with perl or something, but I really think
what I need to do could be done easily in SQL if I could just parse out
the
underscore.

I thank you in advance for any light you could shed on my difficulty.

- Kai.