Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2009
    Posts
    8

    Unhappy Unanswered: Issue with user search in DB2

    I am using DB2v9.1.0.356 . I am trying to retrieve the DB2 users whose name ends with "R" , using the following query.

    SELECT DISTINCT GRANTEE FROM SYSIBM.SYSDBAUTH where GRANTEE like '%R' or GRANTEE = '%R'

    This query returns only those names which has more than eight characters; like ADMINISTRATOR , DAVIDROGER etc , but doesn't returns user names having less than eight characters like USER , ROGER.

    But if query the DB to retrieve users starting with R using the following query it returns all matching results , irrespective of number of characters.

    SELECT DISTINCT GRANTEE FROM SYSIBM.SYSDBAUTH where GRANTEE like 'R%' or GRANTEE = 'R%'
    It returns users like ROGER , REYNOLD etc.

    Please advise!

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    SELECT DISTINCT GRANTEE FROM SYSIBM.SYSDBAUTH where RTRIM(GRANTEE) like '%R';

    1) GRANTEE might be stored with trailing blanks.
    2) If GRANTEE was '%R', it will match with pattern '%R'. Because '%' matches any character(s) including '%' or no character.
    Last edited by tonkuma; 04-25-09 at 13:08.

  3. #3
    Join Date
    Apr 2009
    Posts
    8
    It works! Thanks a lot. So does DB2 add trailing blanks for entries shorter than eight characters ?

  4. #4
    Join Date
    Jun 2009
    Posts
    6
    Quote Originally Posted by tonkuma
    SELECT DISTINCT GRANTEE FROM SYSIBM.SYSDBAUTH where RTRIM(GRANTEE) like '%R';

    1) GRANTEE might be stored with trailing blanks.
    2) If GRANTEE was '%R', it will match with pattern '%R'. Because '%' matches any character(s) including '%' or no character.
    I hope it is OK to tack a question onto your answer here.

    How can modify the LIKE '%R' predicate so that R is actually a host (client side) variable? I'm not having any luck concatenating the parameter marker with the wild card.

    Thanks.

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by SparTodd
    I hope it is OK to tack a question onto your answer here.

    How can modify the LIKE '%R' predicate so that R is actually a host (client side) variable? I'm not having any luck concatenating the parameter marker with the wild card.

    Thanks.
    No, it is not a good idea ot add on to this thread. Please open a new thread and include your OS. DB2 version level, type of programming language, and the exact statement you are trying to submit to DB2 (if you have already tried one), and any error messages you have received.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Jun 2009
    Posts
    6
    Quote Originally Posted by Marcus_A
    No, it is not a good idea ot add on to this thread. Please open a new thread and include your OS. DB2 version level, type of programming language, and the exact statement you are trying to submit to DB2 (if you have already tried one), and any error messages you have received.
    Gotcha, and thanks for the feedback. I resolved my problem, but I'll remember your advice next time.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •