Manual RLV Merging failing

[former_member241792]

Hello All,

Whenever Manual RLV Merging is fired , receiving following error.

• 0000000052 [20919]:
  Delete of 5872 rows completed for table: Z_DATA, 1 seconds.
  0000000052 Mrg U
  0000000052 Mrg I
  0000000052 Exception Thrown from slib/s_ohcins.cxx:2066, Err# 0, tid 720 origtid 720
  0000000052 O/S Err#: 0, ErrID: 6145 (hgex_dataexception); SQLCode:-1002003, SQLState: 'QGA03', Severity: 140000000052 [20505]: Tried to insert a duplicate value into a unique index DBA.Z_DATA.ASIQ_IDX_T808_I7_HG on row 15277747.-- (slib/s_ohcins.cxx 2066)
  0000000052 Exception Thrown from slib/s_ohcins.cxx:2066, Err# 0, tid 9 origtid 720 0000000052 O/S Err#: 0, ErrID: 6145 (hgex_dataexception); SQLCode:-1002003, SQLState: 'QGA03', Severity: 14 0000000052 [20505]: Tried to insert a duplicate value into a unique index DBA.Z_DATA.ASIQ_IDX_T808_I7_HG on row 15277747.
  -- (slib/s_ohcins.cxx 2066) 0000000052 Exception Thrown from slib/s_ohcins.cxx:2066, Err# 0, tid 719 origtid 719

When try rebuilding of these Indexes ,

0000000051 Exception Thrown from dblib/db_catalog.cxx:1605, Err# 329,tid 13 origtid 13I. 10/19 14:56:25. 0000000051  O/S Err#: 0, ErrID: 4098 (db_sqlexception); SQLCode: -1001102, SQLState: 'QRA02', Severity: 14I. 10/19 14:56:25. 0000000051 [22038]: RLV store merge already in progress for table Z_DATA.-- (dblib/db_catalog.cxx 1605)

[Gisung · Accepted solution]

Hi,

I can see the CR note in coverletter of IQ16 SP11.03.

SP11.01 796929 With the option 'blocking' set to 'ON' it was possible that calling sp_iqmergerlvstore() to merge an RLV table could result in a error saying that there was already a merge in progress for the table. This has been corrected.

If your IQ version is lower than SP11.03, Please test with latest version of IQ.

Best Regards,
Gi-Sung Jang

[former_member246062]

Got the same error again ,

Found exact duplicates using select .. group by .. having clause and moved to temp table and loaded the distinct data back to main table.

Now , Merging is successful with no Duplicate errors and also no inconsistent metadata from 'sp_iqcheckdb Check database'

Regards,

Yuvaraj Subramani

[former_member246062]

As you mentioned , it is a tideous process considering the volume of our tables.

Thanks alot for your time. Expecting a patch for this soon. 🙂

Regards,

Yuvaraj Subramani

[Gisung]

Hi,

Here is a KBA that explains how to check the duplicate error during merge phase.

2127757 - Forced merge failing on RLV enabled table - SAP IQ
https://launchpad.support.sap.com/#/notes/2127757

Theoretically this is required to remove conflicting row .
- Identify the blocking row in base table.
- Save the row contents for further research like key , time of insertion etc.
- Delete the row. But it is difficult to identify.

So end-user should work around this problem with following approach.

1. Populate another base table BASE_Table_New from BASE_Table
2. Truncate BASE_Table so there are no conflicting rows to offend sp_iqmergerlvstore
3. Run sp_iqmergerlvstore which should run fine now since target is essentially empty
4. Now compare BASE_Table & BASE_Table_New to figure out which row/s caused problem
5. Remove common rows found
6. Merge BASE_Table_New back into BASE_Table
7. Drop BASE_Table_New which was used for staging purpose.

Best Regards,
Gi-Sung Jang

[former_member246062]

Sorry.

Admin Basis / Yuvaraj Subramani are the same. I'm the owner of both of these id's .

[former_member246062]

Hello Gi-Sung,

Thank you for the response.

We r Using SAP IQ/16.0.110.2195/10299/P/sp11.01/ITANIUM/HP-UXi 11.31/64bit/ and the latest one available for HP-UX.

Tried with Non-blocking mode now, getting the same error "Tried to insert a duplicate value into a unique index DBA.zzzzz.ASIQ_IDX_T808_I7_HG on row 15277747."

When trying rebuilding of this Index getting, RLV store merge already in progress for table .

Last week the same happened with 2 other tables,I did rebuilding of this inconsistent Index post disabling the RLV store for those 2 tables and then enabled RLV . By this I have lost data in RLV store for those tables.

Is there anyother workaround availble for this scenario to get rid of this? Now my Jobs are started taking more time than usual.
Since I can't afford to lose data again for this table (since it is high priority buisness table).

Note :
From DBCC verify Index for "DBA.zzzzz.ASIQ_IDX_T808_I7_HG" , there are no errors detected.
From DBCC check Database Errors detected ** Tiered HG subindex with inconsistent metadata 3 ******