Dictionary Objects
Creating Dictionary Objects
You can create dictionary objects using the Cloudview menus, or by typing in SQL-J statements. You can create the following types of objects in Cloudview:

Deleting Objects
You can delete dictionary objects from Cloudview. Typically, to delete an object from Cloudview, right-click on the object name in the Navigation Panel, and choose Delete from the popup menu.
To delete multiple objects at one time, highlight the Object Type Icon, then multi-select the tables to delete in the right pane, and click the Delete button.
See the following sections for more information:
For information about deleting data from Cloudview, see "Viewing, Inserting, and Modifying Data".

Examining Objects
For information on how to locate and look at existing dictionary objects, see the following sections:

Views
This section covers the following topics:
Creating a View
- To create a new view, do one of the following:
- Type the view name in the Name box.
- Choose the Schema for the view. By default, the schema is APP.
- In the Query field, specify the statement text for the view. If necessary, you can use the Auto Text feature to help populate your SQL statement:
- Click the Auto Text icon above the Query window to see a list of tables and columns in the current database.
- Optionally, click the Execute icon to test the statement. The results are displayed in the bottom, right panel.
- If satisfied with the view definition, click OK to save the view. To cancel view creation, click CANCEL.

Examining the Statement That Created a View
To view the statement that created a view:
- Choose the view name in the left panel.
- Choose the View tab in the right panel.
The statement that created the view appears in the Query window in the right panel.

Examining Data in a View
To view data in a view:
- Choose the view in the left panel.
- Click the execute icon (the lightning bolt).
Built-in data type values appear in a table grid in the bottom pane. If a column contains an object, the object's toString value appears. To inspect the object, choose it and click the Inspect button (see "Inspecting Java Objects").
To stop loading data while the view statement is executing, click the Stop button.
Deleting a View
To delete a view:
- Highlight the view name in the left panel and choose Edit->Delete View from the menu
or
highlight the view name and click Delete in the right panel

Aliases
You can use Cloudview to create and view aliases of the following types:
- Method Alias
- Class Alias
- Work Alias (Work Unit)
- User Defined Aggregates
This section covers the following topics:
About Aliases
A Method Alias references a single Java method. See the Cloudscape Reference Manual for more information on method aliases.
A Class Alias refences a Cloudscape class.
A User Defined Aggregate references an aggregate function that you have defined. See the Cloudscape Reference Manual for more information on user-defined Aggregates.
A Work Unit Alias, available only with Cloudsync, references two Java methods: one at a target database, and one at the source. See the Cloudscape Synchronization Guide for more information on work units.

Creating Aliases
- To create a new alias, expand the Aliases node on the Cloudview object tree, and do one of the following to open the alias submenu:
- Right-click on the corresponding Aliases node (or an alias name) in the left panel, and choose New->Alias->.
- Highlight an Alias node (or an alias name) in the left panel, and choose New->Alias-> from the Edit menu or from the right panel (click the alias icon or New button).
- Choose the type of alias to create from the submenu.
The Alias Definition form displays.
- The Alias Type field is automatically populated with the type chosen in step 2 (Method, Class, User Defined Aggregate, or Work Unit).
- Fill in the remaining fields:
- For a Method Alias, enter the alias name, class name, and method name.
- For a Work Unit Alias, enter the alias name as well as the class and method names for both source and target.
- For a Class Alias, enter the alias name and the class name.
- For a User Defined Aggregate, specify an alias name and the name of the class.
- Click OK after the fields are filled in.

Examining Existing Aliases
To see an alias's type and method(s):
- Click the alias's icon in the left panel.
- Choose the Alias tab.
To see the statement that created the alias:
- Click the alias' icon in the left panel.
- Choose the SQL tab.
Deleting Aliases
To delete an alias:
- Expand the Aliases icons in the left panel to display the aliases of the type you want to delete.
- Choose the alias or aliases you want to delete in the right panel.
- Click the Delete button.
You can also right-click the alias you want to delete in the left panel, then choose Delete from the popup menu.

Stored Statements
You can create and compile stored statements from Cloudview. This section discusses:

Creating Stored Statements
- To create a new stored statement, do one of the following:
- Right-click on the Database or Stored Statement icon in the left panel, and choose New->Stored Statement.
- Highlight the Stored Statement node in the left panel, and choose New->Stored Statement from the Edit menu or from the right panel (click the Stored Statement icon or New button).
The definition form displays in the right panel.
- On the Stored Statement tab, specify the Name and Schema for the stored statement.
- In the Query field, type the text of the stored statement.
- Optionally, specify one or more parameters in the Parameters field.
- To test the statement, click the execute button (the lightning bolt).
- To view the CREATE, DROP, and ALTER statements for the stored statement, click the SQL tab.

