Oninit cdi / Archive
Talk to an expert →

Fast Unload Load

Posted in 2012

Summary

Vikas asked how to move three very large (~80GB) tables between two 11.50 instances on different HP-UX hosts, and whether named pipes work across hosts. Suggestions: use external tables (faster/easier than HPL) plus Art Kagel's myexport, or better his dbcopy utility with mkdbcopy.awk, optionally running many parallel dbcopy jobs split by WHERE clauses. Cross-host FIFOs with HPL do work (start the destination job first), but several posters reported pipes hanging or losing data when too many run concurrently, recommending 2-4 at most or landing files on disk. Art warned that restoring the dbspaces from an onbar level-0 would overwrite the target server. No single resolution is recorded.

Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.

Topics: Migration, Import/Export & Data Conversion, Platform-Specific Issues

VIKAS HIVARKAR — — source: IIUG Forums & Mailing Lists

Hello All, Instance_1: 11.50.FC8W2 on HP-Ux on host_1 Instance_2: 11.50.FC6W3 on HP-Ux on host_2 3 very big tables needs to be unloaded (with/without hpl(deluxe only)or external tables) from Instance_1 (host_1) and load the data on to Instance_2 (host_2) Not sure if named pipes can work across two different instances on different hosts. Any suggesstion, example how to go about this? Regards, Vikas

↪ replying to VIKAS HIVARKAR
Art Kagel — — source: IIUG Forums & Mailing Lists

Use external tables. They are faster than HP loader and easier to use. My myexport utility can make it as easy as using dbexport BTW. Art On Feb 20, 2012 7:39 AM, "VIKAS HIVARKAR" <vikas.hivarkar@gmail.com> wrote: > Hello All, > > Instance_1: 11.50.FC8W2 on HP-Ux on host_1 > Instance_2: 11.50.FC6W3 on HP-Ux on host_2 > > 3 very big tables needs to be unloaded (with/without hpl(deluxe only)or > external tables) from Instance_1 (host_1) and load the data on to > Instance_2 > (host_2) > > Not sure if named pipes can work across two different instances on > different > hosts. > > Any suggesstion, example how to go about this? > > Regards, > Vikas > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --bcaec51a76fa1511b004b9652bcb

↪ replying to VIKAS HIVARKAR
Art Kagel — — source: IIUG Forums & Mailing Lists

Wait, copy data from one instance to another is s job for my dbcopy utility combined with the mkdbcopy.awk script in my utils3_ak package. Fastest way. Art On Feb 20, 2012 7:39 AM, "VIKAS HIVARKAR" <vikas.hivarkar@gmail.com> wrote: > Hello All, > > Instance_1: 11.50.FC8W2 on HP-Ux on host_1 > Instance_2: 11.50.FC6W3 on HP-Ux on host_2 > > 3 very big tables needs to be unloaded (with/without hpl(deluxe only)or > external tables) from Instance_1 (host_1) and load the data on to > Instance_2 > (host_2) > > Not sure if named pipes can work across two different instances on > different > hosts. > > Any suggesstion, example how to go about this? > > Regards, > Vikas > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --e89a8f3bafbfa9092a04b96532e7

↪ replying to VIKAS HIVARKAR
Alexandre Marini — — source: IIUG Forums & Mailing Lists

Yes, you could create unload fifos on the source, and load fifos on the target machine. Hope this link will help you: http://www.google.com/url?sa=t&rct=j&q=informix%20external%20tables%20fifo&sourc e=web&cd=2&ved=0CDQQFjAB&url=http%3A%2F%2Fpublib.boulder.ibm.com%2Finfocenter%2F idshelp%2Fv115%2Ftopic%2Fcom.ibm.admin.doc%2Fids_admin_1332.htm&ei=yV5CT-OCBcXh0 gGg1dW2Bw&usg=AFQjCNE6Cbom_hcrt93p3VRoqm6YGO2xcQ&cad=rja Regards. Alexandre Marini IBM Informix Certified Professional v10 / v11.50 / v11.70 IBM Information Management Informix Technical Professional IBM Infosphere DataStage Technical Professional Database Administrator > To: ids@iiug.org > From: vikas.hivarkar@gmail.com > Subject: Fast Unload Load [26269] > Date: Mon, 20 Feb 2012 07:39:12 -0500 > > Hello All, > > Instance_1: 11.50.FC8W2 on HP-Ux on host_1 > Instance_2: 11.50.FC6W3 on HP-Ux on host_2 > > 3 very big tables needs to be unloaded (with/without hpl(deluxe only)or > external tables) from Instance_1 (host_1) and load the data on to Instance_2 > (host_2) > > Not sure if named pipes can work across two different instances on different > hosts. > > Any suggesstion, example how to go about this? > > Regards, > Vikas > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

