Table Locked After Index Build
Good Morning,
IDS 11.50.FC5 running HDR
O/S AIX 5.3
I am looking for a way around waiting for a lock to release in a particular
situation. Let me explain:
1. create index on large table
2. attempt to create another one or even a PK on the index created above. You
get error 242/106 IE...cannot get exclusive access.
3. chasing everything down in onstat ( find the guy who owns the lock,get his
tid, & getting a stack) shows me some dr_idx_send functions along with the
regular read and write functions.
4. I assume that my index is being sent to the secondary at this point.
So now I wait for this to finish (at least an hour) before I can continue. Is
there any way short of breaking HDR to get around that wait time after I am
finished and the server is sending these pages to the secondary?
TIA,
Dan
Try setting LOG_INDEX_BUILDS to 1.
From: "DAN MUELLER" <dan.mueller@trnswrks.com>
To: ids@iiug.org
Date: 02/07/2011 06:48 AM
Subject: Table Locked After Index Build [22708]
Sent by: ids-bounces@iiug.org
Good Morning,
IDS 11.50.FC5 running HDR
O/S AIX 5.3
I am looking for a way around waiting for a lock to release in a particular
situation. Let me explain:
1. create index on large table
2. attempt to create another one or even a PK on the index created above.
You
get error 242/106 IE...cannot get exclusive access.
3. chasing everything down in onstat ( find the guy who owns the lock,get
his
tid, & getting a stack) shows me some dr_idx_send functions along with the
regular read and write functions.
4. I assume that my index is being sent to the secondary at this point.
So now I wait for this to finish (at least an hour) before I can continue.
Is
there any way short of breaking HDR to get around that wait time after I am
finished and the server is sending these pages to the secondary?
TIA,
Dan
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
IDS 11.50 supports the 'ONLINE' extension to index creation which avoids
locking the table:-
CREATE INDEX anindex ON TABLE atable ( acolumn ) ONLINE ;
On 7 February 2011 12:44, DAN MUELLER <dan.mueller@trnswrks.com> wrote:
> Good Morning,
>
> IDS 11.50.FC5 running HDR
> O/S AIX 5.3
>
> I am looking for a way around waiting for a lock to release in a particular
> situation. Let me explain:
>
> 1. create index on large table
> 2. attempt to create another one or even a PK on the index created above.
> You
> get error 242/106 IE...cannot get exclusive access.
> 3. chasing everything down in onstat ( find the guy who owns the lock,get
> his
> tid, & getting a stack) shows me some dr_idx_send functions along with the
> regular read and write functions.
> 4. I assume that my index is being sent to the secondary at this point.
>
> So now I wait for this to finish (at least an hour) before I can continue.
> Is
> there any way short of breaking HDR to get around that wait time after I am
> finished and the server is sending these pages to the secondary?
>
> TIA,
> Dan
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Nick Lello | Web Architect
o +44 (0) 8433309374 | m +44 (0) 7917 138319
Email: nick.lello at rentrak.com
RENTRAK | www.rentrak.com | NASDAQ: RENT
--0016361e8800bfc3e8049bb2f9e0
The OPs problem, as Madison pointed out, is that he does not have
LOG_INDEX_BUILDS set so his HDR secondary is building the index manually
after the primary's index build is complete. This DOES hold a lock on the
primary's table until it completes. Setting LOG_INDEX_BUILDS will remove
that lock be sending the primary's index whole to the secondaries through
the logical log records.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art@iiug.org)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.
On Mon, Feb 7, 2011 at 10:59 AM, Nick Lello <nick.lello@rentrakmail.com>wrote:
> IDS 11.50 supports the 'ONLINE' extension to index creation which avoids
> locking the table:-
>
> CREATE INDEX anindex ON TABLE atable ( acolumn ) ONLINE ;>
> On 7 February 2011 12:44, DAN MUELLER <dan.mueller@trnswrks.com> wrote:
>
> > Good Morning,
> >
> > IDS 11.50.FC5 running HDR
> > O/S AIX 5.3
> >
> > I am looking for a way around waiting for a lock to release in a
> particular
> > situation. Let me explain:
> >
> > 1. create index on large table
> > 2. attempt to create another one or even a PK on the index created above.
> > You
> > get error 242/106 IE...cannot get exclusive access.
> > 3. chasing everything down in onstat ( find the guy who owns the lock,get
> > his
> > tid, & getting a stack) shows me some dr_idx_send functions along with
> the
> > regular read and write functions.
> > 4. I assume that my index is being sent to the secondary at this point.
> >
> > So now I wait for this to finish (at least an hour) before I can
> continue.
> > Is
> > there any way short of breaking HDR to get around that wait time after I
> am
> > finished and the server is sending these pages to the secondary?
> >
> > TIA,
> > Dan
> >
> >
> >
> >
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --
>
> Nick Lello | Web Architect
> o +44 (0) 8433309374 | m +44 (0) 7917 138319
> Email: nick.lello at rentrak.com
> RENTRAK | www.rentrak.com | NASDAQ: RENT
>
> --0016361e8800bfc3e8049bb2f9e0
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--0015175d09f06010a8049bb35f21