What is the best tool to unload records from a tab

Hi All, I'll be performing unloading records from a table. What is the fastest way to perform the unloading records? My game plan is : 1. set the database in No Logging Mode 2. Execute "unload to tablename.unl select * from tablename" Is there any way is I can do it faster?

JACK PAPA wrote: > Hi All, > > I'll be performing unloading records from a table. What is the fastest way to > perform the unloading records? > > My game plan is : > > 1. set the database in No Logging Mode > > 2. Execute "unload to tablename.unl select * from tablename" > > Is there any way is I can do it faster? I don't think the logging mode will have any material impact on the UNload. If the table is big, then it might be worth looking into HPL. Why are you unloading the table? -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com I will now proceed to pleasure myself with this fish. -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.

Thanks a lot. I haven't done unloading data using HPL. Most are just unload to tablename.unl select * from tablename; I'm still researching on how to unload data from a table using HPL. BTW, the records is around 100M.

JACK PAPA wrote: > Thanks a lot. I haven't done unloading data using HPL. Most are just unload to > tablename.unl select * from tablename; > > I'm still researching on how to unload data from a table using HPL. BTW, the > records is around 100M. Why, though? Why are you unloading the tables in the first place? -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com I will now proceed to pleasure myself with this fish. -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.

HPLoader is faster. My ul.ec utility (in the utils2_ak package) exports and imports data in binary format which tends to be more compact and so a bit faster than text and it can work remotely unloading to a target machine over the net which can save to time to write the output then read it back to transfer it. Jonathan Leffler's sqlcmd's sqlunload utility is also a bit faster and also can be used remotely. If you are using IDS 11.50.xC6 (or later I guess) you can use an external table to export the data even faster than HPLoader can and using an NFS target file you can unload to a remote machine also. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf 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 Wed, Apr 14, 2010 at 6:05 AM, JACK PAPA <informix2009@gmail.com> wrote: > Hi All, > > I'll be performing unloading records from a table. What is the fastest way > to > perform the unloading records? > > My game plan is : > > 1. set the database in No Logging Mode > > 2. Execute "unload to tablename.unl select * from tablename" > > Is there any way is I can do it faster? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0016e6d2634cf2c0580484322d4b

Hi, tested external tables, but was not able to twist it so, that a fragmented non-external table was read using frag parallelism, which is no problem when using the HPL. Having real fast disks, I therefore stay with HPL & make use of multiple devices in device array. To give you an idea of what I consider go(o)d speed, using 2 RAID10 setups (one for reading, 1 for output) on 2 controllers sitting on 2 different PCI 2.0 x16 connectors: If unload speed is > than 30 GB per min I stop the tweaking. cu, dic_k Art Kagel schrieb: > HPLoader is faster. My ul.ec utility (in the utils2_ak package) exports and > imports data in binary format which tends to be more compact and so a bit > faster than text and it can work remotely unloading to a target machine over > the net which can save to time to write the output then read it back to > transfer it. Jonathan Leffler's sqlcmd's sqlunload utility is also a bit > faster and also can be used remotely. > > If you are using IDS 11.50.xC6 (or later I guess) you can use an external > table to export the data even faster than HPLoader can and using an NFS > target file you can unload to a remote machine also. > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > IIUG Board of Directors (art@iiug.org) > > See you at the 2010 IIUG Informix Conference > April 25-28, 2010 > Overland Park (Kansas City), KS > www.iiug.org/conf > > 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 Wed, Apr 14, 2010 at 6:05 AM, JACK PAPA <informix2009@gmail.com> wrote: > >> Hi All, >> >> I'll be performing unloading records from a table. What is the fastest way >> to >> perform the unloading records? >> >> My game plan is : >> >> 1. set the database in No Logging Mode >> >> 2. Execute "unload to tablename.unl select * from tablename" >> >> Is there any way is I can do it faster? >> >> >> >> > ******************************************************************************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> > > --0016e6d2634cf2c0580484322d4b > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Richard Kofler SOLID STATE EDV Dienstleistungen GmbH Vienna/Austria/Europe

