SAP Knowledge Base Article - Preview

2435058 - IQ server gets HUNG while trying to recreating views concurrently.

Symptom

After upgrading to SP11 PL05 from SP08 PL35, the IQ database gets HUNG while trying to recreating views concurrently.

[IQ message log]

I. 02/22 13:08:00. 0000005512 Txn 871347 0 871347
I. 02/22 13:08:00. 0000005512 Rbck
I. 02/22 13:08:00. 0000007339 Txn 871348 0 871348
I. 02/22 13:08:00. 0000005512 PostRbck
I. 02/22 13:08:00. 0000007339 Connect: SA connHandle: 8971 SA connID: 2 IQ connID: 0000007339 User: user001

[srvlog]

I. 02/22 13:07:59. Starting checkpoint of "IQDB" (IQDB.db) at Wed Feb 22 2017 13:07
I. 02/22 13:07:59. Finished checkpoint of "IQDB" (IQDB.db) at Wed Feb 22 2017 13:07
I. 02/22 13:08:00. recreate view user001.view001
I. 02/22 13:08:00. recreate view user001.view001

There were no more information logged from here and multiple sessions were running the same procedure simultaneously.

*** Session A
=,[,1088,user_proc001,31,execute immediate v_viewDef
*** Session B
=,[,4044,user_proc001,31,execute immediate v_viewDef
*** Session C
=,],7161,user_proc001,45

The procedure is to find invalid views using the query below and insert the procedure's information into a temporary table.

select number(*) id, a.object_id,c.name user_name, b.table_name,b.view_def
into #tbl_recreate_view
from sysobject a, systable b, sysusers c
where a.status = 2
and b.table_type ='view'
and a.object_id=b.object_id
and b.creator = c.uid;

And, the found invalid views are recreated dynamically and individually using a cursor operation.

select replace(v_viewDef, 'create view', 'create or replace view') into v_viewDef ;
.......
execute immediate v_viewDef;


Read more...

Environment

SAP IQ 16.0 SP11 PL05

Product

SAP IQ 16.0

Keywords

SAP IQ, IQ, view, recompile, create view, create or replace view, unresponsive, hung, hang, 805991, CR 805991, CR805991,806167, CR 806167, CR806167, Forbid, deadlock , KBA , BC-SYB-IQ , Sybase IQ , Problem

About this page

This is a preview of a SAP Knowledge Base Article. Click more to access the full version on SAP ONE Support launchpad (Login required).

Search for additional results

Visit SAP Support Portal's SAP Notes and KBA Search.