Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2012
    Posts
    3

    Unanswered: Detect from catalog if Varchar-Column was defined for Bit/Mixed/SBCSData

    Is there any way to detect from the DB2 catalog tables in DB2 9.7 LUW if a Varchar-Column was defined for BitData, SBCSData or for MixedData? Codepage in Sysibm.syscolumns seems to 0 for alle these 3 types so you can at least know that it's not a "normal" Varchar but I'm wondering how to distinguish between those 3 subtypes.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I don't know, but you should not be accessing sysibm tables. You should look at the sycat views instead. These are documented in the Appendix of the SQL Reference Vol 1, which can be downloaded for free in PDF format.
    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
    Apr 2012
    Posts
    1,034
    Provided Answers: 18
    if the column type is a string type, then syscat.column.codepage will be 0 when the column is 'FOR BIT DATA' ... for Linux/Unix/Windows V9.7.x and above

  4. #4
    Join Date
    Jan 2012
    Posts
    3
    @Marcus_A: Ok, I'll stick to syscat then but it's basically the same here.
    @db2mor: Yes, I already knew that (see my starting post) but I'm wondering how I can distinguish between those 4 types:
    VARCHAR(10) <= this has a filled codepage in syscat.columns
    VARCHAR(10) FOR BIT DATA <= this has a 0 codepage in syscat.columns
    VARCHAR(10) FOR MIXED DATA <= this also seems to have a 0 codepage in syscat.columns
    VARCHAR(10) FOR SBCS DATA <= currently don't know what this has

  5. #5
    Join Date
    Apr 2012
    Posts
    1,034
    Provided Answers: 18
    Is there really a need to distinguish between them?

    I can't see it, maybe you can explain why you need it for LUW and perhaps it's worthwile then searching further.

    I see a need to recognize binary data columns (for bit data, blob etc) and we know how to do that.

    Plus a need to recognize character data columns ( which either have a single-byte code page, or a multi-byte codepage - and for both of these the codepage column has a value).

    The syntax 'for mixed data' and 'for sbcs' data is "non-standard" and "should not be used" (according to the LUW Infocenter).

    By default on LUW 9.x and above the default is UTF-8 codepage, and all tables inherit, so this seems to be functionally equivalent to 'for mixed data' as far as I can tell.

    The ' for sbcs data' syntax gets rejected with unicode databases (i.e all databases created with default options), and for sbcs codepages this is the default anyway.

Posting Permissions

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