DBMS Server Enhancements
The following DBMS Server enhancements are included:
• DBMS authentication is ON by default.
• Support for Geospatial 3D types have been expanded and many more functions and operations now consider the Z coordinate when making calculations.
• RTREE indexing now handles 3D data types.
• Query optimizer "reuse" heuristic: By default, the query optimizer caches in memory common tables in different fragments of a query and reuses them, which can greatly improve the performance of some queries.
• The MERGE statement allows data to be inserted or updated if the data matches a specified condition.
• CREATE TABLE IF NOT EXISTS creates a table if it does not exist and returns without error if the table exists.
• SET FLOAT_FORMAT format sets floating point output to the specified format, which is useful in a portable copydb operation.
• SET AUTOCOMMIT READ autocommits after a SELECT query.
• SET SCHEMA statement sets the schema for the session and overrides the schema of unqualified objects.
• CREATE TABLE WITH ENCRYPTION sets the table to unlocked so data can be inserted without having to issue the MODIFY...ENCRYPT WITH PASSPHRASE to access it.
• ENABLE/DISABLE SECURITY_AUDIT audit types:
– SEQUENCE logs all types of access by all users to all sequence objects.
– CLIENT_INFO logs the client information string for connecting and disconnecting from the DBMS Server.
• DROP PERMIT ON SEQUENCE seq_name | ALL. Permissions can also be dropped by permission number, for example: DROP PERMIT ON SEQUENCE seq_name 3, 4.
• Support for row value expressions. A row value expression can be used with most comparison predicates and allow for row result comparisons. For example:
...WHERE (empname, empage, salary)=(‘Jones’, 36, 45000)
• Full support for simple CASE operands. In syntax where a common selector is present after the CASE keyword and before the first WHEN, each WHEN clause can now be a list of choices, each of which can be any of the supported operations from the predicate. Examples:
...WHEN 1,2,6,BETWEEN 9 AND 42,>100,IS NULL THEN ...
...WHEN LIKE '%test%',NOT BETWEEN SYMMETRIC 'F' AND 'A',NOT SIMILAR TO '.*' ESCAPE'\'
• COMMENT ON VIEW: The VIEW keyword is added to the COMMENT ON statement syntax.
• IS [NOT] DISTINCT FROM comparison operators
• WHERE clause comparisons of long data types: The operators =, <, >, <= and >= can be applied to the long varchar, long nvarchar, and long byte data types.
• SQL functions:
– Windowing functions LEAD() and LAG()
– SUBSTRING by pattern support using LIKE, SIMILAR TO, BEGINNING or ENDING and can control CASE or DIACRITICAL
– MD5SUM()
– SHA1SUM(), SHA224SUM(), SHA256SUM(), SHA384SUM(), SHA512SUM()
– JARO_WINKLER(), LEVENSHTEIN()
– Soundex functions DMETAPHONE(), SOUNDEX_NYSIIS()
• INQUIRE_SQL (:variable = REMOTE_HOSTNAME) provides an application program with the host name of the connected-to remote server. If the connection is local, it returns an empty string.
• BOOLEAN to INTEGER coercions: INT1(), INT2(), INT4(), INT8(), INT(), and INTEGER() functions accept a Boolean parameter.
• Encryption is allowed for BLOB columns.
• Error log files can be rotated automatically when they exceed a given size by setting the max_log_sz parameter.
• Movedb utility copies data locations from one machine to another, which lets you create a backup copy of the database for disaster recovery purposes.
• New options on rollforwarddb for incrementally applying journals to a backup database. The options -incremental_start, -incremental_continue, -incremental_finish replace the ‑incremental and ‑norollback options, which are deprecated but still supported.
• Verifydb ‑oalltables checks all tables for all users in the database and reports any inconsistencies found, making recommendations to repair the inconsistencies.
• The -filesize option on copydb and COPY splits the output file into multiple parts.
• Time zone files from the Internet Assigned Number Authority (IANA). When installing Ingres, you must select an IANA time zone, which becomes the value assigned to the Ingres environment variable II_TIMEZONE_NAME. For backward compatibility, Ingres time zone names will continue to work, but must be set manually using the ingsetenv utility.
Note: IANA fixed GMT offset time zone names are offset in the opposite direction to the old Ingres fixed offset GMT time zone names. For more information, see GMT Offset in the Installation Guide.
• Y2K38 solution: Internal system catalogs and SQL functions are updated to ensure Ingres continues to operate as expected beyond 03:14:07 UTC on 19 January 2038. The SQL functions _date4(), _date(), date_part(), _bintim() now return INTEGER8 instead of an INTEGER4.
• SET SERVER_TRACE and SET SESSION_TRACE statements allow tracing of all queries processed by the DBMS Server regardless of the source, whether it be an interactive query, or from a JDBC, ODBC, or .NET connection.
• When exiting from the Interactive Terminal Monitor, the message "Are you sure you wish to quit (y/n)?" displays when II_ITM_QUITPROMPT environment variable is set to ON or TRUE.
• In terminal monitor output, column widths respect the length of the column heading (column headings that exceed the required display width of a given column are no longer truncated) and date types are aligned.
• UUID data type and functions: Automatic generation of UUID identifiers for inserting data. A UUID can be used as a primary key and/or as a partition key to ensure that data is spread evenly across nodes.
• Clonedb utility, which lets you copy a database from one instance to another, for example, from one installation, machine, or cluster to another. Clonedb can be used to clone a production database for testing purposes.
• Transliteration between UTF8 and national characters sets.