Join IIUG
 for   
 

Informix News
18 Nov 13 - ZDNet - Top 20 mobile skills in demand... Read
09 Sep 13 - telecompaper - Shaspa and Tatung have shown a new smart home platform at Ifa in Berlin. Powered by the IBM Informix software... Read
06 Sep 13 - IBM data magazine - Mission Accomplished - Miami, Florida will be the backdrop for the 2014 IIUG Informix Conference... Read
01 Feb 13 - IBM Data Magazine - Are your database backups safe? Lester Knutsen (IBM Champion) writes about database back up safety using "archecker"... Read
14 Nov 12 - IBM - IBM's Big Data For Smart Grid Goes Live In Texas... Read
3 Oct 12 - The Financial - IBM and TransWorks Collaborate to Help Louisiana-Pacific Corporation Achieve Supply Chain Efficiency... Read
28 Aug 12 - techCLOUD9 - Splunk kicks up a SaaS Storm... Read
10 Aug 12 - businessCLOUD9 - Is this the other half of Cloud monitoring?... Read
3 Aug 12 - IBM data management - Supercharging the data warehouse while keeping costs down IBM Informix Warehouse Accelerator (IWA) delivers superior performance for in-memory analytics processing... Read
2 Aug 12 - channelbiz - Oninit Group launches Pay Per Pulse cloud-based service... Read
28 May 12 - Bloor - David Norfolk on the recent Informix benchmark "pretty impressive results"... Read
23 May 12 - DBTA - Informix Genero: A Way to Modernize Informix 4GL Applications... Read
9 Apr 12 - Mastering Data Management - Upping the Informix Ante: Advanced Data Tools... Read
22 Mar 12 - developerWorks - Optimizing Informix database access... Read
14 Mar 12 - BernieSpang.com - International Informix User Group set to meet in San Diego... Read
1 Mar 12 - IBM Data Management - IIUG Heads West for 2012 - Get ready for sun and sand in San Diego... Read
1 Mar 12 - IBM Data Management - Running Informix on Solid-State Drives.Speed Up Database Access... Read
26 Feb 12 - BernieSpan.com - Better results, lower cost for a broad set of new IBM clients and partners... Read
24 Feb 12 - developerWorks - Informix Warehouse Accelerator: Continuous Acceleration during Data Refresh... Read
6 Feb 12 - PRLOG - Informix port delivers unlimited database scalability for popular SaaS application ... Read
2 Feb 12 - developerWorks - Loading data with the IBM Informix TimeSeries Plug-in for Data Studio... Read
1 Feb 12 - developerWorks - 100 Tech Tips, #47: Log-in to Fix Central... Read
13 Jan 12 - MC Press online - Informix Dynamic Server Entices New Users with Free Production Edition ... Read
11 Jan 12 - Computerworld - Ecologic Analytics and Landis+Gyr -- Suitors Decide to Tie the Knot... Read
9 Jan 12 - planetIDS.com - DNS impact on Informix / Impacto do DNS no Informix... Read
8 Sep 11 - TMCnet.com - IBM Offers Database Solution to Enable Smart Meter Data Capture... Read
1 Aug 11 - IBM Data Management Magazine - IIUG user view: Happy 10th anniversary to IBM and Informix... Read
8 Jul 11 - Database Trends and Applications - Managing Time Series Data with Informix... Read
31 May 11 - Smart Grid - The meter data management pitfall utilities are overlooking... Read
27 May 11 - IBM Data Management Magazine - IIUG user view: Big data, big time ( Series data, warehouse acceleration, and 4GLs )... Read
16 May 11 - Business Wire - HiT Software Announces DBMoto for Enterprise Integration, Adds Informix. Log-based Change Data Capture... Read
21 Mar 11 - Yahoo! Finance - IBM and Cable&Wireless Worldwide Announce UK Smart Energy Cloud... Read
14 Mar 11 - MarketWatch - Fuzzy Logix and IBM Unveil In-Database Analytics for IBM Informix... Read
11 Mar 11 - InvestorPlace - It's Time to Give IBM Props: How many tech stocks are up 53% since the dot-com boom?... Read
9 Mar 11 - DBTA - Database Administration and the Goal of Diminishing Downtime... Read
2 Feb 11 - DBTAs - Informix 11.7 Flexible Grid Provides a Different Way of Looking at Database Servers... Read
27 Jan 11 - exactsolutions - Exact to Add Informix Support to Database Replay, SQL Monitoring Solutions... Read
25 Jan 11 - PR Newswire - Bank of China in the UK Works With IBM to Become a Smarter, Greener Bank... Read
12 Oct 10 - Database Trends and Applications - Informix 11.7: The Beginning of the Next Decade of IBM Informix... Read
20 Sep 10 - planetIDS.com - ITG analyst paper: Cost/Benefit case for IBM Informix as compared to Microsoft SQL Server... Read
20 Jul 10 - IBM Announcements - IBM Informix Choice Edition V11.50 helps deploy low-cost scalable and reliable solutions for Apple Macintosh and Microsoft Windows... Read
20 Jul 10 - IBM Announcements - Software withdrawal: Elite Support for Informix Ultimate-C Edition... Read
24 May 10 - eWeek Europe - IBM Supplies Database Tech For EU Smart Grid... Read
23 May 10 - SiliconIndia - IBM's smart metering system allows wise use of energy... Read
21 May 10 - CNET - IBM to help people monitor energy use... Read
20 May 10 - ebiz - IBM Teams With Hildebrand To Bring Smart Metering To Homes Across Britain... Read
19 May 10 - The New Blog Times - Misurare il consumo energetico: DEHEMS è pronto... Read
19 May 10 - ZDNet - IBM software in your home? Pact enables five-city smart meter pilot in Europe... Read
17 March 10 - ZDNet (blog) David Morgenstern - TCO: New research finds Macs in the enterprise easier, cheaper to manage than... Read
17 March 2010 - Virtualization Review - ...key components of Big Blue's platform to the commercial cloud such as its WebSphere suite of application ser vers and its DB2 and Informix databases... Read
10 February 2010 - The Wall Street Journal - International Business Machines is expanding an initiative to win over students and professors on its products. How do they lure the college crowd?... Read

