Tables Editor (IBM DB2 LUW)

From RapidSQL
Jump to: navigation, search

Go Up to IBM DB2 for Linux, Unix, and Windows Object Editors

The Tables Editor lets you manage basic properties, columns, dimension columns, distribution key columns, partitions, tablespaces, and constraints for a table.

Tip: Before modifying a table, familiarize yourself with the material in Altering Tables for IBM DB2 LUW for Linux, Unix, and Windows.

To edit a table

  1. Open an editor on the table. For details, see Opening an Object Editor.
  2. Use the following table as a guide to understanding and modifying the settings on the tabs of this editor:
Tab Settings and tasks

Columns

Displays the currently defined columns in the table. Optionally, you can:

Select a column, and in the Property/Value list modify property values for that column.

Click Add Column, provide a name for the new column, and set property values for the column.

Select a column and click Delete to remove the column from the table.

Properties

Displays the Name, Created, Last RunStats, Invalidate Time, and Defined By properties. Lets you set Percent Free, Lock Size, Append Data, Volatile, Compress, Row Compression, Security Policy, RestrictDrop, LogIndexBuild, CCSID, Do not initially log, and Data Capture properties.

Status

Lets you view the following statistics: Total Number of Rows, Number of Overflow Rows, Number of Pages With Rows, Number of Pages, Table Status, Row Organization, RowTypeSchema, RowTypeName, AccessMode, ActiveBlocks, AvgCompressedRowSize, AvgRowCompressionRatio, CodePage, PercentOfPagesSaved, LastRegeneratedTime, and ProtectionGranularity.

Partition

This tab provides details on the partition columns and data partitions for the table. Optionally you can:

Use the New or Delete buttons to create or drop a partition column or data partition.

Use the Edit button to edit a data partition.

Use the Commands menu to attach or detach a data partition.

Tablespaces

Data Tablespace, Long Tablespace, and Index Tablespace

[JavaScript:popup.TextPopup(Poptext1,Popfont,9,9,-1,-1) Lets you view the Database partition Group], Managed By, Page Size, and Extent Size properties. Lets you choose the Name of a tablespace.

Dimensions

Lets you group columns to form a dimension:

Click the New button to add a new column to the dimension for the table.

Select a column and click the Edit button to modify the dimension column properties.

Select a column and click the Delete button to drop a column from the dimension.

Distribution Key Columns

Lets you group one or more columns to form a distribution key:

Click the New button to add a new column to the dimension for the table.

Select a column and click the Edit button to modify the dimension column properties.

Select a column and click the Delete button to drop a column from the dimension.

Indexes

Lets you manage indexes for a table:

Click Add to open a dialog that lets you add a new index to the table.

Select an index and click Edit to open a dialog that lets you edit index properties.

Select an index and click Drop to open a dialog that lets you remove the index from the table.

Constraints

Lets you manage primary key, unique key, foreign key, and check constraints for a table. Constraints are grouped by type, under folders:

Select a constraint type folder and click Add to open a dialog that lets you add a constraint of that type.

Select a constraint and click Edit to open a dialog that lets you modify the constraint details.

Select a constraint and click Drop to remove the constraint.

Comment

For details on using this tab, see Adding a Comment to an object.

Dependencies

For details on using this tab, see Working with Object Dependencies.

Permissions

For details on using this tab, see Working with Privileges and Permissions.

DDL View

For details on using this tab, see Viewing the SQL/DDL for an Object.

3. When finished, you can submit your changes. For details, see Previewing and Submitting Object Editor Changes.

Altering Tables for IBM DB2 LUW for Linux, Unix, and Windows

The ALTER TABLE command of Transact-SQL is limited to adding NULL columns to the end of a table and to adding or dropping constraints. Unfortunately, this scenario does not address many requirements of administrators and developers who need to add, delete or modify columns more broadly:

  • Add columns anywhere in a table, not just the end
  • Add columns that do not permit a NULL value.
  • Change the NULL/NOT NULL status of table columns
  • Change column datatypes to other compatible types
  • Change the length of datatypes
  • Delete a column

Due to the limitations of the ALTER TABLE command, the only way to make broader modifications is to write SQL scripts that step through all desired changes. To perform an enhanced table alter, SQL script that completes the following steps is constructed:

  1. Renames the existing table so that the original and its data remain intact
  2. Builds a CREATE TABLE statement with the new table definition, including declared defaults, primary key and check constraints
  3. Builds an INSERT statement to copy data from the original, renamed table to the new one
  4. Builds foreign keys on the new table
  5. Reapplies any privileges granted on the table
  6. Rebuilds all dependencies on the new table, including indexes, triggers, procedures, packages, functions and views. When rebuilding procedures, functions, packages and views, any permissions on them are also rebuilt.