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

  1. To create a new view, do one of the following:
  2. Type the view name in the Name box.
  3. Choose the Schema for the view. By default, the schema is APP.
  4. 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:
  5. Optionally, click the Execute icon to test the statement. The results are displayed in the bottom, right panel.
  6. 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:

  1. Choose the view name in the left panel.
  2. 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:

  1. Choose the view in the left panel.
  2. 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:

Aliases

You can use Cloudview to create and view aliases of the following types:

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

  1. 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:
  2. Choose the type of alias to create from the submenu.
    The Alias Definition form displays.
  3. The Alias Type field is automatically populated with the type chosen in step 2 (Method, Class, User Defined Aggregate, or Work Unit).
  4. Fill in the remaining fields:
  5. Click OK after the fields are filled in.

Examining Existing Aliases

To see an alias's type and method(s):

To see the statement that created the alias:

Deleting Aliases

To delete an alias:

  1. Expand the Aliases icons in the left panel to display the aliases of the type you want to delete.
  2. Choose the alias or aliases you want to delete in the right panel.
  3. 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

  1. To create a new stored statement, do one of the following:
  2. On the Stored Statement tab, specify the Name and Schema for the stored statement.
  3. In the Query field, type the text of the stored statement.
  4. Optionally, specify one or more parameters in the Parameters field.
  5. To test the statement, click the execute button (the lightning bolt).
  6. 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.

  1. 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.
  2. To view the CREATE, DROP, and ALTER statements for the stored statement, click the SQL tab.
  3. 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.

  1. Highlight the statement name in the left panel (if necessary, expand the database tree to display the Stored Statements node).
  2. Right-click and choose Recompile.

Executing Stored Statements

  1. To execute a stored statement, first select it in the left panel, so that it is open in the right panel.
  2. 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.
  3. Click the lightning bolt icon to execute the statement.

Deleting Stored Statements

To delete a stored statement:

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

  1. To add a jar file to your database, do one of the following:
  2. 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.
  3. 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.
  4. To add the Jar File Path to your database classpath, check the box labeled Add to Classpath.
  5. 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.

  1. In the left panel, highlight a Jar File name.
  2. In the right-side panel, choose the Execute tab.
  3. 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.
  4. 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.
  5. To verify the database class path has been updated and the class properly loaded, choose the Test Class Loader radio button and click Execute.
  6. To create a new instance of the class, choose the Create New Instance radio button and click Execute.
  7. 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:

  1. Expand the Jar Files node in the left panel.
  2. Click the name of the Jar File you want information about.
    The right panel contains three tabs:
  3. 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.
  4. Use the Execute tab for Executing a Jar File.

Removing Jar Files

To remove a Jar file you have previously added to a database:

  1. Expand the Jar Files node in the left panel and do one of the following:

Updating and Replacing Jar Files

You can update or replace a jar file after it has been added to a database.

  1. Select the Jar File name in the Navigation panel.
  2. In the right panel, verify the Jar File tab is selected.
  3. 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).
  4. 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.

  1. To create a publication, do one of the following:

Publication Tab

  1. On the Publication tab, type a name for the publication in the Name field.
  2. Choose a schema name in the Schema field.
  3. To specify parameters for the publication, click the plus symbol above the Parameters field.

    Define the following information for new parameters:

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 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:

  1. Choose the table within the publication in the left panel.
  2. 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:

  1. Choose the table within the publication in the left panel.
  2. 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

  1. To create a new copy file, do one of the following:
  2. In the Save dialog, specify a name and location for the copy file.
  3. 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:

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:

The Trigger panel displays on the right side.

Use the tabs in the Trigger panel to build a trigger definition.

Triggers Tab

  1. In the Name field, type a name for the trigger.
  2. In the Schema field, choose a schema. This value can be any schema except for SYS.
  3. In the Event field, choose the action for the trigger:
  4. In the Firing Time field, choose whether the trigger should fire BEFORE or AFTER the event.
  5. In the Table field, choose the table on which the trigger should act.
  6. 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.

  1. 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)
  2. 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.
  3. In the Reference Type field, choose whether you are referencing an old or new ROW or TABLE.
  4. 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.
  5. In the For Each field, choose whether this is a Row trigger or a Statement trigger.
  6. 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

To enable or disable multiple triggers at one time, follow these steps:

  1. In the left panel, highlight the top-level Triggers node.
  2. 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.
  3. 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.