Table properties

Properties for data server table objects

For more information about specific syntax, see the reference information that is included with each database product. Syntax definitions for some database vendors or versions might differ from the following descriptions.

General page
Property Description
Name Specifies a name for the object.
Label Specifies a label. This label is displayed in visual diagrams.
Schema Shows the relational schema under which this object was created.
DB2® for z/OS® only: Audit Specifies an option for the AUDIT clause. You can select ALL, CHANGES, or NONE. Identifies the types of access to this table that causes auditing to be performed.
Data capture Specifies an option for the DATA CAPTURE clause. You can select CHANGES or NONE. Specifies whether the logging of the following actions on the table is augmented by additional information:
  • SQL data change operations
  • Adding columns (using the ADD COLUMN clause of the ALTER TABLE statement)
  • Changing columns (using the ALTER COLUMN clause of the ALTER TABLE statement)
DB2 for z/OS only: Edit procedure Specifies a program name for the EDITPROC clause. Designates this program name as the edit routine for the table. The edit routine, which must be provided by the current server's site, is invoked during the execution of an SQL data change statement or LOAD and all row retrieval operations on the table.
DB2 for z/OS only: Encoding Specifies an encoding scheme. You can select EBCDIC, ASCII, UNICODE, or NONE. This setting specifies which CCSIDs are used to encode the data.
DB2 for z/OS only: Valid procedure Specifies a program name for the VALIDPROC clause. Designates this program name as the validation exit routine for the table.
DB2 for z/OS only: Volatile Select this option to specify that index access is used on this table whenever possible for SQL operations. However, be aware that list prefetch and certain other optimization techniques are disabled when VOLATILE is used. If you do not select this option, SQL access to this table is based on the current statistics.
DB2 for z/OS only: With Restrict On Drop Select this option to specify that table (or table spaces or databases that contain the table) is not dropped until a restriction is removed.
DB2 for z/OS only: Append Specifies that data rows are placed into the table by disregarding the clustering during SQL INSERT and online LOAD operations. Rather than attempting to insert rows in cluster-preserving order, rows are appended at the end of the table or appropriate partition.
Restrict On Drop Select this option to specify that table (or table spaces or databases that contain the table) is not dropped until a restriction is removed.
DB2 V10.5 for Linux, UNIX, and Windows only: Table organization Specifies the organization of the table, which is either row-organized or column-organized. The default for the database is specified by the dft_table_org database configuration parameter. To view and configure your database configuration parameters, right-click your database and then click Set Up and Configure > Configure.
Compress Specify whether to use compression on the table. Compression saves disk storage space by using fewer database pages to store data.
DB2 V10.1 for Linux, UNIX, and Windows only: Row compression Specify whether to use static or adaptive compression on the table:
  • Classic row compression, also referred to as static compression, compresses data rows by replacing patterns of values that repeat across rows with shorter symbol strings.
  • Adaptive compression improves upon the compression rates that can be achieved using classic row compression by itself. Adaptive compression incorporates classic row compression; however, it also works on a page-by-page basis to further compress data and offers the most dramatic possibilities for storage savings.
Value Compression If you attempt to compress columns that contain system default values, you must also select the Value Compression option.

Value compression optimizes space usage for the representation of data, and the storage structures used internally by the database management system to store data. Value compression removes duplicate entries for a value and stores only one copy. The stored copy keeps track of the location of any references to the stored value.

