FW: Data Loads taking lots of extra time

IDS 9.40.TC1 OS: Win 2000 SP4 Intel(r) XEON(tm) MP CPU 1.90GHz AT/AT COMPATIBLE 3,669,424 KB RAM =20 Questions: =20 1. I did see other processes running at the same time, which could be utilizing internal disk controller. Should I look at something else? 2. Should I let the customer use a different load method? 3. Would a newer version perform faster (11.7)? =20 Problem: =20 From my Customer: =20 "Sent: Thu 3/31/2011 9:37 AM =20 After March 15, our loads to the asc_coach_detail table has increased from loading in 20-45 minutes to 3-5 hours. The amount of data rows has not increased dramatically, to support the delay. Can you check to see if there has been some change that has facilitated this delay in loading? I am copying the wintel team as well in case they are aware of any changes to the servers listed below." =20 My initial response to the Customer and his reply: =20 Sent: Thursday, March 31, 2011 5:13 PM I really don't see much of an informix issue. =20 1. When (time) are you performing these loads? Normally between 5:30 and 6:30 Central. Sometimes after 7am. 2. Are the loads being performed during OLTP times? We are data loading into a reporting database. This database has no OLTP 3. How many rows are you loading between commits? Mary might know. She did analysis in the Fall when we had a similar issue.=20=20 4. You have 9 indexes to also insert into, which can take time. Yes, but until 3/15, load time was <1 hour. After 3/15, consistently >3 hours=20 5. You may want to upgrade to new version of informix 11.5+ Bring it on. We have it in dev, but no time to test it. Joel???? 6. Things seem to be running fast right now. Our load completed at 9:46.=20 7. Are you using load, DBload, HPL, or what to load the data? DBLOAD because of the volume of data.=20 8. How many rows are you loading at each time? 200-300k rows. We loaded 550k (month end data) rows on 2/27 in less than an hour.=20 9. How many of these loads are running each day? only 3 or 4 loads use dbload. We have several (maybe 50 per day) loads using an odbc connection. When I sent the email this morning, the asc_coach_detail dbload was the only hit on the database. =20 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 <mailto:2244650553@messaging.sprintpcs.com>=20 Page via Skytel: 2244650553@sprint.skytel.com <mailto:2244650553@sprint.skytel.com>=20 Informix or MySQL Primary: 9110210@skytel.com <mailto:9110210@skytel.com>=20 Informix or MySQL Secondary: 7276872@skytel.com <mailto:7276872@skytel.com>=20 =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 ******************************************************************* 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.

