Print this page.  If your browser doesn't allow JavaScript, right-click this page and choose Print from the popup-menu.        
News in Mimer SQL version 10.1
Category: Database features
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 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 ExperienceExplanation
Advanced optimizationsMany 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 listThis 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 clauseThis 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
FROM
(SELECT partid, part, model
FROM parts
WHERE model='V70')
AS p(p1,p2,p3)
JOIN
(SELECT carid, carname
FROM cars
WHERE carname ='Volvo')
AS c(c1,c2)
ON c.c1 is not null AND p.p1 is not null;

Row expressionsRow expressions are a sequence of values that together form a row. For example:

SELECT *
FROM tabx
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.
EXCEPTThe 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:

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

INTERSECTThe 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':

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

FETCH FIRST and OFFSETThe 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.):

SELECT *
FROM sometable
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-selectWith 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 ExperienceExplanation
User-defined types and methodsUser-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.
SpatialThe 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 statement support for new select constructionsThe 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 EngineExplanation
Cursor read-only by defaultIn 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_TIMESTAMPCURRENT_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 operatorsChange operators as follows:

Concatenation:
// change to ||
!! change to ||

Not equal:
/= change to <>
^= change to <>

Greater/less than:
^> 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_LENGTHThe 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, DTANA 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.

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 supportedin both SELECTlists 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 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.

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:

Old column nameNew column name
CONDITION_TIMINGACTION_TIMING
CONDITION_REFERENCE_OLD_TABLEACTION_REFERENCE_OLD_TABLE
CONDITION_REFERENCE_NEW_TABLEACTION_REFERENCE_NEW_TABLE
CONDITION_REFERENCE_OLD_ROWACTION_REFERENCE_OLD_ROW
CONDITION_REFERENCE_NEW_ROW ACTION_REFERENCE_NEW_ROW



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.

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.


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

For further reading about what has happened in Mimer SQL 10.1, please see the Mimer SQL Release Notes provided with each product package.

Also see the Mimer SQL Documentation Set (HTML), or Mimer SQL Documentation Set (pdf).

Last updated: 2013-12-12

 

Powered by Mimer SQL

Powered by Mimer SQL