Drop rowid transaction

Greetings. I am hoping I have not painted myself into a corner. I created a fragmented table with rowids. This was to prevent a problem I had encountered in a previous load of another fragmented table. With the perfect 20/20 hindsight most of us possess, I realized that the problem would have been better prevented a different way. Furthermore, to my dismay, I found that one cannot detach a partition if a rowid column is defined on the table. That's bad down the road because eventually we will want to purge the oldest data by detaching its partition. Hence, I would like to: ALTER TABLE ... drop rowids; My hesitation: This table already has over 75 million rows! How does IDS 11.5 handle this? Does it: - Copy the table [partitions] to new TBLspaces without the rowid column, rebuilding all the indexes as it goes? Or - Just drop the rowid index, henceforth ignore the rowid column in existing rows and skip it in new rows. Obviously, the first is likely to lead to a long transaction. (I have a method for migrating a table but I'd rather not go through that.) So I am hoping the second option is actually the case. But is there anyone out there who really knows what it does? The manuals and Knowledge Center pages are remarkably unhelpful for this question. Thanks MUCH! -- Jacob S

Original post: Greetings. I am hoping I have not painted myself into a corner. I created a fragmented table with rowids. This was to prevent a problem I had encountered in a previous load of another fragmented table. With the perfect 20/20 hindsight most of us possess, I realized that the problem would have been better prevented a different way. Furthermore, to my dismay, I found that one cannot detach a partition if a rowid column is defined on the table. That's bad down the road because eventually we will want to purge the oldest data by detaching its partition. Hence, I would like to: ALTER TABLE ... drop rowids; My hesitation: This table already has over 75 million rows! How does IDS 11.5 handle this? Does it: - Copy the table [partitions] to new TBLspaces without the rowid column, rebuilding all the indexes as it goes? Or - Just drop the rowid index, henceforth ignore the rowid column in existing rows and skip it in new rows. Obviously, the first is likely to lead to a long transaction. (I have a method for migrating a table but I'd rather not go through that.) So I am hoping the second option is actually the case. But is there anyone out there who really knows what it does? The manuals and Knowledge Center pages are remarkably unhelpful for this question. Thanks MUCH! -- Jacob S Response: So I just did the following simple test using 11.50.FC8 create table t1 (c1 int) with rowids fragment by round robin in rootdbs, dbs1 ; select partn, fragtype from sysfragments, systables where systables.tabid = sysfragments.tabid andsystables.tabname = "t1"; alter table t1 drop rowids; select partn, fragtype from sysfragments, systables where systables.tabid = sysfragments.tabid andsystables.tabname = "t1"; Here's the output of the select statements against sysfragments: (1st query) partn fragtype 1048950 T 2097154 T 1048951 I 2097155 I (2nd query) partn fragtype 1048952 T 2097156 T So based on the fact that the partn's appear to change after the alter table drop rowids, it would appear that option 1 is happening (it's copying the data to new partitions). Jacques Renaut IBM Informix Advanced Support

Jacques, Thanks so much for the response and the research it required. I must have been having a Homer Simpson "d'oh" moment, not thinking of that. I have a migration strategy that I have used in the recent past with great success but it won't be an issue for another couple of years and scheduling down time with my users is a formidable task on its own. It'll keep. Regards from the painted corner, -- Jacob S.

Do you have enough space to create a duplicate table without rowids and change the names around to do the switch that way? You could potentially have no noticeable downtime at all if you can get your final update to sync the table and the name switch in the same transaction. --EEM > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > JACOB SALOMON > Sent: Thursday, October 06, 2016 11:49 AM > To: ids@iiug.org > Subject: Re: Drop rowid transaction [37931] > > Jacques, > > Thanks so much for the response and the research it required. I must > have been having a Homer Simpson "d'oh" moment, not thinking of that. > > I have a migration strategy that I have used in the recent past with > great success but it won't be an issue for another couple of years and > scheduling down time with my users is a formidable task on its own. > It'll keep. > > Regards from the painted corner, > > -- Jacob S. > > > *********************************************************************** > ******** > Forum Note: Use "Reply" to post a response in the discussion forum.

Everett, I do have a migration strategy in place using that very scheme; we've used it a few times for poorly planned tables on the verge of running into the partition page limit. We even have a fast table-copy Perl script I can't yet release to the IIUG archive. But it does require getting the go-ahead from several layers of client and boss, as well as scheduling a few hours of "hands-off" time for that table. I've only just planted the notion into my boss's head. If it eats away at him like it does at me, we have project on our hands. I posted the question because I was hoping for a quick & clean solution that avoids all of the above. Lacking that (see Jacques's research), we will have to go for slow and elegant, pretty much the way we've done it before. -- Jacob S