You have to make sure you enable PDQ to get parallelism with the reading of the fragment non-external tables and have the associated onconfig setup (which usually are by default). In addition, with both HPL and external tables you can export to an informix native format which saves you converting to and from ascii when moving data between IDS systems. John F. Miller III STSM, Support Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 04/14/2010 11:35:33 AM: > [image removed] > > Re: What is the best tool to unload records fr.... [19689] > > Richard Kofler > > to: > > ids > > 04/14/2010 11:36 AM > > Sent by: > > ids-bounces@iiug.org > > Please respond to ids > > Hi, > > tested external tables, but was not able to twist it > so, that a fragmented non-external table was read > using frag parallelism, which is no problem when > using the HPL. Having real fast disks, I therefore > stay with HPL & make use of multiple devices in device > array. > To give you an idea of what I consider go(o)d speed, > using 2 RAID10 setups (one for reading, 1 for output) > on 2 controllers sitting on 2 different PCI 2.0 x16 > connectors: If unload speed is > than 30 GB per min > I stop the tweaking. > > cu, dic_k > > Art Kagel schrieb: > > HPLoader is faster. My ul.ec utility (in the utils2_ak package) exports and > > imports data in binary format which tends to be more compact and so a bit > > faster than text and it can work remotely unloading to a target > machine over > > the net which can save to time to write the output then read it back to > > transfer it. Jonathan Leffler's sqlcmd's sqlunload utility is also a bit > > faster and also can be used remotely. > > > > If you are using IDS 11.50.xC6 (or later I guess) you can use an external > > table to export the data even faster than HPLoader can and using an NFS > > target file you can unload to a remote machine also. > > > > Art > > > > Art S. Kagel > > Advanced DataTools (www.advancedatatools.com) > > IIUG Board of Directors (art@iiug.org) > > > > See you at the 2010 IIUG Informix Conference > > April 25-28, 2010 > > Overland Park (Kansas City), KS > > www.iiug.org/conf > > > > 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 Wed, Apr 14, 2010 at 6:05 AM, JACK PAPA <informix2009@gmail.com> wrote: > > > >> Hi All, > >> > >> I'll be performing unloading records from a table. What is the fastest way > >> to > >> perform the unloading records? > >> > >> My game plan is : > >> > >> 1. set the database in No Logging Mode > >> > >> 2. Execute "unload to tablename.unl select * from tablename" > >> > >> Is there any way is I can do it faster? > >> > >> > >> > >> > > > ******************************************************************************* > >> Forum Note: Use "Reply" to post a response in the discussion forum. > >> > >> > > > > --0016e6d2634cf2c0580484322d4b > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Richard Kofler > SOLID STATE EDV > Dienstleistungen GmbH > Vienna/Austria/Europe > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

