![Close [x]](http://dw1.s81c.com/i/c.gif){kind=small}
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.
- DB2 Version 10.1 FP2 for Linux, UNIX, and Windows
- DB2 Version 10.1 for z/OS
- DB2 Version 7.1 TR6 for IBM i
- Basic elements
- Data types
- Special registers
- Predicates
- Aggregate functions
- Scalar functions
- Table functions
- Procedures
- Queries
- Basic statements
- Data change statements
- Cursor-oriented operations
- Connection and transaction control
- Dynamic facilities
- Control statements
- Data definition
- Authorization
- Miscellaneous statements
- Host languages
Table 1. Key
| Y | Product includes full support. |
| P | Product includes partial support; check product information. |
Table 2. Basic elements
| Language element | LUW | z/OS | i | Notes |
|---|---|---|---|---|
| Identifiers -Ordinary | Y | Y | Y | |
| Identifiers -Delimited | Y | Y | Y | |
| Identifiers with support of lowercase letters and trailing underscores | Y | Y | Y | |
| Casting of Data Types -Implicit | Y | Y | Y | |
| Automatic data conversion based on code page | Y | Y | Y | |
| Constants | Y | Y | Y | |
| Hex specification of character constants | Y | Y | Y | |
| Null values | Y | Y | Y | |
| Column references | Y | Y | Y | |
| Host Variable References | Y | Y | Y | |
| Indicator variables | Y | Y | Y | |
| Extended indicator variables | Y | Y | Y | |
| Host Structures | Y | Y | Y | |
| "Friendly" arithmetic and conversion | Y | Y | Y | |
| Function invocation | Y | Y | Y | |
| Arithmetic operators | Y | Y | Y | |
| String Concatenation | Y | Y | Y | |
| Scalar subselect as expression | Y | Y | Y | |
| Scalar fullselect as expression | Y | Y | Y | |
| Date/Time Arithmetic | Y | Y | Y | |
| CASE expression | Y | Y | Y | |
| CAST specification | Y | Y | Y | |
| XMLCAST specification | Y | Y | P | |
| ROW CHANGE expression | Y | Y | Y | |
| Sequence reference (NEXT VALUE and PREVIOUS VALUE) | Y | Y | Y | |
| OLAP ranking specification (RANK, DENSE_RANK) | Y | Y | Y | |
| OLAP numbering specification (ROW_NUMBER) | Y | Y | Y | |
| Untyped parameter markers | Y | P | Y | For z/OS, an error is returned for untyped parameter markers in contexts that are supported by other platforms. |
| Diagnostic Area | Y | Y | Y | |
| SQLSTATE | Y | Y | Y | |
| SQLCODE | Y | Y | Y | |
| Descriptor Area | Y | Y | Y |
Table 3. Data types
| Language element | LUW | z/OS | i | Notes |
|---|---|---|---|---|
| SMALLINT | Y | Y | Y | |
| INTEGER | Y | Y | Y | |
| BIGINT | Y | Y | Y | |
| DECIMAL | Y | Y | Y | |
| NUMERIC | Y | Y | Y | NUMERIC is defined as zoned decimal on DB2 for i and packed decimal on other platforms. |
| REAL | Y | Y | Y | For portability across platforms, do not use FLOAT(n). |
| DOUBLE or FLOAT | Y | Y | Y | For portability across platforms, do not use FLOAT(n). |
| DECFLOAT | Y | Y | Y | |
| CHARACTER | Y | Y | Y | |
| VARCHAR | Y | Y | Y | |
| FOR BIT DATA with CHAR or VARCHAR | Y | Y | Y | |
| CLOB | Y | Y | Y | |
| FOR SBCS or MIXED DATA with CHAR, VARCHAR, or CLOB | P | Y | Y | |
| BLOB | Y | Y | Y | |
| GRAPHIC | Y | Y | Y | |
| VARGRAPHIC | Y | Y | Y | |
| DBCLOB | Y | Y | Y | |
| DATE | Y | Y | Y | |
| TIME | Y | Y | Y | |
| TIMESTAMP | Y | Y | Y | |
| XML | Y | Y | P | |
| User-defined distinct types | Y | Y | Y |
Table 4. Special registers
| Language element | LUW | z/OS | i | Notes |
|---|---|---|---|---|
| CURRENT CLIENT_ACCTNG | Y | Y | Y | |
| CURRENT CLIENT_APPLNAME | Y | Y | Y | |
| CURRENT CLIENT_USERID | Y | Y | Y | |
| CURRENT CLIENT_WRKSTNNAME | Y | Y | Y | |
| CURRENT DATE or CURRENT_DATE | Y | Y | Y | |
| CURRENT DECFLOAT ROUNDING MODE | Y | Y | Y | |
| CURRENT DEGREE | Y | Y | Y | |
| CURRENT PATH or CURRENT_PATH | Y | Y | Y | |
| CURRENT SCHEMA or CURRENT_SCHEMA | Y | Y | Y | |
| CURRENT SERVER | Y | Y | Y | |
| CURRENT TIME or CURRENT_TIME | Y | Y | Y | |
| CURRENT TIMESTAMP or CURRENT_TIMESTAMP | Y | Y | Y | |
| CURRENT TIMEZONE | Y | Y | Y | |
| SESSION_USER | Y | Y | Y | |
| USER | Y | Y | Y |
Table 5. Predicates
| Language element | LUW | z/OS | i | Notes |
|---|---|---|---|---|
| Basic ( = <> < > <= >= ) excluding LOB types | Y | Y | Y | |
| Basic with scalar subselect right of comparison operator | Y | Y | Y | |
| Quantified (ALL, ANY or SOME) with single expression (no subquery) on left side and subselect on right side | Y | Y | Y | |
| BETWEEN (no scalar subqueries) | Y | Y | Y | |
| EXISTS (subselect) | Y | Y | Y | |
| IN followed by list of expressions | Y | Y | Y | |
| IN followed by single column subselect | Y | Y | Y | |
| IS NULL (no scalar subquery) | Y | Y | Y | |
| LIKE with host variable or string constant as pattern | Y | Y | Y | |
| LIKE with expression for pattern and escape | Y | Y | Y |
Table 6. Aggregate functions
| Language element | LUW | z/OS | i | Notes |
|---|---|---|---|---|
| AVG | Y | Y | Y | |
| COUNT | Y | Y | Y | |
| COUNT_BIG | Y | Y | Y | |
| MAX | Y | Y | Y | |
| MIN | Y | Y | Y | |
| STDDEV | Y | Y | Y | |
| SUM | Y | Y | Y | |
| VARIANCE or VAR | Y | Y | Y | |
| XMLAGG | Y | Y | Y |
Table 7. Scalar functions
| Language element | LUW | z/OS | i | Notes | |
|---|---|---|---|---|---|
| ABSVAL or ABS | Y | Y | Y | ||
| ACOS | Y | Y | Y | ||
| ADD_MONTHS | Y | Y | Y | ||
| ASCII | Y | Y | Y | ||
| ASIN | Y | Y | Y | ||
| ATAN | Y | Y | Y | ||
| ATANH | Y | Y | Y | ||
| ATAN2 | Y | Y | Y | ||
| BIGINT | Y | Y | Y | ||
| BIT Manipulation functions (BITAND, BITANDNOT, BITNOT, BITOR, BITXOR) | Y | Y | Y | ||
| BLOB | Y | Y | Y | ||
| CEIL or CEILING | Y | Y | Y | ||
| CHAR (no string units) | Y | Y | Y | ||
| CHARACTER_LENGTH or CHAR_LENGTH (no string units) | P | P | Y | For 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) | Y | Y | Y | ||
| COALESCE (or VALUE) | Y | Y | Y | ||
| COMPARE_DECFLOAT | Y | Y | Y | ||
| CONCAT | Y | Y | Y | ||
| CONTAINS | 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 (no string units) | Y | Y | Y | ||
| DECFLOAT | Y | Y | Y | ||
| DECIMAL or DEC | Y | Y | Y | ||
| DECRYPT_BIT | P | Y | Y | LUW supports the function with name DECRYPT_BIN. | |
| DECRYPT_CHAR | Y | Y | Y | ||
| DEGREES | Y | Y | Y | ||
| DIFFERENCE | Y | Y | Y | ||
| DIGITS | Y | Y | Y | ||
| DOUBLE or DOUBLE_PRECISION | Y | Y | Y | ||
| ENCRYPT | Y | 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 | ||
| EXTRACT | Y | Y | Y | ||
| FLOAT | Y | Y | Y | ||
| FLOOR | Y | Y | Y | ||
| GENERATE_UNIQUE | Y | Y | Y | ||
| GETHINT | Y | Y | Y | ||
| GRAPHIC (no string units) | P | Y | Y | ||
| HEX | Y | Y | Y | ||
| HOUR | Y | Y | Y | ||
| IDENTITY_VAL_LOCAL | Y | Y | Y | ||
| INSERT (no string units) | Y | Y | Y | ||
| INTEGER or INT | Y | Y | Y | ||
| JULIAN_DAY | Y | Y | Y | ||
| LAST_DAY | Y | Y | Y | ||
| LCASE or LOWER | Y | Y | Y | ||
| LEFT (no string units) | P | Y | Y | ||
| LENGTH (no string units) | Y | Y | Y | ||
| LN | Y | Y | Y | ||
| LOCATE (no string units) | Y | Y | Y | ||
| LOG10 | Y | Y | Y | ||
| LTRIM (with 1 argument) | P | P | Y | LUW does not support a CLOB argument. z/OS does not support a CLOB argument. | |
| MAX | Y | Y | Y | ||
| MICROSECOND | Y | Y | Y | ||
| MIDNIGHT_SECONDS | Y | Y | Y | ||
| MIN | Y | Y | Y | ||
| MINUTE | Y | Y | Y | ||
| MOD | Y | Y | Y | ||
| MONTH | Y | Y | Y | ||
| MONTHNAME | Y | P | Y | z/OS supports function as a sample in schema DSN8. | |
| MONTHS_BETWEEN | Y | Y | Y | ||
| MQREAD with up to 2 arguments | Y | Y | Y | ||
| MQREADCLOB with up to 2 arguments | Y | Y | Y | ||
| MQRECEIVE | Y | Y | Y | ||
| MQRECEIVECLOB | Y | Y | Y | ||
| MQSEND | Y | Y | Y | ||
| MULTIPLY_ALT | Y | Y | Y | ||
| NEXT_DAY | Y | Y | Y | ||
| NORMALIZE_DECFLOAT | Y | Y | Y | ||
| NULLIF | P | P | Y | LUW does not support LOB arguments. z/OS does not support LOB arguments. | |
| POSITION (no string units) | P | P | Y | For 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. | |
| POSSTR | Y | Y | Y | ||
| POWER | Y | Y | Y | ||
| QUANTIZE | Y | Y | Y | ||
| QUARTER | Y | Y | Y | ||
| RADIANS | Y | Y | Y | ||
| RAISE_ERROR | 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. | |
| RID | Y | Y | Y | ||
| RIGHT (no string units) | P | Y | Y | LUW does not support graphic types. | |
| ROUND (numeric) | Y | Y | Y | ||
| ROUND_TIMESTAMP | Y | Y | Y | ||
| RTRIM (with 1 argument) | P | P | Y | LUW has limited support for a CLOB argument. z/OS does not support a CLOB argument. | |
| SCORE | Y | Y | Y | ||
| SECOND | Y | Y | Y | ||
| SIGN | Y | Y | Y | ||
| SIN | Y | Y | Y | ||
| SINH | Y | Y | Y | ||
| SMALLINT | Y | Y | Y | ||
| SOUNDEX | Y | Y | Y | ||
| SPACE | P | Y | Y | ||
| SQRT | P | Y | Y | ||
| STRIP | Y | Y | Y | ||
| SUBSTR | Y | Y | Y | ||
| SUBSTRING (no string units) | P | P | Y | For 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. | |
| TAN | Y | Y | Y | ||
| TANH | Y | Y | Y | ||
| TIME | Y | Y | Y | ||
| TIMESTAMP | Y | Y | Y | ||
| TIMESTAMP_ISO | Y | Y | Y | ||
| TIMESTAMP_FORMAT | Y | Y | Y | ||
| TIMESTAMPDIFF | Y | Y | Y | ||
| TO_CHAR (timestamp) | Y | Y | Y | ||
| TO_DATE | Y | Y | Y | ||
| TOTALORDER | Y | Y | Y | ||
| TRANSLATE | Y | Y | Y | ||
| TRIM | P | P | Y | LUW and z/OS do not support CLOB or DBCLOB arguments. | |
| TRUNCATE or TRUNC (numeric) | Y | Y | Y | ||
| TRUNC_TIMESTAMP | Y | Y | Y | ||
| UCASE or UPPER | Y | Y | Y | ||
| VALUE | Y | Y | Y | ||
| VARCHAR (no string units) | P | Y | Y | ||
| VARCHAR_FORMAT (timestamp) | Y | Y | Y | ||
| VARGRAPHIC (no string units) | Y | Y | Y | ||
| WEEK | Y | Y | Y | ||
| WEEK_ISO | Y | Y | Y | ||
| XMLATTRIBUTES | Y | Y | Y | ||
| XMLCOMMENT | Y | Y | Y | ||
| XMLCONCAT | Y | Y | Y | ||
| XMLDOCUMENT | Y | Y | Y | ||
| XMLELEMENT | Y | Y | Y | ||
| XMLFOREST | Y | Y | Y | ||
| XMLNAMESPACES | Y | Y | Y | ||
| XMLPARSE | Y | Y | Y | ||
| XMLPI | Y | Y | Y | ||
| XMLSERIALIZE | Y | Y | Y | ||
| XMLTEXT | Y | Y | Y | ||
| YEAR | Y | Y | Y |
Table 8. Table functions
| Language element | LUW | z/OS | i | Notes |
|---|---|---|---|---|
| MQREADALL | Y | Y | Y | |
| MQREADALLCLOB | Y | Y | Y | |
| MQRECEIVEALL | Y | Y | Y | |
| MQRECEIVEALLCLOB | Y | Y | Y | |
| XMLTABLE passing named variables | Y | Y | Y |
Table 9. Procedures
| Language element | LUW | z/OS | i | Notes |
|---|---|---|---|---|
| XSR_ADDSCHEMADOC | Y | Y | Y | |
| XSR_COMPLETE | Y | Y | Y | |
| XSR_REGISTER | Y | Y | Y |
Table 10. Queries
| Language element | LUW | z/OS | i | 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 CROSS JOIN | 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 | |
| FROM clause with FULL OUTER JOIN with equality in the join condition | Y | Y | Y | |
| FROM clause with INSERT statement | 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 | |
| Fullselect with INTERSECT or INTERSECT ALL | Y | Y | P | DB2 for i does not support INTERSECT ALL. |
| Fullselect with EXCEPT or EXCEPT ALL | Y | Y | P | DB2 for i does not support EXCEPT ALL. |
| Select-statement starting with common table expression | Y | Y | Y | |
| Select-statement using recursive 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 | |
| ORDER BY ORDER OF | 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 |
Table 11. Basic statements
| Language element | LUW | z/OS | i | Notes |
|---|---|---|---|---|
| Static CALL statement | Y | Y | Y | |
| Static CALL statement with variable name for procedure name | Y | Y | Y | |
| SELECT INTO statement with optional WHERE, GROUP BY or HAVING clauses | Y | Y | Y | |
| SELECT INTO statement with FETCH FIRST clause | Y | Y | Y | |
| SELECT INTO statement with isolation clause | Y | Y | Y | |
| SET host variable statement | Y | Y | Y | |
| VALUES INTO statement | Y | Y | Y |
Table 12. Data change statements
| Language element | LUW | z/OS | i | Notes |
|---|---|---|---|---|
| Searched DELETE statement | Y | Y | Y | |
| INSERT statement with single row VALUES clause | Y | Y | Y | |
| INSERT statement with subselect | Y | Y | Y | |
| MERGE statement using table-reference input | Y | P | Y | |
| Searched UPDATE statement | Y | Y | Y |
Table 13. Cursor-oriented operations
| Language element | LUW | z/OS | i | Notes |
|---|---|---|---|---|
| ALLOCATE CURSOR statement | P | Y | Y | LUW supports the statement only in SQL procedures. |
| ASSOCIATE LOCATORS statement | P | Y | Y | LUW supports the statement only in SQL procedures. |
| 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 | |
| Declare WITH RETURN TO CLIENT 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. |
| FETCH statement with ABSOLUTE | P | Y | P | LUW only supports this fetch feature through CLI or JDBC. DB2 for i only supports this fetch feature through CLI or JDBC. |
| OPEN statement | Y | Y | Y | |
| Positioned UPDATE statement | Y | Y | Y |
Table 14. Connection and transaction control
| Language element | LUW | z/OS | i | Notes |
|---|---|---|---|---|
| Isolation Level Specification at package level | Y | Y | Y | |
| Isolation Level Specification at statement level | Y | Y | Y | |
| RR (SERIALIZABLE in SQL2008 Standard) | Y | Y | Y | |
| RS (REPEATABLE READ in SQL2008 Standard) | Y | Y | Y | |
| CS (READ COMMITTED in SQL2008 Standard) | Y | Y | Y | |
| UR (READ UNCOMMITTED in SQL2008 Core Standard) | Y | Y | Y | |
| Isolation clause with USE AND KEEP EXCLUSIVE LOCKS | P | P | 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 | |
| SAVEPOINT statement - nested | Y | Y | Y | |
| SET CONNECTION statement | Y | Y | Y |
Table 15. Dynamic facilities
| Language element | LUW | z/OS | i | Notes |
|---|---|---|---|---|
| DESCRIBE output statement without a USING clause | Y | Y | Y | |
| DESCRIBE INPUT statement | Y | Y | Y | |
| EXECUTE statement | Y | Y | Y | |
| EXECUTE IMMEDIATE statement | Y | Y | Y | |
| PREPARE statement | Y | Y | Y | |
| PREPARE statement that specifies cursor options (ATTRIBUTES clause) | P | Y | Y | LUW only supports this capability through CLI or JDBC. |
Table 16. Control statements
| Language element | LUW | z/OS | i | Notes |
|---|---|---|---|---|
| Control statements in SQL procedures | Y | Y | Y | |
| Control statements in SQL functions | Y | P | Y | Partial support allows a single RETURN statement in SQL table functions. |
| Assignment statement | Y | Y | Y | |
| CASE statement | Y | Y | Y | |
| Compound statement | Y | Y | Y | |
| Nested not atomic compound statement | Y | Y | Y | |
| FOR statement | Y | Y | Y | |
| GET DIAGNOSTICS for ROW_COUNT statement | Y | Y | Y | |
| GET DIAGNOSTICS for RETURN_STATUS statement | Y | Y | Y | |
| GET DIAGNOSTICS for MESSAGE_TEXT statement | Y | Y | Y | |
| GOTO statement | Y | Y | Y | |
| IF statement | Y | Y | Y | |
| ITERATE statement | Y | Y | Y | |
| LEAVE statement | Y | Y | Y | |
| LOOP statement | Y | Y | Y | |
| REPEAT statement | Y | Y | Y | |
| RESIGNAL statement | Y | Y | Y | |
| RETURN statement from SQL procedure | Y | Y | Y | |
| RETURN statement from SQL function | Y | Y | Y | |
| SIGNAL statement | Y | Y | Y | |
| WHILE statement | Y | Y | Y |
Table 17. Data definition
| Language element | LUW | z/OS | i | Notes |
|---|---|---|---|---|
| Allow DDL statements in SQL programs | Y | Y | Y | |
| Support Transaction Semantics for DDL | Y | Y | Y | |
| Support 30 byte column names | Y | Y | Y | Some platforms support longer column names. |
| Support 128 byte object names | Y | Y | Y | |
| ALTER FUNCTION statement | P | P | Y | |
| ALTER PROCEDURE statement | P | Y | Y | |
| 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 (External Table) statement | Y | Y | Y | |
| CREATE FUNCTION (Sourced) statement | Y | Y | Y | |
| CREATE FUNCTION (SQL Scalar) statement | Y | Y | Y | Not all features of the statement are supported on each platform. |
| CREATE FUNCTION (SQL Table) statement | Y | P | Y | Partial support allows a single RETURN statement. |
| CREATE INDEX over column list | Y | Y | Y | |
| CREATE UNIQUE INDEX over column list | Y | Y | Y | |
| CREATE PROCEDURE (External) statement | Y | Y | Y | |
| CREATE PROCEDURE (SQL) statement | 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 | Y | DB2 for i tables always capture changes - the clause is ignored. |
| DEFAULT clause with no specified value (ALTAB, CRTAB, DGTTAB) | Y | Y | Y | |
| DEFAULT clause with specified value (ALTAB, CRTAB, DGTTAB) | Y | Y | Y | |
| IMPLICITLY HIDDEN clause (ALTAB, CRTAB) | Y | Y | Y | |
| VOLATILE clause (ALTAB, CRTAB) | Y | Y | Y | |
| IDENTITY clause (ALTAB, CRTAB, DGTTAB) | Y | Y | Y | |
| ROW CHANGE TIMESTAMP clause (ALTAB, CRTAB) | 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 | Y | Y | |
| 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 | Y | |
| CREATE SEQUENCE statement | Y | Y | Y | |
| CREATE TRIGGER statement - BEFORE or AFTER trigger on a table | Y | Y | Y | |
| CREATE TRIGGER statement - INSTEAD OF trigger on a view | Y | P | 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) | Y | 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 | |
| RENAME INDEX statement | Y | Y | Y |
Table 18. Authorization
| Language element | LUW | z/OS | i | Notes |
|---|---|---|---|---|
| GRANT (function or procedure privileges) statement | Y | Y | Y | |
| GRANT (package privileges) statement | Y | Y | Y | |
| GRANT (sequence privileges) statement | Y | Y | Y | |
| GRANT (table privileges) statement | Y | Y | Y | |
| REVOKE (function privileges) statement | Y | Y | Y | |
| REVOKE (procedure privileges) statement | Y | P | Y | |
| REVOKE (package privileges) statement | Y | Y | Y | |
| REVOKE (sequence privileges) statement | Y | Y | Y | |
| REVOKE (table privileges) statement | Y | Y | Y |
Table 19. Miscellaneous statements
| Language element | LUW | z/OS | i | Notes |
|---|---|---|---|---|
| BEGIN and END DECLARE SECTION statement | Y | Y | Y | |
| DECLARE STATEMENT statement | P | Y | Y | Starting with LUW 9.7, supports the statement only in compound SQL (compiled) statements. |
| FREE LOCATOR statement | Y | Y | Y | |
| INCLUDE statement | Y | Y | Y | |
| LOCK TABLE statement | Y | Y | Y | |
| REFRESH TABLE statement | Y | Y | Y | |
| SET CURRENT DECFLOAT ROUNDING MODE statement | P | Y | Y | |
| SET CURRENT DEGREE statement | Y | Y | Y | |
| SET ENCRYPTION PASSWORD statement | Y | Y | Y | |
| SET PATH statement | Y | Y | Y | |
| SET SCHEMA statement | Y | Y | Y | |
| WHENEVER statement | Y | Y | Y |
Table 20. Host languages
| Language element | LUW | z/OS | i | Notes |
|---|---|---|---|---|
| C or C++ | Y | Y | Y | |
| COBOL | Y | Y | Y | |
| FORTRAN | Y | Y | Y | |
| Java (SQLJ) | Y | Y | Y | |
| PL/I | Y | Y | Y | |
| REXX | Y | Y | 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.
-
Previous version of these tables
-
SQL Reference for Cross-Platform Development