1 - defrag the disk. An issue with dbspaces under WIndows. 2 - HPL will load that volume in minutes as opposed to 10's of minutes. 3 - check extents on the table(s) being loaded. are they excessive? = Is table properly sized? 4 - check event viewer and see if there are complaints there. 5 - logical logs are probably not a problem, but dbload will write to = them during the load. We are just starting to work with 11.70 under W2k8 R2. But we used = 10.TC5 and TC10 for quite some time on w2003 sp2 without issues. 10 is = at end of support. cheers j. On Apr 1, 2011, at 7:55 AM, Knox, Ernest wrote: > IDS 9.40.TC1=20 >=20 > OS: Win 2000 SP4=20 >=20 > Intel(r) XEON(tm) MP CPU 1.90GHz AT/AT COMPATIBLE 3,669,424 KB RAM=20 >=20 > =3D20=20 >=20 > Questions:=20 >=20 > =3D20=20 >=20 > 1. I did see other processes running at the same time, which could=20 > be utilizing internal disk controller. Should I look at something = else?=20 > 2. Should I let the customer use a different load method?=20 > 3. Would a newer version perform faster (11.7)?=20 >=20 > =3D20=20 >=20 > Problem:=20 >=20 > =3D20=20 >=20 >> =46rom my Customer:=20 >=20 > =3D20=20 >=20 > "Sent: Thu 3/31/2011 9:37 AM=20 >=20 > =3D20=20 >=20 > After March 15, our loads to the asc_coach_detail table has increased=20= > from loading in 20-45 minutes to 3-5 hours. The amount of data rows = has=20 > not increased dramatically, to support the delay.=20 >=20 > Can you check to see if there has been some change that has = facilitated=20 > this delay in loading? I am copying the wintel team as well in case = they=20 > are aware of any changes to the servers listed below."=20 >=20 > =3D20=20 >=20 > My initial response to the Customer and his reply:=20 >=20 > =3D20=20 >=20 > Sent: Thursday, March 31, 2011 5:13 PM=20 >=20 > I really don't see much of an informix issue.=20 >=20 > =3D20=20 >=20 > 1. When (time) are you performing these loads? Normally between 5:30 = and=20 > 6:30 Central. Sometimes after 7am.=20 >=20 > 2. Are the loads being performed during OLTP times? We are data = loading=20 > into a reporting database. This database has no OLTP=20 >=20 > 3. How many rows are you loading between commits? Mary might know. She=20= > did analysis in the Fall when we had a similar issue.=3D20=3D20=20 >=20 > 4. You have 9 indexes to also insert into, which can take time. Yes, = but=20 > until 3/15, load time was <1 hour. After 3/15, consistently >3 = hours=3D20=20 >=20 > 5. You may want to upgrade to new version of informix 11.5+ Bring it=20= > on. We have it in dev, but no time to test it. Joel????=20 >=20 > 6. Things seem to be running fast right now. Our load completed at=20 > 9:46.=3D20=20 >=20 > 7. Are you using load, DBload, HPL, or what to load the data? DBLOAD=20= > because of the volume of data.=3D20=20 >=20 > 8. How many rows are you loading at each time? 200-300k rows. We = loaded=20 > 550k (month end data) rows on 2/27 in less than an hour.=3D20=20 >=20 > 9. How many of these loads are running each day? only 3 or 4 loads use=20= > dbload. We have several (maybe 50 per day) loads using an odbc=20 > connection. When I sent the email this morning, the=20 >=20 > asc_coach_detail dbload was the only hit on the database.=20 >=20 > =3D20=20 >=20 > Thanks,=20 >=20 > *******************************************************************=20 >=20 > Ernie Knox=20 >=20 > IT Database Administrator Specialist=20 >=20 > Sears Holdings - BU: I & T Group=20 >=20 > 3333 Beverly Rd., B4-266A=20 >=20 > Hoffman Estates, IL. 60179=20 >=20 > Office: (847) 286-5735=20 >=20 > Email: Ernest.Knox@searshc.com=20 >=20 > Blackberry: 2244650553@messaging.sprintpcs.com=20 > <mailto:2244650553@messaging.sprintpcs.com>=3D20=20 >=20 > Page via Skytel: 2244650553@sprint.skytel.com=20 > <mailto:2244650553@sprint.skytel.com>=3D20=20 >=20 > Informix or MySQL Primary: 9110210@skytel.com=20 > <mailto:9110210@skytel.com>=3D20=20 >=20 > Informix or MySQL Secondary: 7276872@skytel.com=20 > <mailto:7276872@skytel.com>=3D20=20 >=20 > =3D20=20 >=20 > " Yes we can make a Change! "=20 >=20 > " It's always a great day to watch Sports - GO LIONS, TIGERS, and = BEARS!=20 > "=20 >=20 > " Lets not forget - GO Pistons and Red Wings! "=20 >=20 > GSU=20 >=20 > *******************************************************************=20 >=20 > This message, including any attachments, is the property of Sears = Holdings =3D=20 > Corporation and/or one of its subsidiaries. It is confidential and may = cont=3D=20 > ain proprietary or legally privileged information. If you are not the = inten=3D=20 > ded recipient, please delete it without reading the contents. Thank = you.=20 >=20 >=20 > = **************************************************************************= *****=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=20= >=20

Hi, have you checked if update statistics has run properly, I mean, just in case? select constructed from sysdistribWe had a case when for some reason the cron job that ran the update statistics stopped working. But that should slow down the OLTP access too. Well, as I said, just in case you didn't check. Cheers, Gerardo

Loads can take a long time if the table has not been sized = appropriately. Constantly allocated new extents is a drag, it's better = now with extent doubling, but always something to keep in mind. If = there is not a lot of contiguous free space in the dbspace, that will = cause the same effect Are there indices on the table? It's expensive to load into indexes. = At times it may be faster to drop the indexes, alter the table type to = RAW, load it, alter the table type back and recreate the indexes. Note = that referential constraints count as indexes. cheers j. On Apr 5, 2011, at 3:10 AM, GERARDO PADIERNA wrote: > Hi,=20 > have you checked if update statistics has run properly, I mean, just = in case?=20 > select constructed from sysdistrib=20> We had a case when for some reason the cron job that ran the update = statistics=20 > stopped working. But that should slow down the OLTP access too.=20 > Well, as I said, just in case you didn't check.=20 >=20 > Cheers,=20 > Gerardo=20 >=20 >=20 > = **************************************************************************= *****=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=20= >=20

Yes, the stats have been running. Thanks, ******************************************************************* Ernie Knox IT Database Administrator Specialist Sears Holdings 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: GERARDO PADIERNA [mailto:g.padierna@gmail.com] Sent: Tuesday, April 05, 2011 03:10 AM To: ids@iiug.org <ids@iiug.org> Subject: Re: FW: Data Loads taking lots of extra time [23318] Hi,=20 have you checked if update statistics has run properly, I mean, just in cas= e?=20 select constructed from sysdistrib=20We had a case when for some reason the cron job that ran the update statist= ics=20 stopped working. But that should slow down the OLTP access too.=20 Well, as I said, just in case you didn't check.=20 Cheers,=20 Gerardo=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.

