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 9.1 MB) and the respective DB2 product SQL Reference books.
Please read the disclaimer before using the tables.
- DB2 Version 9.5 for Linux, UNIX, and Windows
- DB2 Version 9.1 for z/OS
- DB2 Version 6.1 for IBM i
- Basic elements
- Data types
- Special registers
- Predicates
- Column functions
- Scalar functions
- Queries
- Basic statements
- Data change statements
- Cursor-oriented operations
- Connection and transaction control
- Dynamic facilities
- Control statements
- Data definition
- Authorization
- Miscellaneous statements
- Host languages
Key
| Y | Product includes full support. |
| P | Product includes partial support; check product information. |
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 | |
| Host Structures | Y | Y | Y | |
| "Friendly" arithmetic and conversion | 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 | |
| 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 | |
| Diagnostic Area | Y | Y | Y |
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 | |
| User-defined distinct types | Y | Y | Y |
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 | Y | Y | Y | |
| CURRENT_DATE | Y | Y | Y | |
| CURRENT DECFLOAT ROUNDING MODE | Y | Y | Y | |
| CURRENT DEGREE | Y | Y | Y | |
| CURRENT PATH | Y | Y | Y | |
| CURRENT_PATH | Y | Y | Y | |
| CURRENT SCHEMA | Y | Y | Y | |
| CURRENT_SCHEMA | Y | Y | Y | |
| CURRENT SERVER | Y | Y | Y | |
| CURRENT TIME | Y | Y | Y | |
| CURRENT_TIME | Y | Y | Y | |
| CURRENT TIMESTAMP | Y | Y | Y | |
| CURRENT_TIMESTAMP | Y | Y | Y | |
| CURRENT TIMEZONE | Y | Y | Y | |
| SESSION_USER | Y | Y | Y | |
| USER | Y | Y | Y |
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 |
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 |
Scalar functions
| Language element | LUW | z/OS | i | Notes |
|---|---|---|---|---|
| ABSVAL or ABS | Y | Y | Y | |
| ACOS | 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 | |
| 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 and 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 | |
| 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 | |
| LCASE | 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 | |
| LOWER | Y | Y | Y | |
| LTRIM | 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 | 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 | |
| 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 and 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 | Y | Y | Y | |
| RTRIM | P | P | Y | LUW does not support 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 and 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 or TO_DATE | Y | Y | Y | |
| TIMESTAMPDIFF | Y | Y | Y | |
| TOTALORDER | Y | Y | Y | |
| TRANSLATE | Y | Y | Y | |
| TRUNCATE or trUNC | Y | Y | Y | |
| UCASE or UPPER | Y | Y | Y | |
| VALUE | Y | Y | Y | |
| VARCHAR (no string units) | P | Y | Y | |
| VARCHAR_FORMAT or TO_CHAR | Y | Y | Y | |
| VARGRAPHIC (no string units) | Y | Y | Y | |
| WEEK | Y | Y | Y | |
| WEEK_ISO | Y | Y | Y | |
| YEAR | Y | Y | Y |
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 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 |
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 | |
| VALUES INTO statement | Y | Y | Y |
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 | |
| Searched UPDATE statement | Y | Y | Y |
Cursor-oriented operations
| Language element | LUW | z/OS | i | 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. |
| FETCH statement with ABSOLUTE | P | Y | P | |
| OPEN statement | Y | Y | Y | |
| Positioned UPDATE statement | Y | Y | Y |
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 SQL2003 Standard) | Y | Y | Y | |
| RS (REPEATABLE READ in SQL2003 Standard) | Y | Y | Y | |
| CS (READ COMMITTED in SQL2003 Standard) | Y | Y | Y | |
| UR (READ UNCOMMITTED in SQL2003 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 |
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. |
Control statements
| Language element | LUW | z/OS | i | Notes |
|---|---|---|---|---|
| Control statements in SQL procedures | Y | Y | Y | |
| Control statements in SQL functions | P | P | Y | Partial support allows a single RETURN statement in SQL 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 |
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 18 byte object names (except schema name ) | Y | Y | Y | Some platforms support longer object names. |
| Support 10 byte schema names | Y | Y | Y | Some platforms support longer schema names. |
| 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 (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 | DB2 for i 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 | |
| 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) | 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 |
Authorization
| Language element | LUW | z/OS | i | Notes |
|---|---|---|---|---|
| GRANT Function or Procedure Privileges statement | Y | Y | Y | |
| GRANT Package Privileges statement having no WITH GRANT OPTION clause | Y | Y | Y | |
| GRANT Package Privileges statement having a WITH GRANT OPTION clause | 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 |
Miscellaneous statements
| Language element | LUW | z/OS | i | Notes |
|---|---|---|---|---|
| BEGIN and END DECLARE SECTION statement | Y | Y | Y | |
| DECLARE STATEMENT statement | P | Y | Y | LUW supports the statement only in SQL stored procedures. |
| 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 | Y | Y | Y | |
| SET CURRENT DEGREE statement | P | 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 |
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
Comments (Undergoing maintenance)
Next steps from IBM
My developerWorks community
Tags
Use the search field to find all types of content in My developerWorks with that tag.
Use the slider bar to see more or fewer tags.
Popular tags shows the top tags for this particular content zone (for example, Java technology, Linux, WebSphere).
My tags shows your tags for this particular content zone (for example, Java technology, Linux, WebSphere).
Use the search field to find all types of content in My developerWorks with that tag. Popular tags shows the top tags for this particular content zone (for example, Java technology, Linux, WebSphere). My tags shows your tags for this particular content zone (for example, Java technology, Linux, WebSphere).
Popular article tags |
My article tagsSkip to tags list
Popular article tags |
My article tags