Hi John, thank you for mentioning PDQPRIORITY. But my English is rusty and I was not precise enough: What I was not able to archive is to emulate HPLs ability to read AND write in parallel, when input table is fragmented and when using a device array having an appropriate numer of devices. From IDS V11.50.xC4 docs, HPL Users Guide, pg 6-1: When the HPL unloads data, it assigns records to the devices of a device array in a round-robin fashion. And exacly this behaviour ends up as a defacto parallel rading AND parallel writing, speedwise - if buffers are largish (using -a <iobufsize> & -b <bufsize> on the command line or configure AIOBUFERS, AIOBUFSIZE & STRMBUFERS, STRMBUFFSIZE with appropriate values) and if using raw devices anf if your CPUs are fast. BTW, in doing HPL unloads the difference of raw vs. every other type of non raw disks shows the biggest difference in read speed during my testing in the last decade. It is also a VERY GOOD test when evaluating I/O subsystems how much seq read speed they will give. Especially, when the cache of the subsystem is emptied in a few seconds and the bitter truth is visible ;) Or when you read a table fragged 64-way. But this is another subject. Testing external tables as an unload target, I was only able to get near of what I wanted to see when I fragment avoidance on the input table and invoked paralel unloads for every fragment. But failed to fine tune and archive what I can do using -a, -b on onpload on the command line. Which in fact such unload faster, if pagesize is large and not 2 KB, by reducing number of start I/Os to 50% on the ouput side when table page size is 4 KB instead of 2 KB, and it scales further as expected. [ Now why so much details? When using SSDs, and this is what I do nowadays, the number of start-I/Os for writes is a thing to watch! ] dic_k John Miller iii schrieb: > You have to make sure you enable PDQ to get parallelism with > the reading of the fragment non-external tables and have the > associated onconfig setup (which usually are by default). > > In addition, with both HPL and external tables you can export to > an informix native format which saves you converting to > and from ascii when moving data between IDS systems. > > John F. Miller III > STSM, Support Architect > miller3@us.ibm.com > 503-578-5645 > IBM Informix Dynamic Server (IDS) > > ids-bounces@iiug.org wrote on 04/14/2010 11:35:33 AM: > >> [image removed] >> >> Re: What is the best tool to unload records fr.... [19689] >> >> Richard Kofler >> >> to: >> >> ids >> >> 04/14/2010 11:36 AM >> >> Sent by: >> >> ids-bounces@iiug.org >> >> Please respond to ids >> >> Hi, >> >> tested external tables, but was not able to twist it >> so, that a fragmented non-external table was read >> using frag parallelism, which is no problem when >> using the HPL. Having real fast disks, I therefore >> stay with HPL & make use of multiple devices in device >> array. >> To give you an idea of what I consider go(o)d speed, >> using 2 RAID10 setups (one for reading, 1 for output) >> on 2 controllers sitting on 2 different PCI 2.0 x16 >> connectors: If unload speed is > than 30 GB per min >> I stop the tweaking. >> >> cu, dic_k >> >> Art Kagel schrieb: >>> HPLoader is faster. My ul.ec utility (in the utils2_ak package) exports > and >>> imports data in binary format which tends to be more compact and so a > bit >>> faster than text and it can work remotely unloading to a target >> machine over >>> the net which can save to time to write the output then read it back to > >>> transfer it. Jonathan Leffler's sqlcmd's sqlunload utility is also a > bit >>> faster and also can be used remotely. >>> >>> If you are using IDS 11.50.xC6 (or later I guess) you can use an > external >>> table to export the data even faster than HPLoader can and using an NFS > >>> target file you can unload to a remote machine also. >>> >>> Art >>> >>> Art S. Kagel >>> Advanced DataTools (www.advancedatatools.com) >>> IIUG Board of Directors (art@iiug.org) >>> >>> See you at the 2010 IIUG Informix Conference >>> April 25-28, 2010 >>> Overland Park (Kansas City), KS >>> www.iiug.org/conf >>> >>> 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 Wed, Apr 14, 2010 at 6:05 AM, JACK PAPA <informix2009@gmail.com> > wrote: >>>> Hi All, >>>> >>>> I'll be performing unloading records from a table. What is the fastest > way >>>> to >>>> perform the unloading records? >>>> >>>> My game plan is : >>>> >>>> 1. set the database in No Logging Mode >>>> >>>> 2. Execute "unload to tablename.unl select * from tablename" >>>> >>>> Is there any way is I can do it faster? >>>> >>>> >>>> >>>> > > ******************************************************************************* > >>>> Forum Note: Use "Reply" to post a response in the discussion forum. >>>> >>>> >>> --0016e6d2634cf2c0580484322d4b >>> >>> >>> > > ******************************************************************************* > >>> Forum Note: Use "Reply" to post a response in the discussion forum. >>> >>> >> -- >> Richard Kofler >> SOLID STATE EDV >> Dienstleistungen GmbH >> Vienna/Austria/Europe >> >> >> > > ******************************************************************************* > >> Forum Note: Use "Reply" to post a response in the discussion forum. >> > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Richard Kofler SOLID STATE EDV Dienstleistungen GmbH Vienna/Austria/Europe