There are nine indexes. I'll check the extents. Thanks, ******************************************************************* Ernie Knox IT Database Administrator Specialist Sears Holdings 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: Jack Parker [mailto:jack.parker4@verizon.net] Sent: Tuesday, April 05, 2011 06:17 AM To: ids@iiug.org <ids@iiug.org> Subject: Re: Data Loads taking lots of extra time [23319] Loads can take a long time if the table has not been sized = appropriately. Constantly allocated new extents is a drag, it's better = now with extent doubling, but always something to keep in mind. If = there is not a lot of contiguous free space in the dbspace, that will = cause the same effect Are there indices on the table? It's expensive to load into indexes. = At times it may be faster to drop the indexes, alter the table type to = RAW, load it, alter the table type back and recreate the indexes. Note = that referential constraints count as indexes. cheers j. On Apr 5, 2011, at 3:10 AM, GERARDO PADIERNA wrote: > Hi,=20 > have you checked if update statistics has run properly, I mean, just = in case?=20 > select constructed from sysdistrib=20> We had a case when for some reason the cron job that ran the update = statistics=20 > stopped working. But that should slow down the OLTP access too.=20 > Well, as I said, just in case you didn't check.=20 >=20 > Cheers,=20 > Gerardo=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.

Note, that it may not be practical to drop and re-add indices on a large = table. Are all of the 9 indices used? Check sysptprof for more reads = than writes. if that ratio is 1:1, you may be using the index only = during the insert. j. On Apr 5, 2011, at 7:12 AM, Knox, Ernest wrote: > There are nine indexes. I'll check the extents.=20 >=20 > Thanks,=20 > *******************************************************************=20 > Ernie Knox=20 > IT Database Administrator Specialist=20 > Sears Holdings=20 > 3333 Beverly Rd., B4-266A=20 > Hoffman Estates, IL. 60179=20 > Office: (847) 286-5735=20 > Email: Ernest.Knox@searshc.com=20 > Blackberry: 2244650553@messaging.sprintpcs.com=20 > Page via Skytel: 2244650553@sprint.skytel.com=20 > Informix or MySQL Primary: 9110210@skytel.com=20 > Informix or MySQL Secondary: 7276872@skytel.com=20 >=20 > " Yes we can make a Change! "=20 > " It's always a great day to watch Sports - GO LIONS, TIGERS, and = BEARS! "=20 > " Lets not forget - GO Pistons and Red Wings! "=20 > GSU=20 > *******************************************************************=20 >=20 > ----- Original Message -----=20 > From: Jack Parker [mailto:jack.parker4@verizon.net]=20 > Sent: Tuesday, April 05, 2011 06:17 AM=20 > To: ids@iiug.org <ids@iiug.org>=20 > Subject: Re: Data Loads taking lots of extra time [23319]=20 >=20 > Loads can take a long time if the table has not been sized =3D=20 > appropriately. Constantly allocated new extents is a drag, it's better = =3D=20 > now with extent doubling, but always something to keep in mind. If =3D=20= > there is not a lot of contiguous free space in the dbspace, that will = =3D=20 > cause the same effect=20 >=20 > Are there indices on the table? It's expensive to load into indexes. =3D= =20 > At times it may be faster to drop the indexes, alter the table type to = =3D=20 > RAW, load it, alter the table type back and recreate the indexes. Note = =3D=20 > that referential constraints count as indexes.=20 >=20 > cheers=20 > j.=20 >=20 > On Apr 5, 2011, at 3:10 AM, GERARDO PADIERNA wrote:=20 >=20 >> Hi,=3D20=20 >> have you checked if update statistics has run properly, I mean, just = =3D=20 > in case?=3D20=20 >> select constructed from sysdistrib=3D20=20>> We had a case when for some reason the cron job that ran the update =3D= =20 > statistics=3D20=20 >> stopped working. But that should slow down the OLTP access too.=3D20=20= >> Well, as I said, just in case you didn't check.=3D20=20 >> =3D20=20 >> Cheers,=3D20=20 >> Gerardo=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

