Creating a Table

Add a base table to the database. A base table holds persistent data. (The table and its data continue to exist until you explicitly delete the data or drop the table.)

Prerequisites
Database Version Table Privileges
SAP Sybase IQ 15.3 and 15.4 Base table to be owned by self:
  • Requires RESOURCE authority with CREATE permission on the main store dbspace in which the table is created.

Base table to be owned by any user – Requires DBA authority.
SAP Sybase IQ 16.0 Base table to be owned by self –
  • Requires CREATE permission on the dbspace where the table is created.
  • Also requires one of:
    • CREATE TABLE system privilege.
    • CREATE ANY OBJECT system privilege
Base table to be owned by any user –
  • Requires CREATE permission on the dbspace where the table is created.
  • Also requires one of:
    • CREATE ANY TABLE system privilege.
    • CREATE ANY OBJECT system privilege
Task
Altering or creating base tables can interfere with other users of the database.

For large tables, modifying an existing table or creating a new table can be a time-consuming operation. The internal CREATE TABLE statement delays execution of other processes until the statement completes. Although you modify tables while other connections are active, you cannot execute them while any other connection uses the table to be altered. Modifying a table excludes other requests referencing the table being offered while the internal ALTER TABLE statement processes.

  1. In the Perspective Resources view, select the resource and select Resource > Administration Console.
  2. In the Administration Console, expand IQ Servers > Schema Objects > Tables.
  3. Click the arrow next to Tables and select New.
    The Create Table wizard appears.
  4. On the Welcome page, specify:
    Option Description
    Select a resource for which the table will be created From the list, select the resource for which the table will be created.
    What do you want to name the table? Enter a unique name for the new table; maximum of 128 characters.
    Which user do you want to own the table? From the list, select the user to own the table.
  5. Click Next.
  6. On the Dbspace page, specify the data store and dbspace in which the table will be created in. Only those dbspaces to which you have permissions, and only those dbspaces in read-write mode, are listed.
  7. Click Next.
  8. (For IQ store only) On the Row-level versioning (RLV) page, select Enable RLV, if you want the new table to use row-level versioning storage. Alternatively, select Disable RLV if you don't want the table to be RLV-enabled, or select Default if you want to use the settings from the BASE_TABLE_IN_RLV_STORE database option.
    Note:
    • Row-level versioning is supported in a simplex environment only. The row-level versioning page does not appear in a multiple environment.
    • The RLV store dbspace must exist before creating RLV-enabled tables.
    • Only IQ base tables are supported in the RLV store: catalog, temporary and global temporary tables are not supported.
    • LONG VARBINARY (LOB) and LONG VARCHAR data types are not supported on RLV-enabled tables.
    • TEXT and WORD indexes are not supported on RLV-enabled tables.
    • Foreign key constraints are not supported on RLV-enabled tables or across a combination of RLV-enabled and traditional tables.
  9. (For system store only) On the Primary Key Constraints page, enter primary key constraint and cluster primary key information. Default is to create a primary key constraint but not a clustered primary key.
  10. (For system store only) Click Next.
  11. (For system store only) On the Free Space page, enter free space information. Default is to reserve 200 bytes for each table page or accept the defaults
  12. Click Next.
  13. On the Columns page, each row in the Column Definitions table corresponds to a column. Click New to add column definitions for the table.
  14. Follow the Create Column wizard prompts.
    1. On the Column Name page, specify:
      Option Description
      Column Name Name of the column.
      Is the column a primary key? Indicates you are using the column as a primary key (default).
    2. Click Next.
    3. On the Data Type page, specify:
      Option Description
      Built-in type (Default) Choose a predefined data type for the column. Integers, character strings, and dates are examples of predefined data types. For some of these types, you can specify size and scale.
      • Size — Length of string columns, or the total number of digits to the left and right of the decimal point in the result of any decimal arithmetic for numeric columns. For numeric columns, the size is also called the PRECISION value.
      • Scale — Minimum number of digits after the decimal point when an arithmetic result is truncated to the maximum PRECISION value.
      • Units — Unit corresponding to the data type's size. Possible units include: bits, bytes, characters, or digits. For CHAR and VARCHAR data types, you can specify the unit as bytes or characters.
      • Compress values — Not available for all built-in types) Select to compress column values. If a column is compressed, database server activities such as indexing, data comparisons, and statistics generation may be slightly slower if they involve the compressed column because the values must be compressed when written, and decompressed when read.
      • Maintain BLOB indexes for large values — (Available for character, binary, and bit types only) Maintain BLOB indexes for large values.
      Domain Choose a domain. A domain is a named combination of built-in data types, default value, check condition, and nullability.
    4. Click Next.
    5. On the Value page, specify:
      Option Description
      No default or computed value (Default) Select this option if the column is not a computed value and has no default value.
      Default Value Select this option if the column has a default value. If the column is based on a domain, this setting inherits the domain's default value (if any), but you can override the value for the column. Choosing the Default value option enables the User-defined and System-defined options.
      • User-defined — Type a custom value (string, number, or other expression) for the default value. If you have based the column on a domain, you can retain the domain's default value (if any), or override it for the column
        • Literal string — Specify whether the default value for the column should be treated as a literal string. By default, this option is selected for character columns and domains with a character base type. You do not need to enclose default text in single quotes, and escape embedded single quotes or backslashes in the string.
      • System-defined — Lets you select a predefined value (for example, current date) for the default value. Select a value. If you have based the column on a domain, you can retain the domain's default value (if any) or override it for the column.
        • Select Autoincrement or Global Autoincrement from the list.
        • If you select global autoincrement, the domain of values for that column is partitioned. Each partition contains the same number of values. For example, if you set the partition size for an integer column in a database to 1000, one partition extends from 1001 to 2000, the next from 2001 to 3000, and so on.
      Computed Value Define a computed value for the column. A computed column derives its values from calculations of values in other columns. Type an expression in the text box to describe the relationship between the other columns and the value that appears in the computed column.
    6. Click Next.
    7. On the Constraints page, specify:
      Option Description
      Values can be null (Default) Select this option if null values are allowed in the column. If the column is based on a domain, you can retain the domain's nullability or override it for the column.
      Values cannot be null Select this option to allow duplicate values, but not allow null values in this column.
      Values cannot be null and must be unique Select this option if values in the column cannot be null and must be unique.
    8. Click Next.
    9. (For IQ store only) On the Placement page, specify:
      Field Description
      Select the placement details for this column From the list, select the dbspace.
    10. (For IQ store only) Click Next.
    11. (Optional) On the Comment page, specify:
      Option Description
      What would you like the comment for this column to be? Add a descriptive comment for the column. Comments help you organize your database.
    12. Click Finish to create the column.
    13. Repeat steps 14 and 14.l to create additional columns. When all columns are added, click Next.
  15. (Optional) On the Partitions page, click New.
  16. Follow the Create Partition wizard prompts.
    1. On the Partition Type tab, specify a hash partition:
      Option Description
      What type of partition do you want to create? Select Hash, Range, or Hash-Range.
    1. Click Next.
    2. On the Partition Key page, specify one or more columns.
      Option Description
      Which column do you want to include in the partition key?

      Select one (for range partition key) or more table columns and click Add.

      A partition key cannot contain LOB, binary, varbinary, bit, float, double, or real data, or any char or varchar column with a length that exceeds 255 bytes.

      Hash partition keys are restricted to a maximum of 8 columns with a declared column width of 5300 bytes or less.
    3. (For range or hash-range partition only) Click Next.
    4. (For hash-range partition only) On the Subpartition page, specify a column:
      Option Description
      Which column do you want to include in the subpartition key?

      Select a table column and click Add

      A partition key cannot contain LOB, binary, varbinary, bit, float, double, or real data, or any char or varchar column over 255 bytes long.
    5. (For hash-range partitions only) Click Next.
    6. (For hash-range partitions only) On the Subpartitions page, specify the range partition definition:
      Option Description
      Specify the name, value and dbspace for the range subpartition.
      • Name — Enter a descriptive name for the partition.
      • Constraint — Less than or equal (<=) is the only constraint currently supported.
      • Value — Enter the upper limit value of the partition.
      • Dbspace — Select the dbspace for the partition from the pull-down.
         
    7. (For hash-range partitions only) On the Columns page, click Add if you want to store the data for any individual columns in separate dbspaces from the partition. Otherwise continue to the Comment page.
      If you click Add, the Specify Dbspace for a Partition Column page appears.
    8. (For hash-range partitions only) Choose the column, partition, and dbspace from the pull-down and click OK.
    9. (For hash-range partitions only) When you have specified all the desired columns, continue to the Comment page.
    10. (For range partition only) On the Partitions page, click Add.
      The Define a Partition page appears.
    11. (For range partitions only) On the Define a Partition page, specify the range partition definition:
      Option Description
      Specify the name, value and dbspace for the range partition.
      • Name — Enter a descriptive name for the partition.
      • Constraint — Less than or equal (<=) is the only constraint currently supported.
      • Value — Enter the upper limit value of the partition.
      • Dbspace — Select the dbspace for the partition from the pull-down.
    12. (For range partitions only) On the Columns page, click Add if you want to store the data for any individual columns in separate dbspaces from the partition. Otherwise, continue to the Comment page.
    13. (For range partitions only) If you click Add, the Specify Dbspace for a Partition Column page appears.
    14. (For range partitions only) Choose the column, partition, and dbspace from the pull-down and click OK.
    15. (For range partitions only) When you have specified all the columns desired, continue to the Comment page.
  17. (Optional) On the Comment page, enter a comment about the table.
  18. Click Finish.
Parent topic: Tables
Related concepts
Table Columns
Table Permissions
Table Constraints
Table Indexes
Table Triggers
Table Partitions
Related tasks
Creating a Global Temporary Table
Creating a Proxy Table
Viewing Table Data in the Execute SQL Window
Deleting a Table
Generating Table DDL Commands
Moving a Table to Another Dbspace
Validating a System Store Table
Setting the Primary Key
Setting a Clustered Index
Merging Table Data from RLV Store with IQ Main Store
Calculating the Number of Rows in a Table
Moving Table Objects to Another Dbspace
Enabling or Disabling Row-Level Versioning in a Table
Viewing or Modifying Base Table Properties
Viewing or Modifying Global Temporary Table Properties
Viewing and Modifying Proxy Table Properties
Authenticating a Login Account for a Managed Resource
Related reference
Table Privilege Summary