Contents


Develop portable data applications with common IBM SQL features

Write once and run everywhere

Comments

Many of IBM's database customers and independent software vendors have expressed interest in writing portable applications across multiple platforms. IBM® is committed to delivering SQL commonality, on database platforms implementing the Common SQL Engine, in a way that is common and portable and supports the ANSI/ISO SQL standards. These customers' application developers have asked us to provide them with a list of common SQL language capabilities as a quick reference.

Db2 is now the brand name for all of IBM's hybrid data management offerings, including products previously named dashDB. IBM Db2® and IBM Big SQL are designed with compatible SQL. Applications that access data stored on these products using compatible SQL via one of the IBM data server drivers should operate without change against data stored on the rest of the products. Specific detail and factors affecting compatibility are noted in the tables.

A feature might not be available on all common SQL engine 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 can accelerate development of an SQL feature on a specific Db2 product.

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, consult the SQL Reference for cross-platform development (PDF 4.2 MB) and the respective IBM SQL product SQL Reference books. Read the disclaimer before using the tables.

Disclaimer

  • This list is not necessarily complete. The selected features shown here are based on frequent customer interest. You might find other features when you reference SQL language Reference books for these products in detail.
  • This list is not intended to be a feature and function list for any of the Db2 products. A feature might already exist in one platform but in the process of rolling out on other Db2 platforms because new functionality is released all the time.
  • In general, Big SQL compatibility, guided by the constraints of the underlying Hadoop ecosystem, is focused on SELECT and does not offer full ACID transactional support.
  • Beyond the core SQL syntax, you need to consider the surrounding operating environment, including the collation, isolation level, and datatype.
  • Use the latest generally available product level for the greatest compatibility.

Products included

  • IBM Db2 Version 11.1
  • IBM Db2 Warehouse
  • IBM Big SQL Version 4.2

Key

YProduct includes full support.
PProduct includes partial support compared with other Common SQL Engine products; check product information.

Basic elements

Language elementDb2Db2 WarehouseBig SQLNotes
Identifiers - OrdinaryYYY
Identifiers - DelimitedYYY
Identifiers with support of lowercase letters and trailing underscoresYYY
Casting of Data Types - ImplicitPPPDb2 does not support implicit casting of LOBs to or from non-string types.
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
Function invocation with named argumentsYYY
Arithmetic operatorsYYY
bitwise operatorsYYY
String ConcatenationYYY
Scalar subselect as expressionYYY
Scalar fullselect as expressionYYY
Date/Time ArithmeticYYY
CASE expressionYYY
CAST specificationYYY
:: cast operatorYYY
XMLCAST specificationYYY
ROW CHANGE expressionYYY
Sequence reference (NEXT VALUE and PREVIOUS VALUE)YYY
OLAP ranking specification (RANK, DENSE_RANK)YYY
OLAP ranking specification (PERCENT_RANK)YYY
OLAP numbering specification (ROW_NUMBER)YYY
OLAP ordered specification (LAG, LEAD)YYY
OLAP aggregate function specification (CUME_DIST)YYY
OLAP aggregate function specification (FIRST_VALUE, LAST_VALUE)YYY
OLAP aggregate function specification (NTH_VALUE)YYY
OLAP aggregate function specification (NTILE)YYY
OLAP aggregate function specification (RATIO_TO_REPORT)YYY
OLAP aggregation specificationYYY
Untyped parameter markersYYY
Untyped NULLYYY
Untyped DEFAULTYYY
Flag product extensions to standard SQLPPPDb2 only supports flagging against SQL92 entry level SQL syntax.
Diagnostic AreaYYY
SQLSTATEYYY
SQLCODEYYY
Descriptor AreaYYY

Data types

Language elementDb2Db2 WarehouseBig SQLNotes
SMALLINTYYY
INT2YP-
INTEGERYYY
INT4YY-
BIGINTYYY
INT8YY-
DECIMALYYY
NUMERICYYY
REALYYYFor portability across platforms, do not use FLOAT(n).
FLOAT4YY-
DOUBLE or FLOATYYYFor portability across platforms, do not use FLOAT(n).
FLOAT8YY-
DECFLOATYYPIn Big SQL, DECFLOAT data types can be used in most SQL constructs, except in Hadoop and HBase tables since this type is not supported in the Hadoop ecosystem.
CHARACTERYYY
VARCHARYYY
FOR BIT DATA with CHAR or VARCHARYYY
CLOBYYP
FOR SBCS or MIXED DATA with CHAR, VARCHAR, or CLOBPPP
NCHARPP-
NVARCHARPP-
NCLOBPP-
BINARYYYPFor Hadoop and HBase tables, BINARY data type is a synonym for VARBINARY. There is no fixed length BINARY type in Hadoop.
VARBINARYYYP
BLOBYYP
GRAPHICYY-
VARGRAPHICYY-
DBCLOBYY-
DATEYYY
TIMEYYPIn Big SQL, TIME data types can be used in most SQL constructs, except in Hadoop and HBase tables since this type is not supported in the Hadoop ecosystem.
TIMESTAMPYYY
TIMESTAMP with fractional seconds precision 0 to 12YYPIn Big SQL, the maximum fractional seconds precision is 9.
XMLYYPIn Big SQL, XML data types can be used in most SQL constructs, except in Hadoop and HBase tables since this type is not supported in the Hadoop ecosystem.
BOOLEANYYP
BPCHARPPP
User-defined distinct typesYYY
User-defined ordinary array typesYYY
User-defined associative array typesYYY
User-defined cursor typesYYY
User-defined row typesYYY
User-defined reference typesYYY
User-defined structured typesYYY

