Functionality from the DB2 UDB SQL Reference for Cross-Platform Development not supported by Derby

Some of the functionality described in the IBM® DB2® Universal Database™ (DB2 UDB) SQL Reference for Cross-Platform Development Version 1.1 is not supported by Derby. This article lists these differences. This comparison was developed by reviewing the DB2 UDB SQL Reference for Cross-Platform Development Version 1.1 documentation against Derby functionality.

Mark Chiang, Software Engineer, IBM

Mark Chiang is a Software Engineer on the Cloudscape Development team.



Daniel Debrunner (debrunne@us.ibm.com(IBM), djd@debrunners.com(Apache Derby)), STSM - IBM Data Management, IBM

Daniel Debrunner is a Senior Technical Staff Member with IBM's Data Management division in San Francisco, California. For the past eight years he has been the architect for the Cloudscape database engine, guiding the technology from a startup company through two acquisitions to wide deployment in IBM's products and middleware. Now he is looking forward to being a participant in the Apache open source community that will drive Derby. Daniel has worked on the internals of a number of additional database engines at Sybase, Illustra and Informix. Prior to coming to the United States Daniel worked for a London based Unix systems company and received a MA in Physics from the University of Oxford.



09 September 2004

IBM DB2 UDB SQL Reference for Cross-Platform Development

The IBM DB2 UDB SQL Reference for Cross-Platform Development documents a subset of common functionality that is available in the DB2 UDB Family (DB2 UDB for z/OS and OS/390 Version 7, DB2 UDB for iSeries™ Version 5 Release 2, and DB2 UDB for Linux, UNIX®, and Windows® Version 8.1). Please refer to the DB2 UDB SQL Reference for Cross-Platform Development and Derby documentation for more information on features and behavioral differences.

To download the DB2 UDB SQL Reference for Cross-Platform Development Version 1.1 visit ftp://ftp.software.ibm.com/ps/products/db2/info/vr8/pdf/letter/cpsqlrv11.pdf


Data types not supported by Derby

Below is a listing of the Data-types Derby does not support which DB2 UDB v 8.1 does support. Data-types not listed below are completely supported by Derby.

GRAPHIC
VARGRAPHIC
DBCLOB
Distinct-types

Built-in functions not supported by Derby

Below is a listing of the Built-in functions Derby does not support which DB2 UDB v 8.1 does support. Built-in functions not listed below are completely supported by Derby.

ACOS
ASIN
ATAN
ATANH
ATAN2
BLOB
CEILING or CEIL
CHAR
datetime-expression, ISO
datetime-expression, USA
datetime-expression, EUR
datetime-expression, JIS
decimal-expression, decimal-character
floating-point-expression, decimal-character
CLOB
COS
COSH
COUNT_BIG
DAYOFWEEK
DAYOFWEEK_ISO
DAYOFYEAR
DAYS
DBCLOB
DECIMAL or DEC
DEGREES
DIGITS
DOUBLE_PRECISION or DOUBLE
EXP
FLOAT
FLOOR
GRAPHIC
HEX
INTEGER or INT
JULIAN_DAY
LEFT
LN
LOG10
MICROSECOND
MIDNIGHT_SECONDS
POSSTR
POWER
QUARTER
RADIANS
RAND
REAL
ROUND
SIGN
SIN
SINH
SMALLINT
SPACE
STDDEV
TAN
TANH
TRANSLATE
TRUNCATE or TRUNC
VALUE
VARCHAR
graphic-expression, length
VARGRAPHIC
VARIANCE or VAR
WEEK
WEEK_ISO

Statements partially supported or not supported by Derby

Statements not listed below are completed supported by Derby.

Derby provides the message, SQLState values, and error codes. Use the getSQLState and getMessage methods to view the SQLState and error messages. Derby does not support SQLCODEs. The value returned from (SQLException sqle).getErrorCode()); is not defined.

