Compatibility features that are always active

Some compatibility features are always active in the Common IBM® SQL Engine.

The following compatibility features are always active:
  • Implicit casting (weak typing), which reduces the number of SQL statements that you must modify to enable applications to run on an IBM database.
  • New built-in scalar functions. For more information, see Built-in functions.
  • Improvements to the TIMESTAMP_FORMAT and VARCHAR_FORMAT scalar functions. The TIMESTAMP_FORMAT function returns a timestamp for an input string, using a specified format. The VARCHAR_FORMAT function returns a string representation of an input expression that has been formatted according to a specified character template. TO_DATE and TO_TIMESTAMP are synonyms for TIMESTAMP_FORMAT, and TO_CHAR is a synonym for VARCHAR_FORMAT.
  • The lifting of several SQL restrictions, resulting in more compatible syntax between products. For example, the use of correlation names in subqueries and table functions is now optional.
  • Synonyms for syntax that is used by other database products. Examples are as follows:
    • UNIQUE is a synonym for DISTINCT in the column functions and the select list of a query.
    • MINUS is a synonym for the EXCEPT set operator
    • You can use seqname.NEXTVAL in place of the SQL standard syntax NEXT VALUE FOR seqname. You can also use seqname.CURRVAL in place of the SQL standard syntax PREVIOUS VALUE FOR seqname.
  • Global variables, which you can use to easily map package variables, emulate @@nested, @@level or @errorlevel global variables, or pass information from database applications to triggers, functions, or procedures.
  • An ARRAY collection data type that you use to easily map to VARRAY constructs in SQL procedures.
  • Increased identifier length limits.
  • The pseudocolumn ROWID, which you can use to refer to the RID. An unqualified ROWID reference is equivalent to RID_BIT(), and a qualified ROWID reference, such as EMPLOYEE.ROWID, is equivalent to RID_BIT(EMPLOYEE).
  • The DATETIME, INT2, INT4, INT8, FLOAT4, FLOAT8, and BPCHAR data types correspond to the identically named Netezza® data types.
  • Any unresolved and unqualified column reference to the DATASLICEID pseudocolumn is converted to NODENUMBER function and returns the database partition number for a row.
  • Queries can use the outer join operator (+) as alternative syntax within predicates of a WHERE clause.
  • MySQL-compatible and PostgreSQL-compatible LIMIT and OFFSET clauses can be used in fullselect, UPDATE, and DELETE statements.
  • Netezza Platform Software (NPS®) SQL syntax includes a CREATE TEMPORARY TABLE statement. If you need to declare a temporary table, you can use this statement in place of a DECLARE GLOBAL TEMPORARY TABLE statement.