In place alter issue

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

bozon - 26 Nov 2008 14:28 GMT

I am cleaning up my in place alters for my migration to 11.5. I have
everything working. I can find them. I can fake update them. I only
have one problem when I fake update them I get the following after
running oncheck -pT db:table

Home Data Page Version Summary

Version Count

0 (oldest) 0
1 (current) 15961

I know I don't have any records in the old format but the version is
still around. I fiddled around and found that if I alter the primary
index to clustered I get:

Home Data Page Version Summary

Version Count

0 (current) 15961

What is going on and how can I fix it without doing a cluster on the
index that I don't want to do? Or do I even need to fix it?

Reply to this Message

Fernando Nunes - 26 Nov 2008 17:25 GMT

You don't need to fix it...
You'll only see the second output after recreating the table.

This is why it's not possible to get all the tables with "pending in-place
alters" in a quick way. The "quick" way will always see the tables that
*had* a pending alter.
And to get a similar output you have to read all the partition pages...

Regards.

> I am cleaning up my in place alters for my migration to 11.5. I have
> everything working. I can find them. I can fake update them. I only
[quoted text clipped - 24 lines]
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list

Signature

Fernando Nunes
Portugal

http://informix-technology.blogspot.com
My email works... but I don't check it frequently...

Reply to this Message

bozon - 26 Nov 2008 19:40 GMT

> You don't need to fix it...
> You'll only see the second output after recreating the table.
[quoted text clipped - 41 lines]
> http://informix-technology.blogspot.com
> My email works... but I don't check it frequently...

Thanks for your help.

Using SQL to find the pending in-place-alters works pretty fast, much
faster than oncheck does.

Reply to this Message

Fernando Nunes - 26 Nov 2008 22:43 GMT

>> You don't need to fix it...
>> You'll only see the second output after recreating the table.
[quoted text clipped - 36 lines]
> Using SQL to find the pending in-place-alters works pretty fast, much
> faster than oncheck does.

I didn't explain myself correctly.
The "sql way" will check the partition headers. This more or less reads one
page per partition. But as you doubt shows, it only shows the partitions
(tables) that at some point in time were in-place altered.
Oncheck reads all the partitions pages to count how many of them are in each
version. Much slower of course, but it's the only way to really be sure.
The SQL version will keep reporting all the tables, even those you applied
dummy updates to.

I'm not against the sql way... If the tables found are not many and are not too
big you may get the SQL and the dummy updates for the found tables before you
get the results from oncheck...

By the way, you could also do the same that oncheck does, with SQL on
sysmaster, but it would take the same amount of time...

Regards.

Signature

Fernando Nunes
Portugal

http://informix-technology.blogspot.com
My email works... but I don't check it frequently...

Reply to this Message

bozon - 01 Dec 2008 14:14 GMT

> >> You don't need to fix it...
> >> You'll only see the second output after recreating the table.
[quoted text clipped - 61 lines]
> http://informix-technology.blogspot.com
> My email works... but I don't check it frequently...

I believe there is a sql message to look at all of the pages. At least
it was reported to do that in the IBM article that I read. Here is a
link to it:

http://www-01.ibm.com/support/docview.wss?uid=swg21226410

Reply to this Message