SYSDATATYPES catalog table

The SYSDATATYPES table contains one row for each user-defined type defined to the system. The schema is SYSIBM.

Table 1. SYSIBM.SYSDATATYPES table column descriptions
Column name Data type Description Use
SCHEMA
VARCHAR(128)
NOT NULL
 Schema of the data type. G
OWNER
VARCHAR(128)
NOT NULL
 Owner of the data type. G
NAME
VARCHAR(128)
NOT NULL
 Name of the data type. G
CREATEDBY
VARCHAR(128)
NOT NULL
 Primary authorization ID of the user who created the data type. G
SOURCESCHEMA
VARCHAR(128)
NOT NULL
 Schema of the source data type. G
SOURCETYPE
VARCHAR(128)
NOT NULL
 Name of the source type. G
METATYPE
CHAR(1)
NOT NULL
 The class of data type:
A
User-defined ordinary array type
L
User-defined associative array type
T
Distinct type
 G
DATATYPEID
INTEGER
NOT NULL
 Internal identifier of the data type. S
SOURCETYPEID
INTEGER
NOT NULL
 Internal ID of the built-in data type on which the distinct type or array elements are based. S
LENGTH
INTEGER
NOT NULL
 Maximum length or precision for a data type that is based on the IBM®-defined DECIMAL data type. The data type can be a distinct type or an array type. G
SCALE
SMALLINT
NOT NULL
One of the following values:
  • For a data type that is based on the IBM-defined DECIMAL data type, the scale. The data type can be a distinct type or an array type. Number of fractional second digits for a data type that is based on the IBM-defined timestamp or timestamp with time zone type.
  • For a data type that is based on the IBM-defined TIMESTAMP or TIMESTAMP WITH TIME ZONE type, the number of fractional-second digits.
  • For any other data type, the value is 0.

If the value is a timestamp, the LENGTH is 10 and the SCALE is 0, the number of fractional second digits is 6.

 G
SUBTYPE
CHAR(1)
NOT NULL

Subtype of the data type, if the source type is one of the character types. The data type can be a distinct type or an array type. Possible values are:

B
The subtype is FOR BIT DATA.
S
The subtype is FOR SBCS DATA.
M
The subtype is FOR MIXED DATA.
blank
The source type is not a character type.
 G
CREATEDTS
TIMESTAMP
NOT NULL
 Time when the data type was created. G
ENCODING_SCHEME
CHAR(1)
NOT NULL
 Encoding scheme of the data type:
A
ASCII
E
EBCDIC
U
Unicode
 G
IBMREQD
CHAR(1)
NOT NULL
 A value of Y indicates that the row was provided with the Db2 product code. For all other values, see Release dependency indicators.

The value in this field is not a reliable indicator of release dependencies. RELCREATED should be used instead.

 G
REMARKS
VARCHAR(762)
NOT NULL
 A character string provided by the user with the COMMENT statement. G
OWNERTYPE
CHAR(1)
NOT NULL WITH
DEFAULT
 Indicates the type of owner:
blank
Authorization ID
L
Role
 G
RELCREATED
CHAR(1)
NOT NULL
 The release of Db2 that is used to create the object. See Release dependency indicators for the values. G
INLINE_LENGTH
INTEGER
NOT NULL
WITH DEFAULT -1
 The inline length attribute of the type if it is based on a LOB source type:
-1
This type does not specify INLINE LENGTH
greater than or equal to 0
The inline length attribute (in byte) of the type if it is based on a LOB source type
 G
ARRAYLENGTH
BIGINT
NOT NULL
WITH DEFAULT
 Start of changeMaximum cardinality, if the data type is an ordinary array type. For all other data types, the value is 0.End of change G
ARRAYINDEXTYPEID
INTEGER
NOT NULL
WITH DEFAULT
 Data type of the index, if the data types is an associative array type. For all other data types, the value is 0. G
ARRAYINDEXTYPELEN
BIGINT
NOT NULL
WITH DEFAULT
 Maximum length of the array index, if the data types is an associative array type. For all other data types, the value is 0. G
ARRAYINDEXSUBTYPE
CHAR(1)
NOT NULL
WITH DEFAULT
 Subtype of the array index:
B
The subtype is FOR BIT DATA.
S
The subtype is FOR SBCS DATA.
M
The subtype is FOR MIXED DATA.
blank
The array index is not a character type.
 G