Loading 55 million rows on IDS 10.0

We have a project that is going to require 55 million rows to be added to a single table on several servers. This is a production system that runs 24/7/365, so we can't shut off the logs for an extended period of time while the data is being loaded. I have suggested dropping the indexes, loading the data, and recreating the indexes, but I thought that someone else might have a better idea. =20 The servers are running IDS 10.00.UC5 on either AIX 5.3.0.0 or AIX 4.3.3.0. =20 Thanks in advance. =20 Keith Schleicher IT Database Administrator B2-253B-A Office: 847-286-4027 Cell: 224-210-8358 Blackberry: 2242108358@messaging.sprintpcs.com <mailto:2242108358@messaging.sprintpcs.com>=20 Page: 2242108358@sprint.skytel.com <mailto:2242108358@sprint.skytel.com> This message, including any attachments, is the property of Sears Holdings = Corporation and/or one of its subsidiaries. It is confidential and may cont= ain proprietary or legally privileged information. If you are not the inten= ded recipient, please delete it without reading the contents. Thank you.

I would definately alter the table to type raw for the load. You will save yourself a lot of logging woes.

Create the table raw, build the indexes, convert to normal mode, and then build any constraints. Raw tables aren't logged. Jeff Mitchell -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Schleicher, Keith Sent: Tuesday, August 09, 2011 2:32 PM To: ids@iiug.org Subject: Loading 55 million rows on IDS 10.0 [24573] We have a project that is going to require 55 million rows to be added to a single table on several servers. This is a production system that runs 24/7/365, so we can't shut off the logs for an extended period of time while the data is being loaded. I have suggested dropping the indexes, loading the data, and recreating the indexes, but I thought that someone else might have a better idea. =20 The servers are running IDS 10.00.UC5 on either AIX 5.3.0.0 or AIX 4.3.3.0. =20 Thanks in advance. =20 Keith Schleicher IT Database Administrator B2-253B-A Office: 847-286-4027 Cell: 224-210-8358 Blackberry: 2242108358@messaging.sprintpcs.com <mailto:2242108358@messaging.sprintpcs.com>=20 Page: 2242108358@sprint.skytel.com <mailto:2242108358@sprint.skytel.com> This message, including any attachments, is the property of Sears Holdings = Corporation and/or one of its subsidiaries. It is confidential and may cont= ain proprietary or legally privileged information. If you are not the inten= ded recipient, please delete it without reading the contents. Thank you. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Forgot to add - load the data - after creating the raw table. Jeff Mitchell -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Mitchell, Jeffrey J. Sent: Tuesday, August 09, 2011 2:41 PM To: ids@iiug.org Subject: RE: Loading 55 million rows on IDS 10.0 [24576] Create the table raw, build the indexes, convert to normal mode, and then build any constraints. Raw tables aren't logged. Jeff Mitchell -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Schleicher, Keith Sent: Tuesday, August 09, 2011 2:32 PM To: ids@iiug.org Subject: Loading 55 million rows on IDS 10.0 [24573] We have a project that is going to require 55 million rows to be added to a single table on several servers. This is a production system that runs 24/7/365, so we can't shut off the logs for an extended period of time while the data is being loaded. I have suggested dropping the indexes, loading the data, and recreating the indexes, but I thought that someone else might have a better idea. =20 The servers are running IDS 10.00.UC5 on either AIX 5.3.0.0 or AIX 4.3.3.0. =20 Thanks in advance. =20 Keith Schleicher IT Database Administrator B2-253B-A Office: 847-286-4027 Cell: 224-210-8358 Blackberry: 2242108358@messaging.sprintpcs.com <mailto:2242108358@messaging.sprintpcs.com>=20 Page: 2242108358@sprint.skytel.com <mailto:2242108358@sprint.skytel.com> This message, including any attachments, is the property of Sears Holdings = Corporation and/or one of its subsidiaries. It is confidential and may cont= ain proprietary or legally privileged information. If you are not the inten= ded recipient, please delete it without reading the contents. Thank you. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

