Skip to Content
SAP IQ

Steps to configure RLV in an IQ 16 server

Purpose

The purpose of this document is to provide you with the steps to configure RLV on IQ 16 simplex server along with a simple example to see how the RLV feature works


  

Step 1: Configure RLV Memory

  • Server startup:

  -iqrlvmem <max MB>

  •   Runtime:

  sa_server_option ‘rlv_memory_mb’,<max MB>

Step 2: Create RLV Dbspace

create dbspace rlvdbsp using file rlvfile1 ‘rlvfile1.iq’ size 1000 IQ RLV STORE

Step 3: Create/enable RLV table

create table <table-name> (<column definition>) {ENABLE|DISABLE} RLV STORE

e.g

create table rlvtab(col1 int, col2 char(10) ENABLE RLV STORE

To enable/disable RLV storage for a table

alter table <table-name> {ENABLE|DISABLE| RLV STORE}

e.g

alter table rlvtab ENABLE RLV STORE

alter table rlvtab DISABLE RLV STORE

Note: option BASE_TABLES_IN_RLV_STORE registers newly created tables in RLV store after this option is set to ON.

Step 4: Control Versioning

Snapshot_Versioning: Controlling Row-level or Table-level versioning on a connection by connection basis

Syntax:

Set [Temporary] Option Snapshot_Versioning =‘Table-level’|’Row-level’

- default is ‘Table-Level’

- DBA can restrict Snapshot_Versioning values

Set Option Allow_Snapshot_Versioning = ‘Any’ | ‘Table-Level’ | ‘Row-level’

 

Example of setting up an RLV table and seeing how it works:

  1. C:\dbs\16rlv>c:\programdata\sybaseiq\demo\mkiqdemo.bat

This script creates the iqdemo database in the current directory: C:\dbs\16rlv

Continue <Y/N>? y

Setting up temporary files ...

Starting Server ...

Connecting to server via TCPIP ...

Using: uid=DBA;pwd=sql;eng=bld_demo_9577;dbn=utility_db;links=tcpip(host=vm-test;port=9577)

Creation completed.

Shutting down server ...

SQL Anywhere Stop

Server Utility Version 16.0.0.481

Volume in drive C has no label.

Volume Serial Number is B085-C0E8

Directory of C:\dbs\16rlv

09/24/2013  01:37 PM               662 iqdemo.cfg

09/24/2013  01:37 PM         3,874,816 iqdemo.db

09/24/2013  01:37 PM      104,857,600 iqdemo.iq

09/24/2013  01:37 PM            40,674 iqdemo.iqmsg

09/24/2013  01:37 PM        26,214,400 iqdemo.iqtmp

09/24/2013  01:36 PM             1,157 iqdemo.lmp

09/24/2013  01:37 PM           327,680 iqdemo.log

09/24/2013  01:37 PM      104,857,600 iqdemo_main.iq

8 File(s)    240,174,589 bytes

Demo database created successfully

2. Edit cfg file and add -iqrlvmem  2048

3. Start server

4. You can create rlv dbspace and table using SCC (SAP Control Center), but here is how to do it using dbisql. Run dbisql and execute

CREATE DBSPACE "rlvdbspace" USING  FILE "rlvdbfile1" 'c:\dbs\16rlv\rlvdbfile1.iq' SIZE 1024 MB RESERVE 1 MB IQ RLV STORE;

5. Set some options that impact RLV Merge

SET OPTION PUBLIC.RV_AUTO_MERGE_EVAL_INTERVAL='1';

SET OPTION PUBLIC.RV_MERGE_TABLE_NUMROWS=10000;

SET OPTION PUBLIC.BASE_TABLES_IN_RLV_STORE='ON'

SET OPTION PUBLIC.Snapshot_Versioning = 'Row-level';

Note: RV_AUTO_MERGE_EVAL_INTERVAL & RV_MERGE_TABLE_NUMROWS are set at a low value to be able to see RLV feature working in a small test environment.

6. Create example table and stored procedure to load the table

create table rlvtest

(

col1                        varchar(125) not null,

col2                        int not null,

col3                        varchar(1024),

create_time       datetime default getdate(),

create_by           varchar(20) default CURRENT USER,

mod_time           datetime default getdate(),

modified_by      varchar(20) default CURRENT USER,

primary key(col2)

)

ENABLE RLV STORE;

create procedure
p_load_rlvtest()

as

begin

  declare @li_line_num    int

  select @li_line_num = 1

  while (@li_line_num < 60000)

  begin
    insert into rlvtest (col1, col2, col3) values('Test data ' + convert(char(10), @li_line_num), @li_line_num,
    'The information contained in this email is confidential and intended only for the addressee.

    If you are not the intended recipient (or have received this e-mail in error), please notify the sender immediately and destroy this e-mail. Any
    unauthorized copying, disclosure or distribution of the material in this e-mail is strictly forbidden' )
    select @li_line_num= @li_line_num + 1
    if ((@li_line_num % 10000) = 0)

    begin
      commit
    end

  end

  select @li_line_num

end;

7. Run dbisql session and execute the following

call p_load_rlvtest()

8. Command to check the rlv merge history

select * from sysiqrlvmergehistory

9. What to look for in iqmsg

‘Current active row count’ will display how many rows have been inserted.

‘Mrg B’ – indicates the begin of the merge process

‘Mrg E’ – indicates the end of the merge process

You will see such entries in the iqmsg, but since I set merge_rows to 10000, the merge happens and row count is back to 0

I. 09/30 11:15:48. 0000000021 0000005656 Current active row count for table rlvtest is 10000

I. 09/30 11:15:48. 0000000021 0000005656 Current active row count for table rlvtest is 0

I. 09/30 11:15:48. 0000000021 Cmt 8120

I. 09/30 11:15:48. 0000000021 PostCmt 0

I. 09/30 11:15:48. 0000000021 Txn 8121 0 8121

I. 09/30 11:15:48. 0000000021 Cmt 8122

I. 09/30 11:15:48. 0000000021 PostCmt 0

I. 09/30 11:15:49. 0000000194 Txn 8123 0 8123

I. 09/30 11:15:49. 0000000194 Cmt 8124

I. 09/30 11:15:49. 0000000194 PostCmt 0

I. 09/30 11:15:49. 0000000195 Txn 8125 0 8125

I. 09/30 11:15:49. 0000000195 Cmt 8126

I. 09/30 11:15:49. 0000000195 PostCmt 0

I. 09/30 11:15:49. 0000000195 Txn 8127 0 8127

I. 09/30 11:15:49. 0000000195 Mrg B 788 NO

I. 09/30 11:15:49. 0000000195 Mrg D

I. 09/30 11:15:49. 0000000195 [20917]: Delete of 0 rows started for table:

I. 09/30 11:15:49. 0000000195 [20919]: Delete of 0 rows completed for table: rlvtest, 0 seconds.

I. 09/30 11:15:49. 0000000195 Mrg U

I. 09/30 11:15:49. 0000000195 Mrg I

I. 09/30 11:15:49. 0000000195 [20896]: Insert for 'rlvtest' completed in 0 seconds.  20000 rows inserted.

I. 09/30 11:15:50. 0000000195 Mrg E

I. 09/30 11:15:51. 0000000195 Cmt 8128

I. 09/30 11:15:51. 0000000195 PostCmt 0

Related Content
Recover RLV from failed recovery - http://scn.sap.com/docs/DOC-44821

RLV Memory Management and Monitoring - http://scn.sap.com/docs/DOC-50252

Resolving RLV Store Out of Memory Error - http://scn.sap.com/docs/DOC-45064