Home | Previous Page | Next Page   Basics of Database Design and Implementation > Planning a Database > Using ANSI-Compliant Databases >

Differences Between ANSI-Compliant and Non-ANSI-Compliant Databases

Databases that you designate as ANSI compliant and databases that are not ANSI compliant behave differently in the following areas:

Transactions

A transaction is a collection of SQL statements that are treated as a single unit of work. All the SQL statements that you issue in an ANSI-compliant database are automatically contained in transactions. With a database that is not ANSI compliant, transaction processing is an option.

In a database that is not ANSI compliant, a transaction is enclosed by a BEGIN WORK statement and a COMMIT WORK or a ROLLBACK WORK statement. However, in an ANSI-compliant database, the BEGIN WORK statement is unnecessary, because all statements are automatically contained in a transaction. You need to indicate only the end of a transaction with a COMMIT WORK or ROLLBACK WORK statement.

For more information on transactions, see Implementing a Relational Data Model and the IBM Informix: Guide to SQL Tutorial.

Transaction Logging

ANSI-compliant databases run with unbuffered transaction logging. In an ANSI-compliant database, you cannot change the logging mode to buffered logging, and you cannot turn logging off.

Dynamic Server

Databases that are not ANSI compliant can run with either buffered logging or unbuffered logging. Unbuffered logging provides more comprehensive data recovery, but buffered logging provides better performance.

End of Dynamic Server
Extended Parallel Server

Databases that are not ANSI compliant run with unbuffered logging only. Unbuffered logging provides more comprehensive data recovery.

End of Extended Parallel Server

For more information, see the description of the CREATE DATABASE statement in the IBM Informix: Guide to SQL Syntax.

Owner Naming

In an ANSI-compliant database, owner naming is enforced. When you supply an object name in an SQL statement, ANSI standards require that the name include the prefix owner, unless you are the owner of the object. The combination of owner and name must be unique in the database. If you are the owner of the object, the database server supplies your user name as the default.

Databases that are not ANSI compliant do not enforce owner naming.

For more information, see the Owner Name segment in the IBM Informix: Guide to SQL Syntax.

Privileges on Objects

ANSI-compliant databases and non-ANSI-compliant databases differ as to which users are granted table-level privileges by default when a table in a database is created. ANSI standards specify that the database server grants only the table owner (as well as the DBA if they are not the same user) any table-level privileges. In a database that is not ANSI compliant, however, privileges are granted to public. In addition, the database server provides two table-level privileges, Alter and Index, that are not included in the ANSI standards.

To run a user-defined routine, you must have the Execute privilege for that routine. When you create an owner-privileged procedure for an ANSI-compliant database, only the owner of the user-defined routine has the Execute privilege. When you create an owner-privileged routine in a database that is not ANSI compliant, the database server grants the Execute privilege to public by default.

For more information about privileges, see Granting and Limiting Access to Your Database and the description of the GRANT statement in the IBM Informix: Guide to SQL Syntax.

Default Isolation Level

The database isolation level specifies the degree to which your program is isolated from the concurrent actions of other programs. The default isolation level for all ANSI-compliant databases is Repeatable Read. The default isolation level for non-ANSI-compliant databases that do use logging is Committed Read. The default isolation level for non-ANSI-compliant databases that do not use logging is Uncommitted Read.

For information on isolation levels, see the IBM Informix: Guide to SQL Tutorial and the description of the SET TRANSACTION and SET ISOLATION statements in the IBM Informix: Guide to SQL Syntax.

Character Data Types

When a database is not ANSI compliant, you do not get an error if any character field (CHAR, CHARACTER, NCHAR, NVARCHAR, VARCHAR, CHARACTER VARYING) receives a string that is longer than the specified length of the field.The database server truncates the extra characters without resulting in an error message. Thus the semantic integrity of data for a CHAR(n) column or variable is not enforced when the value inserted or updated exceeds n bytes.

In an ANSI-compliant database, you get an error if any character field (CHAR, CHARACTER, NCHAR, NVARCHAR, VARCHAR, CHARACTER VARYING) receives a string that is longer than the specified width of the field.

Decimal Data Type

In an ANSI-compliant database, no scale is used for the DECIMAL data type. You can think of this as scale = 0.

Escape Characters

In an ANSI-compliant database, escape characters can only escape the following characters: percent (%) and underscore (_). You can also use an escape character to escape itself. For more information about escape characters, see the Condition segment in the IBM Informix: Guide to SQL Syntax.

Cursor Behavior

If a database is not ANSI compliant, you need to use the FOR UPDATE keywords when you declare an update cursor for a SELECT statement. The SELECT statement must also meet the following conditions:

In ANSI-compliant databases, you do not have to explicitly use the FOR UPDATE keywords when you declare a cursor. In ANSI-compliant databases, all cursors that meet the restrictions that the preceding list describes are potentially update cursors. You can specify that a cursor is read-only with the FOR READ ONLY keywords on the DECLARE statement.

For more information, see the description of the DECLARE statement in the IBM Informix: Guide to SQL Syntax.

The SQLCODE Field of the SQL Communications Area

If no rows satisfy the search criteria of a DELETE, an INSERT INTO tablename SELECT, a SELECT...INTO TEMP, or an UPDATE statement, the database server sets SQLCODE to 100 if the database is ANSI compliant and 0 if the database is not ANSI compliant.

For more information, see the descriptions of SQLCODE in the IBM Informix: Guide to SQL Tutorial.

Synonym Behavior

Synonyms are always private in an ANSI-compliant database. If you attempt to create a public synonym or use the PRIVATE keyword to designate a private synonym in an ANSI-compliant database, you receive an error.

For more information, see the description of the CREATE SYNONYM statement in the IBM Informix: Guide to SQL Syntax.

Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]