↪ replying to Alexandre Marini
Paul Watson — — source: IIUG Forums & Mailing Lists

The only gotcha on the pipes is to make sure you start the destination HPL job prior to the source HPL job, apart from that then HPL and pipes is trivial Cheers Paul > Yes, you could create unload fifos on the source, and load fifos on the > target > machine. > Hope this link will help you: > > http://www.google.com/url?sa=t&rct=j&q=informix%20external%20tables%20fifo&sourc e=web&cd=2&ved=0CDQQFjAB&url=http%3A%2F%2Fpublib.boulder.ibm.com%2Finfocenter%2F idshelp%2Fv115%2Ftopic%2Fcom.ibm.admin.doc%2Fids_admin_1332.htm&ei=yV5CT-OCBcXh0 gGg1dW2Bw&usg=AFQjCNE6Cbom_hcrt93p3VRoqm6YGO2xcQ&cad=rja > > Regards. > > Alexandre Marini > IBM Informix Certified Professional v10 / v11.50 / v11.70 > > IBM Information Management Informix Technical Professional > > IBM Infosphere DataStage Technical Professional > Database Administrator > >> To: ids@iiug.org >> From: vikas.hivarkar@gmail.com >> Subject: Fast Unload Load [26269] >> Date: Mon, 20 Feb 2012 07:39:12 -0500 >> >> Hello All, >> >> Instance_1: 11.50.FC8W2 on HP-Ux on host_1 >> Instance_2: 11.50.FC6W3 on HP-Ux on host_2 >> >> 3 very big tables needs to be unloaded (with/without hpl(deluxe only)or >> external tables) from Instance_1 (host_1) and load the data on to >> Instance_2 >> (host_2) >> >> Not sure if named pipes can work across two different instances on >> different >> hosts. >> >> Any suggesstion, example how to go about this? >> >> Regards, >> Vikas >> >> >> > ******************************************************************************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> > > > ******************************************************************************* > 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 www.advancedatatools.com Failure is not as frightening as regret. If you want to improve, be content to be thought foolish and stupid.

↪ replying to Paul Watson
Jack Parker — — source: IIUG Forums & Mailing Lists

We had problems with cross-server named pipes. One or two of them were = always shutting down resulting in portions of the data getting lost. I = am much more in favour of landing on disk just to do a sanity check. j. =20 On Feb 20, 2012, at 1:12 PM, Paul Watson wrote: > The only gotcha on the pipes is to make sure you start the destination = HPL=20 > job prior to the source HPL job, apart from that then HPL and pipes is=20= > trivial=20 >=20 > Cheers=20 > Paul=20 >=20 >> Yes, you could create unload fifos on the source, and load fifos on = the=20 >> target=20 >> machine.=20 >> Hope this link will help you:=20 >>=20 >>=20 > = http://www.google.com/url?sa=3Dt&rct=3Dj&q=3Dinformix%20external%20tables%= 20fifo&source=3Dweb&cd=3D2&ved=3D0CDQQFjAB&url=3Dhttp%3A%2F%2Fpublib.bould= er.ibm.com%2Finfocenter%2Fidshelp%2Fv115%2Ftopic%2Fcom.ibm.admin.doc%2Fids= _admin_1332.htm&ei=3DyV5CT-OCBcXh0gGg1dW2Bw&usg=3DAFQjCNE6Cbom_hcrt93p3VRo= qm6YGO2xcQ&cad=3Drja=20 >>=20 >> Regards.=20 >>=20 >> Alexandre Marini=20 >> IBM Informix Certified Professional v10 / v11.50 / v11.70=20 >>=20 >> IBM Information Management Informix Technical Professional=20 >>=20 >> IBM Infosphere DataStage Technical Professional=20 >> Database Administrator=20 >>=20 >>> To: ids@iiug.org=20 >>> From: vikas.hivarkar@gmail.com=20 >>> Subject: Fast Unload Load [26269]=20 >>> Date: Mon, 20 Feb 2012 07:39:12 -0500=20 >>>=20 >>> Hello All,=20 >>>=20 >>> Instance_1: 11.50.FC8W2 on HP-Ux on host_1=20 >>> Instance_2: 11.50.FC6W3 on HP-Ux on host_2=20 >>>=20 >>> 3 very big tables needs to be unloaded (with/without hpl(deluxe = only)or=20 >>> external tables) from Instance_1 (host_1) and load the data on to=20 >>> Instance_2=20 >>> (host_2)=20 >>>=20 >>> Not sure if named pipes can work across two different instances on=20= >>> different=20 >>> hosts.=20 >>>=20 >>> Any suggesstion, example how to go about this?=20 >>>=20 >>> Regards,=20 >>> Vikas=20 >>>=20 >>>=20 >>>=20 >>=20 > = **************************************************************************= *****=20 >>> Forum Note: Use "Reply" to post a response in the discussion forum.=20= >>>=20 >>=20 >>=20 >>=20 > = **************************************************************************= *****=20 >> Forum Note: Use "Reply" to post a response in the discussion forum.=20= >>=20 >=20 > --=20 > Paul Watson=20 > Tel: +1 913-674-0360=20 > Mob: +1 913-387-7529=20 > Web: www.oninit.com=20 >=20 > www.advancedatatools.com=20 >=20 > Failure is not as frightening as regret.=20 > If you want to improve, be content to be thought foolish and stupid.=20= >=20 >=20 > = **************************************************************************= *****=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=20= >=20

