FW: XPS - Adding new columns to exiting tables
Larry asked how to estimate the time to ALTER a large table (~20M rows) in XPS 8.2 on AIX to add two columns. Art Kagel explained XPS has no in-place alter, so the whole table is rewritten: add both columns in one ALTER, or manually create a new table, copy rows, then rename/drop; either way you need space for two copies, unless you UNLOAD, drop, recreate and reload with DBLOAD/the XPS loader (needing filesystem space instead). For sizing the existing table, John Miller pointed to onutil's CHECK INFO TABLE ... DISPLAY (and CHECK TABLE ALLOCATION INFO for detail); 'pages used' counts data, overhead and index pages holding something, not fully empty pages.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Is there any way to estimate how long it will take to add two new columns to a table with over 1,200,000 columns? No data will be added for the columns during creation. XPS 8.2, AIX DECIMAL(9,2) CHAR(1)
XPS does not support in-place alters so the entire table will have to be rewritten to effect the ALTER. To minimize the runtime, add both columns in a single ALTER statement. You can guestimate the time by making a copy of the table manually, or, better yet, just manually create a new table with the additional columns and copy the original rows into the new table your self, then rename the old table, rename the new table, and drop the old table when you don't need it any longer. 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 Mon, Nov 2, 2009 at 10:47 AM, LARRY SORENSEN <lsorensen25@msn.com> wrote: > Is there any way to estimate how long it will take to add two new columns > to a > table with over 1,200,000 columns? No data will be added for the columns > during creation. > > XPS 8.2, AIX > > DECIMAL(9,2) > CHAR(1) > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --000e0ce00996a56c4d047765db89
Thank you for the information. So, it looks like either way I need to make sure that there is enough space to duplicate the table? Larry > To: ids@iiug.org > From: art.kagel@gmail.com > Subject: Re: FW: XPS - Adding new columns to exiting tables [17866] > Date: Mon, 2 Nov 2009 11:27:53 -0500 > > XPS does not support in-place alters so the entire table will have to be > rewritten to effect the ALTER. To minimize the runtime, add both columns in > a single ALTER statement. You can guestimate the time by making a copy of > the table manually, or, better yet, just manually create a new table with > the additional columns and copy the original rows into the new table your > self, then rename the old table, rename the new table, and drop the old > table when you don't need it any longer. > > 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 Mon, Nov 2, 2009 at 10:47 AM, LARRY SORENSEN <lsorensen25@msn.com> wrote: > > > Is there any way to estimate how long it will take to add two new columns > > to a > > table with over 1,200,000 columns? No data will be added for the columns > > during creation. > > > > XPS 8.2, AIX > > > > DECIMAL(9,2) > > CHAR(1) > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --000e0ce00996a56c4d047765db89 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
Unless you UNLOAD the table, drop it, create the new table empty, and use DBLOAD or the XPS data load utility to load the data back into the new table. Then you won't need dbspace for two copies, but you will need disk space in a filesystem for the unloaded data file and it may take slightly longer than the ALTER would (or it could run a bit faster - hard to guess). 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 Mon, Nov 2, 2009 at 11:49 AM, LARRY SORENSEN <lsorensen25@msn.com> wrote: > Thank you for the information. So, it looks like either way I need to make > sure that there is enough space to duplicate the table? > > Larry > > > To: ids@iiug.org > > From: art.kagel@gmail.com > > Subject: Re: FW: XPS - Adding new columns to exiting tables [17866] > > Date: Mon, 2 Nov 2009 11:27:53 -0500 > > > > XPS does not support in-place alters so the entire table will have to be > > rewritten to effect the ALTER. To minimize the runtime, add both columns > in > > a single ALTER statement. You can guestimate the time by making a copy of > > the table manually, or, better yet, just manually create a new table with > > the additional columns and copy the original rows into the new table your > > self, then rename the old table, rename the new table, and drop the old > > table when you don't need it any longer. > > > > 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 Mon, Nov 2, 2009 at 10:47 AM, LARRY SORENSEN <lsorensen25@msn.com> > wrote: > > > > > Is there any way to estimate how long it will take to add two new > columns > > > to a > > > table with over 1,200,000 columns? No data will be added for the > columns > > > during creation. > > > > > > XPS 8.2, AIX > > > > > > DECIMAL(9,2) > > > CHAR(1) > > > > > > > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > --000e0ce00996a56c4d047765db89 > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --00151747b6908e69c90477669565
Along with this, how can I figure out how much space the current table is taking? I have seen some reference to "onutil DISPLAY TABLE", but I can't find anything specific no how to use it. No documentation that I find is very specific. I have also seen some other references to using system tables, but those are not specific either. > To: ids@iiug.org > From: art.kagel@gmail.com > Subject: Re: XPS - Adding new columns to exiting tables [17868] > Date: Mon, 2 Nov 2009 12:19:51 -0500 > > Unless you UNLOAD the table, drop it, create the new table empty, and use > DBLOAD or the XPS data load utility to load the data back into the new > table. Then you won't need dbspace for two copies, but you will need disk > space in a filesystem for the unloaded data file and it may take slightly > longer than the ALTER would (or it could run a bit faster - hard to guess). > > 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 Mon, Nov 2, 2009 at 11:49 AM, LARRY SORENSEN <lsorensen25@msn.com> wrote: > > > Thank you for the information. So, it looks like either way I need to make > > sure that there is enough space to duplicate the table? > > > > Larry > > > > > To: ids@iiug.org > > > From: art.kagel@gmail.com > > > Subject: Re: FW: XPS - Adding new columns to exiting tables [17866] > > > Date: Mon, 2 Nov 2009 11:27:53 -0500 > > > > > > XPS does not support in-place alters so the entire table will have to be > > > rewritten to effect the ALTER. To minimize the runtime, add both columns > > in > > > a single ALTER statement. You can guestimate the time by making a copy of > > > the table manually, or, better yet, just manually create a new table with > > > the additional columns and copy the original rows into the new table your > > > self, then rename the old table, rename the new table, and drop the old > > > table when you don't need it any longer. > > > > > > 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 Mon, Nov 2, 2009 at 10:47 AM, LARRY SORENSEN <lsorensen25@msn.com> > > wrote: > > > > > > > Is there any way to estimate how long it will take to add two new > > columns > > > > to a > > > > table with over 1,200,000 columns? No data will be added for the > > columns > > > > during creation. > > > > > > > > XPS 8.2, AIX > > > > > > > > DECIMAL(9,2) > > > > CHAR(1) > > > > > > > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > > > > > --000e0ce00996a56c4d047765db89 > > > > > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --00151747b6908e69c90477669565 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
I would check the following documentation. It appears that both the XPS administrators guide and administrator reference have lots of information about onutil. I believe you are looking for CHECK INFO TABLE mytab DISPLAY http://publib.boulder.ibm.com/epubs/html/25122310/25122310tfrm.htm OR http://publib.boulder.ibm.com/epubs/html/25122320/25122320tfrm.htm John F. Miller III STSM, Support Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 11/03/2009 09:18:18 AM: > [image removed] > > RE: XPS - Adding new columns to exiting tables [17890] > > LARRY SORENSEN > > to: > > ids > > 11/03/2009 09:19 AM > > Sent by: > > ids-bounces@iiug.org > > Please respond to ids > > Along with this, how can I figure out how much space the current table is > taking? I have seen some reference to "onutil DISPLAY TABLE", but I > can't find > anything specific no how to use it. No documentation that I find is very > specific. I have also seen some other references to using system tables, but > those are not specific either. > > > To: ids@iiug.org > > From: art.kagel@gmail.com > > Subject: Re: XPS - Adding new columns to exiting tables [17868] > > Date: Mon, 2 Nov 2009 12:19:51 -0500 > > > > Unless you UNLOAD the table, drop it, create the new table empty, and use > > DBLOAD or the XPS data load utility to load the data back into the new > > table. Then you won't need dbspace for two copies, but you will need disk > > space in a filesystem for the unloaded data file and it may take slightly > > longer than the ALTER would (or it could run a bit faster - hard to guess). > > > > 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 Mon, Nov 2, 2009 at 11:49 AM, LARRY SORENSEN > <lsorensen25@msn.com> wrote: > > > > > Thank you for the information. So, it looks like either way I > need to make > > > sure that there is enough space to duplicate the table? > > > > > > Larry > > > > > > > To: ids@iiug.org > > > > From: art.kagel@gmail.com > > > > Subject: Re: FW: XPS - Adding new columns to exiting tables [17866] > > > > Date: Mon, 2 Nov 2009 11:27:53 -0500 > > > > > > > > XPS does not support in-place alters so the entire table will > have to be > > > > rewritten to effect the ALTER. To minimize the runtime, add > both columns > > > in > > > > a single ALTER statement. You can guestimate the time by making a copy > of > > > > the table manually, or, better yet, just manually create a new table > with > > > > the additional columns and copy the original rows into the new table > your > > > > self, then rename the old table, rename the new table, and drop the old > > > > table when you don't need it any longer. > > > > > > > > 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 Mon, Nov 2, 2009 at 10:47 AM, LARRY SORENSEN <lsorensen25@msn.com> > > > wrote: > > > > > > > > > Is there any way to estimate how long it will take to add two new > > > columns > > > > > to a > > > > > table with over 1,200,000 columns? No data will be added for the > > > columns > > > > > during creation. > > > > > > > > > > XPS 8.2, AIX > > > > > > > > > > DECIMAL(9,2) > > > > > CHAR(1) > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > > > > > > > > > --000e0ce00996a56c4d047765db89 > > > > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > --00151747b6908e69c90477669565 > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
John, Thank you for pointing me in the right direction with the links below. I still have a question on the results, however. Contents of dwsales:informix.sales_wkly,dcsales3.1-T dcsales3.1 (file /tmp/onc_dump.1.14552.44bb8217) Physical Address 4 Creation date 9/19/2009 17:55:23 TBLspace Flags 8801 Page Locking TBLspace use 4 bit bit-maps Maximum row size 80 Number of special columns 0 Number of keys 0 Number of extents 34 Current serial value 1 First extent size 4 Next extent size 16 Number of pages allocated 433552 Number of pages used 433549 Number of data pages 433476 Number of rows 20805927 Partition partnum 262158 Partition lockid 10001 For "Number of pages used", does this mean that the pages actively have data in them, or that they may have had data in them at one time, but may or may not currently have data in them (still allocated to the tablespace)? Larry > To: ids@iiug.org > From: miller3@us.ibm.com > Subject: RE: XPS - Adding new columns to exiting tables [17892] > Date: Tue, 3 Nov 2009 13:58:36 -0500 > > I would check the following documentation. It appears that both > the XPS administrators guide and administrator reference have > lots of information about onutil. I believe you are looking for > > CHECK INFO TABLE mytab DISPLAY > > http://publib.boulder.ibm.com/epubs/html/25122310/25122310tfrm.htm > > OR > > http://publib.boulder.ibm.com/epubs/html/25122320/25122320tfrm.htm > > John F. Miller III > STSM, Support Architect > miller3@us.ibm.com > 503-578-5645 > IBM Informix Dynamic Server (IDS) > > ids-bounces@iiug.org wrote on 11/03/2009 09:18:18 AM: > > > [image removed] > > > > RE: XPS - Adding new columns to exiting tables [17890] > > > > LARRY SORENSEN > > > > to: > > > > ids > > > > 11/03/2009 09:19 AM > > > > Sent by: > > > > ids-bounces@iiug.org > > > > Please respond to ids > > > > Along with this, how can I figure out how much space the current table is > > > taking? I have seen some reference to "onutil DISPLAY TABLE", but I > > can't find > > anything specific no how to use it. No documentation that I find is very > > specific. I have also seen some other references to using system tables, > but > > those are not specific either. > > > > > To: ids@iiug.org > > > From: art.kagel@gmail.com > > > Subject: Re: XPS - Adding new columns to exiting tables [17868] > > > Date: Mon, 2 Nov 2009 12:19:51 -0500 > > > > > > Unless you UNLOAD the table, drop it, create the new table empty, and > use > > > DBLOAD or the XPS data load utility to load the data back into the new > > > table. Then you won't need dbspace for two copies, but you will need > disk > > > space in a filesystem for the unloaded data file and it may take > slightly > > > longer than the ALTER would (or it could run a bit faster - hard to > guess). > > > > > > 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 Mon, Nov 2, 2009 at 11:49 AM, LARRY SORENSEN > > <lsorensen25@msn.com> wrote: > > > > > > > Thank you for the information. So, it looks like either way I > > need to make > > > > sure that there is enough space to duplicate the table? > > > > > > > > Larry > > > > > > > > > To: ids@iiug.org > > > > > From: art.kagel@gmail.com > > > > > Subject: Re: FW: XPS - Adding new columns to exiting tables [17866] > > > > > > Date: Mon, 2 Nov 2009 11:27:53 -0500 > > > > > > > > > > XPS does not support in-place alters so the entire table will > > have to be > > > > > rewritten to effect the ALTER. To minimize the runtime, add > > both columns > > > > in > > > > > a single ALTER statement. You can guestimate the time by making a > copy > > of > > > > > the table manually, or, better yet, just manually create a new > table > > with > > > > > the additional columns and copy the original rows into the new > table > > your > > > > > self, then rename the old table, rename the new table, and drop the > old > > > > > table when you don't need it any longer. > > > > > > > > > > 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 Mon, Nov 2, 2009 at 10:47 AM, LARRY SORENSEN > <lsorensen25@msn.com> > > > > wrote: > > > > > > > > > > > Is there any way to estimate how long it will take to add two new > > > > > columns > > > > > > to a > > > > > > table with over 1,200,000 columns? No data will be added for the > > > > columns > > > > > > during creation. > > > > > > > > > > > > XPS 8.2, AIX > > > > > > > > > > > > DECIMAL(9,2) > > > > > > CHAR(1) > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > > > > > Forum Note: Use "Reply" to post a response in the discussion > forum. > > > > > > > > > > > > > > > > > > > > > > --000e0ce00996a56c4d047765db89 > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > > > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > > > > > --00151747b6908e69c90477669565 > > > > > > > > > > > > > *********
Pages used includes any overhead pages (like bitmap pages), attached index pages with keys on them, and data pages with at least one row on them. Completely empty pages are marked as free in their header and on the corresponding bitmap page and so are not counted as used. 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 Wed, Nov 4, 2009 at 12:34 PM, LARRY SORENSEN <lsorensen25@msn.com> wrote: > John, > > Thank you for pointing me in the right direction with the links below. I > still > have a question on the results, however. > > Contents > of > dwsales:informix.sales_wkly,dcsales3.1-T > dcsales3.1 > > (file > /tmp/onc_dump.1.14552.44bb8217) > > Physical > Address > 4 > > Creation > date > 9/19/2009 > 17:55:23 > > TBLspace > Flags > 8801 > Page > Locking > > TBLspace > use > 4 > bit > bit-maps > > Maximum > row > size > 80 > > Number > of > special > columns > 0 > > Number > of > keys > 0 > > Number > of > extents > 34 > > Current > serial > value > 1 > > First > extent > size > 4 > > Next > extent > size > 16 > > Number > of > pages > allocated > 433552 > > Number > of > pages > used > 433549 > > Number > of > data > pages > 433476 > > Number > of > rows > 20805927 > > Partition > partnum > 262158 > > Partition > lockid > 10001 > > For "Number of pages used", does this mean that the pages actively have > data > in them, or that they may have had data in them at one time, but may or may > not currently have data in them (still allocated to the tablespace)? > > Larry > > > To: ids@iiug.org > > From: miller3@us.ibm.com > > Subject: RE: XPS - Adding new columns to exiting tables [17892] > > Date: Tue, 3 Nov 2009 13:58:36 -0500 > > > > I would check the following documentation. It appears that both > > the XPS administrators guide and administrator reference have > > lots of information about onutil. I believe you are looking for > > > > CHECK INFO TABLE mytab DISPLAY > > > > http://publib.boulder.ibm.com/epubs/html/25122310/25122310tfrm.htm > > > > OR > > > > http://publib.boulder.ibm.com/epubs/html/25122320/25122320tfrm.htm > > > > John F. Miller III > > STSM, Support Architect > > miller3@us.ibm.com > > 503-578-5645 > > IBM Informix Dynamic Server (IDS) > > > > ids-bounces@iiug.org wrote on 11/03/2009 09:18:18 AM: > > > > > [image removed] > > > > > > RE: XPS - Adding new columns to exiting tables [17890] > > > > > > LARRY SORENSEN > > > > > > to: > > > > > > ids > > > > > > 11/03/2009 09:19 AM > > > > > > Sent by: > > > > > > ids-bounces@iiug.org > > > > > > Please respond to ids > > > > > > Along with this, how can I figure out how much space the current table > is > > > > > taking? I have seen some reference to "onutil DISPLAY TABLE", but I > > > can't find > > > anything specific no how to use it. No documentation that I find is > very > > > specific. I have also seen some other references to using system > tables, > > but > > > those are not specific either. > > > > > > > To: ids@iiug.org > > > > From: art.kagel@gmail.com > > > > Subject: Re: XPS - Adding new columns to exiting tables [17868] > > > > Date: Mon, 2 Nov 2009 12:19:51 -0500 > > > > > > > > Unless you UNLOAD the table, drop it, create the new table empty, and > > use > > > > DBLOAD or the XPS data load utility to load the data back into the > new > > > > table. Then you won't need dbspace for two copies, but you will need > > disk > > > > space in a filesystem for the unloaded data file and it may take > > slightly > > > > longer than the ALTER would (or it could run a bit faster - hard to > > guess). > > > > > > > > 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 Mon, Nov 2, 2009 at 11:49 AM, LARRY SORENSEN > > > <lsorensen25@msn.com> wrote: > > > > > > > > > Thank you for the information. So, it looks like either way I > > > need to make > > > > > sure that there is enough space to duplicate the table? > > > > > > > > > > Larry > > > > > > > > > > > To: ids@iiug.org > > > > > > From: art.kagel@gmail.com > > > > > > Subject: Re: FW: XPS - Adding new columns to exiting tables > [17866] > > > > > > > > Date: Mon, 2 Nov 2009 11:27:53 -0500 > > > > > > > > > > > > XPS does not support in-place alters so the entire table will > > > have to be > > > > > > rewritten to effect the ALTER. To minimize the runtime, add > > > both columns > > > > > in > > > > > > a single ALTER statement. You can guestimate the time by making a > > copy > > > of > > > > > > the table manually, or, better yet, just manually create a new > > table > > > with > > > > > > the additional columns and copy the original rows into the new > > table > > > your > > > > > > self, then rename the old table, rename the new table, and drop > the > > old > > > > > > table when you don't need it any longer. > > > > > > > > > > > > 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 Mon, Nov 2, 2009 at 10:47 AM, LARRY SORENSEN > > <lsorensen25@msn.com> > > > > > wrote: > > > > > > > > > > > > > Is there any way to estimate how long it will take to add two > new > > > > > > > colu
Number of pages used means that the table/fragment is using that number= of pages to store the table. Not all pages in a table are used to store d= ata. It could be that they did have data at one time and do not now. If you want a m= ore detail information then run the ALLOCATION option, but it will take longer to = run. CHECK TABLE ALLOCATION INFO John F. Miller III STSM, Support Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) = From: "LARRY SORENSEN" <lsorensen25@msn.com> = = To: ids@iiug.org = = Date: 11/04/2009 09:36 AM = = Subject: RE: XPS - Adding new columns to exiting tables [17905] = = Sent by: ids-bounces@iiug.org = = John, Thank you for pointing me in the right direction with the links below. = I still have a question on the results, however. Contents of dwsales:informix.sales_wkly,dcsales3.1-T dcsales3.1 (file /tmp/onc_dump.1.14552.44bb8217) Physical Address 4 Creation date 9/19/2009 17:55:23 TBLspace Flags 8801 Page Locking TBLspace use 4 bit bit-maps Maximum row size 80 Number of special columns 0 Number of keys 0 Number of extents 34 Current serial value 1 First extent size 4 Next extent size 16 Number of pages allocated 433552 Number of pages used 433549 Number of data pages 433476 Number of rows 20805927 Partition partnum 262158 Partition lockid 10001 For "Number of pages used", does this mean that the pages actively have= data in them, or that they may have had data in them at one time, but may or= may not currently have data in them (still allocated to the tablespace)? Larry > To: ids@iiug.org > From: miller3@us.ibm.com > Subject: RE: XPS - Adding new columns to exiting tables [17892] > Date: Tue, 3 Nov 2009 13:58:36 -0500 > > I would check the following documentation. It appears that both > the XPS administrators guide and administrator reference have > lots of information about onutil. I believe you are looking for > > CHECK INFO TABLE mytab DISPLAY > > http://publib.boulder.ibm.com/epubs/html/25122310/25122310tfrm.htm > > OR > > http://publib.boulder.ibm.com/epubs/html/25122320/25122320tfrm.htm > > John F. Miller III > STSM, Support Architect > miller3@us.ibm.com > 503-578-5645 > IBM Informix Dynamic Server (IDS) > > ids-bounces@iiug.org wrote on 11/03/2009 09:18:18 AM: > > > [image removed] > > > > RE: XPS - Adding new columns to exiting tables [17890] > > > > LARRY SORENSEN > > > > to: > > > > ids > > > > 11/03/2009 09:19 AM > > > > Sent by: > > > > ids-bounces@iiug.org > > > > Please respond to ids > > > > Along with this, how can I figure out how much space the current ta= ble is > > > taking? I have seen some reference to "onutil DISPLAY TABLE", but I= > > can't find > > anything specific no how to use it. No documentation that I find is= very > > specific. I have also seen some other references to using system tables, > but > > those are not specific either. > > > > > To: ids@iiug.org > > > From: art.kagel@gmail.com > > > Subject: Re: XPS - Adding new columns to exiting tables [17868] > > > Date: Mon, 2 Nov 2009 12:19:51 -0500 > > > > > > Unless you UNLOAD the table, drop it, create the new table empty,= and > use > > > DBLOAD or the XPS data load utility to load the data back into th= e new > > > table. Then you won't need dbspace for two copies, but you will n= eed > disk > > > space in a filesystem for the unloaded data file and it may take > slightly > > > longer than the ALTER would (or it could run a bit faster - hard = to > guess). > > > > > > 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. Neith= er do > > > those opinions reflect those of other individuals affiliated with= > > any entity > > > with which I am affiliated nor those of the entities themselves. > > > > > > On Mon, Nov 2, 2009 at 11:49 AM, LARRY SORENSEN > > <lsorensen25@msn.com> wrote: > > > > > > > Thank you for the information. So, it looks like either way I > > need to make > > > > sure that there is enough space to duplicate the table? > > > > > > > > Larry > > > > > > > > > To: ids@iiug.org > > > > > From: art.kagel@gmail.com > > > > > Subject: Re: FW: XPS - Adding new columns to exiting tables [17866] > > > > > > Date: Mon, 2 Nov 2009 11:27:53 -0500 > > > > > > > > > > XPS does not support in-place alters so the entire table will= > > have to be > > > > > rewritten to effect the ALTER. To minimize the runtime, add > > both columns > > > > in > > > > > a single ALTER statement. You can guestimate the time by maki= ng a > copy > > of > > > > > the table manually, or, better yet, just manually create a ne= w > table > > with > > > > > the additional columns and copy the original rows into the ne= w > table > > your > > > > > self, then rename the old table, rename the new table, and dr= op the > old > > > > > table when you don't need it any longer. > > > > > > > > > > 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 othe= r > > > > 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 themselv= es. > > > > > > > > > > On Mon, Nov 2, 2009 at 10:47 AM, LARRY SORENSEN > <lsorensen25@msn.com> > > > > wrote: > > > > > > > > > > > Is there any way to estimate how long it will take to add t= wo new > > > > > columns > > > > > > to a > > > > > > table with over 1,200,000 columns? No data will be added fo= r the > > > > columns > > > > > > during creation. > > > > > > > > > > > > XPS 8.2, AIX > > > > > > > > > > > > DECIMAL(9,2) > > > > > > CHAR(1) > > > > > > > > > > > >