Special registers

Language elementDb2Db2 WarehouseBig SQLNotes
CURRENT CLIENT_ACCTNGYYY
CURRENT CLIENT_APPLNAMEYYY
CURRENT CLIENT_USERIDYYY
CURRENT CLIENT_WRKSTNNAMEYYY
CURRENT DATE or CURRENT_DATEYYY
CURRENT DBPARTITIONNUMYYY
CURRENT DECFLOAT ROUNDING MODEYYY
CURRENT DEFAULT TRANSFORM GROUPYYY
CURRENT DEGREEYYY
CURRENT EXPLAIN MODEYYY
CURRENT EXPLAIN SNAPSHOTYYY
CURRENT FEDERATED ASYNCHRONYYYY
CURRENT IMPLICIT XMLPARSE OPTIONYYY
CURRENT ISOLATIONYYY
CURRENT LOCALE LC_MESSAGESYYY
CURRENT LOCALE LC_TIMEYYY
CURRENT LOCK TIMEOUTYYY
CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATIONYYY
CURRENT MDC ROLLOUT MODEYYY
CURRENT MEMBERYYY
CURRENT NODEYYY
CURRENT OPTIMIZATION PROFILEYYY
CURRENT PACKAGE PATHYYY
CURRENT PATH or CURRENT_PATHYYY
CURRENT QUERY OPTIMIZATIONYYY
CURRENT REFRESH AGEYYY
CURRENT SCHEMA or CURRENT_SCHEMAYYY
CURRENT SERVERYYY
CURRENT SQL_CCFLAGSYYY
CURRENT TEMPORAL BUSINESS_TIMEYYY
CURRENT TEMPORAL SYSTEM_TIMEYYY
CURRENT TIME or CURRENT_TIMEYYY
CURRENT TIMESTAMP or CURRENT_TIMESTAMPYYY
CURRENT TIMEZONEYYY
CURRENT USER or CURRENT_USERYYY
SESSION_USERYYY
SYSTEM_USERYYY
USERYYY

Global variables

Language elementDb2Db2 WarehouseBig SQLNotes
CLIENT_HOSTYYY
CLIENT_IPADDRYYY
CLIENT_ORIGUSERIDYYY
CLIENT_USRSECTOKENYYY
MON_INTERVAL_IDYYY
PACKAGE_NAMEYYY
PACKAGE_SCHEMAYYY
PACKAGE_VERSIONYYY
ROUTINE_MODULEYYY
ROUTINE_SCHEMAYYY
ROUTINE_SPECIFIC_NAMEYYY
ROUTINE_TYPEYYY
TRUSTED_CONTEXTYYY

Predicates

Language elementDb2Db2 WarehouseBig SQLNotes
Basic ( = <> < > <= >= ) excluding LOB types and row value expressionsYYY
Basic ( = <> < > <= >= ) LOB typesPPPDb2 can compare LOB strings that have an actual length less than 32672 bytes.
Basic ( = <> ) row value expressionsYYY
Basic ( < > <= >= ) row value expressionsYYY
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
ARRAY_EXISTSYYY
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
ISNULL and ISNOTNULL (no scalar subquery)YYY
LIKE with host variable or string constant as patternYYY
LIKE with expression for pattern and escapeYYY
OVERLAPSYYY
REGEXP_LIKEYYY
TYPEYYY
VALIDATED without specified XML schemaYYY
VALIDATED with specified XML schemaYYY
XMLEXISTS passing explicit variablesYYY
XMLEXISTS implicitly passing all columnsYYY

Aggregate functions

Language elementDb2Db2 WarehouseBig SQLNotes
ARRAY_AGG (ordinary array)YYY
ARRAY_AGG (associative array)YYY
AVGYYY
CORRELATION or CORRYYY
COUNTYYY
COUNT_BIGYYY
COVAR_POPYYY
COVARIANCE or COVARYYY
COVARIANCE_SAMP or COVAR_SAMPYYY
CUME_DISTYYY
GROUPINGYYY
LISTAGGPPPDb2 does not support the ON OVERFLOW clause.
MAXYYY
MEDIANYYY
MINYYY
PERCENTILE_CONT or PERCENTILE_DISCYYY
PERCENT_RANKYYY
REGR_AVGXYYY
REGR_AVGYYYY
REGR_COUNTYYY
REGR_INTERCEPT or REGR_ICPTYYY
REGR_R2YYY
REGR_SLOPEYYY
REGR_SXXYYY
REGR_SXYYYY
REGR_SYYYYY
STDDEVYYY
STDDEV_POPYYY
STDDEV_SAMPYYY
SUMYYY
VARIANCE or VARYYY
VARIANCE_POP or VAR_POPYYY
VARIANCE_SAMP or VAR_SAMPYYY
XMLAGGYYY
XMLGROUPYYY