End of Support Dates
IDS 11.10 - 30 Sep 2012
Subscribe to notifications

IIUG on Facebook IIUG on Twitter

[ View Thread ] [ Post Response ] [ Return to Index ] [ Read Prev Msg ] [ Read Next Msg ]

IDS Forum

Re: Data Loads taking lots of extra time

Posted By: Jack Parker
Date: Tuesday, 5 April 2011, at 10:57 a.m.

In Response To: RE: Data Loads taking lots of extra time (Knox, Ernest)

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=

>>> GSU=3D3D3D20=3D3D20=3D20=20
>>> =3D3D=3D20=20
> =
*******************************************************************=3D3D3D=
20=20
> =3D3D20=3D3D=3D20=20
>=20
>>> =3D3D3D20=3D3D20=3D20=20
>>> ----- Original Message -----=3D3D3D20=3D3D20=3D20=20
>>> From: Jack Parker [mailto:jack.parker4@verizon.net]=3D3D3D20=3D3D20=3D=
20=20
>>> Sent: Tuesday, April 05, 2011 06:17 AM=3D3D3D20=3D3D20=3D20=20
>>> To: ids@iiug.org <ids@iiug.org>=3D3D3D20=3D3D20=3D20=20
>>> Subject: Re: Data Loads taking lots of extra time =
[23319]=3D3D3D20=3D3D20=3D20=20
>>> =3D3D3D20=3D3D20=3D20=20
>>> Loads can take a long time if the table has not been sized=20
> =3D3D3D3D=3D3D3D20=3D3D20=3D3D=3D20=20
>=20
>>> appropriately. Constantly allocated new extents is a drag, it's =
=3D3D=3D20=20
> better =3D3D3D=3D3D20=3D20=20
>> =3D3D3D3D=3D3D3D20=3D3D20=3D20=20
>>> now with extent doubling, but always something to keep in mind. If =
=3D3D=3D=20
> =3D20=20
> =3D3D3D3D=3D3D3D20=3D3D3D=3D3D20=3D20=20
>> =3D3D20=3D20=20
>>> there is not a lot of contiguous free space in the dbspace, that =
will=20
> =3D3D=3D20=20
> =3D3D3D=3D3D20=3D20=20
>> =3D3D3D3D=3D3D3D20=3D3D20=3D20=20
>>> cause the same effect=3D3D3D20=3D3D20=3D20=20
>>> =3D3D3D20=3D3D20=3D20=20
>>> Are there indices on the table? It's expensive to load into indexes.=20=

> =3D3D=3D20=20
> =3D3D3D3D=3D3D3D=3D3D20=3D20=20
>> =3D3D3D20=3D3D20=3D20=20
>>> At times it may be faster to drop the indexes, alter the table type =
=3D3D=20
>=20
> to =3D3D3D=3D3D20=3D20=20
>> =3D3D3D3D=3D3D3D20=3D3D20=3D20=20
>>> RAW, load it, alter the table type back and recreate the indexes. =
=3D3D=3D20=20
> Note =3D3D3D=3D3D20=3D20=20
>> =3D3D3D3D=3D3D3D20=3D3D20=3D20=20
>>> that referential constraints count as indexes.=3D3D3D20=3D3D20=3D20=20=

