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
Core
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
LIMIT 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 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
N/A N/A
Retrospective check constraints
N/A 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
N/A N/A
Comparable data types for referential constraints
N/A N/A
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. N/A N/A
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
N/A N/A
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
N/A N/A
Explicit security for external routines
N/A N/A
Explicit security for SQL routines
N/A No
Qualified SQL parameter references
N/A No
Table functions
N/A N/A
Basic roles
No No
Extended roles
No No
Bracketed SQL comments (/*...*/ comments)
No No
Extended grouping capabilities
N/A N/A
Nested and concatenated GROUPING SETS
N/A N/A
Multiargument GROUPING function
N/A N/A
GROUP BY DISINCT
N/A 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