Unload on table with TEXT fields running slow

Hi, We have a problem where UNLOAD statements on a specific table are running slow. The only thing that seems to be different on this table is that the table contains two TEXT fields. For comparison, the average unload speed is around 600,000 blocks per second, but the unload speed on this table is 2,200 blocks per second. I wrote a Perl script to do a select all and dump it to disk (simulating DB activity and I/O), and it runs as I would expect - in the 400,000+ blocks per second. Following is the environment: AIX 5.4 Informix 11.1 I did try upping DBBLOBBUF up to 50, but that didn't seem to have any affect. Thanks in advance, -Justin

For reference, the table has ~380,000 records and takes 1 hour and 28 minutes to create an 11MB unload file. The schema of the table is as follows: Integer not null Varchar(30,1) not null Text Text Justin Killen Senior Programmer / Analyst -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Justin Killen Sent: Friday, June 24, 2011 8:16 AM To: ids@iiug.org Subject: Unload on table with TEXT fields running slow [24127] Hi, We have a problem where UNLOAD statements on a specific table are running slow. The only thing that seems to be different on this table is that the table contains two TEXT fields. For comparison, the average unload speed is around 600,000 blocks per second, but the unload speed on this table is 2,200 blocks per second. I wrote a Perl script to do a select all and dump it to disk (simulating DB activity and I/O), and it runs as I would expect - in the 400,000+ blocks per second. Following is the environment: AIX 5.4 Informix 11.1 I did try upping DBBLOBBUF up to 50, but that didn't seem to have any affect. Thanks in advance, -Justin ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Have you tried HPL? onpladm create project myproject onpladm create job myjob -p myproject -d myoutputfile -D mydatabase -t = mytable -fu -zD onpload -p myproject -j myjob -fu=20 j. On Jun 24, 2011, at 3:49 PM, Justin Killen wrote: > For reference, the table has ~380,000 records and takes 1 hour and 28 = minutes=20 > to create an 11MB unload file.=20 >=20 > The schema of the table is as follows:=20 >=20 > Integer not null=20 > Varchar(30,1) not null=20 > Text=20 > Text=20 >=20 > Justin Killen=20 > Senior Programmer / Analyst=20 > -----Original Message-----=20 > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of = Justin=20 > Killen=20 > Sent: Friday, June 24, 2011 8:16 AM=20 > To: ids@iiug.org=20 > Subject: Unload on table with TEXT fields running slow [24127]=20 >=20 > Hi,=20 >=20 > We have a problem where UNLOAD statements on a specific table are = running=20 > slow. The only thing that seems to be different on this table is that = the=20 > table contains two TEXT fields. For comparison, the average unload = speed is=20 > around 600,000 blocks per second, but the unload speed on this table = is 2,200=20 > blocks per second. I wrote a Perl script to do a select all and dump = it to=20 > disk (simulating DB activity and I/O), and it runs as I would expect - = in the=20 > 400,000+ blocks per second.=20 >=20 > Following is the environment:=20 > AIX 5.4=20 > Informix 11.1=20 >=20 > I did try upping DBBLOBBUF up to 50, but that didn't seem to have any = affect.=20 >=20 > Thanks in advance,=20 > -Justin=20 >=20 >=20 > = **************************************************************************= *****=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=20 >=20 >=20 > = **************************************************************************= *****=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=20= >=20

Thanks Jack, that works much faster (17 seconds instead of an hour and a half). As a side question, which of the output formats is the most condensed? I see the delimited option creates a file compatible with load and unload statements, but is there an option that will create a smaller file? I tried fixed binary, but that seems to create a larger file, and fixed ascii seems like it would probably create something even larger. Also, are these formats compatible with other versions of IDS? Justin Killen Senior Programmer / Analyst -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Jack Parker Sent: Friday, June 24, 2011 1:25 PM To: ids@iiug.org Subject: Re: Unload on table with TEXT fields running slow [24139] Have you tried HPL? onpladm create project myproject onpladm create job myjob -p myproject -d myoutputfile -D mydatabase -t = mytable -fu -zD onpload -p myproject -j myjob -fu=20 j. On Jun 24, 2011, at 3:49 PM, Justin Killen wrote: > For reference, the table has ~380,000 records and takes 1 hour and 28 = minutes=20 > to create an 11MB unload file.=20 >=20 > The schema of the table is as follows:=20 >=20 > Integer not null=20 > Varchar(30,1) not null=20 > Text=20 > Text=20 >=20 > Justin Killen=20 > Senior Programmer / Analyst=20 > -----Original Message-----=20 > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of = Justin=20 > Killen=20 > Sent: Friday, June 24, 2011 8:16 AM=20 > To: ids@iiug.org=20 > Subject: Unload on table with TEXT fields running slow [24127]=20 >=20 > Hi,=20 >=20 > We have a problem where UNLOAD statements on a specific table are = running=20 > slow. The only thing that seems to be different on this table is that = the=20 > table contains two TEXT fields. For comparison, the average unload = speed is=20 > around 600,000 blocks per second, but the unload speed on this table = is 2,200=20 > blocks per second. I wrote a Perl script to do a select all and dump = it to=20 > disk (simulating DB activity and I/O), and it runs as I would expect - = in the=20 > 400,000+ blocks per second.=20 >=20 > Following is the environment:=20 > AIX 5.4=20 > Informix 11.1=20 >=20 > I did try upping DBBLOBBUF up to 50, but that didn't seem to have any = affect.=20 >=20 > Thanks in advance,=20 > -Justin=20 >=20 >=20 > = **************************************************************************= *****=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=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.

