Tables

This section covers the following topics:

Creating and Modifying Tables and Table Objects

Creating a Table

To add a table to a database:

  1. Select the database in the left panel.
    NOTE: For information about opening a database, see Opening an Existing Database. For information about creating a new database, see Creating a Database.
  2. Do one of the following:
  3. Type the table name in the Name box.

    See "Case Sensitivity in Table and Column Names" for information about how case is handled in new table names.

  4. Choose the Schema for the table. By default, the schema is APP.
  5. Begin adding and defining columns in the grid. For each column:
  6. After each column is defined, click the "+" button above the Columns grid to add another column. (Conversely, you can delete a selected column with the "-" button).
  7. Optionally, you can specify the lock level for the table (row- or table- level). Click the Properties tab to find (and set) the table lock level.

NEW: The ability to specify the table lock level in Cloudview is new in version 3.6.

  1. When finished defining the table, click OK to create it.

NOTE: Unless you have unchecked the property Save Edits without Prompting in the System Settings (Property tab), new tables and columns are saved automatically when you click outside the table tab area. You can also explicitly save the table or column by clicking OK. See "Saving New Tables and Columns Automatically" for more details.

Column Specifications

NEW: The autoincrement default type is new in version 3.6.

Modifying an Existing Table

Once a table has been created, you can alter a subset of table elements from within Cloudview.

Adding Columns to an Existing Table

After you have created a table, you can add columns.

  1. Open the table by highlighting it in the left panel.
  2. In the right panel, click the plus (+) symbol to add a column.
  3. Specify the column information as described in "Creating a Table".
  4. Repeat steps 2 and 3 for each column you want to add.
  5. When finished, click OK to save the modified table.

Modifying the Length of BIT VARYING or Character Type Columns

Once a table has been created, you can modify the length of BIT VARYING or selected character type columns (CHAR, NATIONAL CHAR, VARCHAR, and NATIONAL VARCHAR).

  1. Highlight the table name in the left panel.
  2. In the Table tab, select the length field for the column you want to modify.
  3. Type in the new length value and click OK.

NEW: The ability to modify column lengths is new in version 3.6.

Modifying the nullability of a Column

Once a table has been created, you can modify columns defined as NOT NULL to accept NULLs.

  1. Highlight the table name in the left panel.
  2. In the Table tab, select a Nullable field which contains ‘No'.
  3. Click the drop-down arrow and choose ‘Yes'.
  4. Repeat steps 2 and 3 for each column you want to change.
  5. Click OK to save changes.

NEW: The ability to modify the nullability of a column is new in version 3.6.

Adding or Dropping an Autoincrement Default

You can modify an INT type column to include an autoincrement default, or to remove such a default. To add or remove an autoincrement default, toggle the Auto Increment checkbox for that column on the Table tab.

You can also modify the initial and increment values for an autoincrement column by editing the Initial Value and Increment fields.

For more information about the autoincrement default, see Column Specifications.

NEW: Autoincrement defaults are new in version 3.6.

NOTE: You cannot import data into a table with an autoincrement default defined on one of its columns. Prior to importing data, you must drop the default by unchecking the Autoincrement column. After import, you can re-add the default.

Case Sensitivity in Table and Column Names

By default, table and column names are case-insensitive and are created as all uppercase. How to specify case sensitivity depends on whether you are explicitly executing DDL commands from the Cloudview SQL window, or whether you are creating objects using the Cloudview Edit Panel.

When explicitly executing DDL commands from the Cloudview SQL window, you can use quotation marks to indicate case-sensitivity. For more information, see the section "Case Sensitivity in the SQL Window".

When creating objects from the Edit Panel, however, you must set a system property to create case-sensitive object names.

To Turn off Case-sensitivity:

  1. Choose System in the left panel.
  2. Choose the Preferences tab in the right panel.
  3. Uncheck "Save DDL as case insensitive".

For information about creating case-sensitive table and column names from the SQL window, see "Case Sensitivity in the SQL Window" for details.

Creating Indexes

  1. To create a new index, do one of the following:
  2. Choose the Index tab if it is not already chosen.
  3. Enter the name of the index in the Name field.
  4. Choose Unique or Non-unique in the Type field.
    NOTE: Unique Keys and Unique Indexes function the same way. The difference is in the SQL syntax used to create them: a unique index uses the CREATE UNIQUE INDEX statement, and a unique key uses the SQL standard ALTER TABLE ADD CONSTRAINT syntax.
  5. Choose the column(s) to include in the index from the Columns grid. (The way to select multiple columns depends on your operating system.) If the index has more than one column, verify the order shown in the Order column of the grid.
  6. Choose the Properties tab.
  7. To alter the default values, enter the page size and number of initial pages.
    NOTE: The index row must fit on the page, so make sure the page size is large enough.
  8. Choose the SQL tab to see the SQL statement for the index.
  9. Check the values you have entered in both the Index and the Properties tabs to make sure they are correct.
  10. Click OK.