Examining Stored Statements
Follow the steps in this section to view stored statements.
- To view a stored statement, expand the stored statement icon in the left panel, and highlight the statement name. The right panel displays the Stored Statement and SQL tabs for that item.
The Stored Statements tab displays the Name, Schema, Query, and Parameters associated with the stored statement.
- To view the CREATE, DROP, and ALTER statements for the stored statement, click the SQL tab.
- To include system stored statements in the list of stored statements, choose View->System Stored Statements from the Cloudview menu.
Recompiling Stored Statements
If a stored statement definition has changed since it was last compiled, you can recompile it from Cloudview.
- Highlight the statement name in the left panel (if necessary, expand the database tree to display the Stored Statements node).
- Right-click and choose Recompile.
Executing Stored Statements
- To execute a stored statement, first select it in the left panel, so that it is open in the right panel.
- If the stored statement takes parameters, specify them in the Parameter field. Separate multiple parameters with a comma.
NOTE:
The Parameters field is only enabled if the statement contains a dynamic parameter.
NOTE:
Parameters are used for optimization only. You must specify parameters (if any) when executing.
- Click the lightning bolt icon to execute the statement.
Deleting Stored Statements
To delete a stored statement:
- Right-click the stored statement name in the left panel and choose Delete Stored Statement or highlight the statement name and click Delete in the right panel.

Jar Files
You can add one or more Jar Files to your database, and then execute a class that it contains from Cloudview.
This section covers the following topics:

Adding a Jar File
- To add a jar file to your database, do one of the following:
- Right-click on the Database or Jar Files node in the left panel, and choose New->Jar File.
- Highlight the Jar Files node in the left panel, and choose New->Jar File from the Edit menu or from the right panel (click the Jar File icon or New button).
- In the right panel, specify the Name and Schema for the Jar File item. The name specified in this field does not need to match the jar filename.
- In the Jar File Path field, type the fully qualified pathname of an existing Jar file. Optionally, click the Browse button (...) to locate the file using a browser.
- To add the Jar File Path to your database classpath, check the box labeled Add to Classpath.
- Click OK to save.

Executing a Jar File
After you have added a Jar File, you can execute it from the Jar Files Execute' tab.
- In the left panel, highlight a Jar File name.
- In the right-side panel, choose the Execute tab.
- In the Class Name field, specify Start Application or Test Class Loader. If you select Start Application, specify the name of the class to be invoked with the invokeMain method; if you select Test Class Loader, specify the name of the class to be invoked with the Class.forName method
Notice the statement in the SQL field is populated with the value entered in the Class Name field.
- To start the application, choose the Start Application radio button and click Execute. If successful, the application runs as normal. If unsuccessful, an error is returned.
- To verify the database class path has been updated and the class properly loaded, choose the Test Class Loader radio button and click Execute.
- To create a new instance of the class, choose the Create New Instance radio button and click Execute.
- The Call Static Method radio button displays an example of the statement to call a static method in the class.

Examining Jar Files
To view information about Jar files you have previously added to a database:
- Expand the Jar Files node in the left panel.
- Click the name of the Jar File you want information about.
The right panel contains three tabs:
- The Jar File tab lists the file name, the schema, and the jar file path. To specify a different path for the jar file, click the ... button to the right of the field.
- Use the Execute tab for Executing a Jar File.
Removing Jar Files
To remove a Jar file you have previously added to a database:
- Expand the Jar Files node in the left panel and do one of the following:
- Right-click the name of the Jar File you want to remove, and choose Delete Jar File.
- Highlight the Jar File name to delete, and choose Edit->Delete Jar File from the menu.
The Jar file is removed from the database.
Updating and Replacing Jar Files
You can update or replace a jar file after it has been added to a database.
- Select the Jar File name in the Navigation panel.
- In the right panel, verify the Jar File tab is selected.
- Type the name of the updated or new jar file to replace the currently stored jar file (or use the ... button to browse for the file).
- Click OK to save changes.
NOTE: You cannot modify the Cloudscape name for a stored jar file.
You can also use the ReplaceJar method from the database SQL window to replace a jar file. The syntax for this statement is on the SQL tab of the Jar File editing panel.
CALL (CLASS COM.cloudscape.tools.dbclasses).
replaceJar('APP', 'CurrentJarFile', 'NewJarFile');
Substitute the Cloudview Jar File object name for JFileObjNm, and the updated jar file name for NewJarFile.

