DECLARE TABLE
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) (DSN).)
Invocation
This statement can only be embedded in an application program. It is not an executable statement.
Authorization
None required.
Syntax
>>-DECLARE--+-table-name-+--------------------------------------> '-view-name--' .-,--------------------------------------------------------------. V | >--TABLE(---column-name--+-| built-in-type |--+--+-----------------------+-+-)->< '-distinct-type-name-' +-NOT NULL--------------+ '-NOT NULL WITH DEFAULT-'
built-in-type:
>>-+-+-SMALLINT----+-------------------------------------------+->< | +-+-INTEGER-+-+ | | | '-INT-----' | | | '-BIGINT------' | | .-(5,0)--------------------. | +-+-DECIMAL-+--+--------------------------+-----------------+ | +-DEC-----+ '-(integer-+-----------+-)-' | | '-NUMERIC-' '-, integer-' | | .-(53)------. | +-+-FLOAT--+-----------+--+---------------------------------+ | | '-(integer)-' | | | +-REAL------------------+ | | | .-PRECISION-. | | | '-DOUBLE--+-----------+-' | | .-(34)-. | +-DECFLOAT--+------+----------------------------------------+ | '-(16)-' | | .-(1)-------. | +-+-+-+-CHARACTER-+--+-----------+----------+-------------+-+ | | | '-CHAR------' '-(integer)-' | | | | | '-+-+-CHARACTER-+--VARYING-+--(integer)-' | | | | | '-CHAR------' | | | | | '-VARCHAR----------------' | | | | .-(1M)-------------. | | | '-+-+-CHARACTER-+--LARGE OBJECT-+--+------------------+-' | | | '-CHAR------' | '-(integer-+---+-)-' | | '-CLOB------------------------' +-K-+ | | +-M-+ | | '-G-' | | .-(1)-------. | +-+-GRAPHIC--+-----------+-------+--------------------------+ | | '-(integer)-' | | | +-VARGRAPHIC--(--integer--)----+ | | | .-(1M)-------------. | | | '-DBCLOB--+------------------+-' | | '-(integer-+---+-)-' | | +-K-+ | | +-M-+ | | '-G-' | | .-(1)-------. | +-+-BINARY--+-----------+-------------------------+---------+ | | '-(integer)-' | | | +-+-BINARY VARYING-+-(integer)------------------+ | | | '-VARBINARY------' | | | | .-(1M)-------------. | | | '-+-BINARY LARGE OBJECT-+--+------------------+-' | | '-BLOB----------------' '-(integer-+---+-)-' | | +-K-+ | | +-M-+ | | '-G-' | +-+-DATE------+---------------------------------------------+ | +-TIME------+ | | '-TIMESTAMP-' | +-ROWID-----------------------------------------------------+ '-XML-------------------------------------------------------'
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.
Examples
EXEC SQL DECLARE DSN8A10.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) );
CREATE TABLE CANADIAN_SALES
(PRODUCT_ITEM INTEGER,
MONTH INTEGER,
YEAR INTEGER,
TOTAL CANADIAN_DOLLAR);
CREATE TYPE CANADIAN_DOLLAR AS DECIMAL(9,2);
DECLARE TABLE CANADIAN_SALES
(PRODUCT_ITEM INTEGER,
MONTH INTEGER,
YEAR INTEGER,
TOTAL DECIMAL(9,2);