Hi, There will be purging that needs to be done...Before doing that, I would like to unload the data first. Just to make sure it can be returned if needed.

Hi, if you have enough space in the database you can duplicate the data into a copy of the original table (you can define that table as raw table). The insert into <save_table> select * from <orig_table> will be faster than any unload (unless you have binary/text/blob columns - they can slow down the whole process). Afterwards you could unload the data from the copied table for longer storage - without runtime restrictions. Regards, Andreas > ------------------------------------------- SPAR Österreichische Warenhandels-AG Hauptzentrale A - 5015 Salzburg, Europastrasse 3 FN 34170 a Tel: +43 662 4470 84923 Fax: Mobile: +43 664 6259575 E-Mail: Andreas.KUTSCHE@spar.at Internet: http://www.spar.at Wichtiger Hinweis: Der Inhalt dieser E-Mail kann vertrauliche und rechtlich geschützte Informationen, insbesondere Betriebs- oder Geschäftsgeheimnisse, enthalten, zu deren Geheimhaltung der Empfänger verpflichtet ist. Die Informationen in dieser E-Mail sind ausschließlich für den Adressaten bestimmt. Sollten Sie die E-Mail irrtümlich erhalten haben so ersuchen wir Sie, die Nachricht von Ihrem System zu löschen und sich mit uns in Verbindung zu setzen. Über das Internet versandte E-Mails können leicht manipuliert oder unter fremdem Namen erstellt werden. Daher schließen wir die rechtliche Verbindlichkeit der in dieser Nachricht enthaltenen Informationen aus. Der Inhalt der E-Mail ist nur rechtsverbindlich, wenn er von uns schriftlich bestätigt und gezeichnet wird. Sollte trotz der von uns verwendeten Virus-Schutzprogramme durch die Zusendung von E-Mails ein Virus in Ihre Systeme gelangen, haften wir nicht für evtl. hieraus entstehende Schäden. Wir danken für Ihr Verständnis. Important notice: The contents of this e-mail may contain confidential and legally protected information that is in particular related to operational and trade secrets, which the recipient is obliged to treat as confidential. The information in this e-mail is made available exclusively for use by the addressee. In the event that the e-mail may have been sent to you in error, we would ask you to kindly delete this communication from your system and to contact us. E-mails sent via the Internet can be easily manipulated or sent out under someone else's name. We therefore do not accept legal liability for the information contained in this communication. The contents of the e-mail are only legally binding if they have been confirmed and signed by us in writing. If, in spite of our using Antivirus protection software, a virus may have penetrated your system through the sending of this e-mail, we do not accept liability for any damage that may possibly arise as a result of this. We trust that you appreciate our position. ------------------------------------------- -----Ursprüngliche Nachricht----- > Von: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Im Auftrag von > JACK PAPA > Gesendet: Donnerstag, 15. April 2010 04:28 > An: ids@iiug.org > Betreff: Re: What is the best tool to unload records from a [19694] > > Hi, > > There will be purging that needs to be done...Before doing that, I would > like > to unload the data first. Just to make sure it can be returned if needed. > > > ************************************************************************** > ***** > Forum Note: Use "Reply" to post a response in the discussion forum.

