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

IDS11 on Linux - insert into temp takes very long

Posted By: Nandkishor Singare
Date: Monday, 20 July 2009, at 3:18 p.m.

Hi,

Environment details -

Informix 11.50.FC4
OS Red hat linux - 2.6.18-92.el5.

One query with two tables joined runs fast and gives result within 1-2
minutes.
But when I use same query to insert result into temp table "select... from
.. insert into temp t1 with no log" it takes around 8-9 hours.
Records -
Table 1 : 32503
Table 2 : 16827089

I doubt about temp dbspace but temp dbspaces are defined in config and
environment.
I do not see much change in tempdbspace read/write with onstat -D.
Also no files created in /tmp directory.

What can be issue? and where to check? is it know issue with informix11?
Please help.

sorry for the irrelevent subject in earlier mail.

Onstat -d output
---------------------------------------------------------------------------------------------

IBM Informix Dynamic Server Version 11.50.FC4 -- On-Line -- Up 4 days
10:49:03 -- 7838356 Kbytes
Dbspaces
address number flags fchunk nchunks pgsize flags
owner name
12b93d028 1 0x40001 1 1 2048 N B
informix ibs_ia1_rootdbs
12f336418 2 0x40001 2 1 2048 N B
informix ibs_ia1_plogdbs
12f3365b0 3 0x40001 3 3 2048 N B
informix ibs_ia1_logdbs1
12f336748 4 0x40001 6 3 2048 N B
informix ibs_ia1_logdbs2
12f3368e0 5 0x42001 9 1 2048 N TB
informix ibs_ia1_tmpdbs1
12f336a78 6 0x42001 10 1 2048 N TB
informix ibs_ia1_tmpdbs2
12f336c10 7 0x42001 11 1 2048 N TB
informix ibs_ia1_tmpdbs3
12f336da8 8 0x42001 12 1 2048 N TB
informix ibs_ia1_tmpdbs4
12f335cc0 9 0x48001 13 1 2048 N SB
informix ibs_ia1_ersbs
12f335e58 10 0x40001 14 1 2048 N B
informix ibs_ia1_erdbs
12f337028 11 0x40001 15 1 2048 N B
informix ibs_ia1_sadmdbs
12f3371c0 12 0x40001 16 7 2048 N B
informix ibs_ia1_mxdbs1
12f337358 13 0x40001 22 6 2048 N B
informix ibs_ia1_mxdbs2
12f3374f0 14 0x40001 28 4 2048 N B
informix ibs_ia1_mxdbs3
12f337688 15 0x40001 32 1 2048 N B
informix ibs_ia1_mxinvhdr_idx1
12f337820 16 0x40001 33 1 2048 N B
informix ibs_ia1_mxinvhdr_idx2
12f3379b8 17 0x40001 34 1 2048 N B
informix ibs_ia1_mxinvdtl_idx1
12f337b50 18 0x40001 35 1 2048 N B
informix ibs_ia1_mxinvdtl_idx2
12f337ce8 19 0x40001 36 1 2048 N B
informix ibs_ia1_mxinvdtl_dbs1
12f337e80 20 0x40001 37 1 2048 N B
informix ibs_ia1_mxinvdtl_dbs2
12f339028 21 0x40001 38 1 2048 N B
informix ibs_ia1_mxinvdtl_dbs3
12f3391c0 22 0x40001 39 1 2048 N B
informix ibs_ia1_mxinvdtl_dbs4
12f339358 23 0x40001 40 1 2048 N B
informix ibs_ia1_mxinvdtl_dbs5
12f3394f0 24 0x40001 41 1 2048 N B
informix ibs_ia1_mxinvdtl_dbs6
12f339688 25 0x40001 42 2 2048 N B
informix ibs_ia1_mxinvhdr_dbs1
12f339820 26 0x40001 44 2 2048 N B
informix ibs_ia1_mxinvhdr_dbs2
12f3399b8 27 0x40001 46 2 2048 N B
informix ibs_ia1_mxinvhdr_dbs3
12f339b50 28 0x40001 48 2 2048 N B
informix ibs_ia1_mxinvhdr_dbs4
12f339ce8 29 0x40001 50 2 2048 N B
informix ibs_ia1_mxinvhdr_dbs5
12f339e80 30 0x40001 52 2 2048 N B
informix ibs_ia1_mxinvhdr_dbs6
30 active, 2047 maximum
Chunks
address chunk/dbs offset size free bpages
flags pathname
12b93d1c0 1 1 0 512000 504783
PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_rootdbs.1
12f33b028 2 2 0 2048000 47947
PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_plogdbs.1
12f33b218 3 3 0 1024000 23947
PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_logdbs1.1
12f33b408 4 3 0 1024000 23997
PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_logdbs1.2
12f33b5f8 5 3 0 1024000 23997
PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_logdbs1.3
12f33b7e8 6 4 0 1024000 23947
PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_logdbs2.1
12f33b9d8 7 4 0 1024000 23997
PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_logdbs2.2
12f33bbc8 8 4 0 1024000 23997
PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_logdbs2.3
12f33bdb8 9 5 0 1024000 1023907
PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_tmpdbs1.1
12f33d028 10 6 0 1024000 1023923
PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_tmpdbs2.1
12f33d218 11 7 0 1024000 1023947
PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_tmpdbs3.1
12f33d408 12 8 0 1024000 1023947
PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_tmpdbs4.1
12f33d5f8 13 9 0 1024000 955003 955004
POSB- /usr/informix_ia1_ibs/infdev/ibs_ia1_ersbs.1

