Db2

Db2

Connect with Db2, open source, and other data experts to gain value from your data, share insights, and solve problems.

 View Only
Back to Library

IBM Db2: Selected Common SQL Features for Developers of Portable SQL Applications 

Wed March 13, 2019 03:39 PM

IBM Db2
Selected Common SQL Features
for Developers of Portable SQL Applications

Purpose of this Table:

IBM is committed to delivering SQL features that are important to our customers on all platforms in a way that is common and portable and supports the ANSI/ISO SQL standards. A feature may not be available on all Db2 products on exactly the same date. In some cases, this is only because the different Db2 products ship on slightly different schedules. In other cases, this is because customer requirements may accelerate development of an SQL feature on a specific Db2 product.

Many of IBM's database customers and independent software vendors have expressed interest in writing portable applications across multiple platforms. Others prefer to develop using, say Db2 for Windows, and then deploy the application on Db2 for z/OS. These application developers have asked us to provide them with a list of common SQL language capabilities as a quick reference.

This summary version of the common SQL application features is intended to be a quick reference and includes the frequently used features and functions across the platforms. For features and functions not listed in this table, please consult the respective Db2 product SQL Reference books.

Disclaimer:

  1. This list is not necessarily complete. Selected features are based on frequent customer interest. You may find more common features by examining the SQL language reference books for our products in detail.
  2. This list is not intended to be a feature and function list for any of the Db2 products. A feature may already appear on one platform and be in the process of rolling out on other Db2 platforms because we are continuing to ship new functionality all the time. Because of differences in customers and marketplaces, not all platforms require the same features delivered at the same time.

Contents:

Products Included:

  • Db2 11.5 for Linux, Unix and Windows
  • Db2 12 for z/OS
  • Db2 7.5 for IBM i

Key:

  Y
Product includes full support
  P
Product includes partial support, check product information


  Date: Dec 5, 2022
