consolidating dbspaces

HP UX B.11.31 U 9000/800 IDS 11.70.FC7GE Good Day, I want to consolidate a number of 2gb chunks into a single chunk. Can anyone advise me on how to do that ? Thanks

Create a new dbspace with your new large chunks and then move the data into it. For smaler tables (< 500 Mb) ALTER FRAGMENT ON TABLE 'tabname' INIT IN 'new_dbspace' for larger you may need to unload to filestore and then reload, or unload/load to and from a pipe. Watch for large transactions and all the other things that can trip this up. There is no automtic way to merge a number of small chunks into a large one, it is too much a fundamental operation on the engine structure. Keith On 17 September 2014 09:53, FLIP VAN WYNGAARDT <flipv@raf.co.za> wrote: > HP UX B.11.31 U 9000/800 > IDS 11.70.FC7GE > > Good Day, > > I want to consolidate a number of 2gb chunks into a single chunk. Can > anyone > advise me on how to do that ? > > Thanks > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --90e6ba6e8fb83170930503424c91

The only way: create the new ones, move the objects that are on the current ones to the new ones and drop the 2GB. Recent versions may allow you to extend a chunk, but at most you could extend one and move the objects from the others... The way to "move" the objects really depend on your data and if you have or not maintenance window... If you have just create new tables (raw if you're not using HDR) without indexes, do an INSERT INTO ... SELECT FROM... then create indexes, drop the old table, and change the name. If you need to do it "online" than there are more complex options... You may start with triggers from source to destination.... (INSERT/UPDATE/DELETEs) and move copy the records from the old to the new... You could rename the existing table and create a view with the original name that makes a union of both the new and old and create some scripts/programs to really move the records. But if you have DIRTY READERS they'd see duplicate rows... Wel... these are generic ideas... Regards On Sep 17, 2014 9:53 AM, "FLIP VAN WYNGAARDT" <flipv@raf.co.za> wrote: > HP UX B.11.31 U 9000/800 > IDS 11.70.FC7GE > > Good Day, > > I want to consolidate a number of 2gb chunks into a single chunk. Can > anyone > advise me on how to do that ? > > Thanks > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11c3b54223a64d05034350ec

Why UNLOAD/LOAD?! 100% agreement with all the rest On Wed, Sep 17, 2014 at 1:49 PM, Keith Simmons <smiley73@gmail.com> wrote: > Create a new dbspace with your new large chunks and then move the data into > it. > For smaler tables (< 500 Mb) ALTER FRAGMENT ON TABLE 'tabname' INIT IN > 'new_dbspace' > for larger you may need to unload to filestore and then reload, or > unload/load to and from a pipe. > Watch for large transactions and all the other things that can trip this > up. > There is no automtic way to merge a number of small chunks into a large > one, it is too much a fundamental operation on the engine structure. > > Keith > > On 17 September 2014 09:53, FLIP VAN WYNGAARDT <flipv@raf.co.za> wrote: > > > HP UX B.11.31 U 9000/800 > > IDS 11.70.FC7GE > > > > Good Day, > > > > I want to consolidate a number of 2gb chunks into a single chunk. Can > > anyone > > advise me on how to do that ? > > > > Thanks > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --90e6ba6e8fb83170930503424c91 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --047d7b10c847876b84050343559f

Watch out for referential integrity on the table. From: "Fernando Nunes" <domusonline@gmail.com> To: ids@iiug.org Date: 09/17/2014 09:04 AM Subject: Re: consolidating dbspaces [33802] Sent by: ids-bounces@iiug.org Why UNLOAD/LOAD?! 100% agreement with all the rest On Wed, Sep 17, 2014 at 1:49 PM, Keith Simmons <smiley73@gmail.com> wro= te: > Create a new dbspace with your new large chunks and then move the dat= a into > it. > For smaler tables (< 500 Mb) ALTER FRAGMENT ON TABLE 'tabname' INIT I= N > 'new_dbspace' > for larger you may need to unload to filestore and then reload, or > unload/load to and from a pipe. > Watch for large transactions and all the other things that can trip t= his > up. > There is no automtic way to merge a number of small chunks into a lar= ge > one, it is too much a fundamental operation on the engine structure. > > Keith > > On 17 September 2014 09:53, FLIP VAN WYNGAARDT <flipv@raf.co.za> wrot= e: > > > HP UX B.11.31 U 9000/800 > > IDS 11.70.FC7GE > > > > Good Day, > > > > I want to consolidate a number of 2gb chunks into a single chunk. C= an > > anyone > > advise me on how to do that ? > > > > Thanks > > > > > > > > > > ***********************************************************************= ******** > > Forum Note: Use "Reply" to post a response in the discussion forum.= > > > > > > --90e6ba6e8fb83170930503424c91 > > > > ***********************************************************************= ******** > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --047d7b10c847876b84050343559f ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. =

It does tend to be my point of first call for data transfer. UNLOAD TO 'pipe-file' SELECT * FROM 'table' in a dbaccess session and adbload script in a second session (loading from the pipe and transacting every 10,000 records) is quick and simple to set up and reasonably quick to run. It's only if this becomes problematic do I start to look at RAW or other methods that are more 'convoluted'. It comes down to what one uses most often and is familiar with, also if there is down-time and how many other people are still trying to access a 24 x 7 system :-(( Keith On 17 September 2014 15:03, Fernando Nunes <domusonline@gmail.com> wrote: > Why UNLOAD/LOAD?! 100% agreement with all the rest > > On Wed, Sep 17, 2014 at 1:49 PM, Keith Simmons <smiley73@gmail.com> wrote: > > > Create a new dbspace with your new large chunks and then move the data > into > > it. > > For smaler tables (< 500 Mb) ALTER FRAGMENT ON TABLE 'tabname' INIT IN > > 'new_dbspace' > > for larger you may need to unload to filestore and then reload, or > > unload/load to and from a pipe. > > Watch for large transactions and all the other things that can trip this > > up. > > There is no automtic way to merge a number of small chunks into a large > > one, it is too much a fundamental operation on the engine structure. > > > > Keith > > > > On 17 September 2014 09:53, FLIP VAN WYNGAARDT <flipv@raf.co.za> wrote: > > > > > HP UX B.11.31 U 9000/800 > > > IDS 11.70.FC7GE > > > > > > Good Day, > > > > > > I want to consolidate a number of 2gb chunks into a single chunk. Can > > > anyone > > > advise me on how to do that ? > > > > > > Thanks > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > --90e6ba6e8fb83170930503424c91 > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > > --047d7b10c847876b84050343559f > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --485b397dd1f3dd59700503443e3f

FWIW, external tables are significantly faster than UNLOAD since the data doesn't have to be transferred from the server to dbaccess before being written out. Art Art S. Kagel, Principal Consultant ASK Database Management Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on 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 Wed, Sep 17, 2014 at 11:08 AM, Keith Simmons <smiley73@gmail.com> wrote: > It does tend to be my point of first call for data transfer. > UNLOAD TO 'pipe-file' SELECT * FROM 'table' in a dbaccess session and a> dbload script in a second session (loading from the pipe and transacting > every 10,000 records) is quick and simple to set up and reasonably quick to > run. It's only if this becomes problematic do I start to look at RAW or > other methods that are more 'convoluted'. > It comes down to what one uses most often and is familiar with, also if > there is down-time and how many other people are still trying to access a > 24 x 7 system :-(( > Keith > > On 17 September 2014 15:03, Fernando Nunes <domusonline@gmail.com> wrote: > > > Why UNLOAD/LOAD?! 100% agreement with all the rest > > > > On Wed, Sep 17, 2014 at 1:49 PM, Keith Simmons <smiley73@gmail.com> > wrote: > > > > > Create a new dbspace with your new large chunks and then move the data > > into > > > it. > > > For smaler tables (< 500 Mb) ALTER FRAGMENT ON TABLE 'tabname' INIT IN > > > 'new_dbspace' > > > for larger you may need to unload to filestore and then reload, or > > > unload/load to and from a pipe. > > > Watch for large transactions and all the other things that can trip > this > > > up. > > > There is no automtic way to merge a number of small chunks into a large > > > one, it is too much a fundamental operation on the engine structure. > > > > > > Keith > > > > > > On 17 September 2014 09:53, FLIP VAN WYNGAARDT <flipv@raf.co.za> > wrote: > > > > > > > HP UX B.11.31 U 9000/800 > > > > IDS 11.70.FC7GE > > > > > > > > Good Day, > > > > > > > > I want to consolidate a number of 2gb chunks into a single chunk. Can > > > > anyone > > > > advise me on how to do that ? > > > > > > > > Thanks > > > > > > > > > > > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > > > > > --90e6ba6e8fb83170930503424c91 > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > -- > > Fernando Nunes > > Portugal > > > > http://informix-technology.blogspot.com > > My email works... but I don't check it frequently... > > > > --047d7b10c847876b84050343559f > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --485b397dd1f3dd59700503443e3f > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11340f129af0b60503445bf9

John Miller has a good example of this on his website/blog thing Cheers Paul > FWIW, external tables are significantly faster than UNLOAD since the data > doesn't have to be transferred from the server to dbaccess before being > written out. > > Art > > Art S. Kagel, Principal Consultant > ASK Database Management > > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on 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 Wed, Sep 17, 2014 at 11:08 AM, Keith Simmons <smiley73@gmail.com> > wrote: > >> It does tend to be my point of first call for data transfer. >> UNLOAD TO 'pipe-file' SELECT * FROM 'table' in a dbaccess session and a>> dbload script in a second session (loading from the pipe and transacting >> every 10,000 records) is quick and simple to set up and reasonably quick >> to >> run. It's only if this becomes problematic do I start to look at RAW or >> other methods that are more 'convoluted'. >> It comes down to what one uses most often and is familiar with, also if >> there is down-time and how many other people are still trying to access >> a >> 24 x 7 system :-(( >> Keith >> >> On 17 September 2014 15:03, Fernando Nunes <domusonline@gmail.com> >> wrote: >> >> > Why UNLOAD/LOAD?! 100% agreement with all the rest >> > >> > On Wed, Sep 17, 2014 at 1:49 PM, Keith Simmons <smiley73@gmail.com> >> wrote: >> > >> > > Create a new dbspace with your new large chunks and then move the >> data >> > into >> > > it. >> > > For smaler tables (< 500 Mb) ALTER FRAGMENT ON TABLE 'tabname' INIT >> IN >> > > 'new_dbspace' >> > > for larger you may need to unload to filestore and then reload, or >> > > unload/load to and from a pipe. >> > > Watch for large transactions and all the other things that can trip >> this >> > > up. >> > > There is no automtic way to merge a number of small chunks into a >> large >> > > one, it is too much a fundamental operation on the engine structure. >> > > >> > > Keith >> > > >> > > On 17 September 2014 09:53, FLIP VAN WYNGAARDT <flipv@raf.co.za> >> wrote: >> > > >> > > > HP UX B.11.31 U 9000/800 >> > > > IDS 11.70.FC7GE >> > > > >> > > > Good Day, >> > > > >> > > > I want to consolidate a number of 2gb chunks into a single chunk. >> Can >> > > > anyone >> > > > advise me on how to do that ? >> > > > >> > > > Thanks >> > > > >> > > > >> > > > >> > > > >> > > >> > > >> > >> > >> >> > ******************************************************************************* >> > > > Forum Note: Use "Reply" to post a response in the discussion >> forum. >> > > > >> > > > >> > > >> > > --90e6ba6e8fb83170930503424c91 >> > > >> > > >> > > >> > > >> > >> > >> >> > ******************************************************************************* >> > > Forum Note: Use "Reply" to post a response in the discussion forum. >> > > >> > > >> > >> > -- >> > Fernando Nunes >> > Portugal >> > >> > http://informix-technology.blogspot.com >> > My email works... but I don't check it frequently... >> > >> > --047d7b10c847876b84050343559f >> > >> > >> > >> > >> >> > ******************************************************************************* >> > Forum Note: Use "Reply" to post a response in the discussion forum. >> > >> > >> >> --485b397dd1f3dd59700503443e3f >> >> >> >> > ******************************************************************************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> > > --001a11340f129af0b60503445bf9 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > -- Paul Watson Tel: +1 913-674-0360 Mob: +1 913-387-7529 Web: www.oninit.com Failure is not as frightening as regret. If you want to improve, be content to be thought foolish and stupid. What this country needs are more unemployed politicians

Yes... But UNLOAD/LOAD vs INSERT INTO ... SELECT FROM using a raw table... Although it's true that the first using express load doesn't go through the SQL layer... It could be worth comparing for really large tables... but for most of them... On Wed, Sep 17, 2014 at 4:17 PM, Art Kagel <art.kagel@gmail.com> wrote: > FWIW, external tables are significantly faster than UNLOAD since the data > doesn't have to be transferred from the server to dbaccess before being > written out. > > Art > > Art S. Kagel, Principal Consultant > ASK Database Management > > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on 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 Wed, Sep 17, 2014 at 11:08 AM, Keith Simmons <smiley73@gmail.com> > wrote: > > > It does tend to be my point of first call for data transfer. > > UNLOAD TO 'pipe-file' SELECT * FROM 'table' in a dbaccess session and a> > dbload script in a second session (loading from the pipe and transacting > > every 10,000 records) is quick and simple to set up and reasonably quick > to > > run. It's only if this becomes problematic do I start to look at RAW or > > other methods that are more 'convoluted'. > > It comes down to what one uses most often and is familiar with, also if > > there is down-time and how many other people are still trying to access a > > 24 x 7 system :-(( > > Keith > > > > On 17 September 2014 15:03, Fernando Nunes <domusonline@gmail.com> > wrote: > > > > > Why UNLOAD/LOAD?! 100% agreement with all the rest > > > > > > On Wed, Sep 17, 2014 at 1:49 PM, Keith Simmons <smiley73@gmail.com> > > wrote: > > > > > > > Create a new dbspace with your new large chunks and then move the > data > > > into > > > > it. > > > > For smaler tables (< 500 Mb) ALTER FRAGMENT ON TABLE 'tabname' INIT > IN > > > > 'new_dbspace' > > > > for larger you may need to unload to filestore and then reload, or > > > > unload/load to and from a pipe. > > > > Watch for large transactions and all the other things that can trip > > this > > > > up. > > > > There is no automtic way to merge a number of small chunks into a > large > > > > one, it is too much a fundamental operation on the engine structure. > > > > > > > > Keith > > > > > > > > On 17 September 2014 09:53, FLIP VAN WYNGAARDT <flipv@raf.co.za> > > wrote: > > > > > > > > > HP UX B.11.31 U 9000/800 > > > > > IDS 11.70.FC7GE > > > > > > > > > > Good Day, > > > > > > > > > > I want to consolidate a number of 2gb chunks into a single chunk. > Can > > > > > anyone > > > > > advise me on how to do that ? > > > > > > > > > > Thanks > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > > > > > > > > > --90e6ba6e8fb83170930503424c91 > > > > > > > > > > > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > > > > > -- > > > Fernando Nunes > > > Portugal > > > > > > http://informix-technology.blogspot.com > > > My email works... but I don't check it frequently... > > > > > > --047d7b10c847876b84050343559f > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > --485b397dd1f3dd59700503443e3f > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --001a11340f129af0b60503445bf9 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --001a113466b6e16e8805034626b4

I have found external table approach significantly faster when working with TS data Cheers Paul > Yes... But UNLOAD/LOAD vs INSERT INTO ... SELECT FROM using a raw table... > Although it's true that the first using express load doesn't go through > the > SQL layer... > It could be worth comparing for really large tables... but for most of > them... > > On Wed, Sep 17, 2014 at 4:17 PM, Art Kagel <art.kagel@gmail.com> wrote: > >> FWIW, external tables are significantly faster than UNLOAD since the >> data >> doesn't have to be transferred from the server to dbaccess before being >> written out. >> >> Art >> >> Art S. Kagel, Principal Consultant >> ASK Database Management >> >> Blog: http://informix-myview.blogspot.com/ >> >> Disclaimer: Please keep in mind that my own opinions are my own opinions >> and do not reflect on 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 Wed, Sep 17, 2014 at 11:08 AM, Keith Simmons <smiley73@gmail.com> >> wrote: >> >> > It does tend to be my point of first call for data transfer. >> > UNLOAD TO 'pipe-file' SELECT * FROM 'table' in a dbaccess session and>> a >> > dbload script in a second session (loading from the pipe and >> transacting >> > every 10,000 records) is quick and simple to set up and reasonably >> quick >> to >> > run. It's only if this becomes problematic do I start to look at RAW >> or >> > other methods that are more 'convoluted'. >> > It comes down to what one uses most often and is familiar with, also >> if >> > there is down-time and how many other people are still trying to >> access a >> > 24 x 7 system :-(( >> > Keith >> > >> > On 17 September 2014 15:03, Fernando Nunes <domusonline@gmail.com> >> wrote: >> > >> > > Why UNLOAD/LOAD?! 100% agreement with all the rest >> > > >> > > On Wed, Sep 17, 2014 at 1:49 PM, Keith Simmons <smiley73@gmail.com> >> > wrote: >> > > >> > > > Create a new dbspace with your new large chunks and then move the >> data >> > > into >> > > > it. >> > > > For smaler tables (< 500 Mb) ALTER FRAGMENT ON TABLE 'tabname' >> INIT >> IN >> > > > 'new_dbspace' >> > > > for larger you may need to unload to filestore and then reload, or >> > > > unload/load to and from a pipe. >> > > > Watch for large transactions and all the other things that can >> trip >> > this >> > > > up. >> > > > There is no automtic way to merge a number of small chunks into a >> large >> > > > one, it is too much a fundamental operation on the engine >> structure. >> > > > >> > > > Keith >> > > > >> > > > On 17 September 2014 09:53, FLIP VAN WYNGAARDT <flipv@raf.co.za> >> > wrote: >> > > > >> > > > > HP UX B.11.31 U 9000/800 >> > > > > IDS 11.70.FC7GE >> > > > > >> > > > > Good Day, >> > > > > >> > > > > I want to consolidate a number of 2gb chunks into a single >> chunk. >> Can >> > > > > anyone >> > > > > advise me on how to do that ? >> > > > > >> > > > > Thanks >> > > > > >> > > > > >> > > > > >> > > > > >> > > > >> > > > >> > > >> > > >> > >> > >> >> > ******************************************************************************* >> > > > > Forum Note: Use "Reply" to post a response in the discussion >> forum. >> > > > > >> > > > > >> > > > >> > > > --90e6ba6e8fb83170930503424c91 >> > > > >> > > > >> > > > >> > > > >> > > >> > > >> > >> > >> >> > ******************************************************************************* >> > > > Forum Note: Use "Reply" to post a response in the discussion >> forum. >> > > > >> > > > >> > > >> > > -- >> > > Fernando Nunes >> > > Portugal >> > > >> > > http://informix-technology.blogspot.com >> > > My email works... but I don't check it frequently... >> > > >> > > --047d7b10c847876b84050343559f >> > > >> > > >> > > >> > > >> > >> > >> >> > ******************************************************************************* >> > > Forum Note: Use "Reply" to post a response in the discussion forum. >> > > >> > > >> > >> > --485b397dd1f3dd59700503443e3f >> > >> > >> > >> > >> >> > ******************************************************************************* >> > Forum Note: Use "Reply" to post a response in the discussion forum. >> > >> > >> >> --001a11340f129af0b60503445bf9 >> >> >> >> > ******************************************************************************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > > --001a113466b6e16e8805034626b4 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > -- Paul Watson Tel: +1 913-674-0360 Mob: +1 913-387-7529 Web: www.oninit.com Failure is not as frightening as regret. If you want to improve, be content to be thought foolish and stupid. What this country needs are more unemployed politicians

If you can upgrade to an enterprise license you could try using ER providing you have a spare server and storage. Otherwise it's going to be a messy ordeal at best if the dbspace(s) in question are large and there is RI established. if you decide to upgrade your license then you can restore the spare with the source archive. Unload then drop the tables/chunks in the dbspace you want to reorg on the spare and recreate with the new layout , reload and then establish ER and use ER to do the remainder of the grunt work by using sync/check replicate etc. Of course these tables need to have a primary key or unique index (v12 allows for just a unique index). Afterwards you can then restore the archive from the spare back to the source once you define the revised storage layout on the source to match the spare. The advantage of this method is that you can do this over time and not be concerned with taking any outages expect for the cut over back to the source. Regards, Mark

External table writing to a pipe to an external table reading from the pipe and a query reading the input external table and writing to a RAW table? Not fast? OK, complicated, but fast. Art Art S. Kagel, President and Principal Consultant ASK Database Management Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on 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 Wed, Sep 17, 2014 at 1:25 PM, Fernando Nunes <domusonline@gmail.com> wrote: > Yes... But UNLOAD/LOAD vs INSERT INTO ... SELECT FROM using a raw table... > Although it's true that the first using express load doesn't go through the > SQL layer... > It could be worth comparing for really large tables... but for most of > them... > > On Wed, Sep 17, 2014 at 4:17 PM, Art Kagel <art.kagel@gmail.com> wrote: > > > FWIW, external tables are significantly faster than UNLOAD since the data > > doesn't have to be transferred from the server to dbaccess before being > > written out. > > > > Art > > > > Art S. Kagel, Principal Consultant > > ASK Database Management > > > > Blog: http://informix-myview.blogspot.com/ > > > > Disclaimer: Please keep in mind that my own opinions are my own opinions > > and do not reflect on 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 Wed, Sep 17, 2014 at 11:08 AM, Keith Simmons <smiley73@gmail.com> > > wrote: > > > > > It does tend to be my point of first call for data transfer. > > > UNLOAD TO 'pipe-file' SELECT * FROM 'table' in a dbaccess session and a> > > dbload script in a second session (loading from the pipe and > transacting > > > every 10,000 records) is quick and simple to set up and reasonably > quick > > to > > > run. It's only if this becomes problematic do I start to look at RAW or > > > other methods that are more 'convoluted'. > > > It comes down to what one uses most often and is familiar with, also if > > > there is down-time and how many other people are still trying to > access a > > > 24 x 7 system :-(( > > > Keith > > > > > > On 17 September 2014 15:03, Fernando Nunes <domusonline@gmail.com> > > wrote: > > > > > > > Why UNLOAD/LOAD?! 100% agreement with all the rest > > > > > > > > On Wed, Sep 17, 2014 at 1:49 PM, Keith Simmons <smiley73@gmail.com> > > > wrote: > > > > > > > > > Create a new dbspace with your new large chunks and then move the > > data > > > > into > > > > > it. > > > > > For smaler tables (< 500 Mb) ALTER FRAGMENT ON TABLE 'tabname' INIT > > IN > > > > > 'new_dbspace' > > > > > for larger you may need to unload to filestore and then reload, or > > > > > unload/load to and from a pipe. > > > > > Watch for large transactions and all the other things that can trip > > > this > > > > > up. > > > > > There is no automtic way to merge a number of small chunks into a > > large > > > > > one, it is too much a fundamental operation on the engine > structure. > > > > > > > > > > Keith > > > > > > > > > > On 17 September 2014 09:53, FLIP VAN WYNGAARDT <flipv@raf.co.za> > > > wrote: > > > > > > > > > > > HP UX B.11.31 U 9000/800 > > > > > > IDS 11.70.FC7GE > > > > > > > > > > > > Good Day, > > > > > > > > > > > > I want to consolidate a number of 2gb chunks into a single chunk. > > Can > > > > > > anyone > > > > > > advise me on how to do that ? > > > > > > > > > > > > Thanks > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > > > > Forum Note: Use "Reply" to post a response in the discussion > forum. > > > > > > > > > > > > > > > > > > > > > > --90e6ba6e8fb83170930503424c91 > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > > > > > > > > > -- > > > > Fernando Nunes > > > > Portugal > > > > > > > > http://informix-technology.blogspot.com > > > > My email works... but I don't check it frequently... > > > > > > > > --047d7b10c847876b84050343559f > > > > > > > > > > > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > > > > > --485b397dd1f3dd59700503443e3f > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > --001a11340f129af0b60503445bf9 > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > > --001a113466b6e16e8805034626b4 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11340f125fcae105034747c6

You cannot use ER between objects in the same database. Art Art S. Kagel, President and Principal Consultant ASK Database Management Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on 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 Wed, Sep 17, 2014 at 2:35 PM, MARK JALKIEWICZ < mark.jalkiewicz@verizon.net> wrote: > If you can upgrade to an enterprise license you could try using ER > providing > you have a spare server and storage. Otherwise it's going to be a messy > ordeal > at best if the dbspace(s) in question are large and there is RI > established. > > if you decide to upgrade your license then you can restore the spare with > the > source archive. Unload then drop the tables/chunks in the dbspace you want > to > reorg on the spare and recreate with the new layout , reload and then > establish ER and use ER to do the remainder of the grunt work by using > sync/check replicate etc. Of course these tables need to have a primary > key or > unique index (v12 allows for just a unique index). Afterwards you can then > restore the archive from the spare back to the source once you define the > revised storage layout on the source to match the spare. The advantage of > this > method is that you can do this over time and not be concerned with taking > any > outages expect for the cut over back to the source. > > Regards, > > Mark > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11c3783e835e05050347763f

HPL simultaneous unload/load via a named pipe is a good way to go an alternative to ER which requires a primary key or unique index on every table you want to replicate, which a lot of schemas don't have. It's definitely worth learning how to use HPL. The documentation on it is pretty horrible, would benefit from more specific examples and desperately needs an update (anyone still use ipload?) but when set up correctly it really flies. The traditional problem with HPL or any unload/load method was recreating any foreign keys afterwards which was very slow but with 11.70.xC8 and (I think) 12.10.xC4 onwards this is many times faster. Just don't use PDQ for foreign key creation in these versions because it is still slow. Personally I strongly dislike "alter fragment" and think it's pretty useless as currently implemented. Its main benefit is that it leaves the logical schema intact which can save you checking you didn't lose any constraints or references in the process. It's ok for small tables but you rarely need to reorganise these anyway; your situation may be an exception. It is slow and requires a lot of logical logs. Any rollbacks owing to long transactions are slower still. It may do implicit index rebuilds when moving data, which is to be expected, but you often want to move the index as part of the same piece of work so you end up rebuilding it twice.

Why not use external tables? HPL is great, but it's a massive effort to set up. -- Regards Spokey > On 18 Sep 2014, at 11:19, BENJAMIN THOMPSON <benjamin.thompson@bskyb.com> wrote: > > HPL simultaneous unload/load via a named pipe is a good way to go an > alternative to ER which requires a primary key or unique index on every table > you want to replicate, which a lot of schemas don't have. It's definitely > worth learning how to use HPL. The documentation on it is pretty horrible, > would benefit from more specific examples and desperately needs an update > (anyone still use ipload?) but when set up correctly it really flies. > > The traditional problem with HPL or any unload/load method was recreating any > foreign keys afterwards which was very slow but with 11.70.xC8 and (I think) > 12.10.xC4 onwards this is many times faster. Just don't use PDQ for foreign > key creation in these versions because it is still slow. > > Personally I strongly dislike "alter fragment" and think it's pretty useless > as currently implemented. Its main benefit is that it leaves the logical > schema intact which can save you checking you didn't lose any constraints or > references in the process. It's ok for small tables but you rarely need to > reorganise these anyway; your situation may be an exception. It is slow and > requires a lot of logical logs. Any rollbacks owing to long transactions are > slower still. It may do implicit index rebuilds when moving data, which is to > be expected, but you often want to move the index as part of the same piece of > work so you end up rebuilding it twice. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

Hi Spokey, I should use external tables more :) However, I don't think HPL is not as difficult as claimed. I have some of my own notes on it where I can just change a few variables, copy/paste and off it goes. It's four lines using onpladm/onpload to do an unload/load plus another to create a named pipe. I might do a blog post on this at some point. I find the main faff is making sure you preserve all constraints and relationships in the schema afterwards. Just to remind myself how badly you can be punished if your "alter fragment" statement causes a long transaction, I had a go with "alter fragment on table X init..." in a test system. It's a common junior DBA mistake. Started: Thu Sep 18 10:04:11 BST 2014 In online log: 10:04:20 Logical Log 191510 Complete, timestamp: 0x32cc24fb. ... 10:35:19 Logical Log 191824 Complete, timestamp: 0x57cab931. 10:35:22 Aborting Long Transaction: tx: 0x145273c4c8 username: thompsonb uid: 10000345 So that's 314 logs in 31 minutes before I hit LONG TX. Nearly four hours later the roll back is still running and has got (according to 'onstat -x') as far as log 191668 which leaves 158 logs to go which half way, near enough. Given this I estimate it takes 15x longer to roll back than it did to get as far as the LONG TX. Ben.