DB2® Version 9.5 introduced
a number of features that greatly simplify the task of enabling some
applications written for different relational database products to
run on DB2. DB2 Version 9.7 introduces additional features
that reduce this complexity and the time required to enable existing
applications even further.
Some of these features, including the following, are enabled
by default.
- Implicit casting (weak typing), which reduces
the amount of SQL that needs to be modified when applications that
currently run on other data servers are enabled for DB2
- New scalar functions. For details, see "Supported functions
and administrative SQL routines and views".
- Major improvements to the TIMESTAMP_FORMAT and VARCHAR_FORMAT
scalar functions. (TO_DATE and TO_TIMESTAMP are synonyms for TIMESTAMP_FORMAT,
and TO_CHAR is a synonym for VARCHAR_FORMAT.)
- TIMESTAMP_FORMAT - This function returns a timestamp that is based
on the interpretation of the input string using the specified format.
- VARCHAR_FORMAT - This function returns a string representation
of an input expression that has been formatted according to a specified
character template.
- The lifting of several SQL language restrictions, resulting in
compatible syntax between products; for example, the use of correlation
names in subqueries and table functions is now optional
- Synonyms for syntax used by other database products; for example:
- UNIQUE is a synonym for DISTINCT in the context of column functions
and the select list of a query
- MINUS is a synonym for the EXCEPT set operator
- seqname.NEXTVAL and seqname.CURRVAL
can be used in place of the SQL standard syntax NEXT VALUE FOR seqname and
PREVIOUS VALUE FOR seqname
- Global variables, which can be used to easily map package variables,
emulate @@nested, @@level, or @errorlevel global variables, or pass
information from DB2 applications
down to triggers, functions, or procedures
- An ARRAY collection data type that can be used to easily map to
VARRAY constructs in SQL procedures
- Increased identifier length limits that facilitate the enablement
of applications from other DBMS vendors on DB2
- The pseudocolumn ROWID that can be used to refer to the RID; an
unqualified ROWID reference is equivalent to RID_BIT() and a qualified
ROWID, such as EMPLOYEE.ROWID, is equivalent to RID_BIT(EMPLOYEE)
Other features can be selectively enabled by setting a new DB2 registry variable named DB2_COMPATIBILITY_VECTOR.
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, (+)
- 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
- A pseudocolumn named ROWNUM is a synonym for ROW_NUMBER() OVER(),
but ROWNUM is allowed in the SELECT LIST and in the WHERE clause
- A dummy table named DUAL provides a capability that is similar
to SYSIBM.SYSDUMMY1
- Alternate semantics for the TRUNCATE TABLE statement,
under which 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 assignment of the CHAR or GRAPHIC data type (instead
of the VARCHAR or VARGRAPHIC data type) to character and graphic string
constants whose byte length is less than or equal to 254
- Use of collection methods to perform operations on arrays, such
as first, last, next, and previous
- Support for the creation of Oracle data dictionary-compatible
views
- Support for the compilation and execution of PL/SQL statements
and language elements
- Support for making cursors insensitive
to subsequent statements by materializing the cursor at OPEN time.
- Support for INOUT parameters in procedures
that are defined with defaults and can be invoked without specifying
the arguments for those parameters.
DB2 Version 9.7 for Linux, UNIX, and Windows