Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2010
    Posts
    4

    Unanswered: DB2 9.5 query return weird result... codepage issue?

    Hi,

    I can't understand the output below (in DB2 command window)

    ####
    db2 select SI_CUID FROM CMS_InfoObjects6 WHERE SI_CUID IN ('Ae91e1J_GUdIrEEp3VGbY4o')

    SI_CUID
    --------------------------------------------------
    x'4165393165314A5F47556449724545703356476259346F'
    ####

    I would expect the query to return 'Ae91e1J_GUdIrEEp3VGbY4o'...

    SI_CUID is a VARCHAR column, in a DB2 9.5 database configured as follows (get db cfg)

    ####
    Database territory = US
    Database code page = 1208
    Database code set = UTF-8
    Database country/region code = 1
    ####

    If I query another VARCHAR, I got the expected result, for example:

    ####
    db2 select name from sysibm.systables where name = 'CMS_INFOOBJECTS6'

    NAME
    ------------------
    CMS_INFOOBJECTS6
    ####

    I am stuck with this issue

    any help would be welcome

    Thanks in advance

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Please do this and post results:

    db2 describe table CMS_InfoObjects6 (you many need to specify schema name in front of the table).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Nov 2010
    Posts
    4

    skinis take

    Here it is:

    ####
    db2 describe table CMS_InfoObjects6

    Data type Column
    Column name schema Data type name Length Scale Nulls
    ------------------------------- --------- ------------------- ---------- ----- ------
    OBJECTID SYSIBM INTEGER 4 0 No
    PARENTID SYSIBM INTEGER 4 0 No
    TYPEID SYSIBM INTEGER 4 0 No
    OWNERID SYSIBM INTEGER 4 0 No
    VERSION SYSIBM INTEGER 4 0 No
    LASTMODIFYTIME SYSIBM VARCHAR 32 0 No
    SCHEDULESTATUS SYSIBM INTEGER 4 0 Yes
    NEXTRUNTIME SYSIBM VARCHAR 32 0 Yes
    CRC SYSIBM VARCHAR 30 0 No
    PROPERTIES SYSIBM BLOB 2147483647 0 No
    SI_GUID SYSIBM VARCHAR 56 0 Yes
    SI_CUID SYSIBM VARCHAR 56 0 Yes
    SI_RUID SYSIBM VARCHAR 56 0 Yes
    SI_INSTANCE_OBJECT SYSIBM INTEGER 4 0 Yes
    SI_PLUGIN_OBJECT SYSIBM INTEGER 4 0 Yes
    SI_TABLE SYSIBM INTEGER 4 0 Yes
    SI_HIDDEN_OBJECT SYSIBM INTEGER 4 0 Yes
    SI_NAMEDUSER SYSIBM INTEGER 4 0 Yes
    SI_RECURRING SYSIBM INTEGER 4 0 Yes
    SI_RUNNABLE_OBJECT SYSIBM INTEGER 4 0 Yes
    SI_PSS_SERVICE_ID SYSIBM INTEGER 4 0 Yes
    OBJNAME SYSIBM VARCHAR 255 0 Yes
    OBJNAME_TR SYSIBM INTEGER 4 0 Yes
    SI_KEYWORD SYSIBM VARCHAR 255 0 Yes
    SI_KEYWORD_TR SYSIBM INTEGER 4 0 Yes
    LOV_KEY SYSIBM VARCHAR 18 0 Yes
    ####

    what do you think?

    Thanks

  4. #4
    Join Date
    Nov 2010
    Posts
    4
    I could notice the below pattern... not sure it can help with my issue

    ####
    select name, tbname, coltype, codepage, dbcscodepg from sysibm.syscolumns where tbname in ('SYSTABLES', 'CMS_INFOOBJECTS6') and name in ('SI_CUID', 'NAME', 'OBJECTID')

    NAME;SYSTABLES;VARCHAR ;1208;1200
    OBJECTID;CMS_INFOOBJECTS6;INTEGER ;0;0
    SI_CUID;CMS_INFOOBJECTS6;VARCHAR ;0;0
    ####

    I have expected result with systables.name (coltype=varchar, codepage=1208, dbcscodepg=1200)
    I have expected result with cms_infoobjects.objectid (coltype=integer, codepage=O, dbcscodepg=0)
    I do not have expected result with cms_infoobjects.si_cuid (coltype=varchar, codepage=O, dbcscodepg=0), and with other columns matching the same characteristics

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    These appear to be binary (FOR BIT DATA) columns. As DB2 does not have any codepage information for such columns, as evidenced by the codepage column values being 0, it cannot interpret the binary data. You will need to provide your own function to generate their string representations that you want.

  6. #6
    Join Date
    Nov 2010
    Posts
    4
    Thanks N_I for the helpful tips!

    With this and this link Function to convert for bit data column to string? - DB2 Database answers, I can now read my data...

    ####
    db2 select cast(SI_CUID as VARCHAR(56) FOR MIXED DATA) FROM CMS_InfoObjects6 WHERE SI_CUID IN ('Ae91e1J_GUdIrEEp3VGbY4o')

    1
    --------------------------------------------------------
    Ae91e1J_GUdIrEEp3VGbY4o
    ####

    next step for me is to check whether this type of data requires any special consideration for restore & roll forward from online backup... which appear to give unusable result in my case...

    Best Regards

Posting Permissions

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