DECLARE TABLE statement

The DECLARE TABLE statement is used for application program documentation. It also provides the precompiler with information used to check your embedded SQL statements. (The DCLGEN subcommand can be used to generate declarations for tables and views described in any accessible Db2 catalog.

For more information about DCLGEN, see Declaring table and view definitions and DCLGEN (declarations generator) subcommand (DSN).)

Invocation for DECLARE TABLE

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

Authorization for DECLARE TABLE

None required.

Syntax for DECLARE TABLE

built-in-type:

Description for DECLARE TABLE

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 1–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 in the range 1–21 inclusive, the format is single precision floating-point. If the integer is in the range 22–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 1–255. If the length specification is omitted, a length of 1 character is assumed.

CCSID 1208: FL 500Specifies that the column is a Unicode column encoded in UTF-8. This clause must not be specified for an ASCII or Unicode table.

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 5 to determine the maximum record size.

CCSID 1208: Specifies that the column is a Unicode column encoded in UTF-8. This clause must not be specified for an ASCII or Unicode table.

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 1–2147483647. 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 2147483647. The maximum length of the string is integer.
integer K: The maximum value for integer is 2097152. 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.

integer can be separated from K, M, or G by 0 or more spaces.

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

CCSID 1208: FL 500Specifies that the column is a Unicode column encoded in UTF-8. This clause must not be specified for an ASCII or Unicode table.

GRAPHIC(integer)

GRAPHIC

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

CCSID 1200: FL 500Specifies that the column is a Unicode column encoded in UTF-16. This clause must not be specified for an ASCII or Unicode table.

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.

CCSID 1200: Specifies that the column is a Unicode column encoded in UTF-16. This clause must not be specified for an ASCII or Unicode table.

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–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.

integer can be separated from K, M, or G by 0 or more spaces.

CCSID 1200: Specifies that the column is a Unicode column encoded in UTF-16. This clause must not be specified for an ASCII or Unicode table.

BINARY(integer)

A fixed-length binary string of length integer. The integer can range 1–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 1–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–2147483647. 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.

integer can be separated from K, M, or G by 0 or more spaces.

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 0–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 0–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.

Start of change A table can contain at most two ROWID columns. If a table contains two ROWID columns c, one column is implicitly generated by Db2 and the other column is explicitly defined as a ROWID without the IMPLICITLY HIDDEN attribute. End of change 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 for DECLARE TABLE

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.

Examples for DECLARE TABLE

Example 1: Issue a DECLARE TABLE statement for the sample employee table, DSN8C10.EMP.

EXEC SQL DECLARE DSN8C10.EMP TABLE
     (EMPNO     CHAR(6)     NOT NULL,
      FIRSTNME  VARCHAR(12) NOT NULL,
      MIDINIT   CHAR(1)     NOT NULL,
      LASTNAME  VARCHAR(15) NOT NULL,
      WORKDEPT  CHAR(3)             ,
      PHONENO   CHAR(4)             ,
      HIREDATE  DATE                ,
      JOB       CHAR(8)             ,
      EDLEVEL   SMALLINT            ,
      SEX       CHAR(1)             ,
      BIRTHDATE DATE                ,
      SALARY    DECIMAL(9,2)        ,
      BONUS     DECIMAL(9,2)        ,
      COMM      DECIMAL(9,2)        );

Start of change Example 2: Issue a DECLARE TABLE statement for a table that includes a column with a distinct type. End of change

Start of change Assume that table CANADIAN_SALES keeps information for your company's sales in Canada. The table includes one column, TOTAL, that is defined as a distinct type. The distinct type CANADIAN_DOLLAR was created with the following definition: End of change

CREATE TYPE CANADIAN_DOLLAR AS DECIMAL(9,2);

Start of change The CANADIAN_SALES table was created with the following definition, which uses the distinct type CANADIAN_DOLLAR as the data type for the TOTAL column. End of change

CREATE TABLE CANADIAN_SALES
     (PRODUCT_ITEM   INTEGER,
      MONTH          INTEGER,
      YEAR           INTEGER,
      TOTAL          CANADIAN_DOLLAR); -- TOTAL column data type is a distinct type

Start of change A DECLARE TABLE statement must include only built-in data types. Therefore, in the DECLARE TABLE statement for the CANADIAN_SALES table, you need to use the source data type for the CANADIAN_DOLLAR distinct type, which is DECIMAL(9,2). End of change

DECLARE TABLE CANADIAN_SALES
     (PRODUCT_ITEM   INTEGER,
      MONTH          INTEGER,
      YEAR           INTEGER,
      TOTAL          DECIMAL(9,2)); -- Define the column using the source built-in data type