Publications
Publications are used only with Cloudsync, Cloudscape's synchronization option. The Publications icon appears in Cloudview only if you have installed Cloudsync. You can only create Publications for databases defined as Source Databases.
NOTE: This section assumes you understand publications in Cloudsync. For information on publications, see the Cloudscape Synchronization Guide.
A publication and the parameters passed to it define a target database. Since a publication is similar to a database definition, the tree in the navigation panel below a publication name contains the same objects as the tree below a database name.
This section covers the following topics:

Creating a Publication
You can create a Publication from any Source database. Designate a database to be a source database at the time you create it, or by using the convertToSource parameter once the database has been created.
- To create a publication, do one of the following:
- Right-click the Publications icon or a publication name in the left panel, and choose New->Publication.
- Highlight the Publications node in the left panel, and choose
New->Publication from the Edit menu or from the right panel (click the New button).
The right panel displays four tabs:
- Publication Tab
- Items Tab
- Properties Tab
- SQL Tab
Publication Tab
- On the Publication tab, type a name for the publication in the Name field.
- Choose a schema name in the Schema field.
- To specify parameters for the publication, click the plus symbol above the Parameters field.
Define the following information for new parameters:
- Name. The name of the parameter. See "Case Sensitivity in Table and Column Names" for information about how case is handled in new parameters.
- Type. The data type of the parameter. Choose an SQL-J built-in type or SERIALIZE from the drop-down menu.
- Default. The default value for this parameter. Choose NULL to set the parameter default to NULL. Choose None to specify no default.
- Length. The length of the parameter. Alter this value only for CHAR, VARCHAR, BIT, and BIT VARYING columns. Leave this value blank for all other data types.
- Precision. Enter a precision value only 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 parameters. Leave blank for other data types.
- Class. The Java class name. If the data type is SERIALIZE, enter a class name in this column.
Items Tab
To define which dictionary objects are included in the publication, choose the Items tab.
The Items tab displays all items in the database. Checked items will be included in this publication. By default all items are selected. Select or de-select checkboxes according to your publication needs.
NOTE: Keys and Indexes associated with published tables are included implicitly.
Properties Tab
On the properties tab, specify the properties to be associated with the publication. For more information about the specific properties, see Description of Properties.
NOTE: The ability to specify publication properties from Cloudview is new in version 3.5.
SQL Tab
The SQL tab contains the SQL statement associated with the publication.
Click OK to create the publication.
NOTE: If you are unable to view or create Publications, verify that Cloudsync is installed, that your database has been created as (or converted to) a source database, and that your system path lists Cloudsync before any other Cloudscape products.
To modify an existing publication, simply change the selections on the Items tab.

Accessing a Publication
To access an existing publication, highlight the publication name in the left panel.
The Publications tab in the right panel shows:
- the publication name
- the schema that the publication is in
- the publication's parameters
The Items tab displays the dictionary objects included in the publication. This tab is used when creating new publications, and cannot be modified here for existing publications. To modify an existing publication, submit an ALTER PUBLICATION statement in the SQL window for the database.

Examining the CREATE PUBLICATION Statement
When a publication is highlighted in the left pane, the SQL tab shows the statements that can be used to create or drop the publication.
NOTE: These statements may be incomplete, and should be used as guidelines only.

