Customization of Query Runtime Environment

Most aspects of the environment in which queries execute can be customized at runtime for a particular session. This can be done by executing one or more set statements to customize the environment before query execution begins.

Here are some examples:

• Reduce lock conflicts when running read-only queries, as for reports:

  set lockmode session where readlock=nolock

• Display the text of the query as it is executed:

  set printqry

Ingres makes customization flexible by permitting most components of the query environment to be set by means of set statements at the startup of individual queries (sessions). This can be done by using various Ingres environment variables and logicals and startup files that permit users to control both the target and duration of set statement customization.

A full list of set statements can be found in your query language reference guide.

Syntax Rules for Startup Files and Environment Variables

Keep the following general syntax rules in mind while setting up startup files and environment variables:

• Set statements in startup files cannot contain more than one set statement per line. Each statement must be on a line by itself.
• Set statements that are read from a file, but executed only by the single-line terminal monitors, must be terminated with "\g".
• Set statements defined directly inside Ingres environment variables and logicals (not written in a file) never contain the string "\g".
• Set statements defined directly inside Ingres environment variables and logicals (not written in a file) can contain multiple set statements separated by a semicolon up to a total length of 64 characters.
• The terminal monitor dayfile cannot contain set statements. It contains informational startup messages only.
• Ingres environment variables and logicals set with the following commands are global for the Ingres installation and affect the target of the environment variable/logical for all users in an installation:

  Windows and UNIX:

  ingsetenv

  VMS:

  For a system level installation:

  define/system

  For a group level installation:

  define/group

• Ingres environment variables and logicals set from the local user environment apply only to the user setting them. Other users are not affected. This is the case, for example, when Ingres environment variables and logicals are set interactively, or:

  Windows: Through the System icon in the Control Panel.

  UNIX: From a user's .login, .profile, or .cshrc file.

  VMS: Defined at the process level by the individual user with the VMS command define/process.

Dayfile

This name affects all users of the SQL and QUEL single-line terminal monitors.

Dayfile contains text messages only (not set statements), which appear as an informational header whenever the terminal monitors are started up. It is located in $II_SYSTEM/ingres/files directory. The message header can be suppressed with the -d command line flag.

DBNAME_ING

This name applies to the QUEL single-line terminal monitor only. DBNAME_ING points to set statements within a file that are executed whenever this Ingres environment variable takes effect.

DBNAME_ING affects users who connect to the database specified by DBNAME through the QUEL terminal monitor. If set globally with ingsetenv, it affects all users of DBNAME. If set interactively or locally in this user's .login, .profile, or .cshrc file it affects only this user.

Setting the DBNAME_ING environment variable is equivalent to a user's executing \i filename in the QUEL terminal monitor each time they connect to DBNAME.

Example: Set DBNAME_ING

To define this Ingres environment variable, use the following command syntax at the operating system prompt:

Windows:

set DBNAME_ING=path_to_file

where:

DBNAME is the name of the database and must be in uppercase.

For example:

set MYDB_ING= D:\usr\george\mystartfile

UNIX:

C Shell:

setenv DBNAME_ING path_to_file

Bourne Shell:

DBNAME_ING=path_to_file export DBNAME_ING

where:

DBNAME is the name of the database and must be in uppercase.

For example:

setenv MYDB_ING /usr/george/mystartfile

The file contains lines in the following format:

set lockmode session where readlock = nolock \g

This file contains the set statements. If you have several set statements, separate the statements with a space. You must end the entire file with "\g".

DBNAME_SQL_ INIT

This name applies to the SQL single-line terminal monitor only. DBNAME_SQL_INIT points to set statements within a file that are executed whenever this Ingres environment variable/logical takes effect.

DBNAME_SQL_INIT affects users who connect to the database specified by DBNAME through the SQL Terminal Monitor. If set globally, it affects all users of DBNAME. If set interactively or locally, it affects only this user.

Windows: Global setting is with ingsetenv, or locally in the user's environment.

UNIX: Global setting is with ingsetenv, or locally in the user's .login, .profile, or .cshrc file.

VMS: Global setting is with define/system, define/group, or locally with define/process in the user's login.com file.

Setting the DBNAME_SQL_INIT environment variable/logical is equivalent to a user's executing \i filename in the SQL Terminal Monitor each time they connect to DBNAME.

