Tables

In the table from the Tables tab, the value that is displayed in the row count column is an approximate. The exact row count is available after you run the GENERATE STATISTICS ON <table_name> command.

Creating tables

Go to Databases.
Select the database in which you want to create a table.
Select the schema in which you want to create a table.
Ensure that you are in the DB Objects > Tables tab.
Click Create table.
Type a name for the table.
If the name contains special characters, enclose it in double quotation marks. The dot character (".") is not supported.
You can select a name that has up to 128 characters. The name must begin with a letter or an underscore and can't contain embedded spaces. The name must be unique.
Optional: Specify the retention time interval (in days) for the table.
You can select between 1 day and up to 99 days, or zero to alter a temporal table to nontemporal.
Add columns to the table:
1. In the Columns section, under Name, type a name for the column. The name must start with a letter.
2. Select your column type.
  The data type restricts the type of data that can be stored in a column. For example, preventing entry of alphanumeric characters into a numeric field.
  Data types also help sort data correctly and play a role in optimizing storage. For all these reasons, it is important to pick the appropriate data type.
3. Specify whether Not null is true or false.
  A column that allows NULL values also allows rows to be inserted with no value in that column. A column that does not allow NULL values does not accept rows with no value.
4. Specify the default value to be used if no value is specified when a row is inserted.
5. In the Distribute on and Organize on sections, specify the distribution key for the table by selecting up to four columns.
  
  To add another column, click the plus sign.
Click Create.

Column and table constraints

When you create a table, you can specify constraints for a column, table, or both.

For example, a table_constraint can be:

{ PRIMARY KEY ( column_name [, ... ] ) |  FOREIGN KEY ( column_name [,
... ] ) REFERENCES reftable (refcolumn ) [ MATCH matchtype ] [ ON
DELETE action ] [ ON UPDATE action ] [ [ NOT ] DEFERRABLE ] [ INITIALLY
checktime ] } [, ...]

The system permits and maintains primary key, default, foreign key, unique, and references. Because Netezza Performance Server does not support constraint checking and referential integrity, you must ensure your own constraint checking and referential integrity.

If you have permission to create a table, you can specify a constraint.

If you have permission to alter a table, you can add or drop a table constraint.

You cannot change constraint names or directly change the owner of the constraint. The owner of the constraint is always the owner of the table. Thus, if you change the owner of the table, the system changes the owner of all associated constraints.

Grooming tables

GROOM TABLES processes and reorganizes table records in each data slice in a series of steps. You can still run operations such as SELECT, UPDATE, DELETE, and INSERT while data grooming is happening.
SELECT operations run in parallel with the groom operations.
INSERT, UPDATE, and DELETE operations run serially between the groom steps.
For cluster based tables (CBTs), GRROM TABLE takes longer. Tthe INSERT, UPDATE, and DELETE operations might be pending longer until the current step completes.

You can use the Groom Table functionality to reclaim disk space from deleted or outdated rows. You can also use this option to reorganize tables based on the clustered base table organizing keys or to migrate data from tables that have multiple stored versions.

Go to Databases.
Select your database and schema.
Ensure that you are in the DB Objects > Tables tab.
Select the table that you want to groom.
Go to the Grooms tab.
Click Groom table.

To run the operation, the user must have object privileges. The user does not have to be an administrator.
You cannot groom tables inside a transaction block (begin or commit pair) or with a stored procedure.
When you specify organizing keys for an existing table to make it a CBT, the new organization might impact the compression size of the table. The new organization can create sequences of records that improve the overall compression benefit, or it can create sequences that do not compress so well. After a groom operation, your table size might change.

Modes

You can choose different modes when you groom tables.

Table 1. The table lists modes-related values and their definitions.
Value	Description
RECORDS READY	Reclaims and reorganizes records in the tables that were not groomed, and in those that were already groomed but got marked for regrooming. This is the default setting for clustered base tables (CBT).
RECORDS ALL	Reclaims and reorganizes all records in a table. This is the default for a non-CBT.
PAGES ALL	Identifies and marks the data pages in the table with no visible record as Empty to free up disk extents.
PAGES START	Identifies and marks the leading data pages in the table with no visible record as Empty. Stops when it finds a non-empty data page.
VERSIONS	Migrates records from previous table versions. Dropped columns do not appear and added columns show default values.

Assigning owners to tables

Go to Databases.
Select the database and schema in which the table that you want to update is.
Select the table for which you want to assign an owner.
From the overflow menu, click Assign owner.
Select an owner for the table.
Click Assign.

Renaming tables

Go to Databases.
Select the database and schema in which the table that you want to rename is.
From the overflow menu, click Rename.
Type a new name for the table.
If the name contains special characters, enclose it in double quotation marks. The dot character (".") is not supported.
Click Rename.

Updating retention time interval (time travel) for tables

Go to Databases.
Select the database and schema in which the table that you want to update is.
Select the table.
From the overflow menu, click Update interval.
Type a retention time interval.
You can select between 1 day and up to 99 days, or zero to alter a temporal database to nontemporal. For more information on retention time interval and time travel, see NPSaaS time travel.
Click Save.

Dropping tables

Go to Databases.
Select the database and schema in which the table that you want to update is.
Select the table.
From the overflow menu, click Drop.
Confirm your choice by clicking Drop.

Viewing space usage (time travel)

Go to Databases.
Select the database and schema in which the temporal table that you want to analyze is located.
Select the table.
Go to the Time travel tab.
Analyze the data.
You can view the information in a list or as a chart.