Creating a Key

  1. To create a key, do one of the following:
  2. In the Key tab, enter the name of the key in the Name field.
  3. Choose a type for the key in the Type field. Keys can be unique, primary, or foreign.
    NOTE: Unique Keys function the same way as Unique Indexes. The difference is in the SQL syntax used to create them: a unique index uses the CREATE UNIQUE INDEX statement, and a unique key uses the SQL standard ALTER TABLE ADD CONSTRAINT syntax.
  4. Choose the column(s) to include in the key from the Columns grid. (The way to select multiple columns depends on your operating system.) If the key has more than one column, verify the order shown in the Order column of the grid.
  5. Choose the SQL tab to see the SQL statement for the key.
  6. Click OK.

Creating a Check Constraint

Follow these steps to create a check constraint:

  1. In the left panel, highlight the constraint node, a specific constraint, or the name of the table on which to create a check constraint.
  2. Choose New->Check from the right-click menu, from the Edit menu, or by clicking the Check icon or New button in the right panel.
    The check constraint definition form displays.
  3. Choose the Check tab if it is not already chosen.
  4. Enter the name of the check constraint in the Name field.
  5. Enter the condition in the Condition field.

    You can use the Auto Text icon above the Condition field to help you write the condition. To choose a column from the table, click the Auto Text icon, choose Columns, then click the column you want. To add an operator or keyword, click the Auto Text icon, choose Key Words, then click the key word or symbol you want. You may have to type in the Condition field even if you use auto text. For example, to allow only values greater than 0 in the ID column, use auto text to choose the ID column and the ">" operator, then type 0 directly in the Condition field.

  6. Click OK.

Deleting Tables and Table Objects

You can delete tables from Cloudview, as well as table objects such as Keys, Indexes, or Check Constraints.

To delete a table or tables from the database:

  1. Choose Tables in the left panel.
  2. Choose the table(s) you want to delete in the right panel.
  3. Click the Delete button in the right panel.

You can also delete an individual table as follows:

  1. Choose the table in the left panel.
  2. Do one of the following:

Deleting Indexes

To delete indexes or keys from the database:

  1. Click Indexes or Keys in the left panel.
  2. Choose the index(es) or key(s) you want to delete in the right panel.
  3. Click Delete.

Deleting Keys

You can also delete an individual index or key as follows:

  1. Choose the index or key in the left panel.
  2. Do one of the following:

Deleting Check Constraints

To delete check constraints from the database:

  1. Click Checks in the left panel.
  2. Choose the check constraint(s) you want to delete in the right panel.
  3. Click Delete.

You can also delete an individual check constraint as follows:

  1. Choose the check constraint in the left panel.
  2. Do one of the following:

Saving New Tables and Columns Automatically

By default, all changes you make in Cloudview are saved automatically. To be prompted to confirm the changes you have made:

  1. Choose System in the left panel.
  2. Choose the Preferences tab in the right panel.
  3. Uncheck "Save edits without prompting."

If you have configured Cloudview to prompt you about changes:

NOTE: Changes to a table using the Data tab are never saved automatically; you must use the Save icon. See "Viewing, Inserting, and Modifying Data" for details.

Working with Tables

Examining Tables and Table Objects

You can look at definitions for tables and table objects (such as indexes, keys, and check constraints).

Examining Tables

To view the tables in a database:

  1. To view system tables as well as user tables, choose System Tables from the View menu. (By default, only user tables are visible.)
  2. Click the "+" symbol next to the Database icon in the left panel to show the Tables icon.
  3. Display the tables by doing one of the following:

Examining Columns

To view the columns in a table:

  1. Choose the table in either the left or the right panel (see "Examining Tables and Table Objects" for information on how to display tables in either panel).
  2. Choose the Table tab on the right panel.

Information about the table's columns appears in a grid on the bottom of the right panel. The name of each column is listed under Name in the grid. The grid is read-only.

The grid contains the following fields for each column in the table:

Examining Indexes

To view the indexes defined on a table:

  1. Click the "+" to the left of the table name in the left panel.
  2. Click the Indexes icon.

