DECLARE TABLE

Invocation

This statement can only be embedded in an application program. It is not an executable statement.

Authorization

None required.

Description

table-name or view-name

Specifies the name of the table or view to document. If the table is defined in your application program, the description of the table in the SQL statement in which it is defined (for example, CREATE TABLE or DECLARE GLOBAL TEMPORARY TABLE statement) and the DECLARE TABLE statement must be identical.

column-name

Specifies the name of a column of the table or view.

The precompiler uses these names to check for consistency of names within your SQL statements. It also uses the data type to check for consistency of names and data types within your SQL statements.

built-in-type

Specifies the built-in data type of the column. Use one of the built-in data types.

SMALLINT

For a small integer.

INTEGER or INT

For a large integer.

BIGINT

For a big integer.

DECIMAL(integer,integer) or DEC(integer,integer)

DECIMAL(integer) or DEC(integer)

DECIMAL or DEC

For a decimal number. The first integer is the precision of the number. That is, the total number of digits, which can range from 1 to 31. The second integer is the scale of the number. That is, the number of digits to the right of the decimal point, which can range from 0 to the precision of the number.

You can use DECIMAL(p) for DECIMAL(p,0) and DECIMAL for DECIMAL(5,0).

You can also use the word NUMERIC instead of DECIMAL. For example, NUMERIC(8) is equivalent to DECIMAL(8). Unlike DECIMAL, NUMERIC has no allowable abbreviation.

FLOAT(integer)

FLOAT

For a floating-point number. If integer is between 1 and 21 inclusive, the format is single precision floating-point. If the integer is between 22 and 53 inclusive, the format is double precision floating-point.

You can use DOUBLE PRECISION or FLOAT for FLOAT(53).

REAL

For single precision floating-point.

DOUBLE or DOUBLE PRECISION

For double precision floating-point

DECFLOAT( integer)

For a decimal floating-point number. The value of integer must be either 16 or 34 and represents the number of significant digits that can be stored. If integer is omitted, the DECFLOAT column will be capable of representing 34 significant digits.

CHARACTER(integer) or CHAR(integer)

CHARACTER or CHAR

For a fixed-length character string of length integer, which can range from 1 to 255. If the length specification is omitted, a length of 1 character is assumed.

VARCHAR(integer), CHAR VARYING(integer), or CHARACTER VARYING(integer)

For a varying-length character string of maximum length integer, which can range from 1 to the maximum record size minus 10 bytes. See Table 3 to determine the maximum record size.

CLOB(integer [K|M|G]), CHAR LARGE OBJECT(integer [K|M|G]), or CHARACTER LARGE OBJECT(integer [K|M|G])

CLOB, CHAR LARGE OBJECT, or CHARACTER LARGE OBJECT

For a character large object (CLOB) string of the specified maximum length in bytes. The maximum length must be in the range of 1 to 2 147 483 647. A CLOB column has a varying-length. It cannot be referenced in certain contexts regardless of its maximum length. For more information, see Restrictions using LOBs.

When integer is not specified, the default length is 1M. The maximum value that can be specified for integer depends on whether a units indicator is also specified as shown in the following list.

integer

The maximum value for integer is 2 147 483 647. The maximum length of the string is integer.

integer K

The maximum value for integer is 2 097 152. The maximum length is 1024 times integer.

integer M

The maximum value for integer is 2048. The maximum length is 1 048 576 times integer.

integer G

The maximum value for integer is 2. The maximum length is 1 073 741 824 times integer.

If you specify a value that evaluates to 2 gigabytes (2 147 483 648), DB2 uses a value that is one byte less, or 2 147 483 647.

GRAPHIC(integer)

GRAPHIC

For a fixed-length graphic string of length integer, which can range from 1 to 127. If the length specification is omitted, a length of 1 character is assumed.

VARGRAPHIC(integer)

For a varying-length graphic string of maximum length integer, which must range from 1 to n/2, where n is the maximum row size minus 2 bytes.

DBCLOB(integer [K|M|G])

DBCLOB

For a double-byte character large object (DBCLOB) string of the specified maximum length in double-byte characters. The maximum length must be in the range of 1 through 1 073 741 823. A DBCLOB column has a varying-length. It cannot be referenced in certain contexts regardless of its maximum length. For more information, see Restrictions using LOBs.

When integer is not specified, the default length is 1M. The meaning of integer K|M|G is similar to CLOB. The difference is that the number specified is the number of double-byte characters.

BINARY(integer)

A fixed-length binary string of length integer. The integer can range from 1 through 255. If the length specification is omitted, a length of 1 byte is assumed.

BINARY VARYING(integer) or VARBINARY(integer)

A varying-length binary string of maximum length integer, which can range from 1 through 32704. The length is limited by the page size of the table space.

BLOB (integer [K|M|G] or BINARY LARGE OBJECT(integer [K|M|G])

BLOB or BINARY LARGE OBJECT

For a binary large object (BLOB) string of the specified maximum length in bytes. The maximum length must be in the range of 1 through 2 147 483 647. A BLOB column has a varying-length. It cannot be referenced in certain contexts regardless of its maximum length. For more information, see Restrictions using LOBs.

When integer is not specified, the default length is 1M. The meaning of integer K|M|G is the same as for CLOB.

DATE

For a date.

TIME

For a time.

TIMESTAMP(integer) WITHOUT TIME ZONE

For a timestamp. integer specifies the optional timestamp precision attribute and must be in the range from 0 to 12. The timestamp precision denotes the number of fractional second digits that are included in the timestamp. The default is 6.

TIMESTAMP(integer) WITH TIME ZONE

For a timestamp with time zone. integer specifies the optional timestamp precision attribute and must be in the range from 0 to 12. The timestamp precision denotes the number of fractional second digits that are included in the timestamp. The default is 6.

ROWID

For a row ID type.

A table can have only one ROWID column. The values in a ROWID column are unique for every row in the table and cannot be updated. You must specify NOT NULL with ROWID.

XML

For an XML document. Only well-formed XML documents can be inserted into an XML column.

If the XML column is the first XML column that you create for the table, a BIGINT DOCID column is implicitly created and is used to store a unique document identifier for the XML columns of a row.

distinct-type-name

Specifies the distinct type (user-defined data type) of the column. An implicit or explicit schema name qualifies the name.

NOT NULL

Specifies that the column does not allow null values and does not provide a default value.

NOT NULL WITH DEFAULT

Specifies that the column does not allow null values but provides a default value.

Notes

Error handling during processing: If an error occurs during the processing of the DECLARE TABLE statement, a warning message is issued, and the precompiler continues processing your source program.

Documenting a distinct type column: Although you can specify the name of a distinct type as the data type of a column in the DECLARE TABLE statement, use the built-in data type on which the distinct type is based instead. Using the base type enables the precompiler to check the embedded SQL statements for errors; otherwise, error checking is deferred until bind time.

To determine the source data type of the distinct type, check the value of column SOURCETYPE in catalog table SYSDATATYPES.