Just to let you know external tables can both read and write records in parallel. You will need to ensure a few things are setup, some are the same as HPL. 1. The table you are unloading is fragmented 2. When executing the insert into select from statement make sure PDQ is on. This is required for both the insert side and read side parallelism. 3. When creating the external table make sure you specific multiple output devices. CREATE EXTERNAL TABLE unload_cust SAMEAS customer USING ( DATAFILES ('DISK:/tmp/unload_file1.unl','DISK:/tmp/unload_file2.unl','DISK:/tmp/unload_fil e3.unl'), other options..... ); John F. Miller III STSM, Support Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 04/14/2010 12:23:25 PM: > [image removed] > > Re: What is the best tool to unload records fr.... [19691] > > Richard Kofler > > to: > > ids > > 04/14/2010 12:24 PM > > Sent by: > > ids-bounces@iiug.org > > Please respond to ids > > Hi John, > > thank you for mentioning PDQPRIORITY. > > But my English is rusty and I was not precise enough: > What I was not able to archive is to emulate HPLs ability to > read AND write in parallel, when input table is fragmented and > when using a device array having an appropriate numer of devices. > > >From IDS V11.50.xC4 docs, HPL Users Guide, pg 6-1: > When the HPL unloads data, it assigns records to the devices of > a device array in a round-robin fashion. > > And exacly this behaviour ends up as a defacto parallel rading > AND parallel writing, speedwise - if buffers are largish > (using -a <iobufsize> & -b <bufsize> on the command line or configure > AIOBUFERS, AIOBUFSIZE & STRMBUFERS, STRMBUFFSIZE with appropriate values) > and if using raw devices > anf if your CPUs are fast. > > BTW, in doing HPL unloads the difference of raw vs. every other type > of non raw disks shows the biggest difference in read speed during > my testing in the last decade. It is also a VERY GOOD test when > evaluating I/O subsystems how much seq read speed they will give. > Especially, when the cache of the subsystem is emptied in a few seconds > and the bitter truth is visible ;) Or when you read a table fragged 64-way. > But this is another subject. > > Testing external tables as an unload target, I was only able to get > near of what I wanted to see when I fragment avoidance on the input table and > invoked paralel unloads for every fragment. But failed to fine tune and > archive what I can do using -a, -b on onpload on the command line. > Which in fact such unload faster, if pagesize is large and not 2 KB, by > reducing number of start I/Os to 50% on the ouput side when table > page size is > 4 KB instead of 2 KB, and it scales further as expected. > > [ Now why so much details? When using SSDs, and this is what I do nowadays, > the number of start-I/Os for writes is a thing to watch! ] > > dic_k > > John Miller iii schrieb: > > You have to make sure you enable PDQ to get parallelism with > > the reading of the fragment non-external tables and have the > > associated onconfig setup (which usually are by default). > > > > In addition, with both HPL and external tables you can export to > > an informix native format which saves you converting to > > and from ascii when moving data between IDS systems. > > > > John F. Miller III > > STSM, Support Architect > > miller3@us.ibm.com > > 503-578-5645 > > IBM Informix Dynamic Server (IDS) > > > > ids-bounces@iiug.org wrote on 04/14/2010 11:35:33 AM: > > > >> [image removed] > >> > >> Re: What is the best tool to unload records fr.... [19689] > >> > >> Richard Kofler > >> > >> to: > >> > >> ids > >> > >> 04/14/2010 11:36 AM > >> > >> Sent by: > >> > >> ids-bounces@iiug.org > >> > >> Please respond to ids > >> > >> Hi, > >> > >> tested external tables, but was not able to twist it > >> so, that a fragmented non-external table was read > >> using frag parallelism, which is no problem when > >> using the HPL. Having real fast disks, I therefore > >> stay with HPL & make use of multiple devices in device > >> array. > >> To give you an idea of what I consider go(o)d speed, > >> using 2 RAID10 setups (one for reading, 1 for output) > >> on 2 controllers sitting on 2 different PCI 2.0 x16 > >> connectors: If unload speed is > than 30 GB per min > >> I stop the tweaking. > >> > >> cu, dic_k > >> > >> Art Kagel schrieb: > >>> HPLoader is faster. My ul.ec utility (in the utils2_ak package) exports > > and > >>> imports data in binary format which tends to be more compact and so a > > bit > >>> faster than text and it can work remotely unloading to a target > >> machine over > >>> the net which can save to time to write the output then read it back to > > > >>> transfer it. Jonathan Leffler's sqlcmd's sqlunload utility is also a > > bit > >>> faster and also can be used remotely. > >>> > >>> If you are using IDS 11.50.xC6 (or later I guess) you can use an > > external > >>> table to export the data even faster than HPLoader can and using an NFS > > > >>> target file you can unload to a remote machine also. > >>> > >>> Art > >>> > >>> Art S. Kagel > >>> Advanced DataTools (www.advancedatatools.com) > >>> IIUG Board of Directors (art@iiug.org) > >>> > >>> See you at the 2010 IIUG Informix Conference > >>> April 25-28, 2010 > >>> Overland Park (Kansas City), KS > >>> www.iiug.org/conf > >>> > >>> 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 Wed, Apr 14, 2010 at 6:05 AM, JACK PAPA <informix2009@gmail.com> > > wrote: > >>>> Hi All, > >>>> > >>>> I'll be performing unloading records from a table. What is the fastest > > way > >>>> to > >>>> perform the unloading records? > >>>> > >>>> My game plan is : > >>>> > >>>> 1. set the database in No Logging Mode > >>>> > >>>> 2. Execute "unload to tablename.unl select * from tablename" > >>>> > >>>> Is there any way is I can do it faster? > >>>> > >>>> > >>>> > >>>> > > > > > ******************************************************************************* > > > >>>> Forum Note: Use "Reply" to post a response in the discussion forum. > >>>> > >>>> > >>> --0016e6d2634cf2c0580484322d4b > >>> > >>> > >>> > > > > > ******************************************************************************* > > > >>> Forum Note: Use "Rep