↪ replying to Jack Parker
Celso Cabral Coimbra — — source: IIUG Forums & Mailing Lists

We had the same problem this week. I don't trust on named pipes, I didn't had good experiences. I'm just coping the files and loading using HPL right now a table with 6 bi rows partially in four steps. Good luck, Celso Cabral Coimbra Administrador de Banco de Dados ClearTech Ltda "Trust at the heart of Communications" Tel. (11) 3576-4509 -----Mensagem original----- De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Em nome de Jack Parker Enviada em: segunda-feira, 20 de fevereiro de 2012 15:16 Para: ids@iiug.org Assunto: Re: Fast Unload Load [26274] We had problems with cross-server named pipes. One or two of them were = always shutting down resulting in portions of the data getting lost. I = am much more in favour of landing on disk just to do a sanity check. j. =20 On Feb 20, 2012, at 1:12 PM, Paul Watson wrote: > The only gotcha on the pipes is to make sure you start the destination = HPL=20 > job prior to the source HPL job, apart from that then HPL and pipes is=20= > trivial=20 >=20 > Cheers=20 > Paul=20 >=20 >> Yes, you could create unload fifos on the source, and load fifos on = the=20 >> target=20 >> machine.=20 >> Hope this link will help you:=20 >>=20 >>=20 > = http://www.google.com/url?sa=3Dt&rct=3Dj&q=3Dinformix%20external%20tables%= 20fifo&source=3Dweb&cd=3D2&ved=3D0CDQQFjAB&url=3Dhttp%3A%2F%2Fpublib.bould= er.ibm.com%2Finfocenter%2Fidshelp%2Fv115%2Ftopic%2Fcom.ibm.admin.doc%2Fids= _admin_1332.htm&ei=3DyV5CT-OCBcXh0gGg1dW2Bw&usg=3DAFQjCNE6Cbom_hcrt93p3VRo= qm6YGO2xcQ&cad=3Drja=20 >>=20 >> Regards.=20 >>=20 >> Alexandre Marini=20 >> IBM Informix Certified Professional v10 / v11.50 / v11.70=20 >>=20 >> IBM Information Management Informix Technical Professional=20 >>=20 >> IBM Infosphere DataStage Technical Professional=20 >> Database Administrator=20 >>=20 >>> To: ids@iiug.org=20 >>> From: vikas.hivarkar@gmail.com=20 >>> Subject: Fast Unload Load [26269]=20 >>> Date: Mon, 20 Feb 2012 07:39:12 -0500=20 >>>=20 >>> Hello All,=20 >>>=20 >>> Instance_1: 11.50.FC8W2 on HP-Ux on host_1=20 >>> Instance_2: 11.50.FC6W3 on HP-Ux on host_2=20 >>>=20 >>> 3 very big tables needs to be unloaded (with/without hpl(deluxe = only)or=20 >>> external tables) from Instance_1 (host_1) and load the data on to=20 >>> Instance_2=20 >>> (host_2)=20 >>>=20 >>> Not sure if named pipes can work across two different instances on=20= >>> different=20 >>> hosts.=20 >>>=20 >>> Any suggesstion, example how to go about this?=20 >>>=20 >>> Regards,=20 >>> Vikas=20 >>>=20 >>>=20 >>>=20 >>=20 > = **************************************************************************= *****=20 >>> Forum Note: Use "Reply" to post a response in the discussion forum.=20= >>>=20 >>=20 >>=20 >>=20 > = **************************************************************************= *****=20 >> Forum Note: Use "Reply" to post a response in the discussion forum.=20= >>=20 >=20 > --=20 > Paul Watson=20 > Tel: +1 913-674-0360=20 > Mob: +1 913-387-7529=20 > Web: www.oninit.com=20 >=20 > www.advancedatatools.com=20 >=20 > Failure is not as frightening as regret.=20 > If you want to improve, be content to be thought foolish and stupid.=20= >=20 >=20 > = **************************************************************************= *****=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=20= >=20 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to Celso Cabral Coimbra
Paul Watson — — source: IIUG Forums & Mailing Lists

