DB2 Universal Database for iSeries SQL Reference

SYSTYPES

The SYSTYPES table contains one row for each built-in data type and each distinct type created by the CREATE DISTINCT TYPE statement. The following table describes the columns in the SYSTYPES table:

Table 133. SYSTYPES table

Column Name System Column Name Data Type Description
USER_DEFINED_TYPE_SCHEMA TYPESCHEMA VARCHAR(128) Schema name of the data type.
USER_DEFINED_TYPE_NAME TYPENAME VARCHAR(128) Name of the data type.
USER_DEFINED_TYPE_DEFINER DEFINER VARCHAR(128) Name of the user that created the data type.
SOURCE_SCHEMA SRCSCHEMA VARCHAR(128)
Nullable
The schema for the source data type of this data type.

Contains the null value if this is a built-in data type.

SOURCE_TYPE SRCTYPE VARCHAR(128)
Nullable
Name of the source data type of this data type.

Contains the null value if this is a built-in data type.

SYSTEM_TYPE_SCHEMA SYSTSCHEMA CHAR(10) System schema name of the data type.
SYSTEM_TYPE_NAME SYSTNAME CHAR(10) System name of the data type.
METATYPE METATYPE CHAR(1) Indicates the type of data type.

S
System predefined data type.

T
User-defined distinct type.
LENGTH LENGTH INTEGER The length attribute of the data type; or, in the case of a decimal, numeric, or nonzero precision binary column, its precision:

8 bytes
BIGINT

4 bytes
INTEGER

2 bytes
SMALLINT

Precision of number
DECIMAL

Precision of number
NUMERIC

8 bytes
FLOAT, FLOAT(n) where n = 25 to 53, or DOUBLE PRECISION

4 bytes
FLOAT(n) where n = 1 to 24, or REAL

Length of string
CHARACTER

Maximum length of string
VARCHAR or CLOB

Length of graphic string
GRAPHIC

Maximum length of graphic string
VARGRAPHIC or DBCLOB

Length of binary string
BINARY

Maximum length of binary string
VARBINARY or BLOB

4 bytes
DATE

3 bytes
TIME

10 bytes
TIMESTAMP

Maximum length of datalink URL and comment
DATALINK

40 bytes
ROWID

Same value as the source type
DISTINCT
NUMERIC_SCALE SCALE INTEGER

Nullable
Scale of numeric data.

Contains the null value if the data type is not decimal, numeric, or binary.

CCSID CCSID INTEGER

Nullable
The CCSID value for CHAR, VARCHAR, CLOB, DATE, TIME, TIMESTAMP, GRAPHIC, VARGRAPHIC, DBCLOB and DATALINK data types.

Contains the null value if the data type is numeric.

STORAGE STORAGE INTEGER The storage requirements for the column:

8 bytes
BIGINT

4 bytes
INTEGER

2 bytes
SMALLINT

(Precision/2) + 1
DECIMAL

Precision of number
NUMERIC

8 bytes
FLOAT, FLOAT(n) where n = 25 to 53, or DOUBLE PRECISION

4 bytes
FLOAT(n) where n = 1 to 24, or REAL

Length of string
CHAR

Maximum length of string + 2
VARCHAR

Maximum length of string + 29
CLOB

Length of string * 2
GRAPHIC

Maximum length of string * 2 + 2
VARGRAPHIC

Maximum length of string * 2 + 29
DBCLOB

Length of binary string
BINARY

Maximum length of binary string + 2
VARBINARY

Maximum length of string + 29
BLOB

4 bytes
DATE

3 bytes
TIME

10 bytes
TIMESTAMP

Maximum length of datalink URL and comment + 24
DATALINK

42 bytes
ROWID

Same value as the source type
DISTINCT
Note:
This column supplies the storage requirements for all data types.
NUMERIC_PRECISION PRECISION INTEGER

Nullable
The precision of all numeric data types.

Note:
This column supplies the precision of all numeric data types, including single-and double-precision floating point. The NUMERIC_PRECISION_RADIX column indicates if the value in this column is in binary or decimal digits.

Contains the null value if the data type is not numeric.

CHARACTER_MAXIMUM_LENGTH CHARLEN INTEGER

Nullable
Maximum length of the string for binary, character, and graphic string data types.

Contains the null value if the data type is not a string.

CHARACTER_OCTET_LENGTH CHARBYTE INTEGER

Nullable
Number of bytes for binary, character, and graphic string data types.

Contains the null value if the data type is not a string.

ALLOCATE
ALLOCATE INTEGER

Nullable
Allocated length of the string for binary, varying-length character, and varying-length graphic string data types.

Contains the null value if the data type is numeric or fixed-length.

NUMERIC_PRECISION_RADIX RADIX INTEGER

Nullable
Indicates if the precision specified in column NUMERIC_PRECISION is specified as a number of binary or decimal digits:

2
Binary; floating-point precision is specified in binary digits.

10
Decimal; all other numeric types are specified in decimal digits.

Contains the null value if the data type is not numeric.

DATETIME_PRECISION DATPRC INTEGER

Nullable
The fractional part of a date, time, or timestamp.

0
For DATE and TIME data types

6
For TIMESTAMP data types (number of microseconds).

Contains the null value if the data type is not date, time, or timestamp.

CREATE_TIME CRTTIME TIMESTAMP Identifies the timestamp when the data type was created.
LONG_COMMENT REMARKS VARCHAR(2000)

Nullable
A character string supplied with the COMMENT statement.

Contains the null value if there is no long comment.

IASP_NUMBER IASPNUMBER SMALLINT Specifies the independent auxiliary storage pool (IASP) number of the data type.
LAST_ ALTERED
ALTEREDTS TIMESTAMP
Nullable
Reserved. Contains the null value.
NORMALIZE_DATA NORMALIZE VARCHAR(3) Indicates whether the parameter value should be normalized or not. This attribute only applies to UTF-8 and UTF-16 data.

NO
The value should not be normalized.

YES
The value should be normalized.


[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]