A list of the table's indexes appears in the right panel.

For a more detailed view of indexes, do one of the following:

The right panel then displays three tabs:

Examining Keys

In addition to indexes, you can view and define primary, unique, and foreign keys on a table.

To view the keys defined on a table:

  1. Click the "+" to the left of the table name in the left panel.
  2. Click the Keys icon.

A list of the table's keys appears in the right panel. Key icons display with a letter indicating the key type: ‘P' for primary keys, ‘U' for unique keys, and ‘F' for foreign keys.

For a more detailed view of a key, do one of the following:

The right panel then displays two tabs:

Examining Check Constraints

To see the check constraints defined on a table:

  1. Click the "+" to the left of the table name in the left panel.
  2. Click the Checks icon.

A list of the table's check constraints appears in the right panel.

For a more detailed view of check constraints, do one of the following:

The right panel then displays two tabs:

Examining a Table's Properties

When a table is selected in the left panel, the table's Properties tab displays the following information:

You can modify the lock level for a table by clicking on the drop-down arrow.

NEW: The ability to view and change the table lock level from Cloudview is new in version 3.6.

Examining the Statement That Created a Table

To view the statement that created a table:

  1. Choose the table in the left panel.
  2. Choose the SQL tab.

The statement that created the table appears in the right panel. Below it is the statement that can delete the table, as well as statements to create and delete any indexes, keys, and check constraints on the table. These statements are read-only; you cannot modify or run them from this window. However, you can copy them into the SQL field on the Database panel, where you can save, edit, and execute them (see "Executing Statements from the SQL Window").

Viewing, Inserting, and Modifying Data

Cloudview lets you browse data in a table. Cloudview also lets you insert, delete, or modify data of a SQL-J (non-Java-object) data type.

Selecting the Table

To view or alter data in a table:

  1. Select the table in the left panel.
  2. Select the Data tab in the right panel.

Viewing Data

Built-in data type values appear in the table grid on the Data tab. If a column contains a Java object, the object's toString value appears.

NOTE: Cloudview displays a BIT and BIT VARYING value containing 128 bytes or fewer as a hex string. You can copy the hex string, but you cannot alter it. If the BIT or BIT VARYING value is a gif, it is displayed as an image, otherwise nothing is displayed.

NOTE: When you first view the data in a table, the first 1000 rows are loaded. For tables with more than 1000 rows of data, you can choose to load all rows, or to load rows one thousand at a time. To load all the data in the table, click the "Load all" icon above the results grid. To load only the next 1000 rows, click the "Load next 1000 rows" icon.

Viewing Data in a Different Locale

By default, the Cloudview locale is the same as your java system locale. You can specify a different Cloudview session locale by starting Cloudview with the cloudscape.ui.locale property. If you have set the locale property to a different locale, you can toggle display of locale-sensitive data between the two locales.

  1. To display dates, timestamps, numbers, and times in the format of the Cloudview session locale, use the View>Local Data Representation menu option (Cloudview), or the LocalizedDisplay command (ij).
  2. To return the display of these types to the default Cloudview locale, click the menu option again to disable it.

NOTE: These options do not change how Cloudscape stores locale-sensitive data, simply how the data is displayed.

For more information about Localization, see the Cloudscape Tools and Utilities Guide and the Internationalization appendix in the Cloudscape Developer's Guide.

Insert, Delete, or Modify Data

You can also use a table's Data tab to insert, delete, or modify data of a SQL-J (non-Java-object) data type.

NOTE: Do not attempt to insert or modify values in a column defined as Autoincrement. These values are automatically assigned and incremented by the database.

Inspecting Java Objects in Data

To inspect a Java object within the table, choose it and click the Inspect icon (see "Inspecting Java Objects").

System Objects

You can view and query a database's system tables or stored statements. Choose View->System Tables or View->System Stored Statements to toggle on or off display of system objects.

System Tables

By default, system tables do not appear in Cloudview. To make them visible, check System Tables from the View menu. They appear in the list of tables in each database, and you can examine them just like user tables.

Querying system tables can provide information about all items in a database, as opposed to only the items associated with a given table. For example, you can see all the indexes and tables in a database by looking at the data in SYSCONGLOMERATES.

NOTE: You cannot modify data in system tables.

System Stored Statements

By default, system stored statements do not appear in Cloudview. To make them visible, check System Stored Statements from the View menu. They appear in the list of stored statements in each database, and you can inspect them just like user stored statements.