Thank you, John - this is great news! dic_k John Miller iii schrieb: > Just to let you know external tables can both read and write records > in parallel. You will need to ensure a few things are setup, some are > the same as HPL. > > 1. The table you are unloading is fragmented > > 2. When executing the insert into select from statement make sure PDQ is > on. This > > is required for both the insert side and read side parallelism. > > 3. When creating the external table make sure you specific multiple output > devices. > > CREATE EXTERNAL TABLE unload_cust SAMEAS customer > > USING > > ( > > DATAFILES > > ('DISK:/tmp/unload_file1.unl','DISK:/tmp/unload_file2.unl','DISK:/tmp/unload_fil e3.unl'), > > other options..... > > ); > > John F. Miller III > STSM, Support Architect > miller3@us.ibm.com > 503-578-5645 > IBM Informix Dynamic Server (IDS) > > ids-bounces@iiug.org wrote on 04/14/2010 12:23:25 PM: > >> [image removed] >> >> Re: What is the best tool to unload records fr.... [19691] >> >> Richard Kofler >> >> to: >> >> ids >> >> 04/14/2010 12:24 PM >> >> Sent by: >> >> ids-bounces@iiug.org >> >> Please respond to ids >> >> Hi John, >> >> thank you for mentioning PDQPRIORITY. >> >> But my English is rusty and I was not precise enough: >> What I was not able to archive is to emulate HPLs ability to >> read AND write in parallel, when input table is fragmented and >> when using a device array having an appropriate numer of devices. >> >> >From IDS V11.50.xC4 docs, HPL Users Guide, pg 6-1: >> When the HPL unloads data, it assigns records to the devices of >> a device array in a round-robin fashion. >> >> And exacly this behaviour ends up as a defacto parallel rading >> AND parallel writing, speedwise - if buffers are largish >> (using -a <iobufsize> & -b <bufsize> on the command line or configure >> AIOBUFERS, AIOBUFSIZE & STRMBUFERS, STRMBUFFSIZE with appropriate values) > >> and if using raw devices >> anf if your CPUs are fast. >> >> BTW, in doing HPL unloads the difference of raw vs. every other type >> of non raw disks shows the biggest difference in read speed during >> my testing in the last decade. It is also a VERY GOOD test when >> evaluating I/O subsystems how much seq read speed they will give. >> Especially, when the cache of the subsystem is emptied in a few seconds >> and the bitter truth is visible ;) Or when you read a table fragged > 64-way. >> But this is another subject. >> >> Testing external tables as an unload target, I was only able to get >> near of what I wanted to see when I fragment avoidance on the input table > and >> invoked paralel unloads for every fragment. But failed to fine tune and >> archive what I can do using -a, -b on onpload on the command line. >> Which in fact such unload faster, if pagesize is large and not 2 KB, by >> reducing number of start I/Os to 50% on the ouput side when table >> page size is >> 4 KB instead of 2 KB, and it scales further as expected. >> >> [ Now why so much details? When using SSDs, and this is what I do > nowadays, >> the number of start-I/Os for writes is a thing to watch! ] >> >> dic_k >> >> John Miller iii schrieb: >>> You have to make sure you enable PDQ to get parallelism with >>> the reading of the fragment non-external tables and have the >>> associated onconfig setup (which usually are by default). >>> >>> In addition, with both HPL and external tables you can export to >>> an informix native format which saves you converting to >>> and from ascii when moving data between IDS systems. >>> >>> John F. Miller III >>> STSM, Support Architect >>> miller3@us.ibm.com >>> 503-578-5645 >>> IBM Informix Dynamic Server (IDS) >>> >>> ids-bounces@iiug.org wrote on 04/14/2010 11:35:33 AM: >>> >>>> [image removed] >>>> >>>> Re: What is the best tool to unload records fr.... [19689] >>>> >>>> Richard Kofler >>>> >>>> to: >>>> >>>> ids >>>> >>>> 04/14/2010 11:36 AM >>>> >>>> Sent by: >>>> >>>> ids-bounces@iiug.org >>>> >>>> Please respond to ids >>>> >>>> Hi, >>>> >>>> tested external tables, but was not able to twist it >>>> so, that a fragmented non-external table was read >>>> using frag parallelism, which is no problem when >>>> using the HPL. Having real fast disks, I therefore >>>> stay with HPL & make use of multiple devices in device >>>> array. >>>> To give you an idea of what I consider go(o)d speed, >>>> using 2 RAID10 setups (one for reading, 1 for output) >>>> on 2 controllers sitting on 2 different PCI 2.0 x16 >>>> connectors: If unload speed is > than 30 GB per min >>>> I stop the tweaking. >>>> >>>> cu, dic_k >>>> >>>> Art Kagel schrieb: >>>>> HPLoader is faster. My ul.ec utility (in the utils2_ak package) > exports >>> and >>>>> imports data in binary format which tends to be more compact and so a > >>> bit >>>>> faster than text and it can work remotely unloading to a target >>>> machine over >>>>> the net which can save to time to write the output then read it back > to >>>>> transfer it. Jonathan Leffler's sqlcmd's sqlunload utility is also a >>> bit >>>>> faster and also can be used remotely. >>>>> >>>>> If you are using IDS 11.50.xC6 (or later I guess) you can use an >>> external >>>>> table to export the data even faster than HPLoader can and using an > NFS >>>>> target file you can unload to a remote machine also. >>>>> >>>>> Art >>>>> >>>>> Art S. Kagel >>>>> Advanced DataTools (www.advancedatatools.com) >>>>> IIUG Board of Directors (art@iiug.org) >>>>> >>>>> See you at the 2010 IIUG Informix Conference >>>>> April 25-28, 2010 >>>>> Overland Park (Kansas City), KS >>>>> www.iiug.org/conf >>>>> >>>>> 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 Wed, Apr 14, 2010 at 6:05 AM, JACK PAPA <informix2009@gmail.com> >>> wrote: >>>>>> Hi All, >>>>>> >>>>>> I'll be performing unloading records from a table. What is the > fastest >>> way >>>>>> to >>>>>> perform the unloading records? >>>>>> >>>>>> My game plan is : >>>>>> >>>>>> 1. set the database in No Logging Mode >>>>>> >>>>>> 2. Execute "unload to tablename.unl select * from tablename" >>>>>> >>>>>> Is there any way is I can do it faster? >>>>>> >>>>>> >>>>>> >>>>>> >>> > > ******************************************************************************* > >>>>>> Forum Note: Use "Reply" to post a response in the discussion forum. >>>>>> >>>>>> >>>>> --0016e6d2634cf2