Re: Is Oracle SQL99 Compliant?

Serge Rielau wrote:

> You will need to dig fairly deep to find DML statements which are incompatible amongst the DB2 products.
> In the times of FRED DB2 CS was just a baby running on OS/2 and AIX.
> Here is the cross-platform SQL Reference manual:
> http://www-128.ibm.com/developerworks/db2/library/techarticle/0206sqlref/0206sqlref.html

This was always a wonderful bit of subterfuge when this document replaced FRED. If you look closely at this book it is actually the Anti-FRED and more of a FROSTITS (Formal Register of Stuff that is the same) - i.e the book only covers the subset of the stuff that IS common across the DB2 platforms. From the pre-amble

"This book defines the DB2 UDB SQL language elements that are _common_
to the IBM DB2 Universal Database Family of relational database products <snip>
The DB2 Universal Database relational database products have product books that also describe product-specific elements and explain how to prepare and run a program in a particular environment. The information in this book is a _subset_ of the information in the product books."

Even then, for the stuff that is chosen to be the same, there are substantial differences highlighted in this book that would mean that even if you did restrict yourself to the subset to develop a portable app, eventual migration between the platforms would require some significant rework and testing, as even though the syntax remains the same, the semantics can change substantially between the products.

Imagine if date arithimetic changed between the same version of Oracle running on different platforms, or whether an implict rollback or commit was executed at the end of some work. Or the name qualification rules changed. Or the length of strings returned from a function. Or even the precision of multiplication or division. Ugly

Here's just some of the disclaimers.

"statement prepared using the PREPARE statement can be referenced in a
DECLARE CURSOR, DESCRIBE, or EXECUTE statement. The operational form of the statement persists for the duration of the connection. For DB2 UDB for z/OS it only persists for the duration of the transaction."

"The locking facilities of the database managers are similar but not
identical."

"Like the locking facilities, the recovery facilities of the database
managers are similar but not identical."

"Each product provides a product-specific means of explicitly specifying
a default isolation level:"

"For other operations, the rules of level CS apply. In DB2 UDB for z/OS,
UR is escalated to CS for a subquery used in a DELETE or UPDATE statement, or for a subselect used in an INSERT statement."

"In DB2 UDB for z/OS, a package-name in an SQL statement must be qualified."

"In DB2 UDB for iSeries, a blank is not allowed in a delimited schema name."

"Unqualified alias, index, package, sequence, table, trigger, and view
names are implicitly qualified by the default schema. Each product provides a product-specific means of explicitly specifying a default schema"

"In DB2 UDB for z/OS, and DB2 UDB for LUW, zoned decimal is not
supported as a native data type and NUMERIC is treated as a synonym for DECIMAL. Zoned decimal numbers received through DRDA protocols are converted to packed decimal."

"The mechanism to specify the default decimal separator character is
product-specific."

"The mechanism to specify the default date format and default time
format is product-specific"

"Although all products accept times of 24:00:00, the handling of such
times during arithmetic operations is product-specific."

"all products accept timestamps whose time part is 24.00.00.000000, the
handling of such timestamps during arithmetic operations is product-specific"

"The conversion from floating point to decimal involves rounding and the
selection of a suitable precision and scale for the decimal number. The precision is product-specific and dependent on whether the floating-point number is a single- or double-precision number."

"Assignments to C NUL-terminated strings: When a fixed-length or
varying-length string of length n is assigned to a C NUL-terminated string variable with a length greater than n+1, the string is padded on the right with x-n-1 blanks, where x is the length of the variable. The padded string is then assigned to the variable, and a NUL is placed in the next character position. 21 In DB2 UDB for z/OS, if a varying-length string is assigned to a C NUL-terminated string, the value of a varying-length string column is assigned to the first n character positions of the variable, and a NUL is placed in the next character position."

"Multiplication: The precision of the result of multiplication is min
(31,p+p’) and the scale is min(31,s+s’). In DB2 UDB for z/OS, special rules apply if both p and p’ are greater than 15. See the product reference for further information."

"Division: The precision of the result of division is 31. The scale is
31-p+s-s'. The scale must not be negative. In DB2 UDB for z/OS, the scale is different and special rules apply when p’ is greater than 15. See the product reference for further information."

"DB2 UDB for z/OS and DB2 UDB for LUW leading zeroes are returned. In
DB2 UDB for z/OS, a leading blank is returned from the CHAR function for positive decimal values. The leading blank is not returned for CAST(decimal-expression AS CHAR(n) )."

"The result of the function is VARCHAR(100). In DB2 UDB for z/OS, the
result of the function is VARCHAR(9)."

"DB2 UDB for LUW supports DECRYPT_BIN instead of DECRYPT_BIT."

