New Features in Version 3.0
The following features were introduced in Vectorwise 3.0.
• ROLLUP, CUBE, and GROUPING SETS--The following analytical SQL extensions to the GROUP BY clause are supported: ROLLUP, CUBE, GROUPING SETS, GROUPING() function.
• LAG and LEAD analytical functions, which are useful for comparing values across multiple rows, for example across time periods. They compare values in the current row with values in a previous or following row.
• LISTAGG() aggregate function, which concatenates a set of string values for the group into one row. The configuration parameter [engine] listagg_group_maxlen lets you change the maximum length of the result.
• BOOLEAN data type--BOOLEAN can be used as a data type when defining a column in a table. Boolean columns and variables accept as input the SQL literals FALSE and TRUE and the strings 'FALSE' and 'TRUE'. The IS Boolean operator can be used in expressions. ORDER BY BOOLEAN results in grouping rows in this order: FALSE, TRUE, NULL. The CREATE INDEX statement allows an index to be created on BOOLEAN columns. CASE expressions can be used with BOOLEAN columns or literals. The CAST operator supports casting BOOLEAN to and from character types and from the integer values 0 and 1.
For more information, see these sections of the Vector SQL Language Guide: BOOLEAN Data Type, Storage Formats of Data Types, BOOLEAN Literals, Data Type Conversion Functions, IS TRUE, IS FALSE, IS UNKNOWN Predicates, Default Clause.
• Time zone support--Time-related data types now support time zones.
The following data types are supported:
– TIME WITH TIME ZONE
– TIME WITH LOCAL TIME ZONE
– TIMESTAMP WITH TIME ZONE
– TIMESTAMP WITH LOCAL TIME ZONE
The following time-related SQL functions are supported:
– TIME_LOCAL()
– TIME_WITH_TZ()
– TIMESTAMP_LOCAL()
– TIMESTAMP_WITH_TZ()
– EXTRACT(TIMEZONE_HOUR...) and EXTRACT(TIMEZONE_MINUTE...)
Maximum allowed scale for existing TIMESTAMP and INTERVAL DAY TO SECOND types is extended to 9 (nanoseconds).
• New date and time functions TIMESTAMPADD, TIMESTAMPDIFF, INTERVAL_DIFF.
• Horizontal partitioning--A Vector table or single column can be stored in multiple storage locations, a feature known as horizontal partitioning. For more information, see
Horizontal Partitioning.
• DDL improvements--The following improvements have been made to the SQL data definition language (DDL):
– Truncate tables
– All rows can be removed from a table and its indexes by using the following statement:
MODIFY table_name TO TRUNCATED
– Rename tables and columns
A table can be renamed using one of the following statements:
RENAME TABLE [schema.]table_name TO new_table_name
ALTER TABLE [schema.]table_name RENAME TO new_table_name
A column can be renamed using the following statement:
ALTER TABLE [schema.]table_name RENAME [COLUMN] old_column_name TO new_column_name
– ADD CONSTRAINT on a non-empty table. The restriction that a constraint can be added only to an empty table is lifted. To add a constraint, use the ALTER TABLE...ADD CONSTRAINT statement.
– CREATE INDEX on a non-empty table. The restriction that an index can be created only on an empty table is lifted.
For more information, see the Vector SQL Language Guide.
• Per-query parallelism level--An explicit parallelism level can be set for a single query to test its effect. Use the following syntax: SELECT...WITH MAX_PARALLEL n
• Query profiling views--Predefined views are available that let you easily access information on the execution of recent queries. For details, see
View Query Profile Information.
• Asynchronous gather I/O support, which improves the speed of write operations such as loading, sorting, and spilling to disk. For more information, see
Asynchronous Gather I/O.
• Compression improvements--The LZ4 compression algorithm for string data is added. The configuration parameter [cbm] compression_lz4_enabled turns this type of compression on or off. Decimals with precision greater than 18 can be compressed using PFOR, PFOR-DELTA, and PDICT algorithms. For details, see
Data Type Storage Format and Compression Type.
• vwload improvements--The vwload -d option is enhanced to let you specify custom date formats. Support for delimited identifiers is also added, which lets you specify any character in the date format string. In addition, there are new options: --frequency and --timing. For more information, see
vwload Command--Load Data into a Table.
• Authentication at the DBMS level is allowed in addition to the other supported methods. The DBMS authentication feature removes the need to add an operating system user every time a new user needs to access a database. For more information, see DBMS Authentication in the Security Guide.
• New rules for decimal arithmetic. The decimal_rule parameter in config.dat determines the set of rules used for handling decimals. Valid settings are Standard and Classic. New installations use the Standard rules. If the parameter is missing, Classic is used. The new Standard rules have revised precision-overflow handling and a revised division rule. For more information, see Arithmetic Operations on Decimal Data Types in the Vector SQL Language Guide.
• Connectivity enhancements:
– DBMS authentication--A user can connect to a database without having to be defined as an operating system user.
– Session information added to GCC log messages. Session information, including the user ID, is now logged to the Ingres error log (errlog.log). This helps identify problems in client installations. Non-GCC errors are no longer logged by default. Such logging can be re-enabled by setting the GCC logging level to 5. The GCC error_level configuration option is deprecated (ignored).
– ODBC driver explicit batch execution--The Ingres ODBC Driver allows sets of queries to be defined and executed in an application. In explicit batch execution, a set of create, delete, or update queries are chained together in a single SQLExecDirect() statement.
– Java 7 support--The Ingres JDBC Driver supports new methods in the Java 7 API and JDBC 4.1 Specification.
– Java logging support--The Ingres JDBC Driver supports output of driver tracing through the Java Logging package (java.util.logging).
– GCC extended transliteration--The Communications Server transliterates string connection parameters to and from UTF8 and other character sets.
– Obsolete network protocol drivers are removed. The default network protocol is tcp_ip. For details, see the readme.