IDS Forum

RE: Data Loads taking lots of extra time

Posted By: Knox, Ernest
Date: Tuesday, 5 April 2011, at 10:45 a.m.

In Response To: Re: Data Loads taking lots of extra time (Art Kagel)

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.

Messages In This Thread

• FW: Data Loads taking lots of extra time
  Knox, Ernest -- Friday, 1 April 2011, at 7:55 a.m.
  • Re: Data Loads taking lots of extra time
    Jack Parker -- Friday, 1 April 2011, at 8:06 a.m.
  • Re: FW: Data Loads taking lots of extra time
    GERARDO PADIERNA -- Tuesday, 5 April 2011, at 3:10 a.m.
    • Re: Data Loads taking lots of extra time
      Jack Parker -- Tuesday, 5 April 2011, at 6:17 a.m.
      • Re: Data Loads taking lots of extra time
        Knox, Ernest -- Tuesday, 5 April 2011, at 7:12 a.m.
        • Re: Data Loads taking lots of extra time
          Jack Parker -- Tuesday, 5 April 2011, at 7:23 a.m.
          • Re: Data Loads taking lots of extra time
            Knox, Ernest -- Tuesday, 5 April 2011, at 7:34 a.m.
            • Re: Data Loads taking lots of extra time
              Jack Parker -- Tuesday, 5 April 2011, at 8:01 a.m.
              • RE: Data Loads taking lots of extra time
                Knox, Ernest -- Tuesday, 5 April 2011, at 9:22 a.m.
                • Re: Data Loads taking lots of extra time *NM*
                  Jack Parker -- Tuesday, 5 April 2011, at 10:22 a.m.
              • RE: Data Loads taking lots of extra time
                Knox, Ernest -- Tuesday, 5 April 2011, at 10:32 a.m.
                • Re: Data Loads taking lots of extra time
                  Art Kagel -- Tuesday, 5 April 2011, at 10:42 a.m.
                  • RE: Data Loads taking lots of extra time
                    Knox, Ernest -- Tuesday, 5 April 2011, at 10:45 a.m.
                    • Re: Data Loads taking lots of extra time
                      Art Kagel -- Tuesday, 5 April 2011, at 10:48 a.m.
                • Re: Data Loads taking lots of extra time
                  Jack Parker -- Tuesday, 5 April 2011, at 10:57 a.m.
    • Re: FW: Data Loads taking lots of extra time
      Knox, Ernest -- Tuesday, 5 April 2011, at 7:10 a.m.
  • FW: FW: Data Loads taking lots of extra time
    Knox, Ernest -- Friday, 15 April 2011, at 1:18 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.