Scalar functions

Language elementDb2Db2 WarehouseBig SQLNotes
ABSVAL or ABSYYY
ACOSYYY
ADD_DAYSYYY
ADD_HOURSYYY
ADD_MINUTESYYY
ADD_MONTHSYYY
ADD_SECONDSYYY
ADD_YEARSYYY
AGEYYY
ARRAY_DELETEYYY
ARRAY_FIRSTYYY
ARRAY_LASTYYY
ARRAY_NEXTYYY
ARRAY_PRIORYYY
ARRAY_TRIMYYY
ASCIIYYY
ASINYYY
ATANYYY
ATAN2YYY
ATANHYYY
BIGINTYYY
BINARYYYY
BIT Manipulation functions (BITAND, BITANDNOT, BITNOT, BITOR, BITXOR)YYY
BLOBYYY
BTRIMYYY
CARDINALITYYYY
CEIL or CEILINGYYY
CHAR (no string units)YYY
CHARACTER_LENGTH or CHAR_LENGTH (no string units)YYY
CHARACTER_LENGTH or CHAR_LENGTH with string unitsYYY
CHRYYY
CLOB (no string units)PPP
COALESCE (or VALUE)YYY
COLLATION_KEYYYY
COLLATION_KEY_BITYYY
COMPARE_DECFLOATYYY
CONCATYYY
CONTAINSYYY
COSYYY
COSHYYY
COTYYY
CURSOR_ROWCOUNTYYY
DATAPARTITIONNUMYYY
DATEYYY
DATE_PARTYYY
DATE_TRUNCYYY
DAYYYY
DAYNAMEYYY
DAYOFMONTHYYY
DAYOFWEEKYYY
DAYOFWEEK_ISOYYY
DAYOFYEARYYY
DAYSYYY
DAYS_BETWEENYYY
DAYS_TO_END_OF_MONTHYYY
DBCLOB (no string units)PPP
DBPARTITIONNUM (also see NODENUMBER)YYY
DECFLOATYYY
DECFLOAT_FORMATYYY
DECIMAL or DECYYY
DECODEYYY
DECRYPT_BINYYYDeprecated Db2 11.1.
DECRYPT_CHARYYYDeprecated Db2 11.1.
DEGREESYYY
DEREFYYY
DIFFERENCEYYY
DIGITSYYY
DOUBLE or DOUBLE_PRECISIONYYY
EMPTY_BLOB, EMPTY_CLOB, EMPTY_DBCLOBYYY
ENCRYPTYYYDeprecated Db2 11.1.
EVENT_MON_STATEYYY
EXPYYY
EXTRACTYYY
FIRST_DAYYYY
FLOATYYY
FLOORYYY
FROM_UTC_TIMESTAMPYYY
GENERATE_UNIQUEYYY
GETHINTYYYDeprecated Db2 11.1.
GRAPHIC (no string units)PPP
GREATESTYYY
HASHYYY
HASH4YYY
HASH8YYY
HASHEDVALUE (also see PARTITION)YYY
HEXYYY
HEXTORAWYYY
HOURYYY
HOURS_BETWEENYYY
IDENTITY_VAL_LOCALYYY
INITCAPYYY
INSERT (no string units)PPP
INSERT with string unitsYYY
INSTR (also see LOCATE_IN_STRING)YYY
INSTR2 (also see LOCATE_IN_STRING)YYY
INSTR4 (also see LOCATE_IN_STRING)YYY
INSTRB (also see LOCATE_IN_STRING)YYY
INTEGER or INTYYY
INTNAND, INTNOR, INTNXOR, and INTNNOTYYY
JULIAN_DAYYYY
LAST_DAYYYY
LCASE or LOWERYYY
LCASE or LOWER (locale sensitive)YYY
LEASTYYY
LEFT (no string units)PPP
LEFT with string unitsYYY
LENGTH (no string units)YYY
LENGTH with string unitsYYY
LENGTH2YYY
LENGTH4YYY
LENGTHBYYY
LNYYY
LOCATE (no string units)PPP
LOCATE with string unitsYYY
LOCATE_IN_STRING or INSTR (no string units)PPP
LOCATE_IN_STRING or INSTR with string unitsYYY
LOGYYY
LOG10YYY
LPADPPPDb2 has limited support for a CLOB argument.
LTRIM with 1 argumentPPPDb2 has limited support for a CLOB argument.
LTRIM with 2 argumentsPPP
MAXYYY
MAX_CARDINALITYYYY
MICROSECONDYYY
MIDNIGHT_SECONDSYYY
MINYYY
MINUTEYYY
MINUTES_BETWEENYYY
MODYYY
MONTHYYY
MONTHNAMEYYY
MONTHS_BETWEENYYY
MQPUBLISHY-Y
MQREAD with up to 2 argumentsY-Y
MQREADCLOB with up to 2 argumentsY-Y
MQRECEIVEY-Y
MQRECEIVECLOBY-Y
MQSENDY-Y
MQSUBSCRIBEY-Y
MQUNSUBSCRIBEY-Y
MULTIPLY_ALTYYY
NCHARYYY
NCLOBYYY
NEXT_DAYYYY
NEXT_MONTHYYY
NEXT_QUARTERYYY
NEXT_WEEKYYY
NEXT_YEARYYY
NODENUMBERYYY
NORMALIZE_DECFLOATYYY
NOWYYY
NULLIFYYY
NVARCHARYYY
NVLYYY
NVL2YYY
OCTET_LENGTHYYY
OVERLAY (no string units)PPP
OVERLAY with string unitsYYY
PARAMETERYYY
PARTITIONYYY
POSITION (no string units)PPP
POSITION with string unitsYYY
POSSTRYYY
POWYYY
POWERYYY
QUANTIZEYYY
QUARTERYYY
RADIANSYYY
RAISE_ERRORYYY
RANDYYY
RANDOMYYY
RAWTOHEXYYY
REALYYY
REC2XMLYYY
REGEXP_COUNTYYY
REGEXP_EXTRACTYYY
REGEXP_INSTRYYY
REGEXP_MATCH_COUNTYYY
REGEXP_REPLACEYYY
REGEXP_SUBSTRYYY
REPEATYYY
REPLACEPPPDb2 does not support LOB arguments.
REPLACE with 3rd argument optionalPPPDb2 does not support LOB arguments.
RIDYYY
RID_BITYYY
RIGHT (no string units)PPP
RIGHT with string unitsYYY
ROUND (numeric)YYY
ROUND (numeric) with 2nd argument optionalYYY
ROUND (datetime)YYY
ROUND_TIMESTAMPYYY
RPADPPPDb2 has limited support for a CLOB argument.
RTRIM with 1 argumentPPPDb2 has limited support for a CLOB argument.
RTRIM with 2 argumentsPPPDb2 has limited support for a CLOB argument.
SCOREYYY
SECLABELYYY
SECLABEL_BY_NAMEYYY
SECLABEL_TO_CHARYYY
SECONDYYY
SECONDS_BETWEENYYY
SIGNYYY
SINYYY
SINHYYY
SMALLINTYYY
SOUNDEXYYY
SPACEPPPDb2 support is limited to a length of 4000.
SQRTYYY
STRIPYYY
STRLEFTYYY
STRPOSYYY
STRRIGHTYYY
SUBSTRYYY
SUBSTR2YYY
SUBSTR4YYY
SUBSTRBYYY
SUBSTRING (no string units)PPP
SUBSTRING with string unitsYYY
TABLE_NAMEYYY
TABLE_SCHEMAYYY
TANYYY
TANHYYY
THIS_MONTHYYY
THIS_QUARTERYYY
THIS_WEEKYYY
THIS_YEARYYY
TIMEYYY
TIMESTAMPYYY
TIMESTAMPDIFFYYY
TIMESTAMP_FORMATYYY
TIMESTAMP_ISOYYY
TOTALORDERYYY
TO_CHAR (character)YYY
TO_CHAR (decfloat)YYY
TO_CHAR (timestamp)YYY
TO_CLOBYYY
TO_DATEYYY
TO_HEXYYY
TO_NCHARYYY
TO_NCLOBYYY
TO_NUMBERYYY
TO_SINGLE_BYTEYYY
TO_TIMESTAMPYYY
TO_UTC_TIMESTAMPYYY
TRANSLATEYY-
TRIMPPPDb2 has limited support for a CLOB argument.
TRIM_ARRAYYYY
TRUNCATE or TRUNC (datetime)YYY
TRUNCATE or TRUNC (numeric) with 2nd argument optionalYYY
TRUNCATE or TRUNC (numeric)YYY
TRUNC_TIMESTAMPYYY
TYPE_IDYYY
TYPE_NAMEYYY
TYPE_SCHEMAYYY
UCASE or UPPERYYY
UCASE or UPPER (locale sensitive)YYY
VALUEYYY
VARBINARYYYY
VARCHAR (no string units)YYY
VARCHAR_BIT_FORMATYYY
VARCHAR_FORMAT (character)YYY
VARCHAR_FORMAT (decfloat)YYY
VARCHAR_FORMAT (timestamp)YYY
VARCHAR_FORMAT_BITYYY
VARGRAPHIC (no string units)PPP
VERIFY_GROUP_FOR_USERYYY
VERIFY_ROLE_FOR_USERYYY
VERIFY_TRUSTED_CONTEXT_ROLE_FOR_USERYYY
WEEKYYY
WEEKS_BETWEENYYY
WEEK_ISOYYY
WIDTH_BUCKETYYY
WRAPYYY
XML2CLOBYYY
XMLATTRIBUTESYYY
XMLCOMMENTYYY
XMLCONCATYYY
XMLDOCUMENTYYY
XMLELEMENTYYY
XMLFORESTYYY
XMLNAMESPACESYYY
XMLPARSEYYY
XMLPIYYY
XMLQUERY passing named variablesYYY
XMLQUERY implicitly passing all columnsYYY
XMLROWYYY
XMLSERIALIZEYYY
XMLTEXTYYY
XMLVALIDATEYYY
XMLXSROBJECTIDYYY
XSLTRANSFORMYYY
YEARYYY
YEARS_BETWEENYYY
YMD_BETWEENYYY