This is IDS 9.4 on OS Win2K. Should I also allow him to use another load method or upgrade? Thanks, ******************************************************************* Ernie Knox IT Database Administrator Specialist Sears Holdings 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: Jack Parker [mailto:jack.parker4@verizon.net] Sent: Tuesday, April 05, 2011 07:23 AM To: ids@iiug.org <ids@iiug.org> Subject: Re: Data Loads taking lots of extra time [23322] Note, that it may not be practical to drop and re-add indices on a large = table. Are all of the 9 indices used? Check sysptprof for more reads = than writes. if that ratio is 1:1, you may be using the index only = during the insert. j. On Apr 5, 2011, at 7:12 AM, Knox, Ernest wrote: > There are nine indexes. I'll check the extents.=20 >=20 > Thanks,=20 > *******************************************************************=20 > Ernie Knox=20 > IT Database Administrator Specialist=20 > Sears Holdings=20 > 3333 Beverly Rd., B4-266A=20 > Hoffman Estates, IL. 60179=20 > Office: (847) 286-5735=20 > Email: Ernest.Knox@searshc.com=20 > Blackberry: 2244650553@messaging.sprintpcs.com=20 > Page via Skytel: 2244650553@sprint.skytel.com=20 > Informix or MySQL Primary: 9110210@skytel.com=20 > Informix or MySQL Secondary: 7276872@skytel.com=20 >=20 > " Yes we can make a Change! "=20 > " It's always a great day to watch Sports - GO LIONS, TIGERS, and = BEARS! "=20 > " Lets not forget - GO Pistons and Red Wings! "=20 > GSU=20 > *******************************************************************=20 >=20 > ----- Original Message -----=20 > From: Jack Parker [mailto:jack.parker4@verizon.net]=20 > Sent: Tuesday, April 05, 2011 06:17 AM=20 > To: ids@iiug.org <ids@iiug.org>=20 > Subject: Re: Data Loads taking lots of extra time [23319]=20 >=20 > Loads can take a long time if the table has not been sized =3D=20 > appropriately. Constantly allocated new extents is a drag, it's better = =3D=20 > now with extent doubling, but always something to keep in mind. If =3D=20= > there is not a lot of contiguous free space in the dbspace, that will = =3D=20 > cause the same effect=20 >=20 > Are there indices on the table? It's expensive to load into indexes. =3D= =20 > At times it may be faster to drop the indexes, alter the table type to = =3D=20 > RAW, load it, alter the table type back and recreate the indexes. Note = =3D=20 > that referential constraints count as indexes.=20 >=20 > cheers=20 > j.=20 >=20 > On Apr 5, 2011, at 3:10 AM, GERARDO PADIERNA wrote:=20 >=20 >> Hi,=3D20=20 >> have you checked if update statistics has run properly, I mean, just = =3D=20 > in case?=3D20=20 >> select constructed from sysdistrib=3D20=20>> We had a case when for some reason the cron job that ran the update =3D= =20 > statistics=3D20=20 >> stopped working. But that should slow down the OLTP access too.=3D20=20= >> Well, as I said, just in case you didn't check.=3D20=20 >> =3D20=20 >> Cheers,=3D20=20 >> Gerardo=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 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Then also check for disk fragmentation. Windows and onspaces do not = play well together. Right click my computer, manage, disk management is = in there somewhere, I don't know what you're using to load now. HPL is the fastest loader = for that release. I have not played with external tables on 11.7 yet, = but they rocked in XPS. There is a load FAQ that walks through the = various loaders and their pros and cons..(he looks). Although that site = has fallen off the web. I have a copy if you want it. Certainly 9.4 is older, and probably no longer supported (10 went EOS in = Sept 2010), but I have a customer who still uses 2.1 last time I = checked. With Windows these is no ipload interface, so you have to set up jobs = with onpladm. First create a project, then create the jobs. onpladm create project <project> onpladm create job <name> -p <project> -d <file> -D <database> -t = <table> -fl then load with: onpload -p <project> -j <job> -fl=20 There will be some variation in the create job according to what you = have, and layout of the source file will need to match the table - = unless you want to get into onpladm trickery. The onpladm "interface" = is: "type a portion of the command, hit return and it shows you the = options" - even explains some of them. Documentation for it is key - = google for that. j. On Apr 5, 2011, at 7:34 AM, Knox, Ernest wrote: > This is IDS 9.4 on OS Win2K. Should I also allow him to use another = load=20 > method or upgrade?=20 >=20 > Thanks,=20 > *******************************************************************=20 > Ernie Knox=20 > IT Database Administrator Specialist=20 > Sears Holdings=20 > 3333 Beverly Rd., B4-266A=20 > Hoffman Estates, IL. 60179=20 > Office: (847) 286-5735=20 > Email: Ernest.Knox@searshc.com=20 > Blackberry: 2244650553@messaging.sprintpcs.com=20 > Page via Skytel: 2244650553@sprint.skytel.com=20 > Informix or MySQL Primary: 9110210@skytel.com=20 > Informix or MySQL Secondary: 7276872@skytel.com=20 >=20 > " Yes we can make a Change! "=20 > " It's always a great day to watch Sports - GO LIONS, TIGERS, and = BEARS! "=20 > " Lets not forget - GO Pistons and Red Wings! "=20 > GSU=20 > *******************************************************************=20 >=20 > ----- Original Message -----=20 > From: Jack Parker [mailto:jack.parker4@verizon.net]=20 > Sent: Tuesday, April 05, 2011 07:23 AM=20 > To: ids@iiug.org <ids@iiug.org>=20 > Subject: Re: Data Loads taking lots of extra time [23322]=20 >=20 > Note, that it may not be practical to drop and re-add indices on a = large =3D=20 > table. Are all of the 9 indices used? Check sysptprof for more reads =3D= =20 > than writes. if that ratio is 1:1, you may be using the index only =3D=20= > during the insert.=20 >=20 > j.=20 >=20 > On Apr 5, 2011, at 7:12 AM, Knox, Ernest wrote:=20 >=20 >> There are nine indexes. I'll check the extents.=3D20=20 >> =3D20=20 >> Thanks,=3D20=20 >> = *******************************************************************=3D20=20= >> 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=20= >> =3D20=20 >> ----- Original Message -----=3D20=20 >> From: Jack Parker [mailto:jack.parker4@verizon.net]=3D20=20 >> Sent: Tuesday, April 05, 2011 06:17 AM=3D20=20 >> To: ids@iiug.org <ids@iiug.org>=3D20=20 >> Subject: Re: Data Loads taking lots of extra time [23319]=3D20=20 >> =3D20=20 >> Loads can take a long time if the table has not been sized =3D3D=3D20=20= >> appropriately. Constantly allocated new extents is a drag, it's = better =3D=20 > =3D3D=3D20=20 >> now with extent doubling, but always something to keep in mind. If = =3D3D=3D20=3D=20 >=20 >> there is not a lot of contiguous free space in the dbspace, that will = =3D=20 > =3D3D=3D20=20 >> cause the same effect=3D20=20 >> =3D20=20 >> Are there indices on the table? It's expensive to load into indexes. = =3D3D=3D=20 > =3D20=20 >> At times it may be faster to drop the indexes, alter the table type = to =3D=20 > =3D3D=3D20=20 >> RAW, load it, alter the table type back and recreate the indexes. = Note =3D=20 > =3D3D=3D20=20 >> that referential constraints count as indexes.=3D20=20 >> =3D20=20 >> cheers=3D20=20 >> j.=3D20=20 >> =3D20=20 >> On Apr 5, 2011, at 3:10 AM, GERARDO PADIERNA wrote:=3D20=20 >> =3D20=20 >>> Hi,=3D3D20=3D20=20 >>> have you checked if update statistics has run properly, I mean, just = =3D=20 > =3D3D=3D20=20 >> in case?=3D3D20=3D20=20 >>> select constructed from sysdistrib=3D3D20=3D20=20>>> We had a case when for some reason the cron job that ran the update = =3D3D=3D=20 > =3D20=20 >> statistics=3D3D20=3D20=20 >>> stopped working. But that should slow down the OLTP access = too.=3D3D20=3D20=3D=20 >=20 >>> Well, as I said, just in case you didn't check.=3D3D20=3D20=20 >>> =3D3D20=3D20=20 >>> Cheers,=3D3D20=3D20=20 >>> Gerardo=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=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

