The DB2® product provides a number of features that
reduce the time and complexity of enabling some applications that
were written for relational database products other than the DB2 product
to run on a DB2 system.
Some of these features, including the following ones,
are enabled by default.
- Implicit casting (weak typing), which reduces
the number of SQL statements that you must modify to enable applications
to run on the DB2 product.
- New built-in scalar functions. For
details, 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 DB2 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).
You can optionally enable the following
other features by setting the DB2_COMPATIBILITY_VECTOR registry variable. These features are disabled by default.
- An implementation of hierarchical queries using CONNECT BY PRIOR
syntax.
- Support for outer joins using the outer join operator, which is
the plus sign (+).
- Use of the DATE data type as TIMESTAMP(0), a combined date and
time value.
- Syntax and semantics to support the NUMBER data type.
- Syntax and semantics to support the VARCHAR2 data type.
- The ROWNUM pseudocolumn, which is a synonym for ROW_NUMBER() OVER().
However, the ROWNUM pseudocolumn is allowed in the SELECT list and
in the WHERE clause of the SELECT statement.
- A dummy table named DUAL, which provides a capability that is
similar to that of the SYSIBM.SYSDUMMY1 table.
- Alternative semantics for the TRUNCATE statement,
such that IMMEDIATE is an optional keyword that is assumed to be the
default if not specified. An implicit commit operation is performed
before the TRUNCATE statement executes if the TRUNCATE statement is
not the first statement in the logical unit of work.
- Support for assigning the CHAR or GRAPHIC data type instead of
the VARCHAR or VARGRAPHIC data type to character and graphic string
constants whose byte lengths are less than or equal to 254.
- Use of collection methods to perform operations on arrays, such
as FIRST, LAST, NEXT, and previous.
- Support for creating Oracle data dictionary-compatible views.
- Support for compiling and executing PL/SQL statements and other
language elements.
- Support for making cursors
insensitive to subsequent statements by materializing the cursors
at OPEN time.
- Support for INOUT parameters in procedures that
you define with defaults and can invoke without specifying the arguments
for those parameters.
DB2 10.5 for Linux, UNIX, and Windows