News in Mimer SQL version 10.1
Below, is a summary of new functions and updates currently in Mimer SQL version 10.1 is. Further details can be found in the Mimer SQL Documentation Set and in the Mimer SQL Release Notes.
Mimer SQL 10.1 News Summary
Database Server Types
In Mimer SQL version 10.1 there are two types of database servers included. They are called Mimer SQL Engine and Mimer SQL Experience.
- Mimer SQL Engine is a continuation of the long line of database servers from Mimer Information Technology. It preserves backward compatibility and is intended for production systems that are upgraded from earlier versions of Mimer SQL.
- Mimer SQL Experience is a new product and has an SQL compiler that has been developed with many additional optimization techniques and rich SQL language support.
The two SQL compilers have been developed in parallel. They both use a common execution engine and database kernel. This means that databases created with one server type may be used with the other. I.e. it is possible to try applications with the new server simply by changing server type. This is done with the Mimer Administrator on the Windows platform. On other platforms this information is stored in the
multidefs configuration file for the database server.
In version 10.1 existing customers can migrate their applications with retained compatibility to Mimer SQL Engine. Starting with version 10.2 customers are expected to migrate to Mimer SQL Experience as this is the product that will get all new functionality in coming versions. It is a good idea to test your applications with Mimer SQL Experience in version 10.1 even if a migration is not yet planned.
The two products are, from a user point of view, very similar. Here follows the main differences:
|Functionality only in Mimer SQL Experience||Explanation|
|Advanced optimizations||Many new techniques are used in the SQL optimizer. This allows complex queries to be executed significantly faster than with Mimer SQL Engine.|
Other enhancements are the techniques for reorganizing complex queries and views. This allows queries to be optimized in ways that were previously not possible.
|Select in from list||This allows queries in queries. This is a very powerful construction, which makes it much easier to construct complex SQL statements.|
The Mimer SQL Experience server has advanced optimization techniques to allow these nested queries to run quickly. This also allows for advanced optimization when views are used.
|Rename columns in from clause||This allows naming of columns in a from-list select, as in the following example:|
SELECT p.p1, p.p2, p.p3, c.c1, c.c2
(SELECT partid, part, model
(SELECT carid, carname
WHERE carname ='Volvo')
ON c.c1 is not null AND p.p1 is not null;
|Row expressions||Row expressions are a sequence of values that together form a row. For example:|
WHERE (c1, c2) in (select a, b from taby)
In the example the combination of columns c1 and c2 are matched against the rows returned by the subselect.
|EXCEPT||The EXCEPT set operator is used to combine two result sets to one where the combined result set is all records from the first result which is not present in the second result set.|
The following example will select codes from the currencies table, except those that also are found in countries, where the code starts with the letter 'D', and the country does not:
WHERE code LIKE 'D%'
WHERE country LIKE 'D%';
|INTERSECT||The INTERSECT set operator is used to combine two result sets to one where the combined result set is the records that are present in both result sets.|
The following example will select the codes from currencies and countries that exist in both tables, and start with the letter 'D':
WHERE code LIKE 'D%'
WHERE country LIKE 'D%';
|FETCH FIRST and OFFSET||The FETCH FIRST add OFFSET clauses are used to limit the number of rows returned to the application. These can be present both in a subselect and in the top level select. The OFFSET clause is used to skip the first rows of the result set, and the FETCH FIRST clause is used to specify the number of rows to be returned.|
The following example shall return row number 101 to 120. (I.e. first order the result set by col1, then skip the first 100 rows of the result, and finally return the following 20 rows.):
ORDER BY col1
OFFSET 100 ROWS
FETCH 20 FIRST ROWS ONLY;
(Mimer SQL Engine has a limited support for top level specified FETCH FIRST and OFFSET, and where values must be given as integer literals or host-variable values.)
|ORDER BY in sub-select||With the Mimer SQL Experience database server, the ORDER BY clause can be used in a sub-select.|
The following features are currently supported in Mimer SQL Engine but are not yet supported in Mimer SQL Experience. All these features will be supported in version 10.2.
|Functionality NOT yet in Mimer SQL Experience||Explanation|
|User-defined types and methods||User-defined data types (UDT) is a new feature introduced in version 10.1 in Mimer SQL Engine. With user-defined types, it is possible to create new data types that can be used in table definitions and stored procedures. In addition, it is possible to define methods for a user-defined type.|
|Spatial||The spatial data types are used to store and index coordinates in the database. The spatial data types are based on the user-defined types framework, and the pre-defined |
BUILTIN schema contains user-defined types and methods used to store and search positional data in an efficient manner.
There are two basic groups of spatial data: Geographical data, i.e. positions on the Earth's surface, and Coordinate system data, i.e. positions in a two-dimensional plane.
|Create view support for new select constructions||The grammar for CREATE VIEW is based on the Mimer SQL Engine SQL syntax. This means that new constructions such as INTERSECT and FROM-list queries may not be used in views yet.|
|Create statement support for new select constructions||The grammar for CREATE statement is based on the Mimer SQL Engine SQL syntax. This means that new constructions such as INTERSECT and FROM-list queries may not be used in executable statements yet.|
There are a few incompatibilities that must be considered when upgrading from Mimer SQL Engine to Mimer SQL Experience. These incompatibilities will remain in future versions.
|Incompatibilities between Mimer SQL Experience and Mimer SQL Engine||Explanation|
|Cursor read-only by default||In Mimer SQL Experience all select statements are by default read only. This means that they cannot be used with update and delete where current unless a FOR UPDATE clause is added to the select statement. The reason for this change is that it allows auto committed statements to always run as read-only transactions.|
|Strict typing for CURRENT_TIMESTAMP||CURRENT_TIMESTAMP is a representation of the current date and time with time zone.|
LOCALTIMESTAMP is a representation of the current date and time but without a time zone. In Mimer SQL Engine these were treated as equal. In Mimer SQL Experience, however, they are (correctly) considered separate types. This will allow a future introduction of time zone support to be seamless.
Any reference to CURRENT_TIMESTAMP should be changed to LOCALTIMESTAMP. This change will work correctly in both products.
|Non-standard operators||Change operators as follows:|
// change to
!! change to
/= change to
^= change to
^> change to
^< change to
In all these cases there exist SQL standard operators that should be used. The old operators have been deprecated for many years.
|Built-in function: BIT_LENGTH||The BIT_LENGTH function has been removed from the SQL standard. It is not supported in Mimer SQL Experience.|
|Built-in float functions: DACOS, DASIN, DATAN, DATAN2, DCOS, DCOT, DDEGREES, DEXP, DLOG, DLOG10, DPOWER, DRADIANS, DSIN, DSQRT, DTAN||A number of floating point functions are not supported in Mimer SQL Experience.|
INDEX_CHAR Function Added
The new INDEX_CHAR function takes a character string as argument and returns the index character for the string related to it's collation. The default behavior is to return the first letter of the string, decomposed (accents removed) and capitalized (upper case).
However, many languages include accented letters, digraphs, and sometimes trigraphs as basic alphabetical characters, which are all properly handled by the INDEX_CHAR function.
SELECT INDEX_CHAR('östra aros' COLLATE english_1) FROM... -- will return 'O'
SELECT INDEX_CHAR('östra aros' COLLATE swedish_1) FROM... -- will return 'Ö'
Regular Expressions Added
Support for using regular expressions in search criterias has been added. This is achieved by using the boolean function
REGEXP_MATCH. This function will take two string value expressions as arguments, where the first is the string value to be searched and the second is the regular expression. The
REGEXP_MATCH function will return TRUE if the first argument matches the regular expression and FALSE if it does not match. If either argument is the NULL value, NULL is returned.
The following example will find all instances where an
object_name starts with the characters
P_ followed by one or more digits:
Regular expressions offers a more powerful set of pattern matching capabilities than the LIKE predicate. For more details, see the Mimer SQL Reference Manual.
New Collations Added
Collations for the following languages have been added to Mimer SQL 10.1:
The new function BUILTIN.UTC_TIMESTAMP returns a TIMESTAMP denoting the current Coordinated Universal Time (UTC), as being the exact worldwide time reference.
SELECT BUILTIN.UTC_TIMESTAMP() AS ReykjavikTime
Providing User Specific Data In Exception Handling Allowed
The SIGNAL and RESIGNAL statements in PSM now have an optional clause for providing specific data that can be retrieved by the client that handles the raised exception.
SIGNAL sqlstate 'UE432'
SET message_text = n'The specified parent ' || parent ||
' does not exist in the database';
For a complete list of which diagnostic items that can be set, refer to the description of the SIGNAL and RESIGNAL statements in the Mimer SQL Reference Manual.
Before Row Triggers Added
A row trigger is a trigger that will be executed once for each row affected by a data manipulation statement.
Within the trigger it is possible to access the affected row using the old and new variables. The old variable is available if the trigger event is DELETE or UPDATE, while the new variable is available if the trigger event is INSERT or UPDATE. It is possible to modify values in the new variable.
CREATE TRIGGER versionUpdate BEFORE UPDATE ON version
REFERENCING OLD AS oldVersion NEW AS newVersion FOR EACH ROW
IF oldVersion.versionNumber = newVersion.versionNumber
OR newVersion.versionNumber IS NULL THEN
SET newVersion.versionNumber =
COALESCE(oldVersion.versionNumber,0) + 1;
The syntax for the CREATE TRIGGER statement is described in the Mimer SQL Reference Manual.
LIKE Predicate for BLOB Columns Added
The LIKE predicate can now be used to search the contents of columns with data type BINARY LARGE OBJECT.
WHERE blobcol LIKE CAST('%' AS binary(1)) || x'112211' ||
CAST('%' AS binary(1));
Boolean Expressions Added
Boolean expressions are now supportedin both SELECTlists and search conditions.
SELECT boolcol1 OR boolcol2
OLD/NEW Table Order Change
The rows in the old and new tables for a trigger are now sorted in the same order, instead of in primary key order. This means that if old table data and new table data are fetched in parallel, the corresponding rows will be read even if the primary key has been updated.
As a result of this changed behavior, the performance when using the primary key columns when joining old and new tables has significantly decreased, especially for large old/new tables. Use the MIMER_ROWID pseudo-key to retain performance.
CREATE TRIGGER tsttab_upd AFTER UPDATE ON tsttab
REFERENCING old TABLE AS oldt
new TABLE AS newt
INSERT INTO logtable
SELECT localtimestamp, 'UPDATE', oldt.mimer_rowid,
oldt.col1, oldt.col2, ...
newt.col1, newt.col2, ...
FROM oldt, newt
WHERE oldt.mimer_rowid = newt.mimer_rowid;
|Note! Mimer SQL Engine|
|Mimer SQL Engine does currently only support mimer_rowid in triggers for tables declared without a primary key.|
EXTRACT Return Data Type Change
The EXTRACT function does now return
integer for all field types except SECOND. When SECOND is specified, the return data type is
Change concerning Named Parameters In Dynamic SQL
If an SQL query contains multiple instances of a host variable, this is considered to be one and the same host variable. In earlier versions of Mimer SQL this was treated as different host variables.
For example, given the statement...
SELECT LastName as Name, Adress
WHERE LastName = :name
SELECT companyName, Adress
WHERE companyName = :name
...a <describe input statement> will say that there is one host variable whereas in earlier versions it would report that there are two variables. This will have the implication that the same host variable name can not be used in contexts where the implied data types of the variables are incompatible.
Sequences With Support For Large Values Added
The values for a sequence in earlier versions of Mimer SQL was limited by the values possible to store in a 32-bit integer. These limits have now been extended as it is possible to use 64-bit integer. The syntax for creating sequences has also been modified to comply with the SQL-2008 standard specification. The previous syntax for creating sequences is still supported but it is recommended that the new syntax is used.
CREATE SEQUENCE bigseq AS bigint NO CYCLE
This statement will create a sequence that will generate unique values between 1 and 9223372036854775807.
For more details, see the section on CREATE SEQUENCE in the Mimer SQL Reference Manual.
Updates concerning Reserved Words
The following keywords are now reserved:
The following keyword is no longer reserved:
Default Length For Parameter Markers Changed
The default data types for parameter markers have been changed. For a parameter marker used to represent
national character data, the default length is now 128. Previous limits were 15000 for
character and 5000 for
CAST can be used to specify other lengths, if desired.
As before, when a parameter marker is used to represent data assigned to a column, the length is in accordance with the column definition.
WHERE col1 LIKE ? || '%'; -- length 128
WHERE col1 LIKE CAST(? AS varchar(10)) || '%'; -- length 10
WHERE col1 = ?; -- col1’s length
Unicode Identifiers Allowed
Identifiers for created objects, like tables and views, can now include Unicode letters.
Information Schema Column Name Changes
The INFORMATION_SCHEMA.TRIGGER columns have been renamed according to the following table:
|Old column name||New column name|
System Databank Views Changes
To decrease the size of the system databank SYSDB and the time it takes to create it, a number of changes have been made:
- Views owned by schema INFO_SCHEM are no longer defined.
- Views owned by schema FIPS_DOCUMENTATION are no longer defined.
- The MIMER schema is now owned by SYSADM. The schema only contains support views for ODBC/JDBC. The views in the MIMER schema, available for backward compatibility (Level2, QL and other old Mimer modules), are no longer defined.
The views that have been removed are possible to add to a system databank by logging in as SYSADM in BSQL and executing the SQL scripts
If the ODBC/JDBC views needs to be recreated a BSQL script file called
mimerodbc.sql is also included.
When upgrading from earlier versions all the above views are retained in the upgraded system databank with the change of schema owner to be SYSADM.
Last updated: 2013-04-23