Creating a table

Procedure

  1. Log on to the NonStop SQL/MX Database Manager.
  2. In the navigation tree, select Database > My Systems > Catalogs > Schemas.
  3. Right-click the Tables folder or a Schema and select Create Table.
  4. Enter the name of the table to be created.

    The table will be created in the schema that is currently selected in the schema tree. The Create button is disabled until all the relevant fields to create a table are entered.

  5. Set the following table attributes:
    • Audit Compress

    • Clear On Purge

    • Block Size

    • Allocate Extents

  6. Click the Columns tab to add or configure columns of the table.
  7. Click Add in the Columns tab to launch Specify Column dialog.
    Specify Column
  8. Enter the various fields required to create a column.
    The Specify Column dialog displays different set of properties to be set for their corresponding data types. The following data types can be specified for the columns:
    • Character

    • Date

    • Decimal

    • Double Precision

    • Float

    • Integer

    • Integer with identity column set

    • Interval

    • Largeint

    • Largeint with identity column set

    • NChar

    • Numeric

    • Picture

    • PictureComp

    • Real

    • Smallint

    • Smallint with identity column set

    • Time

    • Timestamp

    • Varchar

    • Varchar2

    • Number

  9. Click Edit in the columns tab to launch a dialog similar to Specify Column. In this dialog, the existing values can be modified and updated accordingly.
  10. Click Location tab.
    Location tab

    Location tab displays local node and the remote nodes connected through expand to local node in the System list. If you are creating a table on remote node, the catalog on which the table is being created must be registered on the remote NonStop system connected through MXDM.

    The following attributes can be modified:
    • Volume Name: This is the data volume on which the primary partition of the table will be created.

    • Primary Extent Size: This attribute specifies the size of the primary extent in pages.

    • Secondary Extent Size: This attribute specifies the size of the secondary extents in pages.

    • Max extents:This attribute specifies the maximum number of extents.

    • Name:This attribute specifies whether SQL/MX selects a name of the partition or is user defined.

    • Location: This attribute specifies whether SQL/MX selects the file name or is user defined.

  11. Click Primary Key tab to add primary key constraint to the table.

    This tab contains Columns Available and Columns in Primary Key grids. The Columns Available grid is populated with all the columns that were added in Columns tab having Not Null property set.

  12. Select columns from the Columns Available grid, click Add to move them to the Columns in Primary Key grid.
    1. Select columns from the Columns in Primary Key grid, click Remove to move back the columns to the Columns Available grid.
    2. Click Move Up and Move Down buttons to change the order of the selected columns in the Columns in Primary Key.
  13. Click Store By tab to specify a set of columns on which to base the clustering key.

    The clustering key determines the order of rows within the physical file that holds the table. The storage order has an effect on how you can partition the object.

  14. Click Unique Constraints tab to specify the unique constraints for the table.

    A unique constraint is a column or table constraint that specifies that the column or set of columns cannot contain more than one occurrence of the same value or set of values.

  15. Click Check constraints tab to specify the check constraints for the table.

    A check constraint is a constraint that specifies a condition that must be satisfied for each row in the table.

  16. Click Foreign Keys tab to specify the Foreign Key constraints for the table.

    A foreign key constraint is a constraint that specifies a column or set of columns in table can contain only values that match those in a column or set of columns in the referenced table.

  17. Click Partitioning tab and then select either Range Partition or Hash Partition to define the partitioning scheme for the table.
    Partitioning tab
  18. Click Secondary Partitions tab and click Add to add secondary partitions for the table.

    Specify Partitions dialog appears.

    You can specify the node where the partition resides, volume, extent sizes, name of the partition, and location. Based on the number of partitions per volume, the number of partitions are added to the partitions list and the name of the node and volume are same for all.

  19. Click Create to create the table with the set of values entered in the various tabs.
    Create Table wizard buttons

    Button

    Function

    Create

    Creates the table within a schema.

    Reset

    Resets the selected tab in Create Table wizard.

    Cancel

    Closes the Create Table wizard.