How many are you running concurrently ? Multiple named pipes can be problematic Cheers Paul > We had the same problem this week. > I don't trust on named pipes, I didn't had good experiences. > I'm just coping the files and loading using HPL right now a table with 6 > bi > rows partially in four steps. > > Good luck, > > Celso Cabral Coimbra > Administrador de Banco de Dados > ClearTech Ltda > "Trust at the heart of Communications" > Tel. (11) 3576-4509 > > -----Mensagem original----- > De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Em nome de Jack > Parker > Enviada em: segunda-feira, 20 de fevereiro de 2012 15:16 > Para: ids@iiug.org > Assunto: Re: Fast Unload Load [26274] > > We had problems with cross-server named pipes. One or two of them were = > always shutting down resulting in portions of the data getting lost. I = > am much more in favour of landing on disk just to do a sanity check. > > j. > =20 > On Feb 20, 2012, at 1:12 PM, Paul Watson wrote: > >> The only gotcha on the pipes is to make sure you start the destination = > HPL=20 >> job prior to the source HPL job, apart from that then HPL and pipes >> is=20= > >> trivial=20 >>=20 >> Cheers=20 >> Paul=20 >>=20 >>> Yes, you could create unload fifos on the source, and load fifos on = > the=20 >>> target=20 >>> machine.=20 >>> Hope this link will help you:=20 >>>=20 >>>=20 >> = > http://www.google.com/url?sa=3Dt&rct=3Dj&q=3Dinformix%20external%20tables%= > 20fifo&source=3Dweb&cd=3D2&ved=3D0CDQQFjAB&url=3Dhttp%3A%2F%2Fpublib.bould= > er.ibm.com%2Finfocenter%2Fidshelp%2Fv115%2Ftopic%2Fcom.ibm.admin.doc%2Fids= > _admin_1332.htm&ei=3DyV5CT-OCBcXh0gGg1dW2Bw&usg=3DAFQjCNE6Cbom_hcrt93p3VRo= > qm6YGO2xcQ&cad=3Drja=20 >>>=20 >>> Regards.=20 >>>=20 >>> Alexandre Marini=20 >>> IBM Informix Certified Professional v10 / v11.50 / v11.70=20 >>>=20 >>> IBM Information Management Informix Technical Professional=20 >>>=20 >>> IBM Infosphere DataStage Technical Professional=20 >>> Database Administrator=20 >>>=20 >>>> To: ids@iiug.org=20 >>>> From: vikas.hivarkar@gmail.com=20 >>>> Subject: Fast Unload Load [26269]=20 >>>> Date: Mon, 20 Feb 2012 07:39:12 -0500=20 >>>>=20 >>>> Hello All,=20 >>>>=20 >>>> Instance_1: 11.50.FC8W2 on HP-Ux on host_1=20 >>>> Instance_2: 11.50.FC6W3 on HP-Ux on host_2=20 >>>>=20 >>>> 3 very big tables needs to be unloaded (with/without hpl(deluxe = > only)or=20 >>>> external tables) from Instance_1 (host_1) and load the data on to=20 >>>> Instance_2=20 >>>> (host_2)=20 >>>>=20 >>>> Not sure if named pipes can work across two different instances on=20= > >>>> different=20 >>>> hosts.=20 >>>>=20 >>>> Any suggesstion, example how to go about this?=20 >>>>=20 >>>> Regards,=20 >>>> Vikas=20 >>>>=20 >>>>=20 >>>>=20 >>>=20 >> = > **************************************************************************= > *****=20 >>>> Forum Note: Use "Reply" to post a response in the discussion >>>> forum.=20= > >>>>=20 >>>=20 >>>=20 >>>=20 >> = > **************************************************************************= > *****=20 >>> Forum Note: Use "Reply" to post a response in the discussion forum.=20= > >>>=20 >>=20 >> --=20 >> Paul Watson=20 >> Tel: +1 913-674-0360=20 >> Mob: +1 913-387-7529=20 >> Web: www.oninit.com=20 >>=20 >> www.advancedatatools.com=20 >>=20 >> Failure is not as frightening as regret.=20 >> If you want to improve, be content to be thought foolish and stupid.=20= > >>=20 >>=20 >> = > **************************************************************************= > *****=20 >> Forum Note: Use "Reply" to post a response in the discussion forum.=20= > >>=20 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ******************************************************************************* > 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 www.advancedatatools.com Failure is not as frightening as regret. If you want to improve, be content to be thought foolish and stupid.