After dropping the indexes, alter the table to RAW which will not log the data load, then alter it back to STANDARD after the load completes and before rebuilding indexes. Once the table is back in service, you should take a level 0 archive since the load will not have been logged and so will not be restoreable. FYI, Informix 10.00 will go out-of-support very soon now, you should plan to upgrade to 11.50 or 11.70. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) 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 Tue, Aug 9, 2011 at 3:31 PM, Schleicher, Keith < Keith.Schleicher@searshc.com> wrote: > We have a project that is going to require 55 million rows to be added > to a single table on several servers. This is a production system that > runs 24/7/365, so we can't shut off the logs for an extended period of > time while the data is being loaded. I have suggested dropping the > indexes, loading the data, and recreating the indexes, but I thought > that someone else might have a better idea. > > =20 > > The servers are running IDS 10.00.UC5 on either AIX 5.3.0.0 or AIX > 4.3.3.0. > > =20 > > Thanks in advance. > > =20 > > Keith Schleicher > > IT Database Administrator > > B2-253B-A > > Office: 847-286-4027 > > Cell: 224-210-8358 > > Blackberry: 2242108358@messaging.sprintpcs.com > <mailto:2242108358@messaging.sprintpcs.com>=20 > > Page: 2242108358@sprint.skytel.com <mailto:2242108358@sprint.skytel.com> > > This message, including any attachments, is the property of Sears Holdings > = > Corporation and/or one of its subsidiaries. It is confidential and may > cont= > ain proprietary or legally privileged information. If you are not the > inten= > ded recipient, please delete it without reading the contents. Thank you. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --20cf307f35362f808904aa17c936

I would actually load it into a shadow (raw) table with HPL, then either = trickle it in to the main table OR make the shadow look identical to the = primary (keys, constraints, including NOT NULL etc) and attach it as a = second fragment. Test that out until you get the SQL right, because = there will be twelve things you forget to do. That way you don't impact performance until that little-bitty second = where you attach. Mind you this will alter the definition of the table and all active = prepared statements against the table will need to be re-prepared (i.e. = re-start all applications). j. On Aug 9, 2011, at 3:31 PM, Schleicher, Keith wrote: > We have a project that is going to require 55 million rows to be added=20= > to a single table on several servers. This is a production system that=20= > runs 24/7/365, so we can't shut off the logs for an extended period of=20= > time while the data is being loaded. I have suggested dropping the=20 > indexes, loading the data, and recreating the indexes, but I thought=20= > that someone else might have a better idea.=20 >=20 > =3D20=20 >=20 > The servers are running IDS 10.00.UC5 on either AIX 5.3.0.0 or AIX=20 > 4.3.3.0.=20 >=20 > =3D20=20 >=20 > Thanks in advance.=20 >=20 > =3D20=20 >=20 > Keith Schleicher=20 >=20 > IT Database Administrator=20 >=20 > B2-253B-A=20 >=20 > Office: 847-286-4027=20 >=20 > Cell: 224-210-8358=20 >=20 > Blackberry: 2242108358@messaging.sprintpcs.com=20 > <mailto:2242108358@messaging.sprintpcs.com>=3D20=20 >=20 > Page: 2242108358@sprint.skytel.com = <mailto:2242108358@sprint.skytel.com>=20 >=20 > This message, including any attachments, is the property of Sears = Holdings =3D=20 > Corporation and/or one of its subsidiaries. It is confidential and may = cont=3D=20 > ain proprietary or legally privileged information. If you are not the = inten=3D=20 > ded recipient, please delete it without reading the contents. Thank = you.=20 >=20 >=20 > = **************************************************************************= *****=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=20= >=20

