how to copy large data fast

Dear all: I intend to copy large data from ids7.3 to ids10 . The data size about 40 GB includes several hundred tables. I try my best to copy it fast as I can such that - use INSERT .. SELECT command to copy data - create index after copy data - separate into several process running concurrently. - set database unlogged. Is any other way that I can make it faster ? Or any parameters I can modify to make it ?

Set PDQPRIORITY for the index builds. -- Neil Truby t:01932 724027 Director m:07798 811708 Ardenta Limited e:neil.truby@ardenta.com <roger@star2000.com.tw> wrote in message news:1168753830.198456.261650@s34g2000cwa.googlegroups.com... > Dear all: > I intend to copy large data from ids7.3 to ids10 . > The data size about 40 GB includes several hundred tables. > I try my best to copy it fast as I can such that > - use INSERT .. SELECT command to copy data > - create index after copy data > - separate into several process running concurrently. > - set database unlogged. > Is any other way that I can make it faster ? > Or any parameters I can modify to make it ? >

roger@star2000.com.tw wrote: > Dear all: > I intend to copy large data from ids7.3 to ids10 . > The data size about 40 GB includes several hundred tables. > I try my best to copy it fast as I can such that > - use INSERT .. SELECT command to copy data > - create index after copy data > - separate into several process running concurrently. > - set database unlogged. > Is any other way that I can make it faster ? > Or any parameters I can modify to make it ? HPL? (High performance loader)

>From: "John" <jgleipold@gmail.com> >roger@star2000.com.tw wrote: > > Dear all: > > I intend to copy large data from ids7.3 to ids10 . > > The data size about 40 GB includes several hundred tables. > > I try my best to copy it fast as I can such that > > - use INSERT .. SELECT command to copy data > > - create index after copy data > > - separate into several process running concurrently. > > - set database unlogged. > > Is any other way that I can make it faster ? > > Or any parameters I can modify to make it ? > >HPL? (High performance loader) > He has the data with a table. So he can do a select from the local database and insert in to the remote database, assuming that he can establish a connection to that machine... This would be faster that the HPL since he's skipping the unload process. He could do each table in parallel as well. That would be the *fastest* way. Well, actually there is a "faster" way, but it would require a tad more work, however it isn't worth the effort. -G _________________________________________________________________ Dave vs. Carl: The Insignificant Championship Series. 'Who will win? http://clk.atdmt.com/MSN/go/msnnkwsp0070000001msn/direct/01/?href=http://davevscarl.spaces.live.com/?icid=T001MSN38C07001

Ian Michael Gumby wrote: > >HPL? (High performance loader) > > > > He has the data with a table. > > So he can do a select from the local database and insert in to the remote > database, assuming that he can establish a connection to that machine... > > > This would be faster that the HPL since he's skipping the unload process. > I believe that you can unload to a named pipe with HPL and then load from that same named pipe. This is a very fast way of doing this. I think there might even be a program in the iiug repository that will set up the hpl job to do this so you don't have to go through the horrible HPL GUI. I think you can even create the jobs and run many in parallel.

> > Ian Michael Gumby wrote: >> >HPL? (High performance loader) >> > >> >> He has the data with a table. >> >> So he can do a select from the local database and insert in to the remote >> database, assuming that he can establish a connection to that machine... >> >> >> This would be faster that the HPL since he's skipping the unload process. >> > > I believe that you can unload to a named pipe with HPL and then load > from that same named pipe. This is a very fast way of doing this. I > think there might even be a program in the iiug repository that will > set up the hpl job to do this so you don't have to go through the > horrible HPL GUI. I think you can even create the jobs and run many in > parallel. HPL with named pipes is the one of the coolest things ever and if you have a later version you can use the onpladm command line tool and bypass the GUI. With a little work you can perform an unload of the source table, ship the data over a network (i.e. skip the unload to temporary flat file bottleneck) and concurrently load the data into your target table. 1. Create some named pipes on the target server with the mkfifo command: mkfifo /home/informix/pipe.0 2. Create a device array in HPL on the source server consisting of PIPE devices with a command that looks like: ssh informix@target_server "cat - > /home/informix/pipe.0" 3. Create a device array in HPL on the target server consisting of PIPE devices with a command that looks like: cat /home/informix/pipe.0 4. Create an unload job on the source server and start it with onpload 5. Create a load job on the target server and start it with onpload There you go, you're simultaneously unloading from the source and loading into the target and eliminating those pesky temporary unload files. informix on the source server must be trusted on the target server for the ssh (or remsh or rsh) to work without requesting a password. To speed up the index builds you can change some config parameters and environment variables: set pdqpriority 100 set MAX_PDQPRIORITY to 100increase SHMVIRTSIZE as much a possible without causing swapping increase DS_TOTAL_MEMORY to about 80% of SHMVIRTSIZE set LRU_MIN_DIRTY to 60 set LRU_MAX_DIRTY to 70 set CKPTINTVL to something silly like 1 hour and let the physical log trigger checkpoints export PSORT_NPROCS to 2 * number of CPUVPS export PSORT_DBTEMP to as many large filesystems as you can Andrew

Andrew Ford wrote: > HPL with named pipes is the one of the coolest things ever and if you have a > later version you can use the onpladm command line tool and bypass the GUI. > Do you have examples of using the onpladm command? I haven't used it this way. In fact I haven't had a need to use it for 6 years. I avoided the gui in the past by reverse engineering the onpload database (this was before I knew that other people had already done it.) I have lost that shell script now but I remember using the named pipes to unload and load.

>From: "bozon" <curtis@crowson1.com> >I believe that you can unload to a named pipe with HPL and then load >from that same named pipe. This is a very fast way of doing this. I >think there might even be a program in the iiug repository that will >set up the hpl job to do this so you don't have to go through the >horrible HPL GUI. I think you can even create the jobs and run many in >parallel. > Interesting. Although you'd be using an SQL unload command to the named pipe and then use the HPL to read from the named pipe. In theory, you'd still be faster if you were to select and write the results in to shareed memory, then have n readers from shared memory that write to a tcp/ip socket on the remote machine and then each socket has x readers each with a connection to the database. I say in theory because the complexity of the code outweighs its value unless you were just experimenting. You'd then be limited by hardware, and the size of your network pipe. But the question goes, how fast is fast enough? Even unloading to a named pipe and then using HPL across a network may be faster, you still have to consider the margin of speed vs the complexity. Note: Some of the technology that I'm talking about could be found in the RTL that may or may not still be marketed by IBM. (I think they discontinued the Fin Foundation Bundle but not the individual products.) But hey! what do I know? I'm paid to solve client's problems, not play and develop tools that may or may not improve the quality of life for a DBA. ;-) _________________________________________________________________ Communicate instantly! Use your Hotmail address to sign into Windows Live Messenger now. http://get.live.com/messenger/overview