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 execution memory requirements when running read-only queries, as for reports:
SET SESSION READ ONLY
• Display the text of the query as it is executed:
SET PRINTQRY
Vector 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 Vector environment variables and startup files that let you 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 line-based terminal monitor, must be terminated with “\g”.
• SET statements defined directly inside Vector environment variables (not written in a file) never contain the string “\g”.
• SET statements defined directly inside Vector environment variables (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.
• Vector environment variables set with the following command are global for the installation and affect the target of the environment variable for all users in an installation:
ingsetenv
• Vector environment variables 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 Vector environment variables are set interactively, or:
Windows: Through the System settings in the Control Panel.
Linux: From a user's .profile, or .bashrc file.
Dayfile
This name affects all users of the line-based SQL Terminal Monitor.
Dayfile contains text messages only (not SET statements), which appear as an informational header whenever the Terminal Monitor is started. It is located in $II_SYSTEM/ingres/files directory. The message header can be suppressed with the -d command line flag.
DBNAME_SQL_INIT
This variable applies to the line-based SQL Terminal Monitor only. DBNAME_SQL_INIT points to SET statements within a file that are executed whenever this Vector environment variable 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.
Linux: Global setting is with ingsetenv, or locally in the user’s .login, .profile, or .cshrc file.
Setting the DBNAME_SQL_INIT environment variable is equivalent to executing \i filename in the SQL Terminal Monitor each time you connect to DBNAME.
To define the Vector environment variable DBNAME_SQL_INIT
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
Linux:
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.
The initialization file contains lines in the following format:
SET SESSION READ ONLY \g
Multiple SET statements must be separated with a semicolon (;). The entire file must end with “\g”.
II_SQL_INIT
This name applies to the line-based SQL Terminal Monitor only. II_SQL_INIT points to SET statements within a file that are executed whenever this Vector environment variable 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.
Linux: Global setting is with ingsetenv, or locally in the user’s .login, .profile or .cshrc file
Setting this environment variable is equivalent to executing \i filename in the SQL Terminal Monitor each time you connect to a database.
To define II_SQL_INIT
Use the following command syntax at the operating system prompt:
Windows:
SET II_SQL_INIT
=path_to_file 
Linux:
II_SQL_INIT=path_to_file export II_SQL_INIT
For example:
II_SQL_INIT=/usr/george/mystartfile export II_SQL_INIT
The file contains lines in the following format:
SET LOCKMODE SESSION WHERE READLOCK = NOLOCK \g
Multiple SET statements must be separated with a semicolon (;). The entire file must end with “\g”.
ING_SET
This name applies to the line-based SQL Terminal Monitor and tools such as ABF, VIFRED, embedded SQL, QBF, and ISQL. ING_SET points to SET statements that are executed whenever this Vector environment variable takes effect.
ING_SET affects users who connect to an application, a Vector 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.
Linux: Global setting is with ingsetenv, or locally in the user’s .login, .profile, or .cshrc 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
To define ING_SET
Use the following command syntax at the operating system prompt:
Windows:
SET ING_SET = set-statement{; set statement}
Examples:
SET ING_SET=SET LOCKMODE SESSION WHERE READLOCK=NOLOCK
SET ING_SET=include C:\users\default\mystartfile
Linux:
ING_SET='set-statement {; set-statement}'
or:
ING_SET='include path_to_file' export ING_SET
Examples:
ING_SET='SET LOCKMODE SESSION WHERE READLOCK=NOLOCK' export ING_SET
ING_SET='include /usr/george/mystartfile' export ING_SET
For the include format, the file specified by path_to_file contains the SET statements. Multiple SET statements must be separated with a semicolon (;). Place each SET statement on a separate line. For example:
SET AUTOCOMMIT ON;
SET LOCKMODE SESSION WHERE READLOCK=NOLOCK;
SET RESULT_STRUCTURE VECTORWISE_ROW;
ING_SET_DBNAME
This name applies to the line-based SQL Terminal Monitor and tools such as ABF, VIFRED, embedded SQL, QBF, and ISQL. ING_SET_DBNAME points to SET statements that are executed whenever this Vector environment variable takes effect.
ING_SET_DBNAME affects users who connect to the database specified by DBNAME through an application, a Vector tool, or 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.
Linux: Global setting is with ingsetenv, or locally in the user’s .login, .profile or .cshrc 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
To define ING_SET_DBNAME
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 SESSION WHERE READLOCK=NOLOCK
SET ING_SET_MYDB=include C:\users\default\mystartfile
Linux:
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 SESSION WHERE READLOCK=NOLOCK'export ING_SET_MYDB
ING_SET_MYDB='include /usr/george/mystartfile'export ING_SET_MYDB
For the include format, the file specified by path_to_file contains the SET statements. Multiple SET statements must be separated 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 VECTORWISE_ROW;
ING_SYSTEM_SET
This name applies to the line-based SQL Terminal Monitor and tools such as ABF, VIFRED, embedded SQL, QBF, and ISQL. ING_SYSTEM_SET points to SET statements that are executed whenever this Vector environment variable takes effect.
ING_SYSTEM_SET affects all users who connect to a DBMS Server through an application, a Vector 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
To define ING_SYSTEM_SET
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
Linux:
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
For the include format, the file specified by path_to_file contains the SET statements. Multiple SET statements must be separated 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 VECTORWISE_ROW
Startsql
This name affects the line-based SQL Terminal Monitor only.
Startsql affects all users of the SQL Terminal Monitor. It is a customizable file 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