Examining Publication Objects
To see the list of objects in a publication, expand the publication node in the left panel. Expand an item type node to see the included objects of that type. Highlight an item name to see its properties displayed in the right panel.
Views
To see the views in a publication, click the Views icon under Publications in the left panel.
The views in the publication are displayed in the right panel.
To see which view columns are included in a publication, double-click the view item in the Views list, or choose the view item directly in the left panel. The published columns of that view are checked in the Included column.
Indexes
To see the indexes and keys in a publication:
- Choose the table within the publication in the left panel.
- Choose the Indexes tab in the right panel.
The indexes and unique constraints included in the publication are checked in the Included column.
Check Constraints
To see the check constraints in a publication:
- Choose the table within the publication in the left panel.
- Choose the Checks tab in the right panel.
The check constraints included in the publication appear in the right panel.
Work units in the publication are listed in the right panel.
Creating a Copy File
- To create a new copy file, do one of the following:
- Right-click on a publication name in the left panel, and choose Create Copy File.
- Highlight a publication name in the left panel, and choose New->Copy File from the Edit menu.
- In the Save dialog, specify a name and location for the copy file.
- If parameters are necessary for the copy file, a box displays to enter them in. Enter each value as you would in an SQL statement. For example, for a parameter of data type CHAR, enclose the string in single quotes (').
When you are finished entering all the parameters, click OK.
Deleting a Publication
To delete a publication, highlight the publication name in the left panel, then choose Delete.
Refreshing a Target
The refresh operation synchronizes a target database with the source database. It is run at a target database.
To run the refresh operation from within Cloudview, choose the target database in the left panel, then do one of the following:
- Right-click the target database in the left panel, then choose Refresh Target.
- Type the statement refresh in the database SQL window, and click the execute icon.
For more information on the refresh operation, see the Cloudscape Synchronization Guide.

Triggers

Creating a Trigger
To create a trigger from Cloudview, do one of the following:
- Right-click the Triggers icon or a trigger name in the left panel, and choose New->Trigger from the popup menu.
- Highlight the Triggers node in the left panel, and choose
New->Trigger from the Edit menu or from the right panel (click the New button).
The Trigger panel displays on the right side.
Use the tabs in the Trigger panel to build a trigger definition.
Triggers Tab
- In the Name field, type a name for the trigger.
- In the Schema field, choose a schema. This value can be any schema except for SYS.
- In the Event field, choose the action for the trigger:
- INSERT
- DELETE
- UPDATE
If you choose UPDATE, you can specify a column list on the Options tab.
- In the Firing Time field, choose whether the trigger should fire BEFORE or AFTER the event.
- In the Table field, choose the table on which the trigger should act.
- In the Trigger Action field, specify the statement that the firing trigger will execute. The trigger action can be any valid SQL-J statement, including one with Java method calls. For example:
UPDATE CustomizedTours SET getTotalCost =
customized_tour.getTotalCost()
NOTE:
The trigger action can reference database objects in tables other than that specified in step 5. However, if any of these database objects is dropped, the trigger is invalidated.
NOTE:
There are restrictions on a trigger action, for example it may not contain dynamic parameters or modify or drop the table on which the trigger is defined. For a complete list of restrictions, see the Cloudscape Developer's Guide.
Options Tab
On the Options tab, optionally specify referencing information for the trigger. Typically, you specify referencing information when the trigger action refers to data that is currently being changed by the database event that fired the trigger. The trigger may need to refer to the old (pre-change, or "before") values or the new (post-change or "after") values.
- If the qualifying event is an UPDATE clause, the top of the Options tab displays a list of the columns in the affected table. Select which columns should fire the trigger when updated. (To multi-select hold down the Shift key while clicking or arrowing)
- Optionally, in the Referencing field specify an OLD or NEW transition variable. This value is used when a trigger action must reference the table or row affected by the qualifying event. Specify OLD to refer to table or row values prior to the change, or NEW to refer to the table or row values after the change.
NOTE:
INSERT row or table triggers cannot reference an OLD row or table. DELETE row or table triggers cannot reference a NEW row or table.
NOTE:
Steps 3 through 5 only apply if you specify a value for this field.
- In the Reference Type field, choose whether you are referencing an old or new ROW or TABLE.
- In the Reference As field, optionally specify an alias or correlation name for the NEW or OLD transition variable. You can specify your own alias, or keep the default identifier AREFERENCE.
Specify a value for Reference As if you do not want to use the default variable names OLD or NEW. For example, if a DELETE operation fires a row trigger that references OLD values, you could specify the alias DELETEDROW for use in place of OLD'. You could then use DELETEDROW in the trigger action statement.
- In the For Each field, choose whether this is a Row trigger or a Statement trigger.
- Statement triggers (the default) fire once per qualifying event (regardless of whether rows are modified by the qualifying event).
- Row triggers fire once for each row affected by the qualifying event. If no rows are affected, the trigger does not fire.
- At the bottom of the Options tab, toggle the state of the Enable Trigger checkbox to enable or disable the trigger. By default, the trigger is enabled.
SQL Tab
The SQL tab displays the trigger creation text, as well as a statement for enabling or disabling the trigger, and the statement for dropping the trigger.
Deleting a Trigger
To delete a trigger, right-click on the trigger name in the navigation panel and choose Delete Trigger from the popup menu. You can also click the Delete button when the trigger name is highlighted.
Enabling or Disabling a Trigger
You can enable or disable a trigger by
- typing a disable statement in the SQL window for the database
- choosing enable or disable from one of the Cloudview menus
- checking or unchecking the Enabled checkbox on the Options tab for a trigger.
To enable or disable multiple triggers at one time, follow these steps:
- In the left panel, highlight the top-level Triggers node.
- In the right panel, highlight one or more trigger names. Multi-select by holding down the mouse and dragging, or by holding down the Shift button while you arrow down.
- Right-click, and choose Enable Trigger or Disable Trigger.

Viewing a Trigger
You can use Cloudview to view existing triggers. Expand the navigation tree to display existing trigger names, and highlight the name of the trigger you want to see. In the right pane, the Trigger and Options tabs show information about the trigger, including name, schema, and action text. The SQL tab displays the trigger creation text, as well as the statement to enable or disable the trigger and the statement to drop the trigger.