Drop an index - HELP!!!!
IDS 9.20 FC2 on HP-UX 11.0 One of the tables in my live database has hit an IDS limit of 16G pages. After discussing with UK Tech Support, the suggestion was made to drop an index to get things going again quickly. This seemed a good idea - dropping an index is just a metter or re-arranging a few pointers and should be more-or-less instantaneous, right? Wrong! The drop index has been running for 4 hours, has done 1.3 million page reads and about 50,000 writes, and like Old Man River, just keeps rolling along. I have absolutlely no idea how long it will take, and no-one in UK Support can tell me because they don't know in detail what it does. So I could crash my instance and allow it to fast-recover - presumably another 4 hours, and hope there's no corruption. Or I can just let it run. But, if it takes more than 24 hours, we'll go bust, no kidding! Does anyone have any words of wisdom please? Please reply to neil.truby@londis.co.uk.
Hi Neil,
I would try to find out the pages the server
is currently reading.
You can find them out by reading the sysmaster
table "sysrstcb" ( isrecnum column ). Identify
the session id and have a look into the
correspondig "sysrstcb" blocks "isrecnum".
"isrecnum" is the current rowid and the leading 3 Bytes
represent the logical page number.
Then you should be able to monitor the processing
of the index-page-scan.
On the other hand, I cannot understand why the
"drop index" statement should help. By default,
all explicit created indexes are detached in
version 9.20 and therefore this way will not
help with your 16G pages problem.
How about fragmentation ?
CREATE TABLE newone ( like old one );
ALTER FRAGMENT ON TABLE oldone ATTACH newone;
I have no idea if this will work, but I think
it's worth a try.
Best regards,
Stefan
Neil Truby wrote:
>
> IDS 9.20 FC2 on HP-UX 11.0
>
> One of the tables in my live database has hit an IDS limit of 16G pages.
> After discussing with UK Tech Support, the suggestion was made to drop an
> index to get things going again quickly. This seemed a good idea - dropping
> an index is just a metter or re-arranging a few pointers and should be
> more-or-less instantaneous, right?
>
> Wrong! The drop index has been running for 4 hours, has done 1.3 million
> page reads and about 50,000 writes, and like Old Man River, just keeps
> rolling along. I have absolutlely no idea how long it will take, and no-one
> in UK Support can tell me because they don't know in detail what it does.
>
> So I could crash my instance and allow it to fast-recover - presumably
> another 4 hours, and hope there's no corruption. Or I can just let it run.
> But, if it takes more than 24 hours, we'll go bust, no kidding!
>
> Does anyone have any words of wisdom please? Please reply to
> neil.truby@londis.co.uk.
--
Stefan Weideneder
Phone: +49 89/3565478-2 ---------------
--- Fax: +49 89/3565478-3 -------------
------ mailto:/stefan@weideneder.de ---
-------- http://www.weideneder.de -----
Neil, Did you ever find out why it was doing all the page reads? Like someone mentioned, if the indexes are detached, this really should be a pretty quick thing. Will In article <954c4r$6ru$1@lyonesse.netcom.net.uk>, "Neil Truby" <ntruby@netcomuk.co.uk> wrote: > IDS 9.20 FC2 on HP-UX 11.0 > > One of the tables in my live database has hit an IDS limit of 16G pages. > After discussing with UK Tech Support, the suggestion was made to drop an > index to get things going again quickly. This seemed a good idea - dropping > an index is just a metter or re-arranging a few pointers and should be > more-or-less instantaneous, right? > > Wrong! The drop index has been running for 4 hours, has done 1.3 million > page reads and about 50,000 writes, and like Old Man River, just keeps > rolling along. I have absolutlely no idea how long it will take, and no-one > in UK Support can tell me because they don't know in detail what it does. > > So I could crash my instance and allow it to fast-recover - presumably > another 4 hours, and hope there's no corruption. Or I can just let it run. > But, if it takes more than 24 hours, we'll go bust, no kidding! > > Does anyone have any words of wisdom please? Please reply to > neil.truby@londis.co.uk. > > Sent via Deja.com http://www.deja.com/