Mimer SQL Engine Release Notes

Introduction

Mimer SQL Engine is an advanced database management system developed by Mimer Information Technology AB. This document contains the latest Mimer SQL Engine Release Notes.

To make it easier for you to see when a subject was introduced, the following conventions for section headers are used:

  • For each release note section in this document, a version number is written next to the section header.
  • For the sections describing changed functions and corrected problems, the version number is the version at which the described subject was changed or corrected.
  • For the sections describing known restrictions and known problems, the version number is the version from which the described subject applies.
  • For information on new, changed, corrected and deprecated functions and features, in older versions of Mimer SQL Engine, please refer the Mimer SQL Features and Functionality History, http://developer.mimer.com/documentation/History.pdf.


1.1 About Operating Systems

From time to time, there are descriptions that vary between different types of operating system. Such descriptions are labeled and enclosed by frames.


1.2 Documentation Objectives

The Mimer SQL Engine Release Notes are intended for application developers as well as Mimer SQL Engine database administrators.


1.3 Prerequisites

It is assumed that you are familiar with Mimer SQL.


1.4 Related Mimer SQL Engine Publications

· Mimer SQL Engine Reference Manual

Contains a complete description of the syntax and usage of all statements in Mimer SQL Engine.

· Mimer SQL Engine User's Manual

Contains a description of the interactive SQL facilities. A user-oriented guide to the SQL statements is also included, which may provide help for less experienced users in formulating statements correctly (particularly the SELECT statement, which can become quite complex).

· Mimer SQL Engine Programmer's Manual

Contains a description of how Mimer SQL Engine can be used within the context of application programs, written in conventional programming languages.

· Mimer SQL Engine System Management Handbook

Describes system administration functions, including export/import, backup/restore, and the statistics functionality. The information in this manual is used primarily by the system administrator and is not required by application developers.

· Mimer SQL Engine Guides

Comprise documentation containing platform-specific administration and usage information. A set of one or more guides is provided, where required, for each platform on which Mimer SQL Engine is supplied.


1.5 Terms and Conventions

The following terms and conventions are used in this document:

Term
Description

Level2

Old, deprecated programming interface for Mimer SQL.

Table

Tables (or relations) hold all the information in a relational database.

A table may not be split over several databanks, but a databank can hold several tables.

V9.2.3

Means version 9.2.3 of Mimer SQL Engine.

This is the general form of describing a Mimer SQL Engine version, where the first digit is the version, the second is the release and the third is the sub-release.

V9.x.x

Means an unspecified version 9 release of Mimer SQL Engine.

For other terms, see the Mimer SQL Engine Reference Manual.


1.6 Acronyms and Trademarks

ANSI

American National Standards Institute.

BSQL

Mimer SQL Engine's line-oriented query tool.

ESQL

Embedded SQL.

ISO

International Standards Organization.

ODBC

Open DataBase Connectivity.

PSM

Persistent Stored Modules, i.e. stored procedures.

SQL

Structured Query Language.

UNIX

UNIX is a trademark registered by Open Group.

All trademarks are the property of their respective holders.


Mimer SQL Database Server

The following sections document new and changed functions, corrected problems and known restrictions and problems regarding the Mimer SQL Engine database server.


2.1 New Functions

The following sections document new functions.

2.1.1 Performance (V9.2.1)

A large number of performance improvements have been made in this version.

The improvements include the introduction of a compilation information cache which significantly reduces compilation time.

2.1.2 LOAD/UNLOAD (V9.1.1)

New commands, LOAD and UNLOAD, have been implemented to facilitate transferring data to and from databases. LOAD and UNLOAD can be used with Mimer SQL Engine from any ODBC-based SQL command interpreter. For more information, see the Mimer SQL Engine System Management Handbook.


2.2 Changed Functions

The following sections document changed functions.

2.2.1 Improved UNION ALL Optimization For Views (V9.3.2)

The UNION ALL optimization for views has been improved. As a result of this, the result-set may now be returned in a different order than before (when ORDER BY is not specified.)

2.2.2 Smaller Page Sizes Used at End of LOB Storage (V9.2.5)

The strategy for storing large objects (LOBs) in pages is changed, so the use of disk space is made more economic.

Example:

Previously if a LOB required between 2K and up to 16K, one 16K page was always allocated. Now if the LOB is between 2K and 12K, instead so many 2K pages as required are allocated.

Also the writing of LOB pages is now more efficient as only the occupied part of a page is written to disk.

2.2.3 Compressed Commit Set (V9.2.5)

The commit set is used to secure transaction data at commit. This data is now stored using compression which can decrease the space needed in TRANSDB. It may also allow for more cases where group commit may be used (i.e. several transactions can be secured on disk in a single I/O operation).

2.2.4 More Efficient Bitmap Handling (V9.2.5)

The system no longer has to write bitmap pages to disk when a databank file size has changed. This allows the system to operate even more efficiently when dynamic allocation of file space is needed. In addition the internal algorithm for finding free pages within the bitmaps has been improved.

2.2.5 New Compressed Form for Data Storage (V9.2.5)

A new compress algorithm is now used. This new algorithm is more efficient especially for records containing Unicode data (NCHAR data types in Mimer).

Tables using the new algorithm can be seen by using the DBC program, which displays those tables with the information "Mimer LZU compression".


2.3 Corrected Problems

The following sections document corrected problems.

2.3.1 Restore of Databank With LOB's (V9.3.8C)

A problem has been corrected that could create unnecessary large databanks when restoring a databank with delete and/or update operations on tables with LOB's.

2.3.2 Synonym After Alter Table (V9.3.8C)

If a synonym was used in a SQL statement and the table, which the synonym is a synonym for, was altered the synonym could not be used in subsequent SQL statements. The problem is now corrected.

2.3.3 Internal Databank Identifier Invalid (V9.3.8B)

A problem has been corrected that under rare circumstances would cause COMMIT to fail with an -16185 error code (internal databank identifier invalid). The error message occurs because the problem allows an accessed databank to close before the transaction is completed. A backup operation is expected to have been concurrent with the failing transaction when an error occurred.

2.3.4 Background Dbcheck Hang (V9.3.8)

In extremely rare circumstances the database server would hang when performing a background databank check of the data pages in a databank. For the situation to occur other request must be updating data and the background check must be checking a table with collations. This is now corrected.

2.3.5 LOGDB Restart (V9.3.8)

The restart of large objects in LOGDB could at times reference large objects from the user databank. This caused an incorrect LOGDB corrupt message to be returned. This has now been corrected.

2.3.6 Improved Control of LOB References Outside Databank End-of-file (V9.3.8)

The control of large object references when doing implicit verification or when applying a restart set to a databank has been changed.

Previously this was signalled as an I/O error (end of file) in the log and almost ignored. Now the databank is considered as corrupt and is disabled for further use.

Both mentioned operations occur the first time a databank is opened after a database server start.

(Implicit databank verification is only run if the database server is not correctly stopped.)

2.3.7 LOB's In Databank Created By an Old Mimer Version (V9.3.7G)