ALTER TABLE
DB2 UDB - contents may be specified more than once per alter table statement. For example, adding a column and dropping a constraint is allowed in the same ALTER TABLE statement.
Derby - contents may not be specified more than once per alter table statement.
BEGIN DECLARE SECTION
DB2 UDB - Must not be specified in Java.
Derby - No support
CALL
In JDBC, host-variable means question mark '?' which identifies a list of values to be passed as parameters to the procedure.
cast-function-name host-variable
cast-function-name constant
USING DESCRIPTOR descriptor-name
CLOSE
DB2 UDB - Must not be specified in Java.
Derby - No support. Use JDBC cursors instead.
COMMENT
No support.
COMMIT
No support. Use JDBC transaction semantics.
CONNECT (Type 1)
DB2 UDB - Must not be specified in Java.
Derby - No support.
CONNECT (Type 2)
DB2 UDB - Must not be specified in Java.
Derby - No support.
CREATE ALIAS
No support.
CREATE DISTINCT TYPE
No support.
CREATE FUNCTION (External Scalar)
AS LOCATOR option for any data type
RETURNS datatype-3 CAST FROM datatype-4
LANGUAGE C
PARAMETER STYLE DB2SQL
SPECIFIC specific-name
[NOT] DETERMINISTIC
STATIC DISPATCH
[NO] DBINFO
[NO] EXTERNAL ACTION
FENCED
[NO] FINAL CALL
[DIS]ALLOW PARALLEL
NO SCRATCHPAD
SCRATCHPAD length
CREATE FUNCTION (External Table)
No Support.
CREATE FUNCTION (Sourced)
No Support.
CREATE FUNCTION (SQL Scalar)
No Support.
CREATE PROCEDURE (External)
AS LOCATOR
LANGUAGE C
LANGUAGE COBOL
PARAMETER STYLE DB2SQL
PARAMETER STYLE GENERAL
PARAMETER STYLE GENERAL WITH NULLS
[NOT] DETERMINISTIC
[NO] DBINFO
FENCED
PROGRAM TYPE MAIN
CREATE PROCEDURE (SQL)
No Support.
CREATE TABLE
LIKE table-name
INCLUDING IDENTITY
COLUMN ATTRIBUTES
GENERATED ALWAYS BY DEFAULT AS IDENTITY
START WITH numeric-constant
INCREMENT BY numeric-constant
MINVALUE numeric-constant
MAXVALUE numeric-constant
[NO] CACHE
[NO] CACHE integer-constant
CREATE TRIGGER
WHEN (search-condition)
BEGIN ATOMIC triggered-SQL-statement; ... END
CREATE VIEW
WITH CASCADED CHECK OPTION
WITH LOCAL CHECK OPTION
DECLARE CURSOR
DB2 UDB - Must not be specified in Java.
Derby - No support. Use JDBC cursors instead.
DECLARE GLOBAL TEMPORARY TABLE
LIKE table-name INCLUDING IDENTITY COLUMN ATTRIBUTES INCLUDING COLUMN DEFAULTS
LIKE view-name INCLUDING IDENTITY COLUMN ATTRIBUTES INCLUDING COLUMN DEFAULTS
AS (fullselect) DEFINITION ONLY INCLUDING IDENTITY COLUMN ATTRIBUTES INCLUDING COLUMN DEFAULTS
GENERATED ALWAYS AS IDENTITY
START WITH numeric-constant
INCREMENT BY numeric-constant
MINVALUE numeric-constant
MAXVALUE numeric-constant
[NO] CYCLE
[NO] CACHE integer-constant
DELETE
view-name
correlation-name
WITH RR
WITH RS
WITH CS
DESCRIBE
DB2 UDB - Must not be specified in Java.
Derby - No support.
DROP
ALIAS alias-name
DISTINCT TYPE distinct-type-name
SPECIFIC FUNCTION specific-name
PACKAGE package-name
END DECLARE SECTION
DB2 UDB - Must not be specified in Java.
Derby - No support.
EXECUTE
DB2 UDB - Must not be specified in Java.
Derby - No support.
EXECUTE IMMEDIATE
DB2 UDB - Must not be specified in Java.
Derby - No support.
FETCH
No support. Use JDBC cursors instead.
FREE LOCATOR
DB2 UDB - Must not be specified in Java.
Derby - No support.
GRANT (Distinct Type Privileges)
No support.
GRANT (Function or Procedure Privileges)
No support.
GRANT (Package Privileges)
No support.
GRANT (Table or View Privileges)
No support.
INCLUDE
DB2 UDB - Must not be specified in Java.
Derby - No support.
INSERT
view-name (column-name)
NULL
WITH RR
WITH RS
WITH CS
OPEN
DB2 UDB - Must not be specified in Java.
Derby - No support. Use JDBC cursors instead.
PREPARE
DB2 UDB - Must not be specified in Java.
Derby - No support.
RELEASE (Connection)
DB2 UDB - Must not be specified in Java.
Derby - No support.
REVOKE (Distinct Type Privileges)
No support.
REVOKE (Function or Procedure Privileges)
No support.
REVOKE (Package Privileges)
No support.
REVOKE (Table and View Privileges)
No support.
ROLLBACK
ROLLBACK WORK is only supported with SAVEPOINT options.
DB2 UDB - Must not be specified in Java.
Derby - No support.
SELECT
FETCH FIRST integer ROW[s] ONLY
OPTIMIZE for integer ROW[s] ONLY
SELECT INTO
No support.
SET CONNECTION
DB2 UDB - Must not be specified in Java.
Derby - No support.
SET PATH
No support.
SET transistion-variable
No support.
UPDATE
Multiple column-names and expressions denoted by the asterisk * in the assignment-clause cannot be specified more than once in Derby.
table-name correlation-name
view-name correlation-name
column-name (scalar-subselect)
(column-name,*)=(expression,*)
(column-name,*)=(NULL,*)
(column-name,*)=(row-subselect)
WITH RR
WITH RS
WITH CS
VALUES INTO
No support.
WHENEVER
DB2 UDB - Must not be specified in Java.
Derby - No support.

Resources

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management, Open source
ArticleID=13482
ArticleTitle=Functionality from the DB2 UDB SQL Reference for Cross-Platform Development not supported by Derby
publish-date=09092004