ANSI-Compliant Databases

When you use the LOG MODE ANSI option in the CREATE DATABASE statement, the database that you create is an ANSI-compliant database that conforms to the ANSI/ISO standard for the SQL language.

The following example creates an ANSI-compliant database:
CREATE DATABASE employees WITH LOG MODE ANSI; 
ANSI-compliant databases are different from databases that are not ANSI compliant in several ways, including the following features:
  • All SQL statements are automatically contained in transactions.
  • All databases use unbuffered logging.
  • Owner naming is enforced.

    You must qualify with the owner name any table, view, synonym, index, or constraint that you do not own. Unless you enclose the owner name between quotation marks, alphabetic characters in owner names default to uppercase. (To prevent this upshifting of lowercase letters in undelimited owner names, you can set the ANSIOWNER environment variable to 1.)

    In addition, the routine signature of a UDR includes the name of the owner; in databases that are not ANSI compliant, this is true only for the sysdbopen( ) and sysdbclose( ) procedures.

  • For sessions, the default isolation level is REPEATABLE READ.
  • Default privileges on objects differ from those in databases that are not ANSI compliant. When you create a table or a synonym, other users do not receive access privileges (as members if the PUBLIC group) on the object by default.
  • All DECIMAL data types are fixed-point values. If you declare a column as DECIMAL(p), the default scale is zero, meaning that only integer values can be stored. (In a database that is not ANSI compliant, DECIMAL(p) is a floating-point data type of a scale large enough to store the exponential notation for a value.)

Other slight differences exist between databases that are ANSI compliant and those that are not. These differences are noted with the related SQL statement in this document. For a detailed discussion of the differences between ANSI compliant databases and databases that are not ANSI-compliant, see the IBM® Informix Database Design and Implementation Guide.

Creating an ANSI-compliant database does not mean that you automatically receive warnings for Informix® extensions to the ANSI/ISO standard for SQL syntax when you run the database. You must also use the -ansi flag or the DBANSIWARN environment variable to receive such warnings.

For additional information about -ansi and DBANSIWARN, see the HCL Informix Guide to SQL: Reference.


Copyright© 2019 HCL Technologies Limited