
Databases
When a Database is selected in the left-side panel, the following tabs appear in the Edit Panel:
- Database Tab
Execute queries, view query results, and inspect java objects contained in the database.
- Statistics Tab
View statistics for a query.
- Properties Tab
Set Database properties.
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:
- Highlight the database in the left panel. (Statements are sent only to the selected database.)
- Clear the SQL window if necessary. To do this, click the Clear icon
.
- 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).
- 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:
- Click the Script icon above the SQL window, then click Open.
- Choose a file using the file browser, then click Open.
The contents of the file appear in the SQL window.
- Optionally, edit the SQL-J statements.
- 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:
- Click the Script icon, then click Save.
- Use the file browser to navigate to the desired directory.
- Enter the name of the file in the File Name field.
- 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:
- From the SQL Window, click the Auto Text icon
.
- 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.
- To see a list of column names in a particular object, choose the table or view name from the menu.
- 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,
- Check the Use Statistics box on the Databases tab, displayed when a database node is highlighted in the left-pane.
- 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:
- Choose the database on which you originally ran the statement.
- Highlight the statement and copy it to the clipboard. (Use your operating system's procedures to highlight and copy.)
- Choose the database on which to run the statement.
- Paste the statement into the SQL window. (Use your operating system's procedures to paste.)
- 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
- To shut down and close the database, choose the database in the left panel.
- Then do one of the following:
- right-click on the database name and choose Shutdown Database
- click the database icon on the right panel and choose Shutdown Database
- choose Shutdown Database from the Edit menu
The database is shut down and closed.
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
- To set database properties, highlight the database name in the left pane, and choose the Properties tab in the right pane.
- 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:
- If not already opened, open the database in Cloudview.
- Highlight the name of the non-Source database in the left panel.
- Do one of the following:
- right-click and choose Convert to Source from the popup menu.
- Choose Convert to Source from the Action button menu in the right panel.
- Choose Edit -> Convert to Source from the main menu.
If the Convert to Source menu option is disabled (grayed out), this may be due to one of the following:
- the database is already a source database and does not need to be converted
- Cloudsync is not installed
- your classpath does not include cloudsync.jar
For more information about Source databases, see the the Cloudscape Synchronization Guide.