AFAIK 10 went EOS in October 2010. http://www-01.ibm.com/software/data/support/lifecycle/ j. On Aug 9, 2011, at 3:44 PM, Art Kagel wrote: > After dropping the indexes, alter the table to RAW which will not log = the=20 > data load, then alter it back to STANDARD after the load completes and=20= > before rebuilding indexes. Once the table is back in service, you = should=20 > take a level 0 archive since the load will not have been logged and so = will=20 > not be restoreable.=20 >=20 > FYI, Informix 10.00 will go out-of-support very soon now, you should = plan to=20 > upgrade to 11.50 or 11.70.=20 >=20 > Art=20 >=20 > Art S. Kagel=20 > Advanced DataTools (www.advancedatatools.com)=20 > Blog: http://informix-myview.blogspot.com/=20 >=20 > Disclaimer: Please keep in mind that my own opinions are my own = opinions and=20 > do not reflect on my employer, Advanced DataTools, the IIUG, nor any = other=20 > organization with which I am associated either explicitly, implicitly, = or by=20 > inference. Neither do those opinions reflect those of other = individuals=20 > affiliated with any entity with which I am affiliated nor those of the=20= > entities themselves.=20 >=20 > On Tue, Aug 9, 2011 at 3:31 PM, Schleicher, Keith <=20 > Keith.Schleicher@searshc.com> wrote:=20 >=20 >> We have a project that is going to require 55 million rows to be = added=20 >> to a single table on several servers. This is a production system = that=20 >> runs 24/7/365, so we can't shut off the logs for an extended period = of=20 >> time while the data is being loaded. I have suggested dropping the=20 >> indexes, loading the data, and recreating the indexes, but I thought=20= >> that someone else might have a better idea.=20 >>=20 >> =3D20=20 >>=20 >> The servers are running IDS 10.00.UC5 on either AIX 5.3.0.0 or AIX=20 >> 4.3.3.0.=20 >>=20 >> =3D20=20 >>=20 >> Thanks in advance.=20 >>=20 >> =3D20=20 >>=20 >> Keith Schleicher=20 >>=20 >> IT Database Administrator=20 >>=20 >> B2-253B-A=20 >>=20 >> Office: 847-286-4027=20 >>=20 >> Cell: 224-210-8358=20 >>=20 >> Blackberry: 2242108358@messaging.sprintpcs.com=20 >> <mailto:2242108358@messaging.sprintpcs.com>=3D20=20 >>=20 >> Page: 2242108358@sprint.skytel.com = <mailto:2242108358@sprint.skytel.com>=20 >>=20 >> This message, including any attachments, is the property of Sears = Holdings=20 >> =3D=20 >> Corporation and/or one of its subsidiaries. It is confidential and = may=20 >> cont=3D=20 >> ain proprietary or legally privileged information. If you are not the=20= >> inten=3D=20 >> ded recipient, please delete it without reading the contents. Thank = you.=20 >>=20 >>=20 >>=20 >>=20 > = **************************************************************************= *****=20 >> Forum Note: Use "Reply" to post a response in the discussion forum.=20= >>=20 >>=20 >=20 > --20cf307f35362f808904aa17c936=20 >=20 >=20 > = **************************************************************************= *****=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=20= >=20

You are correct. I always remember it's September and forget it was last September. ;-( Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) 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 Tue, Aug 9, 2011 at 3:49 PM, Jack Parker <jack.parker4@verizon.net>wrote: > AFAIK 10 went EOS in October 2010. > > http://www-01.ibm.com/software/data/support/lifecycle/ > > j. > > On Aug 9, 2011, at 3:44 PM, Art Kagel wrote: > > > After dropping the indexes, alter the table to RAW which will not log = > the=20 > > data load, then alter it back to STANDARD after the load completes > and=20= > > > before rebuilding indexes. Once the table is back in service, you = > should=20 > > take a level 0 archive since the load will not have been logged and so = > will=20 > > not be restoreable.=20 > >=20 > > FYI, Informix 10.00 will go out-of-support very soon now, you should = > plan to=20 > > upgrade to 11.50 or 11.70.=20 > >=20 > > Art=20 > >=20 > > Art S. Kagel=20 > > Advanced DataTools (www.advancedatatools.com)=20 > > Blog: http://informix-myview.blogspot.com/=20 > >=20 > > Disclaimer: Please keep in mind that my own opinions are my own = > opinions and=20 > > do not reflect on my employer, Advanced DataTools, the IIUG, nor any = > other=20 > > organization with which I am associated either explicitly, implicitly, = > or by=20 > > inference. Neither do those opinions reflect those of other = > individuals=20 > > affiliated with any entity with which I am affiliated nor those of > the=20= > > > entities themselves.=20 > >=20 > > On Tue, Aug 9, 2011 at 3:31 PM, Schleicher, Keith <=20 > > Keith.Schleicher@searshc.com> wrote:=20 > >=20 > >> We have a project that is going to require 55 million rows to be = > added=20 > >> to a single table on several servers. This is a production system = > that=20 > >> runs 24/7/365, so we can't shut off the logs for an extended period = > of=20 > >> time while the data is being loaded. I have suggested dropping the=20 > >> indexes, loading the data, and recreating the indexes, but I thought=20= > > >> that someone else might have a better idea.=20 > >>=20 > >> =3D20=20 > >>=20 > >> The servers are running IDS 10.00.UC5 on either AIX 5.3.0.0 or AIX=20 > >> 4.3.3.0.=20 > >>=20 > >> =3D20=20 > >>=20 > >> Thanks in advance.=20 > >>=20 > >> =3D20=20 > >>=20 > >> Keith Schleicher=20 > >>=20 > >> IT Database Administrator=20 > >>=20 > >> B2-253B-A=20 > >>=20 > >> Office: 847-286-4027=20 > >>=20 > >> Cell: 224-210-8358=20 > >>=20 > >> Blackberry: 2242108358@messaging.sprintpcs.com=20 > >> <mailto:2242108358@messaging.sprintpcs.com>=3D20=20 > >>=20 > >> Page: 2242108358@sprint.skytel.com = > <mailto:2242108358@sprint.skytel.com>=20 > >>=20 > >> This message, including any attachments, is the property of Sears = > Holdings=20 > >> =3D=20 > >> Corporation and/or one of its subsidiaries. It is confidential and = > may=20 > >> cont=3D=20 > >> ain proprietary or legally privileged information. If you are not > the=20= > > >> inten=3D=20 > >> ded recipient, please delete it without reading the contents. Thank = > you.=20 > >>=20 > >>=20 > >>=20 > >>=20 > > = > **************************************************************************= > *****=20 > >> Forum Note: Use "Reply" to post a response in the discussion forum.=20= > > >>=20 > >>=20 > >=20 > > --20cf307f35362f808904aa17c936=20 > >=20 > >=20 > > = > **************************************************************************= > *****=20 > > Forum Note: Use "Reply" to post a response in the discussion forum.=20= > > >=20 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --bcaec51b9a49b5513604aa17f509