>>> =3D3D3D20=3D3D20=3D20=20
>>> cheers=3D3D3D20=3D3D20=3D20=20
>>> j.=3D3D3D20=3D3D20=3D20=20
>>> =3D3D3D20=3D3D20=3D20=20
>>> On Apr 5, 2011, at 3:10 AM, GERARDO PADIERNA wrote:=3D3D3D20=3D3D20=3D=
20=20
>>> =3D3D3D20=3D3D20=3D20=20
>>>> Hi,=3D3D3D3D20=3D3D3D20=3D3D20=3D20=20
>>>> have you checked if update statistics has run properly, I mean, =
just=20
> =3D3D=3D20=20
> =3D3D3D=3D3D20=3D20=20
>> =3D3D3D3D=3D3D3D20=3D3D20=3D20=20
>>> in case?=3D3D3D3D20=3D3D3D20=3D3D20=3D20=20
>>>> select constructed from sysdistrib=3D3D3D3D20=3D3D3D20=3D3D20=3D20=20=

>>>> We had a case when for some reason the cron job that ran the update=20=

> =3D3D=3D20=20
> =3D3D3D3D=3D3D3D=3D3D20=3D20=20
>> =3D3D3D20=3D3D20=3D20=20
>>> statistics=3D3D3D3D20=3D3D3D20=3D3D20=3D20=20
>>>> stopped working. But that should slow down the OLTP access =3D3D=3D20=
=20
> too.=3D3D3D3D20=3D3D3D20=3D3D3D=3D3D20=3D20=20
>> =3D3D20=3D20=20
>>>> Well, as I said, just in case you didn't =
check.=3D3D3D3D20=3D3D3D20=3D3D20=3D20=20
>>>> =3D3D3D3D20=3D3D3D20=3D3D20=3D20=20
>>>> Cheers,=3D3D3D3D20=3D3D3D20=3D3D20=3D20=20
>>>> Gerardo=3D3D3D3D20=3D3D3D20=3D3D20=3D20=20
>>>> =3D3D3D3D20=3D3D3D20=3D3D20=3D20=20
>>>> =3D3D3D3D20=3D3D3D20=3D3D20=3D20=20
>>>> =3D3D3D3D=3D3D3D20=3D3D20=3D20=20
>>> =3D3D3D=3D3D20=3D20=20
>> =3D3D=3D20=20
> =
************************************************************************=20=

> **=3D3D=3D20=20
> =3D3D3D=3D3D20=3D20=20
>> =3D3D3D3D=3D3D3D20=3D3D20=3D20=20
>>> *****=3D3D3D3D20=3D3D3D20=3D3D20=3D20=20
>>>> Forum Note: Use "Reply" to post a response in the discussion =
=3D3D3D=3D3D20=3D=20
> =3D20=20
>> forum.=3D3D3D3D20=3D3D3D3D=3D3D3D20=3D3D20=3D20=20
>>> =3D3D3D20=3D3D20=3D20=20
>>>> =3D3D3D3D20=3D3D3D20=3D3D20=3D20=20
>>> =3D3D3D20=3D3D20=3D20=20
>>> =3D3D3D20=3D3D20=3D20=20
>>> =3D3D3D=3D3D20=3D20=20
>> =3D3D=3D20=20
> =
************************************************************************=20=

> **=3D3D=3D20=20
> =3D3D3D=3D3D20=3D20=20
>> *****=3D3D3D20=3D3D20=3D20=20
>>> Forum Note: Use "Reply" to post a response in the discussion =3D3D=3D2=
0=20
> forum.=3D3D3D20=3D3D20=3D20=20
>>> =3D3D3D20=3D3D20=3D20=20
>>> =3D3D3D20=3D3D20=3D20=20
>>> =3D3D3D=3D3D20=3D20=20
>> =3D3D=3D20=20
> =
************************************************************************=20=

> **=3D3D=3D20=20
> =3D3D3D=3D3D20=3D20=20
>> *****=3D3D3D20=3D3D20=3D20=20
>>> Forum Note: Use "Reply" to post a response in the discussion =3D3D=3D2=
0=20
> forum.=3D3D3D20=3D3D3D=3D3D20=3D20=20
>> =3D3D20=3D20=20
>>> =3D3D3D20=3D3D20=3D20=20
>> =3D3D20=3D20=20
>> =3D3D20=3D20=20
>> =3D3D=3D20=20
> =
************************************************************************=20=

> **=3D3D=3D20=20
> *****=3D3D20=3D20=20
>> Forum Note: Use "Reply" to post a response in the discussion =
forum.=3D3D20=20
>=20
>> =3D3D20=3D20=20
>> =3D3D20=3D20=20
>> =3D3D=3D20=20
> =
************************************************************************=20=

> **=3D3D=3D20=20
> *****=3D3D20=3D20=20
>> Forum Note: Use "Reply" to post a response in the discussion=20
> forum.=3D3D20=3D3D=3D20=20
>=20
>> =3D3D20=3D20=20
>=20
> =
************************************************************************=20=

> *******=3D20=20
> Forum Note: Use "Reply" to post a response in the discussion forum.=3D20=
=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

Messages In This Thread

[ View Thread ] [ Post Response ] [ Return to Index ] [ Read Prev Msg ] [ Read Next Msg ]

IDS Forum is maintained by Administrator with WebBBS 5.12.