SAP IQ 16 Hang in the last phase of insert from AS...

Former Member · ‎04-01-2014

Problem description:

When I try to load 73K rows from ASE 15.7 to IQ 16.0 SP3 the IQ server ‘hang’ at the last phase of the insert (merge RLV store to main IQ dbspace).

Hang: The server stops responding; stop writing to iqmsg; and, I’m unable to stop the server using stop_iq; the only way to stop the server is to kill the process (on Unix).

Any suggestion on how to solve this issue will be appreciated.

My configuration

Adaptive Server Enterprise/15.7/EBF 21339 SMP SP101 /P/X64/Windows Server/ase157sp101/3439/64-bit/OPT/Thu Jun 06 12:11:05 2013

Sybase IQ/16.0.0.653/131122/P/sp03/Sun_Sparc/OS 5.10/64bit/2013-11-22 01:16:26 (8 cores)

How to reproduce the problem:

In ASE 15.7

Create and populate the following table:

CREATE TABLE dbo.MyTable (

Program varchar(3) NULL,

EncNum varchar(12) NULL,

MRN varchar(8) NULL,

IntervalStartDtm varchar(19) NULL,

IntervalEndDtm varchar(19) NULL,

IntervalType varchar(50) NULL,

IntervalDuration numeric(19,4) NULL,

PhysFirstName varchar(50) NULL,

PhysLastName varchar(50) NULL,

PhysNum varchar(6) NULL,

Location varchar(3) NULL,

DiseaseSite varchar(20) NULL,

Priority int NULL,

DelayReason int NULL,

ExcludeFlag varchar(1) NULL

)

LOCK ALLPAGES

GO

I’d around 73,000 rows, you can populate the table with random data.

EndNum are of format ‘999999999999’

MRN number are of format ‘99999999’

On SAP IQ 16.0 SP3

Create the new RVL STORE

create dbspace IQ_RLVStore

using file IQ_RLVFile1 '/dev/vx/rdsk/datadg/pdevtemp1' size 15360

IQ RLV STORE

Start Up Parameters

-n devDwIqSrvr

-x tcpip{port=4101}

-c 512m

-gc 20

-gd all

-gl all

-gm 100

-gn 150

-gp 4096

-ti 4400

-iqlm 4096

-iqmc 4096

-iqtc 4096

-iqgovern 100

-iqrlvmem 2048

-iqmsgsz 2047

-iqmsgnum 2

-xs http{port=8082}

Create the table

CREATE TABLE DBA. MyTable (

Program varchar(3) NULL,

EncNum varchar(12) NULL,

MRN varchar(8) NULL,

IntervalStartDtm varchar(19) NULL,

IntervalEndDtm varchar(19) NULL,

IntervalType varchar(50) NULL,

IntervalDuration numeric(19,4) NULL,

PhysFirstName varchar(50) NULL,

PhysLastName varchar(50) NULL,

PhysNum varchar(6) NULL,

Location varchar(3) NULL,

DiseaseSite varchar(20) NULL,

Priority integer NULL,

DelayReason integer NULL,

ExcludeFlag varchar(1) NULL IQ UNIQUE (255)

)

GO

set option public.SNAPSHOT_VERSIONING = 'Row-level'

set option public.RV_RESERVED_DBSPACE_MB = 500

alter table DBA. MyTable

enable RLV STORE

On ASE 15.7

Create a new session (disconnect and reconnect)

Create the new server

sp_addserver 'devDwIqSrvr', ASIQ, 'ohdwdbdev01:4101'

Add and external login

sp_addexternlogin devDwIqSrvr, sa, DBA, myPassword

Create proxy a table

`

create proxy_table MyTable _proxy

at 'devDwIqSrvr.devDwIqDb.DBA. MyTable '

Execute

insert into dbo. MyTable _proxy

select * from dbo. MyTable

In the 2 servers where I tested, the IQ server ‘hang’ after around 1 hrs.

Former Member · ‎05-14-2014

Just an update. To solve our problem we change the way how we load the data. for details you can refer to

c_baker · ‎04-01-2014

You are probably running out of RLV memory. Your approximate row length x the number of rows is more than the -iqrlvmem configuration.

When loading data using RLV, you must commit occasionally in order for the merge to occur to TLV pages. As you are loading in a single transaction, no commit is occuring, so the merge cannot happen.

Since you are running this from the ASE side, you probably need to increase the -iqrlvmem configuration and/or reducing the batch size (using a 'where' clause, etc) to allow the merge to occur and free up RLV memory.

Chris