SOLID Server Version 2.2 New Features

SOLID Server VERSION 2.2
NEW FEATURES

Overview

Ease-of-Deployment Features
Logical Data Source Names
Several Solid Installations within one Computer
Shrink Initial Databases
CD-ROM Support
UNIX Pipes Supported
Silicon Graphics IRIX Supported
SQL Functionality Enhancements
Schema Support
Returning Result Sets from Database Procedures
Row Number in Result Sets
Enhanced Tools

Details

This detailed list of new features in SOLID Server version 2.2 is ordered by the release number and affected subsystem. An exception to this rule is that the features promoting the main focus of this version are listed separately first irrespective of the affected subsystem.

Release 02.20.0027

SQL Functionality Enhancements

Release 02.20.0019

Ease-of-Deployment Features
SQL Functionality Enhancements
Server Functionality Enhancements
Communications Functionality Enhancements
Tools Functionality Enhancements
Changes to Supported Platforms

Release 02.20.0027 (compared to release 2.20.0019)

1 SQL Functionality Enhancements

1.1 Configuration Parameter JoinPathSpan Removed

The modified SQL optimization algorithm no longer needs a limit for the depth of the SQL parse tree to be spanned. Therefore, the solid.ini configuration parameter [SQL] JoinPathSpan has been removed. Setting a value for this parameter has no effect anymore.

Also, the SQL statement SET SQL JOINPATHSPAN has no effect, although the syntax is still accepted for compatibility reasons.

Release 02.20.0019 (compared to release 2.10.0002)

1 Ease-of-Deployment Features

1.1 Several Solid Installations within One Computer

All SOLID driver, interface, and communication library files are named according to the Solid version. This means that when a new Partner application that has SOLID Inside is installed onto a computer that already has Solid installed as part of other Partners' applications, all the SOLID DBMSs can happily co-exist without problems within the same computer.

1.2 Create Database and Exit

New SOLID Server command line options allow starting SOLID Server to only create the database and/or to execute some SQL script to create tables, indices etc. and exiting immediately after that. This feature is useful, for example, in an embedded setup utility, where the database needs to be created and closed first before SOLID Server can be started in the background as an NT service process. For details, see SOLID Server Administrator's Guide: Appendix G.

1.3 Logical Data Source Names

By using Logical Data Source Names, SOLID Server databases can be referred to and accessed from clients also by using a Logical Data Source Name, i.e., any user-specified character string. Now the data source names can be made self-documenting instead of using the network names, which tend to be rather cryptic on certain protocols.

Clients can still use the SOLID Server network name consisting of the communication protocol name and the unique identifier of the server. The logical name is translated to a network name according to the entries in either solid.ini, odbc.ini, or the Windows registry.

Using Logical Data Source Names means that SOLID databases can be named so that the name makes sense to the end user, e.g., "Enterprise Intelligence Database" instead of "tcp serverX 1234". This makes using SOLID databases more convenient to the power users of ODBC tools like Excel. Also, it will be easier for application developers to write portable applications and setup programs.

Please see SOLID Server Administrator's Guide for details.

1.4 Shrink Initial Databases

Based on Partner feedback, we have further developed SOLID Tools in order to make it straight-forward to export all data from a SOLID database and to reinsert the data into a new database. The result is a database with the desired contents and a minimal size in bytes.

The SOLID Server Administrator's Guide contains detailed instructions on how to perform the database compression using the soldd, solexp, and solload utilities.

We suggest compressing the initial databases that are mass distributed with Partner applications always when preparing evaluation or product packages, whatever the delivery media may be: diskette, CD-ROM, FTP, or Web.

1.5 Reset Database Age for Evaluation Databases

This is a service provided to our Partners who are distributing evaluation copies of their software with a SOLID Server database.

Partners can preset the contents of a SOLID Server database and have Solid erase the database creation time stamp after that. This way, the limited evaluation time starts only after the evaluator opens the database.

1.6 CD-ROM Support

In addition to the evaluation database creation and database compression utilities mentioned above, version 2.2 provides the following new features that support CD-ROM distribution:

read-only flag in server startup
read-only license option

Using the read-only startup option allows the database to reside on a CD-ROM device. This feature is useful when implementing tutorial applications that are run directly from CD-ROM and that need a demo database.