↪ replying to Paul Watson
Celso Cabral Coimbra — — source: IIUG Forums & Mailing Lists

I was running 12. So it might be that. Celso Cabral Coimbra Administrador de Banco de Dados ClearTech Ltda "Trust at the heart of Communications" Tel. (11) 3576-4509 -----Mensagem original----- De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Em nome de Paul Watson Enviada em: segunda-feira, 20 de fevereiro de 2012 15:59 Para: ids@iiug.org Assunto: Re: RES: Fast Unload Load [26276] How many are you running concurrently ? Multiple named pipes can be problematic Cheers Paul > We had the same problem this week. > I don't trust on named pipes, I didn't had good experiences. > I'm just coping the files and loading using HPL right now a table with 6 > bi > rows partially in four steps. > > Good luck, > > Celso Cabral Coimbra > Administrador de Banco de Dados > ClearTech Ltda > "Trust at the heart of Communications" > Tel. (11) 3576-4509 > > -----Mensagem original----- > De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Em nome de Jack > Parker > Enviada em: segunda-feira, 20 de fevereiro de 2012 15:16 > Para: ids@iiug.org > Assunto: Re: Fast Unload Load [26274] > > We had problems with cross-server named pipes. One or two of them were = > always shutting down resulting in portions of the data getting lost. I = > am much more in favour of landing on disk just to do a sanity check. > > j. > =20 > On Feb 20, 2012, at 1:12 PM, Paul Watson wrote: > >> The only gotcha on the pipes is to make sure you start the destination = > HPL=20 >> job prior to the source HPL job, apart from that then HPL and pipes >> is=20= > >> trivial=20 >>=20 >> Cheers=20 >> Paul=20 >>=20 >>> Yes, you could create unload fifos on the source, and load fifos on = > the=20 >>> target=20 >>> machine.=20 >>> Hope this link will help you:=20 >>>=20 >>>=20 >> = > http://www.google.com/url?sa=3Dt&rct=3Dj&q=3Dinformix%20external%20tables%= > 20fifo&source=3Dweb&cd=3D2&ved=3D0CDQQFjAB&url=3Dhttp%3A%2F%2Fpublib.bould= > er.ibm.com%2Finfocenter%2Fidshelp%2Fv115%2Ftopic%2Fcom.ibm.admin.doc%2Fids= > _admin_1332.htm&ei=3DyV5CT-OCBcXh0gGg1dW2Bw&usg=3DAFQjCNE6Cbom_hcrt93p3VRo= > qm6YGO2xcQ&cad=3Drja=20 >>>=20 >>> Regards.=20 >>>=20 >>> Alexandre Marini=20 >>> IBM Informix Certified Professional v10 / v11.50 / v11.70=20 >>>=20 >>> IBM Information Management Informix Technical Professional=20 >>>=20 >>> IBM Infosphere DataStage Technical Professional=20 >>> Database Administrator=20 >>>=20 >>>> To: ids@iiug.org=20 >>>> From: vikas.hivarkar@gmail.com=20 >>>> Subject: Fast Unload Load [26269]=20 >>>> Date: Mon, 20 Feb 2012 07:39:12 -0500=20 >>>>=20 >>>> Hello All,=20 >>>>=20 >>>> Instance_1: 11.50.FC8W2 on HP-Ux on host_1=20 >>>> Instance_2: 11.50.FC6W3 on HP-Ux on host_2=20 >>>>=20 >>>> 3 very big tables needs to be unloaded (with/without hpl(deluxe = > only)or=20 >>>> external tables) from Instance_1 (host_1) and load the data on to=20 >>>> Instance_2=20 >>>> (host_2)=20 >>>>=20 >>>> Not sure if named pipes can work across two different instances on=20= > >>>> different=20 >>>> hosts.=20 >>>>=20 >>>> Any suggesstion, example how to go about this?=20 >>>>=20 >>>> Regards,=20 >>>> Vikas=20 >>>>=20 >>>>=20 >>>>=20 >>>=20 >> = > **************************************************************************= > *****=20 >>>> Forum Note: Use "Reply" to post a response in the discussion >>>> forum.=20= > >>>>=20 >>>=20 >>>=20 >>>=20 >> = > **************************************************************************= > *****=20 >>> Forum Note: Use "Reply" to post a response in the discussion forum.=20= > >>>=20 >>=20 >> --=20 >> Paul Watson=20 >> Tel: +1 913-674-0360=20 >> Mob: +1 913-387-7529=20 >> Web: www.oninit.com=20 >>=20 >> www.advancedatatools.com=20 >>=20 >> Failure is not as frightening as regret.=20 >> If you want to improve, be content to be thought foolish and stupid.=20= > >>=20 >>=20 >> = > **************************************************************************= > *****=20 >> Forum Note: Use "Reply" to post a response in the discussion forum.=20= > >>=20 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ******************************************************************************* > 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 www.advancedatatools.com Failure is not as frightening as regret. If you want to improve, be content to be thought foolish and stupid. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to Celso Cabral Coimbra
Paul Watson — — source: IIUG Forums & Mailing Lists

