ANS and ISO catalog views

id="catalogans">ANS and ISO catalog views

There are two versions of some of the ANS and ISO catalog views. The version documented is the normal set of ANS and ISO views. A second set of views have names that are limited to no more than 18 characters and other than the view names are not documented in this book.
The ANS and ISO catalog includes the following tables in the QSYS2 library:

View Name Shorter View Name Description

SQL_FEATURES Information about features supported by the database manager
SQL_LANGUAGES SQL_LANGUAGES_S Information about the supported languages
SQL_SIZING Information about the limits supported by the database manager

The ANS and ISO catalog includes the following views and tables in the SYSIBM and QSYS2 libraries:

View Name Shorter View Name Description

AUTHORIZATIONS AUTHORIZATIONS Information about authorization IDs
CHARACTER_SETS CHARACTER_SETS_S Information about supported CCSIDs
CHECK_CONSTRAINTS Information about check constraints
COLUMNS COLUMNS_S Information about columns
INFORMATION_SCHEMA_CATALOG_NAME CATALOG_NAME Information about the relational database
PARAMETERS PARAMETERS_S Information about procedure parameters
REFERENTIAL_CONSTRAINTS REF_CONSTRAINTS Information about referential constraints
ROUTINES ROUTINES_S Information about routines
SCHEMATA SCHEMATA_S Statistical information about schemas
TABLE_CONSTRAINTS Information about constraints
TABLES TABLES_S Information about tables
USER_DEFINED_TYPES UDT_S Information about distinct types
VIEWS Information about views

AUTHORIZATIONS

The AUTHORIZATIONS view contains one row for every authorization ID. The following table describes the columns in the view:

Table 159. AUTHORIZATIONS view

Column Name Data Type Description

AUTHORIZATION_NAME VARCHAR(128) Authorization ID name
AUTHORIZATION_TYPE VARCHAR(4) The type of authorization ID. Contains 'USER'.

CHARACTER_SETS

The CHARACTER_SETS view contains one row for every CCSID supported. The following table describes the columns in the view:

Table 160. CHARACTER_SETS view

Column Name Data Type Description

CHARACTER_SET_CATALOG VARCHAR(128) Relational database name
CHARACTER_SET_SCHEMA VARCHAR(128) The schema name of the character set. Contains 'SYSIBM'.
CHARACTER_SET_NAME VARCHAR(128) The character set name.
FORM_OF_USE VARCHAR(128) Nullable
Reserved. Contains the null value.
NUMBER_OF_CHARACTERS INTEGER Nullable
Reserved. Contains the null value.
DEFAULT_COLLATE_CATALOG VARCHAR(128) Reserved. Contains the relational database name.
DEFAULT_COLLATE_SCHEMA VARCHAR(128) Reserved. Contains SYSIBM.
DEFAULT_COLLATE_NAME VARCHAR(128) Reserved. Contains IBMDEFAULT.

CHECK_CONSTRAINTS

The CHECK_CONSTRAINTS view contains one row for every check constraint. The following table describes the columns in the view:

Table 161. CHECK_CONSTRAINTS view

Column Name Data Type Description

CONSTRAINT_CATALOG VARCHAR(128) Relational database name
CONSTRAINT_SCHEMA VARCHAR(128) Name of the schema containing the constraint
CONSTRAINT_NAME VARCHAR(128) Name of the constraint
CHECK_CLAUSE VARCHAR(2000) Nullable
Text of the check constraint clause
Contains the null value if the check clause cannot be contained in the column without truncation.

COLUMNS

The COLUMNS view contains one row for every column. The following table describes the columns in the view:

Table 162. COLUMNS view

Column Name Data Type Description

TABLE_CATALOG VARCHAR(128) Relational database name
TABLE_SCHEMA VARCHAR(128) Name of the SQL schema containing the table or view
TABLE_NAME VARCHAR(128) Name of the table or view that contains the column
COLUMN_NAME VARCHAR(128) Name of the column
ORDINAL_POSITION INTEGER Numeric place of the column in the table or view, ordered from left to right
COLUMN_DEFAULT VARCHAR(2000) Nullable
The default value of a column, if one exists. If the default value of the column cannot be represented without truncation, then the value of the column is the string 'TRUNCATED'. The default value is stored in character form. The following special values also exist:

CURRENT_DATE

The default value is the current date.

CURRENT_TIME

The default value is the current time.

CURRENT_TIMESTAMP

The default value is the current timestamp.

NULL

The default value is the null value and DEFAULT NULL was explicitly specified.

USER

The default value is the current job user.

Contains the null value if:

The column has no default value. For example, if the column has an IDENTITY attribute or is a row ID, or

A DEFAULT value was not explicitly specified.

IS_NULLABLE VARCHAR(3) Indicates whether the column can contain null values:

NO

The column cannot contain null values.

YES

The column can contain null values.

DATA_TYPE VARCHAR(128) Type of column:

BIGINT

Big number

INTEGER

Large number

SMALLINT

Small number

DECIMAL

Packed decimal

NUMERIC

Zoned decimal

DOUBLE PRECISION

Double-precision floating point

REAL

Single-precision floating point

CHARACTER

Fixed-length character string

CHARACTER VARYING

Varying-length character string

CHARACTER LARGE OBJECT

Character large object string

GRAPHIC

Fixed-length graphic string

GRAPHIC VARYING

Varying-length graphic string

DOUBLE-BYTE CHARACTER LARGE OBJECT

Double-byte character large object string

BINARY

Fixed-length binary string

BINARY VARYING

Varying-length binary string

BINARY LARGE OBJECT

Binary large object string

DATE

Date

TIME

Time

TIMESTAMP

Timestamp

DATALINK

Datalink

ROWID

Row ID

USER-DEFINED

Distinct type

CHARACTER_MAXIMUM_LENGTH INTEGER Nullable
Maximum length of the string for binary, character and graphic string data types.
Contains the null value if the column is not a string.
CHARACTER_OCTET_LENGTH INTEGER Nullable
Number of bytes for binary, character and graphic string data types.
Contains the null value if the column is not a string.
NUMERIC_PRECISION INTEGER Nullable
The precision of all numeric columns.

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 column is not numeric.
NUMERIC_PRECISION_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 column is not numeric.
NUMERIC_SCALE INTEGER Nullable
Scale of numeric data.
Contains the null value if the column is not decimal, numeric, or binary.
DATETIME_PRECISION 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 column is not a date, time, or timestamp.
INTERVAL_TYPE VARCHAR(128) Nullable
Reserved. Contains the null value.
INTERVAL_PRECISION INTEGER Nullable
Reserved. Contains the null value.
CHARACTER_SET_CATALOG VARCHAR(128) Nullable
Relational database name
Contains the null value if the column is not a string.
CHARACTER_SET_SCHEMA VARCHAR(128) Nullable
The schema name of the character set. Contains SYSIBM.
Contains the null value if the column is not a string.
CHARACTER_SET_NAME VARCHAR(128) Nullable
The character set name.
Contains the null value if the column is not a string.
COLLATION_CATALOG VARCHAR(128) Nullable
Relational database name
Contains the null value if the column is not a string.
COLLATION_SCHEMA VARCHAR(128) Nullable
The schema of the collation. Contains SYSIBM.
Contains the null value if the column is not a string.
COLLATION_NAME VARCHAR(128) Nullable
The collation name. Contains IBMBINARY.
Contains the null value if the column is not a string.
DOMAIN_CATALOG VARCHAR(128) Nullable
Reserved. Contains the null value.
DOMAIN_SCHEMA VARCHAR(128) Nullable
Reserved. Contains the null value.
DOMAIN_NAME VARCHAR(128) Nullable
Reserved. Contains the null value.
UDT_CATALOG VARCHAR(128) Nullable
The relational database name if this is a distinct type.
Contains the null value if this is not a distinct type.
UDT_SCHEMA VARCHAR(128) Nullable
The name of the schema if this is a distinct type.
Contains the null value if this is not a distinct type.
UDT_NAME VARCHAR(128) Nullable
The name of the distinct type.
Contains the null value if this is not a distinct type.
SCOPE_CATALOG VARCHAR(128) Nullable
Reserved. Contains the null value.
SCOPE_SCHEMA VARCHAR(128) Nullable
Reserved. Contains the null value.
SCOPE_NAME VARCHAR(128) Nullable
Reserved. Contains the null value.
MAXIMUM_CARDINALITY INTEGER Nullable
Reserved. Contains the null value.
DTD_IDENTIFIER VARCHAR(128) Nullable
A unique internal identifier for the column.
IS_SELF_REFERENCING VARCHAR(3) Reserved. Contains 'NO'.