Yes, please send a copy of the FAQ. 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 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 = play well together. Right click my computer, manage, disk management is = in there somewhere, I don't know what you're using to load now. HPL is the fastest loader = for that release. I have not played with external tables on 11.7 yet, = but they rocked in XPS. There is a load FAQ that walks through the = various loaders and their pros and cons..(he looks). Although that site = has fallen off the web. I have a copy if you want it. Certainly 9.4 is older, and probably no longer supported (10 went EOS in = Sept 2010), but I have a customer who still uses 2.1 last time I = checked. With Windows these is no ipload interface, so you have to set up jobs = with onpladm. First create a project, then create the jobs. onpladm create project <project> onpladm create job <name> -p <project> -d <file> -D <database> -t = <table> -fl then load with: onpload -p <project> -j <job> -fl=20 There will be some variation in the create job according to what you = have, and layout of the source file will need to match the table - = unless you want to get into onpladm trickery. The onpladm "interface" = is: "type a portion of the command, hit return and it shows you the = options" - even explains some of them. Documentation for it is key - = google for that. j. On Apr 5, 2011, at 7:34 AM, Knox, Ernest wrote: > This is IDS 9.4 on OS Win2K. Should I also allow him to use another = load=20 > method or upgrade?=20 >=20 > Thanks,=20 > *******************************************************************=20 > Ernie Knox=20 > IT Database Administrator Specialist=20 > Sears Holdings=20 > 3333 Beverly Rd., B4-266A=20 > Hoffman Estates, IL. 60179=20 > Office: (847) 286-5735=20 > Email: Ernest.Knox@searshc.com=20 > Blackberry: 2244650553@messaging.sprintpcs.com=20 > Page via Skytel: 2244650553@sprint.skytel.com=20 > Informix or MySQL Primary: 9110210@skytel.com=20 > Informix or MySQL Secondary: 7276872@skytel.com=20 >=20 > " Yes we can make a Change! "=20 > " It's always a great day to watch Sports - GO LIONS, TIGERS, and = BEARS! "=20 > " Lets not forget - GO Pistons and Red Wings! "=20 > GSU=20 > *******************************************************************=20 >=20 > ----- Original Message -----=20 > From: Jack Parker [mailto:jack.parker4@verizon.net]=20 > Sent: Tuesday, April 05, 2011 07:23 AM=20 > To: ids@iiug.org <ids@iiug.org>=20 > Subject: Re: Data Loads taking lots of extra time [23322]=20 >=20 > Note, that it may not be practical to drop and re-add indices on a = large =3D=20 > table. Are all of the 9 indices used? Check sysptprof for more reads =3D= =20 > than writes. if that ratio is 1:1, you may be using the index only =3D=20= > during the insert.=20 >=20 > j.=20 >=20 > On Apr 5, 2011, at 7:12 AM, Knox, Ernest wrote:=20 >=20 >> There are nine indexes. I'll check the extents.=3D20=20 >> =3D20=20 >> Thanks,=3D20=20 >> = *******************************************************************=3D20 =20= >> 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 =20= >> =3D20=20 >> ----- Original Message -----=3D20=20 >> From: Jack Parker [mailto:jack.parker4@verizon.net]=3D20=20 >> Sent: Tuesday, April 05, 2011 06:17 AM=3D20=20 >> To: ids@iiug.org <ids@iiug.org>=3D20=20 >> Subject: Re: Data Loads taking lots of extra time [23319]=3D20=20 >> =3D20=20 >> Loads can take a long time if the table has not been sized =3D3D=3D20=20= >> appropriately. Constantly allocated new extents is a drag, it's = better =3D=20 > =3D3D=3D20=20 >> now with extent doubling, but always something to keep in mind. If = =3D3D=3D20=3D=20 >=20 >> there is not a lot of contiguous free space in the dbspace, that will = =3D=20 > =3D3D=3D20=20 >> cause the same effect=3D20=20 >> =3D20=20 >> Are there indices on the table? It's expensive to load into indexes. = =3D3D=3D=20 > =3D20=20 >> At times it may be faster to drop the indexes, alter the table type = to =3D=20 > =3D3D=3D20=20 >> RAW, load it, alter the table type back and recreate the indexes. = Note =3D=20 > =3D3D=3D20=20 >> that referential constraints count as indexes.=3D20=20 >> =3D20=20 >> cheers=3D20=20 >> j.=3D20=20 >> =3D20=20 >> On Apr 5, 2011, at 3:10 AM, GERARDO PADIERNA wrote:=3D20=20 >> =3D20=20 >>> Hi,=3D3D20=3D20=20 >>> have you checked if update statistics has run properly, I mean, just = =3D=20 > =3D3D=3D20=20 >> in case?=3D3D20=3D20=20 >>> select constructed from sysdistrib=3D3D20=3D20=20>>> We had a case when for some reason the cron job that ran the update = =3D3D=3D=20 > =3D20=20 >> statistics=3D3D20=3D20=20 >>> stopped working. But that should slow down the OLTP access = too.=3D3D20=3D20=3D=20 >=20 >>> Well, as I said, just in case you didn't check.=3D3D20=3D20=20 >>> =3D3D20=3D20=20 >>> Cheers,=3D3D20=3D20=20 >>> Gerardo=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