Language element LUW z/OS    i    Notes
Identifiers - Ordinary Y Y Y
Identifiers - Delimited Y Y Y
Identifiers with support of lowercase letters and trailing underscores Y Y Y
Casting of Data Types - Implicit P P Y Db2 for LUW does not support implicit casting of LOBs to, or from non-string types. Db2 for z/OS does not support implicit casting of LOBs to, or from non-string types. Db2 for z/OS does not support implicit casting of BINARY or untyped function arguments.
Automatic data conversion based on code page Y Y Y
Constants Y Y Y
Hex specification of character constants Y Y Y
Null values Y Y Y
Column references Y Y Y
Host Variable References Y Y Y
Indicator variables Y Y Y
Extended indicator variables Y Y Y
Host Structures Y Y Y
"Friendly" arithmetic and conversion Y Y Y
Function invocation Y Y Y
Arithmetic operators Y Y Y
String Concatenation Y Y Y
Scalar subselect as expression Y Y Y
Scalar fullselect as expression Y Y Y
Date/Time Arithmetic Y Y Y
CASE expression Y Y Y
CAST specification Y Y Y
XMLCAST specification Y P P Db2 for z/OS does not support distinct types with an XML source type. Db2 for i restricts the data-type to XML or a distinct type based on XML.
ROW CHANGE expression Y Y Y
Sequence reference (NEXT VALUE and PREVIOUS VALUE) Y Y Y
OLAP ranking specification (RANK, DENSE_RANK) Y Y Y
OLAP numbering specification (ROW_NUMBER) Y Y Y
OLAP aggregation specification Y Y Y
Untyped parameter markers Y P Y Db2 for z/OS returns an error for untyped parameter markers in contexts that are supported by other platforms.
Diagnostic Area Y Y Y
SQLSTATE Y Y Y
SQLCODE Y Y Y
Descriptor Area Y Y Y
Language element LUW z/OS    i    Notes
SMALLINT Y Y Y
INTEGER Y Y Y
BIGINT Y Y Y
DECIMAL Y Y Y
NUMERIC Y Y Y NUMERIC is defined as zoned decimal on Db2 for i and packed decimal on other platforms.
REAL Y Y Y
DOUBLE or FLOAT Y Y Y For portability across platforms, do not use FLOAT(n).
DECFLOAT Y Y Y
CHARACTER Y Y Y
VARCHAR Y Y Y
FOR BIT DATA with CHAR or VARCHAR Y Y Y
CLOB Y Y Y
FOR SBCS or MIXED DATA with CHAR, VARCHAR, or CLOB P Y Y
BINARY Y Y Y
VARBINARY Y Y Y
BLOB Y Y Y
GRAPHIC Y Y Y
VARGRAPHIC Y Y Y
DBCLOB Y Y Y
DATE Y Y Y
TIME Y Y Y
TIMESTAMP Y Y Y
TIMESTAMP with fractional seconds precision 0 to 12 Y Y Y
XML Y Y P
User-defined distinct types Y Y Y
User-defined ordinary array types Y Y Y
Language element LUW z/OS    i    Notes
CURRENT CLIENT_ACCTNG or CLIENT ACCTNG Y Y Y
CURRENT CLIENT_APPLNAME or CLIENT APPLNAME Y Y Y
CURRENT CLIENT_USERID or CLIENT USERID Y Y Y
CURRENT CLIENT_WRKSTNNAME or CLIENT WRKSTNNAME Y Y Y
CURRENT DATE or CURRENT_DATE Y Y Y
CURRENT DECFLOAT ROUNDING MODE Y Y Y
CURRENT DEGREE Y Y Y
CURRENT PATH, CURRENT_PATH or CURRENT FUNCTION PATH Y Y Y
CURRENT SCHEMA or CURRENT_SCHEMA Y Y Y
CURRENT SERVER or CURRENT_SERVER Y Y Y
CURRENT TEMPORAL SYSTEM_TIME Y Y Y
CURRENT TIME or CURRENT_TIME Y Y Y
CURRENT TIMESTAMP or CURRENT_TIMESTAMP Y Y Y
CURRENT TIMEZONE or CURRENT_TIMEZONE Y Y Y
SESSION_USER Y Y Y
USER Y Y Y
Language element LUW z/OS    i    Notes
CLIENT_IPADDR Y Y Y
PACKAGE_NAME Y P Y Db2 for z/OS implements this as a session variable.
PACKAGE_SCHEMA Y P Y Db2 for z/OS implements this as a session variable.
PACKAGE_VERSION Y P Y Db2 for z/OS implements this as a session variable.
Language element LUW z/OS    i    Notes
Basic ( = <> < > <= >= ) excluding LOB types and row value expressions Y Y Y
Basic ( = <> ) row value expressions Y Y Y
Basic with scalar fullselect Y Y Y
Quantified (ALL, ANY or SOME) with scalar expression Y Y Y
Quantified (ANY or SOME) with row value expression Y Y Y
BETWEEN Y Y Y
DISTINCT with scalar expressions Y Y Y
EXISTS Y Y Y
IN followed by list of expressions Y Y Y
IN followed by single column fullselect Y Y Y
IS NULL and IS NOT NULL Y Y Y
ISNULL and ISNOTNULL Y Y Y
LIKE with host variable or string constant as pattern Y Y Y
LIKE with expression for pattern and escape Y Y Y
SELECTIVITY clause Y P Y Db2 for z/OS only supports the SELECTIVITY clause with an indexable spatial predicate function.
Language element LUW z/OS    i    Notes
ARRAY_AGG (ordinary array) Y Y Y
AVG Y Y Y
CORRELATION or CORR Y Y Y
COUNT Y Y Y
COUNT_BIG Y Y Y
COVAR_POP Y Y Y
COVARIANCE or COVAR Y Y Y
COVARIANCE_SAMP or COVAR_SAMP Y Y Y
GROUPING Y Y Y
LISTAGG P P Y Db2 for LUW does not support the ON OVERFLOW clause. Db2 for z/OS does not support the ON OVERFLOW clause.
MAX Y Y Y
MEDIAN Y Y Y
MIN Y Y Y
PERCENTILE_CONT or PERCENTILE_DISC Y Y Y
STDDEV Y Y Y
STDDEV_POP Y Y Y
STDDEV_SAMP Y Y Y
SUM Y Y Y
VARIANCE or VAR Y Y Y
VARIANCE_POP or VAR_POP Y Y Y
VARIANCE_SAMP or VAR_SAMP Y Y Y
XMLAGG Y Y Y
Language element LUW z/OS    i    Notes
ABSVAL or ABS Y Y Y
ACOS Y Y Y
ADD_MONTHS Y Y Y
ARRAY_TRIM Y Y Y
ASCII Y Y Y
ASIN Y Y Y
ATAN Y Y Y
ATAN2 Y Y Y
ATANH Y Y Y
BIGINT Y Y Y
BINARY Y Y Y
BIT Manipulation functions (BITAND, BITANDNOT, BITNOT, BITOR, BITXOR) Y Y Y
BLOB Y Y Y
BSON_TO_JSON Y P Y
CARDINALITY Y Y Y
CEIL or CEILING Y Y Y
CHAR no string units Y Y Y
CHARACTER_LENGTH or CHAR_LENGTH no string units Y P Y Db2 for z/OS requires string length unit CODEUNITS16 or CODEUNITS32 to be specified to achieve equivalent functionality for most data.
CLOB no string units P P Y Db2 for z/OS requires string length unit CODEUNITS16 or CODEUNITS32 to be specified to achieve equivalent functionality for most data.
COALESCE (or VALUE) Y Y Y
COMPARE_DECFLOAT Y Y Y
CONCAT Y Y Y
CONTAINS Y Y Y
COS Y Y Y
COSH Y Y Y
DATE Y Y Y
DAY Y Y Y
DAYNAME Y P Y Db2 for z/OS supports function as a sample in schema DSN8.
DAYOFMONTH Y Y Y
DAYOFWEEK Y Y Y
DAYOFWEEK_ISO Y Y Y
DAYOFYEAR Y Y Y
DAYS Y Y Y
DBCLOB no string units P P Y Db2 for z/OS requires string length unit CODEUNITS16 or CODEUNITS32 to be specified to achieve equivalent functionality for most data.
DECFLOAT Y Y Y
DECFLOAT_FORMAT Y Y Y
DECIMAL or DEC Y Y Y
DECRYPT_BIT P Y Y Db2 for LUW supports the function with name DECRYPT_BIN.
DECRYPT_CHAR Y Y Y
DEGREES Y Y Y
DIFFERENCE Y Y Y
DIGITS Y Y Y
DOUBLE or DOUBLE_PRECISION Y Y Y
ENCRYPT Y Y Y
EXP Y Y Y
EXTRACT Y P Y
FLOAT Y Y Y
FLOOR Y Y Y
GENERATE_UNIQUE Y Y Y
GETHINT Y Y Y Deprecated Db2 for LUW 11.1.0.0.
GRAPHIC no string units P P Y Db2 for z/OS requires string length unit CODEUNITS16 or CODEUNITS32 be specified to achieve equivalent functionality for most data.
GREATEST Y Y Y
HASH Y P Y Db2 for z/OS does not support algorithm 3 (SHA512).
HEX Y Y Y
HOUR Y Y Y
IDENTITY_VAL_LOCAL Y Y Y
INSERT no string units P P Y Db2 for LUW requires string length unit CODEUNITS16 or CODEUNITS32 be specified to achieve equivalent functionality for most data. Db2 for z/OS requires string length unit CODEUNITS16 or CODEUNITS32 be specified to achieve equivalent functionality for most data.
INSTR (also see LOCATE_IN_STRING) Y Y Y
INTEGER or INT Y Y Y
JULIAN_DAY Y Y Y
LAST_DAY Y Y Y
LCASE or LOWER Y Y Y
LCASE or LOWER (locale sensitive) Y P Y
LEAST Y Y Y
LEFT no string units P P Y Db2 for LUW requires string length unit CODEUNITS16 or CODEUNITS32 be specified to achieve equivalent functionality for most data. Db2 for z/OS requires string length unit CODEUNITS16 or CODEUNITS32 be specified to achieve equivalent functionality for most data.
LENGTH no string units Y Y Y
LN Y Y Y
LOCATE no string units P P Y Db2 for LUW requires string length unit CODEUNITS16 or CODEUNITS32 be specified to achieve equivalent functionality for most data. Db2 for z/OS requires string length unit CODEUNITS16 or CODEUNITS32 be specified to achieve equivalent functionality for most data.
LOCATE_IN_STRING or INSTR no string units P P Y Db2 for LUW requires string length unit CODEUNITS16 or CODEUNITS32 be specified to achieve equivalent functionality for most data. Db2 for z/OS requires string length unit CODEUNITS16 or CODEUNITS32 be specified to achieve equivalent functionality for most data.
LOG10 Y Y Y
LPAD P P Y Db2 for LUW has limited support for a CLOB argument. Db2 for z/OS does not support a CLOB argument.
LTRIM with 1 argument P P Y Db2 for LUW has limited support for a CLOB argument. Db2 for z/OS does not support a CLOB argument.
LTRIM with 2 arguments P P Y
MAX Y Y Y
MAX_CARDINALITY Y Y Y
MICROSECOND Y Y Y
MIDNIGHT_SECONDS Y Y Y
MIN Y Y Y
MINUTE Y Y Y
MOD Y Y Y
MONTH Y Y Y
MONTHNAME Y P Y Db2 for z/OS supports function as a sample in schema DSN8.
MONTHS_BETWEEN Y Y Y
MQREAD with up to 2 arguments Y Y Y
MQREADCLOB with up to 2 arguments Y Y Y
MQRECEIVE Y Y Y
MQRECEIVECLOB Y Y Y
MQSEND Y Y Y
MULTIPLY_ALT Y Y Y
NEXT_DAY Y Y Y
NORMALIZE_DECFLOAT Y Y Y
NULLIF Y P Y Db2 for z/OS does not support LOB arguments.
NVL Y Y Y
OVERLAY no string units P P Y Db2 for LUW requires string length unit CODEUNITS16 or CODEUNITS32 be specified to achieve equivalent functionality for most data. Db2 for z/OS requires string length unit CODEUNITS16 or CODEUNITS32 be specified to achieve equivalent functionality for most data.
POSITION no string units P P Y Db2 for LUW requires string length unit CODEUNITS16 or CODEUNITS32 be specified to achieve equivalent functionality for most data. Db2 for z/OS requires string length unit CODEUNITS16 or CODEUNITS32 be specified to achieve equivalent functionality for most data.
POSSTR Y Y Y
POW Y Y Y
POWER Y Y Y
QUANTIZE Y Y Y
QUARTER Y Y Y
RADIANS Y Y Y
RAISE_ERROR Y Y Y
RAND Y Y Y
RANDOM Y Y Y
REAL Y Y Y
REPEAT Y P Y Db2 for z/OS does not support LOB arguments.
REPLACE P P Y Db2 for LUW does not support LOB arguments. Db2 for z/OS does not support LOB arguments.
REPLACE with 3rd argument optional P P Y Db2 for LUW does not support LOB arguments. Db2 for z/OS does not support LOB arguments.
RID Y Y Y
RIGHT no string units P P Y Db2 for LUW requires string length unit CODEUNITS16 or CODEUNITS32 be specified to achieve equivalent functionality for most data. Db2 for z/OS requires string length unit CODEUNITS16 or CODEUNITS32 be specified to achieve equivalent functionality for most data.
ROUND (numeric) Y Y Y
ROUND (numeric) with 2nd argument optional Y Y Y
ROUND_TIMESTAMP Y Y Y
RPAD P P Y Db2 for LUW has limited support for a CLOB argument. Db2 for z/OS does not support a CLOB argument.
RTRIM with 1 argument P P Y Db2 for LUW has limited support for a CLOB argument. Db2 for z/OS does not support a CLOB argument.
RTRIM with 2 arguments P P Y Db2 for z/OS does not support a CLOB argument. Db2 for LUW has limited support for a CLOB argument.
SCORE Y Y Y
SECOND Y Y Y
SIGN Y Y Y
SIN Y Y Y
SINH Y Y Y
SMALLINT Y Y Y
SOUNDEX Y Y Y
SPACE P Y Y Db2 for LUW support is limited to a length of 4000.
SQRT Y Y Y
STRIP Y Y Y
STRLEFT Y Y Y
STRPOS Y Y Y
STRRIGHT Y Y Y
SUBSTR Y Y Y
SUBSTRING no string units P P Y Db2 for LUW requires string length unit CODEUNITS16 or CODEUNITS32 be specified to achieve equivalent functionality for most data. Db2 for z/OS requires string length unit CODEUNITS16 or CODEUNITS32 be specified to achieve equivalent functionality for most data.
TABLE_NAME Y P Y Db2 for z/OS supports function as a sample in schema DSN8.
TABLE_SCHEMA Y P Y Db2 for z/OS supports function as a sample in schema DSN8.
TAN Y Y Y
TANH Y Y Y
TIME Y Y Y
TIMESTAMP Y Y Y
TIMESTAMPDIFF Y Y Y
TIMESTAMP_FORMAT Y Y Y
TIMESTAMP_ISO Y Y Y
TOTALORDER Y Y Y
TO_CHAR (character) Y Y Y
TO_CHAR (decfloat) Y Y Y
TO_CHAR (timestamp) Y Y Y
TO_CLOB Y Y Y
TO_DATE Y Y Y
TO_NUMBER Y Y Y
TO_TIMESTAMP Y Y Y
TRANSLATE Y Y Y
TRIM P P Y Db2 for LUW has limited support for a CLOB argument. Db2 for z/OS does not support a CLOB argument.
TRIM_ARRAY Y Y Y
TRUNCATE or TRUNC (numeric) with 2nd argument optional Y Y Y
TRUNCATE or TRUNC (numeric) Y Y Y
TRUNC_TIMESTAMP Y Y Y
UCASE or UPPER Y Y Y
UCASE or UPPER (locale sensitive) Y P Y
VALUE Y Y Y
VARBINARY Y Y Y
VARCHAR no string units Y P Y Db2 for z/OS does not support decimal-character for "Floating-point to Varchar" or "Decimal floating point to Varchar".
VARCHAR no string units Y P Y Db2 for z/OS does not support format for "Datetime to Varchar".
VARCHAR_BIT_FORMAT Y Y Y
VARCHAR_FORMAT (character) Y Y Y
VARCHAR_FORMAT (decfloat) Y Y Y
VARCHAR_FORMAT (timestamp) Y Y Y
VARGRAPHIC no string units P P Y Db2 for z/OS requires string length unit CODEUNITS16 or CODEUNITS32 be specified to achieve equivalent functionality for most data.
VERIFY_GROUP_FOR_USER Y Y Y
WEEK Y Y Y
WEEK_ISO Y Y Y
WRAP Y P Y
XMLATTRIBUTES Y Y Y
XMLCOMMENT Y Y Y
XMLCONCAT Y Y Y
XMLDOCUMENT Y Y Y
XMLELEMENT Y Y Y
XMLFOREST Y Y Y
XMLNAMESPACES Y Y Y
XMLPARSE Y Y Y
XMLPI Y Y Y
XMLSERIALIZE Y Y Y
XMLTEXT Y Y Y
XSLTRANSFORM Y Y Y
YEAR Y Y Y
Language element LUW z/OS    i    Notes
JSON_TABLE Y P Y
MQREADALL Y Y Y
MQREADALLCLOB Y Y Y
MQRECEIVEALL Y Y Y
MQRECEIVEALLCLOB Y Y Y
XMLTABLE passing named variables Y Y Y
XMLTABLE implicitly passing all columns Y Y Y
Language element LUW z/OS    i    Notes
CREATE_WRAPPED Y P Y
XSR_ADDSCHEMADOC Y Y Y
XSR_COMPLETE Y Y Y
XSR_REGISTER Y Y Y
Language element LUW z/OS    i    Notes
SELECT clause Y Y Y
AS sub-clause in SELECT clause Y Y Y
Qualified * or unqualified * in SELECT clause Y Y Y
Expressions Y Y Y
Aggregate functions allowed in SELECT clause even if the result table is derived from a grouped view Y Y Y
FROM clause referencing a single table or view Y Y Y
FROM clause with implicit cross join (multiple comma separated table-references) Y Y Y
FROM clause with nested table expression with correlation clause Y Y Y
FROM clause with nested table expression without correlation clause Y Y Y
FROM clause with nested table expression preceded by TABLE keyword Y Y Y
FROM clause with CROSS JOIN Y Y Y
FROM clause with explicit INNER JOIN Y Y Y
FROM clause with LEFT OUTER JOIN Y Y Y
FROM clause with RIGHT OUTER JOIN Y Y Y
FROM clause with FULL OUTER JOIN with equality in the join condition Y Y Y
FROM clause with table function reference with correlation clause Y Y Y
FROM clause with table function reference without correlation clause Y Y Y
FROM clause with JSON_TABLE expression Y P Y
FROM clause with XMLTABLE expression with correlation clause Y Y Y
FROM clause with XMLTABLE expression without correlation clause Y Y Y
FROM clause with collection derived table (UNNEST of ordinary array) Y Y Y
FROM clause with INSERT statement Y Y Y
WHERE clause Y Y Y
GROUP BY referencing column names Y Y Y
GROUP BY referencing grouping expressions Y Y Y
GROUP BY referencing super groups (CUBE & ROLLUP) Y Y Y
GROUP BY referencing grouping sets Y Y Y
HAVING clause Y Y Y
Fullselect with UNION or UNION ALL Y Y Y
Fullselect with INTERSECT or INTERSECT ALL Y Y P Db2 for i does not support INTERSECT ALL.
Fullselect with EXCEPT or EXCEPT ALL Y Y P Db2 for i does not support EXCEPT ALL.
Select-statement starting with common table expression Y Y Y
Select-statement using recursive common table expression Y Y Y
Select-statement with isolation clause Y Y Y
ORDER BY clause over qualified column names Y Y Y
ORDER BY clause over sort-key expressions Y Y Y
ORDER BY clause over expressions not in the SELECT list Y Y Y
ORDER BY ORDER OF Y Y Y
FOR UPDATE clause with list of column names Y Y Y
FOR UPDATE clause with implicit column list Y Y Y
FOR READ ONLY clause Y Y Y
OPTIMIZE clause with integer number of rows Y Y Y
FETCH clause Y Y Y
SKIP LOCKED DATA clause Y Y Y Supported since Db2 for LUW 11.5.4.0.
LIMIT/OFFSET clause (alternative syntax for OFFSET and FETCH clauses) Y Y Y
Period specification Y Y Y
Language element LUW z/OS    i    Notes
Static CALL statement Y Y Y
Static CALL statement with host variable name for procedure name Y Y Y
SELECT INTO statement with optional WHERE, GROUP BY or HAVING clauses Y Y Y
SELECT INTO statement with common table expression Y Y Y
SELECT INTO statement with FETCH clause Y Y Y
SELECT INTO statement with isolation clause Y Y Y
SELECT INTO statement with OFFSET clause Y Y Y
SET variable statement Y Y Y
VALUES INTO statement Y Y Y
Language element LUW z/OS    i    Notes
Searched DELETE statement Y Y Y
DELETE statement with isolation clause Y Y Y
DELETE statement with FETCH clause Y Y Y
INSERT statement with single row VALUES clause Y Y Y
INSERT statement with fullselect Y Y Y
INSERT statement with isolation clause Y Y Y
MERGE statement using table-reference input Y Y Y
TRUNCATE statement with IMMEDIATE clause Y Y Y
TRUNCATE statement without IMMEDIATE clause P Y Y Supported since Db2 for LUW 11.5.4.0. Only supported by Db2 for LUW with column-organized tables.
Searched UPDATE statement Y Y Y
UPDATE statement with isolation clause Y Y Y
Language element LUW z/OS    i    Notes
ALLOCATE CURSOR statement P Y Y Db2 for LUW supports the statement only in compound SQL (compiled).
ASSOCIATE LOCATORS statement P Y Y Db2 for LUW supports the statement only in compound SQL (compiled).
CLOSE statement Y Y Y
DECLARE CURSOR statement (see other cursor elements) Y Y Y
Declare INSENSITIVE SCROLL cursor P Y Y Db2 for LUW only supports this capability through CLI or JDBC.
Declare WITH HOLD cursor Y Y Y
Declare WITH RETURN cursor (implicit return to caller) Y Y Y
Declare WITH RETURN TO CALLER cursor Y Y Y
Declare WITH RETURN TO CLIENT cursor Y Y Y
Positioned DELETE statement Y Y Y
FETCH statement with no cursor orientation (implicit NEXT) Y Y Y
FETCH statement with NEXT, PRIOR, FIRST, LAST, BEFORE, AFTER, CURRENT, or RELATIVE P Y Y Db2 for LUW only supports these fetch features through CLI or JDBC.
FETCH statement with ABSOLUTE P Y P Db2 for LUW only supports this fetch feature through CLI or JDBC. Db2 for i only supports this fetch feature through CLI or JDBC.
OPEN statement Y Y Y
Positioned UPDATE statement Y Y Y
Language element LUW z/OS    i    Notes
Isolation Level Specification at package level Y Y Y
Isolation Level Specification at statement level Y P Y Db2 for z/OS has little support for UR and no support for isolation levels in MERGE statements.
RR (SERIALIZABLE in SQL2011 Standard) Y Y Y
RS (REPEATABLE READ in SQL2011 Standard) Y Y Y
CS (READ COMMITTED in SQL2011 Standard) Y Y Y
UR (READ UNCOMMITTED in SQL2011 Standard) Y Y Y
Isolation clause with USE AND KEEP EXCLUSIVE LOCKS Y Y Y
COMMIT statement Y Y Y
CONNECT statement (1 site per transaction) Y Y Y
CONNECT statement (2+ sites per transaction) (also referred to as Two Phase Commit) Y Y Y
RELEASE connection statement Y Y Y
RELEASE SAVEPOINT statement Y Y Y
ROLLBACK unit of work statement Y Y Y
ROLLBACK TO SAVEPOINT statement Y Y Y
SAVEPOINT statement - not nested Y Y Y
SAVEPOINT statement - nested Y Y Y
SET CONNECTION statement Y Y Y
Language element LUW z/OS    i    Notes
DESCRIBE output statement without a USING clause Y Y Y
DESCRIBE INPUT statement Y Y Y
EXECUTE statement Y Y Y
EXECUTE IMMEDIATE statement that specifies a variable Y Y Y
EXECUTE IMMEDIATE statement that specifies an expression Y P Y Db2 for z/OS only supports EXECUTE IMMEDIATE with expression in PL/I
PREPARE statement that specifies FROM variable Y Y Y
PREPARE statement that specifies cursor options (ATTRIBUTES clause) P Y Y Db2 for LUW only supports this capability through CLI or JDBC.
Language element LUW z/OS    i    Notes
Control statements in SQL procedures Y Y Y
Control statements in SQL scalar functions Y Y Y
Control statements in SQL table functions Y P Y Partial support allows a single RETURN statement in SQL table functions.
Control statements in SQL triggers Y Y Y
Assignment statement Y Y Y
CASE statement Y Y Y
Compound statement Y Y Y
Nested not atomic compound statement Y Y Y
Declared constant in compound statement Y Y Y
FOR statement Y Y Y
GET DIAGNOSTICS for ROW_COUNT statement Y Y Y
GET DIAGNOSTICS for RETURN_STATUS statement Y Y Y
GET DIAGNOSTICS for MESSAGE_TEXT statement Y Y Y
GOTO statement Y Y Y
IF statement Y Y Y
ITERATE statement Y Y Y
LEAVE statement Y Y Y
LOOP statement Y Y Y
REPEAT statement Y Y Y
RESIGNAL statement Y Y Y
RETURN statement from SQL procedure Y Y Y
RETURN statement from SQL function Y Y Y
SIGNAL statement Y Y Y
WHILE statement Y Y Y
Language element LUW z/OS    i    Notes
Allow DDL statements in SQL programs Y Y Y
Support Transaction Semantics for DDL Y Y Y
Support 128 byte object names Y Y Y
ALTER FUNCTION statement P Y Y
ALTER MASK statement Y Y Y
ALTER PERMISSION statement Y Y Y
ALTER PROCEDURE statement P Y Y
ALTER SEQUENCE statement Y Y Y
ALTER TABLE statement (see other elements marked ALTAB) Y P Y Db2 for z/OS does not allow ACTIVATE ROW/COLUMN ACCESS CONTROL on a system-period temporal table.
ADD COLUMN (ALTAB) Y Y Y
ALTER COLUMN (ALTAB) P P Y Db2 for z/OS may require a table reorganization before a table can be accessed following a data type alteration. Db2 for LUW may require a table reorganization before a table can be accessed following a data type alteration.
ALTER COLUMN (ALTAB) P P Y Db2 for LUW does not allow changing the generation attribute of a row change timestamp column. Db2 for z/OS does not allow changing the generation attribute of a row change timestamp column.
ALTER COLUMN (ALTAB) P P Y Db2 for z/OS does not allow ALTER COLUMN on a system-period temporal table. Db2 for z/OS does not allow alteration of a TRANSACTION START ID column.
DROP COLUMN (ALTAB) Y P Y
ALTER TRIGGER statement Y Y Y
COMMENT statement Y Y Y
CREATE ALIAS (table or view) statement Y Y Y
CREATE FUNCTION (external scalar) statement Y Y Y
CREATE FUNCTION (sourced) statement Y Y Y
CREATE FUNCTION (SQL scalar) statement Y Y Y
CREATE FUNCTION (external table) statement Y Y Y
CREATE FUNCTION (SQL table) statement Y P Y Db2 for z/OS only supports the RETURN statement in function body.
CREATE INDEX over column list Y Y Y
CREATE UNIQUE INDEX Y Y Y
CREATE INDEX on expression Y P Y Db2 for z/OS does not allow indexes on DECFLOAT expressions.
CREATE MASK statement Y Y Y
CREATE PERMISSION statement Y Y Y
CREATE PROCEDURE (external) statement Y Y Y
CREATE OR REPLACE PROCEDURE (external) statement Y Y Y
CREATE PROCEDURE (SQL) statement Y Y Y
CREATE OR REPLACE PROCEDURE (SQL) statement Y Y Y
CREATE PROCEDURE statement with AUTONOMOUS Y P Y Db2 for z/OS does not support AUTONOMOUS with CREATE PROCEDURE (External).
CREATE TABLE statement using column definition (see other elements marked CRTAB) Y Y Y
CREATE TABLE statement LIKE other table/view (see other elements marked CRTAB) Y Y Y
CREATE TABLE statement LIKE other table/view including identity (see other elements marked CRTAB) Y Y Y
CREATE TABLE statement AS query (see other elements marked CRTAB) Y Y Y
CHECK constraint clause (ALTAB, CRTAB) Y Y Y
DATA CAPTURE clause (ALTAB, CRTAB) Y Y Y Db2 for i tables always capture changes - the clause is ignored.
DEFAULT clause with no specified value (ALTAB, CRTAB, DGTTAB) Y Y Y
DEFAULT clause with specified value (ALTAB, CRTAB, DGTTAB) Y Y Y
IMPLICITLY HIDDEN clause (ALTAB, CRTAB) Y Y Y
VOLATILE clause (ALTAB, CRTAB) Y Y Y
Generated column: IDENTITY clause (CRTAB, DGTTAB) Y Y Y
Generated column: IDENTITY clause (ALTAB change attributes of existing IDENTITY column) Y Y Y
Generated column: ROW CHANGE TIMESTAMP clause (ALTAB, CRTAB) Y Y Y
Generated column: ROW BEGIN or ROW END clause (ALTAB, CCRTAB) Y Y Y
Generated column: TRANSACTION START ID clause (ALTAB, CCRTAB) Y Y Y
PRIMARY KEY constraint for NOT NULL columns (ALTAB, CRTAB) Y Y Y
UNIQUE constraint for NOT NULL columns (ALTAB, CRTAB) Y Y Y
Automatic index generation for unique constraints (ALTAB, CRTAB) Y Y Y
Referential constraint clause (ALTAB, CRTAB) Y Y Y
REFERENCES clause specifying ON DELETE (ALTAB, CRTAB) P P Y Db2 for LUW does not support ON DELETE SET DEFAULT. Db2 for z/OS does not support ON DELETE SET DEFAULT.
Self referencing tables can be specified without requiring use of ALTER TABLE Y P Y
User maintained MQT (CRTAB, ALTAB) Y Y Y
PERIOD SYSTEM_TIME definition (CRTAB, ALTAB) Y Y Y
WITH RESTRICT ON DROP (CRTAB, ALTAB) Y Y Y
CREATE SEQUENCE statement Y Y Y
CREATE TRIGGER statement - BEFORE or AFTER trigger on a table Y Y Y
CREATE TRIGGER statement - INSTEAD OF trigger on a view Y P Y Db2 for z/OS has several restrictions on the underlying tables of the view.
CREATE OR REPLACE TRIGGER statement Y Y Y
CREATE TYPE (array) statement for ordinary array Y Y Y
CREATE TYPE (distinct) statement Y Y Y
CREATE VARIABLE statement P P P Db2 for z/OS does not support defaults based on expressions or global variables.
CREATE VARIABLE statement P P P Db2 for LUW does not support defaults based on array type. Db2 for i does not support defaults based on array type.
CREATE VIEW statement using fullselect without CHECK OPTION clause Y Y Y
CREATE VIEW WITH CASCADED CHECK OPTION Y Y Y
CREATE VIEW WITH LOCAL CHECK OPTION Y Y Y
DECLARE GLOBAL TEMPORARY TABLE statement (see other elements marked DGTTAB) Y Y Y
DROP statement with specified RESTRICT or CASCADE behavior P P Y
DROP ALIAS statement Y Y Y
DROP FUNCTION statement Y Y Y
DROP INDEX statement Y Y Y
DROP MASK statement Y Y Y
DROP PACKAGE statement Y Y Y
DROP PERMISSION statement Y Y Y
DROP PROCEDURE statement Y P Y Db2 for z/OS only supports procedure name to identify the procedure.
DROP SEQUENCE statement Y Y Y
DROP TABLE statement Y Y Y
DROP TRIGGER statement Y Y Y
DROP TYPE statement Y Y Y
DROP VARIABLE statement Y Y Y
DROP VIEW statement Y Y Y
RENAME TABLE statement P P Y
RENAME INDEX statement Y Y Y
Obfuscation of SQL objects written in SQL Y P Y
Language element LUW z/OS    i    Notes
GRANT (function privileges) statement Y Y Y
GRANT (global variable privileges) statement Y Y Y
GRANT (package privileges) statement Y Y Y
GRANT (procedure privileges) statement Y P Y Db2 for z/OS only supports procedure name to identify the procedure.
GRANT (schema privileges) statement Y Y P
GRANT (sequence privileges) statement Y Y Y
GRANT (table privileges) statement Y Y Y
REVOKE (function privileges) statement Y Y Y
REVOKE (global variable privileges) statement Y Y Y
REVOKE (package privileges) statement Y Y Y
REVOKE (procedure privileges) statement Y P Y Db2 for z/OS only supports procedure name to identify the procedure.
REVOKE (schema privileges) statement Y Y P
REVOKE (sequence privileges) statement Y Y Y
REVOKE (table privileges) statement Y Y Y
TRANSFER OWNERSHIP statement Y P Y
Language element LUW z/OS    i    Notes
BEGIN and END DECLARE SECTION statement Y Y Y
DECLARE STATEMENT statement P Y Y Starting with Db2 for LUW 9.7, supports the statement only in compound SQL (compiled) statements.
FREE LOCATOR statement Y Y Y
INCLUDE statement Y Y Y
LOCK TABLE statement Y Y Y
REFRESH TABLE statement Y Y Y
SET CONNECTION statement Y Y Y
SET CURRENT DECFLOAT ROUNDING MODE statement P Y Y
SET CURRENT DEGREE statement Y Y Y
SET CURRENT TEMPORAL SYSTEM_TIME statement Y Y Y
SET ENCRYPTION PASSWORD statement Y Y Y Deprecated Db2 for LUW 11.1.0.0.
SET PATH statement Y Y Y
SET SCHEMA statement Y Y Y
WHENEVER statement Y Y Y
Language element LUW z/OS    i    Notes
C or C++ Y Y Y
COBOL Y Y Y
Java (JDBC) Y Y Y
Java (SQLJ) Y Y Y
PL/I Y Y Y
REXX Y Y Y

#Db2

Statistics
0 Favorited
53 Views
0 Files
0 Shares
0 Downloads