RE: XPS 8.1 reorg large table to reduce extents
Larry needed to reorganize a table on XPS 8.1 (AIX) that had grown to 400+ extents, worried about a 2GB unload-file limit and about logging/long transactions if he copied the data into a new table. Suggestions: Art Kagel's ul.ec or dbcopy utilities (which can split output into multiple files or copy table-to-table, then drop/rename and rebuild indexes); and creating a RAW table with the desired extent sizes, doing INSERT INTO ... SELECT (light appends, dirty read isolation, stats run), then ALTER to STANDARD and rebuilding indexes. For external unloads, XPS external tables with several files were recommended. No confirmation from the original poster is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I need suggestions. I have a large table with over 400 extents. I would like to reorganize it to reduce the extents. I believe with XPS 8.1 on AIX, that there is still a file limitation of 2GB. IS that correct? If so, that eliminates unloading the table to disk, dropping and recreating the table? Does that include UNLOAD TO "filename" SELECT ..... as well? Other options, I may have enough space to create a new table in the database while the old one exists. Can I just select the data into the new table from the old table? I have logging running, and I don't want this logged, or it may be a long transaction or have other logging problems. Is there a way around this? Other suggestions? Larry
In utils2_ak is a utility, ul.ec, which can export the table to a binary file and reload it. The utility is not limited by the 2GB limit, but if you need to you can limit the size of the files it creates (if the filesystem doesn't support large files) and it will create multiple export files. Another option if you have the dbspace available is to create a new table with the desired extent sizes and use my dbcopy utility in the same package to copy the data from the original table to the new one, drop the original table and rename the new one back to the old name, then recreate any indexes etc. Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. 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 Fri, May 15, 2009 at 10:42 AM, LARRY SORENSEN <lsorensen25@msn.com>wrote: > I need suggestions. I have a large table with over 400 extents. I would > like > to reorganize it to reduce the extents. I believe with XPS 8.1 on AIX, that > there is still a file limitation of 2GB. IS that correct? If so, that > eliminates unloading the table to disk, dropping and recreating the table? > Does that include UNLOAD TO "filename" SELECT ..... as well? > > Other options, I may have enough space to create a new table in the > database > while the old one exists. Can I just select the data into the new table > from > the old table? I have logging running, and I don't want this logged, or it > may > be a long transaction or have other logging problems. Is there a way around > this? > > Other suggestions? > > Larry > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001636c5b31f1c3b380469f4c3d8
You could use the High Performance Loader to unload /reload via multiple files, or create a type RAW table to transfer into then alter to type STANDARD ( I think all this applies to XPS as well as standard IDS). LARRY SORENSEN wrote: > I need suggestions. I have a large table with over 400 extents. I would like > to reorganize it to reduce the extents. I believe with XPS 8.1 on AIX, that > there is still a file limitation of 2GB. IS that correct? If so, that > eliminates unloading the table to disk, dropping and recreating the table? > Does that include UNLOAD TO "filename" SELECT ..... as well? > > Other options, I may have enough space to create a new table in the database > while the old one exists. Can I just select the data into the new table from > the old table? I have logging running, and I don't want this logged, or it may > be a long transaction or have other logging problems. Is there a way around > this? > > Other suggestions? > > Larry > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > >
Sighhhhhhhhhh. Just imagine if IBM would port XPS to Linux. A few small servers running Linux and filled with disks or attached to a SAN....... The possibilities are endless! Bob ----- Original Message ----- From: "malc_p" <iiug@perrior.net> To: ids@iiug.org Sent: Friday, May 15, 2009 11:17:05 AM GMT -05:00 US/Canada Eastern Subject: Re: XPS 8.1 reorg large table to reduce extents [15790] You could use the High Performance Loader to unload /reload via multiple files, or create a type RAW table to transfer into then alter to type STANDARD ( I think all this applies to XPS as well as standard IDS). LARRY SORENSEN wrote: > I need suggestions. I have a large table with over 400 extents. I would like > to reorganize it to reduce the extents. I believe with XPS 8.1 on AIX, that > there is still a file limitation of 2GB. IS that correct? If so, that > eliminates unloading the table to disk, dropping and recreating the table? > Does that include UNLOAD TO "filename" SELECT ..... as well? > > Other options, I may have enough space to create a new table in the database > while the old one exists. Can I just select the data into the new table from > the old table? I have logging running, and I don't want this logged, or it may > be a long transaction or have other logging problems. Is there a way around > this? > > Other suggestions? > > Larry > > > *******************************************************************************
XPS is where the raw table first appeared.=C2=A0 There is no HPL in XPS, bu= t there is a fast loader/unloader, and it's faster and simpler. = If you have the space, create a raw table with the layout you want, insert = into that select * from the other. This will use light appends to load the = raw table.=C2=A0 To ensure that you get light scans reading the source tabl= e, set isolation to dirty read.=C2=A0 Also ensure that update stats have be= en run (low is fine). Once complete, alter table type as suggest= ed to whatever you want and build whatever indices you need. If = you have to take the data out of the databae, look into the 'external' tabl= e which is how XPS deals with fast load/unload.=C2=A0 I don't recall a 2GB = limit with XPS, but you want multiple unload files anyway, just do the math= and create enough files in the external table so that you don't hit the li= mit + 20% for your math vs XPS math. (Table is 10GB, I'll create=C2=A06 2GB= files in the external table + 1 or 2 for goodluck).=C2=A0 This assumes tha= t my memory serves correctly. 8.1?=C2=A0 That's 10 years old and= there were=C2=A0some quirks with it.=C2=A0 What are your upgrade options?<= br /> j. On May 15, 2009, malc_p <ii= ug@perrior.net> wrote: You could use the High Performance Loader to unload /reload via multipl= e files, or create a type RAW table to transfer into then alter to ty= pe STANDARD ( I think all this applies to XPS as well as standard IDS= ). LARRY SORENSEN wrote: > I need suggestions. I have = a large table with over 400 extents. I would like > to reorganize = it to reduce the extents. I believe with XPS 8.1 on AIX, that > th= ere is still a file limitation of 2GB. IS that correct? If so, that &= gt; eliminates unloading the table to disk, dropping and recreating the tab= le? > Does that include UNLOAD TO "filename" SELECT ..... as well?= > > Other options, I may have enough space to create a n= ew table in the database > while the old one exists. Can I just se= lect the data into the new table from > the old table? I have logg= ing running, and I don't want this logged, or it may > be a = long transaction or have other logging problems. Is there a way around > this? > > Other suggestions? > >= Larry > > > *****************************= ************************************************** > Forum Note: U= se "Reply" to post a response in the discussion forum. > >= ; > ********************************************= *********************************** Forum Note: Use "Reply" to post a= response in the discussion forum.
Shhh, don't tell anyone.=C2=A0 XPS was ported to Linux by Informix. j. On May 15, 2009, rroussey@comcast.net= <rroussey@comcast.net> wrote: Sighhhhhhhhhh. Just imagine if IBM would port XPS to Linux. = A few small servers running Linux and filled with disks or attached t= o a SAN....... The possibilities are endless! Bob <= br />----- Original Message ----- From: "malc_p" <[1]iiug@perrior.ne= t> To: [2]ids@iiug.org Sent: Friday, May 15, 2009 11:17:05= AM GMT -05:00 US/Canada Eastern Subject: Re: XPS 8.1 reorg large tab= le to reduce extents [15790] You could use the High Performance= Loader to unload /reload via multiple files, or create a type RAW ta= ble to transfer into then alter to type STANDARD ( I think all this a= pplies to XPS as well as standard IDS). LARRY SORENSEN wrote: <= br />> I need suggestions. I have a large table with over 400 extents. I= would like > to reorganize it to reduce the extents. I believe wi= th XPS 8.1 on AIX, that > there is still a file limitation of 2GB.= IS that correct? If so, that > eliminates unloading the table to = disk, dropping and recreating the table? > Does that include UNLOA= D TO "filename" SELECT ..... as well? > > Other options, = I may have enough space to create a new table in the database > wh= ile the old one exists. Can I just select the data into the new table from = > the old table? I have logging running, and I don't want this log= ged, or it may > be a long transaction or have other logging= problems. Is there a way around > this? > > Oth= er suggestions? > > Larry > > >= ; *************************************************************= ****************** **************************************= ***************************************** Forum Note: Use "Reply" to = post a response in the discussion forum. References 1. file://localhost/tmp/3D"mailt= 2. 3D"mailto:ids@iiug.org"
I hate webmail interfaces which like to introduce random characters into my mail. j. On May 15, 2009, jack.parker4@verizon.net <jack.parker4@verizon.net> wrote: Shhh, don't tell anyone.=C2=A0 XPS was ported to Linux by Informix. j. On May 15, 2009, rroussey@comcast.net= <rroussey@comcast.net> wrote: Sighhhhhhhhhh. Just imagine if IBM would port XPS to Linux. = A few small servers running Linux and filled with disks or attached t= o a SAN....... The possibilities are endless! Bob <= br />----- Original Message ----- From: "malc_p" <[1]iiug@perrior.ne= t> To: [2]ids@iiug.org Sent: Friday, May 15, 2009 11:17:05= AM GMT -05:00 US/Canada Eastern Subject: Re: XPS 8.1 reorg large tab= le to reduce extents [15790] You could use the High Performance= Loader to unload /reload via multiple files, or create a type RAW ta= ble to transfer into then alter to type STANDARD ( I think all this a= pplies to XPS as well as standard IDS). LARRY SORENSEN wrote: <= br />> I need suggestions. I have a large table with over 400 extents. I= would like > to reorganize it to reduce the extents. I believe wi= th XPS 8.1 on AIX, that > there is still a file limitation of 2GB.= IS that correct? If so, that > eliminates unloading the table to = disk, dropping and recreating the table? > Does that include UNLOA= D TO "filename" SELECT ..... as well? > > Other options, = I may have enough space to create a new table in the database > wh= ile the old one exists. Can I just select the data into the new table from = > the old table? I have logging running, and I don't want this log= ged, or it may > be a long transaction or have other logging= problems. Is there a way around > this? > > Oth= er suggestions? > > Larry > > >= ; *************************************************************= ****************** **************************************= ***************************************** Forum Note: Use "Reply" to = post a response in the discussion forum. References 1. file://localhost/tmp/3D"mailt= 2. 3D"mailto:ids@iiug.org" ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.