Topic
1 reply Latest Post - ‏2013-03-08T08:02:55Z by mwandishi
SystemAdmin
SystemAdmin
17917 Posts
ACCEPTED ANSWER

Pinned topic what is the definiton/intent for S = System managed hidden column

‏2013-03-07T17:54:19Z |
Hello,

I have a question about the Table SYSCAT.COLUMNS

what is the exactly definiton/intent for S = System managed hidden column

type of hidden column.

I = Column is defined as IMPLICITLY HIDDEN
S = System-managed hidden column
Blank = Column is not hidden
Updated on 2013-03-08T08:02:55Z at 2013-03-08T08:02:55Z by mwandishi
  • mwandishi
    mwandishi
    47 Posts
    ACCEPTED ANSWER

    Re: what is the definiton/intent for S = System managed hidden column

    ‏2013-03-08T08:02:55Z  in response to SystemAdmin
    Hi mstoeb,

    It's a good question. You can create tables with columns that are implicitly hidden and therefore not returned to a select unless explicitly referenced, for example:

    CREATE TABLE HIDDEN
    (
    CUSTOMERNO INTEGER NOT NULL,
    CUSTOMERNAME VARCHAR(80),
    PHONENO CHAR(8) IMPLICITLY HIDDEN
    );

    PHONENO would be TYPE = I in HIDDEN.

    The 'S' TYPE simply means the DB2 database manager has done precisely the same thing. One such example would be system temporal tables in v10, which has 3 such columns. I can't find any on my v9.7 DB but that doesn't mean they aren't used by certain features.

    A user explicitly hidden example:

    db2 -tvf test.ddl
    CREATE TABLE HIDDEN ( CUSTOMERNO INTEGER NOT NULL, CUSTOMERNAME VARCHAR(80), PHONENO CHAR(8) IMPLICITLY HIDDEN )
    DB20000I The SQL command completed successfully.

    db2 describe table hidden

    Data type Column
    Column name schema Data type name Length Scale Nulls

    ---------
    ----------
    ------
    CUSTOMERNO SYSIBM INTEGER 4 0 No
    CUSTOMERNAME SYSIBM VARCHAR 80 0 Yes
    PHONENO SYSIBM CHARACTER 8 0 Yes <<<===

    3 record(s) selected.

    db2 "select * from hidden"

    CUSTOMERNO CUSTOMERNAME

    --------------------------------------------------------------------------------

    0 record(s) selected.

    Not in the select * list.

    SCHEMA TABLE_NAME COLNAME HIDDEN

    --------------------
    ------
    DB2V10 HIDDEN PHONENO I

    Best regards,Stephen Levett