INFORMATION_SCHEMA_CATALOG_NAME

The INFORMATION_SCHEMA_CATALOG_NAME view contains one row for the relational database. The following table describes the columns in the view:

Table 163. INFORMATION_SCHEMA_CATALOG_NAME view

Column Name Data Type Description

CATALOG_NAME VARCHAR(128) Relational database name

PARAMETERS

The PARAMETERS view contains one row for each parameter of a routine in the relational database. The following table describes the columns in the view:

Table 164. PARAMETERS view

Column Name Data Type Description

SPECIFIC_CATALOG VARCHAR(128) Relational database name
SPECIFIC_SCHEMA VARCHAR(128) Schema name of the routine instance
SPECIFIC_NAME VARCHAR(128) Specific name of the routine instance
ORDINAL_POSITION INTEGER Numeric place of the parameter in the parameter list, ordered from left to right.
PARAMETER_MODE VARCHAR(5) The type of the parameter:

IN

This is an input parameter.

OUT

This is an output parameter.

INOUT

This is an input/output parameter.

IS_RESULT VARCHAR(3) Reserved. Contains 'NO'.
AS_LOCATOR VARCHAR(3) Indicates whether the parameter was specified as a locator.

NO

The parameter was not specified as a locator.

YES

The parameter was specified as a locator.

PARAMETER_NAME VARCHAR(128) Nullable
The name of the parameter
Contains the null value if the parameter does not have a name.
FROM_SQL_SPECIFIC_CATALOG VARCHAR(128) Nullable
Reserved. Contains the null value.
FROM_SQL_SPECIFIC_SCHEMA VARCHAR(128) Nullable
Reserved. Contains the null value.
FROM_SQL_SPECIFIC_NAME VARCHAR(128) Nullable
Reserved. Contains the null value.
TO_SQL_SPECIFIC_CATALOG VARCHAR(128) Nullable
Reserved. Contains the null value.
TO_SQL_SPECIFIC_SCHEMA VARCHAR(128) Nullable
Reserved. Contains the null value.
TO_SQL_SPECIFIC_NAME VARCHAR(128) Nullable
Reserved. Contains the null value.
DATA_TYPE VARCHAR(128) Nullable
Type of the parameter:

BIGINT

Big number

INTEGER

Large number

SMALLINT

Small number

DECIMAL

Packed decimal

NUMERIC

Zoned decimal

DOUBLE PRECISION

Floating point; DOUBLE PRECISION

REAL

Floating point; REAL

CHARACTER

Fixed-length character string

CHARACTER VARYING

Varying-length character string

CHARACTER LARGE OBJECT

Character large object string

GRAPHIC

Fixed-length graphic string

GRAPHIC VARYING

Varying-length graphic string

DOUBLE-BYTE CHARACTER LARGE OBJECT

Double-byte character large object string

BINARY

Fixed-length binary string

BINARY VARYING

Varying-length binary string

BINARY LARGE OBJECT

Binary large object string

DATE

Date

TIME

Time

TIMESTAMP

Timestamp

DATALINK

Datalink

ROWID

Row ID

USER-DEFINED

Distinct Type

CHARACTER_MAXIMUM_LENGTH INTEGER Nullable
Maximum length of the string for binary, character, and graphic string data types.
Contains the null value if the parameter is not a string.
CHARACTER_OCTET_LENGTH INTEGER Nullable
Number of bytes for binary, character, and graphic string data types.
Contains the null value if the parameter is not a string.
CHARACTER_SET_CATALOG VARCHAR(128) Nullable
Relational database name
Contains the null value if the column is not a string.
CHARACTER_SET_SCHEMA VARCHAR(128) Nullable
The schema name of the character set. Contains 'SYSIBM'.
Contains the null value if the column is not a string.
CHARACTER_SET_NAME VARCHAR(128) Nullable
The character set name.
Contains the null value if the column is not a string.
COLLATION_CATALOG VARCHAR(128) Nullable
Relational database name
Contains the null value if the column is not a string.
COLLATION_SCHEMA VARCHAR(128) Nullable
The schema of the collation. SYSIBM is returned.
Contains the null value if the column is not a string.
COLLATION_NAME VARCHAR(128) Nullable
The collation name. IBMBINARY is returned.
Contains the null value if the column is not a string.
NUMERIC_PRECISION INTEGER Nullable
The precision of all numeric parameters.

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 parameter is not numeric.
NUMERIC_PRECISION_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.
NUMERIC_SCALE INTEGER Nullable
Scale of numeric data.
Contains the null value if not decimal, numeric, or binary parameter.
DATETIME_PRECISION 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 parameter is not a date, time, or timestamp.
INTERVAL_TYPE VARCHAR(128) Nullable
Reserved. Contains the null value.
INTERVAL_PRECISION INTEGER Nullable
Reserved. Contains the null value.
UDT_CATALOG VARCHAR(128) Nullable
The relational database name if this is a distinct type.
Contains the null value if this is not a distinct type.
UDT_SCHEMA VARCHAR(128) Nullable
The name of the schema if this is a distinct type.
Contains the null value if this is not a distinct type.
UDT_NAME VARCHAR(128) Nullable
The name of the distinct type.
Contains the null value if this is not a distinct type.
SCOPE_CATALOG VARCHAR(128) Nullable
Reserved. Contains the null value.
SCOPE_SCHEMA VARCHAR(128) Nullable
Reserved. Contains the null value.
SCOPE_NAME VARCHAR(128) Nullable
Reserved. Contains the null value.
MAXIMUM_CARDINALITY INTEGER Nullable
Reserved. Contains the null value.
DTD_IDENTIFIER VARCHAR(128) Nullable
A unique internal identifier for the parameter.

REFERENTIAL_CONSTRAINTS

The REFERENTIAL_CONSTRAINTS view contains one row for each referential constraint. The following table describes the columns in the view:

Table 165. REFERENTIAL_CONSTRAINTS view

Column Name Data Type Description

CONSTRAINT_CATALOG VARCHAR(128) Relational database name
CONSTRAINT_SCHEMA VARCHAR(128) Name of the schema containing the constraint.
CONSTRAINT_NAME VARCHAR(128) Name of the constraint.
UNIQUE_CONSTRAINT_CATALOG VARCHAR(128) Relational database name containing the unique constraint referenced by the referential constraint.
UNIQUE_CONSTRAINT_SCHEMA VARCHAR(128) Name of the SQL schema containing the unique constraint referenced by the referential constraint.
UNIQUE_CONSTRAINT_NAME VARCHAR(128) Name of the unique constraint referenced by the referential constraint.
MATCH_OPTION VARCHAR(7) Reserved. Contains 'NONE'.
UPDATE_RULE VARCHAR(11) Update Rule.

NO ACTION

RESTRICT

DELETE_RULE VARCHAR(11) Delete Rule

NO ACTION

CASCADE

SET NULL

SET DEFAULT

RESTRICT

COLUMN_COUNT INTEGER Count of columns in the constraint.

ROUTINES

The ROUTINES view contains one row for each routine. The following table describes the columns in the view:

Table 166. ROUTINES view

Column Name Data Type Description

