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 (PDF 4.2 MB) 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 elementLUWz/OS i Notes
Identifiers -OrdinaryYYY
Identifiers -DelimitedYYY
Identifiers with support of lowercase letters and trailing underscoresYYY
Casting of Data Types -ImplicitYYY
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 specificationYYP
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 markersYPYFor z/OS, an error is returned for untyped parameter markers in contexts that are supported by other platforms.
Diagnostic AreaYYY
SQLSTATEYYY
SQLCODEYYY
Descriptor AreaYYY
Data types
Language elementLUWz/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 elementLUWz/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 elementLUWz/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 elementLUWz/OS i Notes
Basic ( = <> < > <= >= ) excluding LOB types and row value expressionsYYY
Basic ( = <> < > <= >= ) row value expressionsYYPDB2 for i only supports = and <> with row value expressions.
Basic with scalar subselect right of comparison operatorYYY
Quantified (ALL, ANY or SOME) with single expression (no subquery) on left side and subselect on right sideYYY
BETWEEN (no scalar subqueries)YYY
DISTINCTYYY
EXISTS (subselect)YYY
IN followed by list of expressionsYYY
IN followed by single column subselectYYY
IS NULL and IS NOT NULL (no scalar subquery)YYY
LIKE with host variable or string constant as patternYYY
LIKE with expression for pattern and escapeYYY
Aggregate functions
Language elementLUWz/OS i Notes
ARRAY_AGG (ordinary array)YYY
AVGYYY
CORRELATION or CORRYYY
COUNTYYY
COUNT_BIGYYY
COVARIANCE or COVARYYY
COVARIANCE_SAMP or COVAR_SAMPYYY
CUME_DISTYYY
GROUPINGYYY
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 elementLUWz/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
CARDINALITYYYY
CEIL or CEILINGYYY
CHAR (no string units)YYY
CHARACTER_LENGTH or CHAR_LENGTH (no string units)YPYFor z/OS, string length unit CODEUNITS16 or CODEUNITS32 must be specified to achieve equivalent functionality for most data.
CLOB (no string units)PPYFor z/OS, string length unit CODEUNITS16 or CODEUNITS32 must be specified to achieve equivalent functionality for most data.
COALESCE (or VALUE)YYY
COMPARE_DECFLOATYYY
CONCATYYY
CONTAINSYYY
COSYYY
COSHYYY
DATEYYY
DAYYYY
DAYNAMEYPYz/OS supports function as a sample in schema DSN8.
DAYOFMONTHYYY
DAYOFWEEKYYY
DAYOFWEEK_ISOYYY
DAYOFYEARYYY
DAYSYYY
DBCLOB (no string units)PPYFor z/OS, string length unit CODEUNITS16 or CODEUNITS32 must be specified to achieve equivalent functionality for most data.
DECFLOATYYY
DECIMAL or DECYYY
DECRYPT_BITPYYLUW supports the function with name DECRYPT_BIN.
DECRYPT_CHARYYY
DEGREESYYY
DIFFERENCEYYY
DIGITSYYY
DOUBLE or DOUBLE_PRECISIONYYY
ENCRYPTYYYSyntax for the ENCRYPT function is common but z/OS will use the ENCRYPT_TDES format for the result instead of ENCRYPT_RC2.
EXPYYY
EXTRACTYPP
FLOATYYY
FLOORYYY
GENERATE_UNIQUEYYY
GETHINTYYY
GRAPHIC (no string units)PPYFor z/OS, string length unit CODEUNITS16 or CODEUNITS32 must be specified to achieve equivalent functionality for most data.
HEXYYY
HOURYYY
IDENTITY_VAL_LOCALYYY
INSERT (no string units)PPYFor LUW, string length unit CODEUNITS16 or CODEUNITS32 must be specified to achieve equivalent functionality for most data. For z/OS, string length unit CODEUNITS16 or CODEUNITS32 must 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
LEFT (no string units)PPYFor LUW, string length unit CODEUNITS16 or CODEUNITS32 must be specified to achieve equivalent functionality for most data. For z/OS, string length unit CODEUNITS16 or CODEUNITS32 must be specified to achieve equivalent functionality for most data.
LENGTH (no string units)YYY
LNYYY
LOCATE (no string units)PPYFor LUW, string length unit CODEUNITS16 or CODEUNITS32 must be specified to achieve equivalent functionality for most data. For z/OS, string length unit CODEUNITS16 or CODEUNITS32 must be specified to achieve equivalent functionality for most data.
LOCATE_IN_STRING or INSTR (no string units)PPYFor LUW, string length unit CODEUNITS16 or CODEUNITS32 must be specified to achieve equivalent functionality for most data. For z/OS, string length unit CODEUNITS16 or CODEUNITS32 must be specified to achieve equivalent functionality for most data.
LOG10YYY
LPADPPYLUW has limited support for a CLOB argument. z/OS does not support a CLOB argument.
LTRIM (with 1 argument)PPYLUW has limited support for a CLOB argument. z/OS does not support a CLOB argument.
MAXYYY
MAX_CARDINALITYYYY
MICROSECONDYYY
MIDNIGHT_SECONDSYYY
MINYYY
MINUTEYYY
MODYYY
MONTHYYY
MONTHNAMEYPYz/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
NULLIFYPYz/OS does not support LOB arguments.
OVERLAY (no string units)PPYFor LUW, string length unit CODEUNITS16 or CODEUNITS32 must be specified to achieve equivalent functionality for most data. For z/OS, string length unit CODEUNITS16 or CODEUNITS32 must be specified to achieve equivalent functionality for most data.
POSITION (no string units)PPYFor LUW, string length unit CODEUNITS16 or CODEUNITS32 must be specified to achieve equivalent functionality for most data. For z/OS, string length unit CODEUNITS16 or CODEUNITS32 must be specified to achieve equivalent functionality for most data.
POSSTRYYY
POWERYYY
QUANTIZEYYY
QUARTERYYY
RADIANSYYY
RAISE_ERRORYYY
RANDYYY
REALYYY
REPEATYPYz/OS does not support LOB arguments.
REPLACEPPYLUW does not support LOB arguments. z/OS does not support LOB arguments.
RIDYYY
RIGHT (no string units)PPYFor LUW, string length unit CODEUNITS16 or CODEUNITS32 must be specified to achieve equivalent functionality for most data. For z/OS, string length unit CODEUNITS16 or CODEUNITS32 must be specified to achieve equivalent functionality for most data.
ROUND (numeric)YYY
ROUND (numeric) with 2nd argument optionalYYY
ROUND_TIMESTAMPYYY
RPADPPYLUW has limited support for a CLOB argument. z/OS does not support a CLOB argument.
RTRIM (with 1 argument)PPYLUW has limited support for a CLOB argument. z/OS does not support a CLOB argument.
SCOREYYY
SECONDYYY
SIGNYYY
SINYYY
SINHYYY
SMALLINTYYY
SOUNDEXYYY
SPACEPYYLUW support is limited to a length of 4000
SQRTYYY
STRIPYYY
SUBSTRYYY
SUBSTRING (no string units)PPYFor LUW, string length unit CODEUNITS16 or CODEUNITS32 must be specified to achieve equivalent functionality for most data. For z/OS, string length unit CODEUNITS16 or CODEUNITS32 must be specified to achieve equivalent functionality for most data.
TANYYY
TANHYYY
TIMEYYY
TIMESTAMPYYY
TIMESTAMPDIFFYYY
TIMESTAMP_FORMATYYY
TIMESTAMP_ISOYYY
TOTALORDERYYY
TO_CHAR (character)YYY
TO_CHAR (decfloat)YYY
TO_CHAR (timestamp)YYY
TO_DATEYYY
TRANSLATEYYY
TRIMPPYLUW has limited support for a CLOB argument. 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 units)YPYz/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 units)PPYFor z/OS, string length unit CODEUNITS16 or CODEUNITS32 must 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 elementLUWz/OS i Notes
MQREADALLYYY
MQREADALLCLOBYYY
MQRECEIVEALLYYY
MQRECEIVEALLCLOBYYY
XMLTABLE passing named variablesYYY
XMLTABLE implicitly passing all columnsYYY
Procedures
Language elementLUWz/OS i Notes
CREATE_WRAPPEDYPY
XSR_ADDSCHEMADOCYYY
XSR_COMPLETEYYY
XSR_REGISTERYYY
Queries
Language elementLUWz/OS i Notes
SELECT clauseYYY
AS sub-clause in SELECT clauseYYY
Qualified * or unqualified * in SELECT clauseYYY
Expressions other than scalar subquery in SELECT clauseYYY
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 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 XMLTABLE expression with 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
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
Select-statement with isolation clause with CS, RR, or RSYYY
Select-statement with isolation clause with UR on read only select statementYYY
FETCH FIRST clauseYYY
LIMIT and OFFSET clausesPPP
Period specificationYYY
Basic statements
Language elementLUWz/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 FIRST clauseYYY
SELECT INTO statement with isolation clauseYYY
SET variable statementYYY
VALUES INTO statementYYY
Data change statements
Language elementLUWz/OS i Notes
Searched DELETE statementYYY
INSERT statement with single row VALUES clauseYYY
MERGE statement using table-reference inputYYY
TRUNCATE statementYYY
Searched UPDATE statementYYY
Cursor-oriented operations
Language elementLUWz/OS i Notes
ALLOCATE CURSOR statementPYYLUW supports the statement only in SQL procedures.
ASSOCIATE LOCATORS statementPYYLUW supports the statement only in SQL procedures.
CLOSE statementYYY
DECLARE CURSOR statement (see other cursor elements)YYY
Declare INSENSITIVE SCROLL cursorPYYLUW 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 RELATIVEPYYLUW only supports these fetch features through CLI or JDBC.
FETCH statement with ABSOLUTEPYPLUW 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 elementLUWz/OS i Notes
Isolation Level Specification at package levelYYY
Isolation Level Specification at statement levelYPYDB2 for z 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 LOCKSPPYDB2 for z doesn't support USE AND KEEP EXCLUSIVE LOCKS in an INSERT statement.
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 elementLUWz/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)PYYLUW only supports this capability through CLI or JDBC.
Control statements
Language elementLUWz/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 elementLUWz/OS i Notes
Allow DDL statements in SQL programsYYY
Support Transaction Semantics for DDLYYY
Support 30 byte column namesYYYSome platforms support longer column names.
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)YPY
ALTER COLUMN (ALTAB)PPY
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 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 AUTONOMOUSYPYz/OS does not support AUTONOMOUS with CREATE PROCEDURE (External).
CREATE SCHEMAYPYz/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 (ALTAB, CRTAB, DGTTAB)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)PPYLUW does not support ON DELETE SET DEFAULT. 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 clause (CRTAB,ALTAB)YYY
CREATE SEQUENCE statementYYY
CREATE TRIGGER statement - BEFORE or AFTER trigger on a tableYYY
CREATE TRIGGER statement - INSTEAD OF trigger on a viewYPY
CREATE OR REPLACE TRIGGER statementYYY
CREATE TYPE (array) statement for ordinary arrayYYY
CREATE TYPE (distinct) statementYYY
CREATE VARIABLE statementPPPz/OS does not support defaults based on expressions, global variables. LUW does not support defaults based on array type. DB2 for i does not support defaults based on array type.
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 statementYPYz/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 elementLUWz/OS i Notes
GRANT (function privileges) statementYYY
GRANT (global variable privileges) statementYYY
GRANT (package privileges) statementYYY
GRANT (procedure privileges) statementYPYz/OS only supports procedure name to identify the procedure.
GRANT (sequence privileges) statementYYY
GRANT (table privileges) statementYYY
REVOKE (function privileges) statementYYY
REVOKE (global variable privileges) statementYYY
REVOKE (package privileges) statementYYY
REVOKE (procedure privileges) statementYPYz/OS only supports procedure name to identify the procedure.
REVOKE (sequence privileges) statementYYY
REVOKE (table privileges) statementYYY
TRANSFER OWNERSHIP statementYPY
Miscellaneous statements
Language elementLUWz/OS i Notes
BEGIN and END DECLARE SECTION statementYYY
DECLARE STATEMENT statementPYYStarting with LUW 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 statementYYY
SET PATH statementYYY
SET SCHEMA statementYYY
WHENEVER statementYYY
Host languages
Language elementLUWz/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=08092016