ANSI Compliance Settings

This appendix lists the settings required to operate in compliance with ANSI/ISO Entry SQL-92 and the corresponding Ingres defaults.

To determine the setting in effect, use dbmsinfo. For details about dbmsinfo, see Status Information.

Case sensitivity for delimited identifiers is specified when the database is created. For details about createdb, see the Command Reference Guide and the System Administrator Guide.

Query flattening and default cursor mode options are specified when the DBMS Server is configured and started.

Configuration-By-Forms Settings

If Configuration-By-Forms (cbf) is used to configure the installation, select the Preferences menu item and set the ISO_ENTRY_SQL-92 parameter to ON. This setting ensures ANSI/ISO-compliant behavior for the following areas:

• Case sensitivity of identifiers (all types)
• Default cursor mode
• Query flattening

Case Sensitivity for Identifiers

The ANSI Entry SQL-92 standard specifies how case is handled for identifiers (names of tables, for example). Identifiers can be quoted or unquoted (regular or delimited).

Regular Identifiers

• ANSI/ISO Entry SQL-92

  Regular identifiers are treated as upper case:

  dbsminfo('db_name_case') returns UPPER.

• Ingres Default

  Regular identifiers are treated as lower case:

  dbsminfo('db_name_case') returns LOWER.

Delimited Identifiers

• ANSI/ISO Entry SQL-92

  Delimited identifiers are case-sensitive:

  dbsminfo('db_delimited_case') returns MIXED.

• Ingres Default

  Delimited identifiers are not case-sensitive:

  dbsminfo('db_delimited_case') returns LOWER.

User Names

• ANSI/ISO Entry SQL-92

  User names are treated as upper case:

  dbsminfo('db_real_user_case') returns UPPER.

• Ingres Default

  User names are treated as lower case:

  dbsminfo('db_real_user_case') returns LOWER.

Default Cursor Mode

The ANSI Entry SQL-92 standard specifies the default mode for cursors. For details, see Data Manipulation with Cursors.

• ANSI/ISO Entry SQL-92

  Direct mode:

  dbsminfo('db_direct_update') returns Y

  dbsminfo('db_deferred_update') returns N

• Ingres Default

  Deferred mode:

  dbsminfo('db_direct_update') returns N

  dbsminfo('db_deferred_update') returns Y

Query Flattening

The ANSI Entry SQL-92 standard specifies that query flattening is not performed. By default, Ingres uses query flattening to improve the performance of queries.

• ANSI/ISO Entry SQL-92

  No Flattening:

  dbsminfo('flatten_singleton') returns N

  dbsminfo('flatten_aggregate') returns N

• Ingres Default

  Flattening used to optimize queries, including queries involving aggregate subselects or singleton subselects:

  dbsminfo('flatten_singleton') returns Y

  dbsminfo('flatten_aggregate') returns Y