Databases

When a Database is selected in the left-side panel, the following tabs appear in the Edit Panel:

This section discusses

For information about creating or opening databases, see Creating or Opening Databases.

Submitting SQL-J Statements

You can submit any supported SQL-J statement from within Cloudview. If the statement returns results, they appear in a grid in the bottom pane. The status bar under the bottom window displays the number of database rows affected. It also displays the compilation and execution time for the statement.

Execute SQL-J statements at the Cloudview Database level.

Database Tab

You can perform the following operations from the Database tab:

For information on supported SQL-J statements, see the SQL-J Grammar Index section of the Cloudscape Reference Manual.

SQL Window

The SQL window displays in the right panel when a database node is highlighted in the left panel.

Executing Statements from the SQL Window

To execute SQL-J statements against a database:

  1. Highlight the database in the left panel. (Statements are sent only to the selected database.)
  2. Clear the SQL window if necessary. To do this, click the Clear icon .
  3. Type an SQL-J statement or statements in the top window of the right panel. You can type any supported SQL-J command, including DDL statements. Separate multiple statements with semicolons.
    (The SQL-J Grammar Index section of the Cloudscape Reference Manual lists all SQL-J statements supported by Cloudscape products).
  4. Click the Execute icon (the lightning bolt), or press <Ctrl-e>.

    Any results appear in the bottom pane.

Results are displayed as each statement is executed. The currently executed statement is highlighted. If you click the Stop button while the statements are running, partial results are displayed for the statement running at the time execution is halted. If you do not stop execution, only the results from the final statement are displayed after execution completes.

If you execute an SQL-J statement that modifies the database or data, all changes are automatically saved. Cloudview always executes in auto-commit mode.

NOTE: It can be useful to open two Cloudview windows, one set to the SQL window in Details Only mode. This makes it easy to browse the database without having to navigate back to the SQL window each time you want to execute an SQL-J statement.

Executing from a Script File

To execute SQL-J statements from a file:

  1. Click the Script icon above the SQL window, then click Open.
  2. Choose a file using the file browser, then click Open.

    The contents of the file appear in the SQL window.

  3. Optionally, edit the SQL-J statements.
  4. Click the Execute icon.

    If any queries are executed, their results appear in the bottom window.

Saving SQL-J Statements to a File

To save the SQL-J batch that appears in the SQL window to a new file:

  1. Click the Script icon, then click Save.
  2. Use the file browser to navigate to the desired directory.
  3. Enter the name of the file in the File Name field.
  4. Click Save.

To save the batch to the same file, click the Script icon, then Save. The file you specify is overwritten each time you save the SQL-J batch. To save the batch to another file, choose Save As from the Script icon and specify another file name.

Using Auto Text

Auto text provides an easy way to look up Table, View, or Column names and include them in your SQL-J statements:

To use the auto text feature:

  1. From the SQL Window, click the Auto Text icon .
  2. Choose Tables or Views from the popup menu.
    The menu expands to display the names of all objects of the chosen type in the current database.
  3. To see a list of column names in a particular object, choose the table or view name from the menu.
  4. Click the name you want to use, and it automatically appears in the SQL window at the current cursor position.

Statistics

Runtime statistics provide information about how a statement is processed within Cloudscape.

To generate statistics,

  1. Check the Use Statistics box on the Databases tab, displayed when a database node is highlighted in the left-pane.
  2. To see the statistics, choose the Statistics tab after executing an SQL-J statement.

For information on using runtime statistics within Cloudview, see the Tuning Cloudscape guide.

Stopping a Statement

To stop a statement while it is executing, click the Stop button beneath the bottom window. The statement is stopped just before the next row is fetched. The status bar indicates how many rows have been fetched in the result so far, as well as the compilation and execution time for the terminated statement.

Seeing Previous Statements

To see statements that were run earlier on the current database, use the forward and back arrows above the SQL window.

If you issue multiple statements in a batch and then use the arrows, the entire batch appears in the window.

