Table-level restore to reduce number of extents fo

Hello, after reading some posts on table-level restore, I was wondering if it could be possible to tackle an issue we have. In our main production database, we have quite a few tables with more than 180 extents. We are planning to create a new database where we would set the proper extent size for each table and transfer the data after unloading them. Due to the amount of data, it will take about 7 hours. Would it be an option to use a table-level restore with a schema command file creating the tables with the proper extent size and restoring the data? Does archecker works only with onbar or is it possible to restore tables with ontape backup files? I'm pretty new to Informix. Maybe this solution does not make sense. I'll appreciate any comment or hint. Thanks, Thierry MOREL --0016e65c7bc642214804729585ed

A restore is a page level operation. Your restored DB will look exactly like your old one. Check out the high performance loader/unloader. There is a Load FAQ at: http://artentech.com/downloads.htm cheers j. Sane ego te vocavi. Forsitan capedictum tuum desit. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of Thierry Morel Sent: Wednesday, September 02, 2009 6:19 AM To: ids@iiug.org Subject: Table-level restore to reduce number of extent.... [16833] Hello, after reading some posts on table-level restore, I was wondering if it could be possible to tackle an issue we have. In our main production database, we have quite a few tables with more than 180 extents. We are planning to create a new database where we would set the proper extent size for each table and transfer the data after unloading them. Due to the amount of data, it will take about 7 hours. Would it be an option to use a table-level restore with a schema command file creating the tables with the proper extent size and restoring the data? Does archecker works only with onbar or is it possible to restore tables with ontape backup files? I'm pretty new to Informix. Maybe this solution does not make sense. I'll appreciate any comment or hint. Thanks, Thierry MOREL --0016e65c7bc642214804729585ed **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

Hi, Without knowing what version of IDS you're using, there are several possible answers. If you use IDS 11.50xC5 (the latest), and if you have the storage optimization feature, then you can repack the rows and shrink the table online. Otherwise, I suggest the High-Performance Loader (HPL) as the most efficient means to unload and reload the data. Or you could just restore your backup with ontape. That should be quicker than archecker. But archecker does work just fine with ontape. I've used it frequently. Lastly, you might unload your data using a set of parallel scripts, and then load them on the target the same way. Many options. Hard to pick just one. My personal choice would be to set up the second machine and use SQL (insert into...select from...) to populate the new system. You can use a number of sessions in parallel to move the data as fast as the network allows. Cheers, Dick Dick Snoke Executive IT Specialist IBM Software Group Tel: (404) 487-1595 Email: dsnoke@us.ibm.com From: "Thierry Morel" <morelthi@googlemail.com> To: ids@iiug.org Date: 09/02/09 06:20 AM Subject: Table-level restore to reduce number of extent.... [16833] Sent by: ids-bounces@iiug.org Hello, after reading some posts on table-level restore, I was wondering if it could be possible to tackle an issue we have. In our main production database, we have quite a few tables with more than 180 extents. We are planning to create a new database where we would set the proper extent size for each table and transfer the data after unloading them. Due to the amount of data, it will take about 7 hours. Would it be an option to use a table-level restore with a schema command file creating the tables with the proper extent size and restoring the data? Does archecker works only with onbar or is it possible to restore tables with ontape backup files? I'm pretty new to Informix. Maybe this solution does not make sense. I'll appreciate any comment or hint. Thanks, Thierry MOREL --0016e65c7bc642214804729585ed ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Yes, archecker works with both onbar and ontape archives, no problem. Yes you could use it to restore a table to a new database from an archive. You could also simply copy the data from the original database directly into the new one and it would likely be faster. You can do that at reasonable speed using 'insert into ... select ... from ..' syntax and even faster using the HP Loader piping from one read job directly to a write job. As to sizing your extents, if you use my dbschema replacement utility, myschema, with the -a and -m options (and -M if you have fragmented tables) it will generate a schema you can use to create the new database with the correct extent sizing. My schema is part of the package utils2_ak which you can download from the Oninit web site (www.oninit.com/utils) or from the IIUG website ( www.iiug.org/software). 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, Sep 2, 2009 at 5:18 AM, Thierry Morel <morelthi@googlemail.com>wrote: > Hello, > > after reading some posts on table-level restore, I was wondering if it > could > be possible to tackle an issue we have. > In our main production database, we have quite a few tables with more than > 180 extents. > We are planning to create a new database where we would set the proper > extent size for each table and transfer the data after unloading them. > Due to the amount of data, it will take about 7 hours. > Would it be an option to use a table-level restore with a schema command > file creating the tables with the proper extent size and restoring the > data? > Does archecker works only with onbar or is it possible to restore tables > with ontape backup files? > I'm pretty new to Informix. Maybe this solution does not make sense. > > I'll appreciate any comment or hint. > > Thanks, > > Thierry MOREL > > --0016e65c7bc642214804729585ed > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --00151744843a76ebcd04729ef213

You could consider creating a new dbspace with enough space to hold the table, then do a level 0 backup of the dbspace with the table in it as well as the root dbspace and the new dbspace, just in case something goes wrong later and you need to restore. After that, I'd drop all the indexes on the table, alter the table to raw ( no logging ) and run an alter fragment on table tableName init in newDBSpace. After that run an alter table tableName next size NKB, ie: a more appropriate next extent size. After that, rebuild the indexes, alter the table back to standard (logged), run update statistics and do a level 0 backup of the new dbspace with the data in it. Note that this strategy will require exclusive use of the table whilst it is being reorganized. Stuart McCann Integrated Spatial Services Unit Information Communication & Technology Department of Lands, Bathurst Phone: (02) 63328284 stuart.mccann@lpma.nsw.gov.au *************************************************************** This message is intended for the addressee named and may contain confidential information. If you are not the intended recipient, please delete it and notify the sender. Views expressed in this message are those of the individual sender, and are not necessarily the views of the Land and Property Management Authority. This email message has been swept by MIMEsweeper for the presence of computer viruses. *************************************************************** Please consider the environment before printing this email.