Selected common SQL features for developers of portable DB2 applications

Are you writing SQL applications that need to be portable across platforms? Here's the information you need to make sure your applications are portable. The tables in this article summarize the common SQL application features and make it easy for you to develop applications using SQL that is portable across the DB2® family, including DB2 for z/OS®, DB2 for i®, and DB2 for Linux®, UNIX®, and Windows®.

Share:

Staff, IBM, Staff, IBM

This article is brought to you by IBM Staff.



September 2014 (First published 28 March 2013)

Also available in Spanish

Introduction

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 3.9 MB) and the respective DB2 product SQL Reference books.

Please read the disclaimer before using the tables.


Products included

  • DB2 10.1 FP2 for Linux, UNIX, and Windows
  • DB2 10.1 for z/OS
  • DB2 7.1 TR6 for IBM i

Contents

Table 1. Key
YProduct includes full support.
PProduct includes partial support; check product information.
Table 2. Basic elements
Language elementLUWz/OSiNotes
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
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
Table 3. 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
BLOBYYY
GRAPHICYYY
VARGRAPHICYYY
DBCLOBYYY
DATEYYY
TIMEYYY
TIMESTAMPYYY
XMLYYP
User-defined distinct typesYYY
Table 4. 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 TIME or CURRENT_TIMEYYY
CURRENT TIMESTAMP or CURRENT_TIMESTAMPYYY
CURRENT TIMEZONEYYY
SESSION_USERYYY
USERYYY
Table 5. Predicates
Language elementLUWz/OS i Notes
Basic ( = <> < > <= >= ) excluding LOB typesYYY
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
EXISTS (subselect)YYY
IN followed by list of expressionsYYY
IN followed by single column subselectYYY
IS NULL (no scalar subquery)YYY
LIKE with host variable or string constant as patternYYY
LIKE with expression for pattern and escapeYYY
Table 6. Aggregate functions
Language elementLUWz/OS i Notes
AVGYYY
COUNTYYY
COUNT_BIGYYY
MAXYYY
MINYYY
STDDEVYYY
SUMYYY
VARIANCE or VARYYY
XMLAGGYYY
Table 7. Scalar functions
Language elementLUWz/OS i Notes
ABSVAL or ABSYYY
ACOSYYY
ADD_MONTHSYYY
ASCIIYYY
ASINYYY
ATANYYY
ATANHYYY
ATAN2YYY
BIGINTYYY
BIT Manipulation functions (BITAND, BITANDNOT, BITNOT, BITOR, BITXOR)YYY
BLOBYYY
CEIL or CEILINGYYY
CHAR (no string units)YYY
CHARACTER_LENGTH or CHAR_LENGTH (no string units)PPYFor LUW, the argument CODEUNITS16 is required to achieve equivalent functionality for most data. For z/OS, the argument CODEUNITS16 is required to achieve equivalent functionality for most data.
CLOB (no string units)YYY
COALESCE (or VALUE)YYY
COMPARE_DECFLOATYYY
CONCATYYY
CONTAINSYYY
COSYYY
COSHYYY
DATEYYY
DAYYYY
DAYNAMEYPYz/OS supports function as a sample in schema DSN8.
DAYOFWEEKYYY
DAYOFWEEK_ISOYYY
DAYOFYEARYYY
DAYSYYY
DBCLOB (no string units)YYY
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
EXTRACTYYY
FLOATYYY
FLOORYYY
GENERATE_UNIQUEYYY
GETHINTYYY
GRAPHIC (no string units)PYY
HEXYYY
HOURYYY
IDENTITY_VAL_LOCALYYY
INSERT (no string units)YYY
INTEGER or INTYYY
JULIAN_DAYYYY
LAST_DAYYYY
LCASE or LOWERYYY
LEFT (no string units)PYY
LENGTH (no string units)YYY
LNYYY
LOCATE (no string units)YYY
LOG10YYY
LTRIM (with 1 argument)PPYLUW does not support a CLOB argument. z/OS does not support a CLOB argument.
MAXYYY
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
NULLIFPPYLUW does not support LOB arguments. z/OS does not support LOB arguments.
POSITION (no string units)PPYFor LUW, the argument CODEUNITS16 is required to achieve equivalent functionality for most data. For z/OS, the argument CODEUNITS16 is required to achieve equivalent functionality for most data.
POSSTRYYY
POWERYYY
QUANTIZEYYY
QUARTERYYY
RADIANSYYY
RAISE_ERRORYYY
RANDYYY
REALYYY
REPEATPPYLUW does not support graphic types. z/OS does not support LOB arguments.
REPLACEPPYLUW does not support graphic types. z/OS does not support LOB arguments.
RIDYYY
RIGHT (no string units)PYYLUW does not support graphic types.
ROUND (numeric)YYY
ROUND_TIMESTAMPYYY
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
SPACEPYY
SQRTPYY
STRIPYYY
SUBSTRYYY
SUBSTRING (no string units)PPYFor LUW, the argument CODEUNITS16 is required to achieve equivalent functionality for most data. For z/OS, the argument CODEUNITS16 is required to achieve equivalent functionality for most data.
TANYYY
TANHYYY
TIMEYYY
TIMESTAMPYYY
TIMESTAMP_ISOYYY
TIMESTAMP_FORMATYYY
TIMESTAMPDIFFYYY
TO_CHAR (timestamp)YYY
TO_DATEYYY
TOTALORDERYYY
TRANSLATEYYY
TRIMPPYLUW and z/OS do not support CLOB or DBCLOB arguments.
TRUNCATE or TRUNC (numeric)YYY
TRUNC_TIMESTAMPYYY
UCASE or UPPERYYY
VALUEYYY
VARCHAR (no string units)PYY
VARCHAR_FORMAT (timestamp)YYY
VARGRAPHIC (no string units)YYY
WEEKYYY
WEEK_ISOYYY
XMLATTRIBUTESYYY
XMLCOMMENTYYY
XMLCONCATYYY
XMLDOCUMENTYYY
XMLELEMENTYYY
XMLFORESTYYY
XMLNAMESPACESYYY
XMLPARSEYYY
XMLPIYYY
XMLSERIALIZEYYY
XMLTEXTYYY
YEARYYY
Table 8. Table functions
Language elementLUWz/OS i Notes
MQREADALLYYY
MQREADALLCLOBYYY
MQRECEIVEALLYYY
MQRECEIVEALLCLOBYYY
XMLTABLE passing named variablesYYY
Table 9. Procedures
Language elementLUWz/OS i Notes
XSR_ADDSCHEMADOCYYY
XSR_COMPLETEYYY
XSR_REGISTERYYY
Table 10. Queries
Language elementLUWz/OS i Notes
SELECT clauseYYY
AS subclause 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 inner join (multiple comma separated table-references)YYY
FROM clause with nested table expression containing a subselectYYY
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 INSERT statementYYY
WHERE clauseYYY
GROUP BY referencing column namesYYY
GROUP BY referencing grouping expressionsYYY
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
Table 11. Basic statements
Language elementLUWz/OS i Notes
Static CALL statementYYY
Static CALL statement with variable name for procedure nameYYY
SELECT INTO statement with optional WHERE, GROUP BY or HAVING clausesYYY
SELECT INTO statement with FETCH FIRST clauseYYY
SELECT INTO statement with isolation clauseYYY
SET host variable statementYYY
VALUES INTO statementYYY
Table 12. Data change statements
Language elementLUWz/OS i Notes
Searched DELETE statementYYY
INSERT statement with single row VALUES clauseYYY
INSERT statement with subselectYYY
MERGE statement using table-reference inputYPY
Searched UPDATE statementYYY
Table 13. 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
Table 14. Connection and transaction control
Language elementLUWz/OS i Notes
Isolation Level Specification at package levelYYY
Isolation Level Specification at statement levelYYY
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 LOCKSPPY
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
Table 15. Dynamic facilities
Language elementLUWz/OS i Notes
DESCRIBE output statement without a USING clauseYYY
DESCRIBE INPUT statementYYY
EXECUTE statementYYY
EXECUTE IMMEDIATE statementYYY
PREPARE statementYYY
PREPARE statement that specifies cursor options (ATTRIBUTES clause)PYYLUW only supports this capability through CLI or JDBC.
Table 16. Control statements
Language elementLUWz/OS i Notes
Control statements in SQL proceduresYYY
Control statements in SQL functionsYPYPartial support allows a single RETURN statement in SQL table functions.
Assignment statementYYY
CASE statementYYY
Compound statementYYY
Nested not atomic 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
Table 17. 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 statementPPY
ALTER PROCEDURE statementPYY
ALTER SEQUENCE statementYYY
ALTER TABLE statement (see other elements marked ALTAB)YPYz/OS does not allow the same clause to be specified more than once.
ALTER COLUMN (ALTAB)PPY
COMMENT statementYYY
CREATE ALIAS statementYYY
CREATE DISTINCT TYPE statementYYY
CREATE FUNCTION (External Scalar) statementYYY
CREATE FUNCTION (External Table) statementYYY
CREATE FUNCTION (Sourced) statementYYY
CREATE FUNCTION (SQL Scalar) statementYYYNot all features of the statement are supported on each platform.
CREATE FUNCTION (SQL Table) statementYPYPartial support allows a single RETURN statement.
CREATE INDEX over column listYYY
CREATE UNIQUE INDEX over column listYYY
CREATE PROCEDURE (External) statementYYY
CREATE PROCEDURE (SQL) statementYYY
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
IDENTITY clause (ALTAB, CRTAB, DGTTAB)YYY
ROW CHANGE TIMESTAMP clause (ALTAB, CRTAB)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
CREATE SEQUENCE statementYYY
CREATE TRIGGER statement - BEFORE or AFTER trigger on a tableYYY
CREATE TRIGGER statement - INSTEAD OF trigger on a viewYPY
CREATE VIEW statement using subselect 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 DISTINCT TYPE statementYYY
DROP FUNCTION statementYYY
DROP INDEX statementYYY
DROP PACKAGE statementYYY
DROP PROCEDURE statementYPY
DROP SEQUENCE statementYYY
DROP TABLE statementYYY
DROP TRIGGER statementYYY
DROP VIEW statementYYY
RENAME TABLE statementPPY
RENAME INDEX statementYYY
Table 18. Authorization
Language elementLUWz/OS i Notes
GRANT (function or procedure privileges) statementYYY
GRANT (package privileges) statementYYY
GRANT (sequence privileges) statementYYY
GRANT (table privileges) statementYYY
REVOKE (function privileges) statementYYY
REVOKE (procedure privileges) statementYPY
REVOKE (package privileges) statementYYY
REVOKE (sequence privileges) statementYYY
REVOKE (table privileges) statementYYY
Table 19. 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 CURRENT DECFLOAT ROUNDING MODE statementPYY
SET CURRENT DEGREE statementYYY
SET ENCRYPTION PASSWORD statementYYY
SET PATH statementYYY
SET SCHEMA statementYYY
WHENEVER statementYYY
Table 20. 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.

Resources

Learn

Get products and technologies

  • Evaluate IBM products in the way that suits you best: Download a product trial, try a product online, or use a product in a cloud environment.

Discuss

  • Get involved in the developerWorks community. Connect with other developerWorks users while exploring the developer-driven blogs, forums, groups, and wikis.

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


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