SQLTYPEINFO

The SQLTYPEINFO table contains one row for every built-in data type.

The following table describes the columns in the table:

Table 1. SQLTYPEINFO table
Column Name Data Type Description
TYPE_NAME VARCHAR(128) Name of the built-in data type:
BIGINT
BIGINT
INTeger
INTEGER
SMALLINT
SMALLINT
DECIMAL
DECIMAL
NUMERIC
NUMERIC
FLOAT
DOUBLE PRECISION
REAL
REAL
DECFLOAT
DECFLOAT
CHARacter
CHARACTER
CHARacter FOR BIT DATA
CHARACTER FOR BIT DATA
VARCHAR
VARCHAR
VARCHAR FOR BIT DATA
VARCHAR FOR BIT DATA
CLOB
CLOB
GRAPHIC
GRAPHIC
VARGRAPHIC
VARGRAPHIC
DBCLOB
DBCLOB
NCHAR
NCHAR
NVARCHAR
NVARCHAR
NCLOB
NCLOB
BINARY
BINARY
VARBINARY
VARBINARY
BLOB
BLOB
DATE
DATE
TIME
TIME
TIMESTAMP
TIMESTAMP
DATALINK
DATALINK
ROWID
ROWID
Start of changeXMLEnd of change
Start of changeXMLEnd of change
DATA_TYPE SMALLINT The data type of the built-in data type:
–5
BIGINT
4
INTEGER
5
SMALLINT
3
DECIMAL
2
NUMERIC
8
DOUBLE PRECISION
7
REAL
-360
DECFLOAT
1
CHARACTER
–2
CHARACTER FOR BIT DATA or BINARY
12
VARCHAR
–3
VARCHAR FOR BIT DATA or VARBINARY
-99
CLOB
–95
GRAPHIC
–96
VARGRAPHIC
–350
DBCLOB
–8
NCHAR
–9
NVARCHAR
–10
NCLOB
-99
BLOB
91
DATE
92
TIME
93
TIMESTAMP
70
DATALINK
–100
ROWID
Start of change-370End of change
Start of changeXMLEnd of change
2001
DISTINCT
COLUMN_SIZE INTEGER

Nullable

The maximum length of the data type.
LITERAL_PREFIX VARCHAR(128)

Nullable

Indicates the prefix for a string literal.

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

LITERAL_SUFFIX VARCHAR(128)

Nullable

Indicates the suffix for a string literal.

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

CREATE_PARAMS VARCHAR(128)

Nullable

Indicates the parameters supported with the data type.
length
The parameter is a length. Returned for all string data types and DATALINK.
precision,scale
The parameters include precision and scale. Returned for the DECIMAL and NUMERIC data types.

Contains the null value for all other data types.

NULLABLE SMALLINT

Nullable

Indicates whether the data type is nullable.
0
The data type does not allow nulls.
1
The data type does allow nulls.
CASE_SENSITIVE SMALLINT

Nullable

Indicates whether the data type is case sensitive.
0
The data type is not case sensitive.
1
The data type is case sensitive.
SEARCHABLE SMALLINT

Nullable

Indicates whether the data type can be used in a predicate.
0
The data type cannot be used in predicates.
2
The data type can be used in all predicates except the LIKE predicate.
3
The data type can be used in all predicates including the LIKE predicate.
UNSIGNED_ATTRIBUTE SMALLINT

Nullable

Indicates whether the numeric data type is signed or unsigned.
0
The data type is signed.
1
The data type is unsigned.

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

FIXED_PREC_SCALE SMALLINT

Nullable

Indicates whether the data type has a fixed precision and scale.
0
The data type does not have a fixed precision and scale.
1
The data type does have a fixed precision and scale.

Start of changeContains the null value if the data type is not numeric.End of change

AUTO_UNIQUE_VALUE SMALLINT

Nullable

Indicates whether the numeric data type is auto-incrementing:
0
The data type is not auto-incrementing.
1
The data type is auto-incrementing.

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

LOCAL_TYPE_NAME VARCHAR(128)

Nullable

Reserved. Contains the null value.
MINIMUM_SCALE SMALLINT

Nullable

Indicates the minimum scale of numeric data types.

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

MAXIMUM_SCALE SMALLINT

Nullable

Indicates the maximum scale of numeric data types.

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

SQL_DATA_TYPE SMALLINT

Nullable

Indicates the SQL data type value of the data type:
–5
BIGINT
4
INTEGER
5
SMALLINT
3
DECIMAL
2
NUMERIC
8
DOUBLE PRECISION
7
REAL
-360
DECFLOAT
1
CHARACTER
–2
CHARACTER FOR BIT DATA or BINARY
12
VARCHAR
–3
VARCHAR FOR BIT DATA or VARBINARY
-99
CLOB
–95
GRAPHIC
–96
VARGRAPHIC
–350
DBCLOB
–8
NCHAR
–9
NVARCHAR
–10
NCLOB
-98
BLOB
9
DATE
9
TIME
9
TIMESTAMP
70
DATALINK
–100
ROWID
Start of change-370End of change
Start of changeXMLEnd of change
17
DISTINCT
SQL_DATETIME_SUB SMALLINT

Nullable

The datetime subtype of the data type:
1
DATE
2
TIME
3
TIMESTAMP

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

NUM_PREC_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 parameter is not numeric.

INTERVAL_PRECISION SMALLINT

Nullable

Reserved. Contains the null value.
JDBC_DATA_TYPE SMALLINT The JDBC data type value of the data type:
–5
BIGINT
4
INTEGER
5
SMALLINT
3
DECIMAL
2
NUMERIC
8
DOUBLE PRECISION
7
REAL
1111
DECFLOAT
1
CHARACTER or GRAPHIC or NCHAR
–2
CHARACTER FOR BIT DATA or BINARY
12
VARCHAR or VARGRAPHIC or NVARCHAR
–3
VARCHAR FOR BIT DATA or VARBINARY
2005
CLOB or DBCLOB or NCLOB
2004
BLOB
91
DATE
92
TIME
93
TIMESTAMP
70
DATALINK
1111
ROWID
Start of change2009End of change
Start of changeXMLEnd of change
2001
DISTINCT
I_DATA_TYPE SMALLINT Indicates the IBM® i CLI data type of the data type.
19
BIGINT
4
INTEGER
5
SMALLINT
3
DECIMAL
2
NUMERIC
8
DOUBLE PRECISION
7
REAL
-360
DECFLOAT
1
CHARACTER
–2
CHARACTER FOR BIT DATA or BINARY
12
VARCHAR
–3
VARCHAR FOR BIT DATA or VARBINARY
14
CLOB
95
GRAPHIC or NCHAR
96
VARGRAPHIC or NVARCHAR
15
DBCLOB or NCLOB
13
BLOB
91
DATE
92
TIME
93
TIMESTAMP
16
DATALINK
1111
ROWID
Start of change-370End of change
Start of changeXMLEnd of change
2001
DISTINCT