Find opaque types in the Informix database

Question

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?

Answer 1

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.)