Notes

Table attributes: Tables are created as physical files. When a table is created, the file wait time and record wait time attributes are set to the default that is specified on the WAITFILE and WAITRCD keywords of the Create Physical File (CRTLF) command.

SQL tables are created so that space used by deleted rows will be reclaimed by future insert requests. This attribute can be changed via the command CHGPF and specifying the REUSEDLT(*NO) parameter. For more information about the CHGPF command, see the CL Reference information in the Programming category of the iSeries Information Center.

A distributed table is created on all of the servers across which the table is distributed. For more information about distributed tables, see the DB2(R) Multisystem book.

Table journaling: When a table is created, journaling may be automatically started.

Table ownership: If SQL names were specified:

If system names were specified, the owner of the table is the user profile or group user profile of the job executing the statement.

Table authority: If SQL names are used, tables are created with the system authority of *EXCLUDE to *PUBLIC. If system names are used, tables are created with the authority to *PUBLIC as determined by the create authority (CRTAUT) parameter of the schema.

If the owner of the table is a member of a group profile (GRPPRF keyword) and group authority is specified (GRPAUT keyword), that group profile will also have authority to the table.

Owner privileges: The owner of the table has all table privileges (see GRANT (Table or View Privileges)) with the ability to grant these privileges to others.

Using an identity column: When a table has an identity column, the database manager can automatically generate sequential numeric values for the column as rows are inserted into the table. Thus, identity columns are ideal for primary keys.

Identity columns and ROWID columns are similar in that both types of columns contain values that the database manager generates. ROWID columns can be useful in direct-row access. ROWID columns contain values of the ROWID data type, which returns a 40-byte VARCHAR value that is not regularly ascending or descending. ROWID data values are therefore not well suited to many application uses, such as generating employee numbers or product numbers. For data that does not require direct-row access, identity columns are usually a better approach, because identity columns contain existing numeric data types and can be used in a wide variety of uses for which ROWID values would not be suitable.

When a table is recovered to a point-in-time (using RMVJRNCHG), it is possible that a large gap in the sequence of generated values for the identity column might result. For example, assume a table has an identity column that has an incremental value of 1 and that the last generated value at time T1 was 100 and the database manager subsequently generates values up to 1000. Now, assume that the table is recovered back to time T1. The generated value of the identity column for the next row that is inserted after the recovery completes will be 1001, leaving a gap from 100 to 1001 in the values of the identity column.

When CYCLE is specified duplicate values for a column may be generated even when the column is GENERATED ALWAYS, unless a unique constraint or unique index is defined on the column.

Creating materialized query tables: To ensure that the materialized query table has data before being used by a query:

The isolation level at the time when the CREATE TABLE statement is executed is the isolation level for the materialized query table. The isolation-clause can be used to explicitly specify the isolation level.

Partitioned table performance: The larger the number of partitions in a partitioned table, the greater the overhead in SQL data change and SQL data statements. You should create a partitioned table with the minimum number of partitions that are required to minimize this overhead. It is also highly recommended that a parallelism degree greater than one be considered when accessing a partitioned table.

Syntax alternatives: The following keywords are synonyms supported for compatibility to prior releases. These keywords are non-standard and should not be used:

Maximum row sizes

There are two maximum row size restrictions referred to in the description of column-definition.

To determine the length of a row buffer and/or row data add the corresponding length of each column of that row based on the byte counts of the data type.

The follow table gives the byte counts of columns by data type for columns that do not allow null values. If any column allows null values, one byte is required for every eight columns.

Table 52. Byte Counts of Columns by Data Type
Data Type Row Buffer Byte Count Row Data Byte Count
SMALLINT 2 2
INTEGER 4 4
BIGINT 8 8
DECIMAL( p, s) The integral part of (p/2) + 1 The integral part of (p/2) + 1
NUMERIC( p, s) p p
FLOAT (single precision) 4 4
FLOAT (double precision) 8 8
CHAR( n) n n
VARCHAR( n) n+2 n+2
CLOB( n) 29+pad n+29
GRAPHIC(n) n*2 n*2
VARGRAPHIC (n) n*2+2 n*2+2
DBCLOB( n) 29+pad n*2+29
BINARY( n) n n
VARBINARY( n) n+2 n+2
BLOB( n) 29+pad n+29
DATE 10 4
TIME 8 3
TIMESTAMP 26 10
DATALINK( n) n+24 n+24
ROWID 42 28
distinct-type The byte count for the source type. The byte count for the source type.
Notes:

pad is a value from 1 to 15 necessary for boundary alignment.

Precision as described to the database:

LONG VARCHAR and LONG VARGRAPHIC

The non-standard syntax of LONG VARCHAR and LONG VARGRAPHIC is supported, but deprecated. The alternative standard syntax of VARCHAR(integer) and VARGRAPHIC(integer), is preferred. VARCHAR(integer) and VARGRAPHIC(integer) are recommended. After the CREATE TABLE statement is processed, the database manager considers a LONG VARCHAR column to be VARCHAR and a LONG VARGRAPHIC column to be VARGRAPHIC. The maximum length is calculated in a product-specific fashion that is not portable.

LONG VARCHAR 68
For a varying length character string whose maximum length is determined by the amount of space available in the row.
LONG VARGRAPHIC 68
For a varying length graphic string whose maximum length is determined by the amount of space available in the row.

The maximum length of a LONG column is determined as follows. Let:

The length of each LONG VARCHAR column is INTEGER((32716 - i-((k+7)/8))/j).

The length of each LONG VARGRAPHIC column is determined by taking the length calculated for a LONG VARCHAR column and dividing it by 2. The integer portion of the result is the length.


68.
This option is provided for compatibility with other products. It is recommended that VARCHAR(integer) or VARGRAPHIC(integer) be specified instead.