Table functions

Language elementDb2Db2 WarehouseBig SQLNotes
BASE_TABLEYYY
MQREADALLY-Y
MQREADALLCLOBY-Y
MQRECEIVEALLY-Y
MQRECEIVEALLCLOBY-Y
XMLTABLE passing named variablesYYY
XMLTABLE implicitly passing all columnsYYY

Procedures

Language elementDb2Db2 WarehouseBig SQLNotes
CREATE_WRAPPEDYYY
XSR_ADDSCHEMADOCYYY
XSR_COMPLETEYYY
XSR_DTDYYY
XSR_EXTENTITYYYY
XSR_REGISTERYYY

Queries

Language elementDb2Db2 WarehouseBig SQLNotes
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 without correlation clauseYYY
FROM clause with nested table expression preceded by TABLE keywordYYY
FROM clause with nested table expression preceded by LATERAL 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 named columns JOINYYY
FROM clause with TABLESAMPLE clauseYYY
FROM clause with table function reference with correlation clauseYYY
FROM clause with table function reference without correlation clauseYYY
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 collection derived table (UNNEST of associative array)YYY
FROM clause with external table reference-Y-
FROM clause with DELETE statementYYY
FROM clause with INSERT statementYYY
FROM clause with UPDATE 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
Subselect with isolation clauseYYY
Fullselect with UNION or UNION ALLYYY
Fullselect with INTERSECT or INTERSECT ALLYYY
Fullselect with EXCEPT or EXCEPT ALLYYY
Fullselect with a table value constructor (VALUES clause)YYY
Fullselect with isolation clauseYYP
Select-statement starting with common table expressionYYY
Select-statement using recursive common table expressionYYY
Select-statement using CONNECT BYPPPDb2 does not support the CONNECT_BY_ISCYCLE or CONNECT_BY_ISLEAF pseudocolumns.
Select-statement with isolation clauseYYP
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 FIRST clauseYYY
LIMIT and OFFSET clausesPPPNot supported in select-statement.
Period specificationYYY