The read-only license option is mainly targeted for the information providers who wish to sell and distribute non-alterable data, even if the database is copied from CD-ROM to a hard disk.

2 SQL Functionality Enhancements

2.1 Schema Support

Table schemas allow several logical databases to reside in the same physical database. A typical use could be to have a similar table structure for each customer in the database of an accounting firm. All the data would still be stored in a single physical database, which allows sharing the common parameter information.

For schema usage instructions and syntax details, please refer to SOLID Server Administrator's Guide.

2.2 Enhanced Support for Joins

In addition to the already supported LEFT OUTER JOIN, now SOLID provides support also for the following join types:

•RIGHT [OUTER] JOIN
•FULL [OUTER] JOIN
•NATURAL JOIN
•CROSS JOIN
•UNION JOIN

Specifying join columns can now be done with: USING (...)

The abovementioned keywords are now reserved words that cannot be used as identifiers. For syntax details, please refer to SOLID Server Administrator's Guide.

Nested outer joins are also supported, which is indicated to applications by SQL API function call SQLGetInfo(SQL_OUTER_JOINS) returning value "F".

2.3 NULLIF and COALESCE Supported

Shorthand CASE expressions NULLIF and COALESCE are now supported. For syntax details, please refer to SOLID Server Administrator's Guide.

2.4. Returning Result Sets from Database Procedures

In version 2.2, SOLID Server can return multiple row result sets from database procedures. This enables application designers to have all database operations performed through database procedures when appropriate in order to hide the physical table structures from client applications or to solve data protection or network congestion problems.

2.5. Pseudo Column ROWNUM Implemented

The ROWNUM attribute may be used in SELECT and UPDATE statements to limit the number of rows in the result set that are returned to the client or processed. The ROWNUM semantics are similar with Oracles ROWNUM.

Please take note on the following points:

Only conditions in like ROWNUM < n (with no surrounding NOs or like that) are allowed. All other conditions would be meaningless and are therefore prohibited. (e.g. ROWNUM > 1 would always return zero rows).
New SQL error 86 "illegal ROWNUM constraint" is added.
The trick how to retrieve the specified row numbers from a query can be done with a view. To get every other row from table AA, do as follows:
CREATE VIEWS AAV (RN,...) AS SELECT ROWNUM,* FROM AA; SELECT * FROM AAV WHERE RN / 2 * 2 = RN;
Also the following kind of UPDATE statements work:
UPDATE T SET F = ROWNUM
ROWNUM is a new reserved word.

2.6 WHENEVER SQLERROR Supported

The syntax

EXEC SQL WHENEVER SQLERROR [ROLLBACK [WORK] ,] ABORT;

has been added to decrease the need for

IF NOT SQLSUCCESS THEN ...

tests after every executed SQL statement in a procedure.

2.7 SET STATEMENT MAXTIME Supported

The new SQL statement SET STATEMENT MAXTIME <minutes> sets connection-specific statement maximum execution time in seconds. The setting is effective until a new maximum time is given. A zero value means no maximum time, which is also the default. When a statement fails due to a timeout expiration, the SQL error code 14529 is returned to the application.

2.8 EXCEPT and INTERSECT Supported

Now the EXCEPT and INTERSECT statements are allowed in SELECT criteria. For details on the syntax, please see SOLID Server Administrator's Guide.

2.9 Table Name Shorthand Aliases Allowed

The syntax <table id> AS <identifier> was added as a new feature. The reserved word AS cannot be used as an identifier without double quotes.

2.10 UPDATE <table>.<column> Supported

Support for the following syntax was added: UPDATE <table>.<column> For details, please refer to SOLID Server Administrator's Guide.

2.11 "INTO" Optional in "INSERT INTO"

In the "INSERT INTO" statement, the word "INTO" is now optional. Therefore, the reserved word "INTO" cannot be used any more as an identifier name.

2.12 Accurate Data Type Mapping in Database Procedures

Now all the data type length and precision information are exploited also in database procedures. All data types behave in produres exactly as in the SQL API.

2.13 CASE Control Structure Allowed in Database Procedures

Support for the following syntax was added: CASE WHEN ... THEN ... ELSE ... END For details, please refer to SOLID Server Administrator's Guide.

2.14 Identifier Name Length Restriction Removed

Now the SQL identifier name length is unlimited.

2.15 SQL Info Logging Enhancements