Metadata 68943 51302 68943
12f33d7e8 14 10 0 1024000 1023907
PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_erdbs.1
12f33d9d8 15 11 0 1024000 1021301
PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_sadmdbs.1
12f33dbc8 16 12 0 5120000 17037
PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxdbs1.1
12f33ddb8 17 12 0 5120000 0
PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxdbs1.2
12f33f028 18 12 0 5120000 69720
PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxdbs1.3
12f33f218 19 12 0 5120000 661308
PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxdbs1.4
12f33f408 20 12 0 5120000 5119847
PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxdbs1.5
12f33f5f8 21 12 0 5120000 0
PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxdbs1.6
12f33f7e8 22 13 0 5120000 54623
PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxdbs2.1
12f33f9d8 23 13 0 5120000 0
PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxdbs2.2
12f33fbc8 24 13 0 5120000 5192
PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxdbs2.3
12f33fdb8 25 13 0 5120000 192696
PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxdbs2.4
12f342028 26 13 0 5120000 2619997
PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxdbs2.5
12f342218 27 13 0 5120000 5119997
PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxdbs2.6
12f342408 28 14 0 5120000 333206
PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxdbs3.1
12f3425f8 29 14 0 5120000 0
PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxdbs3.2
12f3427e8 30 14 0 5120000 1684659
PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxdbs3.3
12f3429d8 31 14 0 5120000 2619997
PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxdbs3.4
12f342bc8 32 15 0 1024000 799426
PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxinvhdr_idx1.1
12f342db8 33 16 0 1024000 861518
PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxinvhdr_idx2.1
12f344028 34 17 0 1024000 795128
PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxinvdtl_idx1.1
12f344218 35 18 0 1024000 836397
PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxinvdtl_idx2.1
12f344408 36 19 0 5120000 3839947
PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxinvdtl_dbs1.1
12f3445f8 37 20 0 5120000 3839947
PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxinvdtl_dbs2.1
12f3447e8 38 21 0 5120000 3839947
PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxinvdtl_dbs3.1
12f3449d8 39 22 0 5120000 3839947
PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxinvdtl_dbs4.1
12f344bc8 40 23 0 5120000 3839947
PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxinvdtl_dbs5.1
12f344db8 41 24 0 5120000 3839947
PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxinvdtl_dbs6.1
12f346028 42 25 0 5120000 1791947
PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxinvhdr_dbs1.1
12f346218 43 25 0 5120000 5119997
PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxinvhdr_dbs1.2
12f346408 44 26 0 5120000 1791947
PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxinvhdr_dbs2.1
12f3465f8 45 26 0 5120000 5119997
PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxinvhdr_dbs2.2
12f3467e8 46 27 0 5120000 1791947
PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxinvhdr_dbs3.1
12f3469d8 47 27 0 5120000 5119997
PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxinvhdr_dbs3.2
12f346bc8 48 28 0 5120000 1791947
PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxinvhdr_dbs4.1
12f346db8 49 28 0 5120000 5119997
PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxinvhdr_dbs4.2
12f349028 50 29 0 5120000 1791947
PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxinvhdr_dbs5.1
12f349218 51 29 0 5120000 5119997
PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxinvhdr_dbs5.2
12f349408 52 30 0 5120000 1791947
PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxinvhdr_dbs6.1
12f3495f8 53 30 0 5120000 5119997
PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxinvhdr_dbs6.2
12f3497e8 54 12 0 5120000 0
PO-B- /usr/informix_ia1_ibs/infdev/ibs_ia1_mxdbs1.7
54 active, 32766 maximum
NOTE: The values in the "size" and "free" columns for DBspace chunks are

displayed in terms of "pgsize" of the DBspace to which they belong.
Expanded chunk capacity mode: always

---------------------------------------------------------------------------------------------
SQL EXPLAIN output - with (avoid execute clause.)

---------------------------------------------------------------------------------------------
QUERY: (OPTIMIZATION TIMESTAMP: 07-20-2009 12:12:21)
------
select b.oawb_fl from to_invacct_ro a, o_awb b where a.accnt_no =

b.bill_accnt_no and b.lck_flag = 'I' and b.shp_dt <= a.shp_select_dt
and

b.bill_doc_ty in ('R', 'O') into temp t1 with no log

Estimated Cost: 57451
Estimated # of Rows Returned: 156
1) ibsadm.a: INDEX PATH

(1) Index Keys: accnt_no shp_select_dt (Key-Only) (Serial, fragments:
ALL)
2) ibsadm.b: INDEX PATH

Filters: ibsadm.b.lck_flag = 'I'

(1) Index Keys: bill_accnt_no shp_dt bill_doc_ty (Key-First) (Serial,
fragments: ALL)

Lower Index Filter: ibsadm.a.accnt_no = ibsadm.b.bill_accnt_no

Upper Index Filter: ibsadm.b.shp_dt <= ibsadm.a.shp_select_dt

Index Key Filters: (ibsadm.b.bill_doc_ty IN ('R' , 'O' ))
NESTED LOOP JOIN

---------------------------------------------------------------------------------------------

Thanks,
Nandkishor

--000e0cd329b4ae353a046f280145

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.