Basic statements

Language elementDb2Db2 WarehouseBig SQLNotes
Static CALL statementYYY
Static CALL statement with host variable name for procedure nameYYY
Dynamic CALL statementYYY
CALL using named argumentsYYY
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
SELECT INTO statement with LIMIT and OFFSET clausePPP
SET variable statementYYY
VALUES INTO statementYYY

Data change statements

Language elementDb2Db2 WarehouseBig SQLNotes
Searched DELETE statementYYP
DELETE statement with isolation clauseYYP
DELETE statement with FETCH FIRST clauseYYP
DELETE statement with OFFSET clauseYYP
DELETE statement with ORDER BY clauseYYP
INSERT statement with single row VALUES clauseYYY
INSERT statement with multiple row VALUES clauseYYY
INSERT statement with fullselectYYY
INSERT statement with isolation clauseYYY
INSERT statement using row value expressionYYY
MERGE statement using table-reference inputYYP
MERGE statement with isolation clauseYYP
TRUNCATE statement with IMMEDIATE clauseYYY
Searched UPDATE statementYYP
UPDATE statement with isolation clauseYYP
UPDATE statement with FETCH FIRST clauseYYP
UPDATE statement with OFFSET clauseYYP
UPDATE statement with ORDER BY clauseYYP

Cursor-oriented operations

Language elementDb2Db2 WarehouseBig SQLNotes
ALLOCATE CURSOR statementPPPDb2 supports the statement only in SQL procedures.
ASSOCIATE LOCATORS statementPPPDb2 supports the statement only in SQL procedures.
CLOSE statementYYY
DECLARE CURSOR statement (see other cursor elements)YYY
Declare INSENSITIVE SCROLL cursorPPPDb2 only supports this capability through CLI or JDBC.
Declare SENSITIVE STATIC SCROLL cursorPPPDb2 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 statementYYP
FETCH statement with no cursor orientation (implicit NEXT)YYY
Specify FETCH SENSITIVE or FETCH INSENSITIVEPPP
FETCH statement with NEXT, PRIOR, FIRST, LAST, BEFORE, AFTER, CURRENT, or RELATIVEPPPDb2 only supports these fetch features through CLI or JDBC.
FETCH statement with ABSOLUTEPPPDb2 only supports this fetch feature through CLI or JDBC.
OPEN statementYYY
Positioned UPDATE statementYYP

