External Table fun

Dunno if anyone will find this useful or not - but after the conference I started messing with external table stuff in v11.5 for unloads and loads, using a pipe. My unloads are running 25% faster using the external tables than using HPL. Here is the script I am using: #!/usr/bin/ksh DBNAME=$1 TABLE=$2 EXTABLE=ext_${TABLE} if [ $# -ne 2 ] then echo "Two args required, database and table" exit fi PIPEFILE=/tmp/${TABLE}.pip COMPRESS_SCRIPT=/tmp/compress_${TABLE}.sh ZIPDONE=/tmp/${TABLE}.zipdone rm $PIPEFILE mknod ${PIPEFILE} p echo "compress -f -c <${PIPEFILE} >/tmp/${TABLE}.unl.gz " > ${COMPRESS_SCRIPT} echo "echo return code from compress=$?" >> ${COMPRESS_SCRIPT} echo "touch ${ZIPDONE}" >> ${COMPRESS_SCRIPT} chmod 770 ${COMPRESS_SCRIPT} dbaccess ${DBNAME} - <<EOT! create external table ${EXTABLE} sameas ${TABLE} using (DATAFILES ("PIPE:${PIPEFILE}")); EOT! dbaccess ${DBNAME} - <<EOT! insert into ${EXTABLE} select * from ${TABLE}!${COMPRESS_SCRIPT} & ; EOT!

Same experience here Mike. The next release of myexport/myimport supports external tables as a vehicle for moving data into and out of the engine. I'm still beta testing the new release, so anyone who wants a copy please let me know. 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 Thu, Apr 29, 2010 at 2:45 PM, MIKE MAGIE <jmmagie@yahoo.com> wrote: > Dunno if anyone will find this useful or not - but after the conference I > started messing with external table stuff in v11.5 for unloads and loads, > using a pipe. My unloads are running 25% faster using the external tables > than > using HPL. Here is the script I am using: > > #!/usr/bin/ksh > DBNAME=$1 > TABLE=$2 > EXTABLE=ext_${TABLE} > > if [ $# -ne 2 ] > then > > echo "Two args required, database and table" > > exit > fi > > PIPEFILE=/tmp/${TABLE}.pip > COMPRESS_SCRIPT=/tmp/compress_${TABLE}.sh > ZIPDONE=/tmp/${TABLE}.zipdone > rm $PIPEFILE > > mknod ${PIPEFILE} p > > echo "compress -f -c <${PIPEFILE} >/tmp/${TABLE}.unl.gz " > > ${COMPRESS_SCRIPT} > echo "echo return code from compress=$?" >> ${COMPRESS_SCRIPT} > echo "touch ${ZIPDONE}" >> ${COMPRESS_SCRIPT} > chmod 770 ${COMPRESS_SCRIPT} > > dbaccess ${DBNAME} - <<EOT! > create external table ${EXTABLE} > sameas ${TABLE} > using (DATAFILES ("PIPE:${PIPEFILE}")); > EOT! > > dbaccess ${DBNAME} - <<EOT! > insert into ${EXTABLE} > select * from ${TABLE}> !${COMPRESS_SCRIPT} & > ; > EOT! > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001636e0b6b34682ca0485650b80