Between two physical servers I tend to run 2 or 3, within a single machine then 3 or 4 ... Cheers Paul > I was running 12. So it might be that. > > Celso Cabral Coimbra > Administrador de Banco de Dados > ClearTech Ltda > "Trust at the heart of Communications" > Tel. (11) 3576-4509 > > -----Mensagem original----- > De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Em nome de Paul > Watson > Enviada em: segunda-feira, 20 de fevereiro de 2012 15:59 > Para: ids@iiug.org > Assunto: Re: RES: Fast Unload Load [26276] > > How many are you running concurrently ? Multiple named pipes can be > problematic > > Cheers > Paul > >> We had the same problem this week. >> I don't trust on named pipes, I didn't had good experiences. >> I'm just coping the files and loading using HPL right now a table with 6 >> bi >> rows partially in four steps. >> >> Good luck, >> >> Celso Cabral Coimbra >> Administrador de Banco de Dados >> ClearTech Ltda >> "Trust at the heart of Communications" >> Tel. (11) 3576-4509 >> >> -----Mensagem original----- >> De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Em nome de Jack >> Parker >> Enviada em: segunda-feira, 20 de fevereiro de 2012 15:16 >> Para: ids@iiug.org >> Assunto: Re: Fast Unload Load [26274] >> >> We had problems with cross-server named pipes. One or two of them were = >> always shutting down resulting in portions of the data getting lost. I = >> am much more in favour of landing on disk just to do a sanity check. >> >> j. >> =20 >> On Feb 20, 2012, at 1:12 PM, Paul Watson wrote: >> >>> The only gotcha on the pipes is to make sure you start the destination >>> = >> HPL=20 >>> job prior to the source HPL job, apart from that then HPL and pipes >>> is=20= >> >>> trivial=20 >>>=20 >>> Cheers=20 >>> Paul=20 >>>=20 >>>> Yes, you could create unload fifos on the source, and load fifos on = >> the=20 >>>> target=20 >>>> machine.=20 >>>> Hope this link will help you:=20 >>>>=20 >>>>=20 >>> = >> http://www.google.com/url?sa=3Dt&rct=3Dj&q=3Dinformix%20external%20tables%= >> 20fifo&source=3Dweb&cd=3D2&ved=3D0CDQQFjAB&url=3Dhttp%3A%2F%2Fpublib.bould= >> er.ibm.com%2Finfocenter%2Fidshelp%2Fv115%2Ftopic%2Fcom.ibm.admin.doc%2Fids= >> _admin_1332.htm&ei=3DyV5CT-OCBcXh0gGg1dW2Bw&usg=3DAFQjCNE6Cbom_hcrt93p3VRo= >> qm6YGO2xcQ&cad=3Drja=20 >>>>=20 >>>> Regards.=20 >>>>=20 >>>> Alexandre Marini=20 >>>> IBM Informix Certified Professional v10 / v11.50 / v11.70=20 >>>>=20 >>>> IBM Information Management Informix Technical Professional=20 >>>>=20 >>>> IBM Infosphere DataStage Technical Professional=20 >>>> Database Administrator=20 >>>>=20 >>>>> To: ids@iiug.org=20 >>>>> From: vikas.hivarkar@gmail.com=20 >>>>> Subject: Fast Unload Load [26269]=20 >>>>> Date: Mon, 20 Feb 2012 07:39:12 -0500=20 >>>>>=20 >>>>> Hello All,=20 >>>>>=20 >>>>> Instance_1: 11.50.FC8W2 on HP-Ux on host_1=20 >>>>> Instance_2: 11.50.FC6W3 on HP-Ux on host_2=20 >>>>>=20 >>>>> 3 very big tables needs to be unloaded (with/without hpl(deluxe = >> only)or=20 >>>>> external tables) from Instance_1 (host_1) and load the data on to=20 >>>>> Instance_2=20 >>>>> (host_2)=20 >>>>>=20 >>>>> Not sure if named pipes can work across two different instances >>>>> on=20= >> >>>>> different=20 >>>>> hosts.=20 >>>>>=20 >>>>> Any suggesstion, example how to go about this?=20 >>>>>=20 >>>>> Regards,=20 >>>>> Vikas=20 >>>>>=20 >>>>>=20 >>>>>=20 >>>>=20 >>> = >> **************************************************************************= >> *****=20 >>>>> Forum Note: Use "Reply" to post a response in the discussion >>>>> forum.=20= >> >>>>>=20 >>>>=20 >>>>=20 >>>>=20 >>> = >> **************************************************************************= >> *****=20 >>>> Forum Note: Use "Reply" to post a response in the discussion >>>> forum.=20= >> >>>>=20 >>>=20 >>> --=20 >>> Paul Watson=20 >>> Tel: +1 913-674-0360=20 >>> Mob: +1 913-387-7529=20 >>> Web: www.oninit.com=20 >>>=20 >>> www.advancedatatools.com=20 >>>=20 >>> Failure is not as frightening as regret.=20 >>> If you want to improve, be content to be thought foolish and >>> stupid.=20= >> >>>=20 >>>=20 >>> = >> **************************************************************************= >> *****=20 >>> Forum Note: Use "Reply" to post a response in the discussion forum.=20= >> >>>=20 >> >> >> > > ******************************************************************************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> >> > > ******************************************************************************* >> 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 > > www.advancedatatools.com > > Failure is not as frightening as regret. > If you want to improve, be content to be thought foolish and stupid. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ******************************************************************************* > 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 www.advancedatatools.com Failure is not as frightening as regret. If you want to improve, be content to be thought foolish and stupid.