When you used load/unload from dbaccess you need to set the environment variables DBBLOBBUF. This will allocate an in memory buffer for the blobs to be transfered. If you do not then each blob is transfer via a file. This is slow!!! export DBBLOBBUF =1024 This will allocate a 1 MB buffer for the blobs. John F. Miller III STSM, Embedability Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 06/24/2011 02:44:19 PM: > From: > > "Justin Killen" <jkillen@allamericanasphalt.com> > > To: > > ids@iiug.org > > Date: > > 06/24/2011 02:45 PM > > Subject: > > RE: Unload on table with TEXT fields running slow [24142] > > Sent by: > > ids-bounces@iiug.org > > Thanks Jack, that works much faster (17 seconds instead of an hour and a > half). > > As a side question, which of the output formats is the most condensed? I see > the delimited option creates a file compatible with load and unload > statements, but is there an option that will create a smaller file? I tried > fixed binary, but that seems to create a larger file, and fixed ascii seems > like it would probably create something even larger. Also, are these formats > compatible with other versions of IDS? > > Justin Killen > Senior Programmer / Analyst > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Jack > Parker > Sent: Friday, June 24, 2011 1:25 PM > To: ids@iiug.org > Subject: Re: Unload on table with TEXT fields running slow [24139] > > Have you tried HPL? > > onpladm create project myproject > onpladm create job myjob -p myproject -d myoutputfile -D mydatabase -t = > mytable -fu -zD > onpload -p myproject -j myjob -fu=20> > j. > > On Jun 24, 2011, at 3:49 PM, Justin Killen wrote: > > > For reference, the table has ~380,000 records and takes 1 hour and 28 = > minutes=20 > > to create an 11MB unload file.=20 > >=20 > > The schema of the table is as follows:=20 > >=20 > > Integer not null=20 > > Varchar(30,1) not null=20 > > Text=20 > > Text=20 > >=20 > > Justin Killen=20 > > Senior Programmer / Analyst=20 > > -----Original Message-----=20 > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of = > Justin=20 > > Killen=20 > > Sent: Friday, June 24, 2011 8:16 AM=20 > > To: ids@iiug.org=20 > > Subject: Unload on table with TEXT fields running slow [24127]=20 > >=20 > > Hi,=20 > >=20 > > We have a problem where UNLOAD statements on a specific table are = > running=20 > > slow. The only thing that seems to be different on this table is that = > the=20 > > table contains two TEXT fields. For comparison, the average unload = > speed is=20 > > around 600,000 blocks per second, but the unload speed on this table = > is 2,200=20 > > blocks per second. I wrote a Perl script to do a select all and dump = > it to=20 > > disk (simulating DB activity and I/O), and it runs as I would expect - = > in the=20 > > 400,000+ blocks per second.=20 > >=20 > > Following is the environment:=20 > > AIX 5.4=20 > > Informix 11.1=20 > >=20 > > I did try upping DBBLOBBUF up to 50, but that didn't seem to have any = > affect.=20 > >=20 > > Thanks in advance,=20 > > -Justin=20 > >=20 > >=20 > > = > **************************************************************************= > *****=20 > > Forum Note: Use "Reply" to post a response in the discussion forum.=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. >

