Tables Editor (IBM DB2 Z/OS)

From DBArtisan
Jump to: navigation, search

Go Up to IBM DB2 for z/OS Object Editors

The Tables Editor lets you: manage columns, basic properties, partitions, indexes, and constraints for a table.

Note: Before editing tables, refer to the material in Altering Tables for IBM DB2 z/OS.

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. For each column, the Property/Value list displays the Name and Type for the column. In addition, depending on the Type selected, the list also displays Size, Scale, Identity Column, Allow Nulls, Default Value, Comment, and For Data property values, as appropriate. The Statistics group displays a Number of Distinct Values in the Column value. 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

Lets you work with settings in the following categories:

Table

Lets you view EditProcedure, Table Type, Table Status, Check Flag, DBID, and OBID properties. Lets you set Volatile, Audit, RestrictDrop, Label, and ValidProc properties.

Tablespace Placement

Lets you select a Tablespace.

Log Options

Lets you select a Data Capture option of DATA CAPTURE NONE or DATA CAPTURE CHANGES.

Statistics

Lets you view the following statistics: Last Runstats, Total number of rows, Average Row Length, Number of Pages, Percent Compressed Rows, Max Record Length, and DASD storage.

Partitions

Displays existing partition columns and data partitions. Optionally you can add, edit, or delete partition columns and data partitions.

Indexes

Displays the list of indexes for the table. Optionally, take one of the following actions:

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

Displays constraints in a tree structure. The tree contains folders which contain all constraints associated with the target table. The objects are organized in folders based on the type of constraint. Optionally take one of the following actions:

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 z/OS

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, an 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.