"In DB2 UDB for z/OS and DB2 UDB for LUW, a COMMIT or ROLLBACK of a unit
of work since the most recent INSERT statement that assigned a value will also cause the result to be null. In DB2 UDB for iSeries, COMMIT and ROLLBACK do not affect the value."

"In EBCDIC environments, if the string-expression contains mixed data,
the LEFT function operates on a strict byte-count basis. Because LEFT operates on a strict byte-count basis, the result is not necessarily a properly formed mixed data character string."

"the character-expression is an empty string constant, the length
attribute of the result is 1. In DB2 UDB for LUW the length attribute is 0."

"In DB2 UDB for z/OS and DB2 UDB for LUW, the list of names is
reestablished. Therefore, the number of columns returned by the statement may change. In DB2 UDB for iSeries, the list of names is normally not reestablished. Therefore, the number of columns returned by the statement will not change. There are cases, however, where the list of names is reestablished. See the product documentation for details."

"Queries using recursion are useful in supporting applications such as
bill of materials (BOM), reservation systems, and network planning. Recursive common table expressions are not supported in DB2 UDB for iSeries."

"PRIMARY KEY is not supported by DB2 UDB for z/OS for column-constraint."

"UNIQUE is not supported by DB2 UDB for z/OS for column-constraint."

"In DB2 UDB for z/OS, the check-condition is subject to additional
restrictions. See the product reference for further information."

"ADD MATERIALIZED QUERY materialized-query-definition is not supported
by DB2 UDB for LUW, but the same functionality can be obtained using SET MATERIALIZED QUERY AS materialized-query-definition."

"Adding or dropping primary, foreign or unique keys or check constraints
or altering column lengths may invalidate access plans. The rules are product-specific."

"Product-specific options exist that may extend the scope of cursors and
prepared statements."

"Either an implicit commit or rollback operation will be performed at
the end of an application process depending on the application environment."

"It must be a CHAR or VARCHAR variable with a length attribute that is
not greater than 18. In DB2 UDB for z/OS, the maximum length is 16. In DB2 UDB for LUW, the maximum length is 8"

"Additional information about the connection is placed in the SQLERRMC
field of the SQLCA. The contents are product-specific."

"In DB2 UDB for LUW, distinct types are not supported in procedures."

"To create LOB columns greater than 1 gigabyte in DB2 UDB for LUW, there
are additional requirements. See product documentation."

"In DB2 UDB for z/OS and DB2 UDB for LUW, a column that allows null
values has a byte count that is one more than shown in the list. In DB2 UDB for iSeries, if any column allows null values, one byte is required for every eight columns"

"All the statements in the SQL-trigger-body run under the isolation
level of the triggering SQL operation. In DB2 UDB for z/OS the SQL statements in the SQL-trigger-body run under the isolation level used at the time the trigger was created."

"In DB2 UDB for LUW, the user must have the privilege to create in a
temporary table space to use this statement."

"DB2 UDB for z/OS does not support EXCLUDING COLUMN DEFAULTS and
INCLUDING COLUMN DEFAULTS for the LIKE clause."

"In DB2 UDB for z/OS, and DB2 UDB for LUW, the authorization ID of the
statement only requires the DELETE privilege for the table or view. To require the SELECT privilege, a standards option must be in effect. For DB2 UDB for z/OS use the precompiler option SQLRULES(STD) or set the CURRENT RULES special register to ’STD’. For DB2 UDB for LUW, use the program preparation option LANGLEVEL SQL92E."

"In DB2 UDB for z/OS, if the DELETE statement is embedded in a program,
the DECLARE CURSOR statement must include a select-statement rather than a statement-name."

"Dropping an alias has no effect on any constraint that was defined
using the alias. The effect on any tables, views, routines, or triggers that reference the alias is product-specific."

"In DB2 UDB for z/OS, if the object table is self-referencing, the
fullselect must not return more than one row."

"Prevents concurrent application processes from executing any operations
on the table. This may or may not apply to concurrent application processes running at isolation level UR. The rule is product-specific."

"A product-specific option may be used to cause some SQL statements to
receive ?delayed? errors. For example, DESCRIBE, EXECUTE, and OPEN might receive an SQLCODE that normally occurs during PREPARE"

"In DB2 UDB for LUW, savepoints must not be nested. If a savepoint
statement is issued, and there is already an established savepoint present, then an error occurs."

"In DB2 UDB for LUW column names specified in CREATE TABLE and CREATE
VIEW statements found in the routine body before the reference to the name are included in the search for the name."

"If a string constant is continued from one line to the next, the first
nonblank character in the next line must be either an apostrophe or a quotation mark. In DB2 UDB for LUW this character must be an apostrophe. Identifiers that are not delimited cannot be continued. If a delimited identifier is continued from one line to the next, the first nonblank character in the next line must be either an apostrophe or a quotation mark. In DB2 UDB for LUW this character must be a quotation mark." Received on Sat Jan 15 2005 - 00:17:42 CST