BBj Support for SQL-99 and SQL-2003 Features
The table below shows the features defined in SQL-99 (ANSI/ISO/IEC 9075-2-1999: for Information Technology – Database Languages – SQL – Part 2: Foundation (SQL/Foundation)) and SQL-2003 (ISO/IEC 9075-2: Information technology — Database languages — SQL — Part 2: Foundation (SQL/Foundation)) and designates whether they are implemented in the BBj SQL engine.
Key
|
Yes |
|
Feature is supported. |
|
Yes* |
|
Feature is supported (e.g. through JDBC) but may differ with the SQL standard. See comments for more information. |
|
Partial |
|
Feature is partially supported. |
|
No |
|
Feature is not supported. |
|
N/A |
|
Not applicable. |
|
Feature |
SQL-99 |
SQL-2003Mandatory |
Note |
|
Numeric data types |
Yes |
Yes |
|
|
INTEGER and SMALLINT data types (including all spellings) |
Yes |
Yes |
|
|
REAL, DOUBLE PRECISON, and FLOAT data types |
Yes |
Yes |
|
|
DECIMAL and NUMERIC data types |
Yes |
Yes |
|
|
Arithmetic operators |
Yes |
Yes |
|
|
Numeric comparison |
Yes |
Yes |
|
|
Implicit casting among the numeric data types |
Yes |
Yes |
|
|
Character data types |
Yes |
Yes |
|
|
CHARACTER data type (including all its spellings) |
Yes |
Yes |
|
|
CHARACTER VARYING data type (including all its spellings) |
Yes |
Yes |
|
|
Character literals |
Yes |
Yes |
|
|
CHARACTER_LENGTH function |
Yes* |
Yes* |
Called CHARACTER_LENGTH and LENGTH. {fn LENGTH(...)} is according to JDBC specification. |
|
OCTET_LENGTH function |
Yes |
Yes |
|
|
SUBSTRING function |
Yes |
Yes |
|
|
Character concatenation |
Yes |
Yes |
|
|
UPPER and LOWER functions |
Yes |
Yes |
|
|
TRIM function |
Yes |
Yes |
|
|
Implicit casting among the character data types |
Yes |
Yes |
|
|
POSITION function |
Yes* |
Yes* |
Called LOCATE. {fn LOCATE(...)} is according to JDBC specification. |
|
Character comparison |
Yes |
Yes |
|
|
Identifiers |
Yes |
Yes |
|
|
Delimited identifiers |
Yes |
Yes |
|
|
Lower case identifiers |
Yes |
Yes |
|
|
Trailing underscore |
Yes |
Yes |
|
|
Basic query specification |
Yes |
Yes |
|
|
SELECT DISTINCT |
Yes |
Yes |
|
|
GROUP BY clause |
Yes |
Yes |
|
|
GROUP BY can contain columns not in select-list |
Yes |
Yes |
|
|
Select list items can be renamed |
Yes |
Yes |
|
|
HAVING clause |
Yes |
Yes |
|
|
Qualified * in select list |
Yes |
Yes |
|
|
Correlation names in the FROM clause |
Yes |
Yes |
|
|
Rename columns in the FROM clause |
No |
No |
|
|
Basic predicates and search conditions |
Yes |
Yes |
|
|
Comparison predicate |
Yes |
Yes |
|
|
BETWEEN predicate |
Yes |
Yes |
|
|
IN predicate with list of values |
Yes |
Yes |
|
|
LIKE predicate |
Yes |
Yes |
|
|
LIKE predicate: ESCAPE clause |
Yes |
Yes |
|
|
NULL predicate |
Yes |
Yes |
|
|
Quantified comparison predicate |
Yes |
Yes |
|
|
EXISTS predicate |
Yes |
Yes |
|
|
Subqueries in comparison predicate |
Yes |
Yes |
|
|
Subqueries in IN predicate |
Yes |
Yes |
|
|
Subqueries in quantified comparison predicate |
Yes |
Yes |
|
|
Correlated subqueries |
Yes |
Yes |
|
|
Search condition |
Yes |
Yes |
|
|
Basic query expressions |
Yes |
Yes |
|
|
UNION DISTINCT table operator |
Yes |
Yes |
|
|
UNION ALL table operator |
Yes |
Yes |
|
|
EXCEPT DISTINCT table operator |
No |
No |
|
|
Columns combined via table operators need not have exactly the same data type |
Yes |
Yes |
|
|
Table operators in subqueries |
Yes |
Yes |
|
|
Basic Privileges |
Partial |
Partial |
|
|
SELECT privilege at the table level |
Yes |
Yes |
|
|
DELETE privilege |
Yes |
Yes |
|
|
INSERT privilege at the table level |
Yes |
Yes |
|
|
UPDATE privilege at the table level |
Yes |
Yes |
|
|
UPDATE privilege at the column level |
No |
No |
|
|
REFERENCES privilege at the table level |
No |
No |
|
|
REFERENCES privilege at the column level |
No |
No |
|
|
WITH GRANT OPTION |
Yes |
Yes |
|
|
USAGE privilege |
No |
No |
|
|
EXECUTE privilege |
Yes |
Yes |
|
|
Set functions |
Yes |
Yes |
|
|
AVG |
Yes |
Yes |
|
|
COUNT |
Yes |
Yes |
|
|
MAX |
Yes |
Yes |
|
|
MIN |
Yes |
Yes |
|
|
SUM |
Yes |
Yes |
|
|
ALL quantifier |
Yes |
Yes |
|
|
DISTINCT qualifier |
Yes |
Yes |
|
|
Basic data manipulation |
Yes |
Yes |
|
|
INSERT statement |
Yes |
Yes |
|
|
Searched UPDATE statement |
Yes |
Partial |
correlation name not supported |
|
Searched DELETE statement |
Yes |
Partial |
correlation name not supported |
|
Single row select statement |
Yes |
Yes |
|
|
Basic cursor support |
Yes* |
Yes* |
Through JDBC |
|
Declare cursor |
No |
No |
|
|
ORDER BY columns need not be in select list |
Yes |
Yes |
|
|
Value expressions in ORDER BY clause |
Yes |
Yes |
|
|
OPEN statement |
No |
No |
|
|
Positioned UPDATE statement |
Yes |
Partial |
correlation name not supported |
|
Positioned DELETE statement |
Yes |
Partial |
correlation name not supported |
|
CLOSE statement |
No |
No |
|
|
FETCH statement |
No |
No |
|
|
WITH HOLD cursors |
No |
No |
|
|
Null value support (nulls in lieu of values) |
Yes |
Yes |
|
|
Basic integrity constraints |
Yes* |
Yes* |
Depends on the file type |
|
NOT NULL constraints |
Yes* |
Yes* |
Supported in ESQL only. |
|
UNIQUE constraints of NOT NULL columns |
Yes* |
Yes* |
Supported in ESQL only. |
|
PRIMARY KEY constraints |
Yes |
Yes |
|
|
Basic FOREIGN KEY constraint with the NO ACTION default |
Yes* |
Yes* |
Supported in ESQL only. |
|
CHECK constraints |
Yes* |
Yes* |
Supported in ESQL only. |
|
Column defaults |
Yes* |
Yes* |
Supported in ESQL only. |
|
NOT NULL inferred on PRIMARY KEY |
Yes |
Yes |
|
|
Names in a foreign key can be specified in any order |
Yes |
Yes |
|
|
Transaction support |
Yes* |
Yes* |
Transactions are only supported in ESQL and Journaled files. |
|
COMMIT statement |
Yes* |
Yes* |
Through JDBC Connection.commit. Only supported in ESQL and Journaled files. |
|
ROLLBACK statement |
Yes* |
Yes* |
Through JDBC Connection.rollback. Only supported in ESQL and Journaled files. |
|
Basic SET TRANSACTION statement |
Yes*. |
Yes*. |
See below. |
|
SET TRANSACTION statement: ISOLATION LEVEL SERIALIZABLE clause |
Yes* |
Yes* |
Connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE) is according to JDBC specification. Only supported in ESQL and Journaled files. |
|
SET TRANSACTION statement: READ ONLY and READ WRITE clauses |
Yes* |
Yes* |
No SQL syntax. Connection.setReadWrite() is according to JDBC specification. Only supported in ESQL and Journaled files. |
|
Updatable queries with subqueries |
No |
No |
|
|
SQL comments using leading double minus |
No |
No |
|
|
SQLSTATE support |
Yes |
Yes |
For most values. |
|
Module language |
N/A |
No |
|
|
Basic information schema |
No |
N/A |
Note: JDBC DatabaseMetadata is ok. |
|
COLUMNS view |
No |
N/A |
|
|
TABLES view |
No |
N/A |
|
|
VIEWS view |
No |
N/A |
|
|
TABLE_CONSTRAINTS view |
No |
N/A |
|
|
REFERENTIAL_CONSTRAINTS |
No |
N/A |
|
|
CHECK_CONSTRAINTS |
No |
N/A |
|
|
Basic schema manipulation |
Yes |
Yes |
|
|
CREATE TABLE statement to create persistent base tables |
Yes |
Yes |
|
|
CREATE VIEW statement |
Yes |
Yes |
|
|
GRANT statement |
Yes |
Yes |
|
|
ALTER TABLE statement: ADD COLUMN clause |
Yes |
Yes |
|
|
DROP TABLE statement: RESTRICT clause |
Yes Implicit |
Yes Implicit |
|
|
DROP VIEW statement: RESTRICT clause |
Yes Implicit |
Yes Implicit |
|
|
REVOKE statement: RESTRICT clause |
No |
No |
|
|
Basic joined tables |
Yes |
Yes |
|
|
Inner join (but not necessarily the INNER keyword) |
Yes |
Yes |
|
|
INNER keyword |
Yes |
Yes |
|
|
LEFT OUTER JOIN |
Yes |
Yes |
|
|
RIGHT OUTER JOIN |
Yes |
Yes |
|
|
Outer joins can be nested |
Yes |
Yes |
|
|
The inner table in a left or right outer join can also be used in an inner join |
Yes |
Yes |
|
|
All comparison operators are supported (rather than just =) |
Yes |
Yes |
|
|
Basic date and time |
Yes* |
Yes* |
See below. |
|
DATE data type (including DATE literal) |
Yes* |
Yes* |
DATE literal is implemented as built-in function. {d 'yyyy-mm-dd'} is according to JDBC specification. |
|
TIME data type (including TIME literal) with fractional seconds precision of 0 |
Yes* |
Yes* |
TIME literal is implemented as built-in function. No precision in datatype. {t' hh:mm:ss'} is according to JDBC specification. |
|
TIMESTAMP data type (including TIMESTAMP literal) with fractional seconds precision of 0 and 6 |
Yes* |
Yes* |
TIMESTAMP literal is implemented as built-in function. No precision spec in datatype. No timezone spec in datatype. {ts 'yyyy-mm-dd hh:mm:ss.f...'} is according to JDBC specification. |
|
Comparison predicate on DATE, TIME, and TIMESTAMP data types |
Yes |
Yes |
|
|
Explicit CAST between datetime types and character types |
Yes |
Yes |
|
|
CURRENT_DATE |
Yes* |
Yes* |
No time zone in datetime value expression |
|
LOCALTIME |
Yes*. |
Yes*. |
{fn CURTIME()} is according to JDBC specification. |
|
LOCALTIMESTAMP |
No. |
No. |
|
|
UNION and EXCEPT in views |
Partial |
Partial |
UNION is supported. |
|
Grouped operations |
Yes |
Yes |
|
|
WHERE, GROUP BY, and HAVING clauses supported in queries with grouped views |
Yes |
Yes |
|
|
Multiple tables supported in queries with grouped views |
Yes |
Yes |
|
|
Set functions supported in queries with grouped views |
Yes |
Yes |
|
|
Subqueries with GROUP BY and HAVING clauses and grouped views |
Yes |
Yes |
|
|
Single row SELECT with GROUP BY and HAVING clauses and grouped views |
Yes |
Yes |
|
|
Multiple module support |
No |
No |
|
|
CAST function |
Yes |
Yes |
|
|
Explicit defaults |
Yes |
Yes |
|
|
CASE expression |
Yes |
Yes |
|
|
Simple CASE |
Yes |
Yes |
|
|
Searched CASE |
Yes |
Yes |
|
|
NULLIF function |
Yes |
Yes |
|
|
COALESCE function |
Yes |
Yes |
|
|
Schema definition statement |
Yes |
Yes |
|
|
Create schema |
No |
No |
|
|
CREATE TABLE for persistent base tables |
Yes |
Yes |
|
|
CREATE VIEW |
Yes |
Yes |
|
|
CREATE VIEW: WITH CHECK OPTION |
No |
No |
|
|
GRANT statement |
Yes |
Yes |
|
|
Scalar subquery values |
Yes |
Yes |
|
|
Expanded NULL predicate |
Yes |
Yes |
|
|
Features and conformance views |
No |
N/A |
|
|
SQL_FEATURES view |
No |
N/A |
|
|
SQL_SIZING view |
No |
N/A |
|
|
SQL_LANGUAGES view |
No |
N/A |
|
|
Basic flagging |
No |
No |
|
|
Distinct data types |
No |
No |
|
|
USER_DEFINED_TYPES view |
No |
N/A |
|
|
Basic SQL invoked routines |
Yes |
Yes |
Language BBj. |
|
User-defined functions with no overloading |
Yes |
Yes |
|
|
User-defined stored procedures with no overloading |
Yes |
Yes |
|
|
Function invocation |
Yes |
Yes |
|
|
CALL statement |
Yes |
Yes |
|
|
RETURN statement |
No |
No |
|
|
ROUTINES view |
No |
N/A |
|
|
PARAMETERS view |
No |
N/A |
|
|
IN predicate with one list element |
N/A |
Yes |
|
|
|
|
|
|
|
|
|
|
|
|
EmbeddedAda |
N/A |
No |
|
|
Embedded C |
N/A |
No |
|
|
Embedded COBOL |
N/A |
No |
|
|
Embedded Fortran |
N/A |
No |
|
|
Embedded MUMPS |
N/A |
No |
|
|
Embedded Pascal |
N/A |
No |
|
|
Embedded PL/I |
N/A |
No |
|
|
Direct SQL |
N/A |
No |
|
|
Basic dynamic SQL |
N/A |
No |
|
|
Extended dynamic SQL |
N/A |
No |
|
|
<describe input statement> |
N/A |
No |
|
|
Untyped SQL-invoked function arguments |
N/A |
No |
|
|
Dynamic specification of cursor attributes |
N/A |
No |
|
|
Extensions to embedded SQL exception declarations |
N/A |
No |
|
|
Enhanced execution rights |
N/A |
No |
|
|
Module language Ada |
N/A |
No |
|
|
Module language C |
N/A |
No |
|
|
Module language COBOL |
N/A |
No |
|
|
Module language Fortran |
N/A |
No |
|
|
Module language MUMPS |
N/A |
No |
|
|
Module language Pascal |
N/A |
No |
|
|
Module language PL/I |
N/A |
No |
|
|
Routine language Ada |
N/A |
No |
|
|
Routine language C |
N/A |
No |
|
|
Routine language COBOL |
N/A |
No |
|
|
Routine language Fortran |
N/A |
No |
|
|
Routine language MUMPS |
N/A |
No |
|
|
Routine language Pascal |
N/A |
No |
|
|
Routine language PL/I |
N/A |
No |
|
|
Routine language SQL |
N/A |
No |
|
|
CASCADE drop behavior |
No |
No |
|
|
ALTER TABLE statement: DROP COLUMN clause |
Yes* |
Yes* |
Only supported in ESQL. |
|
Extended REVOKE statement |
No |
No |
|
|
REVOKE statement performed by other than the owner of a schema object |
Yes |
Yes |
|
|
REVOKE statement: GRANT OPTION FOR clause |
No |
No |
|
|
REVOKE statement to revoke a privilege that the grantee has WITH GRANT OPTION |
No |
No |
|
|
Intervals and datetime arithmetic |
No |
No |
|
|
OVERLAPS predicate |
N/A |
No |
|
|
Isolation levels other than SERIALIZABLE |
Yes* |
Yes* |
Only supported in ESQL and Journaled files. |
|
READ UNCOMMITTED isolation level |
Yes* |
Yes* |
Only supported in ESQL and Journaled files. |
|
READ COMMITTED isolation level |
Yes* |
Yes* |
Only supported in ESQL and Journaled files. |
|
REPEATABLE READ isolation level |
Yes* |
Yes* |
Only supported in ESQL and Journaled files. |
|
Basic diagnostics management |
No |
No |
|
|
GET DIAGNOSTICS statement |
No |
No |
|
|
SET TRANSACTION statement: DIAGNOSTICS SIZE clause |
No |
No |
|
|
Multiple schemas per user |
No |
No |
|
|
Referential delete actions |
No |
No |
|
|
INSERT statement: DEFAULT VALUES clause |
No |
No |
|
|
Privilege tables |
No |
No |
|
|
TABLE_PRIVILEGES view |
No |
No |
|
|
COLUMN_PRIVILEGES view |
No |
No |
|
|
USAGE_PRIVILEGES view |
No |
No |
|
|
Domain support |
No |
No |
|
|
Extended CASE expression |
N/A |
|
|
|
Compound character literals |
No |
No |
|
|
LIKE enhancements |
Partial |
Partial |
|
|
UNIQUE predicate |
No |
No |
|
|
CORRESPONDING in query expressions |
No |
No |
|
|
INTERSECT table operator |
No |
No |
|
|
INTERSECT DISTINCT table operator |
No |
No |
|
|
INTERSECT ALL table operator |
No |
No |
|
|
EXCEPT ALL table operator |
No |
No |
|
|
MERGE statement |
N/A |
No |
|
|
User authorization |
No |
No |
|
|
Usage tables |
No |
No |
|
|
Subprogram support |
No |
No |
|
|
Extended schema manipulation |
Partial |
Partial |
|
|
ALTER TABLE statement: ALTER COLUMN clause |
Partial |
Partial |
|
|
ALTER TABLE statement: ADD CONSTRAINT clause |
Partial |
Partial |
|
|
ALTER TABLE statement: DROP CONSTRAINT clause |
Yes |
Yes |
|
|
Long identifiers |
Yes |
Yes |
|
|
Unicode escapes in identifiers |
N/A |
No |
|
|
Unicode escapes in literals |
N/A |
No |
|
|
Extended joined table |
No |
No |
|
|
NATURAL JOIN |
No |
No |
|
|
FULL OUTER JOIN |
Yes |
Yes |
|
|
CROSS JOIN |
No |
No |
|
|
Named column joins for LOBs, arrays, and multisets |
N/A |
No |
|
|
Time zone specification |
No |
No |
|
|
National character |
No |
No |
|
|
Read-only scrollable cursors |
Yes |
Yes |
|
|
FETCH with explicit NEXT |
No |
No |
|
|
FETCH FIRST |
No |
No |
|
|
FETCH LAST |
No |
No |
|
|
FETCH PRIOR |
No |
No |
|
|
FETCH ABSOLUTE |
No |
No |
|
|
FETCH RELATIVE |
No |
No |
|
|
Extended set function support |
No |
No |
|
|
Mixed column references in set functions |
N/A |
No |
|
|
Character set definition |
No |
No |
|
|
Named character sets |
No |
No |
|
|
Constraint management |
Yes |
Yes |
|
|
Enhanced documentation tables |
No |
No |
|
|
SQL_SIZING_PROFILES view |
No |
No |
|
|
SQL_IMPLEMENTATION_INFO view |
No |
No |
|
|
SQL_PACKAGES view |
No |
No |
|
|
Assertions |
No |
No |
|
|
Temporary tables |
Partial |
Partial |
|
|
Enhanced seconds precision |
No |
No |
|
|
Full value expressions |
No |
No |
|
|
Truth value tests |
No |
No |
|
|
Derived tables |
No |
No |
|
|
Indicator data types |
No |
No |
|
|
Row and table constructors |
Yes |
Yes |
|
|
Catalog name qualifiers |
No |
No |
|
|
Simple tables |
No |
No |
|
|
Subqueries in CHECK |
|
|
|
|
Retrospective check constraints |
N/A |
|
|
|
Collation and translation |
No |
No |
|
|
Enhanced collation support |
N/A |
No |
|
|
SQL-session and client module collations |
N/A |
No |
|
|
Translation support |
N/A |
No |
|
|
Additional translation documentation |
N/A |
No |
|
|
Referential update actions |
Partial |
Partial |
|
|
ALTER domain |
No |
No |
|
|
Deferrable constraints |
No |
No |
|
|
INSERT column privileges |
No |
No |
|
|
Referential MATCH types |
No |
No |
|
|
View CHECK enhancements |
No |
No |
|
|
Session management |
No |
No |
|
|
Connection management |
No |
No |
|
|
Self-referencing operations |
Yes |
Yes |
|
|
Insensitive cursors |
Yes* |
Yes* |
Through JDBC. |
|
Full set function |
Partial |
Partial |
DISTINCT in more than one aggregate function will not work, but SELECT DISTINCT with DISTINCT in one aggregate function will work. |
|
Extended flagging |
No |
N/A |
|
|
Extended flagging -- Part 1, Subclause 8.5, "SQL flagger": With "level of flagging" specified to be Core SQL Flagging and "extent of checking" specified to be Catalog Lookup |
No |
No |
|
|
Local table references |
No |
No |
|
|
Full cursor update |
Partial |
Partial |
|
|
Updatable scrollable cursors |
Partial |
Partial |
|
|
Updatable ordered cursors |
No |
No |
|
|
Basic structured types |
No |
No |
|
|
Enhanced structured types |
No |
No |
|
|
Final structured types |
N/A |
No |
|
|
Self-referencing structured types |
N/A |
No |
|
|
Create method by specific method name |
N/A |
No |
|
|
Permutable UDT options list |
N/A |
No |
|
|
Basic reference types |
No |
No |
|
|
Enhanced reference types |
No |
No |
|
|
Create table of type |
No |
No |
|
|
SQL paths in function and type name resolution |
No |
No |
|
|
Subtables |
No |
No |
|
|
Basic array support |
No |
No |
|
|
Arrays of built-in data types |
No |
No |
|
|
Arrays of distinct types |
No |
No |
|
|
Array expressions |
No |
No |
|
|
Arrays of user-defined types |
No |
No |
|
|
Arrays of reference types |
No |
No |
|
|
Array constructors by query |
N/A |
No |
|
|
Optional array bounds |
N/A |
No |
|
|
Array element assignment |
N/A |
No |
|
|
ONLY in query expressions |
No |
No |
|
|
Type predicate |
No |
No |
|
|
Subtype treatment |
No |
No |
|
|
Subtype treatment for references |
N/A |
No |
|
|
SQL-invoked routines on arrays |
No |
No |
|
|
Array parameters |
No |
No |
|
|
Array as result type of functions |
No |
No |
|
|
SQL-invoked routines on multisets |
N/A |
No |
|
|
User-defined cast functions |
No |
No |
|
|
Structured type locators |
No |
No |
|
|
Array locators |
No |
No |
|
|
Multiset locators |
N/A |
No |
|
|
Transform functions |
No |
No |
|
|
Alter transform statement |
N/A |
No |
|
|
User-defined orderings |
No |
No |
|
|
Specific type method |
No |
No |
|
|
Basic multiset support |
N/A |
No |
|
|
Multisets of user-defined types |
N/A |
No |
|
|
Multisets of reference types |
N/A |
No |
|
|
Advanced multiset support |
N/A |
No |
|
|
Nested collection types |
N/A |
No |
|
|
Unique constraint on entire row |
N/A |
No |
|
|
Timestamp in Information Schema |
No |
No |
|
|
BOOLEAN data type |
No |
No |
|
|
Basic LOB data type support |
Yes |
Yes |
|
|
BLOB data type -- Subclause 5.2, "<token> and <separator>": The <reserved word>s BINARY, BLOB, LARGE, and OBJECT -- Subclause 5.3, "<literal>": <binary string literal> -- Subclause 6.1, "<data type>": The BINARY LARGE OBJECT data type -- Subclause 6.28, "<string value expression>": For values of type BINARY LARGE OBJECT -- Subclause 13.6, "Data type correspondences": Type correspondences for BINARY LARGE OBJECT for all supported languages |
Yes |
Yes |
|
|
CLOB data type -- Subclause 5.2, "<token> and <separator>": The <reserved word>s CHARACTER, CLOB, LARGE, and OBJECT -- Subclause 6.1, "<data type>": The CHARACTER LARGE OBJECT data type -- Subclause 6.28, "<string value expression>": For values of type CHARACTER LARGE OBJECT -- Subclause 13.6, "Data type correspondences": Type correspondences for CHARACTER LARGE OBJECT for all supported languages -- The implicit casting among the fixed-length and variable-length character string types supported by subfeature E021-10 is extended to support the character large object type |
Yes |
Yes |
|
|
POSITION, LENGTH, LOWER, TRIM, UPPER, and SUBSTRING functions for LOB data types -- Subclause 6.27, "<numeric value function>": The <position expression> for expressions of type BINARY LARGE OBJECT and CHARACTER LARGE OBJECT -- Subclause 6.27, "<numeric value function>": The <char length expression> for expressions of type CHARACTER LARGE OBJECT -- Subclause 6.27, "<numeric value function>": The <octet length expression> for expressions of type BINARY LARGE OBJECT and CHARACTER LARGE OBJECT -- Subclause 6.29, "<string value function>": The <fold> function for expressions of type CHARACTER LARGE OBJECT -- Subclause 6.29, "<string value function>": The <trim function> for expressions of type CHARACTER LARGE OBJECT -- Subclause 6.29, "<string value function>": The <blob trim function> -- Subclause 6.29, "<string value function>": The <character substring function> for expressions of type CHARACTER LARGE OBJECT -- Subclause 6.29, "<string value function>": The <blob substring function> |
Yes* |
Yes* |
May get unexpected results using binary data. |
|
Concatenation of LOB data types -- Subclause 6.28, "<string value expression>": The <concatenation> expression for expressions of type CHARACTER LARGE OBJECT -- Subclause 6.28, "<string value expression>": The <blob concatenation> expression |
Yes |
Yes |
|
|
LOB locator: non-holdable -- Subclause 13.3, "<externally-invoked procedure>": <locator indication> -- Subclause 14.14, "<free locator statement>" |
No |
No |
|
|
Extended LOB data type support |
Partial |
Partial |
CAST and string value functions implemented. No comparison or ordering. |
|
Row types |
No |
No |
|
|
MAX and MIN for row types |
N/A |
No |
|
|
Explicit aliases for all-fields reference |
N/A |
No |
|
|
UCS support |
N/A |
No |
|
|
BIGINT data type |
N/A |
Yes |
|
|
Updatable joins, unions, and columns |
No |
No |
|
|
WITH (excluding RECURSIVE) in query expression |
No |
No |
|
|
WITH (excluding RECURSIVE) in subquery |
N/A |
No |
|
|
Recursive query |
No |
No |
|
|
Recursive query in subquery |
N/A |
No |
|
|
SIMILAR predicate |
No |
No |
|
|
DISTINCT predicate |
No |
No |
|
|
DISTINCT predicate with negation |
N/A |
No |
|
|
LIKE clause in table definition |
No |
No |
|
|
AS subquery clause in table definition |
N/A |
No |
|
|
Extended LIKE clause in table definition |
N/A |
No |
|
|
Identity columns |
N/A |
Yes* |
Only supported in ESQL files. |
|
Generated columns |
N/A |
Yes* |
Only supported in ESQL. |
|
Sequence generator support |
N/A |
Yes |
|
|
Referential action RESTRICT |
|
|
|
|
Comparable data types for referential constraints |
|
|
|
|
Basic trigger capability |
Yes* |
Yes* |
|
|
Triggers activated on UPDATE, INSERT, or DELETE of one base table. |
Yes |
Yes |
|
|
BEFORE triggers |
Yes* |
Yes* |
|
|
AFTER triggers |
Yes |
Yes |
|
|
FOR EACH ROW triggers |
No |
No |
|
|
Ability to specify a search condition that shall be True before the trigger is invoked. |
No |
No |
|
|
Support for run-time rules for the interaction of triggers and constraints. |
|
|
|
|
TRIGGER privilege |
No |
No |
|
|
Multiple triggers for the same event are executed in the order in which they were created in the catalog. |
Yes |
Yes |
|
|
Enhanced trigger capability |
|
|
|
|
Sensitive cursors |
No |
No |
|
|
START TRANSACTION statement |
No |
No |
|
|
SET TRANSACTION statement: LOCAL option |
No |
No |
|
|
Chained transactions |
No |
No |
|
|
Savepoints |
Yes |
Yes |
Only supported in ESQL files. |
|
Enhanced savepoint management |
N/A |
No |
|
|
SELECT privilege with column granularity |
Yes |
Yes |
|
|
Functional dependencies |
No |
No |
|
|
OVERLAY function |
No |
No |
|
|
Overloading of SQL-invoked functions and procedures |
|
|
|
|
Explicit security for external routines |
|
|
|
|
Explicit security for SQL routines |
N/A |
No |
|
|
Qualified SQL parameter references |
N/A |
No |
|
|
Table functions |
N/A |
|
|
|
Basic roles |
No |
No |
|
|
Extended roles |
No |
No |
|
|
Bracketed SQL comments (/*...*/ comments) |
No |
No |
|
|
Extended grouping capabilities |
|
|
|
|
Nested and concatenated GROUPING SETS |
N/A |
|
|
|
Multiargument GROUPING function |
N/A |
|
|
|
GROUP BY DISINCT |
N/A |
|
|
|
ABS and MOD functions |
Yes |
Yes |
|
|
Symmetric BETWEEN predicate |
No |
No |
|
|
Result sets return value |
No |
No |
|
|
LATERAL derived table |
No |
No |
|
|
Enhanced EXISTS predicate |
No |
No |
|
|
Transaction counts |
No |
No |
|
|
Updatable table references |
No |
N/A |
|
|
Optional key words for default syntax |
No |
No |
|
|
Holdable locators |
No |
No |
|
|
Array-returning external SQL-invoked functions |
No |
No |
|
|
Multiset-returning external SQL-invoked functions |
N/A |
No |
|
|
Regular expression substring function |
No |
No |
|
|
UNIQUE constraints of possibly null columns |
No |
No |
|
|
Local cursor references |
No |
No |
|
|
Elementary OLAP operations |
N/A |
No |
|
|
Advanced OLAP operations |
N/A |
No |
|
|
Sampling |
N/A |
No |
|
|
Enhanced numeric functions |
N/A |
No |
|
|
Multiple column assignment |
N/A |
No |
|
|
SQL-schema statements in SQL routines |
No |
No |
|
|
SQL-dynamic statements in SQL routines |
No |
No |
|
|
SQL-schema statements in external routines |
No |
No |
|
|
SQL-dynamic statements in external routines |
No |
No |
|
|
Cyclically dependent routines |
No |
No |
|