informixuser-defined-data-types

Find opaque types in the Informix database


I want to get a list of custom types (opaque types). Type information is stored in the sysxtdtypes system table, but opaque type is defined as basic (https://help.hcltechsw.com/hclinformix/1410/sqr/ids_sqr_084.html):

extended_id mode name type
2056 B circle 41

At the moment, I do not know by what rule extended_id is calculated. Judging by this answer, type 41 can be anything (Unknown type (41, 24)). Is there any system table that stores user-defined types? Is there a way to distinguish a custom one from a basic one?


Solution

  • The documentation for the sysxtdtypes table says that the mode column is a CHAR(1) containing:

    Code classifying the UDT:

    • B = Base (opaque) type
    • C = Collection type or unnamed ROW type
    • D = Distinct type
    • R = Named ROW type
    • S = Reserved for internal use
    • T = Reserved for internal use
    • ' ' (blank) = Built-in type

    So, the entries in sysxtdtypes where mode = 'B' are the "user-defined" opaque types. Note that some types are recorded in there when the database is created — including "blob", "clob", "json", "bson" and so on. They have smaller values for extended_id — it looks as though types you create will have an ID value from 2048 upwards. (At least, when I created a type for the Nth time, it came up with ID 2057.)