SQL Statements with Exceptions

ALTER TABLE

ALTER TABLE cannot be used to add a column to an IMAGE/SQL table. For example, the command

   isql=> alter table sales.customer add (newcol float);

results in the following error:

   Operation not allowed on non-SQL table.  (DBERR 2454)

ALTER TABLE cannot add a UNIQUE constraint to an IMAGE/SQL table. The command

   isql=> alter table sales.customer

          add constraint unique (last_name);

results in this error:

Command not allowed on a TurboIMAGE table.  (DBERR 13502)

The ALTER TABLE DROP CONSTRAINT specification is also not supported.

ALTER TABLE cannot add a CHECK constraint to an IMAGE/SQL table. The command

   isql=> alter table sales.customer

          add constraint check (last_name < 'SMITH')

          constraint checkname;

is not allowed.

ALTER TABLE cannot add a referential constraint to an IMAGE/SQL table.

BEGIN ARCHIVE

SQL archive logging does not capture modifications made to IMAGE/SQL databases.

TURBOSTORE Online Backup requires special procedures when TurboIMAGE/XL databases (and therefore IMAGE/SQL databases) are in use during the store operation.

BEGIN WORK

IMAGE/SQL transactions are managed by using the TurboIMAGE/XL intrinsics DBXBEGIN and DBXEND. The BEGIN WORK statement does not invoke DBXBEGIN until the first modification or repeatable read is requested for an IMAGE/SQL table.

The priority specified in a BEGIN WORK statement is ignored by IMAGE/SQL. IMAGE/SQL uses priority 100 for all lock requests involving IMAGE/SQL tables.

If isolation level RR (repeatable read), CS (cursor stability), or RC (read committed) is specified and the IMAGE/SQL user has a MODE attribute of 1, 2, 3, or 4, all read operations within the transaction will be repeatable reads.

If isolation level RU (read uncommitted) is specified, all read operations within the transaction will be read-uncommitted.

CHECKPOINT

Contents of log buffers are written to the log file or files, but contain no IMAGE/SQL work.

Data buffers containing changed pages are written to DBEFiles, but contain no IMAGE/SQL data.

COMMIT ARCHIVE

SQL archive logging does not capture modifications made to IMAGE/SQL databases.

COMMIT WORK

The KEEP CURSOR option of the OPEN statement cannot be used with a cursor that references an IMAGE/SQL table. Because KEEP CURSOR does not apply to IMAGE/SQL tables, COMMIT WORK:

releases all IMAGE/SQL locks acquired during the current transaction
closes all cursors that reference IMAGE/SQL tables
does not implicitly start a new IMAGE/SQL transaction

COMMIT WORK ends an IMAGE/SQL transaction by calling DBXEND and then calls DBUNLOCK to release all locks acquired during the transaction.

CREATE INDEX

CREATE INDEX cannot be used to create an index on an IMAGE/SQL table. The command

   isql => create index sales.newindex on

    sales.customer (last_name asc);

invokes this error:

   Operation not allowed on non-sql table.  (DBERR 2454)

CREATE SCHEMA

CREATE SCHEMA cannot be used to define an IMAGE/SQL database, but can be used to define a view.

CREATE TABLE

CREATE TABLE is not supported.

DECLARE CURSOR

A cursor that references an IMAGE/SQL table (in the QueryEXPRESSION or StatementName of the FOR clause) cannot be opened with the KEEP CURSOR option of the OPEN statement.

DELETE

If SQL detects an error during a DELETE statement that references an IMAGE/SQL table, the current transaction is aborted regardless of the setting of the SET DML ATOMICITY and SET CONSTRAINTS statements.

The set of rows to be affected by the DELETE statement is determined before any rule fires, and this set remains fixed until completion of the rule. If the rule adds to the set, the additional rows will not be deleted. If the rule deletes from the set, a TurboIMAGE/XL intrinsic error will result and the current transaction will be aborted.

If an error occurs during the processing of any rule considered during execution of this statement, the current transaction will be aborted.

DELETE WHERE CURRENT

This has the same considerations as DELETE.

DROP DBEFILE

DROP DBEFILE cannot be used to drop DBEFiles containing IMAGE/SQL objects.

DROP TABLE

DROP TABLE cannot be used to drop an IMAGE/SQL table. An IMAGE/SQL table can be dropped only by detaching the database with the IMAGE/SQL DETACH statement.