May need to get another topic for next year's IIUG conference, then =2E =2E= =2E 8-)=0D=0A=0D=0AJohn Carlson=0D=0A=0D=0A=0D=0A-----Original Message----= -=0D=0AFrom: ids-bounces@iiug=2Eorg [mailto:ids-bounces@iiug=2Eorg] On Beha= lf Of Art Kagel=0D=0ASent: Thursday, April 29, 2010 3:22 PM=0D=0ATo: ids@ii= ug=2Eorg=0D=0ASubject: Re: External Table fun [19941]=0D=0A=0D=0ASame exper= ience here Mike=2E The next release of myexport/myimport supports=0D=0Aexte= rnal tables as a vehicle for moving data into and out of the engine=2E=0D= =0AI'm still beta testing the new release, so anyone who wants a copy pleas= e=0D=0Alet me know=2E=0D=0A=0D=0AArt=0D=0A=0D=0AArt S=2E Kagel=0D=0AAdvance= d DataTools (www=2Eadvancedatatools=2Ecom)=0D=0AIIUG Board of Directors (ar= t@iiug=2Eorg)=0D=0A=0D=0ASee you at the 2010 IIUG Informix Conference=0D=0A= April 25-28, 2010=0D=0AOverland Park (Kansas City), KS=0D=0Awww=2Eiiug=2Eor= g/conf=0D=0A=0D=0ADisclaimer: Please keep in mind that my own opinions are = my own opinions and=0D=0Ado not reflect on my employer, Advanced DataTools,= the IIUG, nor any other=0D=0Aorganization with which I am associated eithe= r explicitly, implicitly, or by=0D=0Ainference=2E Neither do those opinions= reflect those of other individuals=0D=0Aaffiliated with any entity with wh= ich I am affiliated nor those of the=0D=0Aentities themselves=2E=0D=0A=0D= =0AOn Thu, Apr 29, 2010 at 2:45 PM, MIKE MAGIE <jmmagie@yahoo=2Ecom> wrote:= =0D=0A=0D=0A> Dunno if anyone will find this useful or not - but after the = conference I=0D=0A> started messing with external table stuff in v11=2E5 fo= r unloads and loads,=0D=0A> using a pipe=2E My unloads are running 25% fast= er using the external tables=0D=0A> than=0D=0A> using HPL=2E Here is the sc= ript I am using:=0D=0A>=0D=0A> #!/usr/bin/ksh=0D=0A> DBNAME=3D$1=0D=0A> TAB= LE=3D$2=0D=0A> EXTABLE=3Dext_${TABLE}=0D=0A>=0D=0A> if [ $# -ne 2 ]=0D=0A> = then=0D=0A>=0D=0A> echo "Two args required, database and table"=0D=0A>=0D= =0A> exit=0D=0A> fi=0D=0A>=0D=0A> PIPEFILE=3D/tmp/${TABLE}=2Epip=0D=0A> COM= PRESS_SCRIPT=3D/tmp/compress_${TABLE}=2Esh=0D=0A> ZIPDONE=3D/tmp/${TABLE}= =2Ezipdone=0D=0A> rm $PIPEFILE=0D=0A>=0D=0A> mknod ${PIPEFILE} p=0D=0A>=0D= =0A> echo "compress -f -c <${PIPEFILE} >/tmp/${TABLE}=2Eunl=2Egz " >=0D=0A>= ${COMPRESS_SCRIPT}=0D=0A> echo "echo return code from compress=3D$?" >> ${= COMPRESS_SCRIPT}=0D=0A> echo "touch ${ZIPDONE}" >> ${COMPRESS_SCRIPT}=0D=0A= > chmod 770 ${COMPRESS_SCRIPT}=0D=0A>=0D=0A> dbaccess ${DBNAME} - <<EOT!=0D= =0A> create external table ${EXTABLE}=0D=0A> sameas ${TABLE}=0D=0A> using (= DATAFILES ("PIPE:${PIPEFILE}"));=0D=0A> EOT!=0D=0A>=0D=0A> dbaccess ${DBNAM= E} - <<EOT!=0D=0A> insert into ${EXTABLE}=0D=0A> select * from ${TABLE}=0D= =0A> !${COMPRESS_SCRIPT} &=0D=0A> ;=0D=0A> EOT!=0D=0A>=0D=0A>=0D=0A>=0D=0A>= =0D=0A*********************************************************************= **********=0D=0A> Forum Note: Use "Reply" to post a response in the discuss= ion forum=2E=0D=0A>=0D=0A>=0D=0A=0D=0A--001636e0b6b34682ca0485650b80=0D=0A= =0D=0A=0D=0A***************************************************************= ****************=0D=0A Forum Note: Use "Reply" to post a response in the d= iscussion forum=2E=0D=0A=0D=0A=0D=0AThe information in this Internet Email = is confidential and may be legally privileged=2E It is intended solely for = the addressee=2E Access to this Email by anyone else is unauthorized=2E If = you are not the intended recipient, any disclosure, copying, distribution o= r any action taken or omitted to be taken in reliance on it, is prohibited = and may be unlawful=2E When addressed to our clients any opinions or advice= contained in this Email are subject to the terms and conditions expressed = in any applicable governing The Home Depot terms of business or client enga= gement letter=2E The Home Depot disclaims all responsibility and liability = for the accuracy and content of this attachment and for any damages or loss= es arising from any inaccuracies, errors, viruses, e=2Eg=2E, worms, trojan = horses, etc=2E, or other items of a destructive nature, which may be contai= ned in this attachment and shall not be liable for direct, indirect, conseq= uential or special damages in connection with this e-mail message or its at= tachment=2E=0D=0A=0D=0A-----------------------------------------=0D=0AThe i= nformation contained in this e-mail and any attached documents=0Amay contai= n information that is confidential or otherwise protected=0Afrom disclosure= =2E If you are not the intended recipient of this=0Amessage, or if this mes= sage has been sent to you in error, please=0Aimmediately alert the sender b= y reply e-mail and then delete this=0Amessage, including any attachments=2E= Any dissemination, distribution=0Aor other use of the contents of this mes= sage by anyone other than=0Athe intended recipient is strictly prohibited= =2E

