You can use the DataDirectory macro in the specification of database filenames to provide support for relative pathnames. For more information on the DataDirectory macro, see Establishing Connections. If you do not use the DataDirectory macro, relative pathnames are relative to the current directory of the process in which Blackfish SQL is executing. On Java platforms, the user.dir property dictates how database filenames are resolved when a fully qualified path name is not specified. The Java Virtual Machine (JVM) defaults this property to the current working directory of the process. You can set this property with a JVM command line option. For example:

-Duser.dir=/myapplication

You can also set this property from within a Java application by using the java.util.System.setProperty method.

System logging is performed for all connections and all databases accessed in the same process. 

You can enable Blackfish SQL system logging in the following ways. 

For the local Blackfish SQL client: 

Set the blackfishsql.logFile system property to the name of the file to which the log output should be written. If you set this to con, the log output is displayed to the console. You can specify the types of operations to include in the log file by setting the blackfishsql.logFilters property.  

For the remote Blackfish SQL client: 

In the Blackfish SQL configuration file set the blackfishsql.logFile property to the name of the file to which the log output should be written. If you set this to con, the log output is displayed to the console. You can specify the types of operations to include in the log file by setting the blackfishsql.logFilters property.

All Blackfish SQL system properties are case sensitive and begin with the blackfishsql. prefix. The SystemProperties class has constant strings for all system properties. For Windows system properties: 

If your application uses the Blackfish SQL server, set system properties in the BSQLServer.exe.config file. If your application does not use the Blackfish SQL server, set system properties by calling the System.AppDomain.CurrentDomain.SetData method. 

For Java system properties: 

If your application uses the Blackfish SQL server, set system properties in the BSQLServer.config file by prefixing the property setting with vmparam -D. If your application does not use the Blackfish SQL server, set system properties by calling the System.setProperty method.

For Blackfish SQL for Java, these are additional logging options:

• If you are using a javax.sql.DataSource implementation, call the setLogWriter method of the DataSource implementation. See com.borland.javax.sql.JdbcDataSource and com.borland.javax.sql.JdbcConnectionPool.
• Call the java.sql.DriverManager.setLogStream method.
• Call the java.sql.DriverManager.setLogWriter method.

Database logging output is performed on a per-database basis and is sent to the status log files for that database. The lifetime of status log files is managed in the same fashion as the transactional log files for the database. When a transactional log file is dropped, the corresponding status log file is dropped also. When you create a database, status logging is disabled by default. You can enable database status logging by calling the DB_ADMIN.ALTER_DATABASE built-in stored procedure. You can set the log filtering options for all connections to a database by calling the DB_ADMIN.SET_DATABASE_STATUS_LOG_FILTER built-in stored procedure. You can set the log filtering options for a single connection by setting the logFilter connection property or by calling the DB_ADMIN.SET_STATUS_LOG_FILTER built-in stored procedure.

Locks can fail due to lock timeouts or deadlocks. Lock timeouts occur when a connection waits to acquire a lock held by another transaction and that wait exceeds the milliseconds set in the lockWaitTime connection property. In such cases, an exception is thrown that identifies which connection encountered the timeout and which connection is currently holding the required lock. The transaction that encounters the lock timeout is not rolled back. 

Blackfish SQL has automatic, high speed deadlock detection that should detect all deadlocks. An appropriate exception is thrown that identifies which connection encountered the deadlock, and the connection with which it is deadlocked. Unlike lock timeout exceptions, deadlock exceptions encountered by a java.sql.Connection cause that connection to automatically roll back its transaction. This behavior allows other connections to continue their work. 

Use the following guidelines to detect timeouts and deadlocks:

• Read the exception message from the timeout or deadlock. The message has information on what tables and what connections are involved.
• Enable system or database logging. To restrict log output to lock-related issues, set the log filter options to LOCK_ERRORS.
• Use the DB_ADMIN.GET_LOCKS built-in stored procedure to report locks held by all connections.

A connection usually requires a lock when it either reads from or writes to a table stream or row. It can be blocked by another connection that is reading or writing. You can prevent blocks in two ways:

• Minimize the life span of transactions that write.
• Use read-only transactions, since these do not require locks to read.

Connections should use short-duration transactions in high concurrency environments. However, in low- or no-concurrency environments, a long-duration transaction can provide better throughput, since fewer commit requests are made. There is a significant overhead to the commit operation because it must guarantee the durability of a transaction.

