Topic
IC4NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
7 replies Latest Post - ‏2013-02-13T11:06:14Z by Jotac
Jotac
Jotac
82 Posts
ACCEPTED ANSWER

Pinned topic extra space on TABSCHEMA field

‏2013-01-23T09:54:42Z |
I was writing a script that need to query the tables catalog using the LIKE operator,
since was not working as expected I have investigated a bit and so I have discovered a "bizzare" tingh.
On the TABSCHEMA field of syscat.tables the value have one extra SPACE added to the schema name.
Here you can see that IPROD01 exist as schema:


$ db2 
"select count(*) from iprod01.t1"   1 ----------- 512   1 record(s) selected.


This query fails because do not find IPROD01 as schema name:

$ db2 
"select count(*) from syscat.tables where tabschema like 'IPROD01'"   1 ----------- 0   1 record(s) selected.


This query work because there is one SPACE added to value....

$ db2 
"select count(*) from syscat.tables where tabschema like 'IPROD01 '"   1 ----------- 3   1 record(s) selected.


Here you can see that the there is one SPACE added to the schema name.

db2 
"select hex(tabschema), hex('IPROD01') from syscat.tables where tabschema like 'IPROD01 '"   1                   2 ------------------- -------------- 4950524F44303120    4950524F443031 4950524F44303120    4950524F443031 4950524F44303120    4950524F443031   3 record(s) selected.

How come? It's expected beavhiour?

Obviously using the equal match (=) instead of LIKE operator it work but isn't my case where I have to use the LIKE.

ciao
GIovanni
Updated on 2013-02-13T11:06:14Z at 2013-02-13T11:06:14Z by Jotac
  • Jotac
    Jotac
    82 Posts
    ACCEPTED ANSWER

    Re: extra space on TABSCHEMA field

    ‏2013-01-23T09:57:08Z  in response to Jotac
    forgot to say.... DB2/LINUXX8664 9.7.5
    • mwandishi
      mwandishi
      47 Posts
      ACCEPTED ANSWER

      Re: extra space on TABSCHEMA field

      ‏2013-01-23T11:34:24Z  in response to Jotac
      Hi

      I think it is because up to 8 chars we pad with 0x020:

      db2 "select length(tabschema) as schema_length, substr(tabschema,1,15) as tabschema, substr(tabname,1,10) as tabname, length(tabname) as tab_length from syscat.tables where tabschema like 'IPR%'"

      SCHEMA_LENGTH TABSCHEMA TABNAME TAB_LENGTH

      ---------------
      -----------
      8 IPR T1 2
      8 IPRO T1 2
      8 IPROD T1 2
      8 IPROD0 T1 2
      8 IPROD01 T1 2
      8 IPROD011 T1 2
      9 IPROD0111 T1 2
      10 IPROD01111 T1 2
      11 IPROD011111 T1 2
      12 IPROD0111111 T1 2
      13 IPROD01111111 T1 2
      14 IPROD011111111 T1 2
      15 IPROD0111111111 T1 2

      13 record(s) selected.

      db297: db2 "select hex(TABSCHEMA) from syscat.tables where tabschema like 'IPR%'"

      1
      4950522020202020
      4950524F20202020
      4950524F44202020
      4950524F44302020
      4950524F44303120
      4950524F44303131
      4950524F4430313131
      4950524F443031313131
      4950524F44303131313131
      4950524F4430313131313131
      4950524F443031313131313131
      4950524F44303131313131313131
      4950524F4430313131313131313131

      13 record(s) selected.

      An rtrim() should remove these or just specify like 'IP%'
      Best regards,Stephen Levett
      • mwandishi
        mwandishi
        47 Posts
        ACCEPTED ANSWER

        Re: extra space on TABSCHEMA field

        ‏2013-01-23T17:33:29Z  in response to mwandishi
        Hi,

        I apologise for the poor formatting in my previous post but I can't select a monospaced font. Secondly, I am not 100% sure the padding up to 8 chars in syscat.tables TABSCHEMA is the cause here. However, irrespective of this I think you should use: ''like %...%' or whatever string satisfies your requirements.

        Best regards,Stephen Levett
      • Jotac
        Jotac
        82 Posts
        ACCEPTED ANSWER

        Re: extra space on TABSCHEMA field

        ‏2013-02-13T11:06:14Z  in response to mwandishi
        thank you all,
        yes the issue come from extra space added to the schema name

        ciao
        GIovanni
  • SystemAdmin
    SystemAdmin
    17917 Posts
    ACCEPTED ANSWER

    Re: extra space on TABSCHEMA field

    ‏2013-01-23T17:56:58Z  in response to Jotac
    Try with equal predicate, like...
    
    SELECT count(*) FROM syscat.tables WHERE tabschema = 
    'IPROD01'
    


    Because, string comparison done by padding on the right with blanks to make length of both srings equal.
    DB2 Version 9.7 for LUW SQL > Language elements > Data types > Assignments and comparisons
    String comparisons
    ...
    ...

    When comparing character strings of unequal lengths, the comparison is made using a logical copy of the shorter string,
    which is padded on the right with blanks sufficient to extend its length to that of the longer string.
    This logical extension is done for all character strings, including those tagged as FOR BIT DATA.
    • SystemAdmin
      SystemAdmin
      17917 Posts
      ACCEPTED ANSWER

      Re: extra space on TABSCHEMA field

      ‏2013-01-23T18:10:19Z  in response to SystemAdmin
      I'm sorry that I forgot
      " ... but isn't my case where I have to use the LIKE."

      My first idea was
      
      SELECT count(*) FROM syscat.tables WHERE tabschema LIKE 
      'IPROD01%'
      

      But, this will reurn 'IPROD011' , 'IPROD0111' , so on...

      Another idea was
      (Not tested. Just an idea.)
      
      SELECT count(*) FROM syscat.tables WHERE tabschema LIKE 
      'IPROD01 %'
      
      • SystemAdmin
        SystemAdmin
        17917 Posts
        ACCEPTED ANSWER

        Re: extra space on TABSCHEMA field

        ‏2013-01-23T18:17:06Z  in response to SystemAdmin
        Or, more generally,
        
        SELECT count(*) FROM syscat.tables WHERE tabschema LIKE RPAD(
        'IPROD01' , 8) || 
        '%'
        


        
        SELECT count(*) FROM syscat.tables WHERE tabschema || 
        ' ' LIKE 
        'IPROD01' || 
        ' %'