Hi Mike, Thanks for sharing this with us. I tried to attempt the same but getting an error. I am using Informix IDS version 11.50.FCWE. Is this option available in later version than the one I mentioned here? Thanks in advance. -Dharmendra > To: ids@iiug.org > From: jmmagie@yahoo.com > Subject: External Table fun [19940]. > Date: Thu, 29 Apr 2010 14:45:05 -0400 > > Dunno if anyone will find this useful or not - but after the conference I > started messing with external table stuff in v11.5 for unloads and loads, > using a pipe. My unloads are running 25% faster using the external tables than > using HPL. Here is the script I am using: > > #!/usr/bin/ksh > DBNAME=$1 > TABLE=$2 > EXTABLE=ext_${TABLE} > > if [ $# -ne 2 ] > then > > echo "Two args required, database and table" > > exit > fi > > PIPEFILE=/tmp/${TABLE}.pip > COMPRESS_SCRIPT=/tmp/compress_${TABLE}.sh > ZIPDONE=/tmp/${TABLE}.zipdone > rm $PIPEFILE > > mknod ${PIPEFILE} p > > echo "compress -f -c <${PIPEFILE} >/tmp/${TABLE}.unl.gz " > ${COMPRESS_SCRIPT} > echo "echo return code from compress=$?" >> ${COMPRESS_SCRIPT} > echo "touch ${ZIPDONE}" >> ${COMPRESS_SCRIPT} > chmod 770 ${COMPRESS_SCRIPT} > > dbaccess ${DBNAME} - <<EOT! > create external table ${EXTABLE} > sameas ${TABLE} > using (DATAFILES ("PIPE:${PIPEFILE}")); > EOT! > > dbaccess ${DBNAME} - <<EOT! > insert into ${EXTABLE} > select * from ${TABLE}> !${COMPRESS_SCRIPT} & > ; > EOT! > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > _________________________________________________________________ Hotmail has tools for the New Busy. Search, chat and e-mail from your inbox. http://www.windowslive.com/campaign/thenewbusy?ocid=PID28326::T:WLMTAGL:ON:WL:en -US:WM_HMP:042010_1

Load and unload using external tables are a new feature added to 11.50.xC6. It looks like you're running Workgroup Edition but you didn't list what fixpack you are trying this on. I hope this is just a case of you running a version prior to xC6 and not a case of this feature is only available in Enterprise Edition because that would make me sad. The feature is listed in both the EE and WE release notes. Andrew

External tables are first available in 11.50xC6 and later, for workgroup edition (which you have) that would be 11.50.FC6WE. If you want to upgrade to the latest you need to get 11.50.FC6WEW3 or later. 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 Fri, Apr 30, 2010 at 10:50 PM, dharmendra sharma < dharmendrasharma@hotmail.com> wrote: > Hi Mike, > > Thanks for sharing this with us. > > I tried to attempt the same but getting an error. I am using Informix IDS > version 11.50.FCWE. > > Is this option available in later version than the one I mentioned here? > > Thanks in advance. > > -Dharmendra > > > To: ids@iiug.org > > From: jmmagie@yahoo.com > > Subject: External Table fun [19940]. > > Date: Thu, 29 Apr 2010 14:45:05 -0400 > > > > Dunno if anyone will find this useful or not - but after the conference I > > started messing with external table stuff in v11.5 for unloads and loads, > > using a pipe. My unloads are running 25% faster using the external tables > than > > using HPL. Here is the script I am using: > > > > #!/usr/bin/ksh > > DBNAME=$1 > > TABLE=$2 > > EXTABLE=ext_${TABLE} > > > > if [ $# -ne 2 ] > > then > > > > echo "Two args required, database and table" > > > > exit > > fi > > > > PIPEFILE=/tmp/${TABLE}.pip > > COMPRESS_SCRIPT=/tmp/compress_${TABLE}.sh > > ZIPDONE=/tmp/${TABLE}.zipdone > > rm $PIPEFILE > > > > mknod ${PIPEFILE} p > > > > echo "compress -f -c <${PIPEFILE} >/tmp/${TABLE}.unl.gz " > > ${COMPRESS_SCRIPT} > > echo "echo return code from compress=$?" >> ${COMPRESS_SCRIPT} > > echo "touch ${ZIPDONE}" >> ${COMPRESS_SCRIPT} > > chmod 770 ${COMPRESS_SCRIPT} > > > > dbaccess ${DBNAME} - <<EOT! > > create external table ${EXTABLE} > > sameas ${TABLE} > > using (DATAFILES ("PIPE:${PIPEFILE}")); > > EOT! > > > > dbaccess ${DBNAME} - <<EOT! > > insert into ${EXTABLE} > > select * from ${TABLE}> > !${COMPRESS_SCRIPT} & > > ; > > EOT! > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > _________________________________________________________________ > Hotmail has tools for the New Busy. Search, chat and e-mail from your > inbox. > > > http://www.windowslive.com/campaign/thenewbusy?ocid=PID28326::T:WLMTAGL:ON:WL:en -US:WM_HMP:042010_1 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --000e0cd32874777ea10485946b37