Read-only transactions are not blocked by writers or other readers, and since they do not acquire locks, they never block other transactions. 

Setting the readOnlyTx connection property to true causes a connection to use read only connections. Note that there is also a readOnly connection property, which is very different from the readOnlyTx connection property. The readOnly connection property causes the database file to be open in read only mode, preventing any other connections from writing to the database.  

For Blackfish SQL for Java JDBC connections you can also enable read-only transactions by setting the readOnly property of the java.sql.Connection object or the com.borland.dx.sql.dataset.Database.getJdbcConnection methods to true. When using Blackfish SQL for Java DataStoreConnection objects, set the readOnlyTx property to true before opening the connection. 

Read-only transactions work by simulating a snapshot of the Blackfish SQL database. The snapshot sees only data from transactions that are committed at the point the read-only transaction starts; otherwise, the connection would have to check if there were pending changes and roll them back whenever it accessed the data. A snapshot begins when the connection opens. The snapshot is refreshed each time the commit method is called.

If you suspect that cache contents were not properly saved on a non-transactional Blackfish SQL database, you can verify the integrity of the file by calling the DB_ADMIN.VERIFY built-in stored procedure.  

Note that transactional Blackfish SQL databases have automatic crash recovery when they are opened. Under normal circumstances, Blackfish SQL databases do not require verification.

This section provides more Java-specific troubleshooting guidelines.

The approach to debugging triggers and stored procedures depends on whether your application uses the local or remote JDBC driver. 

If your application uses the local JDBC driver, there is nothing special to set up, since the database engine is executing in the same process as your application. 

If your application uses the remote JDBC driver, you can use either of the following procedures.  

Using the DataStoreServer JavaBean for debugging: 

In your application, instantiate a com.borland.datastore.jdbc.DataStoreServer JavaBean component and execute its start method.  

Using the JdsServer for debugging: 

Complete the following steps:

1. Add the following lines to your <jds_home>/bin/JdsServer.config file: vmparam -Xdebug vmparam -Xnoagent vmparam -Djava.compiler=NONE vmparam -Xrunjdwp:transport=dt_socket,server=y,address=5000,suspend=y
2. Execute the JdsServer. The server will not come up until a remote debugger (such as the JBuilder debugger) is launched to attach to the JdsServer process on port 5000.

Creating an SQL table forces unquoted identifiers to be uppercase. You must quote the identifiers to enable case sensitivity. See “Identifiers” in the SQL Reference. 

When you use DataExpress components to create a table, the table and column names are case sensitive. If you specify these identifiers in lowercase or mixed case, SQL is not able to access them unless the identifiers are quoted.  

When you use DataExpress to access a table, the StorageDataSet storeName property is case sensitive. However, the column identifiers can be referenced in a case-insensitive fashion. Consequently, for DataExpress, you can access an address column by using ADDRESS or address. 

The simplest way to avoid problems with identifiers for both SQL and DataExpress components is to always use uppercase identifiers when your application creates or accesses tables.

Set the saveMode property to 2 when you are debugging an application that uses a non-transactional Blackfish SQL database. The debugger stops all threads when you are single-stepping through code or when breakpoints are hit. If you do not set the saveMode property to 2, the Blackfish SQL daemon thread cannot save modified cache data. For more information, see “Non-transactional Database Disk Cache Write Options” in Optimizing Blackfish SQL Applications.

Sun Microsystems makes changes to the java.text.CollationKey classes from time to time as it corrects problems. The secondary indices for tables stored inside a Blackfish SQL database use these CollationKey classes to generate sortable keys for non-US locales. When Sun changes the format of these CollationKeys classes, the secondary indexes created by an older Sun JDK may not work properly with a new Sun JDK. The problems resulting from such a situation manifest themselves in the following ways:

• Locate and query operations might not find records that they should find.
• A table viewed in secondary index order (by setting the StorageDataSet.sort property) might not be ordered properly.

Currently, the only way to correct this is to drop the secondary indices and rebuild them with the current JDK. The StorageDataSet.restructure() method also drops all the secondary indexes.

Preface 

Overview 

System Architecture 

Establishing Connections 

Administering Blackfish SQL 

Using Blackfish SQL Security 

Using Stored Procedures and User Defined Functions 

Using Triggers in Blackfish SQL Tables 

Stored Procedures Reference 

SQL Reference 

Optimizing Blackfish SQL Applications 

Deploying Blackfish SQL Database Applications