Re: Onload, the "fast" alternative

On Mar 15, 8:37 am, Cesar Inacio Martins <cesar_inacio_mart...@yahoo.com.br> wrote: > Hi Neil, > > AFAIK you cannot use onunload/onload for differ IFX versions... > Probably the natural differ of struct of the data pages generate this problem of slowness... > > My suggestion: Go to HPL ... much faster of dbimport/dbexport  . But the control of the creation of objects must be manually... > > check the command: > onpladm create project <prj> -D <database> .... > > The easy / dummy way to do this is: > On the source server: > - Generate a dbschema full > - Export with HPL (using the project or table-by-table) > On the target server: > - Create this dbschema on the target (all objects) > - Disable all FK constraints and triggers > - Load all data with HPL > - Enable all FKs and triggers. > - Update statistics... > > And exists several ways to tunning this process: > - using gzip to unload all data (avoiding write I/O or network I/O) > - Writing all data from table to several files in parallel > - parameter of the HPL (plconfig.std) > - PDQ configuration (for indexes creation) > > And for the load, alternatively  you can use "external tables' ... if you don't want use the HPL > > Copied from v11.70 Information Center:http://publib.boulder.ibm.com/infocenter/idshelp/v117/topic/com.ibm.m... > > You can use only onunload and onload if your answer > to each of the following questions is yes. If your answer is no, > you cannot use onunload and onload. > Use onunload and onload Only If Your Answer To > Each Question Is Yes > > Is the target database server on the same hardware > platform? > > Do you want to move to another database server > of the same version? > > Do you want to keep the existing database schema > without modifying it? > > Do you want to move an entire database or an entire > table? > > Are the page images compatible? > > Are the numeric representations the same? > > --- Em ter, 15/3/11, Neil Truby <neil.tr...@ardenta.com> escreveu: > > De: Neil Truby <neil.tr...@ardenta.com> > Assunto: Onload, the "fast" alternative > Para: informix-l...@iiug.org > Data: Terça-feira, 15 de Março de 2011, 11:23 > > 11.5FC8W2 on Linux > > I'm doing a 9.40 upgrade to 11.5 on a smallish database, around 100g.  I > want to unload and reload the data too, as the dbspace layout is dreadful. > > timings so far: > dbexport 2h 20m > dbimport 10h > > So I thought I'd try onunload. > > onunload -t filename -s 204800000 -b 512 databasename took 0h 42m> > The onload is still going, 15 hours later!!  By the looks of it (the > onunload file size was 62g in size) it still has about 6g to go ... > > onstat -D ....> 67bf1028         9      9      0          24275898 24274725 > /opt/informix/dbspaces3/appdbs_1 > 67bf1218         10     9      0          4784051  4700042 > /opt/informix/dbspaces3/appdbs_2 > > Is onunload supposed to be this slow?  Was my blocksize choice (512) wrong? > Why is it doing so many page *reads" (just given myself an idea here; > perhaps it's a free page list thing and if I reinitialise the db server it > wouldn't do the reads ...?). > > !5 hours and counting for a 62g database is crazy.  This is all on fairly > slow internal disk, but even a dbimport was much faster than this. > > Any ideas?  (myexport is on my list for sure, but I'd like to know what's > wrong with onload). > > Thanks > Neil > > _______________________________________________ > Informix-list mailing list > Informix-l...@iiug.orghttp://www.iiug.org/mailman/listinfo/informix-list onstat -g ath onstat -g stk for the onload threads ??

Hello Neil, If my memory is correct, to minimize I/O and translation (informix internal to ASCII), I like to SELECT from source server table and INSERT into target server table all in 1 SQL. Plus I try to get thebest network connection between the 2 servers (even if I have to run a new cable directly between them). On the source server setup LIGHT_SCANS, etc. On the target server initially make the table RAW (then STANDARD when done), etc. I would insert tables in parallel (if the lesser computer has 4 cpu's I would do 3 tables in parallel). I did this years ago with more data than you in less time. Good luck, LS

My dbcopy utility does this faster when copying between two servers (even a bit faster, oddly, if you run it on a 3rd machine). Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Mar 17, 2011 at 3:29 PM, LIGHT SCANS <light_scans@yahoo.com> wrote: > Hello Neil, > > If my memory is correct, to minimize I/O and translation (informix > internal to ASCII), I like to SELECT from source server table and > INSERT into target server table all in 1 SQL. Plus I try to get the> best network connection between the 2 servers (even if I have to run a > new cable directly between them). On the source server setup > LIGHT_SCANS, etc. On the target server initially make the table RAW > (then STANDARD when done), etc. I would insert tables in parallel (if > the lesser computer has 4 cpu's I would do 3 tables in parallel). > > I did this years ago with more data than you in less time. > > Good luck, > LS > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >