BBj Support for SQL-99 and SQL-2003 Features

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-2003 Mandatory

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

Feature Name

SQL-99
Core

SQL-2003 Mandatory

Note

Embedded Ada

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