SPECIFIC_CATALOG VARCHAR(128) Relational database name
SPECIFIC_SCHEMA VARCHAR(128) Schema name of the routine instance.
SPECIFIC_NAME VARCHAR(128) Specific name of the routine.
ROUTINE_CATALOG VARCHAR(128) Relational database name
ROUTINE_SCHEMA VARCHAR(128) Name of the SQL schema that contains the routine.
ROUTINE_NAME VARCHAR(128) Name of the routine.
ROUTINE_TYPE VARCHAR(15) Type of the routine.

PROCEDURE

This is a procedure.

FUNCTION

This is a function.

INSTANCE METHOD

This is a built-in data type function created for a distinct type.

MODULE_CATALOG VARCHAR(128) Nullable
Reserved. Contains the null value.
MODULE_SCHEMA VARCHAR(128) Nullable
Reserved. Contains the null value.
MODULE_NAME VARCHAR(128) Nullable
Reserved. Contains the null value.
UDT_CATALOG VARCHAR(128) Nullable
Relational database name.
Contains the null value if this is not an INSTANCE METHOD.
UDT_SCHEMA VARCHAR(128) Nullable
Name of the SQL schema that contains the distinct type related to this function.
Contains the null value if this is not an INSTANCE METHOD.
UDT_NAME VARCHAR(128) Nullable
Name of the distinct type name related to this function.
Contains the null value if this is not an INSTANCE METHOD.
DATA_TYPE VARCHAR(128) Nullable
Type of the result of the function:

BIGINT

Big number

INTEGER

Large number

SMALLINT

Small number

DECIMAL

Packed decimal

NUMERIC

Zoned decimal

DOUBLE PRECISION

Floating point; DOUBLE PRECISION

REAL

Floating point; REAL

CHARACTER

Fixed-length character string

CHARACTER VARYING

Varying-length character string

CHARACTER LARGE OBJECT

Character large object string

GRAPHIC

Fixed-length graphic string

GRAPHIC VARYING

Varying-length graphic string

DOUBLE-BYTE CHARACTER LARGE OBJECT

Double-byte character large object string

BINARY

Fixed-length binary string

BINARY VARYING

Varying-length binary string

BINARY LARGE OBJECT

Binary large object string

DATE

Date

TIME

Time

TIMESTAMP

Timestamp

DATALINK

Datalink

ROWID

Row ID

USER-DEFINED

Distinct Type

Contains the null value if this is not a scalar function.
CHARACTER_MAXIMUM_LENGTH INTEGER Nullable
Maximum length of the result string of the function for binary, character, and graphic string data types.
Contains the null value if this is not a scalar function or the parameter is not a string.
CHARACTER_OCTET_LENGTH INTEGER Nullable
Number of bytes for the result string of the function for binary, character, and graphic string data types.
Contains the null value if this is not a scalar function or the parameter is not a string.
CHARACTER_SET_CATALOG VARCHAR(128) Nullable
Relational database name of the result of the function.
Contains the null value if this is not a scalar function or the result is not a string.
CHARACTER_SET_SCHEMA VARCHAR(128) Nullable
The schema name of the character set of the result of the function. Contains 'SYSIBM'.
Contains the null value if this is not a scalar function or the result is not a string.
CHARACTER_SET_NAME VARCHAR(128) Nullable
The character set name of the result of the function.
Contains the null value if this is not a scalar function or the result is not a string.
COLLATION_CATALOG VARCHAR(128) Nullable
Relational database name of the result of the function.
Contains the null value if this is not a scalar function or the result is not a string.
COLLATION_SCHEMA VARCHAR(128) Nullable
The schema of the collation of the result of the function. SYSIBM is returned.
Contains the null value if this is not a scalar function or the result is not a string.
COLLATION_NAME VARCHAR(128) Nullable
The collation name of the result of the function. IBMBINARY is returned.
Contains the null value if this is not a scalar function or the result is not a string.
NUMERIC_PRECISION INTEGER Nullable
The precision of the result of the function.

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 this is not a scalar function or the result is not numeric.
NUMERIC_PRECISION_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 this is not a scalar function or the result is not numeric.
NUMERIC_SCALE INTEGER Nullable
Scale of numeric result of the function.
Contains the null value if this is not a scalar function or the result is not numeric.
DATETIME_PRECISION INTEGER Nullable
The fractional part of a date, time, or timestamp result of the function.

0

For DATE and TIME data types

6

For TIMESTAMP data types (number of microseconds).

Contains the null value if this is not a scalar function or the result is not a date, time, or timestamp.
INTERVAL_TYPE VARCHAR(128) Nullable
Reserved. Contains the null value.
INTERVAL_PRECISION INTEGER Nullable
Reserved. Contains the null value.
TYPE_UDT_CATALOG VARCHAR(128) Nullable
The relational database name if the result of the function is a distinct type.
Contains the null value if this is not a scalar function or the result is not a distinct type.
TYPE_UDT_SCHEMA VARCHAR(128) Nullable
The name of the schema if the result of the function is a distinct type.
Contains the null value if this is not a scalar function or the result is not a distinct type.
TYPE_UDT_NAME VARCHAR(128) Nullable
The name of the distinct type if the result of the function is a distinct type.
Contains the null value if this is not a scalar function or the result is not a distinct type.
SCOPE_CATALOG VARCHAR(128) Nullable
Reserved. Contains the null value.
SCOPE_SCHEMA VARCHAR(128) Nullable
Reserved. Contains the null value.
SCOPE_NAME VARCHAR(128) Nullable
Reserved. Contains the null value.
MAXIMUM_CARDINALITY INTEGER Nullable
Reserved. Contains the null value.
DTD_IDENTIFIER VARCHAR(128) Nullable
A unique internal identifier for the result of the function.
ROUTINE_BODY VARCHAR(8) The type of the routine body:

EXTERNAL

This is an external routine.

SQL

This is an SQL routine.

ROUTINE_DEFINITION DBCLOB(2M) CCSID 13488 Nullable
If this is an SQL routine, this column contains the SQL routine body.
Contains the null value if this is not an SQL routine or if the routine body cannot be contained in this column without truncation.
EXTERNAL_NAME VARCHAR(279) Nullable
If this is an external routine, this column identifies the external program name.

For REXX, the external program name is schema-name/source-file-name(member-name).

For ILE service programs, the external program name is schema-name/service-program-name(entry-point-name).

For Java™ programs, the external program name is an optional jar-id followed by a fully-qualified-class-name!method-name or fully-qualified-class-name.method-name.

For all other languages, the external program name is schema-name/program-name.

Contains the null value if this is a system-generated function or a function sourced on a built-in function.
EXTERNAL_LANGUAGE VARCHAR(8) Nullable
If this is an external routine, this column identifies the external program name.

C

The external program is written in C.

C++

The external program is written in C++.

CL

The external program is written in CL.

COBOL

The external program is written in COBOL.

COBOLLE

The external program is written in ILE COBOL.

FORTRAN

The external program is written in FORTRAN.

JAVA

The external program is written in JAVA.

PLI

The external program is written in PL/I.

REXX

The external program is a REXX procedure.

RPG

The external program is written in RPG.

RPGLE

The external program is written in ILE RPG.

Contains the null value if this is not an external routine.
PARAMETER_STYLE VARCHAR(18) Nullable
If this is an external routine, this column identifies the parameter style (calling convention).

DB2GENERAL

This is the DB2GENERAL calling convention.

DB2SQL

This is the DB2SQL calling convention.

GENERAL

This is the GENERAL calling convention.

JAVA

This is the JAVA calling convention.

GENERAL WITH NULLS

This is the GENERAL WITH NULLS calling convention.

SQL

This is the SQL standard calling convention.

Contains the null value if this is not an external routine.
IS_DETERMINISTIC VARCHAR(3) This column identifies whether the routine is deterministic. That is, whether a call to the routine with the same arguments will always return the same result.

NO

The routine is not deterministic.

YES

The routine is deterministic.

SQL_DATA_ACCESS VARCHAR(17) This column identifies whether a routine contains SQL and whether it reads or modifies data.

NO SQL

The routine does not contain any SQL statements.

CONTAINS SQL

The routine contains SQL statements.

READS SQL DATA

The routine possibly reads data from a table or view.