Connection and transaction control

Language elementDb2Db2 WarehouseBig SQLNotes
Isolation Level Specification at package levelYYY
Isolation Level Specification at statement levelYYY
RR (SERIALIZABLE in SQL2011 Standard)YYP
RS (REPEATABLE READ in SQL2011 Standard)YYP
CS (READ COMMITTED in SQL2011 Standard)YYP
UR (READ UNCOMMITTED in SQL2011 Standard)YYY
Isolation clause with USE AND KEEP EXCLUSIVE LOCKSYYY
Isolation clause with USE AND KEEP SHARED|UPDATE LOCKSYYY
COMMIT statementYYY
CONNECT statement (1 site per transaction)YYY
CONNECT statement (2+ sites per transaction) (also referred to as Two Phase Commit)YYY
DISCONNECT statementYYY
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 elementDb2Db2 WarehouseBig SQLNotes
Compound statement (dynamic)YYY
DESCRIBE output statement without a USING clauseYYY
DESCRIBE INPUT statementYYY
EXECUTE statementYYY
EXECUTE IMMEDIATE statement that specifies a variableYYY
EXECUTE IMMEDIATE statement that specifies an expressionYYY
PREPARE statement that specifies FROM variableYYY
PREPARE statement that specifies FROM expressionYYY
PREPARE statement that specifies cursor options (ATTRIBUTES clause)PPPDb2 only supports this capability through CLI or JDBC.

Control statements

Language elementDb2Db2 WarehouseBig SQLNotes
Control statements in SQL proceduresYYY
Control statements in SQL scalar functionsYYY
Control statements in SQL table functionsYYY
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
PIPE statementYYY
REPEAT statementYYY
RESIGNAL statementYYY
RETURN statement from SQL procedureYYY
RETURN statement from SQL functionYYY
SIGNAL statementYYY
WHILE statementYYY

Data definition