The formats are compatible across engine versions - I'm told. The most compressed is to dump to a gzip process instead of a file. = That will kick up the speed as well. Under unix you can create a device first - as a pipe. decice.txt BEGIN OBJECT DEVICEARRAY zipper BEGIN SEQUENCE TYPE PIPE FILE=20 TAPEBLOCKSIZE 0 TAPEDEVICESIZE 0 PIPECOMMAND "gzip -1 > file.gz" END SEQUENCE END OBJECT Then you create a device onpladm create object -F device.txt and on the job itself, you use: onpladm create job myjob -p myproject -d zipper -D mydb -t mytable -fua = -zD The 'a' signifying that it is a device array instead of a file. The fastest unload/load will come from using the internal format -zFI Have fun Regards, Jack Parker On Jun 24, 2011, at 5:44 PM, Justin Killen wrote: > Thanks Jack, that works much faster (17 seconds instead of an hour and = a=20 > half).=20 >=20 > As a side question, which of the output formats is the most condensed? = I see=20 > the delimited option creates a file compatible with load and unload=20 > statements, but is there an option that will create a smaller file? I = tried=20 > fixed binary, but that seems to create a larger file, and fixed ascii = seems=20 > like it would probably create something even larger. Also, are these = formats=20 > compatible with other versions of IDS?=20 >=20 > Justin Killen=20 > Senior Programmer / Analyst=20 >=20 > -----Original Message-----=20 > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of = Jack=20 > Parker=20 > Sent: Friday, June 24, 2011 1:25 PM=20 > To: ids@iiug.org=20 > Subject: Re: Unload on table with TEXT fields running slow [24139]=20 >=20 > Have you tried HPL?=20 >=20 > onpladm create project myproject=20 > onpladm create job myjob -p myproject -d myoutputfile -D mydatabase -t = =3D=20 > mytable -fu -zD=20 > onpload -p myproject -j myjob -fu=3D20=20 >=20 > j.=20 >=20 > On Jun 24, 2011, at 3:49 PM, Justin Killen wrote:=20 >=20>> For reference, the table has ~380,000 records and takes 1 hour and 28 = =3D=20 > minutes=3D20=20 >> to create an 11MB unload file.=3D20=20 >> =3D20=20 >> The schema of the table is as follows:=3D20=20 >> =3D20=20 >> Integer not null=3D20=20 >> Varchar(30,1) not null=3D20=20 >> Text=3D20=20 >> Text=3D20=20 >> =3D20=20 >> Justin Killen=3D20=20 >> Senior Programmer / Analyst=3D20=20 >> -----Original Message-----=3D20=20 >> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of = =3D=20 > Justin=3D20=20 >> Killen=3D20=20 >> Sent: Friday, June 24, 2011 8:16 AM=3D20=20 >> To: ids@iiug.org=3D20=20 >> Subject: Unload on table with TEXT fields running slow [24127]=3D20=20= >> =3D20=20 >> Hi,=3D20=20 >> =3D20=20 >> We have a problem where UNLOAD statements on a specific table are =3D=20= > running=3D20=20 >> slow. The only thing that seems to be different on this table is that = =3D=20 > the=3D20=20 >> table contains two TEXT fields. For comparison, the average unload =3D=20= > speed is=3D20=20 >> around 600,000 blocks per second, but the unload speed on this table = =3D=20 > is 2,200=3D20=20 >> blocks per second. I wrote a Perl script to do a select all and dump = =3D=20 > it to=3D20=20 >> disk (simulating DB activity and I/O), and it runs as I would expect = - =3D=20 > in the=3D20=20 >> 400,000+ blocks per second.=3D20=20 >> =3D20=20 >> Following is the environment:=3D20=20 >> AIX 5.4=3D20=20 >> Informix 11.1=3D20=20 >> =3D20=20 >> I did try upping DBBLOBBUF up to 50, but that didn't seem to have any = =3D=20 > affect.=3D20=20 >> =3D20=20 >> Thanks in advance,=3D20=20 >> -Justin=3D20=20 >> =3D20=20 >> =3D20=20 >> =3D=20 > = **************************************************************************= =3D=20 > *****=3D20=20 >> Forum Note: Use "Reply" to post a response in the discussion = forum.=3D20=20 >> =3D20=20 >> =3D20=20 >> =3D=20 > = **************************************************************************= =3D=20 > *****=3D20=20 >> Forum Note: Use "Reply" to post a response in the discussion = forum.=3D20=3D=20 >=20 >> =3D20=20 >=20 >=20 > = **************************************************************************= *****=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=20 >=20 >=20 > = **************************************************************************= *****=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=20= >=20