how to update round-robin frag'd table by fragment
NormaJean Sebastian needed to update 44 million rows of a 144-million-row, 6-fragment round-robin table and had failed repeatedly. Scottishpoet asked for the error. Olfan advised a cursor-based tool committing every n rows, or a level 0 backup and logging disabled in single-user mode. NormaJean said the team dropped the test table and would archive the data instead.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Hi, I have a 60GB frag'd table (6 frags). The table does not have rowid, nor did i add it when we frag'd. We want to update 44 million rows in this 144 million row table. We have failed multiple times....Now we want to try to update the table by updating rows in one fragment at a time. I have the 6 partition numbers for the frags in this table.... now how do i write a select or update statement on this table such that i only do one frag? Maybe this is really easy... but my head is foggy from a cold... thanks, Norma Jean -- Message posted via DBMonster.com http://www.dbmonster.com/Uwe/Forums.aspx/informix/200505/1
or perhaps, how easy (and time consuming) is it to alter the table to be 6 individual tables, which we can then update, and then attach them back as a big frag'd table?...
thank you for your support. I got responses regarding the speed of attach/detach and also some advice on trying raw tables.... I will experiment with both. in the meantime the team with the problem decided to drop the table in the test environment and will archive the data from SAP production so it will not hamper them in the future.
We want to update 44 million rows in this 144 million row table. We have failed multiple times.... what problems have you had? Have you hit a long transaction error? any error numbers or messages?
Hi there, you should build yourself a small tool ( C, 4GL, perl even ) that takes your query and executes it using a statement cursor issuing a COMMIT every (n) rows. Or, if you can afford taking the database offline for some time, you could make a L0 backup (!!) and then just disable logging on the server instance for the duration of the statement. But make pretty sure you run the statement in single user mode. Best regards, Olfan scottishpoet schrieb: > We want to update 44 million rows in this 144 million row table. We > have > failed multiple times.... > > what problems have you had? Have you hit a long transaction error? > > any error numbers or messages? >