InfoSphere® BigInsights™ only: External Specifies whether the table that is being created must point to a distributed file system that contains the data files.
InfoSphere BigInsights only: Location Specifies that a specific distributed file system directory must be used to store data files.
Columns page
In edit mode, use the grid and the toolbar buttons on this page to add, delete, change the order, and modify properties for columns.
  • Click New to add a column to the bottom of the grid.
  • Click Delete to delete a column.
  • Select a column in the grid and click Move Column Up or Move Column Down to change the order of columns.
  • Use the grid to modify the properties of a column. Double-click a cell or click a check box in a cell to edit the cell contents.
    Note: You can also select a single column in a view to view or modify these and other properties in the Properties view for the column. When you view or modify properties from the Properties view for a column, additional properties are available.
    Property Description
    Primary Key Designates a column as a primary key column.
    Domain Specifies a domain data type for the column. You must associate a domain model with the data design project or define domains in your physical model to see domain data types in this list.
    Data type Specifies a base data type for the column.
    Length Specifies a length for the column. Only available for data types that requires a length specification.
    Scale Specify a scale for the column. Only available for data types that requires a scale specification.
    Not Null Specifies that the column cannot contain null values.
    Generated Specifies that values for this column are generated by the DB2 database system.
    Default Value - Generate Expression Specifies a default value or expression for the column. If the Generated check box is selected for this column, this field designates an expression. If the Generated check box is not selected for this column, this field designates a default value.
Volumetrics page (DB2 for Linux, UNIX, and Windows or DB2 for z/OS only)
Property Description
Initial number of rows In edit mode, enter the initial number of rows for this table.
Row growth per month In edit mode, enter the estimated increased number of rows that you expect each month for this table.
Maximum number of rows In edit mode, enter the maximum number of rows for this table.
Size estimation (Projected in month) In edit mode, enter the month for which you want to calculate size and the estimations in the table are updated.
  • Initial size is calculated based on the initial number of rows and the average row length.
  • Projected size is calculated based on the initial number of rows, the row growth per month and the average row length for the projected month.
  • Maximum size is calculated based on the maximum number of rows and the average row length.
  • Average row length is calculated based on the average length of the columns in the table.
Privileges page (DB2 for Linux, UNIX, and Windows)
Specify which users, groups, and roles can access the table.
Distribution Key page (DB2 for Linux, UNIX, and Windows, DB2 for z/OS, or Oracle only)
In edit mode, use the grid and the toolbar buttons on this page to add, delete, change the order, and modify properties for partition key columns.
Prerequisites for setting partition key properties:
  • The table must be partitioned to a table space.
  • The table must contain at least one column.
  • The table space must be partitioned.
  • To add a column to the partition key, click the ellipsis toolbar button, select columns in the window that opens, and click OK.
  • For DB2 only: To change the order of columns, select a column in the grid and click Move Column Up or Move Column Down.
  • For DB2 only: Use the grid to modify the properties of a partition key column. Double-click a cell or click a check box in a cell to edit the cell contents.
    Property Description
    DB2 for Linux, UNIX, and Windows, DB2 for iSeries®, or Oracle only: Maintained By Select an option for the PARTITIONING KEY clause. You can select HASHING for DB2 databases, or HASH, RANGE, or LIST for Oracle databases.
    DB2 for z/OS only: Partition by range Specifies the range partitioning scheme for the table (the columns that are used to partition the data). When this clause is specified, the table space is complete, and it is not necessary to create a partitioned index on the table.
    DB2 for z/OS only: Partition by size Specifies that the table is created in a partition-by-growth table space.
    Name or Column Specifies the name of the column.
    Data type Specifies the data type of the column.
    DB2 for z/OS only: Ordering type Specifies an option for ordering the entries by column. You can select ASC (ASCENDING) or DESC (DESCENDING).
    DB2 for z/OS only: Nulls last Specifies using the NULLS LAST clause. Specifies that null values are treated as positive infinity for purposes of comparison.
    DB2 for z/OS only: Ending limit Specifies an ending limit for each partition.
