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 2.7 MB) and the respective DB2 product SQL Reference books.
| Language element | LUW | z/OS | i5/OS | Notes |
|---|
| ABSVAL or ABS | Y | Y | Y |
| ACOS | Y | Y | Y |
| ASIN | Y | Y | Y |
| ATAN | Y | Y | Y |
| ATANH | Y | Y | Y |
| ATAN2 | Y | Y | Y |
| BLOB | Y | Y | Y |
| CEIL or CEILING | Y | Y | Y |
| CHAR | Y | Y | Y |
| CLOB | Y | Y | Y |
| COALESCE (or VALUE) | Y | Y | Y |
| CONCAT | Y | Y | Y |
| COS | Y | Y | Y |
| COSH | Y | Y | Y |
| DATE | Y | Y | Y |
| DAY | Y | Y | Y |
| DAYNAME | Y | P | Y | z/OS supports function as a sample in schema DSN8. |
| DAYOFWEEK | Y | Y | Y |
| DAYOFWEEK_ISO | Y | Y | Y |
| DAYOFYEAR | Y | Y | Y |
| DAYS | Y | Y | Y |
| DBCLOB | Y | Y | Y |
| DECIMAL or DEC | Y | Y | Y |
| DECRYPT_BIT | P | Y | Y | LUW supports the function with name DECRYPT_BIN. |
| DECRYPT_CHAR | P | Y | Y |
| DEGREES | Y | Y | Y |
| DIGITS | Y | Y | Y |
| DOUBLE or DOUBLE_PRECISION | Y | Y | Y |
| ENCRYPT | P | Y | Y | Syntax for the ENCRYPT function is common but z/OS will use the ENCRYPT_TDES format for the result instead of ENCRYPT_RC2. |
| EXP | Y | Y | Y |
| FLOAT | Y | Y | Y |
| FLOOR | Y | Y | Y |
| GETHINT | Y | Y | Y |
| GRAPHIC | P | Y | Y |
| HEX | Y | Y | Y |
| HOUR | Y | Y | Y |
| IDENTITY_VAL_LOCAL | Y | Y | Y |
| INSERT | Y | Y | Y |
| INTEGER or INT | Y | Y | Y |
| JULIAN_DAY | Y | Y | Y |
| LCASE | Y | Y | Y |
| LEFT | P | Y | Y |
| LENGTH | Y | Y | Y |
| LN | Y | Y | Y |
| LOCATE | P | Y | Y |
| LOG10 | Y | Y | Y |
| LOWER | Y | Y | Y |
| LTRIM | P | P | Y | LUW does not support a CLOB argument. z/OS does not support a CLOB argument. |
| MICROSECOND | Y | Y | Y |
| MIDNIGHT_SECONDS | Y | Y | Y |
| MINUTE | Y | Y | Y |
| MOD | P | Y | Y |
| MONTH | Y | Y | Y |
| MONTHNAME | Y | P | Y | z/OS supports function as a sample in schema DSN8. |
| MULTIPLY_ALT | Y | Y | Y |
| NULLIF | P | P | Y | LUW does not support LOB arguments. z/OS does not support LOB arguments. |
| POSSTR | Y | Y | Y |
| POWER | Y | Y | Y |
| QUARTER | Y | Y | Y |
| RADIANS | Y | Y | Y |
| RAND | Y | Y | Y |
| REAL | Y | Y | Y |
| REPEAT | P | P | Y | LUW does not support graphic types. z/OS does not support LOB arguments. |
| REPLACE | P | P | Y | LUW does not support graphic types. z/OS does not support LOB arguments. |
| RIGHT | P | Y | Y | LUW does not support graphic types. |
| ROUND | Y | Y | Y |
| RTRIM | P | P | Y | LUW does not support a CLOB argument. z/OS does not support a CLOB argument. |
| SECOND | Y | Y | Y |
| SIGN | Y | Y | Y |
| SIN | Y | Y | Y |
| SINH | Y | Y | Y |
| SMALLINT | Y | Y | Y |
| SPACE | P | Y | Y |
| SQRT | P | Y | Y |
| SUBSTR | Y | Y | Y |
| TAN | Y | Y | Y |
| TANH | Y | Y | Y |
| TIME | Y | Y | Y |
| TIMESTAMP | Y | Y | Y |
| TRANSLATE | Y | Y | Y |
| TRUNCATE or TRUNC | Y | Y | Y |
| UCASE or UPPER | Y | Y | Y |
| VALUE | Y | Y | Y |
| VARCHAR | P | Y | Y |
| VARGRAPHIC | Y | Y | Y |
| WEEK | Y | Y | Y |
| WEEK_ISO | Y | Y | Y |
| YEAR | Y | Y | Y |
| Language element | LUW | z/OS | i5/OS | Notes |
|---|
| SELECT clause | Y | Y | Y |
| AS sub-clause in SELECT clause | Y | Y | Y |
| Qualified * or unqualified * in SELECT clause | Y | Y | Y |
| Expressions other than scalar subquery in SELECT clause | Y | Y | Y |
| Aggregate functions allowed in SELECT clause even if the result table is derived from a grouped view | Y | Y | Y |
| FROM clause referencing a single table or view | Y | Y | Y |
| FROM clause with implicit inner join (multiple comma separated table-references) | Y | Y | Y |
| FROM clause with nested table expression containing a subselect | Y | Y | Y |
| FROM clause with nested table expression preceded by TABLE keyword | Y | Y | Y |
| FROM clause with explicit INNER JOIN | Y | Y | Y |
| FROM clause with LEFT OUTER JOIN | Y | Y | Y |
| FROM clause with RIGHT OUTER JOIN | Y | Y | Y |
| WHERE clause | Y | Y | Y |
| GROUP BY referencing column names | Y | Y | Y |
| GROUP BY referencing grouping expressions | Y | Y | Y |
| HAVING clause | Y | Y | Y |
| Fullselect with UNION or UNION ALL | Y | Y | Y |
| Select-statement starting with common table expression | Y | Y | Y |
| ORDER BY clause over qualified column names | Y | Y | Y |
| ORDER BY clause over sort-key expressions | Y | Y | Y |
| ORDER BY clause over expressions not in the SELECT list | Y | Y | Y |
| FOR UPDATE clause with list of column names | Y | Y | Y |
| FOR UPDATE clause with implicit column list | Y | Y | Y |
| FOR READ ONLY clause | Y | Y | Y |
| OPTIMIZE clause with integer number of rows | Y | Y | Y |
| Select-statement with isolation clause with CS, RR, or RS | Y | Y | Y |
| Select-statement with isolation clause with UR on read only select statement | Y | Y | Y |
| FETCH FIRST clause | Y | Y | Y |
| Language element | LUW | z/OS | i5/OS | Notes |
|---|
| CLOSE statement | Y | Y | Y |
| DECLARE CURSOR statement (see other cursor elements) | Y | Y | Y |
| Declare INSENSITIVE SCROLL cursor | P | Y | Y | LUW only supports this capability through CLI or JDBC. |
| Declare WITH HOLD cursor | Y | Y | Y |
| Declare WITH RETURN cursor (implicit return to caller) | Y | Y | Y |
| Declare WITH RETURN TO CALLER cursor | Y | Y | Y |
| Positioned DELETE statement | Y | Y | Y |
| FETCH statement with no cursor orientation (implicit NEXT) | Y | Y | Y |
| FETCH statement with NEXT, PRIOR, FIRST, LAST, BEFORE, AFTER, CURRENT, or RELATIVE | P | Y | Y | LUW only supports these fetch features through CLI or JDBC. |
| OPEN statement | Y | Y | Y |
| Positioned UPDATE statement | Y | Y | Y |
| Language element | LUW | z/OS | i5/OS | Notes |
|---|
| Isolation Level Specification at package level | Y | Y | Y |
| Isolation Level Specification at statement level | Y | Y | Y |
| RR (SERIALIZABLE in SQL1999 Standard) | Y | Y | Y |
| RS (REPEATABLE READ in SQL1999 Standard) | Y | Y | Y |
| CS (READ COMMITTED in SQL1999 Standard) | Y | Y | Y |
| UR (READ UNCOMMITTED in SQL1999 Core Standard) | Y | Y | Y |
| COMMIT statement | Y | Y | Y |
| CONNECT statement (1 site per transaction) | Y | Y | Y |
| CONNECT statement (2+ sites per transaction) (also referred to as Two Phase Commit) | Y | Y | Y |
| DISCONNECT statement | Y | Y | Y |
| RELEASE connection statement | Y | Y | Y |
| RELEASE SAVEPOINT statement | Y | Y | Y |
| ROLLBACK unit of work statement | Y | Y | Y |
| ROLLBACK TO SAVEPOINT statement | Y | Y | Y |
| SAVEPOINT statement - not nested | Y | Y | Y |
| SET CONNECTION statement | Y | Y | Y |
| Language element | LUW | z/OS | i5/OS | Notes |
|---|
| Allow DDL statements in SQL programs | Y | Y | Y |
| Support Transaction Semantics for DDL | Y | Y | Y |
| Support 18 byte object names (except schema name ) | Y | Y | Y | Some platforms support longer object names. |
| Support 8 byte schema names | Y | Y | Y | Some platforms support longer schema names. |
| ALTER SEQUENCE statement | Y | Y | Y |
| ALTER TABLE statement (see other elements marked ALTAB) | Y | P | Y | z/OS does not allow the same clause to be specified more than once. |
| ALTER COLUMN (ALTAB) | P | P | Y |
| COMMENT statement | Y | Y | Y |
| CREATE ALIAS statement | Y | Y | Y |
| CREATE DISTINCT TYPE statement | Y | Y | Y |
| CREATE FUNCTION (External Scalar) statement | Y | Y | Y |
| CREATE FUNCTION (Sourced) statement | Y | Y | Y |
| CREATE FUNCTION (SQL Scalar) statement | P | P | Y | Not all features of the statement are supported on each platform. |
| CREATE FUNCTION (External Table) statement | Y | Y | Y |
| CREATE INDEX over column list | Y | Y | Y |
| CREATE UNIQUE INDEX over column list | Y | Y | Y |
| CREATE PROCEDURE statement for external procedure | Y | Y | Y |
| CREATE PROCEDURE statement for SQL procedure | Y | Y | Y |
| CREATE SCHEMA | Y | P | Y | z/OS requires the statement to be processed by a special schema processor. |
| CREATE TABLE statement using column definition (see other elements marked CRTAB) | Y | Y | Y |
| CREATE TABLE statement LIKE other table/view (see other elements marked CRTAB) | Y | Y | Y |
| CREATE TABLE statement LIKE other table/view including identity (see other elements marked CRTAB) | Y | Y | Y |
| CREATE TABLE statement AS query (see other elements marked CRTAB) | Y | Y | Y |
| CHECK constraint clause (ALTAB, CRTAB) | Y | Y | Y |
| DATA CAPTURE clause (ALTAB, CRTAB) | Y | Y | P | iSeries tables always capture changes - the clause is not supported. |
| DEFAULT clause with no specified value (ALTAB, CRTAB, DGTTAB) | Y | Y | Y |
| DEFAULT clause with specified value (ALTAB, CRTAB, DGTTAB) | Y | Y | Y |
| IDENTITY clause (ALTAB, CRTAB, DGTTAB) | Y | Y | Y |
| PRIMARY KEY constraint clause (ALTAB, CRTAB) | Y | Y | Y |
| UNIQUE constraint clause (ALTAB, CRTAB) | Y | Y | Y |
| Automatic index generation for unique constraints (ALTAB, CRTAB) | Y | P | Y | z/OS requires the use of the schema processor. |
| Referential constraint clause (ALTAB, CRTAB) | Y | Y | Y |
| REFERENCES clause specifying ON DELETE (ALTAB, CRTAB) | P | P | Y | LUW 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 TABLE | Y | P | Y |
| User maintained MQT (CRTAB,ALTAB) | Y | Y | P |
| CREATE SEQUENCE statement | Y | Y | Y |
| CREATE TRIGGER statement - BEFORE or AFTER trigger on a table | Y | Y | Y |
| CREATE VIEW statement using subselect without CHECK OPTION clause | Y | Y | Y |
| CREATE VIEW WITH CASCADED CHECK OPTION | Y | Y | Y |
| CREATE VIEW WITH LOCAL CHECK OPTION | Y | Y | Y |
| DECLARE GLOBAL TEMPORARY TABLE statement (see other elements marked DGTTAB) | P | Y | Y |
| DROP statement with specified RESTRICT or CASCADE behaviour | P | P | Y |
| DROP ALIAS statement | Y | Y | Y |
| DROP DISTINCT TYPE statement | Y | Y | Y |
| DROP FUNCTION statement | Y | Y | Y |
| DROP INDEX statement | Y | Y | Y |
| DROP PACKAGE statement | Y | Y | Y |
| DROP PROCEDURE statement | Y | P | Y |
| DROP SEQUENCE statement | Y | Y | Y |
| DROP TABLE statement | Y | Y | Y |
| DROP TRIGGER statement | Y | Y | Y |
| DROP VIEW statement | Y | Y | Y |
| RENAME TABLE statement | P | P | Y |
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.