\

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 *******************************************************************=3D3D20 =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 *******************************************************************=3D3D20 =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,

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 > *******************************************************************=3D3D20 > =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 > *******************************************************************=3D3D20 > =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 > >>

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=3D3D

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 > > > Fro

16 million rows? Unless you are loading another ~10 million+, it does = not make sense to drop and re-add indices. I just went through scripting load/unload using HPL in Perl so it works = on Windows and *nix. I've requested permission to take it public, but = that process takes time. j. =20 On Apr 5, 2011, at 10:32 AM, Knox, Ernest wrote: > So are you saying that disabling and re-enabling the indexes could = take=20 > a long time to perform?=20 >=20 > There are only 16 million rows in the table.=20 >=20 > They also performed a defrag of the disk.=20 >=20 > Art, can your load script go in windows servers?=20 >=20 > Thanks,=20 > *******************************************************************=20 > Ernie Knox=20 > IT Database Administrator Specialist=20 > Sears Holdings - BU: I & T Group=20 > 3333 Beverly Rd., B4-266A=20 > Hoffman Estates, IL. 60179=20 > Office: (847) 286-5735=20 > Email: Ernest.Knox@searshc.com=20 > Blackberry: 2244650553@messaging.sprintpcs.com=20 > Page via Skytel: 2244650553@sprint.skytel.com=20 > Informix or MySQL Primary: 9110210@skytel.com=20 > Informix or MySQL Secondary: 7276872@skytel.com=20 > =3D20=20 > " Yes we can make a Change! "=20 > " It's always a great day to watch Sports - GO LIONS, TIGERS, and = BEARS!=20 > "=20 > " Lets not forget - GO Pistons and Red Wings! "=20 > GSU=20 > *******************************************************************=20 >=20 > -----Original Message-----=20 > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of=20= > Jack Parker=20 > Sent: Tuesday, April 05, 2011 8:01 AM=20 > To: ids@iiug.org=20 > Subject: Re: Data Loads taking lots of extra time [23324]=20 >=20 > Then also check for disk fragmentation. Windows and onspaces do not = =3D3D=3D20=20 > play well together. Right click my computer, manage, disk management = is=20 > =3D3D=3D20=20 > in there somewhere,=3D20=20 >=20 > I don't know what you're using to load now. HPL is the fastest loader = =3D3D=3D=20 > =3D20=20 > for that release. I have not played with external tables on 11.7 yet, = =3D3D=3D=20 > =3D20=20 > but they rocked in XPS. There is a load FAQ that walks through the = =3D3D=3D20=20 > various loaders and their pros and cons..(he looks). Although that = site=20 > =3D3D=3D20=20 > has fallen off the web. I have a copy if you want it.=3D20=20 >=20 > Certainly 9.4 is older, and probably no longer supported (10 went EOS = in=20 > =3D3D=3D20=20 > Sept 2010), but I have a customer who still uses 2.1 last time I = =3D3D=3D20=20 > checked.=3D20=20 >=20 > With Windows these is no ipload interface, so you have to set up jobs = =3D3D=3D=20 > =3D20=20 > with onpladm. First create a project, then create the jobs.=3D20=20 >=20 > onpladm create project <project>=3D20=20 > onpladm create job <name> -p <project> -d <file> -D <database> -t = =3D3D=3D20=20 > <table> -fl=3D20=20 > then load with:=3D20=20 > onpload -p <project> -j <job> -fl=3D3D20=3D20=20 >=20> There will be some variation in the create job according to what you = =3D3D=3D20=20 > have, and layout of the source file will need to match the table - = =3D3D=3D20=20 > unless you want to get into onpladm trickery. The onpladm "interface" = =3D3D=3D=20 > =3D20=20 > is: "type a portion of the command, hit return and it shows you the = =3D3D=3D20=20 > options" - even explains some of them. Documentation for it is key - = =3D3D=3D20=20 > google for that.=3D20=20 >=20 > j.=3D20=20 >=20 > On Apr 5, 2011, at 7:34 AM, Knox, Ernest wrote:=3D20=20 >=20 >> This is IDS 9.4 on OS Win2K. Should I also allow him to use another = =3D3D=3D=20 > =3D20=20 > load=3D3D20=3D20=20 >> method or upgrade?=3D3D20=3D20=20 >> =3D3D20=3D20=20 >> Thanks,=3D3D20=3D20=20 >> = *******************************************************************=3D3D20= =20 >=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=20 > BEARS! "=3D3D20=3D20=20 >> " Lets not forget - GO Pistons and Red Wings! "=3D3D20=3D20=20 >> GSU=3D3D20=3D20=20 >> = *******************************************************************=3D3D20= =20 >=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 07:23 AM=3D3D20=3D20=20 >> To: ids@iiug.org <ids@iiug.org>=3D3D20=3D20=20 >> Subject: Re: Data Loads taking lots of extra time [23322]=3D3D20=3D20=20= >> =3D3D20=3D20=20 >> Note, that it may not be practical to drop and re-add indices on a = =3D3D=3D20=20 > large =3D3D3D=3D3D20=3D20=20 >> table. Are all of the 9 indices used? Check sysptprof for more reads=20= > =3D3D3D=3D3D=3D20=20 > =3D3D20=3D20=20 >> than writes. if that ratio is 1:1, you may be using the index only=20 > =3D3D3D=3D3D20=3D3D=3D20=20 >=20 >> during the insert.=3D3D20=3D20=20 >> =3D3D20=3D20=20 >> j.=3D3D20=3D20=20 >> =3D3D20=3D20=20 >> On Apr 5, 2011, at 7:12 AM, Knox, Ernest wrote:=3D3D20=3D20=20 >> =3D3D20=3D20=20 >>> There are nine indexes. I'll check the extents.=3D3D3D20=3D3D20=3D20=20= >>> =3D3D3D20=3D3D20=3D20=20 >>> Thanks,=3D3D3D20=3D3D20=3D20=20 >>> =3D3D=3D20=20 > = *******************************************************************=3D3D3D= 20=20 > =3D3D20=3D3D=3D20=20 >=20 >>> Ernie Knox=3D3D3D20=3D3D20=3D20=20 >>> IT Database Administrator Specialist=3D3D3D20=3D3D20=3D20=20 >>> Sears Holdings=3D3D3D20=3D3D20=3D20=20 >>> 3333 Beverly Rd., B4-266A=3D3D3D20=3D3D20=3D20=20 >>> Hoffman Estates, IL. 60179=3D3D3D20=3D3D20=3D20=20 >>> Office: (847) 286-5735=3D3D3D20=3D3D20=3D20=20 >>> Email: Ernest.Knox@searshc.com=3D3D3D20=3D3D20=3D20=20 >>> Blackberry: 2244650553@messaging.sprintpcs.com=3D3D3D20=3D3D20=3D20=20= >>> Page via Skytel: 2244650553@sprint.skytel.com=3D3D3D20=3D3D20=3D20=20= >>> Informix or MySQL Primary: 9110210@skytel.com=3D3D3D20=3D3D20=3D20=20= >>> Informix or MySQL Secondary: 7276872@skytel.com=3D3D3D20=3D3D20=3D20=20= >>> =3D3D3D20=3D3D20=3D20=20 >>> " Yes we can make a Change! "=3D3D3D20=3D3D20=3D20=20 >>> " It's always a great day to watch Sports - GO LIONS, TIGERS, and=20 > =3D3D3D=3D3D20=3D3D=3D20=20 >=20 >> BEARS! "=3D3D3D20=3D3D20=3D20=20 >>> " Lets not forget - GO Pistons and Red Wings! "=3D3D3D20=3D3D20=3D20=20=@@N

