 |
IDS Forum
Re: Data Loads taking lots of extra time
Posted By: Art Kagel
Date: Tuesday, 5 April 2011, at 10:48 a.m.
In Response To: RE: Data Loads taking lots of extra time (Knox, Ernest)
Informix will perform incrementally better than a windows server on the same
hardware.
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, Apr 5, 2011 at 10:45 AM, Knox, Ernest <Ernest.Knox@searshc.com>wrote:
> Sounds good. I may discuss moving them to a Linux or AIX server.
>
> Thanks,
> *******************************************************************
> Ernie Knox
> IT Database Administrator Specialist
> Sears Holdings - BU: I & T Group
> 3333 Beverly Rd., B4-266A
> Hoffman Estates, IL. 60179
> Office: (847) 286-5735
> Email: Ernest.Knox@searshc.com
> Blackberry: 2244650553@messaging.sprintpcs.com
> Page via Skytel: 2244650553@sprint.skytel.com
> Informix or MySQL Primary: 9110210@skytel.com
> Informix or MySQL Secondary: 7276872@skytel.com
>
> " Yes we can make a Change! "
> " It's always a great day to watch Sports - GO LIONS, TIGERS, and BEARS!
> "
> " Lets not forget - GO Pistons and Red Wings! "
> GSU
> *******************************************************************
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Art Kagel
> Sent: Tuesday, April 05, 2011 10:42 AM
> To: ids@iiug.org
> Subject: Re: Data Loads taking lots of extra time [23332]
>
> Likely not. They are ksh scripts (work with bash as well), but even if
> you
> have cygwin, you can't access Informix from the cygwin environment - at
> least I haven't made it work yet. You could, however, set up a Linux
> machine or VM configured to see the windows based server and run
> myexport/myimport from there.
>
> 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, Apr 5, 2011 at 10:32 AM, Knox, Ernest
> <Ernest.Knox@searshc.com>wrote:
>
> > So are you saying that disabling and re-enabling the indexes could
> take
> > a long time to perform?
> >
> > There are only 16 million rows in the table.
> >
> > They also performed a defrag of the disk.
> >
> > Art, can your load script go in windows servers?
> >
> > Thanks,
> > *******************************************************************
> > Ernie Knox
> > IT Database Administrator Specialist
> > Sears Holdings - BU: I & T Group
> > 3333 Beverly Rd., B4-266A
> > Hoffman Estates, IL. 60179
> > Office: (847) 286-5735
> > Email: Ernest.Knox@searshc.com
> > Blackberry: 2244650553@messaging.sprintpcs.com
> > Page via Skytel: 2244650553@sprint.skytel.com
> > Informix or MySQL Primary: 9110210@skytel.com
> > Informix or MySQL Secondary: 7276872@skytel.com
> > =20
> > " Yes we can make a Change! "
> > " It's always a great day to watch Sports - GO LIONS, TIGERS, and
> BEARS!
> > "
> > " Lets not forget - GO Pistons and Red Wings! "
> > GSU
> > *******************************************************************
> >
> > -----Original Message-----
> > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> > Jack Parker
> > Sent: Tuesday, April 05, 2011 8:01 AM
> > To: ids@iiug.org
> > Subject: Re: Data Loads taking lots of extra time [23324]
> >
> > Then also check for disk fragmentation. Windows and onspaces do not
> =3D=20
> > play well together. Right click my computer, manage, disk management
> is
> > =3D=20
> > in there somewhere,=20
> >
> > I don't know what you're using to load now. HPL is the fastest loader
> =3D=
> > =20
> > for that release. I have not played with external tables on 11.7 yet,
> =3D=
> > =20
> > but they rocked in XPS. There is a load FAQ that walks through the
> =3D=20
> > various loaders and their pros and cons..(he looks). Although that
> site
> > =3D=20
> > has fallen off the web. I have a copy if you want it.=20
> >
> > Certainly 9.4 is older, and probably no longer supported (10 went EOS
> in
> > =3D=20
> > Sept 2010), but I have a customer who still uses 2.1 last time I
> =3D=20
> > checked.=20
> >
> > With Windows these is no ipload interface, so you have to set up jobs
> =3D=
> > =20
> > with onpladm. First create a project, then create the jobs.=20
> >
> > onpladm create project <project>=20
> > onpladm create job <name> -p <project> -d <file> -D <database> -t
> =3D=20
> > <table> -fl=20
> > then load with:=20
> > onpload -p <project> -j <job> -fl=3D20=20
> >
> > There will be some variation in the create job according to what you
> =3D=20
> > have, and layout of the source file will need to match the table -
> =3D=20
> > unless you want to get into onpladm trickery. The onpladm "interface"
> =3D=
> > =20
> > is: "type a portion of the command, hit return and it shows you the
> =3D=20
> > options" - even explains some of them. Documentation for it is key -
> =3D=20
> > google for that.=20
> >
> > j.=20
> >
> > On Apr 5, 2011, at 7:34 AM, Knox, Ernest wrote:=20
> >
> > > This is IDS 9.4 on OS Win2K. Should I also allow him to use another
> =3D=
> > =20
> > load=3D20=20
> > > method or upgrade?=3D20=20
> > >=3D20=20
> > > Thanks,=3D20=20
> > >
> *******************************************************************=3D20
>
> >
> > > Ernie Knox=3D20=20
> > > IT Database Administrator Specialist=3D20=20
> > > Sears Holdings=3D20=20
> > > 3333 Beverly Rd., B4-266A=3D20=20
> > > Hoffman Estates, IL. 60179=3D20=20
> > > Office: (847) 286-5735=3D20=20
> > > Email: Ernest.Knox@searshc.com=3D20=20
> > > Blackberry: 2244650553@messaging.sprintpcs.com=3D20=20
> > > Page via Skytel: 2244650553@sprint.skytel.com=3D20=20
> > > Informix or MySQL Primary: 9110210@skytel.com=3D20=20
> > > Informix or MySQL Secondary: 7276872@skytel.com=3D20=20
> > >=3D20=20
> > > " Yes we can make a Change! "=3D20=20
> > > " It's always a great day to watch Sports - GO LIONS, TIGERS, and
> =3D=20
> > BEARS! "=3D20=20
> > > " Lets not forget - GO Pistons and Red Wings! "=3D20=20
> > > GSU=3D20=20
> > >
> *******************************************************************=3D20
>
> >
> > >=3D20=20
> > > ----- Original Message -----=3D20=20
> > > From: Jack Parker [mailto:jack.parker4@verizon.net]=3D20=20
> > > Sent: Tuesday, April 05, 2011 07:23 AM=3D20=20
> > > To: ids@iiug.org <ids@iiug.org>=3D20=20
> > > Subject: Re: Data Loads taking lots of extra time [23322]=3D20=20
> > >=3D20=20
> > > Note, that it may not be practical to drop and re-add indices on a
> =3D=20
> > large =3D3D=3D20=20
> > > table. Are all of the 9 indices used? Check sysptprof for more reads
>
> > =3D3D=3D=20
> > =3D20=20
> > > than writes. if that ratio is 1:1, you may be using the index only
> > =3D3D=3D20=3D=20
> >
> > > during the insert.=3D20=20
> > >=3D20=20
> > > j.=3D20=20
> > >=3D20=20
> > > On Apr 5, 2011, at 7:12 AM, Knox, Ernest wrote:=3D20=20
> > >=3D20=20
> > >> There are nine indexes. I'll check the extents.=3D3D20=3D20=20
> > >> =3D3D20=3D20=20
> > >> Thanks,=3D3D20=3D20=20
> > >> =3D=20
> >
> *******************************************************************=3D3D
> 20
> > =3D20=3D=20
> >
> > >> Ernie Knox=3D3D20=3D20=20
> > >> IT Database Administrator Specialist=3D3D20=3D20=20
> > >> Sears Holdings=3D3D20=3D20=20
> > >> 3333 Beverly Rd., B4-266A=3D3D20=3D20=20
> > >> Hoffman Estates, IL. 60179=3D3D20=3D20=20
> > >> Office: (847) 286-5735=3D3D20=3D20=20
> > >> Email: Ernest.Knox@searshc.com=3D3D20=3D20=20
> > >> Blackberry: 2244650553@messaging.sprintpcs.com=3D3D20=3D20=20
> > >> Page via Skytel: 2244650553@sprint.skytel.com=3D3D20=3D20=20
> > >> Informix or MySQL Primary: 9110210@skytel.com=3D3D20=3D20=20
> > >> Informix or MySQL Secondary: 7276872@skytel.com=3D3D20=3D20=20
> > >> =3D3D20=3D20=20
> > >> " Yes we can make a Change! "=3D3D20=3D20=20
> > >> " It's always a great day to watch Sports - GO LIONS, TIGERS, and
> > =3D3D=3D20=3D=20
> >
> > > BEARS! "=3D3D20=3D20=20
> > >> " Lets not forget - GO Pistons and Red Wings! "=3D3D20=3D20=20
> > >> GSU=3D3D20=3D20=20
> > >> =3D=20
> >
> *******************************************************************=3D3D
> 20
> > =3D20=3D=20
> >
> > >> =3D3D20=3D20=20
> > >> ----- Original Message -----=3D3D20=3D20=20
> > >> From: Jack Parker [mailto:jack.parker4@verizon.net]=3D3D20=3D20=20
> > >> Sent: Tuesday, April 05, 2011 06:17 AM=3D3D20=3D20=20
> > >> To: ids@iiug.org <ids@iiug.org>=3D3D20=3D20=20
> > >> Subject: Re: Data Loads taking lots of extra time
> [23319]=3D3D20=3D20=20
> > >> =3D3D20=3D20=20
> > >> Loads can take a long time if the table has not been sized
> > =3D3D3D=3D3D20=3D20=3D=20
> >
> > >> appropriately. Constantly allocated new extents is a drag, it's
> =3D=20
> > better =3D3D=3D20=20
> > > =3D3D3D=3D3D20=3D20=20
> > >> now with extent doubling, but always something to keep in mind. If
> =3D=
> > =20
> > =3D3D3D=3D3D20=3D3D=3D20=20
> > >=3D20=20
> > >> there is not a lot of contiguous free space in the dbspace, that
> will
> > =3D=20
> > =3D3D=3D20=20
> > > =3D3D3D=3D3D20=3D20=20
> > >> cause the same effect=3D3D20=3D20=20
> > >> =3D3D20=3D20=20
> > >> Are there indices on the table? It's expensive to load into
> indexes.
> > =3D=20
> > =3D3D3D=3D3D=3D20=20
> > > =3D3D20=3D20=20
> > >> At times it may be faster to drop the indexes, alter the table type
> =3D
> >
> > to =3D3D=3D20=20
> > > =3D3D3D=3D3D20=3D20=20
> > >> RAW, load it, alter the table type back and recreate the indexes.
> =3D=20
> > Note =3D3D=3D20=20
> > > =3D3D3D=3D3D20=3D20=20
> > >> that referential constraints count as indexes.=3D3D20=3D20=20
> > >> =3D3D20=3D20=20
> > >> cheers=3D3D20=3D20=20
> > >> j.=3D3D20=3D20=20
> > >> =3D3D20=3D20=20
> > >> On Apr 5, 2011, at 3:10 AM, GERARDO PADIERNA wrote:=3D3D20=3D20=20
> > >> =3D3D20=3D20=20
> > >>> Hi,=3D3D3D20=3D3D20=3D20=20
> > >>> have you checked if update statistics has run properly, I mean,
> just
> > =3D=20
> > =3D3D=3D20=20
> > > =3D3D3D=3D3D20=3D20=20
> > >> in case?=3D3D3D20=3D3D20=3D20=20
> > >>> select constructed from sysdistrib=3D3D3D20=3D3D20=3D20=20
> > >>> We had a case when for some reason the cron job that ran the
> update
> > =3D=20
> > =3D3D3D=3D3D=3D20=20
> > > =3D3D20=3D20=20
> > >> statistics=3D3D3D20=3D3D20=3D20=20
> > >>> stopped working. But that should slow down the OLTP access =3D=20
> > too.=3D3D3D20=3D3D20=3D3D=3D20=20
> > >=3D20=20
> > >>> Well, as I said, just in case you didn't
> check.=3D3D3D20=3D3D20=3D20=20
> > >>> =3D3D3D20=3D3D20=3D20=20
> > >>> Cheers,=3D3D3D20=3D3D20=3D20=20
> > >>> Gerardo=3D3D3D20=3D3D20=3D20=20
> > >>> =3D3D3D20=3D3D20=3D20=20
> > >>> =3D3D3D20=3D3D20=3D20=20
> > >>> =3D3D3D=3D3D20=3D20=20
> > >> =3D3D=3D20=20
> > > =3D=20
> >
> ************************************************************************
>
> > **=3D=20
> > =3D3D=3D20=20
> > > =3D3D3D=3D3D20=3D20=20
> > >> *****=3D3D3D20=3D3D20=3D20=20
> > >>> Forum Note: Use "Reply" to post a response in the discussion
> > =3D3D=3D20=
> > =20
> > > forum.=3D3D3D20=3D3D3D=3D3D20=3D20=20
> > >> =3D3D20=3D20=20
> > >>> =3D3D3D20=3D3D20=3D20=20
> > >> =3D3D20=3D20=20
> > >> =3D3D20=3D20=20
> > >> =3D3D=3D20=20
> > > =3D=20
> >
> ************************************************************************
>
> > **=3D=20
> > =3D3D=3D20=20
> > > *****=3D3D20=3D20=20
> > >> Forum Note: Use "Reply" to post a response in the discussion =3D=20
>
> > forum.=3D3D20=3D20=20
> > >> =3D3D20=3D20=20
> > >> =3D3D20=3D20=20
> > >> =3D3D=3D20=20
> > > =3D=20
> >
> ************************************************************************
>
> > **=3D=20
> > =3D3D=3D20=20
> > > *****=3D3D20=3D20=20
> > >> Forum Note: Use "Reply" to post a response in the discussion =3D=20
>
> > forum.=3D3D20=3D3D=3D20=20
> > >=3D20=20
> > >> =3D3D20=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
> >
> > >=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
> >
> > >=3D20=20
> >
> >
> ************************************************************************
>
> > *******=20
> > Forum Note: Use "Reply" to post a response in the discussion forum.=20
>
> >
> > This message, including any attachments, is the property of Sears
> Holdings
> > =
> > Corporation and/or one of its subsidiaries. It is confidential and may
>
> > cont=
> > ain proprietary or legally privileged information. If you are not the
> > inten=
> > ded recipient, please delete it without reading the contents. Thank
> you.
> >
> >
> >
> >
> ************************************************************************
> *******
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --20cf307abcf9f628b604a02ce071
>
> ************************************************************************
> *******
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--20cf307cfe9edb09e304a02cf5b9
Messages In This Thread
IDS Forum is maintained by Administrator with WebBBS 5.12.
|
|