MODIFIES SQL DATA

The routine possibly modifies data in a table or view or issues SQL DDL statements.

IS_NULL_CALL VARCHAR(3) Nullable
Identifies whether the function needs to be called if an input parameter is the null value.

NO

This function need not be called if an input parameter is the null value. If this is a scalar function, the result of the function is implicitly null if any of the operands are null. If this is a table function, the result of the function is an empty table if any of the operands are the null value.

YES

This function must be called even if an input operand is null.

Contains the null value if this is not a function.
SQL_PATH VARCHAR(3483) Nullable
If this is an SQL routine, this column identifies the path.
Contains the null value if this is not an SQL routine.
SCHEMA_LEVEL_ROUTINE VARCHAR(3) Reserved. Contains 'YES'.
MAX_DYNAMIC_RESULT_SETS SMALLINT Identifies the maximum number of result sets returned. 0 indicates that there are no result sets.
IS_USER_DEFINED_CAST VARCHAR(3) Nullable
Identifies whether the this function is a cast function created when a distinct type was created.

NO

This function is not a cast function.

YES

This function is a cast function.

Contains the null value if the routine is not a function.
IS_IMPLICITLY_INVOCABLE VARCHAR(3) Nullable
Identifies whether the this function is a cast function created when a distinct type was created and can be implicitly invoked.

NO

This function is not a cast function.

YES

This function is a cast function and can be implicitly invoked.

Contains the null value if the routine is not a function.
SECURITY_TYPE VARCHAR(22) Nullable
Reserved. Contains 'IMPLEMENTATION DEFINED' if this is an external routine.
Contains the null value if the routine is not an external routine.
TO_SQL_SPECIFIC_CATALOG VARCHAR(128) Nullable
Reserved. Contains the null value.
TO_SQL_SPECIFIC_SCHEMA VARCHAR(128) Nullable
Reserved. Contains the null value.
TO_SQL_SPECIFIC_NAME VARCHAR(128) Nullable
Reserved. Contains the null value.
AS_LOCATOR VARCHAR(3) Nullable
Indicates whether the result was specified as a locator.

NO

The parameter was not specified as a locator.

YES

The parameter was specified as a locator.

Contains the null value if this is not a scalar function.
CREATED TIMESTAMP Identifies the timestamp when the routine was created.
LAST_ALTERED TIMESTAMP Nullable
Timestamp when routine was last altered. Contains null if the routine has never been altered.
NEW_SAVEPOINT_LEVEL VARCHAR(3) Nullable
Indicates whether the routine starts a new savepoint level.

NO

A new savepoint level is not started when the procedure is called.

YES

A new savepoint level is started when the procedure is called.

Contains the null value if this is not a function.
IS_UDT_DEPENDENT VARCHAR(3) Indicates whether the routine is dependent on a UDT.

NO

The routine is not dependent on a UDT.

YES

The routine is dependent on a UDT.

RESULT_CAST_FROM_DATA_TYPE VARCHAR(128) Nullable
Type of the parameter:

BIGINT

Big number

INTEGER

Large number

SMALLINT

Small number

DECIMAL

Packed decimal

NUMERIC

Zoned decimal

DOUBLE PRECISION

Floating point; DOUBLE PRECISION

REAL

Floating point; REAL

CHARACTER

Fixed-length character string

CHARACTER VARYING

Varying-length character string

CHARACTER LARGE OBJECT

Character large object string

GRAPHIC

Fixed-length graphic string

GRAPHIC VARYING

Varying-length graphic string

DOUBLE-BYTE CHARACTER LARGE OBJECT

Double-byte character large object string

BINARY

Fixed-length binary string

BINARY VARYING

Varying-length binary string

BINARY LARGE OBJECT

Binary large object string

DATE

Date

TIME

Time

TIMESTAMP

Timestamp

DATALINK

Datalink

ROWID

Row ID

USER-DEFINED

Distinct Type

RESULT_CAST_AS_LOCATOR VARCHAR(3) Nullable
Indicates whether the result is cast from a locator.

NO

The result is not cast from a locator.

YES

The result is cast from a locator.

RESULT_CAST_CHAR_MAX_LENGTH INTEGER Nullable
Maximum length of the string for binary, character, and graphic string data types.
Contains the null value if the parameter is not a string.
RESULT_CAST_CHAR_OCTET_LENGTH INTEGER Nullable
Number of bytes for binary, character, and graphic string data types.
Contains the null value if the parameter is not a string.
RESULT_CAST_CHAR_SET_CATALOG VARCHAR(128) Nullable
Relational database name
Contains the null value if the column is not a string.
RESULT_CAST_CHAR_SET_SCHEMA VARCHAR(128) Nullable
The schema name of the character set. Contains 'SYSIBM'.
Contains the null value if the column is not a string.
RESULT_CAST_CHAR_SET_NAME VARCHAR(128) Nullable
The character set name.
Contains the null value if the column is not a string.
RESULT_CAST_COLLATION_CATALOG VARCHAR(128) Nullable
Relational database name
Contains the null value if the column is not a string.
RESULT_CAST_COLLATION_SCHEMA VARCHAR(128) Nullable
The schema of the collation. SYSIBM is returned.
Contains the null value if the column is not a string.
RESULT_CAST_COLLATION_NAME VARCHAR(128) Nullable
The collation name. IBMBINARY is returned.
Contains the null value if the column is not a string.
RESULT_CAST_NUMERIC_PRECISION INTEGER Nullable
The precision of all numeric parameters.

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 parameter is not numeric.
RESULT_CAST_NUMERIC_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.
RESULT_CAST_NUMERIC_SCALE INTEGER Nullable
Scale of numeric data.
Contains the null value if not decimal, numeric, or binary parameter.
RESULT_CAST_DATETIME_PRECISION 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 parameter is not a date, time, or timestamp.
RESULT_CAST_INTERVAL_TYPE VARCHAR(128) Nullable
Reserved. Contains the null value.
RESULT_CAST_INTERVAL_PRECISION INTEGER Nullable
Reserved. Contains the null value.
RESULT_CAST_TYPE_UDT_CATALOG VARCHAR(128) Nullable
The relational database name if this is a distinct type.
Contains the null value if this is not a distinct type.
RESULT_CAST_TYPE_UDT_SCHEMA VARCHAR(128) Nullable
The name of the schema if this is a distinct type.
Contains the null value if this is not a distinct type.
RESULT_CAST_TYPE_UDT_NAME VARCHAR(128) Nullable
The name of the distinct type.
Contains the null value if this is not a distinct type.
RESULT_CAST_SCOPE_CATALOG VARCHAR(128) Nullable
Reserved. Contains the null value.
RESULT_CAST_SCOPE_SCHEMA VARCHAR(128) Nullable
Reserved. Contains the null value.
RESULT_CAST_SCOPE_NAME VARCHAR(128) Nullable
Reserved. Contains the null value.
RESULT_CAST_MAX_CARDINALITY INTEGER Nullable
Reserved. Contains the null value.
RESULT_CAST_DTD_IDENTIFIER VARCHAR(128) Nullable
A unique internal identifier for the parameter.

SCHEMATA

The SCHEMATA view contains one row for each schema. The following table describes the columns in the view:

Table 167. SCHEMATA view

Column Name Data Type Description

CATALOG_NAME VARCHAR(128) Relational database name
SCHEMA_NAME VARCHAR(128) Name of the schema
SCHEMA_OWNER VARCHAR(128) Owner of the schema
DEFAULT_CHARACTER_SET_CATALOG VARCHAR(128) Relational database name
DEFAULT_CHARACTER_SET_SCHEMA VARCHAR(128) The schema name of the default character set. Contains 'SYSIBM'.
DEFAULT_CHARACTER_SET_NAME VARCHAR(128) The default character set name.
SQL_PATH VARCHAR(4096) Nullable
Reserved. Contains the null value.

SQL_FEATURES

The SQL_FEATURES table contains one row for each feature supported by the database manager. The following table describes the columns in the table:

Table 168. SQL_FEATURES table

Column Name Data Type Description

FEATURE_ID VARCHAR(7) Nullable
ANS and ISO feature ID
FEATURE_NAME VARCHAR(128) The name of the ANS and ISO feature.
SUB_FEATURE_ID VARCHAR(7) Nullable
ANS and ISO subfeature ID
SUB_FEATURE_NAME VARCHAR(256) The name of the ANS and ISO subfeature.
IS_SUPPORTED VARCHAR(3) Indicates whether the feature is supported:

YES

This feature is supported.

NO

This feature is not supported.

IS_VERIFIED_BY VARCHAR(128) Nullable
Reserved. Contains the null value.
COMMENTS VARCHAR(2000) Nullable
Reserved. Contains the null value.

SQL_LANGUAGES

The SQL_LANGUAGES table contains one row for every SQL language binding and programming language for which conformance is claimed. The following table describes the columns in the SQL_LANGUAGES table:

Table 169. SQL_LANGUAGES table

Column Name Data Type Description

SQL_LANGUAGE_SOURCE VARCHAR(254) Name of the standard.
SQL_LANGUAGE_YEAR VARCHAR(254)
Year in which the standard was approved.
SQL_LANGUAGE_CONFORMANCE VARCHAR(254)
Nullable
Level of conformance.

2

For the 1987 and 1989 standards, indicates that Level 2 conformance is claimed.

ENTRY

For the 1992 standard, indicates that Entry Level conformance is claimed.

CORE

For the 2003 standard, indicates that Core Level is conformance is claimed.

Contains the null value if conformance is not yet claimed.
SQL_LANGUAGE_INTEGRITY VARCHAR(254)
Nullable
Support of the integrity feature.

YES

conformance is claimed to the integrity feature

NO

conformance is not claimed to the integrity feature

Contains the null value if the standard does not have a separate integrity feature.
SQL_LANGUAGE_IMPLEMENTATION VARCHAR(254)
Nullable
Reserved. Contains the null value.
SQL_LANGUAGE_BINDING_STYLE VARCHAR(254)
The style of binding of the SQL language

EMBEDDED

support for embedded SQL for the language in SQL_LANGUAGE_PROGRAMMING_LANG

DIRECT

DIRECT SQL is supported (for example Interactive SQL)

CLI

Support for CLI for the language in SQL_LANGUAGE_PROGRAMMING_LANG

SQL_LANGUAGE_PROGRAMMING_LANG VARCHAR(254)
Nullable
The language supported by EMBEDDED or CLI.

C

The C language is supported.

COBOL

The COBOL language is supported.

PLI

The PL/I language is supported.

Contains the null value if the SQL_LANGUAGE_BINDING_STYLE is DIRECT.

SQL_SIZING

The SQL_SIZING table contains one row for each limit supported by the database manager. The following table describes the columns in the table:

Table 170. SQL_SIZING table

Column Name Data Type Description

SIZING_ID INTEGER ANS and ISO sizing ID
SIZING_NAME VARCHAR(128) Name of the ANS and ISO sizing.
SUPPORTED_VALUE BIGINT Nullable
Indicates the sizing limit.
Contains the null value if the sizing limit is not applicable.
COMMENTS VARCHAR(2000) Nullable
Reserved. Contains the null value.

TABLE_CONSTRAINTS

The TABLE_CONSTRAINTS view contains one row for each constraint. The following table describes the columns in the view:

Table 171. TABLE_CONSTRAINTS view

Column Name Data Type Description

CONSTRAINT_CATALOG VARCHAR(128) Relational database name
CONSTRAINT_SCHEMA VARCHAR(128) Name of the schema containing the constraint.
CONSTRAINT_NAME VARCHAR(128) Name of the constraint.
TABLE_CATALOG VARCHAR(128) Relational database name
TABLE_SCHEMA VARCHAR(128) Name of the schema containing the table.
TABLE_NAME VARCHAR(128) Name of the table which the constraint is created over.
CONSTRAINT_TYPE VARCHAR(11) Constraint Type

CHECK

UNIQUE

PRIMARY KEY

FOREIGN KEY

IS_DEFERRABLE VARCHAR(3) Indicates whether the constraint checking can be deferred. Contains 'NO'.
INITIALLY_DEFERRED VARCHAR(3) Indicates whether the constraint was defined as initially deferred. Contains 'NO'.

TABLES

The TABLES view contains one row for each table, view, and alias. The following table describes the columns in the view:

Table 172. TABLES view

Column Name Data Type Description

TABLE_CATALOG VARCHAR(128) Relational database name
TABLE_SCHEMA VARCHAR(128) Name of the SQL schema that contains the table, view or alias.
TABLE_NAME VARCHAR(128) Name of the table, view or alias.
TABLE_TYPE VARCHAR(24) Indicates the type of the table:

ALIAS

The table is an alias.

BASE TABLE

The table is an SQL table or physical file.

MATERIALIZED QUERY TABLE

The object is a materialized query table.

VIEW

The table is an SQL view or logical file.

SELF_REFERENCING_COLUMN_NAME VARCHAR(128) Nullable
Reserved. Contains the null value.
REFERENCE_GENERATION VARCHAR(128) Nullable
Reserved. Contains the null value.
USER_DEFINED_TYPE_CATALOG VARCHAR(128) Nullable
Reserved. Contains the null value.
USER_DEFINED_TYPE_SCHEMA VARCHAR(128) Nullable
Reserved. Contains the null value.
USER_DEFINED_TYPE_NAME VARCHAR(128) Nullable
Reserved. Contains the null value.
IS_INSERTABLE_INTO VARCHAR(3) Identifies whether an INSERT is allowed on the table.

NO

An INSERT is not allowed on this table.

YES

An INSERT is allowed on this table.

USER_DEFINED_TYPES

The USER_DEFINED_TYPES view contains one row for each distinct type.¹¹⁵ The following table describes the columns in the view:

Table 173. USER_DEFINED_TYPES view

Column Name Data Type Description

USER_DEFINED_TYPE_CATALOG VARCHAR(128) Relational database name
USER_DEFINED_TYPE_SCHEMA VARCHAR(128) Schema name of the distinct type.
USER_DEFINED_TYPE_NAME VARCHAR(128) Name of the user that created the distinct type.
USER_DEFINED_TYPE_CATEGORY VARCHAR(128) Indicates the type of user-defined type. Contains 'DISTINCT'.
IS_INSTANTIABLE VARCHAR(3) Reserved. Contains 'YES'.
IS_FINAL VARCHAR(3) Reserved. Contains 'YES'.
ORDERING_FORM VARCHAR(4) Indicates what kind of predicates are allowed when this distinct type is a comparand:

FULL

All predicates are allowed.

NONE

No predicates are allowed

ORDERING_CATEGORY VARCHAR(8) Reserved. Contains 'MAP'.
ORDERING_ROUTINE_CATALOG VARCHAR(128) Nullable
Relational database name
Contains the null value if the ORDERING_FORM is 'NONE'.
ORDERING_ROUTINE_SCHEMA VARCHAR(128) Nullable
Reserved. Contains 'SYSIBM'.
Contains the null value if the ORDERING_FORM is 'NONE'.
ORDERING_ROUTINE_NAME VARCHAR(128) Nullable
Reserved. Contains a data type name.
Contains the null value if the ORDERING_FORM is 'NONE'.
REFERENCE_TYPE VARCHAR(16) Nullable
Reserved. Contains the null value.
DATA_TYPE VARCHAR(128) Nullable
Source data type of the distinct type:

BIGINT

Big number

INTEGER

Large number

SMALLINT

Small number

DECIMAL

Packed decimal

NUMERIC

Zoned decimal

DOUBLE PRECISION

Floating point; DOUBLE PRECISION

REAL

Floating point; REAL

CHARACTER

Fixed-length character string

CHARACTER VARYING

Varying-length character string

CHARACTER LARGE OBJECT

Character large object string

GRAPHIC

Fixed-length graphic string

GRAPHIC VARYING