Language elementDb2Db2 WarehouseBig SQLNotes
Allow DDL statements in SQL programsYYY
Support Transaction Semantics for DDLYYY
Support 30 byte column namesYYY
Support 128 byte column namesYYY
Support 128 byte object namesYYY
ALTER FUNCTION statementPPP
ALTER MASK statementYYY
ALTER MODULE statementYYY
ALTER PERMISSION statementYYY
ALTER PROCEDURE statementPPP
ALTER SEQUENCE statementYYY
ALTER TABLE statement (see other elements marked ALTAB)YYY
ALTER COLUMN (ALTAB)PPP
DROP COLUMN (ALTAB)YYY
RENAME COLUMN (ALTAB)YYY
ATTACH and DETACH PARTITION (ALTAB)YYY
ACTIVATE NOT LOGGED (ALTAB)YYY
ALTER TRIGGER statementYYY
ALTER TRUSTED CONTEXT statementYYY
ALTER XSROBJECT statementYYY
COMMENT statementYYY
CREATE ALIAS (table or view) statementYYY
CREATE ALIAS (module) statementYYY
CREATE ALIAS (sequence) statementYYY
CREATE OR REPLACE ALIAS statementYYY
CREATE PUBLIC ALIAS (table or view) statementYYY
CREATE PUBLIC ALIAS (module) statementYYY
CREATE PUBLIC ALIAS (sequence) statementYYY
CREATE OR REPLACE PUBLIC ALIAS statementYYY
CREATE FUNCTION (external scalar) statementYYY
CREATE OR REPLACE FUNCTION (external scalar) statementYYY
CREATE FUNCTION (sourced) statementYYY
CREATE FUNCTION (SQL scalar) statementYYY
CREATE OR REPLACE FUNCTION (SQL scalar) statementYYY
CREATE FUNCTION (external table) statementYYY
CREATE OR REPLACE FUNCTION (external table) statementYYY
CREATE FUNCTION (SQL table) statementYYY
CREATE OR REPLACE FUNCTION (SQL table) statementYYY
CREATE FUNCTION (SQL row) statementYYY
CREATE OR REPLACE FUNCTION (SQL row) statementYYY
CREATE FUNCTION (external generic table) statementYYY
CREATE FUNCTION statement with parameter defaultsYYY
CREATE FUNCTION (aggregate interface) statementYYY
CREATE OR REPLACE FUNCTION (aggregate interface) statementYYY
CREATE GLOBAL TEMPORARY TABLE statementYYY
CREATE INDEX over column listYYP
CREATE UNIQUE INDEX over column listYYP
CREATE INDEX on expressionYYY
CREATE INDEX on XMLPATTERNYYY
CREATE MASK statementYYY
CREATE OR REPLACE MASK statementYYY
CREATE MODULE statementYYY
CREATE OR REPLACE MODULE statementYYY
CREATE PERMISSION statementYYY
CREATE OR REPLACE PERMISSION statementYYY
CREATE PROCEDURE (external) statementYYY
CREATE OR REPLACE PROCEDURE (external) statementYYY
CREATE PROCEDURE (SQL) statementYYY
CREATE OR REPLACE PROCEDURE (SQL) statementYYY
CREATE PROCEDURE statement for existing name with different number of parametersYYY
CREATE PROCEDURE statement with parameter defaultsYYY
CREATE PROCEDURE statement with AUTONOMOUSYYY
CREATE SCHEMAYYY
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)YYP
NOT ENFORCED clause on CHECK constraint (ALTAB, CRTAB)YYY
DATA CAPTURE clause (ALTAB, CRTAB)YYP
DEFAULT clause with no specified value (ALTAB, CRTAB, DGTTAB)YYY
DEFAULT clause with specified value (ALTAB, CRTAB, DGTTAB)YYY
IMPLICITLY HIDDEN clause (ALTAB, CRTAB)YYP
VOLATILE clause (ALTAB, CRTAB)YYP
Generated column: IDENTITY clause (ALTAB, CRTAB, DGTTAB)YYP
Generated column: ROW CHANGE TIMESTAMP clause (ALTAB, CRTAB)YYP
Generated column: ROW BEGIN or ROW END clause (ALTAB, CCRTAB)YYP
Generated column: TRANSACTION START ID clause (ALTAB, CCRTAB)YYP
Generated column: Deterministic expression (ALTAB, CCRTAB)PPP
PRIMARY KEY constraint clause (ALTAB, CRTAB)YYP
UNIQUE constraint clause (ALTAB, CRTAB)YYP
Automatic index generation for unique constraints (ALTAB, CRTAB)YYP
Referential constraint clause (ALTAB, CRTAB)YYP
REFERENCES clause specifying ON DELETE (ALTAB, CRTAB)PPPDb2 does not support ON DELETE SET DEFAULT.
REFERENCES clause specifying ON UPDATE (ALTAB, CRTAB)YYP
REFERENCES clause specifying NOT ENFORCED clause (ALTAB, CRTAB)YYY
Self referencing tables can be specified without requiring use of ALTER TABLEYYY
Functional dependency clause (ALTAB, CRTAB)YYY
Refresh immediate system maintained MQT (CRTAB,ALTAB)YYY
Refresh deferred system maintained MQT (CRTAB,ALTAB)YYY
User maintained MQT (CRTAB,ALTAB)YYY
PERIOD clause (CRTAB,ALTAB)YYP
CREATE EXTERNAL TABLE statement-Y-
CREATE ROLE statementYYY
CREATE SEQUENCE statementYYY
CREATE OR REPLACE SEQUENCE statementYYY
CREATE TRIGGER statement - BEFORE or AFTER trigger on a tableYYY
CREATE TRIGGER statement - INSTEAD OF trigger on a viewYYY
CREATE TRIGGER statement - multiple trigger eventsYYY
CREATE OR REPLACE TRIGGER statementYYY
CREATE TRUSTED CONTEXT statementYYY
CREATE TYPE (array) statement for ordinary arrayYYY
CREATE TYPE (array) statement for associative arrayYYY
CREATE OR REPLACE TYPE (array) statementYYY
CREATE TYPE (cursor) statementYYY
CREATE OR REPLACE TYPE (cursor) statementYYY
CREATE TYPE (distinct) statementYYY
CREATE TYPE (distinct) statement with weak type rulesYYY
CREATE TYPE (row) statementYYY
CREATE OR REPLACE TYPE (row) statementYYY
CREATE TYPE (structured) statementYYY
CREATE VARIABLE statementPPPDb2 does not support defaults based on array type.
CREATE VARIABLE statement (CONSTANT)YYY
CREATE VARIABLE statement (default based on cursor-value-constructor)YYY
CREATE OR REPLACE VARIABLE statementPPP
CREATE VIEW statement using fullselect without CHECK OPTION clauseYYY
CREATE VIEW WITH CASCADED CHECK OPTIONYYY
CREATE VIEW WITH LOCAL CHECK OPTIONYYY
CREATE VIEW WITH ROW MOVEMENTYYY
CREATE OR REPLACE VIEW statementYYY
DECLARE GLOBAL TEMPORARY TABLE statement (see other elements marked DGTTAB)YYY
CREATE TEMPORARY TABLE (alternative to DECLARE GLOBAL TEMPORARY TABLE)YYY
DROP statement with specified RESTRICT or CASCADE behaviourPPP
DROP ALIAS statementYYY
DROP FUNCTION statementYYY
DROP INDEX statementYYY
DROP MASK statementYYY
DROP MODULE statementYYY
DROP PACKAGE statementYYY
DROP PERMISSION statementYYY
DROP PROCEDURE statementYYY
DROP SCHEMA statementPPPDb2 does not support DROP SCHEMA CASCADE.
DROP SEQUENCE statementYYY
DROP TABLE statementYYY
DROP TRIGGER statementYYY
DROP TYPE statementYYY
DROP VARIABLE statementYYY
DROP VIEW statementYYY
DROP XSROBJECT statementYYY
RENAME TABLE statementPPP
RENAME INDEX statementYYY
Obfuscation of SQL objects written in SQLYYY

