Features introduced in Mimer SQL version 10.1

Introduction

Below, is a summary of new functions and updates currently in Mimer SQL version 10.1. Further details can be found in the Mimer SQL Documentation Set and in the Mimer SQL Release Notes, see Links.

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 11.0 customers are expected to migrate to Mimer SQL Experience. 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:

SELECT p.pid, p.p, p.m, c.cid, c.cn, c.mid
FROM
(
    SELECT partid, part, model, modelid
    FROM parts
    WHERE 
) AS p (pid, p, m, mid)
JOIN
(
    SELECT carid, carname, modelid
    FROM cars
    WHERE carname = 'Volvo' AND model = 'V70'
) AS c (cid ,cn, mid)
    ON p.mid = c.mid;

SELECT *
FROM tabx
WHERE (c1, c2) in (select a, b from taby)

SELECT code
FROM currencies
WHERE code LIKE 'D%'
EXCEPT
SELECT currency_code
FROM countries
WHERE country LIKE 'D%';

SELECT code
FROM currencies
WHERE code LIKE 'D%'
INTERSECT
SELECT currency_code
FROM countries
WHERE country LIKE 'D%';

SELECT * 
FROM sometable
ORDER BY col1
OFFSET 100 ROWS
FETCH 20 FIRST ROWS ONLY;

The following features are currently supported in Mimer SQL Engine but are not yet supported in Mimer SQL Experience. Most of these features will be supported in version 11.0.

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.

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 its 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. Examples:

 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:

 SELECT *
 FROM objects
 WHERE REGEXP_MATCH(object_name,'^P_\d+$')

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:

• Javanese
• Chinese_Pinyin_Name
• Chinese_Zhuyin_Name

BUILTIN.UTC_TIMESTAMP Added

The new function BUILTIN.UTC_TIMESTAMP returns a TIMESTAMP denoting the current Coordinated Universal Time (UTC), as being the exact worldwide time reference. Example:

 SELECT BUILTIN.UTC_TIMESTAMP() AS ReykjavikTime
 FROM sometable;

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. Example:

 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. Example:

 CREATE TRIGGER versionUpdate BEFORE UPDATE ON version
 REFERENCING OLD AS oldVersion NEW AS newVersion FOR EACH ROW
 BEGIN ATOMIC
     IF  oldVersion.versionNumber = newVersion.versionNumber
     OR  newVersion.versionNumber IS NULL THEN
         SET newVersion.versionNumber =
         COALESCE(oldVersion.versionNumber, 0) + 1;
     END IF;
 END

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. Example:

 SELECT *
 FROM tab1
 WHERE blobcol LIKE CAST('%' AS binary(1)) || x'112211' || CAST('%' AS binary(1));

Boolean Expressions Added

Boolean expressions are now supported in both SELECT lists and search conditions. Example:

 SELECT boolcol1 OR boolcol2
 FROM sometable
 WHERE boolcol3;

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. Example:

 CREATE TRIGGER tsttab_upd AFTER UPDATE ON tsttab
 REFERENCING old TABLE AS oldt
             new TABLE AS newt
 begin atomic
     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;
 end

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 decimal.

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
 FROM people
 WHERE LastName = :name
 UNION
 SELECT companyName, Adress
 FROM company
 WHERE companyName = :name

…a  DESCRIBE statement will say that there is only one host variable, whereas in earlier versions it would report that there were 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.

Example:

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:

• ASSYMETRIC
• CALLED
• EXTERNAL
• FIRST
• MEMBER
• NEXT
• OFFSET
• PARAMETER
• ROWS
• SYMMETRIC

The following keyword is no longer reserved:

• CHARACTER

Default Length For Parameter Markers Changed

The default data types for parameter markers have been changed. For a parameter marker used to represent character or national character data, the default length is now 128. Previous limits were 15000 for character and 5000 for nchar.

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.

Examples:

SELECT *
FROM tab1
WHERE col1 LIKE ? || '%'; -- length 128

SELECT *
FROM tab1
WHERE col1 LIKE CAST(? AS varchar(10)) || '%'; -- length 10

SELECT *
FROM tab1
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:

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 infoschem.sql, fipsdoc.sql, and mimerold.sql, respectively.

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.

Databank Check Utility Improvements

The standalone databank check utility now checks the entire contents of each large object (LOB) column. Previously only the starting blocks were verified. In addition, when extended checking is used, the program now checks that LOB record referenced in the data records are also present in the LOB directory.

A new command line option, –extended or -e, causes the utility to perform more extensive validation of the contents of each record. Each column value is validated to check that it conforms to the rules for internal storage representation.

The dbc tool now displays table and index names in the output.

MIMLOAD Output File Encoding

The default output file encoding for the mimload tool has been changed to UTF-8 with BOM on all platforms. Previously, the default encoding on the operating system was used, such as UTF-16 on Windows.