Data Partitions page (DB2 for Linux, UNIX, and Windows or Oracle only)
In edit mode, use the toolbar buttons and the grid to add, remove, or modify data partitions.
Prerequisites for creating data partitions:
  • You must create at least one partition key.
  • To add a data partition, click the ellipsis [...] toolbar button, specify partition details in the window that opens. In Oracle only, you can add one or more data partitions individually, or add multiple partitions together, as a group.

    For HASH or USING HASHING partitions: You must specify a partition name, and if you select a LOB table space you must select at least one LOB column.

    For LIST or RANGE partitions (Oracle only): You must specify a partition name, column values, and if you select a LOB table space you must select at least one LOB column.

  • For DB2 only: To change the order of columns, select a column in the grid and click Move Column Up or Move Column Down.
  • For DB2 only: Use the grid to modify the properties of a partition key column. Double-click a cell or click a check box in a cell to edit the cell contents.
    Property Description
    DB2 for Linux, UNIX, and Windows only: Column For a partition expression, identifies a column of the data partitioning key.
    DB2 for Linux, UNIX, and Windows only: Nulls For a partition expression, indicates how NULL values compare (high or low).
    For Oracle only: Individual partitions Enables the table to have one or more partitions that you specify individually, one at a time.
    For Oracle only: Partitions by quantity Enables the table to have multiple partitions that you specify together, as a group. You can specify the number of partitions and select the table spaces for them.
    Partition Name Specifies the name of the partition.
    DB2 for Linux, UNIX, and Windows only: Starting value For a RANGE partition, specifies the low end of the range.
    DB2 for Linux, UNIX, and Windows only: Ending value For a RANGE partition, specifies the high end of the range.
    DB2 for Linux, UNIX, and Windows only: Inclusiveness For a RANGE partition, indicates that the specified range values are to be included in the data partition, or indicates that the specified constant values are to be excluded from the data partition.
    DB2 for Linux, UNIX, and Windows only: Regular table space Specifies the regular table space where the partition are stored. The named table space must have the same page size, be in the same database partition group, and manage space in the same way as the other table spaces of the partitioned table (SQLSTATE 42838); it must be a table space on which the authorization ID of the statement holds the USE privilege. If this clause is not specified, a table space is automatically assigned to the partition.
    LOB Table Space Specifies LOB table spaces under which this partition was created.
    Oracle only: Values Specifies the column values for the partition.
    Oracle only: Table Space Specifies the table space under which this partition was created.
    Oracle only: LOB Columns Specifies LOB columns that are included in the partition.
Table spaces page (DB2 for z/OS only)
Select a table space. Click Show Properties Show Properties icon to open the Properties view for the selected object.
Table spaces page (DB2 for Linux, UNIX, and Windows, or DB2 for iSeries only)
Property Description
Regular Specifies a table space for the table.
Index Specifies a table space in which any indexes on the table are created.
LOB Specifies a table space in which the values of any long columns are stored.
MDC page (DB2 for Linux, UNIX, and Windows only)
Specifies the multidimensional clustering options. Create dimensions within dimensional models.
Dimensional page (DB2 for Linux, UNIX, and Windows only)
Allows you to change the table type within a dimensional model.
Restriction: You can modify only the options on this page if dimensional notation is enabled on your data model.
Row Format page (InfoSphere BigInsights only)
Specifies the format of data rows, DELIMITED or SERDE.
DELIMITED
Specifies a delimiter at the table level for structured fields, for array items, for map keys, and for line termination.
Property Description
Fields terminated by Specifies a delimiter for structure fields.
Collection items terminated by Specifies a delimiter for array items.
Map keys terminated by Specifies a delimiter for map keys.
Lines terminated by Specifies a delimiter for line termination.
SERDE
SerDe is short for Serializer/Deserializer and allows data to be read from a table, and write it back out to HDFS in any custom format.
Property Description
Class name Specifies the name of a Java™ class in the Big SQL server CLASSPATH that implements the Hive SerDe interface org.apache.hadoop.hive.serde2.SerDe.
Properties SerDe properties associated with the SerDe class.
Partition By page (InfoSphere BigInsights only)

Specifies the list of columns that can be used as partitioned columns in the table.

Relationships page
Shows objects that are related to the object and information about the relationship. These fields cannot be edited. Select a relationship in the grid and click Show Properties Show Properties icon to open the Properties view for a related object.
Statistics page
Shows the statistics for the table.
Documentation page
Specifies user-supplied information about the object.
Annotation page (in edit mode only)
Add, modify, or delete properties.

Feedback