Varying-length graphic string

DOUBLE-BYTE CHARACTER LARGE OBJECT

Double-byte character large object string

BINARY

Fixed-length binary string

BINARY VARYING

Varying-length binary string

BINARY LARGE OBJECT

Binary large object string

DATE

Date

TIME

Time

TIMESTAMP

Timestamp

DATALINK

Datalink

ROWID

Row ID

USER-DEFINED

Distinct Type

CHARACTER_MAXIMUM_LENGTH INTEGER Nullable
Maximum length of the distinct type for binary, character, and graphic string data types.
Contains the null value if the distinct type is not a string.
CHARACTER_OCTET_LENGTH INTEGER Nullable
Number of bytes of the distinct type for binary, character, and graphic string data types.
Contains the null value if the distinct type is not a string.
CHARACTER_SET_CATALOG VARCHAR(128) Nullable
Relational database name of the distinct type.
Contains the null value if the distinct type is not a string.
CHARACTER_SET_SCHEMA VARCHAR(128) Nullable
The schema name of the character set of the distinct type. Contains 'SYSIBM'.
Contains the null value if the distinct type is not a string.
CHARACTER_SET_NAME VARCHAR(128) Nullable
The character set name of the distinct type.
Contains the null value if the distinct type is not a string.
COLLATION_CATALOG VARCHAR(128) Nullable
Relational database name of the distinct type.
Contains the null value if the distinct type is not a string.
COLLATION_SCHEMA VARCHAR(128) Nullable
The schema of the collation of the distinct type. SYSIBM is returned.
Contains the null value if the distinct type is not a string.
COLLATION_NAME VARCHAR(128) Nullable
The collation name of the distinct type. IBMBINARY is returned.
Contains the null value if the distinct type is not a string.
NUMERIC_PRECISION INTEGER Nullable
The precision of the distinct type.

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 distinct type is not numeric.
NUMERIC_PRECISION_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 distinct type is not numeric.
NUMERIC_SCALE SMALLINT Nullable
Scale of numeric distinct type.
Contains the null value if the distinct type is not decimal, numeric, or binary.
DATETIME_PRECISION INTEGER Nullable
The fractional part of a date, time, or timestamp distinct type.

0

For DATE and TIME data types

6

For TIMESTAMP data types (number of microseconds).

Contains the null value if the distinct type is not date, time, or timestamp.
INTERVAL_TYPE VARCHAR(128) Nullable
Reserved. Contains the null value.
INTERVAL_PRECISION INTEGER Nullable
Reserved. Contains the null value.
SOURCE_DTD_IDENTIFIER VARCHAR(128) Nullable
A unique internal identifier for the source data type.
Contains the null value if the distinct type is not sourced on another distinct type.
REF_DTD_IDENTIFIER VARCHAR(256) Nullable
Reserved. Contains the null value.

VIEWS

The VIEWS view contains one row for each view. The following table describes the columns in the view:

Table 174. VIEWS view

Column Name Data Type Description

TABLE_CATALOG VARCHAR(128) Relational database name
TABLE_SCHEMA VARCHAR(128) Name of the SQL schema that contains the view.
TABLE_NAME VARCHAR(128) Name of the view.
VIEW_DEFINITION DBCLOB(2M) CCSID 13488 Nullable
The query expression portion of the CREATE VIEW statement.
CHECK_OPTION VARCHAR(8) The check option used on the view

NONE

No check option was specified

LOCAL

The local option was specified

CASCADED

The cascaded option was specified

IS_UPDATABLE VARCHAR(3) Specifies if the view is updatable:

YES

The view is updatable

NO

The view is read-only

115. This view does not contain information about built-in data types.

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

View Name	Shorter View Name	Description
SQL_FEATURES		Information about features supported by the database manager
SQL_LANGUAGES	SQL_LANGUAGES_S	Information about the supported languages
SQL_SIZING		Information about the limits supported by the database manager

View Name	Shorter View Name	Description
AUTHORIZATIONS	AUTHORIZATIONS	Information about authorization IDs
CHARACTER_SETS	CHARACTER_SETS_S	Information about supported CCSIDs
CHECK_CONSTRAINTS		Information about check constraints
COLUMNS	COLUMNS_S	Information about columns
INFORMATION_SCHEMA_CATALOG_NAME	CATALOG_NAME	Information about the relational database
PARAMETERS	PARAMETERS_S	Information about procedure parameters
REFERENTIAL_CONSTRAINTS	REF_CONSTRAINTS	Information about referential constraints
ROUTINES	ROUTINES_S	Information about routines
SCHEMATA	SCHEMATA_S	Statistical information about schemas
TABLE_CONSTRAINTS		Information about constraints
TABLES	TABLES_S	Information about tables
USER_DEFINED_TYPES	UDT_S	Information about distinct types
VIEWS		Information about views

Column Name	Data Type	Description
AUTHORIZATION_NAME	VARCHAR(128)	Authorization ID name
AUTHORIZATION_TYPE	VARCHAR(4)	The type of authorization ID. Contains 'USER'.

Column Name	Data Type	Description
CHARACTER_SET_CATALOG	VARCHAR(128)	Relational database name
CHARACTER_SET_SCHEMA	VARCHAR(128)	The schema name of the character set. Contains 'SYSIBM'.
CHARACTER_SET_NAME	VARCHAR(128)	The character set name.
FORM_OF_USE	VARCHAR(128) Nullable	Reserved. Contains the null value.
NUMBER_OF_CHARACTERS	INTEGER Nullable	Reserved. Contains the null value.
DEFAULT_COLLATE_CATALOG	VARCHAR(128)	Reserved. Contains the relational database name.
DEFAULT_COLLATE_SCHEMA	VARCHAR(128)	Reserved. Contains SYSIBM.
DEFAULT_COLLATE_NAME	VARCHAR(128)	Reserved. Contains IBMDEFAULT.

Column Name	Data Type	Description
CONSTRAINT_CATALOG	VARCHAR(128)	Relational database name
CONSTRAINT_SCHEMA	VARCHAR(128)	Name of the schema containing the constraint
CONSTRAINT_NAME	VARCHAR(128)	Name of the constraint
CHECK_CLAUSE	VARCHAR(2000) Nullable	Text of the check constraint clause Contains the null value if the check clause cannot be contained in the column without truncation.