Storage of large objects (LOB's) has been slightly changed in version 9.3. This led to an introduction of an error when deleting records or updating a column with LOB data in tables held in a databank upgraded from an earlier Mimer version. The error is now corrected.

2.3.8 Problem With Large SQL Statements (V9.3.7F)

In some cases, compiling large SQL statements via ODBC caused a server crasch. The problem occured if the connection attribute SQL_NOSCAN was set to off.

One way to avoid this problem in versions prior to 9.3.7F is to set the attribute to on by the call:

rc = SQLSetConnectOption(Connection(ConnectionIdx).hdbc, SQL_NOSCAN,
SQL_NOSCAN_ON);

Anyhow, the problem is now corrected.

2.3.9 Table Control Area Exhausted (V9.3.7F)

In cases where there was a large bufferpool and the application performed a large number of changes in a databank with WORK option it was possible that the system used an excessive number of table control blocks. In this version the system will not use more than half the table control blocks in these cases. In addition, it will only report table control area exhausted a few times rather than filling the mimer.log with these messages.

2.3.10 SQLPool Exhausted During Restore (V9.3.7E)

When performing restore of a databank the system, at times, reported that the space in the SQLPOOL was exhausted, while in fact it was not. This has now been corrected.

2.3.11 Recompilation of Views Closing Cursors (V9.3.7)

If internal recompilation of views were needed (e.g. when performing UPDATE STATISTICS) and the amount of open tables were above a certain level, recompilation of views erroneously closed all open cursors when the internal view recompilation committed its changes. This has been corrected in the database server.

2.3.12 Online Backup (V9.3.7)

When performing online backups the system would in very rare circumstances stop with an internal inconsistency logged in the mimer.log file. When this happened the backup was complete and the system could be restarted with no special actions. The cause of the shutdown has now been corrected.

2.3.13 Large Object Restart (V9.3.7)

During long restart sequences involving large object columns the system at times incorrectly reported the databank to be corrupt. This has now been corrected.

2.3.14 Write Set Corrupt when Automatic Refresh (V9.3.6)

Creating an index or some other statement that caused an automatic refresh of executable statements could give the error -16206, "Write set corrupt".

This is now corrected.

2.3.15 Server Hangs when Less than Seven 16K Pages (V9.3.6)

When running a server with less than seven 16K pages the server may hang. Previously, the absolute minimum number of 16K pages was specified to be 4. For physical reasons, the absolute limit is now 4 only if there are 64K buffer pool pages in the system. If there are no 64K buffer pool pages, the lower limit is 5 pages.

Also, a problem has been corrected that will now indeed allow five and six 16K buffer pool pages.

2.3.16 Read-only Transactions (V9.3.3)

A problem introduced in v9.3.1 has been corrected that could cause the SET TRANSACTION READ ONLY statement to be ignored. The problem would typically occur if the transaction was the very first transaction for the user.

2.3.17 -19078 Warning on the Error Log (V9.3.3)

A problem has been corrected that could cause "Invalid MOS parameter" warning messages on MIMER.LOG. The warnings could occur during very high load with very complex SQL statements.

2.3.18 ALTER DATABANK'S FILESIZE Option (V9.3.2)

Support for the SET FILESIZE and DROP FILESIZE in the ALTER DATABANK statement is now implemented.

2.3.19 ALTER DATABANK To READ ONLY (V9.3.2)

In version v9.3.1 it was possible to change the option for a databank to READ ONLY even though the databank contained tables that were defined as using a current collation. This is not allowed in version v932.

2.3.20 CAST Boolean AS Varchar (V9.3.2)

Using the construction CAST(<boolean-expression> AS varchar(5)) could cause memory corruption. This is now corrected.

2.3.21 Update Statistics (V9.3.1)

A problem with the dictionary cache not being updated after an UPDATE or DELETE STATISTICS statement has now been corrected.

2.3.22 Execution Interrupted by Scheduler (V9.2.5)

A problem has been corrected which sometimes could cause incomplete statements to terminate with an "Execution interrupted by scheduler" error.

The problem would only occur for expensive statements that are both prepared and executed with the same client request (such as SQLExecDirect for ODBC clients) on a fully occupied database server.

2.3.23 Online Backup Improvements (V9.2.4)

When an online backup was made the transaction cache was not released properly. This could cause degradation of performance for long online backup sequences. This has now been corrected.

Occasionally the online backup would produce errors such as "Table identifier invalid" or "Internal inconsistency detected". This was caused by a concurrent copying of TRANSDB content while applying changes to other backup databanks. This has now been corrected.

A problem has also been corrected for databanks containing BLOB or CLOB data. The problem occasionally caused an inconsistent backup databank where some of the LOB data references were corrupt. The inconsistency can be detected by using the DBCHECK utility on the backup databank.

2.3.24 Recreating a Smaller LOGDB (V9.2.4)

A problem has been corrected regarding recreation of LOGDB. The problem could occur under rare circumstances if the recreated LOGDB file was smaller than the preceding file. The problem would cause I/O errors to be logged when dropping the log next time or when restarting after a crash.

2.3.25 Improved Index Handling (V9.2.4)

The SQL compiler's index handling has been improved, as corrections have been done to the query optimizer.

2.3.26 ORDER BY With Many Indexes (V9.2.2G)

SELECT with ORDER BY had problems when too many indexes were involved. This is now corrected.

2.3.27 More Than 32 Foreign Key References From a Table (V9.2.2)

If a table was defined with more than 32 foreign key references the error message "Internal inconsistency detected" was signalled at insert or update.

2.3.28 Interrupted UPDATE STATISTICS (V9.2.2)

If a databank was offline, or if a table was not accessible for any reason, updating statistics was interrupted and no statistic was gathered for the following tables. This is now changed, so those tables are ignored. A warning message would have been nice, but is not introduced.

2.3.29 Implicit Databank Verification Causing Loop (V9.2.2)

If a databank has more than one rootpage (i.e. holding many tables) and there are tables with primary key columns using collation definition, the implicit databank verification could cause an infinite loop. Implicit databank verification will only be run if the database server is not correctly stopped.

2.3.30 Using Columns With Collations in Primary Keys or Indexes (V9.2.1C)

There is a problem in version 9.2.1A and 9.2.1B which can cause a database server to hang when accessing a table where a primary key or index is sorted using a non-default collation.

This problem is now corrected.

2.3.31 XA Transactions - Migrating Between Processes (V9.2.1)

When distributed transactions were used with the XA protocol, it was possible for a transaction to be handled by one process and then subsequently handled by another process. When this was done, the ownership for the reference counts for underlying objects used by the transaction was not properly migrated to the other process. The effect could be that exclusive operations incorrectly were allowed or, in rare circumstances, the objects actually were closed before the transaction completes. This is now corrected.

2.3.32 XA Transactions - Object Reference after Restart (V9.2.1)

When a machine crashes, any distributed transactions that were in the prepared state (and not yet committed), are activated upon restart of the server.

However, reference counts for the objects used by the transaction were not set properly. The effect could be that exclusive operations may be incorrectly allowed or, in rare circumstances, the objects actually were closed before the transaction completed. This is now corrected.

2.3.33 Database Hang During Databank Verification (V9.2.1)

The database server would previously hang during the internal verification, when the last page of the databank file was a bitmap page. This occurs rarely as only every 16256th block is a bitmap and the extension of the databank file occurs in chunks.


2.4 Known Restrictions

The following sections document known restrictions.

2.4.1 SET FILESIZE For System Databanks (V9.3.2)

The SET FILESIZE option for the ALTER DATABANK statement does not work for system databanks, i.e. SYSDB, TRANSDB, LOGDB, and SQLDB.

2.4.2 Version Dependent Programs (V9.2.3)

The mimservers and mimcontrol programs delivered with Mimer SQL Engine can only be used with database servers of the same major versions, e.g. 9.2.

If, for example, you run a version 9 mimcontrol against a version 8.1 database server, the following error message may be returned:

# mimcontrol -t old_database

2004-11-05 19:20:32.00 <Error>

Could not create proper execution environment

stat: [ENOENT] No such file or directory

#

2.4.3 Level2 Restrictions (V8.1.1)

Win: Level2 is supported on a server basis on these platforms. That is, it is possible to use a Level2 client on another platform and access the database server on a Windows platform.

Mac: Level2 is not supported on Mac OS X, neither as a client nor as a server.

Linux: Level2 is not supported on Linux, neither as a client nor as a server.

UNIX + VMS: Level2 is supported with the following restrictions:

Stored procedures are not accessible from Level2.

The old Level2 application interface only supports object names of up to 18 characters.

Tables that contain columns defined with the data types: CHARACTER VARYING, CHARACTER LARGE OBJECT, BINARY VARYING, BINARY, BINARY LARGE OBJECT, DATE/TIME or INTERVAL are not accessible from Level2 applications.

The same is true for tables with check clauses, default values that are sequences and triggers. If an attempt is made to open such a table, the following error will be returned:

MIMER/DB error -2138, Table is not Level 2 accessible

Views which are defined using syntax or conditions (e.g. join views) that cannot be understood by the Level2 interface will not be accessible from Level2 applications. When trying to open such a view, the following error will be returned:

MIMER/DB error -2138, Table is not Level 2 accessible

Note: once a table or view has been classified as not accessible from Level2, it can never be re-classified as accessible from Level2 again.
Even if all triggers, check constraints and default values are removed, the table will not be accessible from the level2 interface.


2.5 Known Problems

None.


SQL - Structured Query Language

The following sections document new and changed functions, corrected problems and known restrictions and problems regarding Mimer SQL Engine.


3.1 New Functions

The following sections document new functions.

3.1.1 Support For FOR Loop in PSM (V9.3.7)

The FOR statement in PSM provides a simple way of iterating through all records in a result set and performing some operations for each record.

example
for SELECT forname, surname FROM customers DO
    CALL editNames(forename,surname);
END for

Within the for loop the columns of the result set are available as variables. It is also possible to call a result procedure instead of a select statement.

3.1.2 BEGINS Function (V9.3.7)

LIKE predicates, addressing the "begins with" functionality, are very common. However, when a parameter marker is used for the LIKE pattern, the SQL compiler cannot determine the LIKE pattern characteristics, and possible optimizations will not be applied. To overcome this issue, the built-in function BEGINS has been implemented.

The BEGINS function has the following equivalence:

BEGINS(column,'AB') <==> column LIKE 'AB%'

BEGINS(column,?) will be optimized for "begins with" performance.

Examples

Return all customers whose surname begins with 'Pe':

SELECT * FROM customers WHERE BEGINS(surname, 'Pe');

3.1.3 Automatic Refresh of Executable Statements (V9.3.6)

Executable statements will be recompiled when a dependent object is changed. I.e. if a column is added to a table, all statements using this table, directly or indirectly, will be refreshed implicitly. Other operations that will cause an automatic refresh of statements are UPDATE STATISTICS, CREATE INDEX, and DROP INDEX.

3.1.4 Removable Databanks (V9.3.4)

It is possible to specify that a databank should be removable, using either the CREATE DATABANK statement or the ALTER DATABANK statement.

This functionality is useful, for example, if the databank is located on a flash memory card.

3.1.5 ALTER DATABASE SET AUTOUPGRADE (V9.3.4)

The AUTOUPGRADE functionality makes the database system keep track of changes to tables. Then it's possible to distribute an upgraded version of the system databank, to be integrated into existing customer installations, where the same table changes will be performed.

The database's autoupgrade mode is currently found in the system table SYSTEM.SERVER_INFO, where AUTOUPGRADE_ENABLED indicates if the autoupgrade attribute is set or not.

See Mimer SQL Reference Manual, ALTER DATABASE for more information.

3.1.6 Specifying Size Limits for Databank Files (V9.3.3)

When creating a databank it is possible to specify that the size of the physical file should be within certain limits.

E.g.

CREATE DATABANK mimer_store SET MINSIZE 10 M, GOALSIZE 20 M, MAXSIZE 30 M;

This means that the file size will be between 10 and 30 mega bytes and that the system will try to keep the size as close to 20 mega bytes as possible. The size can be specified using the suffixes K, M or G which stands for kilo bytes, mega bytes and giga bytes respectively.

These limits can also be specified in an alter databank statement.

ALTER DATABANK mimer_store SET MAXSIZE 2 G;

Another new feature is the possibility to set the actual size for a file.

ALTER DATABANK mimer_store SET FILESIZE 20 K;

If the file size is less than the currently allocated size, the system will release unused file blocks in order to shrink the file and if the file size is larger than the current size, new blocks will be allocated.

There is also an option for dropping size limitations for a databank.

ALTER DATABANK mimer_store DROP MAXSIZE;

Dropping the FILESIZE for databank means that the file will be made as small as possible (but not smaller than minsize, if specified).

3.1.7 Read Only Databanks (V9.3.3)

It is possible to specify that a databank should be read-only with the alter databank statement

ALTER DATABANK mimer_store SET OPTION READ ONLY;

This means that no write operations on tables located in this databank are allowed. It is also not possible to create tables or indexes in the databank.

3.1.8 Parameter Overloading (V9.3.0)

By parameter overloading it is possible to create multiple routines with the same name. The routines must either have a different number of parameters or parameters with different types.

Create function f(c1 smallint) returns int
return ...

create function f(c1 date) returns char(10)
return ...

The subject routine will be determined at create time.

It is not possible to overload functions with regard to the returned data type.

3.1.9 Specific Names (V9.3.0)

A specific name can be assigned to all routines (functions and procedures) when created. If no specific name is given a unique name is generated by the system. A specific name must be unique within a schema. The specific name can be used to identify a (overloaded) routine in GRANT, REVOKE and DROP statements instead of having to supply a list of data types for parameters.

CREATE PROCEDURE p(out p1 int, in p2 varchar(10)) SPECIFIC p_int_varchar
BEGIN
    ...
END

CREATE PROCEDURE p(out p1 varchar(10), in p2 int) SPECIFIC p_varchar_int
BEGIN
    ...
END

To drop an overloaded procedure you can either specify a data type list, e.g.

DROP PROCEDURE p(int,varchar)

or use the specific name e.g.

DROP SPECIFIC PROCEDURE p_varchar_int

3.1.10 Boolean Data Type (V9.3.0)

Support for a boolean data type has been added. It can be used throughout Mimer SQL, e.g. as data type for columns or as return type for a user defined function.

Boolean expressions in the SELECT list are not implemented yet. I.e. expressions like

SELECT boolcol1 OR boolcol2 FROM sometable;

are not supported.

3.1.11 Compound Statement (V9.3.0)

A compound statement may now be used outside routines. I.e. it is possible to use a statement as

BEGIN
    DECLARE x INT;
    SET x = 1;
    WHILE x < 1000 DO
        INSERT INTO numberTable VALUES(x);
        SET x = x + 1;
    END WHILE;
END

in BSQL or any other query tool.

3.1.12 SUBSTRING on CLOB and NCLOB Columns (V9.2.5)

The SUBSTRING function can now be used on columns with data type CHARACTER LARGE OBJECT or NATIONAL CHARACTER LARGE OBJECT.

Example:
SELECT column1,
       SUBSTRING(clobcolumn FROM 1 FOR 50) AS intro
FROM sometable;

3.1.13 Using LIKE Predicate for CLOB and NCLOB Columns (V9.2.4)

The LIKE predicate can now be used to search the contents of columns with data type CHARACTER LARGE OBJECT or NATIONAL CHARACTER LARGE OBJECT.

Example:
SELECT * FROM tab1 WHERE clobcol LIKE '%Mimer%Uppsala%';

3.1.14 ORDER BY on Expressions (V9.2.4)

The ORDER BY clause can now contain expressions. An expression in the ORDER BY specification must contain at least one column reference to a table in the query.

Examples:
SELECT column1 FROM sometable ORDER BY column2;
SELECT column1 FROM sometable ORDER BY ABS(column1);
SELECT column1 FROM sometable
    ORDER BY CASE WHEN column1 < column2 THEN column1 ELSE column2 END;
table1 JOIN table2 USING (column1) ORDER BY somefunction(column1);

See Mimer SQL Engine Reference Manual, Chapter 12 for more information.

3.1.15 INSERT Large Object Column With a Subselect (V9.2.3)

A column with a Large Object data type can now participate in INSERT statements with subselect, i.e. the values to be inserted can be fetched from another table in the database.

Example:
INSERT INTO tab2 SELECT c1, lobcol FROM tab1 WHERE c1 < 10;

However, a Large Object can still not be used in an UPDATE with subselect statement

3.1.16 Extended Support for Collations (V9.2.1)

Collations for all European languages are supported. (See list in Mimer SQL Engine Reference Manual, Chapter 4.)

It is also possible to create new collations based on existing collations for specific purposes. See the updated CREATE COLLATION statement in the reference manual.

3.1.17 New Scalar Functions (V9.2.1)

Two new functions for converting between a unicode letter and a numerical value has been introduced. The function UNICODE_CODE will given a Unicode letter return the corresponding numerical value and the UNICODE_CHAR function returns a Unicode letter.

Examples:
SET uc = UNICODE_CHAR(4711);
SET i = UNICODE_CODE(n'Ö');

3.1.18 New Views in INFORMATION_SCHEMA (V9.2.1)

The new views are INFORMATION_SCHEMA.SQL_FEATURES, INFORMATION_SCHEMA.SQL_SIZING, and INFORMATION_SCHEMA.EXT_COLLATION_DEFINITIONS.

See Mimer SQL Engine Reference Manual, Chapter 13 for more information on these views.

3.1.19 Support for Scalar Subqueries (V9.2.1)

A scalar subquery can be used in a DML statement wherever an expression can be used.

Examples:
CREATE FUNCTION f() RETURNS int READS SQL DATA
    RETURN (SELECT count(*) FROM mimer_store.music)

SELECT title, (SELECT title FROM tracks
    WHERE albumID = a.albumID
    AND seqno = 1) AS track
FROM album AS a

3.1.20 WITH HOLD (V9.2.1)

Support for WITH HOLD cursors have been added. Cursors opened WITH HOLD are not implicitly closed by COMMIT. For more information, see the Mimer SQL Engine Reference Manual.

3.1.21 NCLOB Data Type (V9.2.1)

The NCLOB (NATIONAL CHARACTER LARGE OBJECT) data type is now supported.

3.1.22 ALTER STATEMENT (V9.2.1)

The ALTER STATEMENT command can be used to get a new execution plan for a precompiled statement. This is useful when, for instance, an index has been added or the statistics data has been updated.

Example:
ALTER STATEMENT book_statement_stmt REFRESH;


3.2 Changed Functions

The following sections document changed functions.

3.2.1 Scrollable Cursors and WHERE CURRENT (V9.3.7)

An UPDATE WHERE CURRENT on a scrollable cursor will now also update column values in its own result-set. (I.e. after a row has been updated using UPDATE WHERE CURRENT and the same row is re-fetched, the new value is returned.)

A FETCH returning a column value that has been updated by an UPDATE WHERE CURRENT on its own scrollable cursor (without an intermediate close and open) will return a warning with the SQLSTATE value 01998.

Note that plain column values are updated, but not expressions.

A fetch of a row deleted by a DELETE WHERE CURRENT on its own scrollable cursor will still return the row, but also a warning with the SQLSTATE value 01999.

Clients from older versions will return updated result-set rows, but will not return the warnings.

3.2.2 New Columns in INFORMATION_SCHEMA Views (V9.3.3)

The view INFORMATION_SCHEMA.EXT_DATABANKS have some additional columns. These columns contain information about size limitations for a databank.

3.2.3 Changed Data Types in INFORMATION_SCHEMA View (V9.3.3)

The data type for the numerical columns in the view INFORMATION_SCHEMA.EXT_SEQUENCES has been changed from INTEGER to BIGINT.

3.2.4 CREATE and ALTER DATABANK Syntax (V9.3.1)

A new set clause has been added to the CREATE and ALTER DATABANK statements.Instead of having different clauses for different attributes, all attributes can be given as a comma separated list in a single set clause.

E.g.

CREATE DATABANK mimer_store SET OPTION LOG,
    FILE 'disk_4:<mimerdb>mimer_store.dbf',
    FILESIZE 20 M;

and

ALTER DATABANK mimer_store SET OPTION TRANSACTION, FILESIZE 30 M;

Note that some databank options have been renamed (this is when using the set syntax, the old syntax still supports the old option names.) TRANS is now TRANSACTION and NULL is now WORK.

3.2.5 New Reserved Words (V9.3.1)

The following keywords are now reserved:
LANGUAGE, METHOD, RESULT, STATIC

3.2.6 Specification of ISOLATION LEVEL READ UNCOMMITTED (V9.3.0)

If SET SESSION ISOLATION LEVEL READ UNCOMMITTED is specified, then a default transaction access mode of READ ONLY is now implicit. I.e. transactions performing updates are not allowed unless a SET TRANSACTION statement changing this default is specified before doing such a transaction.

If SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED is specified, then a transaction access mode of READ ONLY is now implicit for the single next transaction.

3.2.7 Changed Date and Time Arithmetic (V9.3.0)

In earlier versions of Mimer an expression such as

date '2008-01-31' + interval '1' month

would cause an exception since the result would be an invalid date, i.e. the 31:th of February. This has now been changed so the result will always be a valid date. If the day value is larger than the last date for the actual month the day value will be set to the latest day for the month. In the example above the result will be the 29:th of February.

3.2.8 Using Tables With LOB Columns in Triggers (V9.2.4)

In earlier versions of Mimer SQL it was not allowed to define a trigger on a table with a column defined with a lob type. This has now been changed. For example it is possible to write triggers to log all operations on a table, e.g.

CREATE TABLE master     (pnr VARCHAR(10),
                         image BLOB,
                         PRIMARY KEY(pnr));

CREATE TABLE masterCopy (byWhom VARCHAR(128),
                         action VARCHAR(10),
                         pnr VARCHAR(10),
                         image BLOB,
                         whatTime TIMESTAMP,
                         PRIMARY KEY(byWhom, action, whatTime));

CREATE TRIGGER copyMaster AFTER INSERT ON master
REFERENCING NEW TABLE AS n
BEGIN ATOMIC
    INSERT INTO masterCopy
        SELECT SESSION_USER, 'INSERT', pnr, image, LOCALTIMESTAMP FROM n;
END

The only restriction remaining is that it is not possible to refer to LOB columns in the NEW TABLE in an INSTEAD OF trigger.

3.2.9 Collations and NCHAR (V9.2.1)

Collations now support the national character data types.

3.2.10 New Columns in INFORMATION_SCHEMA Views (V9.2.1)

The view INFORMATION_SCHEMA.EXT_IDENTS has an additional column IDENT_SCHEMA.

The view INFORMATION_SCHEMA.EXT_STATEMENTS has two additional columns, IS_SCROLLABLE and IS_FORWARD_ONLY.

See Mimer SQL Engine Reference Manual, Chapter 13 for more information on these views.

3.2.11 Changed Privilege Check in Views (V9.2.1)

The views INFORMATION_SCHEMA.TABLE_CONSTRAINTS and INFORMATION_SCHEMA.KEY_COLUMN_USAGE now show information on tables where the current user has some privilege other than select. Earlier it only showed information if the current user was the creator of the table.

The view INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS now shows data if the current user has some privilege, other than select, on the referenced table.


3.3 Corrected Problems

The following sections document corrected problems.

3.3.1 Instead of Trigger Not Updatable When UNION (V9.3.8C)

It was not possible to insert data using an instead of trigger if the view was non-updatable because of a UNION. This is now corrected.

3.3.2 CASE With Different Collations (V9.3.8C)

When processing a CASE expression's different results, no verification of collations was performed. This is now corrected, so that the use of one single collation is verified.

A CASE expression containing results with different collations, e.g:

SELECT CASE WHEN id > 10 THEN col_swe
                ELSE col_eng END
FROM sometable

must now be re-written to use a specified collation, e.g:

SELECT CASE WHEN id > 10 THEN col_swe COLLATE english_1
                ELSE col_eng END
FROM sometable

3.3.3 Complex Old/New Table Query Out Of Memory (V9.3.8B)

If a trigger included a rather complex query using the old (or new) table, the internal error -11002 "Internal DB dynamic storage area (SQLPOOL) exhausted" was sometimes returned.

This problem is now corrected.

3.3.4 Outer Join With schema.table.* (V9.3.8B)

In an outer join it was not possible to use an asterisk to return all columns of a qualified table. This is now corrected.

3.3.5 Many Values in IN Clause (V9.3.8B)

A query including a condition on an indexed column using an IN clause with very many values specified (typically more than 100), was not able to be executed on some specific platforms.

This problem is now corrected.

3.3.6 Support for Clients Running version 7 (V9.3.8)

A problem has been corrected which caused the server (version 9.3.7) to crash if it was used with database clients running version 7.

3.3.7 Recursive Stored Procedures (V9.3.8)

A problem with recursive stored procedures has been corrected.

The problem could under rare circumstances cause the server to crash.

3.3.8 BEGINS With Long Literal (V9.3.8)

If the BEGINS function was applied on a NATIONAL CHARACTER type column, and the length of the literal was more than three times the column length, no rows were ever returned. E.g. SELECT * FROM sometable WHERE BEGINS(nchr8, N'abc').

This is now corrected.

3.3.9 SELECT DISTINCT on Part of Primary Key (V9.3.7G)

If SELECT DISTINCT was used to read nothing but the first two or more columns of a primary key, but not the whole key, and at least one of the selected columns was VARCHAR, NATIONAL VARCHAR or VARBINARY, the result could contain duplicates.

This is now corrected and no duplicated rows are returned.

3.3.10 Default NULL For Binary Columns (V9.3.7F)

If a binary column was declared with NULL as default value, it was not possible to use that default value when inserting or updating data. This is now corrected.

3.3.11 Drop Filesize For System Databanks (V9.3.7E)

In earlier versions, dropping the file size for a system databank, e.g. transdb, caused the error message 'Databank transdb locked by another MIMER/DB user'.

This has now been corrected so that it works as expected.

3.3.12 Problem With For Loop in PSM

If an item from the select list in a for loop was used in an record literal this caused an spurious error message. E.g.

FOR SELECT book_title,isbn
    FROM books
    WHERE book_title LIKE p_title DO
        RETURN (book_title,isbn);
    END FOR

resulted in the error 'Out of memory'.

This is now corrected.

3.3.13 OUTER JOIN With DISTINCT In Views (V9.3.7E)

View definitions with an outer join in combination with distinct could in some cases return internal error -19225. The problem was introduced in v9.2.4, and has now been corrected.

3.3.14 Executable Statement Problem (V9.3.6C)

Using executable statements which invokes routines containing multiple instances of identical DML statement on a table with triggers causes a database failure. There are also some other cases involving recursive invocations in statements that could cause a database failure as well.

These problems are now corrected.

3.3.15 Scrollable Cursor for Result-set Procedures (V9.3.6C)

It was not possible to open a scrollable cursor for a result set procedure more than once in an application. This is now corrected.

3.3.16 Invalid Path When Creating Statements (V9.3.6C)

When creating a statement in a specific schema, the name of the current used was incorrectly used as a qualifier when doing look up for unqualified names. This is now corrected so that the schema name is used instead.

3.3.17 Problem Using Executable Statements (V9.3.6C)

Executing a statement that contains multiple identical DML statements that in turn invokes PSM routines, causes a database failure. This is now corrected.

3.3.18 ORDER BY Indexed Column Not In SELECT List (V9.3.6C)

If a primary key column was also included in an index specification, it was not possible to order a result-set by that column without also specifying the column in the SELECT list. This is now corrected.

3.3.19 FETCH LAST Returns NO DATA (V9.3.6C)

Fetch LAST related functionality did not work properly for scrollable cursors when two tables were joined using a unique key in the first accessed table. This is now corrected.

3.3.20 COUNT DISTINCT in JOIN (V9.3.6C)

COUNT DISTINCT related functionality did not work properly for joins using a key in the first accessed table. This is now corrected.

3.3.21 Insert Using Boolean Variable (V9.3.5)

In earlier versions it was not possible to insert a boolean value using a host/PSM variable. This functionality is now implemented.

3.3.22 Self Referencing Foreign Keys (V9.3.3)

Given the following tables:

SQL>CREATE TABLE t1(c1 INT PRIMARY KEY,
                    c2 INT REFERENCES t1 ON DELETE CASCADE);
SQL>CREATE TABLE t2(c1 INT REFERENCES t1 ON DELETE CASCADE);

it was not possible to create an executable statement containing a delete on table t2. This problem has been corrected.

3.3.23 Date Arithmetics (V9.2.5)

When calculating an interval between two dates, days within a month was not considered. As an example

SELECT (DATE '2005-04-23' - DATE '2004-04-24') YEAR
FROM information_schema.ext_onerow;

will serve. In previous versions the result would be one year but this is now corrected in that the query will return the result zero years.

3.3.24 UPDATE WHERE CURRENT and Primary Key Duplicates (V9.2.4)

An UPDATE WHERE CURRENT that updates the primary key was previously allowing the updated record to replace an existing record with the new primary key value. The corrected behavior is to return a primary key duplicate error.

3.3.25 Compound SQL Statements and Large Objects (V9.2.4)

It is now allowed to use LOB columns in compound SQL.

Example:
DELETE FROM lobtab WHERE c1 = 2; INSERT INTO lobtab VALUES (99,?);

3.3.26 LOB Values Incorrectly Returned as NULL (V9.2.3B)

There has been a problem where some inserted BLOB, CLOB or NCLOB values are treated and returned as NULL values by the database server.

The problem can occur on systems where the database server has been started and stopped many times.

This problem is now corrected and values that previously were returned incorrectly as NULL will now be returned correctly.

3.3.27 LIKE Predicate in Functions or Procedures (V9.2.2H)

A problem has been corrected regarding use of the LIKE predicate in functions or procedures.

The problem could cause a "Compiled LIKE pattern corrupt" error message or possibly terminate the database server. The problem would only occur when several users access a function or procedure where the character pattern in the LIKE predicate is specified using a variable.

3.3.28 CAST From Timestamp to Time (V9.2.2G)

Using the following code:

DECLARE ftime TIMESTAMP;
DECLARE ntime TIME;

SET ftime = LOCALTIMESTAMP;
SET ntime = CAST(ftime AS TIME);

in a PSM routine caused an unjustified internal error. This has now been corrected.

3.3.29 Defining Foreign Keys With On Delete (V9.2.2G)

When defining a foreign key constraint with an ON DELETE action where the added length of the column names used in the constraint exceeded approximately 150 characters, the statement would fail or make it impossible to access the table at a later stage.

This has now been corrected

3.3.30 Numeric Collations (V9.2.2G)

The [numeric on] option did not always work correctly in a collation, that contained a contraction. Swedish Å, Ä and Ö, are examples of contractions. A + digits and O + digits did not always sort as expected. This is now corrected.

3.3.31 MAX/MIN on Variable Length Columns (V9.2.2E)

MAX and MIN did in some cases have problem with the length of variable length data types. This is now corrected.

3.3.32 MAX/MIN on NCHAR Column Included in Index (V9.2.2E)

Using the MAX or MIN functions on a column of NCHAR or NVARCHAR type, where the column is included in any type of index (primary key, unique constraint, etc.), raised the internal error "Invalid OPCODE". This problem is now corrected.

3.3.33 Varchar Function in SELECT (V9.2.2E)

If a function returning variable length data (VARCHAR, VARBINARY or NVARCHAR) was invoked from a SELECT, UPDATE or DELETE statement, the length value was not handled correctly. This problem is now corrected.

3.3.34 UPDATE With LOCALTIMESTAMP and NULL (V9.2.2E)

Depending on the column order, an UPDATE with both LOCALTIMESTAMP and NULL could in some cases raise an 'constraint violation' error. This has now been corrected.

3.3.35 Error When Updating Foreign Keys (V9.2.2E)

There has been a problem where an update of a table could stop the database server if the table references or is referenced by a table that is inaccessible.

This problem is now corrected.

3.3.36 Multiple Column Indexes (V9.2.2E)

There has been a problem in previous 9.2 versions where an update of a table with an multiple column index could result in an "Internal inconsistency" error.

This problem is now corrected.

3.3.37 UPDATE With Subquery (V9.2.2E)

A problem with UPDATE, containing a correlated scalar subquery in a SET clause, has been corrected.

3.3.38 Temporary Storage Areas in PSM (V9.2.2E)

PSM has some problems with handling of temporary storage areas used for intermediate results when evaluating complex expressions. This can cause the inappropriate error 'Length error or incorrect value found during data type conversion'.

This has now been corrected.

3.3.39 Case Statement in Continue Handler (V9.2.2E)

Using case statements within a exception handler could cause the server to crash. This has now been corrected.

3.3.40 Compound SQL With Triggers (V9.2.2E)

Using a compound statement, e.g.

DELETE FROM orders; INSERT INTO orders SELECT * FROM ind;

when there are triggers defined on the IND table could cause unpredictable behavior. This has now been corrected.

3.3.41 Missing Cascade Effects (V9.2.2E)

The cascade effects of DROP SCHEMA/DROP TABLE did not drop all multiple triggers with references to the dropped table. This has now been corrected.

3.3.42 Scrollable Cursor and WCHAR Host Variables (V9.2.2D)

A scrollable cursor for a SELECT statement with host variables for input values (in the WHERE clause), where those variables were used to provide variable length data, raised the error 'Negative overflow for VARCHAR length'. This has now been corrected.

3.3.43 LEFT JOIN and Descending Index (V9.2.2D)

LEFT JOIN in combination with a descending index read data from the index table instead of the base table. As a result of this, random data was returned. This has now been corrected.

3.3.44 Declare Cursor WHERE CURRENT Mismatch in PSM (V9.2.2C)

In some cases when declaring a cursor with a subselect and then using that cursor in an UPDATE WHERE CURRENT could erroneously cause the error the table used in update where current is not the same as in the cursor declaration.

This has now been corrected.

3.3.45 Invalid Argument Allowed for ASCII_CODE in PSM (V9.2.2C)

When using the function ASCII_CODE in PSM it accepted nchar data as argument (with an invalid result). Such an attempt will now cause a semantical error saying that only character data can be used as arguments to the ASCII_CODE function. To get the code point for nchar data, the UNICODE_CODE function should be used.

3.3.46 Table Lookup Problem in PSM (V9.2.2C)

Using a statement such as

INSERT INTO t1(c1,c2) SELECT * FROM v1

in PSM where v1 is not updatable and v1 contains a column named c1 or c2 causes the unexpected error -12202 (i.e. Table V1 does not exist.)

This will no longer happen.

3.3.47 Type Resolution Problem for COALESCE in PSM (V9.2.2C)

Using coalesce(data,'') combined with concatenation in PSM could cause unpredictable behavior.

This has now been corrected.

3.3.48 Date Arithmetic Problem in PSM (V9.2.2C)

The construction

(dateExpression1 - dateExpression2) intervalQualifier

could cause unpredictable behavior when used in PSM.

This has now been corrected.

3.3.49 On-line Backup (V9.2.1F)

A problem with collations and on-line backups have been corrected. The problem could sometimes prevent tables with collations to be accessed after an on-line backup.

3.3.50 Integrity Constraints and Triggers (V9.2.1C)

A table having both triggers and either FOREIGN KEY or UNIQUE constraints could previously allow updates that violated the integrity constraint.

This problem is now corrected.

3.3.51 Remaining Triggers (V9.2.1C)

Due to a error in the implementation for DROP TABLE, triggers referencing the dropped table could be left in data dictionary or the DROP TABLE statement would cause an unwarranted error. This happened if there were more than one trigger referencing the table being dropped.

This problem is now corrected.

3.3.52 Incorrect SQLSTATE in PSM (V9.2.1C)

After a SELECT INTO in PSM, the SQLSTATE was erroneously set to 02000 (instead of the correct 00000) when a row was found.

This is now corrected.

3.3.53 CASE Statement in Scope of Continue Handler (V9.2.1C)

In some circumstances the presence of a continue handler and a CASE statement in a compound statement in PSM could cause unpredictable behavior.

This is now corrected.

3.3.54 Dropping VARYING Length Column Using ALTER TABLE (V9.2.1B)

An ALTER TABLE dropping a column could previously produce an inconsistent or corrupt table. The problem would only occur if the dropped column have a VARYING length data type and is followed by a NATIONAL CHARACTER VARYING column.

This problem is now corrected.

3.3.55 Scalar String Functions and Parameter Markers (V9.2.1)

When parameter markers were used for one or several arguments to a scalar string function, there were problems mixing arguments of the character data types with arguments of the national character data types. This is now corrected.

3.3.56 SUM Combined With GROUP (V9.2.1)

The result of a complex GROUP BY statement could previously return an incorrect result. The error did only occur for statements including a join with a table having a multi-column index.

The result of a SUM combined with a GROUP yields different results depending on which columns that are included in the select list. This is now corrected.

3.3.57 UPPER/LOWER and the German Character ß (V9.2.1)

The German character ß was not translated by the UPPER and LOWER functions in version 9.1 of Mimer SQL Engine. In version 9.2, ß is treated as 'ss' and correctly translated.


3.4 Known Restrictions

The following sections document known restrictions.

3.4.1 Column Names Required When CREATE STATEMENT (V9.3.5)

To make executable statements safe for future table changes, column names must now be specified for INSERT and SELECT statements, when CREATE STATEMENT is executed.

Instead of

CREATE STATEMENT selst SELECT * FROM sometable

do now specify selected columns:

CREATE STATEMENT selst SELECT column1, column2 FROM sometable

Instead of

CREATE STATEMENT insst INSERT INTO sometable VALUES (?,?)

do now specify inserted columns:

CREATE STATEMENT insst INSERT INTO sometable (column1, column2) VALUES (?,?)

3.4.2 Remaining Objects After AUTOUPGRADE (V9.3.4)

Currently the database server will not remove dropped tables from a databank after an autoupgrade operation.

3.4.3 ADD COLUMN With DEFAULT When AUTOUPGRADE (V9.3.4)

If the database is set to AUTOUGRADE, it is not allowed to add a column with a default value. This restriction will be removed in a future version.

3.4.4 Using LOBs in Triggers (9.3.0)

The improved compression of LOBs has not yet been fully implemented, because it does not allow deleted LOBs (found in OLD table for AFTER DELETE/UPDATE triggers) to be copied.

3.4.5 DROP COLLATION (V9.1.1)

Support for drop collations is limited:

  • You cannot drop any collation that is used. Specifying CASCADE for DROP COLLATION works like RESTRICT. A table column, a domain, an index or another collation may use a collation. Any such dependency must be removed before the collation can be dropped.
  • You cannot drop an ident or a schema that has created one or more collations.

    • The following error message is generated:

    MIMER/DB error -12589 in function EXECUTE

An ident or a schema owning any collation cannot be dropped.

Drop collation first.

To drop the ident or schema, you must therefore first drop all collations created. As seen above, then before you can drop a collation, all dependencies to the collation must be removed.

3.4.6 Using LOB Data Types (V9.1.1)

The support for using Large OBject (LOB) data types (CHARACTER LARGE OBJECT and BINARY LARGE OBJECT) is not yet fully implemented.

The following limitations and restrictions are known:

  • When using the NULL option for a databank in Mimer SQL Engine, you cannot be sure that a record will be flushed to disk. As the storage of a LOB is separated from the actual record, it is possible that the record is flushed to disk but not the LOB value.
  • Shadowing is not currently allowed for databanks containing tables which have columns of large object data type.
  • LOB data types cannot be used in stored procedures (PSM).


3.5 Known Problems

The following sections document known problems.

3.5.1 INSERT VALUES With Scalar Subquery (V9.3.3)

If a scalar subqeury is included in the values clause of an INSERT statement, there are problems with aggregate functions and search conditions. The query does in those cases raise an error (since v933D).

As a work-around, it is sometimes possible to re-write the query as an INSERT with SELECT.

Example

Instead of

INSERT INTO sometable
  VALUES (1,(SELECT col1 FROM table2 WHERE col2 = 3),'abc');

re-write as

INSERT INTO sometable SELECT 1,col1,'abc' FROM table2 WHERE col2 = 3;

Note that this construction may insert zero, one, or many rows depending on the number of rows returned by the SELECT clause. The original INSERT with VALUES statement does always insert exactly one row.

3.5.2 GROUPED OUTER JOIN With CASE (V9.3.1)

An OUTER JOIN in combination with GROUP BY may not contain a CASE expression (or COALESCE or NULLIF) in the SELECT list, since the CASE will not work correctly.

Example of statement not executing correctly:

SELECT COALESCE(tbl1.id, 72), COUNT(*)
    FROM tbl1
    LEFT OUTER JOIN tbl2 ON tbl1.id = tbl2.id
    GROUP BY tbl1.id;

3.5.3 Restore Including DROP COLLATION (V9.2.1)

Currently there is a problem when restoring a databank, which at the backup time has any table using a collation in a table constraint (PRIMARY KEY, UNIQUE, REFERENTIAL) or an index. If such a collation later is dropped and you need to do a restore operation, it will fail due to that the collation cannot be found. To avoid this problem always make a new backup after such a table is altered or dropped and if the collation also is dropped.

3.5.4 No Warning when Truncated Result of a LOWER/UPPER on NCHAR (V9.1.2)

The UPPER and LOWER functions on NCHAR data types are performed using case folding. This means that the result may be of greater length than the input value.

When the result is truncated, no warning is raised, For example, when: UPPER('Straße') returns 'STRASS'.

3.5.5 Default Values for LOBs (V9.1.1)

Although not yet supported, it is possible to define a default value for a LOB column. Any subsequent INSERT statement will raise an error when the default value should be used for the LOB.

3.5.6 Trigger in WHERE CURRENT (V8.2.1)

Use of join-views with INSTEAD OF triggers in UPDATE/DELETE WHERE CURRENT statements does not work correctly.

3.5.7 Union All in Views (V8.2.1)

The usage of union all in a view will always cause the creation of a worktable when the view is queried. Although the performance of worktables has been enhanced, this may be a performance bottleneck if the result of the union parts consists of a large amount of data.

3.5.8 Privileges in Constraints (V8.2.1)

If, for instance, an ident creates a check constraint for a table which contains a function invocation and the ident grants any privilege on the table, then any attempt by the grantee to use that table will fail, unless he also have execute privilege on the function. To avoid this, an explicit grant on the function must be done. The same problem holds for sequences used in default clauses.

3.5.9 WHERE CURRENT and Updated Primary Keys (V8.2.1)

The UPDATE and DELETE WHERE CURRENT statements use the primary key found by the updated cursor to identify the row to be updated. The update or delete will not take any primary key updates into account.

3.5.10 Using DISTINCT with UNION ALL (V7.2.1)

The effect of the DISTINCT keyword can be lost when used together with UNION ALL. The effect is lost when DISTINCT follows the leading SELECT keyword in either of the subselects bound together by the UNION ALL statement.

3.5.11 Using Synonyms in CREATE VIEW (V7.2.1)

If a synonym name is used in a CREATE VIEW statement and the synonym is then dropped and recreated, re-compilation of the view will not use the original synonym.

Re-compilation of a view is performed when new statistics information has been encountered for a dependent base table when a base table has been altered or when a secondary index for a base table is changed.

3.5.12 Setting Shadows ONLINE and OFFLINE (V7.2.1)

SET SHADOW shadow-list OFFLINE will return an error rather than a warning if any shadow is found to be inaccessible. All shadows in the list will, despite the error, be set OFFLINE. The error message will only specify the first inaccessible shadow.

SET SHADOW shadow-list ONLINE will return an error rather than a warning if any shadow is found to be inaccessible. All shadows in the list will, despite the error, be set ONLINE. Log records are dropped for all the corresponding databanks if the RESET LOG option is specified. The error message will only specify the first inaccessible shadow.


3.6 Deprecated Features

The following sections document deprecated features.

3.6.1 PSM: JOIN Without SELECT (V9.3.7)

Retrieval statements without a select list is no longer supported in PSM. E.g. a statement such as

items JOIN procedures
    ON items.producer_id = producers.producer_id

will cause a syntax error when used in a PSM routine.

Now the SELECT keyword must be specified:

SELECT * FROM items JOIN procedures
    ON items.producer_id = producers.producer_id

3.6.2 Unqualified Column Name in ORDER BY When Duplicated (V9.2.4)

If a column name exists in several tables in a query, it must always be qualified in an ORDER BY specification. Prior to v9.2.4 the following statement did work because only SELECT list items were allowed in the ORDER BY clause:

SELECT t1.*, t2.c2 FROM t1, t1 AS t2 ORDER BY c1;

But since the introduction of ORDER BY expressions in v9.2.4, the c1 specification in the above ORDER BY clause is ambiguous, since any column in the tables can be specified, even if its not in the SELECT list.

To avoid this problem, qualify the columns in the ORDER BY clause, e.g.

SELECT t1.*, t2.c2 FROM t1, t1 AS t2 ORDER BY t1.c1;


Mimer ESQL - Embedded SQL

The following sections document new and changed functions, corrected problems, known restrictions and problems as well as deprecated features regarding Mimer ESQL.


4.1 New Functions

The following sections document new functions.

4.1.1 Support for L""-style String Literals in C (V9.3.8)

Embedded SQL/C now supports wchar_t (NCHAR) initialization using L""-style string literals.

Example:
exec sql BEGIN DECLARE SECTION;
wchar_t wstr[20] = L"Unicode data";
exec sql END DECLARE SECTION;

4.1.2 GET DIAGNOSTICS Option TRANSACTION_ACTIVE (V9.3.3)

The GET DIAGNOSTICS option TRANSACTION_ACTIVE is now supported.

Example:
exec sql GET DIAGNOSTICS :tr_stat = TRANSACTION_ACTIVE;

where tr_stat will be set to 1 if there is an active transaction, otherwise tr_stat will be set to 0.

4.1.3 Support for Embedded Fortran (V9.2.4B)

Linux: The ESQL program now supports the -for option for embedded Fortran. The currently supported Fortran compiler is Intel (R) Fortran Compiler Version 8.

4.1.4 Support for long long Data Type in C (V9.2.1)

Embedded SQL/C now supports the C data type long long. This data type is an integer which always is 64 bits, regardless of architecture and platform.

4.1.5 Support for NCLOB struct in C (V9.2.1)

Embedded SQL/C now supports the C data type nclob, which is recognized by the ESQL/C preprocessor and converted to a struct. See Programmer's Manual, Appendix A, Host Language Dependent Aspects.

4.1.6 Support for NATIONAL CHARACTER LARGE OBJECT (NCLOB) type (V9.2.1)

Mimer ESQL now supports the SQL data type NCLOB. The client may use host variables of any character string data type. However non-Latin1 data may only be accessed using the wide character host variable data types WCHAR_T and VARWCHAR_T.

If an NCLOB containing non-Latin1 characters is fetched into a host variable of type CHARACTER, CHARACTER VARYING or CLOB, an error exception is raised.

4.1.7 WITH HOLD Cursors (V9.2.1)

From version 9.2 onwards, cursors may be kept open during transaction commits. This behavior is accomplished using the WITH HOLD clause within the cursor specification.

Examples:
exec sql DECLARE CRS CURSOR WITH HOLD FOR ... ;

exec sql ALLOCATE 'CRS' CURSOR WITH HOLD FOR ... ;

4.1.8 Block Fetching of Result Sets (V9.1.4)

ESQL applications will now, whenever possible, fetch result rows in blocks from the server. In effect this means that ESQL will, whenever the application want to fetch more data, get a number of rows from the server at once and store these in an internal buffer. Future fetches will read directly from the internal buffer until it is exhausted, when a new block of rows are requested from the server.

In most cases, this has a positive effect on performance, applications will communicate less with the server and thus improving its scalability. Communication overheads are also reduced. There are, however, a few cases when this might be detrimental to performance. One situation might be when one want the first result row as fast as possible, while there can take some time for the server to complete an entire block request. In these situations ESQL programmers may change the block fetch behavior with the session attribute FETCH SIZE. This attribute will provide a hint about a suitable fetch size, that is the number of rows to fetch in each block, to ESQL. ESQL, will whenever possible try to use the specified fetch size, but it may in practice use a fetch size smaller than specified. If the value is zero, the hint is ignored.

Example 1 (ensures that rows are transferred one at a time from the server):
EXEC SQL SET SESSION FETCH SIZE 1;
Example 2 (using a host variable):
EXEC SQL BEGIN DECLARE SECTION;
long fetch_size = 24;
EXEC SQL END DECLARE SECTION;
...
EXEC SQL SET SESSION FETCH SIZE :fetch_size;


4.2 Changed Functions

The following section documents changed functions.

4.2.1 ESQL Command-Line Arguments (V9.3.4)

The short form switch -cob(ol) has been replaced with the short form switch -b. The long form switch --cobol has also been introduced.

The short form switch -for(tran) has been replaced with the short form switch -f. The long form switch --fortran has also been introduced.

The Flagger flags have been replaced. Now use the --verify switch to specify SQL-92 level. E.g:

esql --cobol --verify=full dsql.ec

The short form switch -s has been replaced with the short form switch -n. The long form switch --nologo has also been introduced. The VMS switch /SILENT has been replaced with /NOLOGO.

4.2.2 New SQL Descriptor Code for BIGINT (V.9.3.3)

The SQL descriptor type code for BIGINT is now 25, as defined by the SQL-2003 standard. (The old Mimer SQL specific value -5 is still supported, to ensure backward compatibility.)


4.3 Corrected Problems

The following section documents corrected problems.

4.3.1 WHERE CURRENT Internal Error (V9.3.7F)

Whenever an application attempted to perform an UPDATE WHERE CURRENT or DELETE WHERE CURRENT on the last row of a result set, and the size of the result set equalled a multiple of the used fetch size plus one, a -19068 ("no matching record") was returned from the server. The fetch size is the number of rows fetched from the server in each server call. Unless the application has specified a fetch size using the SET SESSION FETCH SIZE construct, the fetch size depends on the actual size of each row in the result set.

For example, if the fetch size happens to be 10 rows, this error would occur when performing an UPDATE WHERE CURRENT or DELETE WHERE CURRENT on the last row of result sets whose total sizes equals 11, 21, 31, 41 rows etc.

The problem appeared in v9.1.4.

4.3.2 Commits in Procedures Not Closing Cursors (V9.3.7)

Embedded SQL clients did not close cursors correctly when a called procedure committed or rollbacked a transaction.

The client did still believe, after the procedure call, that cursors were still open, and the client kept fetching from locally cached data, until a request for more data from the server found that the cursor had been closed.

From version 9.3.7, this is corrected, and the client will know immediately that a cursor has been closed.

4.3.3 Fetching Using a Reopened Scrollable Cursor (V9.3.7)

If a scrollable cursor was reopened and the first fetch returned an error or a row not found exception, the next fetch may produce the "kernel error -16183 in function DKFET1 Bad parameter, value = 100" on Mimer.Log, and the "internal error -16269 in DYNAMIC FETCH Unexpected duplicate row found in temporary table" is returned to the application.

This is now corrected.

4.3.4 External Character Set Support (V9.3.5)

The handling of the single byte character data types has been completely revised in this version. The system now follows the current locale setting on the machine to determine what characters are stored/retrieved when an embedded SQL application passes single-byte character strings to the Mimer client.

When character data is stored in Mimer SQL it can be stored in CHAR, VARCHAR or CLOB columns, or in NCHAR, NVARCHAR or NCLOB columns. Data in CHAR, VARCHAR and CLOB columns use the Latin-1 character representation (also called ISO 8859-1). This character set can only be used to store 256 different characters. For the exact characters that can be stored see Mimer SQL Reference Manual, Appendix B. To store any other characters the data type NCHAR, NVARCHAR or NCLOB must be used. These column types can store ANY character.

If a locale is used by the application that has characters that are not included in Latin-1, it means that the columns in the database data must use an NCHAR/NVARCHAR/NCLOB column to store the correct characters. Previously, each character in the application was simply stored in a character column. When these characters were retrieved with, for example, DbVisualizer or other Unicode enabled applications, the interpretation of the characters were done differently and the wrong characters were displayed. With the new locale support the Mimer SQL client now understands the representation of the characters in the application and maps them accordingly to its internal representation.

When retrieving data from the database, the translation work the other way. I.e. when retrieving data from a CHAR or NCHAR column to a single-byte character variable, the current locale must be able to represent all the characters returned from the database. When this is not possible, a conversion error -10401 is returned. If characters stored in the database have no representation in the chosen locale, a wide character data type must be used by the application instead (e.g. the C type wchar_t rather than char).

This means that applications using older versions of Mimer may have to be updated to work with the new version. Typically the data type used in the database is altered from CHAR to NCHAR, or from VARCHAR to NVARCHAR. This is done with the ALTER TABLE statement (see Mimer SQL Reference Manual). Other possible changes is to switch from a character representation (e.g. char) to a Unicode representation (e.g. wchar_t) for the application variables, or to switch to a locale that can handle all relevant characters.

On Windows the setting used for the external character set is set in the Regional and Language Options in the Control Panel under the tab Advanced. This setting is used automatically by the Mimer client.

On VMS the system continues to use the Latin-1 character representation regardless of locale settings.

On other platforms (Unix, Mac OS X, others) the application must call the runtime library routine setlocale to pick the locale to use. For example, the call setlocale(LC_CTYPE, "") sets the default locale as decided by the environment setting. The actual conversions made by the Mimer client are through the library routines mbstowcs (multibyte character set to wide char set) and wcstombs. Please note that if an application does not call setlocale a default 7-bit locale is used. This means that no 8-bit characters can be used without getting a conversion error. For applications where the source is not available it is possible to set an environment variable MIMER_LOCALE that will be used when calling the Mimer client. The value of the environment variable is used as the second argument to setlocale. To use the default locale set the environment variable to default. On windows the environment variable is set to the desired code page, i.e. only numeric values may be specified (for example: 1250: ANSI Central Europe, 1251: ANSI Cyrillic, 1252: Latin1, 1253: ANSI Greek, 1254: ANSI Turkish, and so on.)

The fact that the character type is considered a multi-byte character set allows any external character representation to be used. In particular various character sets such as Traditional Chinese Big5 and Japanese Shift-JIS may be used. The character set may, of course, be a single byte character set as such as the Greek Latin-7 character set (code page 1253 on Windows). On Unix platforms the prevalent representation is UTF-8 that allows any Unicode character to be stored in a character variable.

This change in character set handling sorts out a number of problems that were present in previous versions of Mimer. Some applications may be dependent on the old incorrect handling. It is strongly recommended that these issues are dealt with in the applications to ensure correct data in the database and application, and also compatibility between platforms using different character sets.

4.3.5 Scrollable Cursor (V9.2.5)

If a scrollable cursor wasn't used directly after OPEN, so instead of a FETCH, another operation was performed, the exception 'Invalid descriptor encountered on server' sometimes was raised. This is now corrected.

4.3.6 Setting Binary Descriptors With Character Data (V9.2.2E)

When setting a binary type descriptor with a null-terminated character data type using SET DESCRIPTOR, the string length was erroneously calculated from the maximum field length rather than looking for the terminating null character. In most cases, the user would experience a -14312 'Input character string too long' error. This problem was related to the client component and is corrected in version 9.2.2E.

4.3.7 Too Many Host Variables (V9.2.2D)

Using a huge number of host variables in one single SQL statement did not work correctly in previous versions. This problem is now corrected.


4.4 Known Restrictions

The following section documents known restrictions.

4.4.1 Vendor Specific SQL Disabled (V9.2.1)

The vendor specific escape clauses, described in Chapter 9 of Mimer SQL Engine Reference Manual, are disabled from Embedded SQL programs. This will be corrected in a future release.

4.4.2 ESQL Versions (V8.1.1)

An embedded program that has been pre-processed with ESQL version 8 or later can only be used with Mimer SQL Engine version 8 or later.

The main reason for this is that the treatment of WHENEVER SQLERROR, SQLWARNING and NOTFOUND is incompatible with earlier versions.

However, pre-processed code from ESQL version 7 or version 5 may be used with Mimer SQL Engine version 8 and 9.


4.5 Known Problems

The following section documents known problems.

4.5.1 INSERT of CLOB and NCLOB data (V9.3.5)

Inserting CLOB and NCLOB data may fail with "client and server disagreed on LOB length". It is not currently possible to insert CLOB and NCLOB data because the client and the server does not agree on the length of the objects. The BLOB data type is still available, however.


4.6 Deprecated Features

The following section documents deprecated features.

4.6.1 Windows: SQLCA and SQLDA Removed (V9.3.8)

Win: The deprecated SQLCA and SQLDA structures have been removed from the Mimer SQL Windows distribution.

If an application tries to use SQLDA or SQLCA, the error -18009, "Embedded SQL and MIMER/DB versions are incompatible, please preprocess source code" is returned.

As a consequence of the removal, all code pre-processed using ESQL of version 7.1 (or older) must be pre-processed again using the current ESQL pre-processor.

4.6.2 JOIN Without SELECT (V9.3.7)

Retrieval statements without a select list is no longer supported. E.g. a statement such as

items JOIN procedures
    ON items.producer_id = producers.producer_id

will cause a syntax error when used in an ESQL program.

Now the SELECT keyword must be specified:

SELECT * FROM items JOIN procedures
    ON items.producer_id = producers.producer_id


Mimer ODBC - Open Database Connectivity

The ODBC interface is described in detail in the Microsoft ODBC 3.0 Programmer's Reference and SDK Guide.

In the following descriptions regarding functionality, it is assumed that a Mimer SQL 8.2 or later database server is being used.

Mimer ODBC is delivered on a variety of platforms such as Windows 2000/XP/Vista, Mac OS X, and UNIX/Linux. Please read the machine specific chapter of these Release Notes for complete information about the availability of Mimer ODBC on your platform.

The Mimer ODBC implementation can work with both version 2 and version 3 of the ODBC driver managers.


5.1 New Functions

The following sections document new functions.

5.1.1 Indicator variables support SQL_DEFAULT_PARAM (V9.2.3)

Applications may now use the SQL_DEFAULT_PARAM value in indicator variables. The indicator variable is a word defined in the last parameter to SQLBindParameter or using the descriptor attribute SQL_DESC_INDICATOR_PTR. However, the SQL-2003 standard does not include the notion of a default value for parameters, and neither do Mimer SQL servers. The Mimer SQL server will therefore treat all default parameter values as being SQL NULL values.

5.1.2 Mimer Specific Keywords to SQLDriverConnect (V9.2.2)

To allow an application to connect without specifying a data source in the connection string, the following driver-specific keywords have been added for the Mimer ODBC Driver:

  • PROTOCOL
  • NODE
  • SERVICE
  • INTERFACE

The PROTOCOL keyword is mandatory for this new option to be used. The "old" keyword DATABASE must also be specified. Other new keywords should be used depending on the specified protocol. When PROTOCOL is specified, no lookup is done in registry (Windows) or MIMER_SQLHOSTS (Unix and VMS).

Supported protocols are LOCAL (shared memory), TCP, NAMEDPIPES (only for Windows), RAPI (only for Windows), and DECNET (only for VMS).

The protocol TCP requires keyword NODE specifying the network node name. If keyword SERVICE is not specified, 1360 is used as default.

The protocol NAMEDPIPES requires keyword NODE. If keyword SERVICE is not specified, the database name is used as default.

The protocol DECNET requires keyword NODE.

Note: SQLDriverConnect has a parameter that enables prompting for missing information. Currently when PROTOCOL is specified, this possibility is not allowed.

Examples of connection strings that now can be used:

"DRIVER={Mimer};DATABASE=cartoons;UID=mickey;PWD=mouse;PROTOCOL=local"

"DRIVER={Mimer};DATABASE=musix;UID=discux;PWD=records;PROTOCOL=local"

"DRIVER={Mimer};DATABASE=strips;UID=winnie;PWD=thepooh;PROTOCOL=tcp;
NODE=milne;SERVICE=1360"

"DRIVER={Mimer};DATABASE=pip;UID=mickey;PWD=mouse;PROTOCOL=NamedPipes;
NODE=winpix;SERVICE=pip"

"DRIVER={Mimer};DATABASE=disney;UID=donald;PWD=duck;PROTOCOL=decnet;
NODE=pictvms;INTERFACE=BG"

5.1.3 Connection Attribute SQL_ATTR_CONNECTION_DEAD (V9.2.1)

The connection attribute SQL_ATTR_CONNECTION_DEAD to poll connection status is now supported. By calling SQLGetConnectAttr with this attribute, the driver will return SQL_CD_TRUE if the connection has become unusable. SQL_CD_FALSE is returned if the driver believes the connection is alive and well. The driver won't try to diagnose the connection status, it merely returns what it has learned so far. Calling SQLGetConnectAttr with this attribute is therefore very cheap, but connection changes since the last network request will go by unnoticed.

5.1.4 Internal Array Fetching (V9.2.1)

The ODBC driver is now doing internal array fetches whenever possible. In most situations this will improve performance, but there may be some situations where it is important to get the first few rows returned to the application fast. In these situations, setting the row array size (SQL_ATTR_ROW_ARRAY_SIZE statement attribute) to anything, for instance one, will disable internal array fetching.

5.1.5 Mimer Specific Descriptor Fields (V9.2.1)

Mimer SQL supports large objects of up to 8 terabytes. This poses a problem to ODBC applications since the ODBC API specifies length fields to be 32 bits. An ODBC compliant application is therefore unable to work with any objects larger than 2 gigabytes. The SQL/CLI standard has the same problem, so we won't get much help there.

To remedy this situation, the Mimer ODBC driver has four vendor specific descriptor attributes. Each attribute mimics the behavior of an existing attribute, the only difference is that the existing attribute is working with a 32-bit integer while ours use 64-bit integers.

C-definitions for these attributes are available in the mimodbc.h header file. Include this file along with the regular ODBC include files (sql.h, sqlext.h) to gain access to these features.

Note that, although these attributes are most useful when working with large objects, they may be used for any type of data.

These are the extra descriptor attributes:

Record field name
Type
R/W
Default

SQL_DESC_DISPLAY_SIZE_64

SQLBIGINT

ARD: Unused

APD: Unused

IRD: R

IPD: Unused

ARD: Unused

APD: Unused

IRD: D

IPD: Unused

SQL_DESC_LENGTH_64

SQLUBIGINT

ARD: Unused

APD: Unused

IRD: R

IPD: R/W

ARD: Unused

APD: Unused

IRD: D

IPD: ND

SQL_DESC_OCTET_LENGTH_64

SQLBIGINT

ARD: Unused

APD: Unused

IRD: R

IPD: R/W

ARD: Unused

APD: Unused

IRD: D

IPD: ND

SQL_DESC_OCTET_LENGTH_PTR_64

SQLBIGINT*

ARD: R/W

APD: R/W

IRD: Unused

IPD: Unused

ARD: Null ptr

APD: Null ptr

IRD: Unused

IPD: Unused

SQL_DESC_DISPLAY_SIZE_64 [IRDs]

This read-only SQLBIGINT record field contains the maximum number of characters required to display the data from the column. The value in this field is not the same as the descriptor field SQL_DESC_LENGTH because the SQL_DESC_LENGTH field is undefined for all numeric types.

SQL_DESC_LENGTH_64 [Implementation descriptors]

This SQLUBIGINT record field is either the maximum or actual character length of a character string or a binary data type. It is the maximum character length for a fixed-length data type, or the actual character length for a variable-length data type. Its value always excludes the null-termination character that ends the character string. For values whose type is SQL_TYPE_DATE, SQL_TYPE_TIME, SQL_TYPE_TIMESTAMP, or one of the SQL interval data types, this field has the length in characters of the character string representation of the datetime or interval value. Note that this field is a count of characters, not a count of bytes.

The value in this field may be different from the value for "length" as defined in ODBC 2.x. For more information, see Microsoft ODBC 3.0 Programmer's Reference, Appendix D, "Data Types."

SQL_DESC_OCTET_LENGTH_64 [Implementation descriptors]

This SQLBIGINT record field contains the length, in bytes, of a character string or binary data type. For fixed-length character or binary types, this is the actual length in bytes. For variable-length character or binary types, this is the maximum length in bytes. This value always excludes space for the null-termination character.

SQL_DESC_OCTET_LENGTH_PTR_64 [Application descriptors]

This SQLBIGINT* record field points to a variable that will contain the total length in bytes of a dynamic argument (for parameter descriptors) or of a bound column value (for row descriptors).

For an APD, this value is ignored for all arguments except character string and binary; if this field points to SQL_NTS, the dynamic argument must be null-terminated. To indicate that a bound parameter will be a data-at-execution parameter, an application sets this field in the appropriate record of the APD to a variable that, at execute time, will contain the value SQL_DATA_AT_EXEC or the result of the SQL_LEN_DATA_AT_EXEC macro. If there is more than one such field, SQL_DESC_DATA_PTR can be set to a value uniquely identifying the parameter to help the application determine which parameter is being requested.

If the OCTET_LENGTH_PTR_64 field of an ARD is a null pointer, the driver does not return length information for the column. When setting the SQL_DESC_OCTET_LENGTH_PTR_64 field to anything other than a null pointer, this field overrides the SQL_DESC_OCTET_LENGTH_PTR field. When both SQL_DESC_OCTET_LENGTH_PTR and SQL_DESC_OCTET_LENGTH_PTR_64 are set to null pointers the driver assumes that character strings and binary values are null-terminated. (Binary values should not be null-terminated, but should be given a length to avoid truncation.)

If the call to SQLFetch or SQLFetchScroll that fills in the buffer pointed to by this field did not return SQL_SUCCESS or SQL_SUCCESS_WITH_INFO, the contents of the buffer are undefined. This field is a deferred field: It is not used at the time it is set, but is used at a later time by the driver to determine or indicate the octet length of the data.

5.1.6 Support for NATIONAL CHARACTER LARGE OBJECT (NCLOB) type (V9.2.1)

ODBC now supports the SQL NCLOB type. The client may use any character string data type, but non-Latin1 data may only be accessed using the wide character client type SQL_C_WCHAR.

If an NCLOB containing non-Latin1 characters is fetched into a SQL_C_CHAR an error exception is raised.

5.1.7 Parameter Arrays in Conjunction With Data at Execution Parameters (V9.1.3)

Prior to version 9.1.3, Mimer ODBC did not support the usage of parameter arrays in conjunction with data at execution parameters. Data at execution parameters are specified by setting the word pointed to by SQL_ATTR_OCTET_LENGTH_PTR to SQL_DATA_AT_EXEC, or to a value defined by the SQL_LEN_DATA_AT_EXEC(n) macro. In these situations, the driver tells the application to supply parameter data by having the SQLExecute call returning SQL_NEED_DATA. In these situations the application should call SQLParamData to obtain the parameter requested by the driver. The data should then be supplied by calling the SQLPutData function. In Mimer ODBC version 9.1.3 and onwards, after the call to SQLParamData, the word pointed to by the statement attribute SQL_ATTR_PARAM_PROCESSED_PTR will contain information on which row the driver expects data to. The parameter is recognized by using the SQLParamData parameter *ValutPtrPtr, as before.

5.1.8 Unicode API (V9.1.1)

On Microsoft Windows systems, Mimer ODBC fully supports the wide character API used by the Microsoft ODBC Driver Manager. Whenever drivers export these functions (like SQLConnectW), the driver manager will use them with Unicode (UTF-16) arguments.

If the application has been compiled to use ASCII strings (UNICODE=0 define) the Driver Manager will convert the string to Unicode before calling the driver.

5.1.9 National Character Data Types (V9.1.1)

The SQL_WCHAR data type is now fully supported. Applications may now use the SQL_WCHAR data type to store and retrieve Unicode data.

Exactly which Unicode format is used depends on the platform. Microsoft Windows uses 16-bit characters while most other platforms use 32-bit characters.

5.1.10 Large OBject Support (V9.1.1)

The SQL data types SQL_LONGVARBINARY and SQL_LONGVARCHAR are now fully supported.

Each data type supports large character and binary objects of sizes up to 8 TB. 32-bit platforms have a smaller size limit of 2 GB.


5.2 Changed Functions

The following sections document changed functions.

5.2.1 SQLGetDiagField Returns Row Value for SQL_DIAG_ROW_NUMBER (V9.3.7)

The SQL_DIAG_ROW_NUMBER diagnostic field is supposed to be used by applications to determine which row of a parameter set a certain error occurred on. Previously, the Mimer ODBC driver returned this value only for errors that was detected by the client. The row number for errors detected by the server was SQL_ROW_NUMBER_UNKNOWN (-2).

Now, the ODBC driver returns the row number for server related errors as well. Note that SQL_ROW_NUMBER_UNKNOWN may still be returned for errors that are unrelated the row of the parameter set, such as a network failure.

5.2.2 Indicator Variables Return SQL_NO_TOTAL (V9.3.6)

Indicator variables, for example as specified in the last argument to SQLBindCol, may now return the value SQL_NO_TOTAL (-4) if the actual size of the column data to be returned cannot be calculated at the time of the call to SQLFetch. This may occur only if the SQL data type is CLOB or NCLOB, and the host variable data type is SQLCHAR or SQLWCHAR.

If the SQL_NO_TOTAL is returned, the server does not know the exact length of the text object, so it must call SQLGetData consecutive times until SQL_SUCCESS is returned.

5.2.3 Need Data State Now Handled at Statement Handle Level (V9.3.5)

Earlier versions of Mimer ODBC handled the Need data state entirely at the connection level. This had the effect that once one statement handle entered the Need data state, other statement handles attempting to perform many operations would return with a Function sequence error.

Now, whenever a statement is needing more data, such as a call to SQLExecute or SQLExecDirect has returned SQL_NEED_DATA, only that statement goes into need data mode. Other statements on the same connection handle may continue doing their business as usual. The connection related Need data state, which may be entered after a call to SQLBrowseConnect, are still handled at the connection handle level.

This has the effect that it is now possible to have two or more simultaneous SQLExecute/SQLParamData/SQLPutData-session ongoing at the same time, which was not possible before.

5.2.4 SQLBrowseConnect and SQLDriverConnect (V9.2.2)

The ODBC Mimer Driver behavior for the DSN, DRIVER and DATABASE keywords found in a connection string has been changed.

Previously, when DATABASE was specified, the database name was always used for lookup even though a DSN was specified first. If only DSN was specified, the data source name was first tried to be found as a data source and if not found a second try was made by using it as a database name.

Now, if the DSN and DRIVER keywords are included in the same connection string, the driver use whichever keyword appears first (and the other is ignored). If DRIVER is used then the DATABASE keyword is also required.

5.2.5 Mimer Product Name (V9.2.1)

The Mimer product name has changed from `MIMER/DB' to `Mimer SQL'. This reflects error messages as well as some information items returned by SQLGetInfo, such as SQL_DBMS_NAME and SQL_DBMS_VER.


5.3 Corrected Problems

The following sections document corrected problems.

5.3.1 Malformed Cancel Requests (V9.3.8B)

A cancel request issued by a ODBC client connected to a database server with a byte ordering different from the client would cause the server to drop the connection. In the Mimer log an "Illegal reentrant request on channel" message would be produced.

E.g. if this problem occurs when a Mimer ODBC client on Windows is connected to a Mimer SQL server running on Solaris or AIX, the Mimer Windows client needs to be updated.

This problem has been fixed in Mimer clients from version 9.3.8B and later.

5.3.2 SQL_DESC_LENGTH for Binary Columns (V9.3.8B)

The Mimer ODBC driver returned the wrong SQL_DESC_LENGTH value for BINARY and BINARY VARYING columns. In all cases, the returned value was the correct length times two. The correct behavior is to return the length in bytes for binary columns. The problem appeared in 8.2.1.

5.3.3 SQL_ATTR_PARAM_OPERATION_PTR Array (V9.3.7)

The array pointed to by the statement attribute SQL_ATTR_PARAM_OPERATION_PTR was incorrectly treated by the driver as a 32-bit integer array, while the correct behavior is to treat this array as a 16-bit integer array.

5.3.4 SQL_ATTR_PARAM_STATUS_PTR Array Not Filled Properly (V9.3.7)

The array items pointed to by the statement attribute SQL_ATTR_PARAM_STATUS_PTR was not set properly after execute. Each array item, corresponding to each row in a parameter set (array of parameters) is now set to SQL_PARAM_SUCCESS, SQL_PARAM_ERROR or SQL_PARAM_UNUSED depending on the outcome of the array operation.

Also, if parameter rows are set to be ignored using the SQL_PARAM_IGNORE parameter operation attribute (see the statement attribute SQL_ATTR_PARAM_OPERATION_PTR), the corresponding parameter status is now being set to SQL_PARAM_UNUSED, to indicate that this particular parameter row was not used.

5.3.5 External Character Set Support (V9.3.5)

The handling of the data type SQL_C_CHAR has been completely revised in this version. The system now follows the current locale setting on the machine to determine what characters are stored/retrieved when an application passes single-byte character strings to ODBC.

When character data is stored in Mimer SQL it can be stored in CHAR or VARCHAR columns or in NCHAR or NVARCHAR columns. Data in CHAR and VARCHAR columns use the Latin-1 character representation (also called ISO 8859-1). This character set can only be used to store 256 different characters. For the exact characters that can be stored see Mimer SQL Reference Manual, Appendix B. To store any other characters the data type NCHAR or NVARCHAR must be used. These column types can store ANY character.

If a locale is used by the application that has characters that are not included in Latin-1, it means that the columns in the database data must use an NCHAR or NVARCHAR column to store the correct characters. Previously, each character in the application was simply stored in a character column. When these characters were retrieved with, for example, DbVisualizer or other Unicode enabled applications, the interpretation of the characters were done differently and the wrong characters were displayed. With the new locale support the Mimer SQL client now understands the representation of the characters in the application and maps them accordingly to its internal representation.

When retrieving data from the database, the translation work the other way. I.e. when retrieving data from a CHAR or NCHAR column to a SQL_C_CHAR variable, the current locale must be able to represent all the characters returned from the database. When this is not possible, a conversion error -10401 is returned. If characters stored in the database have no representation in the chosen locale, a wide character data type must be used by the application instead (SQLWCHAR rather than SQLCHAR).

This means that applications using older versions of Mimer ODBC, may have to be updated to work with the new version. Typically, the only change needed is to alter the data type used in the database from CHAR to NCHAR, or from VARCHAR to NVARCHAR. This is done with the ALTER TABLE statement (see Mimer SQL Reference Manual). Other possible changes is to switch from a character representation (SQL_C_CHAR) to a Unicode representation (SQL_C_WCHAR) for the application variables. Yet another solution is to switch to a locale that can handle all relevant characters.

On Windows the setting used for the external character set is set in the Regional and Language Options in the Control Panel under the tab Advanced. This setting is used automatically by the Mimer ODBC client.

On VMS the system continues to use the Latin-1 character representation regardless of locale settings.

On other platforms (Unix, Mac OS X, others) the application must call the runtime library routine setlocale to pick the locale to use. For example, the call setlocale(LC_CTYPE, "") sets the default locale as decided by the environment setting. The actual conversions made by the Mimer client are through the library routines mbstowcs (multibyte character set to wide char set) and wcstombs. Please note that if an application does not call setlocale a default 7-bit locale is used. This means that no 8-bit characters can be used without getting a conversion error. For applications where the source is not available it is possible to set an environment variable MIMER_LOCALE that will be used when calling the Mimer client. The value of the environment variable is used as the second argument to setlocale. To use the default locale set the environment variable to default. On Windows the environment variable is set to the desired code page, i.e. only numeric values may be specified (for example: 1250: ANSI Central Europe, 1251: ANSI Cyrillic, 1252: Latin1, 1253: ANSI Greek, 1254: ANSI Turkish, and so on.)

The fact that the character type is considered a multi-byte character set allows any external character representation to be used. In particular various character sets such as Traditional Chinese Big5 and Japanese Shift-JIS may be used. The character set may, of course, be a single byte character set as such as the Greek Latin-7 character set (code page 1253 on Windows). On Unix platforms the prevalent representation is UTF-8 that allows any Unicode character to be stored in a character variable.

This change in character set handling sorts out a number of problems that were present in previous versions of Mimer. Some applications may be dependent on the old incorrect handling. It is strongly recommended that these issues are dealt with in the applications to ensure correct data in the database and application, and also compatibility between platforms using different character sets.

5.3.6 Access Violation When Too Many Handles (V9.3.5)

ODBC clients could experience an access violation during deallocation of handles when executing on Linux, OpenVMS, AIX, Mac OS X or Solaris and had a number of environment or connection handles open simultaneously. The problem appeared in Mimer ODBC clients v9.2.1 and was fixed in v9.3.5.

5.3.7 SQLNativeSql With Truncated Destination Strings (V9.3.5)

Supplying a too short OutStatementText in calls to SQLNativeSql could destroy the stack causing an access violation within the Driver Manager. If a truncation occurred, the driver wrote too much of the converted SQL string to the intermediate buffer located in the Driver Manager. The problem appeared in v9.1.3 and was corrected in v9.3.5.

5.3.8 Conversion of Character Data to SQL_C_DOUBLE (V9.3.5)

When a CHAR, VARCHAR, NCHAR or NVARCHAR string was used to hold a floating point number, with a larger exponent than 255 or smaller than -255, an incorrect value was returned in the SQL_C_DOUBLE. This problem appeared in v9.3.1 and is corrected in v9.3.5.

5.3.9 Truncating a Datetime or Interval (V9.3.5)

When DATE, TIME, TIMESTAMP, or an INTERVAL with fractional seconds components was converted to a SQL_C_CHAR, or SQL_C_WCHAR and truncating only the decimal point, the call to SQLFetch, SQLFetchScroll or SQLGetData returned SQL_ERROR instead of the correct SQL_SUCCESS_WITH_INFO.

This error appeared in v9.3.1 and is corrected in v9.3.5.

5.3.10 Default Conversion for the SQL BIGINT Data Type Unavailable (V9.2.4)

The SQL data type BIGINT could not be fetched using the default C data type SQL_C_DEFAULT. Mimer ODBC clients prior to 9.2.4 encountered the error -109 `Invalid C data type' on such attempts. Fetching BIGINT columns using the SQL_C_DEFAULT data type will now correctly place the data in a 64 bit integer. In C this would correspond to a long long int or unsigned long long int.

5.3.11 Unknown Request Code in Network Package when Closing Cursors Against a Mimer 8.2.6 Server. (V9.2.3)

Applications opening cursors on a Mimer v8.2.6 server could experience the Mimer error -18551 error when closing the cursor (a call to SQLFreeStmt or SQLCloseCursor). The error text is `Unknown request code in network package'. This error could also be seen when the ODBC driver decided to close the cursor for some other reason than the application calling these functions. The consequence of this problem is that cursors remain open on the 8.2.6 server until the client disconnects. This problem is corrected in v9.2.3 clients.

5.3.12 Conversion From Wide Character Parameter (V9.2.2G)

There has been a problem with conversions from SQL_C_WCHAR parameters to numeric data types. The problem can cause an 'Invalid numeric value' error if the character string has several leading blanks. Note that the problem could occur when using Mimer SQL with external tools such as Microsoft Access.

This problem is now corrected.

5.3.13 ODBC Stand-alone Installation (V9.2.2E)

There has been a problem with Mimer ODBC stand-alone installations in previous versions of 9.2.

The installed Mimer ODBC driver could erroneously require the server to have a beta test licence. Note that the ordinary Mimer Installations are not based on ODBC stand-alone installation (see Mimer Packaging Guide for further information about ODBC stand-alone installation).

This problem is now corrected.

5.3.14 Disconnect Failure (V9.2.2E)

There has been a problem where SQLDisconnect fails with an `invalid transaction state' error. The problem occurred when SQLDisconnect is preceeded by a failing data definition statement and the connection attribute SQL_ATTR_AUTOCOMMIT is set to SQL_AUTOCOMMIT_OFF.

This problem is now corrected.

5.3.15 Statement Increasing in Size Problem (V9.2.1C)

SQL statements supplied to SQLPrepare or SQLExecDirect when the statement attribute SQL_ATTR_NOSCAN is set to SQL_NOSCAN_OFF and whose length is less than 400 characters, but as a result of the escape clause processing grows to a size larger than 400 characters may return a -16183 (bad parameter) error. This problem was introduced with the version 9.2.1 server and is corrected in version 9.2.1C and later.

5.3.16 Support of SQL_ATTR_FETCH_BOOKMARK_PTR (V9.2.1B)

In earlier versions the attribute SQL_ATTR_FETCH_BOOKMARK_PTR was not supported by Mimer ODBC. Mimer ODBC does not currently support bookmarks at all, and any attempt to enable them returns an error. Nevertheless, from v9.2.1b onwards, the bookmark attribute SQL_ATTR_FETCH_BOOKMARK_PTR is itself supported, but the driver will not do anything useful with this.

Also, when an application tries to enable bookmarks using SQLSetStmtAttr(..., SQL_USE_BOOKMARKS,...), earlier versions of the Mimer ODBC driver returned HY009 (invalid option value), rather than the more correct HYC00 (option value not supported). This is now corrected.

5.3.17 Incorrect Capability Information for Forward Only Cursors (V9.2.1B)

Earlier versions of Mimer ODBC incorrectly reported that forward only cursor could do absolute and relative cursor positioning. This was incorrect. Forward only cursors can only move forward one row at a time in a result set. This problem applies only to applications coded and compiled according to the ODBC 3.0 specification.

5.3.18 Transaction That Lives On in Autocommit Mode When a BLOB, CLOB or NCLOB Operation Fails (V9.2.1)

When in auto commit mode a BLOB, CLOB or NCLOB operation fails in some way (such as a truncation error) after the transaction was started, the transaction lived on causing problems such as 'xxx cannot be dropped because it is in use'. This could also lead to that server resources in time become exhausted. The problem is now corrected when connecting to v9.1.3 servers or later.

5.3.19 Transaction That Lives On in Autocommit Mode When a Forward Only Cursor is Limited by the SQL_ATTR_MAX_ROWS Attribute (V9.2.1)

When a forward only cursor was limited in size by the SQL_ATTR_MAX_ROWS attribute, the ODBC driver now closes the cursor immediately when the last row was read. Previously, the cursor was kept open until the statement was explicitly dropped. This led to unnecessary server resource use. The problem applied to 8.2 servers or later, and the problem is now corrected against all server versions.

5.3.20 Auto-Commit of Read-Only Cursors (V9.2.1)

There has been a problem with server transaction resources associated with auto-committed read-only cursors. Previously, these resources could remain allocated after close cursor, until the cursor was opened again or the statement handle was freed. It could have a negative effect on server performance when such transaction resources remained allocated for a very long time. This problem is now corrected.


5.4 Known Restrictions

The following sections document known restrictions.

5.4.1 ODBC on Linux Itanium(V9.3.3)

Linux: The ODBC library on Linux Itanium is not fully functional. Do not use it.

Note that ODBC clients running on other platforms can still use a database running on a Linux Itanium server.

5.4.2 SQLWCHAR on UNIX (V9.1.3)

The ODBC data type SQLWCHAR is currently fixed at 2 bytes on UNIX platforms. The Mimer ODBC driver handles the SQL_C_WCHAR data type as UTF16.

5.4.3 Statement Options Support (V7.2.1)

Connect options SQL_TRANSLATE_DLL and SQL_TRANSLATE_OPTION are not supported.

The statement option SQL_CURSOR_SENSITIVITY is not supported. Cursors in Mimer SQL Engine are always insensitive to changes made by another cursor. This insensitivity does not affect its updatability.

5.4.4 Performing ENTER/LEAVE from ODBC (V7.2.1)

When using ENTER and LEAVE from an ODBC application the following rules apply:

  • Statements that are in the prepared state before ENTER is performed are accessible after the ENTER operation.
  • All prepared statements created after ENTER are implicitly discarded when LEAVE is performed. However, the handles are still valid and may be used for subsequent operations.

    • If an application is written without regard for this rule, error messages may be returned saying:

    Illegal sequence ...

5.4.5 Bookmarks (V7.2.1)

Bookmarks are currently not supported by Mimer ODBC.

This includes the bookmark data types (SQL_C_BOOKMARK), the bookmark column (column 0), and the SQLBulkOperations routine.

5.4.6 SQLSetPos Locking Modes (V7.2.1)

The fourth argument to SQLSetPos specifies which locking mode to apply to the underlying row. It is ignored as Mimer SQL Engine uses optimistic concurrency.

Locking modes are not applicable in Mimer ODBC.


5.5 Known Problems

The following sections document known problems.

5.5.1 SQLPutData and Character Data in Parts (V9.3.5)

Specifying SQL_DATA_AT_EXEC or SQL_LEN_DATA_AT_EXEC in the indicator variable (specified in the last parameter to SQLBindParameter) and feeding character data to server by calling SQLPutData, is not working in this version.

5.5.2 SQLGetData and Character Data in Several Calls (V9.3.5)

Calling SQLGetData multiple times to obtain character data in chunks, may return incomplete characters, if the current locale is any locale that uses multibyte character sets. The incomplete character will, if it exists, always be placed at the end of each returned chunk.

5.5.3 INSERT of CLOB and NCLOB data (V9.3.5)

Inserting CLOB and NCLOB data may fail with "client and server disagreed on LOB length". It is not currently possible to insert CLOB and NCLOB data because the client and the server does not agree on the length of the objects. The BLOB data type is still available, however.

5.5.4 Inserting SQL_C_BINARY Into NCLOB Data Columns (V9.2.1)

Neither the ODBC client nor the server is validating NCLOB data when inserting data from a SQL_C_BINARY client type. This means, both that illegal character data may be stored in the NCLOB and that an illegal NCLOB length might be stored in the Mimer SQL database. The latter will have the effect that the NCLOB can never be accessed.

5.5.5 SQL_C_DEFAULT Mappings for NCLOB Unsupported (V9.2.1)

The default data type mapping for NCLOB is not supported.

The correct mapping is SQL_C_WCHAR for NCLOBs.

5.5.6 Some Conversions To and From SQL_LONGVARCHAR Not Supported (V9.1.1)

Conversions to and from all data types other than SQL_C_CHAR are not supported when working with Character Large OBjects (CLOBs).

5.5.7 SQL_C_DEFAULT Mappings for CLOB and BLOB Unsupported (V9.1.1)

The default data type mappings for CLOB and BLOB are not supported.

The correct mapping is SQL_C_CHAR for CLOBs and SQL_C_BINARY for BLOBs.

5.5.8 SQL_ATTR_PARAM_OPERATION_PTR (V8.2.1)

Using SQL_ATTR_PARAM_OPERATION_PTR to control the operation of specific arrayed input parameters only works for synchronous INSERT and UPDATE statements. This attribute is ignored for all other statements.

This statement attribute is identical to SQL_DESC_ARRAY_STATUS_PTR for Application Parameter Descriptors (APDs). Therefore, this problem applies to this descriptor attribute as well.

5.5.9 Creating Tables Using Microsoft Query (V8.2.1)

The menu alternative for creating tables in Microsoft Query does not work together with Mimer SQL Engine version 8.2.

The problem occurs because there is a limit in Microsoft Query on the length of the data type names that may be returned by SQLGetTypeInfo. This limit is exceeded by the SQL standard domains supported by Mimer SQL Engine.

5.5.10 Old Versions of Microsoft Query (V8.2.1)

Old versions of Microsoft Query terminate upon initialization, when used with Mimer ODBC driver (client) version 8.2 and Mimer SQL Engine (server) version 8.1 or 7.3.

This problem does not occur in later versions of Microsoft Query, such as version 2000.

5.5.11 Information about SYNONYMS (V7.2.1)

SQLForeignKeys and SQLStatistics do not return information about SYNONYMS.

5.5.12 Truncation of Decimals (V7.2.1)

SQL_SUCCESS_WITH_INFO should be returned if decimals are truncated when converting from character data types to exact numeric data types.

The driver currently returns SQL_ERROR.


Mimer BSQL

The following sections document new and changed functions, corrected problems and known restrictions and problems regarding Mimer BSQL.


6.1 New Functions

The following sections document new functions.

6.1.1 New List and Describe Functionality (V9.3.0)

BSQL has support for list and describe function for all objects in version 9.3 of Mimer. List and describe support for executable statements has been added.

6.1.2 SET EXECUTE ON/OFF Statement (V9.2.2)

When EXECUTE is set to OFF, statements will be compiled but not executed. This can be useful when checking statements for errors or when using the explain facility.

6.1.3 SET EXPLAIN ON/OFF Statement (V9.2.2)

When EXPLAIN is active, a query plan will be shown. This plan shows in which order tables are accessed and if any indexes are used when executing the query.

Example:

SQL>set explain on;
SQL>set execute off;
SQL>select * from orders as o, order_items as oi
SQL&  where o.order_id = 12
SQL&  and o.order_id = oi.order_id;

L1:
Sequential read MIMER_STORE.ORDERS , end of table goto end
compare, no hit goto L1
L2:
Sequential read MIMER_STORE.ORDER_ITEMS , end of table goto L1
compare, no hit goto L2
Record found, resume at L2
end:

See Mimer SQL User's Manual for more details.

6.1.4 Additional Parameters for BSQL Command (V9.2.1)

When starting BSQL it is now possible to supply username and password to the command to facilitate an automatic login.

Example VMS:
BSQL /USERNAME="MIMER_STORE" /PASSWORD="GoodiesRUs"
Example UNIX:
bsql -u MIMER_STORE -p GoodiesRUs

It is also possible to give a command as argument in which case BSQL executes the query and then quits.

Example:
bsql -q"select * from music_store" -uMUSIC_STORE -pGoodiesRUs

This command can be any valid SQL/BSQL statement.

6.1.5 List/Describe Functions for Collations (V9.2.1)

It is possible to get information about collations. This can be obtained by using the menus or commands directly.

Example:
SQL> describe collation information_schema.albanian_1;

6.1.6 UTIL Functionality (V9.2.1)

Some of the functionality formerly found in UTIL has been moved to BSQL. This includes the readlog functionality and logic for recreating system databanks at startup.

The menu for readlog is invoked by using the statement READLOG, see Mimer SQL Engine User Manual.

6.1.7 New Data Type NCLOB (V9.2.1)

The new data type NATIONAL CHARACTER LARGE OBJECT (i.e. NCLOB) can be used in BSQL.

6.1.8 Enhanced File I/O (V9.1.1)

Mimer BSQL can now handle files in UTF8, UTF16, UTF16BE, UTF16LE, UTF32, UTF32BE and UTF32LE formats. For example:

LOG OUTPUT ON 'sales' AS UTF16;

For more information, see the LOG and READ commands in the Mimer SQL Engine User's Manual.

6.1.9 Limiting the Amount of Data Displayed (V9.1.1)

Two new BSQL commands have been introduced. These are:

SET MAX_CHARACTER_LENGTH value

SET MAX_BINARY_LENGTH value

The keywords MAX_CHARACTER_LENGTH and MAX_BINARY_LENGTH can be abbreviated to MC and MB respectively.

Example:
SET MC 20;
SELECT * FROM INFORMATION_SCHEMA.COLUMNS;

In the example above, only the first 20 characters of all (character type) columns will be displayed.


6.2 Changed Functions

The following sections document changed functions.

6.2.1 BSQL Command-line Arguments (V9.3.4)

The BSQL command-line switches for username and password must now include a value. To connect as OS_USER, specify "" as username, i.e:

bsql --username=""


6.3 Corrected Problems

The following sections document corrected problems.

6.3.1 Duplicated Output in Readlog (V9.3.8B)

When listing operations done on a table, some data could in rare cases be written twice to the log file. This is now corrected.

6.3.2 Altered Tables in Readlog (V9.3.8B)

If a table had been altered, readlog did not display operations on this table. This is now corrected.

6.3.3 Incorrect Privilege Description (V9.3.8B)

When using the DESCRIBE TABLE command in BSQL, privileges granted on the table was not shown correctly. This is now corrected.

6.3.4 Tables Not Found in Readlog (V9.3.8)

When using the readlog utility after an ENTER statement in BSQL, the procedure for verifying that the current user has some privilege on a table did not function properly. E.g. it claimed erroneously that tables did not exist. This problem is now corrected.

6.3.5 Operations Not Shown in Readlog (V9.3.8)

When using the Readlog utility operations on some tables was not shown. This could happen for tables that have been altered or if the option for showing operation for specific tables was used repeatedly. This problem is now corrected.

6.3.6 Describe View Problem (V9.2.5)

When describing a view in BSQL with a definition length > 400 characters the definition would not be displayed.

This has now been corrected.

6.3.7 Read Input (V9.2.5)

Starting BSQL with a query such as

bsql - q "read 'file'"

where file contains a read input command would cause BSQL to crash. This have now been corrected.

6.3.8 Mimini Usage (V9.2.5)

Support for using the MIMINI file is now working again.

6.3.9 Describe Result in Wrong Order (V9.2.2E)

When describing a routine whose definition exceeds 800 character, the source definition would sometimes be mixed up. This has now been corrected.

6.3.10 Using <tab> in Load Statement (V9.2.2E)

The use of white-space characters (<tab>, <cr>, <lf>) in a LOAD/UNLOAD statement caused unwarranted syntax errors. This has now been corrected.

6.3.11 Failed Procedure Calls With Input Followed by Output Parameters (V9.2.1)

When BSQL was used to call a procedure having input parameters before the output parameters in the parameter list, BSQL returned errors such as -10310 'invalid character value for cast' or -10203 'negative overflow occurred during data type conversion'. Only 9.1 BSQL versions are affected by this problem. Earlier versions worked well.

6.3.12 String Literal Usage (V9.2.1)

When reading input containing character literals spanning multiple lines without a terminating apostrophe, it will not be a valid literal. If you wish to have a literal that spans multiple lines, you should use the string concatenation operator, e.g.

INSERT INTO music_title VALUES(4711,
'The long and winding ' ||
'character literal that spans ' ||
'multiple lines')

6.3.13 Describe Functions - New Client/Old Server (V9.1.2)

Previously, in some describe functions, there was a problem using a version 9.1 BSQL client against a version 8.2 database server. This is now corrected.


6.4 Known Restrictions

The following sections document known restrictions.

6.4.1 READLOG, NCHAR and LOB (V9.2.1)

READLOG does not show data for columns defined as NCHAR, NCHAR VARYING, BLOB, CLOB or NCLOB. In these cases the text <nchar data> or <lob data> is shown instead.

6.4.2 Mimer BSQL 8.2 Unable to do DESCRIBE (V9.1.1)

Because of a correction in the INFORMATION_SCHEMA, Mimer BSQL 8.2 clients connected to servers of version 9.1 and later, are unable to use the DESCRIBE command to see information about tables, domains, and views. BSQL doesn't find any objects of these types and the message 'No information found' is returned. This problem applies to BSQL version 8.2 only, both BSQL 8.1 and older, and BSQL 9.1 and newer work well against version 9 servers.

6.4.3 Cancel in BSQL (V8.1.1)

Canceling the current database request by using <Ctrl-c> is not yet supported in BSQL.

6.4.4 Column Header Limit in BSQL (V7.2.1)

The column header generated by BSQL is limited to 4000 characters.

This restriction disallows SELECT statements which have numerous large columns. SELECT statements with only a few very large columns are not a problem, since each column contributes at most one line (usually 80 characters) to the column header.

6.4.5 LOAD/UNLOAD of VARCHAR (V7.2.1)

When a column of type VARCHAR is unloaded to a sequential file, the output field is padded with blanks. On the other hand, when load is performed to a column of type VARCHAR, the trailing blanks are not considered to be part of the value. This means that the load operation cannot determine whether any trailing blanks are significant.

In order to avoid this problem, you can use the option for using a delimiter when unloading data.


6.5 Known Problems

The following section documents known problems.

6.5.1 LOAD and Disk Space Exhausted (V7.2.1)

Large LOAD operations are automatically divided into several transactions. If, during such an operation, there is not enough disk space to complete all of these transactions, this may result in the table being partially loaded.

When more disk space is made available, the load operation can be repeated, but then a number of duplicates will be received.


6.6 Deprecated Functions

The following sections document deprecated functions.

6.6.1 Mimini Initiation File (V9.3.5)

The initiation file MIMINI is no longer supported. Corresponding functionality can be achieved by using command line arguments for BSQL.

6.6.2 Load/Unload Deprecated (V9.1.1)

The BSQL functionality for Load/Unload is now deprecated. Use MIMLOAD instead. See MIMLOAD (V9.1.1).


DbVisualizer

The SQL front-end tool DbVisualizer, from Minq Software (http://www.dbvis.com/), is tightly integrated with Mimer SQL and displays information about all Mimer SQL database objects.

The tool can also be used with other databases. To do this a separate license is required, see help menus in DbVisualizer for more information.

The following sections document new and changed functions, corrected problems and known restrictions and problems regarding DbVisualizer.


7.1 New Functions

The following sections document new functions.

7.1.1 New Version of DbVisualizer (V9.3.8)

A new version of DbVisualizer called 6.0 is now bundled with Mimer SQL. It contains enhancements for:

  • Data type drop down support.
  • Action dialogs do not contain double quotes for names in drop-down lists.
  • Support for simple create table when using the free version of DbVisualizer.
  • Better readability for dialogs where sizes are given i K-, M-, or G-bytes of data.
  • Support for replication information.

7.1.2 Overloaded Function Names (V9.3.7E)

Support for function and procedure names with overloaded parameters, i.e. where several instances of a procedure with the same name is present.

7.1.3 New Version of DbVisualizer (V9.3.5)

A new version of DbVisualizer called 5.1 is now bundled with Mimer SQL. It contains numerous enhancements for constructing SQL statements for all different Mimer SQL commands.

In the new version of DbVisualizer the Mimer driver is pre-loaded which means that new users do not have to locate the Mimer JDBC jar files manually.

Win: On the Windows platform the default has changed from Alloy to Windows look and feel.

Win: The new version requires at least version 1.5 of Java Runtime. This is verified when DbVisualizer is started.

7.1.4 DbVisualizer (V9.2.4D)

The SQL tool DbVisualizer is bundled with Mimer SQL.


7.2 Changed Functions

N/A


7.3 Corrected Problems

The following sections document corrected problems.

7.3.1 Overloaded Function and Procedure Names (V9.3.7E)

The commands for functions and procedures in DbVisualizer did not handle parameter overloading correctly when using a server with version 9.3. The commands are now changed to use specific names for procedures and functions. At the same time the views for Functions facts and Execute permissions are updated to display correct information.

Previously, in some describe functions, there was a problem using a version 9.1 BSQL client against a version 8.2 database server. This is now corrected.


7.4 Known Restrictions

The following sections document known restrictions.

7.4.1 Parameter Marker Support (V9.3.1)

DbVisualizer currently does not support parameter markers. This means that procedures with output parameters and precompiled statements can generally not be executed from DbVisualizer.


7.5 Known Problems

N/A


Mimer Utilities

The following sections document new and changed functions, corrected problems and known restrictions and problems regarding utility programs.


8.1 New Functions

The following sections document new functions in utility programs.

8.1.1 Miminfo SQLPOOL Information Extended (V9.3.7F)

When showing information about SQLPOOL statistics, the system now shows the number of statements and active transactions per connection to the server. This information may be used to track leaks in applications that, for example, forget to close handles.

The number of statements typically correspond to the number of SQL statements open by the application. In some cases, such as when a procedure is called, more than one statement may be needed by the system to keep track of all referenced objects.

The number of transactions include both explicitly started transactions as well as statement transactions started when a statement is opened and autocommit is on. In addition internal database server transactions performed on behalf of the user may show up briefly in the statistics.

8.1.2 MIMLOAD UNLOAD To Two Files (V9.3.7E)

The UNLOAD command can now optionally take two files as arguments, where the first file will contain the definitions and the second file will contain data. This makes it easier to merge several data files.

Example:
mimload -uSYSADM -p"pwd" "unload to 'def.dat', 'data.dat' from current user"

8.1.3 MIMLOAD Performance Improvement (V9.3.7)

MIMLOAD's load performance has been improved significantly with the use of array operations.

8.1.4 MIMLOAD File Size Reduction (V9.3.7)

When MIMLOAD unloads fixed character data (CHAR and NCHAR data types), trailing spaces are now removed before the column is written to the output file. This may reduce the size of the output file.

8.1.5 MIMLOAD Support For NCLOB (V9.3.7)

MIMLOAD can now load and unload NCLOB data.

8.1.6 Updated DBC Syntax (V9.2.1)

The DBC program has a new, optional parameter for sysdb filename. This filename is required if any tables are using collations. If not specified, the correct sort order for such tables is unknown.

8.1.7 DBOPEN Command Line Arguments (V9.2.1)

The DBOPEN program now accepts username and passwords as command line arguments.

Example (Unix)
dbopen -u SYSADM -p GoddiesRUs server1
Example (VMS)
dbopen /username=SYSADM /password=GoodiesRUs server1

8.1.8 EXPTOLOAD (V9.2.1)

Since UTIL has been removed from version 9.2 of Mimer SQL, an Exptoload program is available for conversion of old UTIL export files to files that can be loaded by the LOAD/UNLOAD functionality.


8.2 Changed Functions

The following sections document changed functions.

8.2.1 Subscription Table Limit Removed (V9.3.7)

There was a limit of 500 tables in a replication subscription. This has been changed and the internal structures are now dynamically allocated and the number of tables in a subscription is now only limited by available memory.

8.2.2 Unimportant REPSERVER Logging Removed (V9.3.7)

REPSERVER writes on the log output the number of rows deleted during and after purge of the log tables. It used to write on the log even when 0 rows were deleted. This caused a lot of unnecessary text in the log output. Now output is only written when changes has been made to a table.

8.2.3 MIMLOAD Command-line Arguments (V9.3.4)

The -d (Unix, Windows) or /DATABASE (VMS) switch has been removed. Database is now an optional argument, which must be specified as the last argument.

The password to the program ident switch has been removed. Now a subsequent switch is used to specify the password. Short form switch -i, long form switch --using, VMS style switch /USING.

Examples:
mimload -u Usr -p UsrPwd -e Pgm -i PgmPwd ...
mimload --username=Usr --password=UsrPwd --program=Pgm --using=PgmPwd ...
MIMLOAD /USERNAME="Usr" /PASSWORD="UsrPwd" /PROGRAM="Pgm" /USING="PgmPwd" ...

8.2.4 DBOPEN Command-line Arguments (V9.3.4)

The DBOPEN command-line switches for username and password must now include a value. To connect as OS_USER, specify "" as username, i.e:

dbopen --username=""

8.2.5 MIMLOAD (V9.1.1)

A new program, MIMLOAD, for loading definitions and data to and from databases has been implemented. For more information, see the Mimer SQL Engine System Management Handbook.


8.3 Corrected Problems

The following sections document corrected problems in the utility programs.

8.3.1 Interrupted Display of Users List (miminfo -u) (V9.3.8B)

There has been a problem that caused the miminfo program to interrupt the display of current users with a "circular list" error message. This problem is now corrected.

8.3.2 MIMLOAD and Stopping Server (V9.3.8)

There has been a problem which could cause loaded data from a completed MIMLOAD to disappear if the database server crashes afterwards.

This problem has now been corrected.

8.3.3 DBC Long File Names (V9.3.7F)

The databank check utility, DBC did not handle command line parameter output file names longer than 80 characters properly. The user may experience this, depending on platform, either as characters beyond the 80th are replaced by junk characters or that a exception is raised.

Problem appeared in v8.2.1 and was corrected in v9.3.7F.

8.3.4 REPSERVER Missing Transactions (V9.3.7)

REPSERVER could in some cases miss parallel transactions where short transactions start and commit within the scope of long transactions.

8.3.4.1 Upgrading From V9.3.5 Replication

When upgrading a v9.3.5 system using replication, a few manual steps have to be done before starting the v9.3.7 replication. These are described here:

· Before upgrading to V937

When still using v9.3.5, the table REPADM.REP_LOG and all source log tables (schema.REP_TABLE_name) must be emptied. Running repserver with the exit option (-e) on all subscriptions should empty all log tables. You can verify that the log tables are empty by doing a SELECT COUNT(*) FROM REP_LOG as user REPADM.

· After the upgrade to V937

Log in as REPADM using BSQL and run the following statements:

CREATE TABLE REP_LOG_PROCESSED (
  SUBSCRIPTION_NAME VARCHAR(128),
  SYSTEM_STARTUP TIMESTAMP(2),
  TRANSNO INTEGER,
  PRIMARY KEY (SUBSCRIPTION_NAME, SYSTEM_STARTUP, TRANSNO)
) IN REPDB;
GRANT ALL ON REP_LOG_PROCESSED TO REP_SOURCE_USER;

You can now start REPSERVER.

8.3.5 MIMSYNC Multi-column Primary Key Problem (V9.3.7)

MIMSYNC did not work correctly in some cases when there were more than one column in the primary key of a table. This has been corrected.

8.3.6 MIMSYNC Comparison Problem (V9.3.7)

MIMSYNC did not work correctly when primary key columns differed in the right-most parts of the column and a binary compare was done. This has been corrected.

8.3.7 MIMLOAD Error Message Problem (V9.2.5)

The Windows release of Mimer SQL v9.2.4C had an error in MIMLOAD, which meant error messages produced from MIMLOAD were not printed correctly. This has been corrected.

8.3.8 DBC Giving Errors for SQLDB After Crash (V9.2.4)

A control of the SQLDB databank after a server crash may signal errors. SQLDB, which is considered as a work databank always indicates "properly closed", which lead to the DBC program earlier was doing a bitmap control.

8.3.9 Comments Causing Misplaced Breakpoints (V9.2.2E)

When trying to use the PSM debugger on a routine containing comments the icons indicating possible breakpoints were not set correctly. This problem also affected the STEP functionality and display of variable values.This has now been corrected.

8.3.10 DBC Program Not Fulfilling Databank Control (V9.2.2E)

If the databank has more than one rootpage (happens when there are many tables) and the last table entry stored in a rootpage holds an empty table, the DBC program misses to verify tables in the rootpage following.

This is now corrected.

8.3.11 MIMLOAD and Views Using Sequences (V9.2.2E)

MIMLOAD did not include any sequence usage (NEXT_VALUE or CURRENT_VALUE) in the view definititions. This problem is now corrected.

8.3.12 MIMLOAD and LOB Data (V9.1.3)

MIMLOAD can now load and unload LOBs correctly.


8.4 Known Restrictions

The following sections document known restrictions in MIMLOAD, LOAD and UNLOAD.

8.4.1 Overloaded Routines (V9.3.1)

Overloaded routines and specific names are currently not supported by MIMLOAD.

8.4.2 Boolean Data Type (V9.3.1)

The Boolean data type is currently not supported by MIMLOAD.

8.4.3 GOALSIZE, MINSIZE and MAXSIZE on Databanks (V9.3.1)

The GOALSIZE, MINSIZE, and MAXSIZE properties for databanks are currently not included in CREATE DATABANK statements written by MIMLOAD.

8.4.4 Displaying Variables in PSM Debugger (V9.1.1)

The PSM Debugger cannot display NCHAR or NCHAR VARYING variables. The text <Unknown datatype> is currently shown instead. This will be corrected in a future release.


8.5 Known Problems

The following sections describe known problems in the utility programs.

8.5.1 Last Line Execution (V9.2.2E)

When standing on the last line in a routine being debugged it is some times necessary to press STEP twice before the line is executed.


8.6 Deprecated Features

8.6.1 Mimer UTIL (V9.2.1)

UTIL is deprecated and has been removed from the Mimer SQL distribution.

In Mimer UTIL there was an option to generate special system databanks for the old Mimer modules FM, PG, QL, RG, and SH. Please, contact our support department if this option is required.


Upgrading from Older Versions

The following sections document how to upgrade a Mimer SQL Engine database.


9.1 Upgrading the Mimer SQL Engine Database to Version 9.3

9.1.1 Before Running the Upgrade Program

Note: This section is important. You should ensure that your database is consistent before you run the upgrade program!

Before upgrading your old database, make sure that it is logically consistent.

When you stop Mimer SQL, the tables may be logically inconsistent, as some committed transaction may not have been completed. Normally, when the system is restarted, these operations will be read from TRANSDB on a per-databank basis after accessing each databank and be automatically completed. The upgrade program may not be able to finish these transactions, so they must be completed before the upgrade process.

Before you upgrade, we strongly recommend that you run the DBOPEN facility to open all databanks and ensure transaction consistency quickly. See your System Management Handbook for more information on DBOPEN.

9.1.2 Running the Upgrade Program

The upgrade program can be used for Mimer SQL version 7.1 - version 9.2 databases. The upgrade program will perform the following operations:

  • Generate a new system databank (SYSDB). The new filename will be SYSDB93. This includes generating new schemas for:
  • FIPS_DOCUMENTATION
  • INFORMATION_SCHEMA
  • INFO_SCHEM
  • MIMER
  • Convert the old transaction handling databank (TRANSDB), transaction logging databank (LOGDB) and work databank (SQLDB).
  • The old contents in these databanks are deleted, e.g. the entire log is dropped.
  • Transfer all information from the old system databank to the new, i.e. move and convert data according to the new layouts for the system base tables.
  • Convert (bitmap and root pages are re-built) all user databanks (and shadows). If a databank (or shadow) cannot be opened (e.g. due to file not found at location stored in dictionary) an error message is displayed and the databank can later be dropped or the upgrade program can be executed again after correcting the error situation.

If the old database is from version 9.1:

As the implementation of collations has been changed since 9.1 the following are done by the upgrade program:

  • If a collation was specified for any column participating in a primary key clause, the collation specification is removed. To introduce it again you need to do ALTER TABLE DROP CONSTRAINT pk_constraint_name, ALTER TABLE ALTER column_name data_type COLLATE collation_name, ALTER TABLE ADD CONSTRAINT constraint_name PRIMARY KEY (...) after upgrade.
  • If any of the system-defined collations (ENGLISH, SWEDISH or UNICODE_DEFAULT) was specified for other columns, they will be changed to use the new definitions.
  • If any user-defined collations was specified for other columns, they will be removed.
  • All indexes including any column using a collation or specified by collate clause in the index are dropped. Use CREATE INDEX after upgrade to recreate wanted indexes.
  • All user-defined collations are removed.

If the old database is from versions older than 8.2 it will:

  • Eliminate any duplicate access privileges given.
  • Transform the programs MIMER_BR, MIMER_SC, and MIMER_SW to the equivalent privileges BACKUP, STATISTICS and SHADOW.
  • Give generated names to all constraints such as primary keys, foreign keys, check clauses and so on.
  • Give privileges so that primary key updates will be allowed.
  • Mark all indexes as inconsistent (see Update statistics).
  • If any create statement (domain, table, view, procedure) stored in the dictionary uses a newly reserved word, the word will be quoted.
  • If there were synonyms or views defined on any of the old system base tables or views, they are dropped and cannot be used by any application or script. The reason is that the old base tables no longer exist and the views have changed.
  • Any privileges granted on old system base tables or views are also removed. SYSADM has SELECT privilege on the new base tables. PUBLIC has SELECT privilege (with grant option) on all system views, so no further granting is required.
  • For each user databank, it will rebuild any 'index' table with one or more VARCHAR columns.

    • Win: After you install version 9.3.x of Mimer SQL Engine database server, you must upgrade existing system databanks.

    In Mimer Administrator, right-click on a database and select Upgrade. Click Yes to confirm that you want to upgrade the system databanks. Mimer SQL Engine upgrades the databanks automatically.


9.2 Known Restrictions

After upgrading, the following restrictions are known.

9.2.1 Changed System Views (V9.3.7)

In version 9.3 some columns of the system views have been changed. This implies that if you, in version 8.2, 9.1 or 9.2, have defined your own view using such a system view there might be problems using your own view. If you run in to any such problem, the solution is to drop the view and recreate it.

9.2.2 Internal Float and Double Precision Representation (V9.2.1)

From version 8.2, the internal representation of DOUBLE PRECISION and FLOAT has one additional bit. If your original database is from a version before 8.2, old columns using these data types will continue to use the old representation.

9.2.3 Drop Column With Cascade Option (V9.2.1)

In versions older than version 8.2, no information was given regarding which columns in a table that were actually used by a view. Therefore if your original database is from a version before 8.2, it may not be known which columns in a table is used by a view. This implies that when any column is dropped (with the CASCADE option) from such a table in the future, the view will also be dropped even it does not make use of the column.

In versions older than version 8.2, no information was given regarding which columns in a table that were actually used by a view. Therefore if your original database is from a version before 8.2, it may not be known which columns in a table is used by a view. This implies that when any column is dropped (with the CASCADE option) from such a table in the future, the view will also be dropped even it does not make use of the column.


9.3 Corrected Problems

The following sections describe corrected problems.

9.3.1 User-defined Collations (V9.3.8)

During the upgrade process possible existing user-defined collations were not correctly moved to the new version.

9.3.2 Previously Upgraded Database (V9.3.8B)

After a second upgrade (e.g. from version 8.2 via 9.2 to version 9.3) records in table SOURCE_DEFINITION may be missing. In most cases the upgrade process reports a "Dictionary mismatch" error but it is also possible that problems occur first later when running version 9.3. This is now corrected.

9.3.3 Default NULL (V9.2.2)

At upgrade of databases from version 8.1 or earlier, default values explicitly specified as NULL were not handled correctly. Caused error message 'Undefined value found during data type conversion' at describe table. This is now corrected.

9.3.4 Invalid Record Length for Index Tables with Character Varying Columns (V9.1.3)

An error concerning recreation of 'index tables' holding columns with data type VARCHAR has been detected in the upgrade program. If a VARCHAR column is defined as other than the first column participating in the "index table" (INDEX, UNIQUE or FOREIGN KEY) or in the primary key clause, the record length stored in dictionary is not calculated correctly. The error message "Table xxx has invalid record length" is encountered when the base table is to be opened. The error in the upgrade program is now corrected.

If you have an old upgrade program and receive this error message a workaround afterwards is to use DROP INDEX and CREATE INDEX for an ordinary index. If it is a constraint of type unique or foreign key use ALTER TABLE DROP CONSTRAINT and ALTER TABLE ADD CONSTRAINT.


9.4 Known Problems

The following sections describe known problems.

9.4.1 New Reserved Words Used In Stored Module

The upgrade program does not currently solve new version 9.3 reserved words that exists in old stored modules.

If such a word is found a warning is issued. The procedures within the module cannot be used. You have to alter the module before or after the upgrade. (See New Reserved Words (V9.3.1).)

9.4.2 Non-Upgradable Precompiled Statements

Precompiled statements that contain a SELECT *, or an INSERT without column-specification are not upgradable. These statements must be re-created before upgrade can be performed successfully. Instead of

CREATE STATEMENT selst SELECT * FROM sometable

do now specify selected columns:

CREATE STATEMENT selst SELECT column1, column2 FROM sometable

Instead of

CREATE STATEMENT insst INSERT INTO sometable VALUES (?,?)

do now specify inserted columns:

CREATE STATEMENT insst INSERT INTO sometable (column1, column2) VALUES (?,?)

9.4.3 Lost Check Clause From a Dropped Domain

The following problem only exists when upgrading a database, which is created by Mimer SQL versions earlier than 8.2. If any column has inherited a check clause from a dropped domain, this check will be missing after upgrading the database.


9.5 Upgrading Applications

9.5.1 Upgrading from Version 8.2

If the upgrade is from Mimer SQL version 8.2 and your application uses the changed columns of system views, it may require some changes.

9.5.2 Upgrading from Version 7 or Version 8

The new features in Mimer SQL introduced in version 8.2 may require some application changes, depending on how the applications have been written:

  • The cascade/restrict option for DROP of databanks, table, views, and so on has changed. The system now defaults to RESTRICT as the number of dependencies between objects has increased with the introduction of procedures, functions and triggers. If, for example, a table is referenced in one of these objects, the object is dropped when the table is dropped if CASCADE is specified.
  • If an application gives out privileges for backup, shadow, or statistics, the GRANT statement must be changed according to the following:

    • Previously, the right to perform certain commands was controlled by EXECUTE privilege on three system defined PROGRAM idents. These programs have now been replaced by system privileges:

    Old PROGRAM ident
    New privilege
    MIMER_BR

    BACKUP

    MIMER_SC

    STATISTICS

    MIMER_SW

    SHADOW

    The command to grant or revoke the privilege has changed accordingly:

    Old command
    New command
    grant EXECUTE on MIMER_BR to ident
revoke EXECUTE on MIMER_BR from ident

    grant BACKUP to ident
revoke BACKUP from ident

    grant EXECUTE on MIMER_SC to ident
revoke EXECUTE on MIMER_SC from ident

    grant STATISTICS to ident
revoke STATISTICS from ident

    grant EXECUTE on MIMER_SW to ident
revoke EXECUTE on MIMER_SW from ident

    grant SHADOW to ident
revoke SHADOW from ident

  • A number of new reserved words have been introduced. If an application has used any of these words without using double quotes, a syntax error will be given when compiling the SQL statement in question. The work-around is either to use double quotes in all places, or change the name to a non-reserved word. For a full list of reserved words, see Appendix A in Mimer SQL Engine Reference Manual.
  • MIMER views are now deprecated. This means that support for them will be removed in some future version of Mimer SQL Engine. MIMER views are limited to 18 characters in length and do not display information about objects that have names that are longer than 18 characters. The application should use the INFORMATION_SCHEMA views instead.
  • Data definition statements are not automatically committed and can thus be rolled-back or committed. A data definition statement will be implicitly committed when the next statement is executed.
  • The data type for a hexadecimal constant has been changed from character to binary. This means that any such constants must be explicitly casted to character before they can be used in a comparison with a character value or an assignment to a character value.

If upgrading from Mimer SQL version 7, the new features in Mimer SQL Engine may require some application changes, depending on how the applications have been written:

  • From version 8.1, the default for ESQL and LEVEL2 applications is that a transaction reads through its own write-set (CHANGES VISIBLE). If an application, for any reason, used the fact that the default setting was the opposite (CHANGES INVISIBLE) in earlier Mimer SQL Engine versions, the behavior can be regained by using the statement SET TRANSACTION CHANGES INVISIBLE in ESQL or by calling the SETTR2 function in LEVEL2, immediately after connecting to the Mimer SQL Engine database. As there is no performance gain from setting changes invisible; in fact, the opposite is true, and as the feature is deprecated, it is recommended that applications are rewritten so as not to be dependent on this functionality.
  • The introduction of a consistent view of the database and cursor stability may require some changes; for example, if you have an application containing transactions that depend on `seeing' changes caused by other transactions committed during its build-up phase (i.e. when the database operations are performed). In these cases, such transactions may have to be split into several smaller transactions to achieve the same behavior as before.
  • The return code from unconditional DELETE has been changed. This may require changes in applications using WHENEVER NOT FOUND in combination with unconditional DELETE. This means that if a delete statement does not delete any rows, this will be detected by the whenever not found declaration. In earlier versions, the SQLSTATE '00000' was returned for a delete statement that did not delete any rows, in version 8.1 and later the returned SQLSTATE is '20000'.


9.6 Using Mimer SQL Engine V8 Applications with Mimer SQL Engine V9 - UNIX

Mimer SQL version 8 and Mimer SQL Engine version 9 have the same structure and workflow concerning libraries, executables, etc. When starting to use version 9, the main thing is to get access to the version 9 distribution instead of version 8. This can be achieved using two different methods as described below.

Applications using Mimer SQL version 8 are dynamically linked towards Mimer SQL Engine, thus, there is usually no need to re-link them.

These guidelines are also valid if replacing an older v9-installation with a newer one.

9.6.1 Default Access to Mimer SQL Engine V9

When Mimer SQL Engine version 9 is installed, the installation procedure will suggest that files in the distribution are linked to places in the UNIX/Linux file system where they, by default, can be located by various programs. These locations are typically /usr/lib,
/usr/bin, etc.

If you accept this suggestion, which is the default option during the installation procedure, file links will be created. In this case, if another Mimer SQL Engine distribution is already linked this way, for example a Mimer SQL version 8 system, it will be unlinked before the file link operation is completed. This way the old system will be disabled and the default access method will be given to the new Mimer SQL Engine version 9 system.

Note: The operation described here can be executed manually by using the commands mimlink and mimunlink.

9.6.2 Customized Access to Mimer SQL Engine V9

Access to Mimer SQL Engine can also be controlled by using environment variables.

To locate Mimer executables the PATH variable should be defined to include the bin directory of the Mimer SQL Engine version 9 installation path. The position in the pathlist defined for PATH is crucial for a correct search order.

To get the Mimer SQL Engine version 9 shared library loaded when starting an application, the LD_LIBRARY_PATH environment variable must be defined to include the lib directory of the Mimer SQL Engine version 9 installation path.

Note: Again, the position in the pathlist is crucial for a correct search order.

HP-UX: On HP-UX systems, the environment variable used to located shared libraries is SHLIB_PATH.

AIX: On AIX systems, the environment variable LIBPATH is used to locate shared libraries.

For automatic lookup of sequential files used within a Mimer SQL Engine system, the MIMER_PATH environment variable should be defined.

HP-UX: For HP-UX systems, the MIMER_HOME environment variable should be defined to hold the Mimer SQL Engine version 9 installation path.

9.6.3 Client/server Access to Mimer SQL Engine V9

Verify that the mimer definition in the /etc/inetd.conf, or the configuration file for xinetd, uses a version 9 mimtcp program.


9.7 Using MIMER7 Applications with Mimer SQL Engine V9 - UNIX

The following sections describe the specific task of using a MIMER7 application with a Mimer SQL Engine version 9 database server on UNIX.

If a MIMER7 application is to be used with a Mimer SQL Engine version 9 database server, there are three main approaches to getting communication established:

  • Connect to the database using the client/server interface, i.e. from a client node to a Mimer SQL Engine version 9 server node. See Client/server Access.
  • Connect to the database using the client/server interface locally, i.e. the client and server nodes are the same. See Local Client/server Access.
  • Re-link MIMER7 applications to include the shared library for Mimer SQL Engine version 9 and the compatibility library. Re-linking Applications.

9.7.1 Client/server Access

MIMER7 applications can access a Mimer SQL Engine version 9 database server through the client/server interface, i.e. from a client node running MIMER7 to a server node running Mimer SQL Engine version 9, without any modification to the application, licenses, etc.

9.7.2 Local Client/server Access

It is possible to use the client/server interface locally, i.e. have a remote database definition pointing to a local database server. This is the recommended access method when using MIMER7 tools, such as ql and pg, towards a Mimer SQL Engine version 9 database.

To achieve this, the /etc/sqlhosts file must be updated in a specific way using a 6th parameter in the remote definition, as shown in the following example (current node is `startrek', i.e. where the Mimer SQL Engine version 9 database `m9server' runs):

--
-- =============================================================================
DEFAULT:
--
-- Database
-- -----------------------------------------------------------------------------
   m9access
-- =============================================================================
LOCAL:
--
-- Database           Path
-- ------------------ ----------------------------------------------------------
   m9server           /database/m9server:/mnt1/m9server:/mnt2/m9server
-- =============================================================================
REMOTE:
--
-- Database          Node              Protocol Interface Service
-- ----------------- ----------------- -------- --------- ----------------------
   example_database  server_nodename    ''       ''        1360
   m9access          startrek           ''       ''        mimer m9server

To access the `m9server' database, the MIMER7 application must connect to the database `m9access'.

In this case, the `startrek' node must have both a MIMER7 license and a Mimer SQL Engine version 9 license, where the application depends upon /mimkey7 and the database server depends upon /etc/mimerkey, this license must allow client/server access.

9.7.3 Re-linking Applications

For optimal performance, the MIMER7 application should be re-linked.

Mimer SQL Engine version 9 is distributed as a shared library which implies that an application that was previously statically linked must now employ dynamic load. The example makefiles supplied in MIMER7 and Mimer SQL Engine version 9 provide useful information about loader switches, etc.

For an application that depends upon old, low-level modules, such as the runtime parts of MIMER/FM, MIMER/SH and MIMER/PG, the compatibility library compat.a is provided for inclusion.

In the following example `cc' is used to link a MIMER/PG application from version 7. The shared library libmimer.so and the archive compat.a are taken from a Mimer SQL Engine version 9 distribution:

cc  -o pgappl pgappl.o \
/home/mimer7/lib/pgfml.a \
/home/mimer7/lib/pgl.a \
/home/mimer7/lib/fmlib.a \
/usr/lib/compat.a \
/usr/lib/libmimer.so \
-lm -lcurses

Now, when referring to the previous section, the `m9server' database can be accessed directly by the re-linked application.

In this case, only a Mimer SQL Engine version 9 license is needed (/etc/mimerkey), but it must include the necessary licenses for the entire application, e.g. MIMER/PG and MIMER/FM.


9.8 Using Mimer SQL Version 8 Applications with Mimer SQL Version 9 - OpenVMS

See the Mimer SQL Engine Open VMS Guide.


VMS Specific Features

The following sections document new and changed functions, corrected problems and known restrictions and problems regarding the Mimer SQL Engine for OpenVMS.


10.1 New Functions

The following section documents new functions.

10.1.1 Unicode and Long Form Switches (V9.3.4)

Command line arguments do now support long form switches (e.g. --start), and Unicode characters.

The following programs now accept command line switches with a long or a short form:

  • bsql
  • dbc
  • dbopen
  • esql
  • mimcontrol
  • mimload
  • mimversion

The longnames are prefixed by -- while the short switches use a single - (minus) character.

Arguments that contain filename specifications or SQL statements may now contain Unicode characters. (esql does not yet support Unicode arguments.)

Use program --help for more information about the individual arguments for the programs.

bsql example

bsql has a switch to specify the username to use when logging in:

Short form:

bsql -m -u SYSADM -p SYSPSW DB1

Long form:

bsql --multi --username=SYSADM --password=SYSPSW DB1

The command will log in as SYSADM with the password SYSPSW to the database DB1 using multiuser mode.

10.1.2 The TCPCONTROL Command Procedure (V9.2.1)

The TCPCONTROL command procedure can now be used to start, stop and check the MIMTCP processes. Please see the Mimer SQL Engine Open VMS Guide for further information.


10.2 Changed Functions

The following section documents changed functions.

10.2.1 ESQL Command-Line Arguments (V9.3.4)

The ESQL command-line switch /SILENT has been replaced with the switch /NOLOGO.

The command-line switch /OUTPUT has been removed. The output file name is now an optional argument. If specified it should be placed at the end of the command, e.g.

ESQL /C very_long_name.ec v.c

10.2.2 BSQL Command-line Argument (V9.3.4)

The BSQL command-line switch /USER has been replaced with the
switch /USERNAME.

10.2.3 MIMLICENSE Command-line Argument (V9.3.4)

The MIMLICENSE command-line switch /MIMSERV has been replaced with the switch /PERFORMANCE.

10.2.4 MIMLOAD Command-line Argument (V9.3.4)

The MIMLOAD command-line switch /ENTER has been replaced with the switch
/PROGRAM. A new, subsequent switch /USING is used to specify the program ident's password.

10.2.5 MININFO Command-line Argument (V9.3.4)

The MIMINFO command-line switch /SILENT has been replaced with the
switch /NOLOGO.


10.3 Corrected Problems

The following section documents corrected problems.

10.3.1 Login Delay Problem (V9.3.7)

Under heavy load, a central VMS lock used for managing connections could be left in a permanently locked state. When this happened, logging in or out of the server would take a very long time (several seconds). If additional database servers were started on the machine, logging into and out of these servers would not work at all.

This problem has been fixed.

10.3.2 Supported OpenVMS versions (V9.2.3C)

Version 9.2.3B only supported OpenVMS 7.3-2. Version 9.2.3C supports OpenVMS 7.3 and later.

10.3.3 Using MIMINFO with DCL Command Qualifiers (V9.2.3C)

In version 9.2.3B, the MIMINFO command could not be used with DCL-style command qualifiers. Only the UNIX-style command switches was supported. Version 9.2.3C supports both styles.

10.3.4 Error Messages in MIMER.LOG From Disconnected Clients (V9.2.1)

An error message could sometimes be produced on the mimer.log file when a Windows client disconnected its Mimer session. Since this event was harmless, these messages are no longer produced in the log file.

10.3.5 Hang When Waiting for I/O Completion (V9.1.3)

If prefetch of pages was interleaved with exclusive operations on a page, two threads could both be waiting for the completion of the I/O in which case only one succeeded and the other thread caused a hang in the database server. This problem has now been corrected.


10.4 Known Restrictions

The following section documents known restrictions.

10.4.1 SYSGEN Parameter MULTITHREAD (V8.1.1)

The database server makes extensive use of the kernel threads available in OpenVMS version 7. The OpenVMS kernel threads can be disabled by setting the SYSGEN parameter MULTITHREAD to zero. The Mimer SQL Engine database server checks this SYSGEN parameter and will not start if it is zero.


10.5 Known Problems

The following sections document known problems.

10.5.1 The MIMLICENSE and MIMLOAD commands (V9.2.1)

By using the command

$ SET COMMAND MIMLIB9:MIMER

All Mimer commands are defined as DCL verbs, including the commands MIMLICENSE and MIMLOAD. However, DCL only uses the first four characters to distinguish between verbs, so these two commands become ambiguous. You can start both programs by using either verb (or by just abbreviating the verb to MIML). The command definition file uses the (required) options to determine which image to start. So for correctly entered commands, this will not be a problem.

However, if a command is incorrectly entered, or if none of the required command options are specified, it can not be determined which command the user actually wanted to run. Thus, the usage text for both the MIMLOAD and the MIMLICENSE command is displayed.

10.5.2 OpenVMS System Requirements (V9.3.7)

If Mimer SQL Engine version 9.3 is to be used on OpenVMS Integrity, version 8.3 of the operating system, or later, is required.


UNIX Specific Features

The following sections document new and changed functions, corrected problems and known restrictions and problems regarding Mimer SQL Engine for UNIX.


11.1 New Functions

The following sections document new functions.

11.1.1 Unicode and Long Form Switches (V9.3.4)

Command line arguments do now support long form switches (e.g. --start), and Unicode characters.

The following programs now accept command line switches with a long or a short form:

  • bsql
  • dbc
  • dbopen
  • esql
  • mimcontrol
  • mimload
  • mimversion

The longnames are prefixed by -- while the short switches use a single - (minus) character.

Arguments that contain filename specifications or SQL statements may now contain Unicode characters. (esql does not yet support Unicode arguments.)

Use program --help for more information about the individual arguments for the programs.

bsql example

bsql has a switch to specify the username to use when logging in:

Short form:

bsql -m -u SYSADM -p SYSPSW DB1

Long form:

bsql --multi --username=SYSADM --password=SYSPSW DB1

The command will log in as SYSADM with the password SYSPSW to the database DB1 using multiuser mode.

11.1.2 DbVisualizer (V9.2.4F)

The DbVisualizer SQL tool is now bundled with Mimer SQL. For details on DbVisualizer, please see http://www.dbvis.com/products/dbvis/.

DbVisualizer must be started from the File Manager or from the Application Manager in the desktop. If using the File Manager, /usr/local/bin/dbvis-mimer can be opened. In the Application Manager "DbVisualizer (Mimer-SQL)" is found under the Applications tab.

11.1.3 .dumper.sh Script (V9.2.1)

When dbinstall is executed, a .dumper.sh script is generated in the database server home directory. This script is defined as the DumpScript parameter in the multidefs file, which will cause it to be run if the system fails. The .dumper.sh script executes some commands to verify system resource allocation.

11.1.4 New Method For Local Communication (V9.1.4)

The "local" communication method has been redesigned. This communication method is used by Mimer SQL clients that are on the same node as the database server. Previous versions used a pipe to send control messages between the client and the server. The new version will mainly use a System V semaphore to perform the communication; when a client or a thread in the server needs to wait for each other, they can do so by waiting for a semaphore to be set. By using semaphores rather than pipes, applications that communicate much (i.e. don't use array fetches or procedures) can get a significant performance boost.

Whenever a client connects to the database server, the server creates a new System V semaphore set to be used for communication purposes. Since most UNIX systems have a limit on the number of such sets that can be created, this operation can fail. If this happens, and error message will be returned to the client, and a message will also be written in the mimer.log file. The OS error message will contain the text:

semget: [ENOSPC] No space left on device

(This message will only be displayed on the client side if the application is programmed to look up error messages and display them fully.)

The command ipcs can be used to list all semaphore sets in the system. The semaphore sets created by a Mimer SQL server will have a key of 0x00000000 or 0xffffffff (depending on UNIX platform), access mode of 0600 and have 2 semaphores in the set. The semaphore set is created by the user that is running the dbserver process and is owned by the user that runs the client process.

The database server is responsible for removing the semaphores it allocates. Semaphores are removed whenever:

  • A client disconnects normally.
  • When the server detects that a client has disappeared without disconnecting.
  • When the server is shut down.
  • If the server should crash it will remove all used semaphores after it has created a dump directory.
  • When a server is started, it will remove any old semaphores.
  • Whenever the command mimcontrol -k is given.

Note that some UNIX systems have system limits that control the number of semaphores that can be created. These limits include:

SEMMNI

Maximum number of semaphore sets.
One semaphore set is needed per local connection

SEMMNS

Maximum number of semaphores.
Should be twice the value of SEMMNI for Mimer SQL purposes.

SEMMSL

Maximum number of semaphores per semid.
Mimer SQL uses two semaphores in each semaphore set. For Mimer SQL usage, SEMMSL must be at least 2.

Please consult the manuals for your operating system for more information about how these limits are used.


11.2 Changed Functions

The following sections document changed functions.

11.2.1 Added Sudo Support (V9.3.8B)

Some Mimer SQL administrative task must currently be performed by root. Earlier these commands had to be executed by the root user explicitly. Now this is changed so that sudo can be used.

The affected commands are:

dbinstall

Install a Mimer SQL database

dbuninstall

Remove a Mimer SQL database

mimadmin

Manage Mimer SQL databases

mimautoset

Manage Mimer SQL database autostart/autostop

mimdbfiles

Manage files for a Mimer SQL database

mimdbvis

Manage DbVisualizer installation

mimdesktop

Manage Mimer SQL availability in the desktop

miminetd

Manage Mimer SQL adaptation to internet services daemons.

miminstall

Install Mimer SQL

mimlink

Link Mimer SQL installation to system locations, e.g. /usr/bin

mimlistdb

List started Mimer SQL database servers

mimodbcadmin

Administer ODBC drivers and ODBC data sources

mimowner

Manage Mimer SQL database owner

mimuninstall

Uninstall Mimer SQL

mimunlink

Unlink Mimer SQL from system locations

In addition, for this matter the following new commands are added together with corresponding man pages:

mimdbserver

Control a Mimer SQL database, e.g. start/stop

mimodbcdm

Manage iODBC environment

mimsqlhosts

Manage the Mimer SQL database registration file

11.2.2 dbinstall Execution (V9.2.1)

The dbinstall program is now changed so that it can be executed towards an already existing database. If used this way, missing objects for the database can be recreated. For example, if the .dumper.sh script is missing, it will be recreated.

11.2.3 New I/O Primitives Used on Solaris (V9.2.1)

Solaris: Earlier the Solaris Asynchronous I/O package was used, but since there were problems detected (especially when using third party disk management products) the I/O system has been replaced. Now there are dedicated I/O-threads in the Mimer SQL configuration that perform all I/O.


11.3 Corrected Problems

The following sections document corrected problems.

11.3.1 miminfo -u and Crashing Server (V9.2.4)

There has been a problem where a miminfo -u command under rare circumstances could make a database server crash. The problem would only occur on LINUX or Mac OS X. This is now corrected.

11.3.2 Undeleted Files Under /var/tmp (V9.2.1)

When a database server was owned and executed from another user that root, temporary files could be left undeleted on /var/tmp, which could lead to connection attempt failures when filenames were reused. This is now corrected.

11.3.3 Error Messages in mimer.log From Disconnected Clients (V9.2.1)

An error message could sometimes be produced on the mimer.log file when a Windows client disconnected its Mimer session. Since this event was harmless, these messages are no longer produced in the log file.

11.3.4 Connection Handshake Using /var/tmp (V9.2.1)

Tru64: In the operating system Tru64 version 5, the process ID number was extended from 5 digits to 6 digits. Since the process id was part of connection handshake filename (for platforms using Unix Domain Sockets derived from BSD 4.3) that was limited to 14 characters, where /var/tmp/<pid> was used, the connection attempt failed. This is now corrected.

11.3.5 Logging Out Local Connections by mimcontrol -l (V9.2.1)

A list of currently connected users can be obtained by the command:

$ miminfo -u <database>

It is possible to use the mimcontrol command to log out individual connections by specifying the channel number of that connection:

$ mimcontrol -l <channel> <database>

Previous versions of Mimer SQL Engine on UNIX could not log out local connections by using the mimcontrol command. This has now been corrected.

If the connection is executing in the server while it is logged out by mimcontrol, it will receive a cancel notification. This will cause the request to complete earlier and to automatically log out.


11.4 Known Restrictions

The following sections document known restrictions.

11.4.1 Level2 Database API (V8.1.2)

Linux: The old, deprecated Mimer database API, called Level2, is not supported on the Linux platform. If used, an error message will be received.

The error code can be one of the following, depending on if the Level2 module is included in the MRS-key:

-18103 Operation not allowed. MIMER/DB Level2 license required.

-18516 The network server version does not support Level2

Note: When trying to connect from a client of an older version than 8.1, only the code (and not the message) may be obtained.

11.4.2 Using Raw Devices (V8.1.2)

Linux: Use of raw device files for databank storage is not supported on Linux.

HP-UX: See section OS Patch Requirements (V8.1.3).

Solaris: See section OS Patch Requirements (V8.1.3).

11.4.3 Execute Rights for the Directory Chain (V8.1.1)

You must have execute rights for the directory chain down to the database server home directory in order to access the database server. If you do not, you might receive the following error message:

MIMER/DB fatal error -21028 in function CONNECT
          Failed to do a LOCAL connection to the server for database XXX
          OS Error message:
          'connect: [EACCES] Permission denied'


11.5 Known Problems

The following sections document known problems.

11.5.1 RPM (V8.2.2)

Linux: As RPM has control of the files, you may encounter problems if you use RPM to install Mimer SQL Engine and have other Mimer SQL Engine installations on the same node. That is, if the Mimer software you installed with RPM is removed, this may affect other installations on the same node.

11.5.2 OS Patch Requirements (V8.1.3)

HP-UX: IMPORTANT!!!
To be able to use Mimer SQL Engine on HP-UX 11.0, the patch PHNE_20436 needs to be installed. It depends on PHNE_20008, which must also be installed.
When locating databanks on raw device files, i.e. character special, the patch PHKL_18543 needs to be installed. It depends upon the following patches: PHCO_17556, PHCO_17792, PHKL_17038, PHKL_20016, PHKL_20079 and PHKL_20674. These patches must also be installed.
Patches are provided by Hewlett-Packard Co. http://www.hp.com

11.5.3 mimuninstall (V8.1.1)

The mimuninstall command does not remove entries inserted by miminstall in
/etc/profile, /etc/services and /etc/inetd.conf. One reason for not removing these entries is that they may be used by another Mimer SQL Engine installation on the machine.

The file entries should not harm any functions running on the machine.


11.6 Other UNIX Specific Features

The following sections document other UNIX specific features.

11.6.1 Platform Requirements

AIX: CPU type: PowerPC
OS version: AIX 4.3

Linux: CPU-type: Intel (x86, Pentium, etc.)

Linux kernel: 2.4, glibc 2.1.1

HP-UX: CPU type: PA-RISC
OS-version: HP-UX 11.0 (with patches, see section Known Problems).

Solaris: CPU type: UltraSparc

OS-version: Solaris 7

Tru64: CPU-type: Alpha

OS-version: Tru64 Unix V4.0D

11.6.2 OS Setup

On some platforms, it is necessary to enable functionality in the operating system to be able to use Mimer SQL Engine or to use specific Mimer SQL Engine features. These areas are described below.

11.6.2.1 Asynchronous I/O

AIX: Important! To enable asynchronous I/O, which is used by Mimer SQL Engine, the following operations needs to be done from the root account:

# mkdev -l aio

# smit -C aio

Select Change/Show characteristics of Asynchronous I/O. Change the STATE to be configured at system restart to the state available. Update the parameter MAXIMUM number of servers to at least 1000.

11.6.2.2 Using Large Files

AIX: To enable large file support the smit command is used:

# smit -C fs

Select Add / Change / Show / Delete File Systems, Journaled File Systems and then Change / Show Characteristics of a Journaled File System. Choose the file system in question and verify the setting for Large File Enabled, which should be true.
Application programs that are converted to be aware of large files may fail in their attempts to create large files due to the file-size resource limit. The ulimit parameter in /etc/security/limits must be set to -1 for unlimited file sizes.

HP-UX: A file system must be configured to allow large files. This is done by using the sam (System Administration Management) utility, started as follows:

# sam

Select Disks and File Systems and then File Systems. Select the actual file system from the list and choose Modify from the Actions menu where it can be marked that the file system should Allow Large Files.


Windows Specific Features

The following sections document new and changed functions, corrected problems and known restrictions and problems regarding Mimer SQL Engine for Windows.


12.1 Supported Windows Versions

Mimer SQL Engine supports Windows Vista, Windows XP, Windows 2000, and Windows 2003.

The current version is a 32-bit version of Mimer SQL. It can, however, be installed on 64-bit versions of Windows Vista and Windows XP that run on Intel compatible processors.


12.2 New Functions

The following sections document new functions.

12.2.1 Configurable Extend Size (V9.3.8)

It is now possible to control the default extend size when a databank file needs to grow. The size is stored in the registry entry HKEY_LOCAL_MACHINE\SOFTWARE\Mimer\Mimer SQL\9.3\DefaultExtendSize

The size is specified in 2K blocks. It is recommended that the size is a multiple of 32 to allow for less fragmentation of the 64k blocks used internally by Mimer SQL. The default size is 256.

12.2.2 Mimer Administrator Enhancements (V9.3.8)

The Mimer Administrator can now be used to start many of the other utilities provided with Mimer SQL, such as Databank Check, SQL PSM debugger, and Mimer Info. New support for opening an explorer prompt at a database home directory and also support for starting the Windows Event viewer and Performance Monitor has been added.

12.2.3 Export of Shadowing Database (V9.3.8)

It is now supported to export a database that is using shadowing. The shadows are not exported to the device and are removed from the system databank in the process.

12.2.4 SQLConfig Support on Device (V9.3.8)

A new library for calling SQLConfigDriverW, SQLConfigDataSourceW, and SQLInstallerErrorW on the device has been added together with the corresponding link libraries.

12.2.5 Authenticode Signatures (V9.3.7)

The Mimer executables are now signed with a digital certificate. On Windows Vista this is seen when additional privileges are granted through User Account Control prompts.

12.2.6 Expanded Unicode Support (V9.3.5)

Since v9.2.5 it is possible to store database files in a home directory that contains Unicode characters. In addition, descriptions for all types of object (Local database, Remote Database, System Data Source, and User Data Source) may also contain Unicode characters.

From version 9.3.5 filenames may also be specified using Unicode characters. This means that utilities, such as Databank Check, and others that take a databank filename now also support Unicode characters.

12.2.7 Unicode and Long Form Switches (V9.3.4)

The following programs now accept command line switches with a long or a short form:

  • bsql
  • dbc
  • dbopen
  • esql
  • mimcontrol
  • mimload
  • mimversion
  • exloadw
  • mimctrl
  • miminfow
  • pdacopyw
  • sdbgenw

The long names are prefixed by -- while the short switches use a single - (minus) character.

Arguments that contain filename specifications or SQL statements may now contain Unicode characters. (esql does not yet support Unicode arguments.)

Use program --help for more information about the individual arguments for the programs.

bsql example

bsql has a switch to specify the username to use when logging in:

Short form:

bsql -m -u SYSADM -p SYSPSW DB1

Long form:

bsql --multi --username=SYSADM --password=SYSPSW DB1

The command will log in as SYSADM with the password SYSPSW to the database DB1 using multiuser mode.

12.2.8 Running BSQL Without Installing (V9.3.0)

Setting the environment variable MIMER_MODE to the value "STANDALONE" will allow the program bsql.exe to be executed without having installed a Mimer product. The SYSDB databank file (SYSDB93.DBF) must be located in the current directory.

12.2.9 Home Directory Path (V9.2.5)

The home directory for a database may now contain a list of directories separated with semicolons. When the server searches for an unqualified filename (a name with drive and directory specification), the directories in the path are tried in order to find the databank file. When a new file with an unqualified filename is created it is created in the first directory in the path.

12.2.10 Service Descriptions (V9.2.5)

On Windows XP systems the Mimer SQL Database and network services now includes a description that is shown in the Services panel of the Control Panel.

12.2.11 Mimer ODBC Trace (V9.2.2)

Mimer ODBC Trace is a tool for getting the details out of an ODBC session. It can for example be used for debugging, monitoring and analyzing purposes. For each ODBC statement the tool can trace:

  • the elapsed time in server
  • the SQL statement used (if applicable)
  • the ODBC function used
  • the timestamp for the ODBC call
  • the ODBC handle used
  • the calling application process ID and name
  • the return code for the ODBC call

The software consists of two parts, the Mimer ODBC Trace Administration tool and the Mimer ODBC Trace DLL (that substitutes the standard Microsoft ODBC Trace DLL).

The Mimer ODBC Trace Administration tool has two main functions:

  • Configuration - to setup prerequisites for the trace logging and to start/stop logging
  • Presentation - to select and display the records from a logging file.

The Mimer ODBC Trace DLL is activated from the administration tool.

See the Getting Started on Windows guide for a more complete description of Mimer ODBC Trace.

Note: Before using the Mimer ODBC Trace tool, please see the following notes:

  • When changing any settings using the administration tool, you should restart any application for which you want the changes to be used. The reason for this is that the settings will only be read when the DLL is loaded by the application, and thus the DLL has to be reloaded for any changes to take effect.
  • The administration tool changes the registry entries in HKEY_CURRENT_USER\SOFTWARE\ODBC\ODBC.INI\ODBC. If there is any problem look there in order to see if your changes are applied.
  • The Windows default ODBC Administrator may also change those registry entries. Currently there is a problem concerning the cooperating use between these two administration tools. Watch out which DLL is selected, as both administrators may start or stop tracing with anyone of the DLL's.

12.2.12 Automatic Database Server Memory Configuration (V9.2.1)

When a new local database definition is made in the Mimer Administrator, the size of the database server cache, the bufferpool, is now determined by examining the amount of memory present in a machine configuration. There are three different page sizes in the cache: 2K, 16K, and 64K. The 2K buffers are assigned 12.5%, 16K buffers 8.33%, and 64K buffer 5% of the available memory.


12.3 Changed Functions

The following sections document changed functions.

12.3.1 Embedded SQL Support (V9.3.8)

The old interfaces in the ESQL processor which used SQLCA and SQLDA areas to access the database server are no longer supported. This functionality has been marked as deprecated for a period of 15 years.

Applications using SQLDA must switch to the standardized SQL descriptor area. See command ALLOCATE DESCRIPTOR in the SQL Reference.

Applications using SQLCA for error code checking should switch to SQLSTATE, SQLCODE and/or GET DIAGNOSTICS instead. For example references to sqlca.sqlcode is replaced with sqlcode. To access sqlcode it must be declared in the declare section. For example:

exec sql include sqlca;
...
void routine1()
{
...
open cursor1;
if (sqlca.sqlcode < 0) {

is replaced by

void routine1()
{
exec sql begin declare section;
int sqlcode;
exec sql end declare section;
...
open cursor1;
if (sqlcode < 0) {

If an application tries to use SQLDA or SQLCA, the error -18009, "Embedded SQL and MIMER/DB versions are incompatible, please preprocess source code" is returned.

12.3.2 Administrative Privileges (V9.3.7E)

When running programs that require administrative privileges on Windows Vista the user will automatically be prompted for additional privileges when the application is started. This change is made in the Mimer Administrator and the Mimer Controller. These programs require privileges in order to start and stop Mimer database servers.

The mimcontrol command line program also requires privileges. Because of this the program should be used from a command prompt with elevated privileges.

12.3.3 C Runtime Library (V9.3.5)

The Mimer distribution is now built with the C-runtime library that comes with Visual Studio 2005. The libraries are installed in the Mimer SQL installation folder.

12.3.4 Renamed Wizards (V9.3.4)

The wizard sdbgen has been renamed to sdbgenw.

The wizard pdacopy has been renamed to pdacopyw.

The wizard exload has been renamed to exloadw.

Both programs now support a new set of command-line arguments and switches. Use sdbgenw --help and pdacopyw --help for more information about available program arguments.


12.4 Corrected Problems

The following sections document corrected problems.

12.4.1 Mimtrace Manifest (V9.3.8C)

There has been a manifest related problem with the Mimtrace functionality. The problem caused a message box with a Runtime error and prevented logging from taking place. The problem is now corrected.

12.4.2 Long Database Names (V9.3.8)

It is now possible to use database names of maximum length (128 characters).

12.4.3 Uninstalling on Windows Vista (V9.3.8)

When uninstalling Mimer SQL the Windows Installer did sometimes display a message box stating that the files mimtcp.exe and/or mimsrv.exe were locked. This was because there were active Mimer services using these files. This problem has now been corrected.

12.4.4 Output Text File Problem (V9.3.7F)

From v9.3.5 up until v9.3.7E no Mimer tools could create an output text file unless either specifying the full path (.e.g. C:\tmp\logfile.txt), or setting the MIMER_PATH environment variable. The MIMER_PATH defines a path to use for searching and creating Mimer related files. When this situation occurs, a runtime exception was thrown due to an assertion in the Microsoft C Runtime Library.

A couple of examples of situations that triggered this error was:

  • Using the 'log output on <file>' command in BSQL.
  • Specifying an output file in DBC.

This problem is now corrected.

12.4.5 Mimcontrol Hang on Windows Vista (V9.3.7E)

Due to a change in Windows Vista compared to earlier Windows versions the mimcontrol program and the database service did not use the same shared memory objects. This caused the mimcontrol program to hang. This has now been corrected.

12.4.6 Unicode Installation Directory (V9.3.5)

It is now possible to install Mimer SQL in a directory that contains Unicode letters in the filename. Previously, the Performance Monitor component did not install correctly.

In addition, help files could not be viewed due to a bug in the help reader. A workaround for this bug is now in effect which allows help files to be shown from Mimer programs that reference the help file and also via the shortcuts on the start menu. It is not, however, possible to double click on the help files in the installation directory until the Microsoft help reader supports Unicode characters in the help filename.

12.4.7 XA Library Registration (V9.2.5)

When service pack 2 of Windows was introduced it was necessary to register all libraries that can perform distributed XA transactions. This is now done automatically for the Mimer ODBC dynamic link library.

12.4.8 PSM Debugger Shortcut (V9.2.5)

The PSM debugger shortcut shows up properly even if Java is not installed.

12.4.9 Checking SYSDB using Databank Check Utility (V9.2.2E)

The previous 9.2 versions of the Databank Check Utility (dbcw.exe) returned an `File locked by other user' error when trying to check SYSDB.

This problem is now corrected.


12.5 Known Restrictions

The following sections document known restrictions.

12.5.1 Data Sources Names with Unicode Characters (V9.3.8)

It is possible to create data sources that contain Unicode characters. These are not currently accepted by the Mimer ODBC driver.

12.5.2 Windows Firewall (V9.3.8)

In order to access Mimer SQL from a remote computer via the network protocol TCP it is necessary to allow access from the network through the Mimer SQL port 1360. Do the following:

  1. Open the control panel.
  2. Open Windows firewall.
  3. Select the tab named Exceptions.
  4. Press Add Port.
  5. In the dialog enter name Mimer SQL. Set port number to 1360 and use protocol tcp. Press Ok.

If you are using a different firewall perform the equivalent settings in the product you are using.

12.5.3 Java Runtime Notes (V9.2.5)

Several programs in the Mimer SQL distribution, and in particular DbVisualizer, requires a Java runtime version 1.5 or later. When a Java program is invoked a check is made whether Java is installed. If not, the user is asked whether Java runtime should be installed.

For earlier versions of the runtime (1.3) a message box from the Java Virtual Machine Launcher is displayed with the message "Could not find the main class. Program will exit!". If this occurs you should install Java runtime either by double clicking on the file jre-1_5_0_XX-windows-i586-p-iftw.exe (where XX is a number for the current update) in the Mimer SQL installation directory or by downloading J2SE Runtime Environment 5.0 or later from http://java.sun.com.

12.5.4 Distributed Transaction Support and Old Server Versions (V9.1.1)

To use Mimer SQL Engine and distributed transactions, the database server must be version 9.1 or later. Previously, a limited form of transactions was supported with any server version. To retain the limited support, you must use a client older than 9.1.

12.5.5 TCP/IP Connections (V8.2.4)

If TCP/IP is not installed or is disabled for some reason, for example, when a portable computer's network card is not started, then the database server will not start if it is configured to accept TCP/IP connections.

This condition is logged in the Event Log on Windows 2000/XP/Vista and in MIMER.LOG on Windows 98/ME with the socket error code WSAESOCKTNOSUPPORT.

The only way to get the database server started when this happens is to disable TCP/IP for the database with the Mimer Administrator.

12.5.6 Parallel Mimer SQL Versions (V8.1.1)

Only one version of Mimer SQL Mobile and Mimer SQL Engine may be installed at any one time on Windows. Since the Mimer SQL Mobile product contains more functionality than the corresponding version of Mimer SQL Engine, it is always safe to replace a Mimer SQL Engine with a Mimer SQL Mobile.

When installing a newer version, the system will automatically uninstall the previous version. If the upgrade is to another major version, the installer verifies that the user wishes the upgrade to proceed.

Sub-releases of Mimer SQL Engine (e.g. 9.1.1, 9.1.2) are always forward compatible. As no database upgrade is required between sub-releases, it is possible to move between sub-release versions (both forward and backward).

Major releases of Mimer SQL Engine (e.g. 9.1 to 9.2 or 7.3 to 8.1) require a database upgrade. Therefore, it is not possible to use a database with a later version of Mimer SQL Engine and then move back to an earlier version as the upgrade program only allows migration from an older version to a newer version.

To go back to the previous version, you must first uninstall the newer version and then reinstall the old version.


12.6 Known Problems

The following sections document known problems.

12.6.1 Database Service Logon Account (V8.1.2)

Mimer SQL Engine database servers are installed as services in the Windows environment. It is possible to let a service run under a different account by changing the logon account in the startup options dialog for the service in the Control Panel.

When an uninstall is performed, or components are added or removed, the installation program temporarily removes the Mimer SQL Engine services. During this operation, any modified logon account for the service is lost, and must subsequently be reconfigured in the Control Panel by the administrator.

On Windows Vista the option to allow a service to access the desktop is lost when Mimer is reinstalled.


12.7 Deprecated Features

The following sections document deprecated features.

12.7.1 Level2 Database API (V9.3.9)

The deprecated Mimer database API, called Level2, is no longer supported on Windows platforms, not even from Level2 applications running on remote computers.


Mac OS X Specific Features

The following sections document new and changed functions, corrected problems and known restrictions and problems regarding Mimer SQL Engine for Mac OS X.


13.1 New Functions

The following sections document new functions.

13.1.1 The Mac OS X Platform (V9.2.4)

Version 9.2.4 of Mimer SQL Engine is the first version available for the Mac OS X platform.

Currently, if nothing else is stated, the Mimer SQL documentation should be read and used as if the Mac OS X platform is equal to Unix/Linux.

The applications Mimer SQL database Install and Mimer SQL database Admin have both built-in help libraries.

13.1.2 DbVisualizer (V9.2.4D)

The SQL tool DbVisualizer is bundled with Mimer SQL.


13.2 Changed Functions

N/A


13.3 Corrected Problems

N/A


13.4 Known Restrictions

The following sections document known restrictions.

13.4.1 Level2 Database API (V9.2.4)

The deprecated Mimer database API, called Level2, is not supported on the Mac OS X platform. If used, an error message will be received:

MIMER/DB error -2191 in function OPENC2 or OPENT2
         Error code unknown for Level 2
-18516, The network server version does not support Level2

Note:	When trying to connect from a client of an older version than 8.1, only the error codes (and not the message texts) may be obtained.

13.4.2 Using Raw Devices (V9.2.4)

Use of raw device files for databank storage is currently not tested and thus not supported.

13.4.3 Execute Rights for the Directory Chain (V9.2.4)

You must have execute rights for the directory chain down to the database server home directory in order to access the database server. If you do not, you might receive the following error message:

MIMER/DB fatal error -21028 in function CONNECT
         Failed to do a LOCAL connection to the server for database XXX
         OS Error message:
         'connect: [EACCES] Permission denied'


13.5 Known Problems

The following sections document known problems.

13.5.1 mimuninstall

The mimuninstall command does not remove entries inserted by miminstall in
/etc/profile, /etc/services and /etc/inetd.conf.

One reason for not removing these entries is that they may be used by another Mimer SQL Engine installation on the machine.

The file entries should not harm any functions running on the machine.


13.6 Other Mac OS X Specific Features

The following sections document other Mac OS X specific features.

13.6.1 Platform Requirements

Mimer SQL is developed on Mac OS X 10.4, but is also enabled for execution on 10.3 as well. For Mac OS X 10.4, Mimer SQL requires 10.4.3 or later due to operating system bugs.

13.6.2 Remove the Mimer SQL Installation

Currently on Mac OS X there is no built-in uninstall feature in the Installer concept.

To uninstall Mimer SQL start the mimuninstall command from a terminal window. Give the Mimer SQL installation path as argument, e.g.:

# mimuninstall /Applications/MimerSQL-9.2.4D

Then, from Finder, remove the corresponding pkg file, e.g. MimerSQL-9.2.4D.pkg, from /Library/Receipts by moving it to the Trash.


Mimer SQL Engine
Release Notes
Version 9.3.8C

Mimer SQL Engine Release Notes, Version 9.3.8C, April 2008
© Copyright Mimer Information Technology AB.

The contents of this manual may be printed in limited quantities for use at a Mimer SQL installation site. No parts of the manual may be reproduced for sale to a third party.
Information in this document is subject to change without notice. All registered names, product names and trademarks of other companies mentioned in this documentation are used for identification purposes only and are acknowledged as the property of the respective company. Companies, names and data used in examples herein are fictitious unless otherwise noted.

Produced and published by Mimer Information Technology AB, Uppsala, Sweden.
P.O. Box 1713,
SE-751 47 Uppsala, Sweden.
Tel +46(0)18-780 92 00.
Fax +46(0)18-780 92 40.

Mimer Web Sites:
http://www.mimer.com
 http://developer.mimer.com