Specifying Snapshot Versioning

Use the SNAPSHOT_VERSIONING option to set the snapshot versioning type to either Row-level or Table-Level. You can set the option at the database (PUBLIC) level, connection level (TEMPORARY) or user level. To use the in-memory RLV store, enable row-level snapshot versioning for your transactions. For simultaneous updates to different rows of the same table, each transaction or connection must also enable row-level snapshot versioning.

Prerequisites

If setting to Row-level, the RLV store dbspace exists with at least one dbfile.
If setting to Row-level, the table is RLV-enabled.
Requires the SET ANY PUBLIC OPTION system privilege to set this option for PUBLIC or for other user or role.

Task

Once the snapshot versioning property has been set for a transaction, it remains the same until the transaction commits.

Determine the scope of the SET OPTION command to set the option as a database-wide option, connection-level option, or user-level option:
- SET OPTION public.SNAPSHOT_VERSIONING...
- SET TEMPORARY OPTION SNAPSHOT_VERSIONING...
- SET OPTION username.SNAPSHOT_VERSIONING...

Specify the snapshot versioning type.

Level	Option
Row-level	Row-level snapshot versioning. Required for in-memory RLV storage. Row-level snapshot versioning allows multiple writers to make concurrent DML changes to a table, but never to the same rows at the same time.
Table-level	Classic (backward-compatible) SAP Sybase IQ versioning behavior. Takes snapshots at the table-level. Multiple writers cannot make concurrent DML changes to a table.

SET TEMPORARY OPTION Snapshot_Versioning = 'Row-level';

CREATE TABLE rv_locks(c1 int, c2 int, c3 int);

INSERT INTO rv_locks VALUES (1,1,1);
INSERT INTO rv_locks VALUES (2,2,2);
INSERT INTO rv_locks VALUES (3,3,3);
INSERT INTO rv_locks VALUES (4,4,4);
COMMIT;