Column Name	Data Type	Description
TABLE_CATALOG	VARCHAR(128)	Relational database name
TABLE_SCHEMA	VARCHAR(128)	Name of the SQL schema containing the table or view
TABLE_NAME	VARCHAR(128)	Name of the table or view that contains the column
COLUMN_NAME	VARCHAR(128)	Name of the column
ORDINAL_POSITION	INTEGER	Numeric place of the column in the table or view, ordered from left to right
COLUMN_DEFAULT	VARCHAR(2000) Nullable	The default value of a column, if one exists. If the default value of the column cannot be represented without truncation, then the value of the column is the string 'TRUNCATED'. The default value is stored in character form. The following special values also exist: CURRENT_DATE The default value is the current date. CURRENT_TIME The default value is the current time. CURRENT_TIMESTAMP The default value is the current timestamp. NULL The default value is the null value and DEFAULT NULL was explicitly specified. USER The default value is the current job user. Contains the null value if: The column has no default value. For example, if the column has an IDENTITY attribute or is a row ID, or A DEFAULT value was not explicitly specified.
IS_NULLABLE	VARCHAR(3)	Indicates whether the column can contain null values: NO The column cannot contain null values. YES The column can contain null values.
DATA_TYPE	VARCHAR(128)	Type of column: BIGINT Big number INTEGER Large number SMALLINT Small number DECIMAL Packed decimal NUMERIC Zoned decimal DOUBLE PRECISION Double-precision floating point REAL Single-precision floating point CHARACTER Fixed-length character string CHARACTER VARYING Varying-length character string CHARACTER LARGE OBJECT Character large object string GRAPHIC Fixed-length graphic string GRAPHIC VARYING Varying-length graphic string DOUBLE-BYTE CHARACTER LARGE OBJECT Double-byte character large object string BINARY Fixed-length binary string BINARY VARYING Varying-length binary string BINARY LARGE OBJECT Binary large object string DATE Date TIME Time TIMESTAMP Timestamp DATALINK Datalink ROWID Row ID USER-DEFINED Distinct type
CHARACTER_MAXIMUM_LENGTH	INTEGER Nullable	Maximum length of the string for binary, character and graphic string data types. Contains the null value if the column is not a string.
CHARACTER_OCTET_LENGTH	INTEGER Nullable	Number of bytes for binary, character and graphic string data types. Contains the null value if the column is not a string.
NUMERIC_PRECISION	INTEGER Nullable	The precision of all numeric columns. 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 column is not numeric.
NUMERIC_PRECISION_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 column is not numeric.
NUMERIC_SCALE	INTEGER Nullable	Scale of numeric data. Contains the null value if the column is not decimal, numeric, or binary.
DATETIME_PRECISION	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 column is not a date, time, or timestamp.
INTERVAL_TYPE	VARCHAR(128) Nullable	Reserved. Contains the null value.
INTERVAL_PRECISION	INTEGER Nullable	Reserved. Contains the null value.
CHARACTER_SET_CATALOG	VARCHAR(128) Nullable	Relational database name Contains the null value if the column is not a string.
CHARACTER_SET_SCHEMA	VARCHAR(128) Nullable	The schema name of the character set. Contains SYSIBM. Contains the null value if the column is not a string.
CHARACTER_SET_NAME	VARCHAR(128) Nullable	The character set name. Contains the null value if the column is not a string.
COLLATION_CATALOG	VARCHAR(128) Nullable	Relational database name Contains the null value if the column is not a string.
COLLATION_SCHEMA	VARCHAR(128) Nullable	The schema of the collation. Contains SYSIBM. Contains the null value if the column is not a string.
COLLATION_NAME	VARCHAR(128) Nullable	The collation name. Contains IBMBINARY. Contains the null value if the column is not a string.
DOMAIN_CATALOG	VARCHAR(128) Nullable	Reserved. Contains the null value.
DOMAIN_SCHEMA	VARCHAR(128) Nullable	Reserved. Contains the null value.
DOMAIN_NAME	VARCHAR(128) Nullable	Reserved. Contains the null value.
UDT_CATALOG	VARCHAR(128) Nullable	The relational database name if this is a distinct type. Contains the null value if this is not a distinct type.
UDT_SCHEMA	VARCHAR(128) Nullable	The name of the schema if this is a distinct type. Contains the null value if this is not a distinct type.
UDT_NAME	VARCHAR(128) Nullable	The name of the distinct type. Contains the null value if this is not a distinct type.
SCOPE_CATALOG	VARCHAR(128) Nullable	Reserved. Contains the null value.
SCOPE_SCHEMA	VARCHAR(128) Nullable	Reserved. Contains the null value.
SCOPE_NAME	VARCHAR(128) Nullable	Reserved. Contains the null value.
MAXIMUM_CARDINALITY	INTEGER Nullable	Reserved. Contains the null value.
DTD_IDENTIFIER	VARCHAR(128) Nullable	A unique internal identifier for the column.
IS_SELF_REFERENCING	VARCHAR(3)	Reserved. Contains 'NO'.

Column Name	Data Type	Description
SPECIFIC_CATALOG	VARCHAR(128)	Relational database name
SPECIFIC_SCHEMA	VARCHAR(128)	Schema name of the routine instance
SPECIFIC_NAME	VARCHAR(128)	Specific name of the routine instance
ORDINAL_POSITION	INTEGER	Numeric place of the parameter in the parameter list, ordered from left to right.
PARAMETER_MODE	VARCHAR(5)	The type of the parameter: IN This is an input parameter. OUT This is an output parameter. INOUT This is an input/output parameter.
IS_RESULT	VARCHAR(3)	Reserved. Contains 'NO'.
AS_LOCATOR	VARCHAR(3)	Indicates whether the parameter was specified as a locator. NO The parameter was not specified as a locator. YES The parameter was specified as a locator.
PARAMETER_NAME	VARCHAR(128) Nullable	The name of the parameter Contains the null value if the parameter does not have a name.
FROM_SQL_SPECIFIC_CATALOG	VARCHAR(128) Nullable	Reserved. Contains the null value.
FROM_SQL_SPECIFIC_SCHEMA	VARCHAR(128) Nullable	Reserved. Contains the null value.
FROM_SQL_SPECIFIC_NAME	VARCHAR(128) Nullable	Reserved. Contains the null value.
TO_SQL_SPECIFIC_CATALOG	VARCHAR(128) Nullable	Reserved. Contains the null value.
TO_SQL_SPECIFIC_SCHEMA	VARCHAR(128) Nullable	Reserved. Contains the null value.
TO_SQL_SPECIFIC_NAME	VARCHAR(128) Nullable	Reserved. Contains the null value.
DATA_TYPE	VARCHAR(128) Nullable	Type of the parameter: BIGINT Big number INTEGER Large number SMALLINT Small number DECIMAL Packed decimal NUMERIC Zoned decimal DOUBLE PRECISION Floating point; DOUBLE PRECISION REAL Floating point; REAL CHARACTER Fixed-length character string CHARACTER VARYING Varying-length character string CHARACTER LARGE OBJECT Character large object string GRAPHIC Fixed-length graphic string GRAPHIC VARYING Varying-length graphic string DOUBLE-BYTE CHARACTER LARGE OBJECT Double-byte character large object string BINARY Fixed-length binary string BINARY VARYING Varying-length binary string BINARY LARGE OBJECT Binary large object string DATE Date TIME Time TIMESTAMP Timestamp DATALINK Datalink ROWID Row ID USER-DEFINED Distinct Type
CHARACTER_MAXIMUM_LENGTH	INTEGER Nullable	Maximum length of the string for binary, character, and graphic string data types. Contains the null value if the parameter is not a string.
CHARACTER_OCTET_LENGTH	INTEGER Nullable	Number of bytes for binary, character, and graphic string data types. Contains the null value if the parameter is not a string.
CHARACTER_SET_CATALOG	VARCHAR(128) Nullable	Relational database name Contains the null value if the column is not a string.
CHARACTER_SET_SCHEMA	VARCHAR(128) Nullable	The schema name of the character set. Contains 'SYSIBM'. Contains the null value if the column is not a string.
CHARACTER_SET_NAME	VARCHAR(128) Nullable	The character set name. Contains the null value if the column is not a string.
COLLATION_CATALOG	VARCHAR(128) Nullable	Relational database name Contains the null value if the column is not a string.
COLLATION_SCHEMA	VARCHAR(128) Nullable	The schema of the collation. SYSIBM is returned. Contains the null value if the column is not a string.
COLLATION_NAME	VARCHAR(128) Nullable	The collation name. IBMBINARY is returned. Contains the null value if the column is not a string.
NUMERIC_PRECISION	INTEGER Nullable	The precision of all numeric parameters. 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 parameter is not numeric.
NUMERIC_PRECISION_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.
NUMERIC_SCALE	INTEGER Nullable	Scale of numeric data. Contains the null value if not decimal, numeric, or binary parameter.
DATETIME_PRECISION	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 parameter is not a date, time, or timestamp.
INTERVAL_TYPE	VARCHAR(128) Nullable	Reserved. Contains the null value.
INTERVAL_PRECISION	INTEGER Nullable	Reserved. Contains the null value.
UDT_CATALOG	VARCHAR(128) Nullable	The relational database name if this is a distinct type. Contains the null value if this is not a distinct type.
UDT_SCHEMA	VARCHAR(128) Nullable	The name of the schema if this is a distinct type. Contains the null value if this is not a distinct type.
UDT_NAME	VARCHAR(128) Nullable	The name of the distinct type. Contains the null value if this is not a distinct type.
SCOPE_CATALOG	VARCHAR(128) Nullable	Reserved. Contains the null value.
SCOPE_SCHEMA	VARCHAR(128) Nullable	Reserved. Contains the null value.
SCOPE_NAME	VARCHAR(128) Nullable	Reserved. Contains the null value.
MAXIMUM_CARDINALITY	INTEGER Nullable	Reserved. Contains the null value.
DTD_IDENTIFIER	VARCHAR(128) Nullable	A unique internal identifier for the parameter.

