SAND CDBMS SQL Reference Guide
ALTER SESSION

 

The ALTER SESSION ALTER DIVISION statement instructs SAND CDBMS to intercept divide-by-zero errors in the current user session and return a null value instead. The ALTER SESSION EXECUTE RENUMBER statement triggers internal renumbering of records in all tables, which can improve query performance for the larger tables. The ALTER SESSION SET DOMINDEX statement is used to disable or enable domain indexing, which is relevant for bulk inserts performed with the SAND CDBMS Data Loader utility (ndlm). The ALTER SESSION SET JOINLIMIT statement overrides the nucleus.ini JoinLimit value (or the default, if JoinLimit is not set) for the duration of the session.


Required Privileges

No special privileges are required to execute the ALTER SESSION ALTER DIVISION statement.

DBA privileges are required to execute the EXECUTE RENUMBER, SET DOMINDEX, or SET JOINLIMIT commands.


Syntax


x
Set DOMINDEX to 0 to turn off domain indexing. Set DOMINDEX to a positive integer value (for example, 1) to enable and immediately begin domain indexing.

y
The number of rows to set as the limit in the intermediate join results table. The value must be greater than or equal to 16384, and less than or equal to 500000000000 (five hundred billion).


Description

ALTER SESSION ALTER DIVISION

The ALTER SESSION ALTER DIVISION statement, which enables the interception of each divide-by-zero error to return a null value, affects the current user session only. To turn off this feature and return to normal error handling, the user must disconnect from their session and then reconnect without specifying ALTER SESSION ALTER DIVISION.


ALTER SESSION EXECUTE RENUMBER

The ALTER SESSION EXECUTE RENUMBER statement initiates internal renumbering of the records in all tables. This renumbering action removes gaps in the tuple bitmap for each table, accumulated over time by the periodic deletion and insertion of records in a table. The end result of the renumbering is a physically smaller and more efficient bitmap, which can significantly improve query performance for larger tables.

Note that renumbering cannot be performed when there are active transactions. This means that the EXECUTE RENUMBER command must be issued at the start of a new transaction, either immediately after a COMMIT or ROLLBACK command, or just after connecting to the database.

Tuple renumbering can be an expensive operation, in terms of both time and system resources. Database administrators should carefully consider the available batch window before starting any renumbering operations. If the batch window is limited, the ALTER TABLE...EXECUTE RENUMBER command can be used to renumber the records for individual tables.


ALTER SESSION SET DOMINDEX

The ALTER SESSION SET DOMINDEX statement, used to defer the domain indexing phase of data loads, can be useful if multiple loads will be performed with ndlm. By default, domain indexing occurs after each individual load operation. Using ALTER SESSION SET DOMINDEX, domain indexing can be postponed until all load operations are complete, thereby eliminating redundant domain indexing and saving significant time on the overall load work.

It is important that domain indexing be performed after the load operations, and before attempting to access the loaded data. Working with an unindexed domain can generate a number of different errors. In modes other than Real, a SHUTDOWN IMMEDIATE KEEP CHANGE command will have to be executed after enabling domain indexing to make the indexing permanent.

Also note that turning on domain indexing will cause a COMMIT to be issued internally, so any previous DDL statements in the transaction will be made permanent.


ALTER SESSION SET JOINLIMIT

The ALTER SESSION SET JOINLIMIT statement sets the maximum number of rows in intermediate join results tables for the rest of the session. Any query join that exceeds the limit will generate an error condition. The specified value (y) overrides the value for the JoinLimit parameter in the nucleus.ini file, or the default value of 500 billion, if JoinLimit is not set.


Examples

The following command executes the ALTER DIVISION option:

ALTER SESSION ALTER DIVISION;

If the above command had not already been executed in the current session, SAND CDBMS will now return a null value instead of an error in the case of division by zero. If the command had been executed previously in the session, executing it again will have no effect (that is, SAND CDBMS will continue to intercept divide-by-zero errors).

The following command initiates tuple renumbering for all tables:

ALTER SESSION EXECUTE RENUMBER;

Note that the above command must be issued at the start of a new transaction, otherwise an error will be returned.

The SET DOMINDEX command below turns on domain indexing, which begins immediately:

ALTER SESSION SET DOMINDEX = 1;
SHUTDOWN IMMEDIATE KEEP CHANGE;

Only a user with DBA privileges can execute the commands above. The SHUTDOWN IMMEDIATE KEEP CHANGE command shuts down the database running in Virtual or Persistent Mode, saving the changes to an Update File. When the database is not in Real Mode, the KEEP CHANGE clause is required to preserve the domain indexing.

The following command sets the maximum size of intermediate join tables to 100 billion rows for the current session:

ALTER SESSION SET JOINLIMIT = 100000000000;