Just to follow-up on this discussion. I had the customer to set the pdqpriority to 25 for starters and the loads were much faster. I'm waiting to hear from him for the week and will let you know. I asked him to increase the quantity over time to a comfortable amount less than 100. Thanks for everyone's suggestions, ******************************************************************* 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 <mailto:2244650553@messaging.sprintpcs.com> Page via Skytel: 2244650553@sprint.skytel.com <mailto:2244650553@sprint.skytel.com> Informix or MySQL Primary: 9110210@skytel.com <mailto:9110210@skytel.com> Informix or MySQL Secondary: 7276872@skytel.com <mailto: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: Swanson, Donald Sent: Thursday, April 07, 2011 9:20 AM To: Knox, Ernest Subject: RE: FW: Data Loads taking lots of extra time [23287] Hi Ernie, I will ask wintel to check the controller cache. Without the controller cache being checked, I set pdqpriority. Yesterday's load took 9 minutes, and todays took 35. Did we just get lucky or did pdqpriority help? First log is without pdqpriority on 4/5. Execution Started: 4/5/2011 9:40:0 Adding asccoachdetail data from dailySalesAssocAndLoc.txt Database selected. 128126 row(s) loaded. Database closed. Execution Complete: 4/5/2011 11:15:24 Execution Started: 4/6/2011 5:35:1 Adding asccoachdetail data from dailySalesAssocAndLoc.txt Database selected. PDQ Priority set. 148050 row(s) loaded. Database closed. Execution Complete: 4/6/2011 5:44:29 Execution Started: 4/7/2011 5:30:0 Adding asccoachdetail data from dailySalesAssocAndLoc.txt Database selected. PDQ Priority set. 162444 row(s) loaded. Database closed. Execution Complete: 4/7/2011 6:4:17 Don Swanson From: Bogdan BOTEZ [mailto:bmbogdan@gmail.com] Sent: Wednesday, April 06, 2011 03:17 AM To: Knox, Ernest Subject: Re: FW: Data Loads taking lots of extra time [23287] I had more than once the experience of failed/depleted battery for the controller cache where the drivers will disable the cache resulting in an 5x-6x-... performance deterioration. Did you checked if the basic/direct IO (without Informix) didn't degraded? On unix land you can do this using dd, on windows there must be utilities that will do the same (just not delivered with the OS). Regards, Bogdan BOTEZ. On Fri, Apr 1, 2011 at 14:55, Knox, Ernest <Ernest.Knox@searshc.com> wrote: IDS 9.40.TC1 OS: Win 2000 SP4 Intel(r) XEON(tm) MP CPU 1.90GHz AT/AT COMPATIBLE 3,669,424 KB RAM =20 Questions: =20 1. I did see other processes running at the same time, which could be utilizing internal disk controller. Should I look at something else? 2. Should I let the customer use a different load method? 3. Would a newer version perform faster (11.7)? =20 Problem: =20 >From my Customer: =20 "Sent: Thu 3/31/2011 9:37 AM =20 After March 15, our loads to the asc_coach_detail table has increased from loading in 20-45 minutes to 3-5 hours. The amount of data rows has not increased dramatically, to support the delay. Can you check to see if there has been some change that has facilitated this delay in loading? I am copying the wintel team as well in case they are aware of any changes to the servers listed below." =20 My initial response to the Customer and his reply: =20 Sent: Thursday, March 31, 2011 5:13 PM I really don't see much of an informix issue. =20 1. When (time) are you performing these loads? Normally between 5:30 and 6:30 Central. Sometimes after 7am. 2. Are the loads being performed during OLTP times? We are data loading into a reporting database. This database has no OLTP 3. How many rows are you loading between commits? Mary might know. She did analysis in the Fall when we had a similar issue.=20=20 4. You have 9 indexes to also insert into, which can take time. Yes, but until 3/15, load time was <1 hour. After 3/15, consistently >3 hours=20 5. You may want to upgrade to new version of informix 11.5+ Bring it on. We have it in dev, but no time to test it. Joel???? 6. Things seem to be running fast right now. Our load completed at 9:46.=20 7. Are you using load, DBload, HPL, or what to load the data? DBLOAD because of the volume of data.=20 8. How many rows are you loading at each time? 200-300k rows. We loaded 550k (month end data) rows on 2/27 in less than an hour.=20 9. How many of these loads are running each day? only 3 or 4 loads use dbload. We have several (maybe 50 per day) loads using an odbc connection. When I sent the email this morning, the asc_coach_detail dbload was the only hit on the database. =20 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 <tel:%28847%29%20286-5735> Email: Ernest.Knox@searshc.com Blackberry: 2244650553@messaging.sprintpcs.com <mailto:2244650553@messaging.sprintpcs.com>=20 Page via Skytel: 2244650553@sprint.skytel.com <mailto:2244650553@sprint.skytel.com>=20 Informix or MySQL Primary: 9110210@skytel.com <mailto:9110210@skytel.com>=20 Informix or MySQL Secondary: 7276872@skytel.com <mailto:7276872@skytel.com>=20 =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 ******************************************************************* 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. **************************************