Tutorial: Using Row-Level Versioning on a Table

In this tutorial, create an RLV enabled dbspace and table, insert data, and then merge the RLV store into the IQ main store.

Prerequisites

SAP Sybase IQ server has a simplex database.

Task

Create an RLV dbspace dbsp1 with the db file file1.

CREATE DBSPACE dbsp1 
USING FILE file1 'file1.iqrlv' 
SIZE 1000 
IQ RLV STORE

Create an RLV enabled table in the dbspace or RLV enable an existing table.

Enable new table:

CREATE TABLE test1 
   (rowid INT NOT NULL, 
    col1 char(25) NOT NULL, 
    col2 char(25) NULL, 
    col3 varchar(25) NOT NULL, 
    col4 varchar(25) NULL) 
   ENABLE RLV STORE

Enable existing table:

ALTER TABLE test1
ENABLE RLV STORE

Verify the table is RLV enabled.

SELECT is_rlv FROM sysiqtab 
WHERE table_id = (SELECT table_id FROM systab WHERE table_name = 'test1')

Set the snapshot versioning option to "row-level".

SET TEMPORARY OPTION snapshot_versioning = 'Row-level'

Check RLV memory usage prior to inserting data into RLV enabled table.
```
SELECT TOTAL FROM sp_iqrlvmemory('test1', 'DBA')
```

Insert data into the table.

INSERT INTO test1 VALUES (1, 'char25', NULL, 'varchar25', NULL) 
INSERT INTO test1 VALUES (2, 'char25', NULL, 'varchar25', NULL) 
INSERT INTO test1 VALUES (3, 'char25', NULL, 'varchar25', NULL)

Check RLV memory usage after inserting data to RLV enabled table.
```
SELECT TOTAL FROM sp_iqrlvmemory('test1', 'DBA')
```
Manually merge the data to main Blocking merge.
```
sp_iqmergerlvstore 'BLOCKING', 'test1','DBA'
```
Check RLV memory usage after merging data to main.
```
SELECT TOTAL FROM sp_iqrlvmemory('test1', 'DBA')
```