
Tables
This section covers the following topics:

Creating and Modifying Tables and Table Objects
Creating a Table
To add a table to a database:
- 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.
- Do one of the following:
- 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.
- Choose the Schema for the table. By default, the schema is APP.
- Begin adding and defining columns in the grid. For each column:
- Type a name for the highlighted column in the Columns grid.
- Specify type information for the column. For an explanation of each field, see "Column Specifications", below.
- 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).
- 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.
- 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
- Name
The name of the column. See "Case Sensitivity in Table and Column Names" for information about how case is handled in new column names.
- Type
The data type of the column. Choose an SQL-J built-in type or SERIALIZE from the drop-down menu.
- Nullable
Leave the value "Yes" if the column will accept NULLs. To prevent a column from allowing NULLs, choose No from the drop-down menu.
NOTE:
You can now revert a NOT NULL column to accept NULLs after the table has been created. See Modifying the nullability of a Column.
- Length
The length of the column. Specify this value only for BIT, BIT VARYING, and character type columns (CHAR, NATIONAL CHAR, VARCHAR, and NATIONAL VARCHAR). Leave this value blank for all other data types.
- Precision
Enter a precision value for DECIMAL and NUMERIC columns; leave blank for all other data types.
- Scale
Enter the number of digits to appear after the decimal point in DECIMAL and NUMERIC columns. Leave blank for other data types.
- Default
Optionally enter a default value for the column. Enclose characters in single quotes.
- Auto Increment
Check this box if you want this column to contain automatically-assigned increasing integer values. The autoincrement default can be applied to any of the int datatypes (INT, LONGINT, SMALLINT, or TINYINT). Optionally, specify a starting value and the increment amount in the Initial Value and Increment fields, respectively. If not specified, the initial value and increment amount are both 1. You can specify a negative number to decrement the value.
- Class
The Java class name. If the data type is SERIALIZE, enter a class name in this column.
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.
- Open the table by highlighting it in the left panel.
- In the right panel, click the plus (+) symbol to add a column.
- Specify the column information as described in "Creating a Table".
- Repeat steps 2 and 3 for each column you want to add.
- 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).
- Highlight the table name in the left panel.
- In the Table tab, select the length field for the column you want to modify.
- 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.
- Highlight the table name in the left panel.
- In the Table tab, select a Nullable field which contains No'.
- Click the drop-down arrow and choose Yes'.
- Repeat steps 2 and 3 for each column you want to change.
- 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:
- Choose System in the left panel.
- Choose the Preferences tab in the right panel.
- 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
- To create a new index, do one of the following:
- Right-click on the Index node (or an index name) in the left panel, and choose New->Index.
- Highlight the index name in the left panel, and choose New->Index from the Edit menu or from the right panel (Index icon or New button)
The index definition form appears in the right panel.
- Choose the Index tab if it is not already chosen.
- Enter the name of the index in the Name field.
- 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.
- 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.
- Choose the Properties tab.
- 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.
- Choose the SQL tab to see the SQL statement for the index.
- Check the values you have entered in both the Index and the Properties tabs to make sure they are correct.
- Click OK.

Creating a Key
- To create a key, do one of the following:
- Right-click on the table name in the left panel, and choose New->Key.
- Highlight the table name in the left panel, and choose New->Key from the Edit menu or from the right panel (using the Key icon or New button)
The key definition form appears in the right panel. Use this form to create a key that is unique, primary, or foreign.
- In the Key tab, enter the name of the key in the Name field.
- 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.
- 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.
- Choose the SQL tab to see the SQL statement for the key.
- Click OK.

Creating a Check Constraint
Follow these steps to create a check constraint:
- In the left panel, highlight the constraint node, a specific constraint, or the name of the table on which to create a check constraint.
- 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.
- Choose the Check tab if it is not already chosen.
- Enter the name of the check constraint in the Name field.
- 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.
- 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:
- Choose Tables in the left panel.
- Choose the table(s) you want to delete in the right panel.
- Click the Delete button in the right panel.
You can also delete an individual table as follows:
- Choose the table in the left panel.
- Do one of the following:
- Choose Edit->Delete from the menu.
- Right-click on the database name in the left panel, then choose Delete.
- Click the table icon in the right panel, then choose Delete.