Some new parameters to configure the SQL Info facility were added:

[SQL]SQLInfo
SQL-specific info level. Only SQL info output, no table level info. If not set, default taken from the [SQL]Info parameter.
[SQL]InfoFileName
Default global info file name.
[SQL]InfoFileSize
Max info file size, used only for user given info files or if InfoFileName is specified.
[SQL]InfoFileFlush
Default is yes, flushes info file after every write operation.

2.16 Several SQL Performance Enhancements

The SQL operations performance has been further improved by:

adding single-row subqueries optimization
adding DISTINCT-statement optimization in conjunction with ANY or ALL
improving range constraint's cost estimation - adjusting estimator parameter default values
using external sorter, when appropriate, to save central memory
optimizing ORDER processing to avoid redundant searches
introducing new, improved join path algorithm

3 Server Functionality Enhancements

3.1 More Efficient Group Commit

The log write in multithread environments has been improved to automatically execute group commit for write operations. This is done by queueing the write operations both before and after the logical log write operation. The grouping increases adaptively and prevents the log file flushing from becoming a bottleneck to the server performance.

The new group commit method is independent of the write mode and usually the default LogWriteMode is the best alternative. Note that the configuration parameter setting

[Logging] CommitMaxWait = n

has now become obsolete and should be removed from solid.ini.

Also the LogWriteMode=3 (lazy) is now obsolete and is implicitly replaced by Logwritemode=2 (overwrite last block mode)

3.2 Cursor Read Level Changed

The cursor read level has been changed. Previously the cursor was able to see changes that were made in the same transaction after the cursor was opened. For example, if the cursor was positioned onto row 2 and a new row was added in position 5, the new row was visible in following fetch calls. This caused some problems because the cursor behaved differently if the set was ordered using external sorter or index leaf buffering.

The new changed behaviour fixes the cursor read level to the point when the cursor was opened. Later changes in the same transaction are not visible to the cursor. At transaction commit or rollback the cursor read level is updated to the point of the start of the transaction.

3.3 Windows Info... Display

On Windows platforms (3.1x, 95, NT) the SOLID Server icon menu has the new option 'Info...'. It displays server startup messages including license and listening details in a message box. This helps to identify separate SOLID Server processes that are running on the same workstation.

3.4 ADMIN COMMAND Accepts Arguments that Contain Spaces