Authorization

Language elementDb2Db2 WarehouseBig SQLNotes
GRANT (function privileges) statementYYY
GRANT (global variable privileges) statementYYY
GRANT (module privileges) statementYYY
GRANT (package privileges) statementYYY
GRANT (procedure privileges) statementYYY
GRANT (role) statementYYY
GRANT (schema privileges) statementYYY
GRANT (sequence privileges) statementYYY
GRANT (table privileges) statementYYY
GRANT (XSR object privileges) statementYYY
REVOKE (function privileges) statementYYY
REVOKE (global variable privileges) statementYYY
REVOKE (module privileges) statementYYY
REVOKE (package privileges) statementYYY
REVOKE (procedure privileges) statementYYY
REVOKE (role) statementYYY
REVOKE (schema privileges) statementYYY
REVOKE (sequence privileges) statementYYY
REVOKE (table privileges) statementYYY
REVOKE( XSR object privileges) statementYYY
SET SESSION AUTHORIZATION statementYYY
TRANSFER OWNERSHIP statementYYY

Miscellaneous statements

Language elementDb2Db2 WarehouseBig SQLNotes
BEGIN and END DECLARE SECTION statementYYY
DECLARE STATEMENT statementPPPStarting with Db2 9.7, supports the statement only in compound SQL (compiled) statements.
FREE LOCATOR statementYYY
INCLUDE statementYYY
LOCK TABLE statementYYP
REFRESH TABLE statementYYY
SET COMPILATION ENVIRONMENT statementYYY
SET CONNECTION statementYYY
SET CURRENT DECFLOAT ROUNDING MODE statementPPP
SET CURRENT DEFAULT TRANSFORM GROUP statementYYY
SET CURRENT DEGREE statementYYY
SET CURRENT EXPLAIN MODEYYY
SET CURRENT EXPLAIN SNAPSHOT statementYYY
SET CURRENT FEDERATED ASYNCHRONY statementYYY
SET CURRENT IMPLICIT XMLPARSE OPTION statementYYY
SET CURRENT ISOLATION statementYYY
SET CURRENT LOCALE LC_MESSAGES statementYYY
SET CURRENT LOCALE LC_TIME statementYYY
SET CURRENT LOCK TIMEOUT statementYYY
SET CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION statementYYY
SET CURRENT MDC ROLLOUT MODE statementYYY
SET CURRENT OPTIMIZATION PROFILE statementYYY
SET CURRENT PACKAGE PATH statementYYY
SET CURRENT PACKAGESET statementPPP
SET CURRENT QUERY OPTIMIZATION statementYYY
SET CURRENT REFRESH AGE statementYYY
SET CURRENT SQL_CCFLAGS statementYYY
SET CURRENT TEMPORAL BUSINESS_TIME statementYYY
SET CURRENT TEMPORAL SYSTEM_TIME statementYYY
SET ENCRYPTION PASSWORD statementYYYDeprecated Db2 11.1.
SET EVENT MONITOR STATE statementYYY
SET INTEGRITY statementYYP
SET PASSTHRU statementYYY
SET PATH statementYYY
SET ROLE statementYYY
SET SCHEMA statementYYY
SET SERVER OPTION statementYYY
SET SESSION AUTHORIZATION statementYYY
SET USAGE LIST STATE statementYYY
WHENEVER statementYYY

Host languages

Language elementDb2Db2 WarehouseBig SQLNotes
C or C++YYY
COBOLYYY
FORTRANYYY
Java (SQLJ)YYY
PL/IYYY
REXXYYY

Netezza compatibility

Language elementDb2Db2 WarehouseBig SQLNotes
Double-dot notationPPP
** exponential operatorPPP
# bitwise XOR operatorPPP
GROUP BY SELECT clause columnsPPP
Expressions referencing column aliasPPP
NZPLSQL supportPPP
CREATE TABLE AS using Netezza syntaxYYY
CREATE TEMP TABLE (alternate syntax for DGTT)YYY

Disclaimer

This list is not necessarily complete. Selected features are based on frequent customer interest. You can find more common features by examining the SQL language Reference books for products in detail.

This list is not intended to be a feature and function list for any of the Db2 products. A feature might already appear on one platform and be in the process of rolling out on other Db2 platforms because new functionality continues to be shipped all the time. Because of the 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=1048296
ArticleTitle=Develop portable data applications with common IBM SQL features
publish-date=08092017