Contents


Selected common SQL features for developers of portable Db2 applications

Comments

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 more detailed information, please consult the SQL Reference for Cross-Platform Development and the respective Db2 product SQL Reference books.

Please read the disclaimer before using the tables.

Products included

  • Db2 11.1 for Linux, UNIX, and Windows®
  • Db2 12.1 for z/OS®
  • Db2 7.3 for IBM i®

Contents

Key
YProduct includes full support.
PProduct includes partial support; check product information.
Basic elements
Language elementDb2z/OS i Notes
Identifiers - OrdinaryYYY
Identifiers - DelimitedYYY
Identifiers with support of lowercase letters and trailing underscoresYYY
Casting of Data Types - ImplicitPPYDb2 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 pageYYY
ConstantsYYY
Hex specification of character constantsYYY
Null valuesYYY
Column referencesYYY
Host Variable ReferencesYYY
Indicator variablesYYY
Extended indicator variablesYYY
Host StructuresYYY
"Friendly" arithmetic and conversionYYY
Function invocationYYY
Arithmetic operatorsYYY
String ConcatenationYYY
Scalar subselect as expressionYYY
Scalar fullselect as expressionYYY
Date/Time ArithmeticYYY
CASE expressionYYY
CAST specificationYYY
XMLCAST specificationYPPDb2 for z/OS does not support UDTs with an XML source type. Db2 for i restricts the data-type to XML or a distinct type based on XML.
ROW CHANGE expressionYYY
Sequence reference (NEXT VALUE and PREVIOUS VALUE)YYY
OLAP ranking specification (RANK, DENSE_RANK)YYY
OLAP numbering specification (ROW_NUMBER)YYY
OLAP aggregation specificationYYY
Untyped parameter markersYPYDb2 for z/OS returns an error for untyped parameter markers in contexts that are supported by other platforms.
Diagnostic AreaYYY
SQLSTATEYYY
SQLCODEYYY
Descriptor AreaYYY
Data types
Language elementDb2z/OS i Notes
SMALLINTYYY
INTEGERYYY
BIGINTYYY
DECIMALYYY
NUMERICYYYNUMERIC is defined as zoned decimal on Db2 for i and packed decimal on other platforms.
REALYYYFor portability across platforms, do not use FLOAT(n).
DOUBLE or FLOATYYYFor portability across platforms, do not use FLOAT(n).
DECFLOATYYY
CHARACTERYYY
VARCHARYYY
FOR BIT DATA with CHAR or VARCHARYYY
CLOBYYY
FOR SBCS or MIXED DATA with CHAR, VARCHAR, or CLOBPYY
BINARYYYY
VARBINARYYYY
BLOBYYY
GRAPHICYYY
VARGRAPHICYYY
DBCLOBYYY
DATEYYY
TIMEYYY
TIMESTAMPYYY
TIMESTAMP with fractional seconds precision 0 to 12YYY
XMLYYP
User-defined distinct typesYYY
User-defined ordinary array typesYYY
Special registers
Language elementDb2z/OS i Notes
CURRENT CLIENT_ACCTNGYYY
CURRENT CLIENT_APPLNAMEYYY
CURRENT CLIENT_USERIDYYY
CURRENT CLIENT_WRKSTNNAMEYYY
CURRENT DATE or CURRENT_DATEYYY
CURRENT DECFLOAT ROUNDING MODEYYY
CURRENT DEGREEYYY
CURRENT PATH or CURRENT_PATHYYY
CURRENT SCHEMA or CURRENT_SCHEMAYYY
CURRENT SERVERYYY
CURRENT TEMPORAL SYSTEM_TIMEYYY
CURRENT TIME or CURRENT_TIMEYYY
CURRENT TIMESTAMP or CURRENT_TIMESTAMPYYY
CURRENT TIMEZONEYYY
SESSION_USERYYY
USERYYY
Global variables
Language elementDb2z/OS i Notes
CLIENT_IPADDRYYY
PACKAGE_NAMEYPYDb2 for z/OS implements this as a session variable.
PACKAGE_SCHEMAYPYDb2 for z/OS implements this as a session variable.
PACKAGE_VERSIONYPYDb2 for z/OS implements this as a session variable.
Predicates
Language elementDb2z/OS i Notes
Basic ( = <> < > <= >= ) excluding LOB types and row value expressionsYYY
Basic ( = <> ) row value expressionsYYY
Basic with scalar fullselectYYY
Quantified (ALL, ANY or SOME) with scalar expressionYYY
Quantified (ANY or SOME) with row value expressionYYY
BETWEENYYY
DISTINCTYYY
EXISTSYYY
IN followed by list of expressionsYYY
IN followed by single column fullselectYYY
IS NULL and IS NOT NULLYYY
LIKE with host variable or string constant as patternYYY
LIKE with expression for pattern and escapeYYY
Aggregate functions
Language elementDb2z/OS i Notes
ARRAY_AGG (ordinary array)YYY
AVGYYY
CORRELATION or CORRYYY
COUNTYYY
COUNT_BIGYYY
COVARIANCE or COVARYYY
COVARIANCE_SAMP or COVAR_SAMPYYY
GROUPINGYYY
LISTAGGPPYDb2 does not support the ON OVERFLOW clause. Db2 for z/OS does not support the ON OVERFLOW clause.
MAXYYY
MEDIANYYY
MINYYY
PERCENTILE_CONT or PERCENTILE_DISCYYY
STDDEVYYY
STDDEV_POPYYY
STDDEV_SAMPYYY
SUMYYY
VARIANCE or VARYYY
VARIANCE_POP or VAR_POPYYY
VARIANCE_SAMP or VAR_SAMPYYY
XMLAGGYYY
Scalar functions
Language elementDb2z/OS i Notes
ABSVAL or ABSYYY
ACOSYYY
ADD_MONTHSYYY
ASCIIYYY
ASINYYY
ATANYYY
ATAN2YYY
ATANHYYY
BIGINTYYY
BINARYYYY
BIT Manipulation functions (BITAND, BITANDNOT, BITNOT, BITOR, BITXOR)YYY
BLOBYYY
BSON_TO_JSONYPY
CARDINALITYYYY
CEIL or CEILINGYYY
CHAR no string unitsYYY
CHARACTER_LENGTH or CHAR_LENGTH no string unitsYPYDb2 for z/OS requires string length unit CODEUNITS16 or CODEUNITS32 to be specified to achieve equivalent functionality for most data.
CLOB no string unitsPPYDb2 for z/OS requires string length unit CODEUNITS16 or CODEUNITS32 to be specified to achieve equivalent functionality for most data.
COALESCE (or VALUE)YYY
COMPARE_DECFLOATYYY
CONCATYYY
CONTAINSYYY
COSYYY
COSHYYY
DATEYYY
DAYYYY
DAYNAMEYPYDb2 for z/OS supports function as a sample in schema DSN8.
DAYOFMONTHYYY
DAYOFWEEKYYY
DAYOFWEEK_ISOYYY
DAYOFYEARYYY
DAYSYYY
DBCLOB no string unitsPPYDb2 for z/OS requires string length unit CODEUNITS16 or CODEUNITS32 to be specified to achieve equivalent functionality for most data.
DECFLOATYYY
DECFLOAT_FORMATYYY
DECIMAL or DECYYY
DECRYPT_BITPYYDb2 supports the function with name DECRYPT_BIN.
DECRYPT_CHARYYY
DEGREESYYY
DIFFERENCEYYY
DIGITSYYY
DOUBLE or DOUBLE_PRECISIONYYY
ENCRYPTYYY
EXPYYY
EXTRACTYPY
FLOATYYY
FLOORYYY
GENERATE_UNIQUEYYY
GETHINTYYYDeprecated Db2 11.1
GRAPHIC no string unitsPPYDb2 for z/OS requires string length unit CODEUNITS16 or CODEUNITS32 be be specified to achieve equivalent functionality for most data.
GREATESTYYY
HEXYYY
HOURYYY
IDENTITY_VAL_LOCALYYY
INSERT no string unitsPPYDb2 requires string length unit CODEUNITS16 or CODEUNITS32 be be specified to achieve equivalent functionality for most data. Db2 for z/OS requires string length unit CODEUNITS16 or CODEUNITS32 be be specified to achieve equivalent functionality for most data.
INSTR (also see LOCATE_IN_STRING)YYY
INTEGER or INTYYY
JULIAN_DAYYYY
LAST_DAYYYY
LCASE or LOWERYYY
LCASE or LOWER (locale sensitive)YPY
LEASTYYY
LEFT no string unitsPPYDb2 requires string length unit CODEUNITS16 or CODEUNITS32 be be specified to achieve equivalent functionality for most data. Db2 for z/OS requires string length unit CODEUNITS16 or CODEUNITS32 be be specified to achieve equivalent functionality for most data.
LENGTH no string unitsYYY
LNYYY
LOCATE no string unitsPPYDb2 requires string length unit CODEUNITS16 or CODEUNITS32 be be specified to achieve equivalent functionality for most data. Db2 for z/OS requires string length unit CODEUNITS16 or CODEUNITS32 be be specified to achieve equivalent functionality for most data.
LOCATE_IN_STRING or INSTR no string unitsPPYDb2 requires string length unit CODEUNITS16 or CODEUNITS32 be be specified to achieve equivalent functionality for most data. Db2 for z/OS requires string length unit CODEUNITS16 or CODEUNITS32 be be specified to achieve equivalent functionality for most data.
LOG10YYY
LPADPPYDb2 has limited support for a CLOB argument. Db2 for z/OS does not support a CLOB argument.
LTRIM with 1 argumentPPYDb2 has limited support for a CLOB argument. Db2 for z/OS does not support a CLOB argument.
LTRIM with 2 argumentsPPY
MAXYYY
MAX_CARDINALITYYYY
MICROSECONDYYY
MIDNIGHT_SECONDSYYY
MINYYY
MINUTEYYY
MODYYY
MONTHYYY
MONTHNAMEYPYDb2 for z/OS supports function as a sample in schema DSN8.
MONTHS_BETWEENYYY
MQREAD with up to 2 argumentsYYY
MQREADCLOB with up to 2 argumentsYYY
MQRECEIVEYYY
MQRECEIVECLOBYYY
MQSENDYYY
MULTIPLY_ALTYYY
NEXT_DAYYYY
NORMALIZE_DECFLOATYYY
NULLIFYPYDb2 for z/OS does not support LOB arguments.
OVERLAY no string unitsPPYDb2 requires string length unit CODEUNITS16 or CODEUNITS32 be be specified to achieve equivalent functionality for most data. Db2 for z/OS requires string length unit CODEUNITS16 or CODEUNITS32 be be specified to achieve equivalent functionality for most data.
POSITION no string unitsPPYDb2 requires string length unit CODEUNITS16 or CODEUNITS32 be be specified to achieve equivalent functionality for most data. Db2 for z/OS requires string length unit CODEUNITS16 or CODEUNITS32 be be specified to achieve equivalent functionality for most data.
POSSTRYYY
POWERYYY
QUANTIZEYYY
QUARTERYYY
RADIANSYYY
RAISE_ERRORYYY
RANDYYY
REALYYY
REPEATYPYDb2 for z/OS does not support LOB arguments.
REPLACEPPYDb2 does not support LOB arguments. Db2 for z/OS does not support LOB arguments.
REPLACE with 3rd argument optionalPPYDb2 does not support LOB arguments. Db2 for z/OS does not support LOB arguments.
RIDYYY
RIGHT no string unitsPPYDb2 requires string length unit CODEUNITS16 or CODEUNITS32 be be specified to achieve equivalent functionality for most data. Db2 for z/OS requires string length unit CODEUNITS16 or CODEUNITS32 be be specified to achieve equivalent functionality for most data.
ROUND (numeric)YYY
ROUND (numeric) with 2nd argument optionalYYY
ROUND_TIMESTAMPYYY
RPADPPYDb2 has limited support for a CLOB argument. Db2 for z/OS does not support a CLOB argument.
RTRIM with 1 argumentPPYDb2 has limited support for a CLOB argument. Db2 for z/OS does not support a CLOB argument.
RTRIM with 2 argumentsPPYDb2 for z/OS does not support a CLOB argument. Db2 has limited support for a CLOB argument.
SCOREYYY
SECONDYYY
SIGNYYY
SINYYY
SINHYYY
SMALLINTYYY
SOUNDEXYYY
SPACEPYYDb2 support is limited to a length of 4000
SQRTYYY
STRIPYYY
SUBSTRYYY
SUBSTRING no string unitsPPYDb2 requires string length unit CODEUNITS16 or CODEUNITS32 be be specified to achieve equivalent functionality for most data. Db2 for z/OS requires string length unit CODEUNITS16 or CODEUNITS32 be be specified to achieve equivalent functionality for most data.
TABLE_NAMEYPYDb2 for z/OS supports function as a sample in schema DSN8.
TABLE_SCHEMAYPYDb2 for z/OS supports function as a sample in schema DSN8.
TANYYY
TANHYYY
TIMEYYY
TIMESTAMPYYY
TIMESTAMPDIFFYYY
TIMESTAMP_FORMATYYY
TIMESTAMP_ISOYYY
TOTALORDERYYY
TO_CHAR (character)YYY
TO_CHAR (decfloat)YYY
TO_CHAR (timestamp)YYY
TO_DATEYYY
TO_NUMBERYYY
TRANSLATEYYY
TRIMPPYDb2 has limited support for a CLOB argument. Db2 for z/OS does not support a CLOB argument.
TRIM_ARRAYYYY
TRUNCATE or TRUNC (numeric) with 2nd argument optionalYYY
TRUNCATE or TRUNC (numeric)YYY
TRUNC_TIMESTAMPYYY
UCASE or UPPERYYY
UCASE or UPPER (locale sensitive)YPY
VALUEYYY
VARBINARYYYY
VARCHAR no string unitsYPYDb2 for z/OS does not support decimal-char for "Floating-point to Varchar" and "Decimal floating point to Varchar". z/OS does not support format for "Datetime to Varchar".
VARCHAR_BIT_FORMATYYY
VARCHAR_FORMAT (character)YYY
VARCHAR_FORMAT (decfloat)YYY
VARCHAR_FORMAT (timestamp)YYY
VARGRAPHIC no string unitsPPYDb2 for z/OS requires string length unit CODEUNITS16 or CODEUNITS32 be be specified to achieve equivalent functionality for most data.
VERIFY_GROUP_FOR_USERYYY
WEEKYYY
WEEK_ISOYYY
WRAPYPY
XMLATTRIBUTESYYY
XMLCOMMENTYYY
XMLCONCATYYY
XMLDOCUMENTYYY
XMLELEMENTYYY
XMLFORESTYYY
XMLNAMESPACESYYY
XMLPARSEYYY
XMLPIYYY
XMLSERIALIZEYYY
XMLTEXTYYY
XSLTRANSFORMYYY
YEARYYY
Table functions
Language elementDb2z/OS i Notes
JSON_TABLEYPY
MQREADALLYYY
MQREADALLCLOBYYY
MQRECEIVEALLYYY
MQRECEIVEALLCLOBYYY
XMLTABLE passing named variablesYYY
XMLTABLE implicitly passing all columnsYYY
Procedures
Language elementDb2z/OS i Notes
CREATE_WRAPPEDYPY
XSR_ADDSCHEMADOCYYY
XSR_COMPLETEYYY
XSR_REGISTERYYY
Queries
Language elementDb2z/OS i Notes
SELECT clauseYYY
AS sub-clause in SELECT clauseYYY
Qualified * or unqualified * in SELECT clauseYYY
ExpressionsYYY
Aggregate functions allowed in SELECT clause even if the result table is derived from a grouped viewYYY
FROM clause referencing a single table or viewYYY
FROM clause with implicit cross join (multiple comma separated table-references)YYY
FROM clause with nested table expression with correlation clauseYYY
FROM clause with nested table expression without correlation clauseYYY
FROM clause with nested table expression preceded by TABLE keywordYYY
FROM clause with CROSS JOINYYY
FROM clause with explicit INNER JOINYYY
FROM clause with LEFT OUTER JOINYYY
FROM clause with RIGHT OUTER JOINYYY
FROM clause with FULL OUTER JOIN with equality in the join conditionYYY
FROM clause with table function reference with correlation clauseYYY
FROM clause with table function reference without correlation clauseYYY
FROM clause with JSON_TABLE expressionYPY
FROM clause with XMLTABLE expression with correlation clauseYYY
FROM clause with XMLTABLE expression without correlation clauseYYY
FROM clause with collection derived table (UNNEST of ordinary array)YYY
FROM clause with INSERT statementYYY
WHERE clauseYYY
GROUP BY referencing column namesYYY
GROUP BY referencing grouping expressionsYYY
GROUP BY referencing super groups (CUBE & ROLLUP)YYY
GROUP BY referencing grouping setsYYY
HAVING clauseYYY
Fullselect with UNION or UNION ALLYYY
Fullselect with INTERSECT or INTERSECT ALLYYPDb2 for i does not support INTERSECT ALL.
Fullselect with EXCEPT or EXCEPT ALLYYPDb2 for i does not support EXCEPT ALL.
Select-statement starting with common table expressionYYY
Select-statement using recursive common table expressionYYY
Select-statement with isolation clauseYYY
ORDER BY clause over qualified column namesYYY
ORDER BY clause over sort-key expressionsYYY
ORDER BY clause over expressions not in the SELECT listYYY
ORDER BY ORDER OFYYY
FOR UPDATE clause with list of column namesYYY
FOR UPDATE clause with implicit column listYYY
FOR READ ONLY clauseYYY
OPTIMIZE clause with integer number of rowsYYY
FETCH clauseYYY
LIMIT/OFFSET clause (alternative syntax for OFFSET and FETCH clauses)YYY
Period specificationYYY
Basic statements
Language elementDb2z/OS i Notes
Static CALL statementYYY
Static CALL statement with host variable name for procedure nameYYY
SELECT INTO statement with optional WHERE, GROUP BY or HAVING clausesYYY
SELECT INTO statement with common table expressionYYY
SELECT INTO statement with FETCH clauseYYY
SELECT INTO statement with isolation clauseYYY
SELECT INTO statement with OFFSET clauseYYY
SET variable statementYYY
VALUES INTO statementYYY
Data change statements
Language elementDb2z/OS i Notes
Searched DELETE statementYYY
DELETE statement with isolation clauseYYY
DELETE statement with FETCH clauseYYY
INSERT statement with single row VALUES clauseYYY
INSERT statement with fullselectYYY
INSERT statement with isolation clauseYYY
MERGE statement using table-reference inputYYY
TRUNCATE statement with IMMEDIATE clauseYYY
Searched UPDATE statementYYY
Cursor-oriented operations
Language elementDb2z/OS i Notes
ALLOCATE CURSOR statementPYYDb2 supports the statement only in SQL procedures.
ASSOCIATE LOCATORS statementPYYDb2 supports the statement only in SQL procedures.
CLOSE statementYYY
DECLARE CURSOR statement (see other cursor elements)YYY
Declare INSENSITIVE SCROLL cursorPYYDb2 only supports this capability through CLI or JDBC.
Declare WITH HOLD cursorYYY
Declare WITH RETURN cursor (implicit return to caller)YYY
Declare WITH RETURN TO CALLER cursorYYY
Declare WITH RETURN TO CLIENT cursorYYY
Positioned DELETE statementYYY
FETCH statement with no cursor orientation (implicit NEXT)YYY
FETCH statement with NEXT, PRIOR, FIRST, LAST, BEFORE, AFTER, CURRENT, or RELATIVEPYYDb2 only supports these fetch features through CLI or JDBC.
FETCH statement with ABSOLUTEPYPDb2 only supports this fetch feature through CLI or JDBC. Db2 for i only supports this fetch feature through CLI or JDBC.
OPEN statementYYY
Positioned UPDATE statementYYY
Connection and transaction control
Language elementDb2z/OS i Notes
Isolation Level Specification at package levelYYY
Isolation Level Specification at statement levelYPYDb2 for z/OS has little support for UR and no support for isolation levels in MERGE statements.
RR (SERIALIZABLE in SQL2011 Standard)YYY
RS (REPEATABLE READ in SQL2011 Standard)YYY
CS (READ COMMITTED in SQL2011 Standard)YYY
UR (READ UNCOMMITTED in SQL2011 Standard)YYY
Isolation clause with USE AND KEEP EXCLUSIVE LOCKSYYY
COMMIT statementYYY
CONNECT statement (1 site per transaction)YYY
CONNECT statement (2+ sites per transaction) (also referred to as Two Phase Commit)YYY
RELEASE connection statementYYY
RELEASE SAVEPOINT statementYYY
ROLLBACK unit of work statementYYY
ROLLBACK TO SAVEPOINT statementYYY
SAVEPOINT statement - not nestedYYY
SAVEPOINT statement - nestedYYY
SET CONNECTION statementYYY
Dynamic facilities
Language elementDb2z/OS i Notes
DESCRIBE output statement without a USING clauseYYY
DESCRIBE INPUT statementYYY
EXECUTE statementYYY
EXECUTE IMMEDIATE statement that specifies a variableYYY
PREPARE statement that specifies FROM variableYYY
PREPARE statement that specifies cursor options (ATTRIBUTES clause)PYYDb2 only supports this capability through CLI or JDBC.
Control statements
Language elementDb2z/OS i Notes
Control statements in SQL proceduresYYY
Control statements in SQL scalar functionsYYY
Control statements in SQL table functionsYPYPartial support allows a single RETURN statement in SQL table functions.
Control statements in SQL triggersYYY
Assignment statementYYY
CASE statementYYY
Compound statementYYY
Nested not atomic compound statementYYY
Declared constant in compound statementYYY
FOR statementYYY
GET DIAGNOSTICS for ROW_COUNT statementYYY
GET DIAGNOSTICS for RETURN_STATUS statementYYY
GET DIAGNOSTICS for MESSAGE_TEXT statementYYY
GOTO statementYYY
IF statementYYY
ITERATE statementYYY
LEAVE statementYYY
LOOP statementYYY
REPEAT statementYYY
RESIGNAL statementYYY
RETURN statement from SQL procedureYYY
RETURN statement from SQL functionYYY
SIGNAL statementYYY
WHILE statementYYY
Data definition
Language elementDb2z/OS i Notes
Allow DDL statements in SQL programsYYY
Support Transaction Semantics for DDLYYY
Support 30 byte column namesYYY
Support 128 byte object namesYYY
ALTER FUNCTION statementPYY
ALTER MASK statementYYY
ALTER PERMISSION statementYYY
ALTER PROCEDURE statementPYY
ALTER SEQUENCE statementYYY
ALTER TABLE statement (see other elements marked ALTAB)YPYDb2 for z/OS does not allow ACTIVATE ROW/COLUMN ACCESS CONTROL on a system-period temporal table.
ADD COLUMN (ALTAB)YYY
ALTER COLUMN (ALTAB)PPYDb2 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. Db2 for z/OS may require a table reorganization before a table can be accessed following a data type alteration Db2 may require a table reorganization before a table can be accessed following a data type alteration
DROP COLUMN (ALTAB)YPY
ALTER TRIGGER statementYYY
COMMENT statementYYY
CREATE ALIAS (table or view) statementYYY
CREATE FUNCTION (external scalar) statementYYY
CREATE FUNCTION (sourced) statementYYY
CREATE FUNCTION (SQL scalar) statementYYY
CREATE FUNCTION (external table) statementYYY
CREATE FUNCTION (SQL table) statementYPYDb2 for z/OS only supports the RETURN statement in function body.
CREATE INDEX over column listYYY
CREATE UNIQUE INDEX over column listYYY
CREATE INDEX on expressionYYY
CREATE MASK statementYYY
CREATE PERMISSION statementYYY
CREATE PROCEDURE (external) statementYYY
CREATE PROCEDURE (SQL) statementYYY
CREATE PROCEDURE statement with AUTONOMOUSYPYDb2 for z/OS does not support AUTONOMOUS with CREATE PROCEDURE (External).
CREATE SCHEMAYPYDb2 for z/OS requires the statement to be processed by a special schema processor.
CREATE TABLE statement using column definition (see other elements marked CRTAB)YYY
CREATE TABLE statement LIKE other table/view (see other elements marked CRTAB)YYY
CREATE TABLE statement LIKE other table/view including identity (see other elements marked CRTAB)YYY
CREATE TABLE statement AS query (see other elements marked CRTAB)YYY
CHECK constraint clause (ALTAB, CRTAB)YYY
DATA CAPTURE clause (ALTAB, CRTAB)YYYDb2 for i tables always capture changes - the clause is ignored.
DEFAULT clause with no specified value (ALTAB, CRTAB, DGTTAB)YYY
DEFAULT clause with specified value (ALTAB, CRTAB, DGTTAB)YYY
IMPLICITLY HIDDEN clause (ALTAB, CRTAB)YYY
VOLATILE clause (ALTAB, CRTAB)YYY
Generated column: IDENTITY clause (CRTAB, DGTTAB)YYY
Generated column: IDENTITY clause (ALTAB change attributes of existing IDENTITY column)YYY
Generated column: ROW CHANGE TIMESTAMP clause (ALTAB, CRTAB)YYY
Generated column: ROW BEGIN or ROW END clause (ALTAB, CCRTAB)YYY
Generated column: TRANSACTION START ID clause (ALTAB, CCRTAB)YYY
PRIMARY KEY constraint clause (ALTAB, CRTAB)YYY
UNIQUE constraint clause (ALTAB, CRTAB)YYY
Automatic index generation for unique constraints (ALTAB, CRTAB)YYY
Referential constraint clause (ALTAB, CRTAB)YYY
REFERENCES clause specifying ON DELETE (ALTAB, CRTAB)PPYDb2 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 TABLEYPY
User maintained MQT (CRTAB, ALTAB)YYY
PERIOD SYSTEM_TIME definition (CRTAB, ALTAB)YYY
CREATE SEQUENCE statementYYY
CREATE TRIGGER statement - BEFORE or AFTER trigger on a tableYYY
CREATE TRIGGER statement - INSTEAD OF trigger on a viewYPYDb2 for z/OS has several restrictions on the underlying tables of the view.
CREATE OR REPLACE TRIGGER statementYYY
CREATE TYPE (array) statement for ordinary arrayYYY
CREATE TYPE (distinct) statementYYY
CREATE VARIABLE statementPPPDb2 for z/OS does not support defaults based on expressions, global variables. Db2 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 clauseYYY
CREATE VIEW WITH CASCADED CHECK OPTIONYYY
CREATE VIEW WITH LOCAL CHECK OPTIONYYY
DECLARE GLOBAL TEMPORARY TABLE statement (see other elements marked DGTTAB)YYY
DROP statement with specified RESTRICT or CASCADE behaviourPPY
DROP ALIAS statementYYY
DROP FUNCTION statementYYY
DROP INDEX statementYYY
DROP MASK statementYYY
DROP PACKAGE statementYYY
DROP PERMISSION statementYYY
DROP PROCEDURE statementYPYDb2 for z/OS only supports procedure name to identify the procedure.
DROP SEQUENCE statementYYY
DROP TABLE statementYYY
DROP TRIGGER statementYYY
DROP TYPE statementYYY
DROP VARIABLE statementYYY
DROP VIEW statementYYY
RENAME TABLE statementPPY
RENAME INDEX statementYYY
Obfuscation of SQL objects written in SQLYPY
Authorization
Language elementDb2z/OS i Notes
GRANT (function privileges) statementYYY
GRANT (global variable privileges) statementYYY
GRANT (package privileges) statementYYY
GRANT (procedure privileges) statementYPYDb2 for z/OS only supports procedure name to identify the procedure.
GRANT (schema privileges) statementYYP
GRANT (sequence privileges) statementYYY
GRANT (table privileges) statementYYY
REVOKE (function privileges) statementYYY
REVOKE (global variable privileges) statementYYY
REVOKE (package privileges) statementYYY
REVOKE (procedure privileges) statementYPYDb2 for z/OS only supports procedure name to identify the procedure.
REVOKE (schema privileges) statementYYP
REVOKE (sequence privileges) statementYYY
REVOKE (table privileges) statementYYY
TRANSFER OWNERSHIP statementYPY
Miscellaneous statements
Language elementDb2z/OS i Notes
BEGIN and END DECLARE SECTION statementYYY
DECLARE STATEMENT statementPYYStarting with Db2 9.7, supports the statement only in compound SQL (compiled) statements.
FREE LOCATOR statementYYY
INCLUDE statementYYY
LOCK TABLE statementYYY
REFRESH TABLE statementYYY
SET CONNECTION statementYYY
SET CURRENT DECFLOAT ROUNDING MODE statementPYY
SET CURRENT DEGREE statementYYY
SET CURRENT TEMPORAL SYSTEM_TIME statementYYY
SET ENCRYPTION PASSWORD statementYYYDeprecated Db2 11.1
SET PATH statementYYY
SET SCHEMA statementYYY
WHENEVER statementYYY
Host languages
Language elementDb2z/OS i Notes
C or C++YYY
COBOLYYY
FORTRANYYY
Java (SQLJ)YYY
PL/IYYY
REXXYYY

Disclaimer

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.

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.


Downloadable resources


Related topics


Comments

Sign in or register to add and subscribe to comments.

static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=196314
ArticleTitle=Selected common SQL features for developers of portable Db2 applications
publish-date=05082017