Skip to main content
Skip table of contents

Default Options

setDefaultOptionValue/getDefaultOptionValue

Default-only options and default values of $NOOPT, $PREFER_DWH and $ALLOW_CARTESIAN can be changed using the following procedure:

SQL
SYSADMIN.setDefaultOptionValue(IN opt string NOT NULL, IN val string, IN encVal string)

and displayed by this procedure:

SQL
SYSADMIN.getDefaultOptionValue(IN opt string NOT NULL)

For more information, please see System Procedures.

Here are some things to keep in mind:

  • Default values can be overridden explicitly by the default options of a certain query or the SET Statement Statement for the current session;
  • Default option value changed via setDefaultOptionValue will affect the CData Virtuality Server behaviour for sessions which are newly opened after the change. Changing the value for the actual session can be done with the SET Statement Statement;

  • Default option value for ALLOW_INDEX_RECOMMENDATION and ALLOW_INDEX_CREATION changed via setDefaultOptionValue will affect the CData Virtuality Server behaviour immediately, including the current session;

  • <optionname> for both setDefaultOptionValue() and getDefaultOptionValue() should be passed without the dollar sign ($).

Encrypted default options are available since v4.5

Default-only Options

These options can only be set via the setDefaultOptionValue procedure.

ALLOW_INDEX_RECOMMENDATION 

This option controls how recommended indexes are created when a query is processed. It can have the following values:

ValueDescription
ALLAll recommended/physical indexes are automatically created; default
WHEREOnly recommended/physical indexes with type WHERE are created
JOINOnly recommended/physical indexes with type JOIN are created
NONENo indexes are created

ALLOW_INDEX_CREATION 

This option controls how physical indexes are created for the corresponding materialized tables when an optimization is enabled and materialized. It can have the following values:

ValueDescription
ALLAll recommended/physical indexes are automatically created; default
WHEREOnly recommended/physical indexes with type WHERE are created
JOINOnly recommended/physical indexes with type JOIN are created
MANUAL

Only recommended indexes manually generated using the createIndex() system procedure are created

NONENo indexes are created

A combination of values for ALLOW_INDEX_CREATION option can be provided in a CSV format. For example, WHERE, JOIN or JOIN, MANUAL are accepted values. ALL and NONE values cannot be combined to any other value (e.g. ALL, JOIN is not a valid value).

As for the MANUAL indexes, they can be created using the SYSADMIN.createIndex() or SYSADMIN.importIndex() stored procedure. Here is an example:

SQL
CALL SYSADMIN.createIndex(3, '', 'test_tables.test_b.c', 'MANUAL', NULL)
CALL SYSADMIN.importIndex(3, '', 'test_tables.test_b.c', 'MANUAL', NULL)

The main difference between the create and import procedures is that the first one creates a recommended index and a physical index in the materialized table, while the second one only creates a recommended index. 

For more information, please see System Procedures.

ALLOW_MAT_AGGR

This option enables/disables the generation and the rewriting of MAT_AGGRs. The default value is FALSE.

ALLOW_MAT_JOIN

This option enables/disables the generation and the rewriting of MAT_JOINs. The default value is FALSE.

SKIP_STRUCTURE_CHECKING

This option controls validation of materialized tables structure and can have the following values:

  • TRUE (structure check is disabled; default);
  • FALSE (structure check is enabled) 

Structure validation algorithm is used to check the correspondence of materialized table to its source table(s) and whether it is applicable for further usage. This is done by verifying the following:

  1. Changes in the structure (one or more columns have been added, removed or renamed);
  2. Datatype mismatch (type of one or more columns has been changed)

For changes in the structure, before rewriting of a source table (or a join) with the corresponding materialized table, the system checks if all the columns needed to execute the current query are present in the materialized table. If the system detects incompatible structure changes, an error message is generated providing information about the specific materialized table which should be synchronized with the source.

Example workflow

SQL
CREATE TABLE ds.test1 (a integer);
INSERT INTO ds.test1 VALUES(1);
SELECT * FROM ds.test1;
-- materialize the source table ds.test1
-- add the column b to the source table
DROP TABLE ds.test1;
CREATE TABLE ds.test1 (a integer, b integer);
INSERT INTO ds.test1 VALUES(1,1);
-- selecting all the columns from the source table will throw an exception since the column "b" is not found in the mat table
SELECT * FROM ds.test1;
-- selecting only the column "a" from the source will correctly work
SELECT a FROM ds.test1;
-- to fully synchronize the materialized table with the source, a full re-materialization of the optimization is needed

The same happens if the optimization is related to a join instead of a single table.

Data type mismatch check is related to data types. The algorithm detects if the data type in the source column is the same as the column data type in the materialized table. If the system detects a mismatch, it throws an exception providing information about the expected and actual data types, the column names in the source and in the materialized table, the materialized and the source table names.

Example workflow

SQL
CREATE TABLE ds.test1 (a integer)
INSERT INTO ds.test1 VALUES(1)
-- enable the optimization
DROP TABLE ds.test1;
CREATE TABLE ds.test1 (a double);
INSERT INTO ds.test1 VALUES(1.1);
--  selecting data from the source table will throw an data type mismatch exception
SELECT * FROM ds.test1

Please note that the current version of the structure checking algorithm detects only the first occurrence of structure mismatch. This means that if a query gets data from more than one modified table, the error message will include information only about the first of them. Information about the next issue will be shown only after the previous one has been fixed.

PLAINID_CONFIGURATION

This option enables PlainID authorization policies. It takes several parameters as input, depending on how the service is configured on PlainID side. The following parameters are accepted:

ParameterDescription
urlURL of the PlainID endpoint
appIdclientId used for accessing the PlainID service
appSecretclientSecret used for accessing the PlainID service
entityTypeentityTypeId configured in PlainID
maskExpression used for column masking. It can be a simple string or a more complex expression (see our column masking documentation)
userswithdomainCan be TRUE or FALSE, depending on how the users/entities are configured in PlainID (e.g. user1 or user1@dv); default: FALSE

Example:

SQL
CALL SYSADMIN.setDefaultOptionValue("opt" => 'PLAINID_CONFIGURATION', "val" => 'url=http://123.123.123.123/plainid-accesslist/resolution,appId=XXX,appSecret=YYY,entityType=DV_Users,mask="00000",userswithdomain=false') ;;

Note

Please note that resource names should be fully qualified in PlainID and they should exactly match the names of the resources defined in CData Virtuality.

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.