Column Name	Data Type	Description
FEATURE_ID	VARCHAR(7) Nullable	ANS and ISO feature ID
FEATURE_NAME	VARCHAR(128)	The name of the ANS and ISO feature.
SUB_FEATURE_ID	VARCHAR(7) Nullable	ANS and ISO subfeature ID
SUB_FEATURE_NAME	VARCHAR(256)	The name of the ANS and ISO subfeature.
IS_SUPPORTED	VARCHAR(3)	Indicates whether the feature is supported: YES This feature is supported. NO This feature is not supported.
IS_VERIFIED_BY	VARCHAR(128) Nullable	Reserved. Contains the null value.
COMMENTS	VARCHAR(2000) Nullable	Reserved. Contains the null value.

Column Name	Data Type	Description
SQL_LANGUAGE_SOURCE	VARCHAR(254)	Name of the standard.
SQL_LANGUAGE_YEAR	VARCHAR(254)	Year in which the standard was approved.
SQL_LANGUAGE_CONFORMANCE	VARCHAR(254) Nullable	Level of conformance. 2 For the 1987 and 1989 standards, indicates that Level 2 conformance is claimed. ENTRY For the 1992 standard, indicates that Entry Level conformance is claimed. CORE For the 2003 standard, indicates that Core Level is conformance is claimed. Contains the null value if conformance is not yet claimed.
SQL_LANGUAGE_INTEGRITY	VARCHAR(254) Nullable	Support of the integrity feature. YES conformance is claimed to the integrity feature NO conformance is not claimed to the integrity feature Contains the null value if the standard does not have a separate integrity feature.
SQL_LANGUAGE_IMPLEMENTATION	VARCHAR(254) Nullable	Reserved. Contains the null value.
SQL_LANGUAGE_BINDING_STYLE	VARCHAR(254)	The style of binding of the SQL language EMBEDDED support for embedded SQL for the language in SQL_LANGUAGE_PROGRAMMING_LANG DIRECT DIRECT SQL is supported (for example Interactive SQL) CLI Support for CLI for the language in SQL_LANGUAGE_PROGRAMMING_LANG
SQL_LANGUAGE_PROGRAMMING_LANG	VARCHAR(254) Nullable	The language supported by EMBEDDED or CLI. C The C language is supported. COBOL The COBOL language is supported. PLI The PL/I language is supported. Contains the null value if the SQL_LANGUAGE_BINDING_STYLE is DIRECT.

Column Name	Data Type	Description
SIZING_ID	INTEGER	ANS and ISO sizing ID
SIZING_NAME	VARCHAR(128)	Name of the ANS and ISO sizing.
SUPPORTED_VALUE	BIGINT Nullable	Indicates the sizing limit. Contains the null value if the sizing limit is not applicable.
COMMENTS	VARCHAR(2000) Nullable	Reserved. Contains the null value.

Column Name	Data Type	Description
USER_DEFINED_TYPE_CATALOG	VARCHAR(128)	Relational database name
USER_DEFINED_TYPE_SCHEMA	VARCHAR(128)	Schema name of the distinct type.
USER_DEFINED_TYPE_NAME	VARCHAR(128)	Name of the user that created the distinct type.
USER_DEFINED_TYPE_CATEGORY	VARCHAR(128)	Indicates the type of user-defined type. Contains 'DISTINCT'.
IS_INSTANTIABLE	VARCHAR(3)	Reserved. Contains 'YES'.
IS_FINAL	VARCHAR(3)	Reserved. Contains 'YES'.
ORDERING_FORM	VARCHAR(4)	Indicates what kind of predicates are allowed when this distinct type is a comparand: FULL All predicates are allowed. NONE No predicates are allowed
ORDERING_CATEGORY	VARCHAR(8)	Reserved. Contains 'MAP'.
ORDERING_ROUTINE_CATALOG	VARCHAR(128) Nullable	Relational database name Contains the null value if the ORDERING_FORM is 'NONE'.
ORDERING_ROUTINE_SCHEMA	VARCHAR(128) Nullable	Reserved. Contains 'SYSIBM'. Contains the null value if the ORDERING_FORM is 'NONE'.
ORDERING_ROUTINE_NAME	VARCHAR(128) Nullable	Reserved. Contains a data type name. Contains the null value if the ORDERING_FORM is 'NONE'.
REFERENCE_TYPE	VARCHAR(16) Nullable	Reserved. Contains the null value.
DATA_TYPE	VARCHAR(128) Nullable	Source data type of the distinct type: BIGINT Big number INTEGER Large number SMALLINT Small number DECIMAL Packed decimal NUMERIC Zoned decimal DOUBLE PRECISION Floating point; DOUBLE PRECISION REAL Floating point; REAL CHARACTER Fixed-length character string CHARACTER VARYING Varying-length character string CHARACTER LARGE OBJECT Character large object string GRAPHIC Fixed-length graphic string GRAPHIC VARYING Varying-length graphic string DOUBLE-BYTE CHARACTER LARGE OBJECT Double-byte character large object string BINARY Fixed-length binary string BINARY VARYING Varying-length binary string BINARY LARGE OBJECT Binary large object string DATE Date TIME Time TIMESTAMP Timestamp DATALINK Datalink ROWID Row ID USER-DEFINED Distinct Type
CHARACTER_MAXIMUM_LENGTH	INTEGER Nullable	Maximum length of the distinct type for binary, character, and graphic string data types. Contains the null value if the distinct type is not a string.
CHARACTER_OCTET_LENGTH	INTEGER Nullable	Number of bytes of the distinct type for binary, character, and graphic string data types. Contains the null value if the distinct type is not a string.
CHARACTER_SET_CATALOG	VARCHAR(128) Nullable	Relational database name of the distinct type. Contains the null value if the distinct type is not a string.
CHARACTER_SET_SCHEMA	VARCHAR(128) Nullable	The schema name of the character set of the distinct type. Contains 'SYSIBM'. Contains the null value if the distinct type is not a string.
CHARACTER_SET_NAME	VARCHAR(128) Nullable	The character set name of the distinct type. Contains the null value if the distinct type is not a string.
COLLATION_CATALOG	VARCHAR(128) Nullable	Relational database name of the distinct type. Contains the null value if the distinct type is not a string.
COLLATION_SCHEMA	VARCHAR(128) Nullable	The schema of the collation of the distinct type. SYSIBM is returned. Contains the null value if the distinct type is not a string.
COLLATION_NAME	VARCHAR(128) Nullable	The collation name of the distinct type. IBMBINARY is returned. Contains the null value if the distinct type is not a string.
NUMERIC_PRECISION	INTEGER Nullable	The precision of the distinct type. 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 distinct type is not numeric.
NUMERIC_PRECISION_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 distinct type is not numeric.
NUMERIC_SCALE	SMALLINT Nullable	Scale of numeric distinct type. Contains the null value if the distinct type is not decimal, numeric, or binary.
DATETIME_PRECISION	INTEGER Nullable	The fractional part of a date, time, or timestamp distinct type. 0 For DATE and TIME data types 6 For TIMESTAMP data types (number of microseconds). Contains the null value if the distinct type is not date, time, or timestamp.
INTERVAL_TYPE	VARCHAR(128) Nullable	Reserved. Contains the null value.
INTERVAL_PRECISION	INTEGER Nullable	Reserved. Contains the null value.
SOURCE_DTD_IDENTIFIER	VARCHAR(128) Nullable	A unique internal identifier for the source data type. Contains the null value if the distinct type is not sourced on another distinct type.
REF_DTD_IDENTIFIER	VARCHAR(256) Nullable	Reserved. Contains the null value.