↪ replying to Paul Watson
Clive Eisen — — source: IIUG Forums & Mailing Lists

On 20/02/2012 18:59, Paul Watson wrote: > How many are you running concurrently ? Multiple named pipes can be > problematic In what way - can you elaborate please -- Clive

↪ replying to Clive Eisen
Paul Watson — — source: IIUG Forums & Mailing Lists

Never got to the bottom of it, I always assumed its an OS resource type problem but its NOT OS specific. Just always found if you run too many concurrently the process hangs at one end - which end I've never fathomed, a truss/trace on either end says waiting on data. So I know tend to wrap the HPL move in a script that looks at the data volumes on table basis and then rounds robins the HPL script across three named pipes. Just let it go and come back when its emails me to say it complete - trivial Cheers Paul > On 20/02/2012 18:59, Paul Watson wrote: >> How many are you running concurrently ? Multiple named pipes can be >> problematic > > In what way - can you elaborate please > > -- > Clive > > > ******************************************************************************* > 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 www.advancedatatools.com Failure is not as frightening as regret. If you want to improve, be content to be thought foolish and stupid.

↪ replying to Paul Watson
VIKAS HIVARKAR — — source: IIUG Forums & Mailing Lists

Thank you All for the responses, lots to explore, especially "my dbcopy" My 3 tables (each table in seperate dbspace) are of size ~80gigs and dont want to get these on disk. I take a level 0 backup every night with onbar on to tape, just wondering If restore of critical dbspaces and these 3 table dbspaces only could be safe and quicker option? Regards, Vikas ******************************************************************************* Between two physical servers I tend to run 2 or 3, within a single machine then 3 or 4 ... Cheers Paul ******************************************************************************* I was running 12. So it might be that. Celso Cabral Coimbra ******************************************************************************* How many are you running concurrently ? Multiple named pipes can be problematic Cheers Paul ******************************************************************************* We had the same problem this week. I don't trust on named pipes, I didn't had good experiences. I'm just coping the files and loading using HPL right now a table with 6 bi rows partially in four steps. Good luck, Celso Cabral Coimbra ******************************************************************************* We had problems with cross-server named pipes. One or two of them were = always shutting down resulting in portions of the data getting lost. I = am much more in favour of landing on disk just to do a sanity check. j. ******************************************************************************* The only gotcha on the pipes is to make sure you start the destination HPL job prior to the source HPL job, apart from that then HPL and pipes is trivial Cheers Paul ******************************************************************************* Yes, you could create unload fifos on the source, and load fifos on the target machine. Hope this link will help you: http://www.google.com/url?sa=t&rct=j&q=informix%20external%20tables%20fifo&sourc e=web&cd=2&ved=0CDQQFjAB&url=http%3A%2F%2Fpublib.boulder.ibm.com%2Finfocenter%2F idshelp%2Fv115%2Ftopic%2Fcom.ibm.admin.doc%2Fids_admin_1332.htm&ei=yV5CT-OCBcXh0 gGg1dW2Bw&usg=AFQjCNE6Cbom_hcrt93p3VRoqm6YGO2xcQ&cad=rja Regards. Alexandre Marini IBM Informix Certified Professional v10 / v11.50 / v11.70 ******************************************************************************* Wait, copy data from one instance to another is s job for my dbcopy utility combined with the mkdbcopy.awk script in my utils3_ak package. Fastest way. Art ******************************************************************************* Use external tables. They are faster than HP loader and easier to use. My myexport utility can make it as easy as using dbexport BTW. Art ******************************************************************************* Hello All, Instance_1: 11.50.FC8W2 on HP-Ux on host_1 Instance_2: 11.50.FC6W3 on HP-Ux on host_2 3 very big tables needs to be unloaded (with/without hpl(deluxe only)or external tables) from Instance_1 (host_1) and load the data on to Instance_2 (host_2) Not sure if named pipes can work across two different instances on different hosts. Any suggesstion, example how to go about this? Regards, Vikas

