Results 1 to 14 of 14
  1. #1
    Join Date
    Jan 2009
    Posts
    4

    Unanswered: Find within a table

    I'm looking for a query to search every field of a specified table for a certain character. In this case I'm looking for the euro sign (). Any help on this is much appreciated.

    Thank you,

    Keith

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Try the LOCATE function. Or you could use the LIKE expression.

    Andy

  3. #3
    Join Date
    Jan 2009
    Posts
    4
    Thank you for the quick response.

    I'm really looking for something to check EVERY column within a table. How would I do that with LIKE or LOCATE.

    Thanks,

    Keith

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    select * from mytable where col1 like '%$%' or col2 like '%$%' ...

    Andy

  5. #5
    Join Date
    Jan 2009
    Posts
    4
    Yes, Thank you. I was just wondering if there is a method to query each column in the table without specifically naming each one.

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Certainly. You could query SYSCAT.COLUMNS, then construct and execute a dynamic SQL statement that takes into account each column's datatype.
    ---
    "It does not work" is not a valid problem statement.

  7. #7
    Join Date
    Jan 2009
    Posts
    4
    That's what I was looking for! Thank you!

  8. #8
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    This should generate the SELECT Statements you need:
    Code:
    with tab(tsch,tname) as 
    (
    -- Select only the required tables or list them 
    --values ('SYSCAT','SEQUENCES') ,('SYSCAT','TABLES')
    select tabschema,tabname from syscat.tables where type='T'
    ),
    star(ts,tn,cno,concatcol,dtype) as 
    (
    select tsch,tname,-1,varchar('''A''=''B''',5000),'' from sysibm.sysdummy1,tab
    union all
    select tsch,tname,colno,concatcol||case when typename in 
    -- include other character data types 
    ('CHARACTER','VARCHAR','CLOB','LONG VARCHAR')
     then ' OR LOCATE('''','||colname||') > 0 ' else '' end ,
    typename  from star s,syscat.columns c, tab where c.colno=cno+1 and tabname=tname and tabschema=tsch and 
      s.ts=c.tabschema and s.tn=c.tabname  )
      select rtrim('select * from '||rtrim(ts)||'.'||rtrim(tn)||' where ')||concatcol
    from star so
    where cno=(select max(cno) from star si where si.ts=so.ts and si.tn=so.tn )
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  9. #9
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Sathyaram,

    You should put this in the Script Library ;-)
    Maybe I'll add the variant for DB2 on z/OS there.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  10. #10
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    What "codeset" do you use? I've had trouble with the euro sign when converting databases. If I recall it right then the "1208" codeset does not have the euro sign at all, but the windows client just puts the euro sign in code "1252" in there and that is represented correctly when you retrieve that data again..... But in the database this is not recognized! The binary representation in "1208" is meaningless. So you can have errors in your database for ever without noticing it.

  11. #11
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by dr_te_z
    What "codeset" do you use? I've had trouble with the euro sign when converting databases. If I recall it right then the "1208" codeset does not have the euro sign at all
    "1208" certainly has the euro sign: it is UTF-8, one of the encodings that is able to represent the full Unicode character set.
    Beware that UTF-8 is a variable length encoding, i.e., some characters (including the euro sign) are represented with more than one byte.
    Most likely your interface (graphical interface, editor, ...) either didn't recognise that the data is UTF-8 encoded, so it will show single bytes as single characters and the euro sign comes out as 3 bytes. Or the interface does not support the full UTF-8 range (maybe just the 1-byte and 2-byte characters); or maybe the GUI does not have an euro sign in its graphical font.

    What exactly did you do and what exactly are you seeing, and in which environment?
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  12. #12
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by Peter.Vanroose
    "1208" certainly has the euro sign: it is UTF-8,
    Peter, do not confuse codeset and codepage
    Old: Windows 2003-32 bit DB2 V8.2
    New: Windows 2003-64 bit DB2 V9.5
    I made scritps to export the data into *.ixf files (so the data was translated from 1208 to 1252 by the DB2CMD CLI) and load those files in the new database (translated back from 1252 to 1208). In this process our EUR sign got corrupted (in CLOB columns).
    It seemed that the 1208-CLOB columns were populated with 1252 data. This did not survive the translations.

    char 0x80 (euro sign in Windows-1252) became 0x1a (Substitute character in 1208)

    This
    Code:
    db2set -g DB2CODEPAGE=1208
    did not help (made it even worse).
    We ended up cleaning up our old database
    Code:
     
    UPDATE table  SET column = REPLACE(VARGRAPHIC(column), X'80', 'EUR');
    The problem (as I see it):
    1 - codeset 1208 does not have het EUR sign
    2 - DB2-CLOB columns can be populated with (binary?) data which does not adhere to its own codeset standards.

  13. #13
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by dr_te_z
    ...load those files in the new database (translated back from 1252 to 1208)... char 0x80 (euro sign in Windows-1252) became 0x1a (Substitute character in 1208)
    OK, then the problem must be in this (back)conversion.
    Since version 8, DB2 takes the responsibility to translate a character from the codeset of the application to the codeset of the table (and vice versa), so encoding should be 100% transparant.
    Most likely, in the communication between the application sending the 1252-character and DB2, either the application failed to identify itself as speaking 1252, or DB2 erroneously doesn't know that char 0x80 is the Euro sign.
    (I don't know Windows-1252 well enough to know which of the two is wrong here.)
    I would suggest you verify the connection settings with DB2 to make sure both sides "speak the same language" (viz. Windows-1252, which IBM calls "CCSID 1252"); maybe you need "db2set -g DB2CODEPAGE=1252" for this? I'm not familiar with the "db2set -g" command, sorry.

    CCSID 1208 most certainly *has* the euro sign; it's represented as 0xE2:0x82:0xAC (so observe it's represented with three bytes!)

    P.S. for a good explanation of the difference between "codeset" and "codepage", see e.g. CCSID - Wikipedia, the free encyclopedia
    So, Unicode is a *codepage*, while UTF-8 (aka CCSID-1208) is a *codeset*.
    In the context of physical transfer of data, only the codeset is relevant.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  14. #14
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Thanks Peter. For me and my client this is all in the past now, but we've got it documented here

Posting Permissions

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