Example: Set DBNAME_SQL_INIT

To define this Ingres environment variable, use the following command syntax at the operating system prompt:

Windows:

SET DBNAME_SQL_INIT = path_to_file

where:

DBNAME is the name of the database and must be in uppercase.

For example:

SET MYDB_SQL_INT=c:\user\mystartfile

UNIX:

C Shell:

setenv DBNAME_SQL_INIT path_to_file

For example:

setenv MYDB_SQL_INIT /usr/george/mystartfile

Bourne Shell:

DBNAME_SQL_INIT=path_to_file export DBNAME_SQL_INIT

For example:

MYDB_SQL_INIT=/usr/george/mystartfile export MYDB_SQL_INIT

where:

DBNAME is the name of the database and must be in uppercase.

VMS:

define DBNAME_SQL_INIT path_to_file

For example:

DEFINE /PROCESS MYDB_SQL_INIT DUA1:[USER.GEORGE]MYSTART.FILE

where:

DBNAME is the name of the database and must be in uppercase.

The file contains lines in the following format:

set lockmode session where readlock = nolock \g

This file contains the set statements. If you have several set statements, separate the statements with a semicolon (;). You must end the entire file with "\g".

II_SQL_INIT

This name applies to the SQL single-line Terminal Monitor only. II_SQL_INIT points to set statements within a file that are executed whenever this Ingres environment variable/logical takes effect.

II_SQL_INIT affects users who connect to the SQL terminal monitor. If set globally, it affects all users. If set interactively or locally, it affects only this user.

Windows: Global setting is with ingsetenv, or locally in the user's environment.

UNIX: Global setting is with ingsetenv, or locally in the user's .login, .profile or .cshrc file

VMS: Global setting is with define/system, define/group, or locally with define/process in the user's login.com file.

Setting this environment variable/logical is equivalent to a user's executing \i filename in the SQL terminal monitor each time they connect to a database.

Example: Set II_SQL_INIT

To define this Ingres environment variable, use the following command syntax at the operating system prompt:

Windows:

SET II_SQL_INIT=path_to_file

UNIX:

C Shell:

setenv II_SQL_INIT path_to_file

For example:

setenv II_SQL_INIT /usr/george/mystartfile

Bourne Shell:

II_SQL_INIT=path_to_file export II_SQL_INIT

For example:

II_SQL_INIT=/usr/george/mystartfile export II_SQL_INIT

VMS:

define II_SQL_INIT path_to_file

For example:

DEFINE /PROCESS II_SQL_INIT -DUA1:[USER.GEORGE]MYSTART.FILE

The file contains lines in the following format:

set lockmode session where readlock = nolock \g

This file contains the set statements. If you have several set statements, separate the statements with a semicolon (;). You must end the entire file with "\g".

ING_SET

This name applies to SQL, QUEL single-line terminal monitors as well as Ingres tools such as ABF, VIFRED, embedded SQL, QBF, IQUEL, ISQL, and so forth. ING_SET points to set statements that are executed whenever this Ingres environment variable/logical takes effect.

ING_SET affects users who connect to an application, an Ingres tool, or a terminal monitor. If set globally, it affects all users. If set interactively or locally, it affects only this user.

Windows: Global setting is with ingsetenv, or locally in the user's environment.

UNIX: Global setting is with ingsetenv, or locally in the user's .login, .profile, or .cshrc file

VMS: Global setting is with define/system, define/group, or locally with define/process in the user's login.com file.

ING_SET is set to a string surrounded by quotes. The string must be 64 characters or less, or it is invalid. The string can contain either:

• One or more set statements totaling no more than 64 characters
• The word include followed by the full path name to a file containing any number of set statements

Example: Set ING_SET

To define this Ingres environment variable, use the following command syntax at the operating system prompt:

Windows:

SET ING_SET = set-statement{; set statement}

Examples:

SET ING_SET=set lockmode show
SET ING_SET=include C:\users\default\mystartfile

UNIX:

C Shell:

setenv ING_SET 'set-statement {; set-statement}'

or

setenv ING_SET 'include path_to_file'

Examples:

setenv ING_SET 'set lockmode show'
setenv ING_SET 'include /usr/george/mystartfile'

Bourne Shell:

ING_SET='set-statement {; set-statement}'

or:

ING_SET='include path_to_file' export ING_SET

Examples:

ING_SET='set lockmode show' export ING_SET

ING_SET='include /usr/george/mystartfile' export ING_SET

VMS:

define ING_SET "set-statement {;set-statement}"

or:

define ING_SET "include path_to_file"

Examples:

DEFINE /PROCESS ING_SET "SET LOCKMODE SHOW"

DEFINE /PROCESS ING_SET – 
"INCLUDE DUA1:[USER.GEORGE]MYSTART.FILE"

For the include format, the file specified by path_to_file contains the set statements. If you have several set statements, separate the statements with a semicolon (;). Place each set statement on a separate line in the file. For example:

set autocommit on;
set lockmode session where readlock=nolock;
set result_structure cbtree

ING_SET_ DBNAME

This name applies to both SQL and QUEL single-line terminal monitors as well as Ingres tools such as ABF, VIFRED, embedded SQL, QBF, IQUEL, ISQL, and so forth. ING_SET_DBNAME points to set statements that are executed whenever this Ingres environment variable/logical takes effect.

ING_SET_DBNAME affects users who connect to the database specified by DBNAME through an application, an Ingres tool, or a terminal monitor. If set globally, it affects all users of DBNAME. If set interactively or locally, it affects only this user.

Windows: Global setting is with ingsetenv, or locally in the user's environment.

UNIX: Global setting is with ingsetenv, or locally in the user's .login, .profile or .cshrc file.

VMS: Global setting is with define/system, define/group, or locally with define/process in the user's login.com file.

ING_SET_DBNAME is set to a string surrounded by quotes. The string must be 64 characters or less, or it is invalid. The string can contain either:

• One or more set statements totaling no more than 64 characters
• The word include followed by the full path name to a file containing any number of set statements

Example: Set ING_SET_DBNAME

To define this Ingres environment variable/logical use the following command entered at the operating system prompt:

Windows:

SET ING_SET_DBNAME=set-statement {;set statement}

For example:

SET ING_SET_MYDB=set lockmode show
SET ING_SET_MYDB=include C:\users\default\mystartfile

UNIX:

C Shell:

setenv ING_SET_DBNAME 'set-statement {; set-statement}'

or:

setenv ING_SET_DBNAME 'include path_to_file'

For example:

setenv ING_SET_MYDB 'set lockmode show'
setenv ING_SET_MYDB 'include /usr/george/mystartfile'

Bourne Shell:

ING_SET_DBNAME='set-statement {; set-statement}'

or:

ING_SET_DBNAME='include path_to_file' export ING_SET_DBNAME

For example:

ING_SET_MYDB='set lockmode show'export ING_SET_MYDB

ING_SET_MYDB='include /usr/george/mystartfile'export ING_SET_MYDB

VMS:

define ING_SET_DBNAME "set-statement {; set-statement}"

or:

define ING_SET_DBNAME "include path_to_file"

For example:

DEFINE /PROCESS ING_SET_MYDB – "SET LOCKMODE SHOW"

DEFINE /PROCESS ING_SET_MYDB – "INCLUDE DUA1:[USER.GEORGE]MYSTART.FILE"

For the include format, the file specified by path_to_file contains the set statements. If you have several set statements, separate the statements with a semicolon (;). Place each set statement on a separate line in the file.

For example:

set autocommit on;
set lockmode session where readlock=nolock;
set result_structure cbtree

ING_SYSTEM_ SET

This name applies to both SQL and QUEL single-line terminal monitors as well as Ingres tools such as ABF, VIFRED, embedded SQL, QBF, IQUEL, ISQL, and so forth. ING_SYSTEM_SET points to set statements that are executed whenever this Ingres environment variable/logical takes effect.

ING_SYSTEM_SET affects all users who connect to a DBMS Server through an application, an Ingres tool, or terminal monitor. It is always effective globally and can be set only by a privileged user.

ING_SYSTEM_SET is set to a string surrounded by quotes. The string must be 64 characters or less, or it is invalid. The string can contain either:

• One or more set statements totaling no more than 64 characters
• The word include followed by the full path name to a file containing any number of set statements

Example: Set ING_SYSTEM_SET

To define this Ingres environment variable/logical use the following command entered at the operating system prompt:

Windows:

SET ING_SYSTEM_SET = set-statement{;set-statement}

For example:

SET ING_SYSTEM_SET = set autocommit on

UNIX:

C Shell:

setenv ING_SYSTEM_SET 'set-statement{; set-statement}'

For example:

setenv ING_SYSTEM_SET 'set autocommit on'
setenv ING_SYSTEM_SET 'include \
/usr/ingres/ourstartfile'

Bourne Shell:

ING_SYSTEM_SET='set-statement {; set-statement}'

or:

ING_SYSTEM_SET='include path_to_file'

export ING_SYSTEM_SET

For example:

ING_SYSTEM_SET='set autocommit on'

export ING_SYSTEM_SET

ING_SYSTEM_SET='include \ /usr/ingres/ourstartfile'

export ING_SYSTEM_SET

VMS:

define ING_SYSTEM_SET "set-statement {; set-statement}"

or:

define ING_SYSTEM_SET "include path_to_file "

For example:

DEFINE /SYSTEM ING_SYSTEM_SET –
"SET LOCKMODE SHOW"

DEFINE /SYSTEM ING_SYSTEM_SET – 
"INCLUDE DUA1:[INGRES]OURSTART.FILE"

For the include format, the file specified by path_to_file contains the set statements. If you have several set statements, separate the statements with a semicolon (;). Place each set statement on a separate line in the file.

For example:

set autocommit on;
set lockmode session where readlock=nolock;
set result_structure cbtree;

INIT_INGRES

This name applies to the QUEL single-line terminal monitor only. INIT_INGRES points to set statements within a file that are executed whenever this Ingres environment variable/logical takes effect.

INIT_INGRES affects users who connect to the QUEL terminal monitor. If set globally, it affects all users. If set interactively or locally, it affects only this user.

Windows: Global setting is with ingsetenv, or locally in the user's environment.

UNIX: Global setting is with ingsetenv, or locally in the user's .login, .profile, or .cshrc file.

VMS: Global setting is with define/system, define/group, or locally with define/process in the user's login.com file.

Setting INIT_INGRES variable/logical is equivalent to a user's executing \i filename in the QUEL terminal monitor each time they connect to a database.

Example: Set INIT_INGRES

To define this Ingres environment variable/logical use the following command entered at the operating system prompt:

Windows:

SET INIT_INGRES=path_to_file

For example:

SET INIT_INGRES= c:\user\mystartfile

UNIX:

C Shell:

setenv INIT_INGRES path_to_file

For example:

setenv INIT_INGRES /usr/george/mystartfile

Bourne Shell:

INIT_INGRES=path_to_file export INIT_INGRES

For example:

INIT_INGRES=/usr/george/mystartfile export INIT_INGRES

VMS:

define INIT_INGRES path_to_file

For example:

DEFINE /PROCESS INIT_INGRES –   DUA1:[USER.GEORGE]MYSTART.FILE

The file contains lines in the following format:

set lockmode session where readlock = nolock \g

This file contains the set statements. If you have several set statements, separate the statements with a semicolon (;). You must end the entire file with "\g".

Startup

This name affects the QUEL single-line terminal monitor only.

The Ingres environment variable/logical startup affects all users of the QUEL terminal monitor. It is a customizable file (delivered with some macros already defined). It is located at: II_SYSTEM/ingres/files/startup.

Users with permission to edit this file can add statements of the such as the following:

{DEFINE RET; RETRIEVE}

The result of using this macro startup file is that users typing "RET TABLE1.ALL" has the string "RET" substituted by the string "RETRIEVE" at query execution time. For an explanation of how macros are used and defined, see the QUEL Reference Guide.

The quantity of some operating system resources available to each user session is fixed at Ingres DBMS Server start up. These include resources such as the quantities of memory available for parsing, storing and optimizing a query. Default values are used for these resources at installation time. These do not need to be changed unless you are receiving explicit error messages that indicate that these resources are inadequate.

Startsql

This name affects the SQL single-line terminal monitor only.

Startsql affects all users of the SQL terminal monitor. It is a customizable file (delivered empty on the release tape). It is located at: II_SYSTEM/ingres/files/startsql.

Users with permission to edit this file can add statements of the following example:

set lockmode session where readlock = nolock \g