Table

You can use the table object options to create, template, alter, and drop DB2 tables. A DB2 table is a collection of rows all having the same columns. All data in DB2 is stored in tables, including the system catalog information. A DB2 table is the main object type within DB2.
carcudb219
You can use the table object options to create, template, alter, and drop DB2 tables. A DB2 table is a collection of rows all having the same columns. All data in DB2 is stored in tables, including the system catalog information. A DB2 table is the main object type within DB2.
The following functionality is provided for creating and maintaining DB2 tables:
  • DB2 Object Selection Help
    When defining a table, the object names of the table's database and tablespace must be entered. Instead of entering a specific object name, selection criteria can be entered, from which a list of objects appears.
  • Column Selection Help
    You can retrieve other table columns that have already been defined into the current table definition. This lets you create standard column definitions that can be copied into other tables.
  • Row Size Indicator
    Automatically displays the effects different data type decisions will have on the total row size as a table is created or changed.
  • Column Editor
    When defining columns, any of the standard ISPF editor commands can be used to copy, replicate, delete, and move columns within the table definition. Four-way ISPF scrolling is supported for the table column display and split screen mode is fully supported. A help screen is available for selecting the column data types.
  • Column Explode
    Instead of scrolling horizontally to view all the attributes of a column, you can use the column explode option. This option shows all column attributes on one screen.
  • Referential Integrity
    You can create unique constraints as part of the table create or table alter process. Table and column selection panels make selecting the right columns easy, and let you specify the sequence number for each column.
  • Table-Controlled Partitioning
    You can fully exploit table-controlled partitioning when creating, altering, and dropping DB2 objects.
  • Alteration Support
    DB2 permits only certain alterations to a DB2 table using the DB2 ALTER command. If the alterations to a table cannot be performed using the DB2 ALTER command, you can use the Alter Table Option to automatically invoke RC/Alter to perform a drop and recreation of the table with the changes. All dependents, data, and authorizations are automatically restored and all changes, including the impact on any indexes using columns, are automatically propagated to any dependent object types. This is a very powerful feature and is transparent to the end user. When the end user makes the change, we will make the change using the appropriate methods.
  • COMPARE Command
    While modifying a table, you can display a summary of all changes by entering the COMPARE command. A new screen appears showing the old and new versions of the table, and the associated changes. Use the RESET command to reset the changed column back to its original values.
  • CURRENT SQLID Support
    Create tables for other users using the SET CURRENT SQLID command to perform the necessary ID switching.
The term “table screen” is used to refer to the general layout of the Table Create screen, Table Template screen, and Table Alter screen. All these screens share the same fields and layout, but the available options are different depending on the mode (create, alter, template).
When the following conditions are met, the resulting DDL to change the table is an ALTER TABLE ... ADD COLUMN, instead of a DROP/CREATE:
  • The table contains a hidden ROWID column that is the last column of the table
  • Object edit is used to add a new column immediately before the hidden ROWID
The column is added immediately after the hidden ROWID column instead of immediately before it.
To insert the column immediately before the hidden ROWID column, specify Y for the DROP & RECREATE analysis option, and then run a RC/Alter or analysis. The DROP/CREATE is included in the generated DDL without mention of the hidden ROWID column. The hidden ROWID column is implicitly created by DB2 at the end of the table after the newly added column.