↪ replying to VIKAS HIVARKAR
Art Kagel — — source: IIUG Forums & Mailing Lists

That will wipe out the target server. If that's what you want, it will work. Oh, on using dbcopy: If you can partition the data with WHERE clauses, you can run multiple copies of dbcopy each copying a part of the table. This works great especially on a multi-processor machine. I once copied a huge table between two 32 processor machines running 40 copies of dbcopy. Whoosh! (Just watch your logical logs if you do that.) 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 Tue, Feb 21, 2012 at 12:38 AM, VIKAS HIVARKAR <vikas.hivarkar@gmail.com>wrote: > Thank you All for the responses, lots to explore, especially "my dbcopy" > > My 3 tables (each table in seperate dbspace) are of size ~80gigs and dont > want > to get these on disk. > > I take a level 0 backup every night with onbar on to tape, just wondering > If > restore of critical dbspaces and these 3 table dbspaces only could be safe > and > quicker option? > > Regards, > Vikas > > > ******************************************************************************* > Between two physical servers I tend to run 2 or 3, within a single machine > then 3 or 4 ... > > Cheers > Paul > > > > ******************************************************************************* > I was running 12. So it might be that. > > Celso Cabral Coimbra > > > > ******************************************************************************* > How many are you running concurrently ? Multiple named pipes can be > problematic > > Cheers > Paul > > > > ******************************************************************************* > We had the same problem this week. > I don't trust on named pipes, I didn't had good experiences. > I'm just coping the files and loading using HPL right now a table with 6 bi > rows partially in four steps. > > Good luck, > > Celso Cabral Coimbra > > > > ******************************************************************************* > We had problems with cross-server named pipes. One or two of them were = > always shutting down resulting in portions of the data getting lost. I = > am much more in favour of landing on disk just to do a sanity check. > > j. > > > ******************************************************************************* > The only gotcha on the pipes is to make sure you start the destination HPL > job prior to the source HPL job, apart from that then HPL and pipes is > trivial > > Cheers > Paul > > > > ******************************************************************************* > Yes, you could create unload fifos on the source, and load fifos on the > target > machine. > Hope this link will help you: > > > http://www.google.com/url?sa=t&rct=j&q=informix%20external%20tables%20fifo&sourc e=web&cd=2&ved=0CDQQFjAB&url=http%3A%2F%2Fpublib.boulder.ibm.com%2Finfocenter%2F idshelp%2Fv115%2Ftopic%2Fcom.ibm.admin.doc%2Fids_admin_1332.htm&ei=yV5CT-OCBcXh0 gGg1dW2Bw&usg=AFQjCNE6Cbom_hcrt93p3VRoqm6YGO2xcQ&cad=rja > > Regards. > > Alexandre Marini > IBM Informix Certified Professional v10 / v11.50 / v11.70 > > > > ******************************************************************************* > Wait, copy data from one instance to another is s job for my dbcopy utility > combined with the mkdbcopy.awk script in my utils3_ak package. Fastest way. > > Art > > > ******************************************************************************* > Use external tables. They are faster than HP loader and easier to use. My > myexport utility can make it as easy as using dbexport BTW. > > Art > > > ******************************************************************************* > > Hello All, > > Instance_1: 11.50.FC8W2 on HP-Ux on host_1 > Instance_2: 11.50.FC6W3 on HP-Ux on host_2 > > 3 very big tables needs to be unloaded (with/without hpl(deluxe only)or > external tables) from Instance_1 (host_1) and load the data on to > Instance_2 > (host_2) > > Not sure if named pipes can work across two different instances on > different > hosts. > > Any suggesstion, example how to go about this? > > Regards, > Vikas > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --90e6ba6134b817bbbb04b973109e