Deleting Indexes
To delete indexes or keys from the database:
- Click Indexes or Keys in the left panel.
- Choose the index(es) or key(s) you want to delete in the right panel.
- Click Delete.
Deleting Keys
You can also delete an individual index or key as follows:
- Choose the index or key in the left panel.
- Do one of the following:
- Right-click, then choose Delete.
- Click the Delete button in the right panel.

Deleting Check Constraints
To delete check constraints from the database:
- Click Checks in the left panel.
- Choose the check constraint(s) you want to delete in the right panel.
- Click Delete.
You can also delete an individual check constraint as follows:
- Choose the check constraint in the left panel.
- Do one of the following:
- Right-click, then choose Delete.
- Click the Delete button in the right panel.
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:
- Choose System in the left panel.
- Choose the Preferences tab in the right panel.
- Uncheck "Save edits without prompting."
If you have configured Cloudview to prompt you about changes:
- After defining a table or column, click OK to save the changes to the database, or Cancel to leave the database unchanged.
- If you move the cursor off the panel without saving or undoing the changes, Cloudview asks you to whether you want to save the changes. Click Yes or Cancel. If you choose not to save the changes, the database remains unchanged.
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:
- To view system tables as well as user tables, choose System Tables from the View menu. (By default, only user tables are visible.)
- Click the "+" symbol next to the Database icon in the left panel to show the Tables icon.
- Display the tables by doing one of the following:
- Click the "+" next to the Tables icon. The tables in the database appear under the Tables icon in the left panel.
- Click the Tables icon. The tables in the database are listed in the right panel.
Examining Columns
To view the columns in a table:
- 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).
- 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:
- Name
The name of the column.
- Type
The data type of the column.
- Nullable
Shows whether the column can contain null values.
- Length
The length of the column in bytes. Used only for CHAR, VARCHAR, BIT, and BIT VARYING columns.
- Precision
The number of significant digits. Used only for DECIMAL and NUMERIC columns.
- Scale
The number of digits after the decimal point. Used only for DECIMAL and NUMERIC columns.
- Default
Any specified default value for this column.
- Auto Increment
Whether an autoincrement default has been applied to this column (only applicable for int type columns).
- Initial Value
Optional starting value for autoincrement.
- Increment
Optional value by which to increment an autoincrement field.
- Class
The Java class used as the column's data type. Blank unless the value of the Type field is SERIALIZE.

Examining Indexes
To view the indexes defined on a table:
- Click the "+" to the left of the table name in the left panel.
- 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:
- Click the index you want to inspect in the left panel.
- Double-click the index listed in the right panel.
The right panel then displays three tabs:
- Index
Shows the name, type, and columns of the index
- Properties
Shows the page size and initial pages of the index
- SQL
Shows the statements that can be used to create and delete the index

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:
- Click the "+" to the left of the table name in the left panel.
- 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:
- Click the key you want to inspect in the left panel.
- Double-click the key listed in the right panel.
The right panel then displays two tabs:
- Key
Shows the name, type, and columns and columns used in the key. Type can be Primary, Unique, or Foreign.
- SQL
Shows the statements that can be used to create or delete the key.

Examining Check Constraints
To see the check constraints defined on a table:
- Click the "+" to the left of the table name in the left panel.
- 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:
- Click the check constraint in the left panel.
- Double-click the check constraint listed in the right panel.
The right panel then displays two tabs:
- Check
Shows the name of the check constraint and the condition it enforces
- SQL
Shows the statements that can be used to create or delete the check constraint

Examining a Table's Properties
When a table is selected in the left panel, the table's Properties tab displays the following information:
- the minimum record size
- the page size
- the page reserve size
- the number of initial pages
- the table locking level (row or table)
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:
- Choose the table in the left panel.
- 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:
- Select the table in the left panel.
- 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.
- 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).
- 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.
- To modify data, click the field you want to alter and make the changes.
- To add a row, click the Insert icon (plus sign) above the grid. A blank row appears above the currently selected row.
- To delete a row, place the cursor in the row you want to delete and click the red Minus icon above the grid.
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.