DROP VIEW

Cannot be used to drop a view created by IMAGESQL. A view created by IMAGE/SQL can be dropped only by detaching the database with the IMAGE/SQL DETACH command.

INSERT

If SQL detects an error during an INSERT statement that references an IMAGE/SQL table, the current transaction is aborted regardless of the setting of the SET DML ATOMICITY and SET CONSTRAINTS statements.

If an error occurs during the processing of any rule considered during execution of this statement, the current transaction will be aborted.

IMAGE/SQL columns are defined as NOT NULL with default values. Default values are based on IMAGE/SQL item types. If columns are omitted from the column name list of an INSERT statement, the missing columns will be defined using the default values. The default values are listed in Table 2-5 “IMAGE/SQL Default Data Types”.

LOCK TABLE

A LOCK TABLE statement always provides an EXCLUSIVE lock to a TurboIMAGE/XL data set.

OPEN

A cursor referencing an IMAGE/SQL table (in the QueryExpression or StatementName of the DECLARE CURSOR statement) cannot be opened with the KEEP CURSOR option.

IMAGE/SQL columns are defined as NOT NULL. The INDICATOR option of the OPEN statement therefore cannot be used with IMAGE/SQL.

REVOKE

REVOKE cannot be used to revoke authorities granted by the IMAGE/SQL ATTACH statement.

ROLLBACK WORK

Savepoints cannot be defined in an IMAGE/SQL transaction. The TO clause of ROLLBACK WORK therefore cannot be used with an IMAGE/SQL transaction.

SAVEPOINTS

Savepoints cannot be defined in an IMAGE/SQL transaction.

SELECT

IMAGE/SQL columns are defined as NOT NULL. The INDICATOR option of the SELECT statement therefore cannot be used with IMAGE/SQL.

SET CONSTRAINTS

If a modification error occurs within an IMAGE/SQL transaction, the current transaction is aborted regardless of the setting of SET CONSTRAINTS.

SET DML ATOMICITY

If a modification error occurs within an IMAGE/SQL transaction, the current transaction is aborted regardless of the setting of SET DML AUTOMICITY.

START DBE NEW

The default DBEFile size of 150 pages used by START DBE NEW may not be sufficient to allow a database to be attached with IMAGESQL.

TRANSFER OWNERSHIP

IMAGE/SQL objects created by the IMAGESQL ATTACH statement cannot be transferred to another owner.

UPDATE

If ALLBASE/SQL detects an error during an UPDATE statement that references an IMAGE/SQL table, the current transaction is aborted regardless of the setting of the SET DML ATOMICITY and SET CONSTRAINTS statements.

The set of rows to be affected by the UPDATE statement is determined before any rule fires, and this set remains fixed until completion of the rule. If the rule adds to the set, the additional rows will not be updated. If the rule deletes from the set, a TurboIMAGE/XL intrinsic error will result and the current transaction will be aborted.

If an error occurs during the processing of any rule considered during execution of this statement, the current transaction will be aborted.

IMAGE/SQL columns are defined as NOT NULL. The NULL option of the SET clause therefore cannot be used with IMAGE/SQL columns.

If an IMAGE/SQL column specified in an UPDATE statement corresponds to a search or sort item in a TurboIMAGE/XL detail data set, the database's Critical Item Update flag must be set to ON. If an IMAGE/SQL column specified in an UPDATE statement corresponds to a search item in a TurboIMAGE/XL detail data set and the corresponding master data set is a manual master, the new column value must already exist as a chain head in the master data set.

UPDATE WHERE CURRENT

This has the same considerations as UPDATE.

SQL Statements with Exceptions

Technical documentation

» Table of Contents

» Glossary

» Index

ALTER TABLE

BEGIN ARCHIVE

BEGIN WORK

CHECKPOINT

COMMIT ARCHIVE

COMMIT WORK

CREATE INDEX

CREATE SCHEMA

CREATE TABLE

DECLARE CURSOR

DELETE

DELETE WHERE CURRENT

DROP DBEFILE

DROP TABLE

DROP VIEW

INSERT

LOCK TABLE

OPEN

REVOKE

ROLLBACK WORK

SAVEPOINTS

SELECT

SET CONSTRAINTS

SET DML ATOMICITY

START DBE NEW

TRANSFER OWNERSHIP

UPDATE

UPDATE WHERE CURRENT