A couple of questions. A lot of folks have suggested changing the table to a RAW table - and that might be a viable solution. But this is a 24X7 system so I'm not sure about going to a RAW table is best. Also this needs to be done on several servers? Does that mean that ER is involved? If so, then ER will block you from converting to RAW. While this load process is underway, can the table be updated by other user activity? If that is true then perhaps it isn't such a good idea to drop indexes on the table because that would in effect also remove constraints on the table as well. How wide are the rows? M.P. From: "Schleicher, Keith" <Keith.Schleicher@searshc.com> To: ids@iiug.org Date: 08/09/2011 01:32 PM Subject: Loading 55 million rows on IDS 10.0 [24573] Sent by: ids-bounces@iiug.org We have a project that is going to require 55 million rows to be added to a single table on several servers. This is a production system that runs 24/7/365, so we can't shut off the logs for an extended period of time while the data is being loaded. I have suggested dropping the indexes, loading the data, and recreating the indexes, but I thought that someone else might have a better idea. =20 The servers are running IDS 10.00.UC5 on either AIX 5.3.0.0 or AIX 4.3.3.0. =20 Thanks in advance. =20 Keith Schleicher IT Database Administrator B2-253B-A Office: 847-286-4027 Cell: 224-210-8358 Blackberry: 2242108358@messaging.sprintpcs.com <mailto:2242108358@messaging.sprintpcs.com>=20 Page: 2242108358@sprint.skytel.com <mailto:2242108358@sprint.skytel.com> This message, including any attachments, is the property of Sears Holdings = Corporation and/or one of its subsidiaries. It is confidential and may cont= ain proprietary or legally privileged information. If you are not the inten= ded recipient, please delete it without reading the contents. Thank you. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Just happen to have that one branded on my forehead while we struggle to = allow customers to move to 11.7 and W2K8R2. In the interim, I am their = IDS 10 support - not that an upgrade changes anything. j. On Aug 9, 2011, at 3:56 PM, Art Kagel wrote: > You are correct. I always remember it's September and forget it was = last=20 > September. ;-(=20 >=20 > Art=20 >=20 > Art S. Kagel=20 > Advanced DataTools (www.advancedatatools.com)=20 > Blog: http://informix-myview.blogspot.com/=20 >=20 > Disclaimer: Please keep in mind that my own opinions are my own = opinions and=20 > do not reflect on my employer, Advanced DataTools, the IIUG, nor any = other=20 > organization with which I am associated either explicitly, implicitly, = or by=20 > inference. Neither do those opinions reflect those of other = individuals=20 > affiliated with any entity with which I am affiliated nor those of the=20= > entities themselves.=20 >=20 > On Tue, Aug 9, 2011 at 3:49 PM, Jack Parker = <jack.parker4@verizon.net>wrote:=20 >=20 >> AFAIK 10 went EOS in October 2010.=20 >>=20 >> http://www-01.ibm.com/software/data/support/lifecycle/=20 >>=20 >> j.=20 >>=20 >> On Aug 9, 2011, at 3:44 PM, Art Kagel wrote:=20 >>=20 >>> After dropping the indexes, alter the table to RAW which will not = log =3D=20 >> the=3D20=20 >>> data load, then alter it back to STANDARD after the load completes=20= >> and=3D20=3D=20 >>=20 >>> before rebuilding indexes. Once the table is back in service, you =3D=20= >> should=3D20=20 >>> take a level 0 archive since the load will not have been logged and = so =3D=20 >> will=3D20=20 >>> not be restoreable.=3D20=20 >>> =3D20=20 >>> FYI, Informix 10.00 will go out-of-support very soon now, you should = =3D=20 >> plan to=3D20=20 >>> upgrade to 11.50 or 11.70.=3D20=20 >>> =3D20=20 >>> Art=3D20=20 >>> =3D20=20 >>> Art S. Kagel=3D20=20 >>> Advanced DataTools (www.advancedatatools.com)=3D20=20 >>> Blog: http://informix-myview.blogspot.com/=3D20=20 >>> =3D20=20 >>> Disclaimer: Please keep in mind that my own opinions are my own =3D=20= >> opinions and=3D20=20 >>> do not reflect on my employer, Advanced DataTools, the IIUG, nor any = =3D=20 >> other=3D20=20 >>> organization with which I am associated either explicitly, = implicitly, =3D=20 >> or by=3D20=20 >>> inference. Neither do those opinions reflect those of other =3D=20 >> individuals=3D20=20 >>> affiliated with any entity with which I am affiliated nor those of=20= >> the=3D20=3D=20 >>=20 >>> entities themselves.=3D20=20 >>> =3D20=20 >>> On Tue, Aug 9, 2011 at 3:31 PM, Schleicher, Keith <=3D20=20 >>> Keith.Schleicher@searshc.com> wrote:=3D20=20 >>> =3D20=20 >>>> We have a project that is going to require 55 million rows to be =3D=20= >> added=3D20=20 >>>> to a single table on several servers. This is a production system =3D= =20 >> that=3D20=20 >>>> runs 24/7/365, so we can't shut off the logs for an extended period = =3D=20 >> of=3D20=20 >>>> time while the data is being loaded. I have suggested dropping = the=3D20=20 >>>> indexes, loading the data, and recreating the indexes, but I = thought=3D20=3D=20 >>=20 >>>> that someone else might have a better idea.=3D20=20 >>>> =3D20=20 >>>> =3D3D20=3D20=20 >>>> =3D20=20 >>>> The servers are running IDS 10.00.UC5 on either AIX 5.3.0.0 or = AIX=3D20=20 >>>> 4.3.3.0.=3D20=20 >>>> =3D20=20 >>>> =3D3D20=3D20=20 >>>> =3D20=20 >>>> Thanks in advance.=3D20=20 >>>> =3D20=20 >>>> =3D3D20=3D20=20 >>>> =3D20=20 >>>> Keith Schleicher=3D20=20 >>>> =3D20=20 >>>> IT Database Administrator=3D20=20 >>>> =3D20=20 >>>> B2-253B-A=3D20=20 >>>> =3D20=20 >>>> Office: 847-286-4027=3D20=20 >>>> =3D20=20 >>>> Cell: 224-210-8358=3D20=20 >>>> =3D20=20 >>>> Blackberry: 2242108358@messaging.sprintpcs.com=3D20=20 >>>> <mailto:2242108358@messaging.sprintpcs.com>=3D3D20=3D20=20 >>>> =3D20=20 >>>> Page: 2242108358@sprint.skytel.com =3D=20 >> <mailto:2242108358@sprint.skytel.com>=3D20=20 >>>> =3D20=20 >>>> This message, including any attachments, is the property of Sears =3D= =20 >> Holdings=3D20=20 >>>> =3D3D=3D20=20 >>>> Corporation and/or one of its subsidiaries. It is confidential and = =3D=20 >> may=3D20=20 >>>> cont=3D3D=3D20=20 >>>> ain proprietary or legally privileged information. If you are not=20= >> the=3D20=3D=20 >>=20 >>>> inten=3D3D=3D20=20 >>>> ded recipient, please delete it without reading the contents. Thank = =3D=20 >> you.=3D20=20 >>>> =3D20=20 >>>> =3D20=20 >>>> =3D20=20 >>>> =3D20=20 >>> =3D=20 >> = **************************************************************************= =3D=20 >> *****=3D20=20 >>>> Forum Note: Use "Reply" to post a response in the discussion = forum.=3D20=3D=20 >>=20 >>>> =3D20=20 >>>> =3D20=20 >>> =3D20=20 >>> --20cf307f35362f808904aa17c936=3D20=20 >>> =3D20=20 >>> =3D20=20 >>> =3D=20 >> = **************************************************************************= =3D=20 >> *****=3D20=20 >>> Forum Note: Use "Reply" to post a response in the discussion = forum.=3D20=3D=20 >>=20 >>> =3D20=20 >>=20 >>=20 >>=20 >>=20 > = **************************************************************************= *****=20 >> Forum Note: Use "Reply" to post a response in the discussion forum.=20= >>=20 >>=20 >=20 > --bcaec51b9a49b5513604aa17f509=20 >=20 >=20 > = **************************************************************************= *****=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=20= >=20

On 09/08/2011 20:31, Schleicher, Keith wrote: > We have a project that is going to require 55 million rows to be added > to a single table on several servers. This is a production system that > runs 24/7/365, so we can't shut off the logs for an extended period of > time while the data is being loaded. I have suggested dropping the > indexes, loading the data, and recreating the indexes, but I thought > that someone else might have a better idea. > > =20 > > The servers are running IDS 10.00.UC5 on either AIX 5.3.0.0 or AIX > 4.3.3.0. I am, as ever, entirely unclear what the problem is here. Why is "loading the tables on a dripfeed basis while the system is running" an issue? -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com I will now proceed to pleasure myself with this fish.

Since you dont have the luxury of turning off logging, your plan is as sound as it can get outside of taking risks (alter table raw table turns off logging for the table and also manipulates internal metadata structures, an unwarranted added risk for a 24 x 7 table). Hopefully you have at least one unique index on this table. That will allow you to do the load using a defined commit interval. 1. Drop all indexes that you can afford to drop , of course retain the unique index if you have one defined. 2. use one of the tools (not dbaccess) such as hpl/dbload and define a commit interval as high as possible that will NOT trip a long trx rollback. This will require some analysis of course but remember a long trx rollback will take a long time if many indexes remain intact on this table. 3. be sure to turn on pdq when recreating the indexes. 4. run update stats when done, this will take advantage of any cached data from the index builds. 5. If no unique index exists , then you are forced to do a massive load (one commit afterwards) so be sure to you have enough logs defined to avoid a long trx. 6. If doing this from home, nohup and background your load as the server could decide to kick your telnet session out. Good luck !

On 11/08/2011 16:29, MARK JALKIEWICZ wrote: > Since you dont have the luxury of turning off logging, your plan is as sound > as it can get outside of taking risks (alter table raw table turns off logging > for the table and also manipulates internal metadata structures, an > unwarranted added risk for a 24 x 7 table). > > Hopefully you have at least one unique index on this table. That will allow > you to do the load using a defined commit interval. > > 1. Drop all indexes that you can afford to drop , of course retain the unique > index if you have one defined. > > 2. use one of the tools (not dbaccess) such as hpl/dbload and define a commit > interval as high as possible that will NOT trip a long trx rollback. This will > require some analysis of course but remember a long trx rollback will take a > long time if many indexes remain intact on this table. > > 3. be sure to turn on pdq when recreating the indexes. > > 4. run update stats when done, this will take advantage of any cached data > from the index builds. > > 5. If no unique index exists , then you are forced to do a massive load (one > commit afterwards) so be sure to you have enough logs defined to avoid a long > trx. > > 6. If doing this from home, nohup and background your load as the server could > decide to kick your telnet session out. Seriously, I still don't know what the actual issue is here. Loading up 55M rows on a fully-decked out Workgroup Edition box shouldn't take much more than 20 minutes, with indexes and logging in place. -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com I will now proceed to pleasure myself with this fish.