Server-internal command line argument parsing, which is used to process, e.g., ADMIN COMMAND staments now accepts double quoting to give arguments that contain spaces. Double quotes can be included in the command string by preceding them with a backslash (i.e. \").

3.5 Enhancements to the ADMIN COMMAND 'Userlist'

The ADMIN COMMAND userlist output has been changed. Instead of dividing users to control and normal users, it now displays the user type (e.g., SQL, SA, rcon, or solcon user).

Also, there are new arguments for the userlist command. The new extended syntax is: ul [-l] [name|id] Option -l prints the long list including more info such as user login time and current SQL statement details. The optional name or id argument requests the info for that one user only.

3.6 Configuration File Shorthand Syntax for Numeric Values

Numeric values in solid.ini (e.g., CacheSize) can now be given with the suffix k, K, m, or M.

The letter K stands for kilobytes (value * 1024) and
the letter M for megabytes (value * 1024 * 1024).

The parameter type must be long; this mechanism does not work for parameters of the type double.

3.7 Ability to Configure No Timeouts

Negative or zero value for the solid.ini configuration parameters [Srv]ConnectTimeOut and [Srv]AbortTimeOut mean infinite timeout.

3.8 Error Log File Size Limited

The error log file solerror.out size is limited to 1 MB. When the file grows over this limit it is renamed to solerror.bak, and a new solerror.out is started.

3.9 Minimum Cache Size Increased

The minimum size of the database cache in central memory has been increased from 10*blocksize to 20*blocksize.

3.10 Several Server Performance Improvements

Several performance optimizations have been implemented in the:

I/O Manager
Log Manager
Merge process
Backup process
Multi-thread operation
Aggregate functions execution
Large table index creation optimization

4 Communication Functionality Enhancements

4.1 UNIX Pipes Support

Now SOLID Server and the client applications can communicate locally within a UNIX host by using the UNIX Pipes protocol. The shorthand for the UNIX Pipes protocol is 'upipe' or 'unp'. E.g.:

[Com] Listen = unp MySOLID

This means that Desktop licenses can now be controlled also on UNIX platforms by allowing only UNIX Pipes connections. UNIX Pipes are implemented on AIX, Clix, HP-UX, IRIX, Linux, and Solaris platforms.

4.2 Unlimited Amount of Listening Sessions

The previous limitation of 10 concurrent listening sessions was removed. Now any number of listening sessions or asynchronous client connections is possible.

4.3 DECNet Client on 32-bit Windows Platforms

DECNet protocol is now supported for client communication on 32-bit Windows platforms. Requires Digital PATHWORKS 32 for Windows NT/95 to be installed.

4.4 NetBIOS Multiadapter Connection

When NetBIOS listening is started in Windows NT without specifying the adapter identifier (i.e., the lana number), the default now is "listen to all available lanas" instead of just lana 0, which often is not the wanted one or may even be unusable. The lana number containing the "Nbf" on the top level is used first.

Also, the client using the NetBIOS protocol without specifying any adapter tries first to connect to the server using the lana bound to "Nbf". If the connection is not established, all the available lanas are tried one at a time.

5 Tools Functionality Enhancements

5.1 Schema Support Related Changes

The following schema support related changes have been made to tools:

SOLID Datadictionary (soldd): - schema names are exported for tables - for other
entities SET SCHEMA statements are added

SOLID Export (solexp): - table names are stored in format schema.table - default file
name is now schema_table, except for 16-bit Windows only table name due to eight
character limit - schema, table and column names are printed in duoble quotes

SOLID Speedloader (solload, solloads): - schema name and double quotes are now
accepted in table name definition

For schema usage instructions and syntax details, please refer to SOLID Server Administrator's Guide.

5.2 Streamlined Operation; Command Line Options

The co-operations between soldd, solexp, and solload has been streamlined. Also, the command line options have been changed to be as consistent as possible between executables. For details, please see SOLID Server Administrator's Guide or run tool programs with the option: '-?'.

5.3 Long File Names in Solexp and Soldd

Solexp and soldd no longer truncate file names generated from table names to 8 characters except in Windows 3.x versions.

5.4 BLOb Support for solexp

Solexp now also supports binary fields of unlimited length.

5.5 Inserting Enclose Characters

SpeedLoader accepts the special characters CR and LF in enclosed formats and within fixed length fields. Enclose characters can be included in strings by typing two consequtive enclose characters (e.g., 'can''t'). Solexp now outputs two quote characters in strings that contain quote characters (').

5.6 SOLID SpeedLoader Commit Interval Configurable

The SOLID SpeedLoader commit batch size can be changed using the command line option -b or the control file option COMMITBATCH. If the commit batch size is specified, data is committed at commit batch intervals. If the commit batch value is zero, all data is loaded before commit. The default commit batch value is one, which is the same as the autocommit mode.

5.7 SOLID SpeedLoader TIME and TIMESTAMP Format Keywords

In the SOLID SpeedLoader control file it is possible to also specify default TIME and TIMESTAMP formats for columns in addition to the DATE format. The syntax is the following:

DATE 'format-str'
TIME 'format-str'
TIMESTAMP 'format-str'

Any or all of the default formats may be missing, but the existing ones must be given in the above order, e.g., DATE must always be specified before TIME. The keywords TIME and TIMESTAMP work in the same way as the keyword DATE.

5.8 'Quit' also from Solcon & Solinst

For consistency reasons, solcon now acceps the quit command, which is equivalent to the exit command. Solinst accepts the 'Q' command, which is equivalent to 'X'.

6 Changes to Supported Platforms

6.1 Silicon Graphics IRIX

IRIX was provided as a 2.1 beta release. Now, with SOLID Server version 2.2, IRIX v. 6.2 becomes a supported platform.

6.2 Windows NT 4.0 (Intel)

SOLID Server 2.1 for NT could be run without problems on Windows NT 4.0, although it was not an officially supported platform. Now, both NT 3.51 and NT 4.0 are officially supported on the Intel hardware.

On Digital Alpha hardware, NT 3.51 continues to be the supported NT version.

6.3 Intergraph CLIX Phased Out

SOLID Server 2.2 is not provided for the Intergraph CLIX platform anymore. The support for customers using SOLID Server 2.1 for CLIX is continued for 18 months after the version 2.2 release date.