To execute a statement that was previously run on another database:

  1. Choose the database on which you originally ran the statement.
  2. Highlight the statement and copy it to the clipboard. (Use your operating system's procedures to highlight and copy.)
  3. Choose the database on which to run the statement.
  4. Paste the statement into the SQL window. (Use your operating system's procedures to paste.)
  5. Click Execute.

Case Sensitivity in the SQL Window

By default, database items created in Cloudscape are case-insensitive and are stored internally as all uppercase. To preserve the case of an item, use double quotes (") around the item in the SQL window.

For example, the following statement in the SQL window creates a table whose name is case-insensitive, and is stored and displayed as "MYTABLE":

create table MyTable (counter int)

However, the following statement creates a table whose name is case-sensitive and which is stored and displayed as "MyTable":

create table "MyTable" (counter int)

The following statement retrieves data from "MYTABLE" but not from "MyTable", since case-insensitivity is assumed:

select * from MyTable

See the Cloudscape Reference Manual for more information on case sensitivity in SQL-J.

Case sensitivity is handled differently if you create items in the SQL window than if you create the items directly using Cloudview tools. See "Case Sensitivity in Table and Column Names" for details.

Working with Dictionary Objects

From Cloudview, you can perform the following operations on most dictionary objects:

Closing the Connection to a Database

You can close a connection with or without explicitly shutting down the database. Shutting down a database lets Cloudscape perform a final checkpoint on it.

NOTE: Another VM cannot access a database unless it has been shut down.

Close

To close the connection to a database without shutting the database down, highlight the database and choose File -> Close.

Note that simply closing a database connection in embedded mode does not shut the database down.

Shutdown and Close

  1. To shut down and close the database, choose the database in the left panel.
  2. Then do one of the following:

NEW: The ability to shut down a database inside Cloudview is new in version 3.6.

Deleting a Database

You cannot delete a database from within Cloudview. To delete a database, simply delete the entire database directory from the operating system (this directory has the same name as the database).

Setting Database Properties

This section specifically discusses setting Database Properties from Cloudview. In-depth information about properties can be found in Tuning Cloudscape.

About Database Properties in Cloudview

Properties set within Cloudview have a database-wide scope. That is, the property is true for just the selected database (for the duration of the Cloudview session) and not for the other databases in the system unless it is set individually within each of them.

When you change these properties, they affect any conglomerates created after this change.

Properties Tab

  1. To set database properties, highlight the database name in the left pane, and choose the Properties tab in the right pane.
     

     
  2. Choose a type of property from the Property drop-down box. To see a list of all property types, choose ALL.

NOTE: For source or target databases (created with Cloudsync), the Properties tab includes Source and Target properties.

When you highlight a property name (in the Name column), the panel beneath the grid lists the Full Name of the selected property, whether it is a static or dynamic property, and the query Cloudview will use to set the property.

 

 

NOTE: The text in the Query field changes to reflect entries in the Value column.

Description of Properties

The following table lists the Properties you can set from Cloudview, grouped by type:

Database Property Descriptions
Type Property Description
Database

classpath

One or more fully-qualified jar file name(s) for use by the class loader. ('schema.jarfile'). Separate multiple file names with a colon. For example

APP.ToursLogic:APP.GUILogic.

defaultConnectionMode

Default connection mode for the database (noAccess, readOnlyAccess, or fullAccess)

fullAccessUsers

Comma-delimited list of users with read-write access. ('user1,user2')

noAutoBoot

Set to true to suppress automatic booting of this database when the bootAll property has been turned on. If bootAll is not set, or is set to false, noAutoBoot is irrelevant.

propertiesOnly

If set to true, database-wide properties for this database cannot be overridden by system-wide properties. false by default.

readOnlyAccessUsers

Comma-delimited list of users with read-only access. ('user1,user2')

Authentication

ldap.searchAuthDN

Specifies the Distinguished Name for an LDAP user search. ('uid=uname'). Use with searchAuthPW, described next.

ldap.searchAuthPW

Password for the user configured by
searchAuthDN. ('passwd')

ldap.searchBase

The root DN from which to begin a search for the guest or user DN. ('uid=uname')

ldap.searchFilter

Specifies a DN search filter to use during user authentication. Set to the value
cloudscape.user to search all users defined in the cloudsacpe.user.UserName property.
( (&(objectClass=person)(uid=uname)) or (coudscape.user) ).

provider

Specifies an authentication provider for Cloudscape user authentication. Values include LDAP, NIS+, CLOUDSCAPE, or a complete Java class name. If setting LDAP or NIS+, you must also set the server property, described next.

server

Location of the user-authenticating external directory service defined by the provider property. For LDAP, specify hostname:portnumber. For NIS+, specify the nisServerName and the nisDomainName.

Language

bulkFetchDefault

The number of rows Cloudscape fetches at a time when reading a table or index. Default value is 16.

defaultIsolationLevel

Default isolation level for the database. Legal values are SERIALIZABLE or READ_COMMITTED.

logStatementText

If set to true, at the beginning of statement execution Cloudscape writes the text and parameter values to the information log. false by default.

preloadClasses

If set to true, Cloudscape preloads compilation classes at database bootup. Boot time is slower, but query compilation time is much faster. false by default.

spsCacheSize

Size of the database's stored prepared statement cache. The default is 32.

stalePlanCheckInterval

Number of times a statement executes before checking to see if the statement is stale. The default value is 100. The minimum value is 5.

statementCacheSize

Number of statements to cache per connection. By default, 20.

triggerMaximumRecursionLevel

Maximum recursion level for trigger firing. By default, 16. A value of -1 means there are no limits to recursion. A value of 0 means no trigger will ever fire.

Locks

deadlockTimeout

The number of seconds to wait before attempting to detect a deadlock for the waiting transaction. Default is 60. If this value is higher than waitTimeout, no deadlock checking occurs.

deadlockTrace

If set to true, causes a stack trace of all threads involved in a deadlock to be written to the error log. This property has meaning only when the monitor property is set to true.

escalationThreshold

The maximum number of rows the optimizer will lock before simply locking the entire table (during compilation). At runtime, Cloudscape uses this property to determine locking escalation (independent of any compilation decision). By default, 5000. The minimum value is 100.

monitor

If set to true, all deadlock errors are logged to the error log. The default value is false.

waitTimeout

The number of seconds to wait before aborting a transaction waiting for a lock.
0 means abort the transaction upon encountering a lock wait. A negative value means to wait forever. The default value is 180.

Other

user.UserName

A password (if provider is set to cloudscape), or a user DN (if provider is set to ldap and searchFilter is set to cloudscape.user). Substitute the user name for UserName, and passwd or userDSN for the value.

connection.requireAuthentication

If set to true, all connection requests must include a valid user name a password. The value of the provider property determines the type of user authentication. The default value is false.

encryptionAlgorithm

encryptionProvider

These are database connection URL attributes which cannot be set from here. To use these in Cloudview, specify them on the Connection tab before creating a database. For more information about these attributes, see the Cloudscape Reference Manual.

Storage

minimumRecordSize

Specifies the minimum row size (in bytes) to use when creating a table. The default value is 12 bytes.

pageReservedSpace

The percentage of space reserved for table updates. The default value is 20 percent.

pageSize

The page size (in bytes) for tables or indexes used during table or index creation. This value should be a power of 2. The default value is 4096.

rowLocking

If set to false, row-level locking is disabled. The default value is true.

tempDirectory

Specifies the directory in which to write temporary files. By default, this property is set to a tmp directory beneath the database directory. The value specified for this property does not need to be enclosed in quotes. (c:\dirname)

Source

refresh.guaranteedRefreshInterval

Specifies the maximum amount of time (in hours or days) a target can wait between refreshes. Specify int {h | d} where h indicates hours and d indicates days. The default value is 7d.

database.refresh

If set to OFF, the database will not accept refresh requests. The default value is ON.

dataComm.listenType

Specifies whether to manage refresh messages using the servlet API or built-in HTTP support. Possible values are servlet, or builtinHttp (the default).

dataComm.port

The port number on which the system's source databases expect to receive refresh messages. By default, the port number is 2001. This property is only meaningful when dataComm.listenType is set to builtinHttp.

listener.synchronization.address

The URL to which (subsequently created) targets should send refresh messages. With the servlet API, there is no default value. With build-in HTTP support, the default is http://hostname:port/application. The word application is required.

database.synchronizationCleanup

If set to false, synchronization-related file cleanup does not occur automatically at the source database. Typically this property is set to false only when enhancing performance during peak load times. The default value is true.

Target

database.readAccessTarget

If set to true, write operations (such as INSERT, UPDATE, and DELETE) are prohibited on the target (although the target can still be refreshed). The default value is false.

database.sourceUser

An identifier for the target (used during refresh). If not specified, this defaults to the user name. ('value')

datacomm.url

If set to true, Cloudview uses the standard JDK URL support for post messages (enabling SSL support). When set to false (the default), Cloudview uses Java Sockets.

synchronization.workUnitOnly

If set to true, this property forces targets to use work units to update data.

Converting to a Source Database

Source databases are synchronization-enabled. If you have installed Cloudsync, you can convert a non-source database to a source database using the convertToSource attribute or from within Cloudview.

NEW: The ability to convert a database from inside Cloudview is new in version 3.6.

To convert a standard database to a Source database, follow these steps:

  1. If not already opened, open the database in Cloudview.
  2. Highlight the name of the non-Source database in the left panel.
  3. Do one of the following:

If the Convert to Source menu option is disabled (grayed out), this may be due to one of the following:

For more information about Source databases, see the the Cloudscape Synchronization Guide.