SQL/MX 3.2.1 Management Manual (H06.26+, J06.15+)

4 Reviewing and Setting System Defaults
NonStop SQL/MX uses system-defined default settings for attributes associated with compiling and
executing queries. The system-defined default settings, which are hard-coded settings, are optimal
in most cases. Under some circumstances, however, you might want to override a specific
system-defined default setting. System-defined default settings can be overridden on a system level
(affecting all applications) by inserting a row into the SYSTEM_DEFAULTS table, or in a specific,
limited context by executing any of these statements:
• CONTROL QUERY DEFAULT
• CONTROL TABLE
• SET TABLE
This section identifies the system defaults that are new for SQL/MX Release 3.2. It also identifies
those defaults that you must change after you install SQL/MX Release 3.1 and before you run your
applications. Sometimes, an alternate setting might be required, but the default setting is retained
to ensure backward-compatibility with existing applications.
This section addresses these topics:
• “Setting and Updating System Defaults” (page 68)
• “Consequences of Inserting Rows Into the SYSTEM_DEFAULTS Table” (page 69)
For comprehensive information about system defaults and the SYSTEM_DEFAULTS metadata table,
see the SQL/MX Reference Manual.
Setting and Updating System Defaults
The SYSTEM_DEFAULTS table is defined in schema SYSTEM_DEFAULTS_SCHEMA of catalog
NONSTOP_SQLMX_nodename. The SYSTEM_DEFAULTS table contains only one row, inserted
by the InstallSqlmx script for the NATIONAL_CHARSET default, until you insert additional
rows that contain default settings. To update the SYSTEM_DEFAULTS table, you must be the super
ID or a user to whom the super ID has granted UPDATE privileges. All other users have SELECT
privileges on this table.
The statements in this example insert a default into the SYSTEM_DEFAULTS table on \MYSYS to
set the default setting for the transaction isolation level:
SET SCHEMA nonstop_sqlmx_mysys.system_defaults_schema;
INSERT INTO SYSTEM_DEFAULTS
(ATTRIBUTE, ATTR_VALUE)
VALUES ('ISOLATION_LEVEL', 'SERIALIZABLE');
The statements in this example update the current default setting for the transaction isolation level
on \MYSYS:
SET SCHEMA nonstop_sqlmx_mysys.system_defaults_schema;
UPDATE SYSTEM_DEFAULTS
SET ATTR_VALUE = 'READ COMMITTED'
WHERE ATTRIBUTE = 'ISOLATION_LEVEL';
The system-defined default settings can be viewed in MXCI using the showcontrol all command
or by using following query:
select * from table(compilercontrols()) where type = 'CQD' and state =
'DEF_TAB';
68 Reviewing and Setting System Defaults