One temp space fills and 4gl fails
A 4GL program loading a temp table filled one of three temp dbspaces (tempdb3) and failed instead of spilling into the other two. Respondents ruled out down spaces, logged temp spaces and DBSPACETEMP overrides, and explained the actual behaviour: only implicit temp tables (e.g. SELECT INTO TEMP) are spread round-robin across temp dbspaces; an explicitly created temp table lives in a single dbspace and fails when that space fills. Brian fixed it by temporarily adding a large chunk to each temp dbspace to get the report out, then changing the 4GL to create the temp table fragmented round-robin across all temp dbspaces.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Got a 4gl program that fills up one temp dbspace inserting into a temp table then fails. I have 3 temp dbspaces, but it's not overflowing into the other two, it just fills up the tempdb3 and fails. I thought there would be an automatic overflow into the other two temp spaces, could this be a bug? Informix version 10.0.FC3R1 HPUX B11.11 # temporary files in /tmp instead. DBSPACETEMP tempdb1,tempdb2,tempdb3 # Default temp dbspaces Thanks, Brian Minnick
Brian-
Post the output of onstat -D. I'm thinking that tempdb3 may be
defined differently than the others.
--EEM
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> BRIAN MINNICK
> Sent: Wednesday, February 06, 2008 10:36 AM
> To: ids@iiug.org
> Subject: One temp space fills and 4gl fails [11201]
>
> Got a 4gl program that fills up one temp dbspace inserting into a temp
> table
> then fails. I have 3 temp dbspaces, but it's not overflowing into the
> other
> two, it just fills up the tempdb3 and fails.
>
> I thought there would be an automatic overflow into the other two temp
> spaces,
> could this be a bug?
>
> Informix version 10.0.FC3R1
> HPUX B11.11
> # temporary files in /tmp instead.
>
> DBSPACETEMP tempdb1,tempdb2,tempdb3 # Default temp dbspaces
>
> Thanks,
>
> Brian Minnick
>
>
>
************************************************************************
**
> *****
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
> See you at the IIUG Informix 2008 Conference
> The Power Conference for Informix Professionals
> April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas
> http://www.iiug.org/conf
> Registration Now Open!!
As far as I know, all 3 temp dbspaces should be filled up concurrently unless: 1. some env variables overide this default 2. the other 2 temp dbspaces are down? ----- Original Message ---- From: BRIAN MINNICK <minnickbrian@aim.com> To: ids@iiug.org Sent: Wednesday, February 6, 2008 11:35:52 AM Subject: One temp space fills and 4gl fails [11201] Got a 4gl program that fills up one temp dbspace inserting into a temp table then fails. I have 3 temp dbspaces, but it's not overflowing into the other two, it just fills up the tempdb3 and fails. I thought there would be an automatic overflow into the other two temp spaces, could this be a bug? Informix version 10.0.FC3R1 HPUX B11.11 # temporary files in /tmp instead. DBSPACETEMP tempdb1,tempdb2,tempdb3 # Default temp dbspaces Thanks, Brian Minnick ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. See you at the IIUG Informix 2008 Conference The Power Conference for Informix Professionals April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas http://www.iiug.org/conf Registration Now Open!! ________________________________________________________________________________ ____ Never miss a thing. Make Yahoo your home page. http://www.yahoo.com/r/hs
Kern- Another reason could be the situation we have in our system. I have four temp spaces, one of which is logged (so that any temp tables mistakenly created with logging turned on won't end up in rootdbs). If a user created a logged temp table and loaded 500MB worth of data into it, they would have the same problem Brian is having, even though three other temp spaces are empty. --EEM > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Kern > Doe > Sent: Wednesday, February 06, 2008 11:11 AM > To: ids@iiug.org > Subject: Re: One temp space fills and 4gl fails [11203] > > As far as I know, all 3 temp dbspaces should be filled up concurrently > unless: > 1. some env variables overide this default > 2. the other 2 temp dbspaces are down? > > ----- Original Message ---- > From: BRIAN MINNICK <minnickbrian@aim.com> > To: ids@iiug.org > Sent: Wednesday, February 6, 2008 11:35:52 AM > Subject: One temp space fills and 4gl fails [11201] > > Got a 4gl program that fills up one temp dbspace inserting into a temp > table > then fails. I have 3 temp dbspaces, but it's not overflowing into the > other > two, it just fills up the tempdb3 and fails. > > I thought there would be an automatic overflow into the other two temp > spaces, > could this be a bug? > > Informix version 10.0.FC3R1 > HPUX B11.11 > # temporary files in /tmp instead. > > DBSPACETEMP tempdb1,tempdb2,tempdb3 # Default temp dbspaces > > Thanks, > > Brian Minnick > > > ************************************************************************ ** > ***** > Forum Note: Use "Reply" to post a response in the discussion forum. > > See you at the IIUG Informix 2008 Conference > The Power Conference for Informix Professionals > April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas > http://www.iiug.org/conf > Registration Now Open!! > > > ________________________________________________________________________ __ > __________ > Never miss a thing. Make Yahoo your home page. > http://www.yahoo.com/r/hs > > > ************************************************************************ ** > ***** > Forum Note: Use "Reply" to post a response in the discussion forum. > > See you at the IIUG Informix 2008 Conference > The Power Conference for Informix Professionals > April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas > http://www.iiug.org/conf > Registration Now Open!!
IBM Informix Dynamic Server Version 10.00.FC3R1 -- On-Line (Prim) -- Up 3 days
10:40:10 -- 1403348 Kbytes
Dbspaces
address number flags fchunk nchunks pgsize flags owner name
c00000003b2bee88 1 0x1 1 1 2048 N informix rootdbs
c00000003c934848 2 0x1 2 1 2048 N informix phylog
c00000003c9349e0 3 0x1 3 1 2048 N informix logfiles
c00000003c934b78 4 0x1001 4 5 2048 N informix db0
c00000003c934d10 5 0x2001 5 1 2048 N T informix tempdb1
c00000003c937028 6 0x2001 6 1 2048 N T informix tempdb2
c00000003c9371c0 7 0x2001 7 1 2048 N T informix tempdb3
c00000003c937358 8 0x1 12 2 2048 N informix dw0
8 active, 2047 maximum
Chunks
address chunk/dbs offset size free bpages flags pathname
c00000003b2bf028 1 1 4 64000 46215 PO-- /exe_prd/dblinks/ROOTDBS_PRD
c00000003c9333d0 2 2 3 49149 10096 PO-- /exe_prd/dblinks/PHYLOG_PRD
c00000003c933570 3 3 3 1048571 48518 PO-- /exe_prd/dblinks/LOGFILES_PRD
c00000003c933710 4 4 3 1032189 64 PO-- /exe_prd/dblinks/DB0_PRD
c00000003c9338b0 5 5 3 114685 114536 PO-- /exe_prd/dblinks/TEMPDB1_PRD
c00000003c933a50 6 6 3 114685 113039 PO-- /exe_prd/dblinks/TEMPDB2_PRD
c00000003c933bf0 7 7 3 114685 114536 PO-- /exe_prd/dblinks/TEMPDB3_PRD
c00000003c933d90 8 4 3 1032189 29 PO-- /exe_prd/dblinks/DB0_PRD_CH2
c00000003c934028 9 4 3 1032189 12 PO-- /exe_prd/dblinks/DB0_PRD_CH3
c00000003c9341c8 10 4 3 1032189 992 PO-- /exe_prd/dblinks/DB0_PRD_CH4
c00000003c934368 11 4 3 1032189 374031 PO-- /exe_prd/dblinks/DB0_PRD_CH5
c00000003c934508 12 8 3 1000000 127645 PO-- /exe_prd/dblinks/DW0_PRD
c00000003c9346a8 13 8 3 900000 899997 PO-- /exe_prd/dblinks/DW0_PRD_CH2
13 active, 2047 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: disabled
Onstat -d isn't showing any of the temp dbspaces offline and monitoring shows them being used. Here's the error from the message log: 09:20:28 Maximum server connections 155 09:34:35 WARNING: temporary space tempdb3 is full 09:35:37 Checkpoint Completed: duration was 0 seconds. 09:35:37 Checkpoint loguniq 40381, logpos 0x5246018, timestamp: 0xf1550631
Brian-
Your temp spaces are all marked for unlogged temp data. They
should fill by round robin unless something is preventing that. As Kern
said, look for down spaces (I don't see any here) or the DBSPACETEMP
environment variable overriding the default behavior.
--EEM
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> BRIAN MINNICK
> Sent: Wednesday, February 06, 2008 11:22 AM
> To: ids@iiug.org
> Subject: Re: RE: One temp space fills and 4gl fails [11205]
>
> IBM Informix Dynamic Server Version 10.00.FC3R1 -- On-Line (Prim) --Up 3
> days
> 10:40:10 -- 1403348 Kbytes
>
> Dbspaces
> address number flags fchunk nchunks pgsize flags owner name
> c00000003b2bee88 1 0x1 1 1 2048 N informix rootdbs
> c00000003c934848 2 0x1 2 1 2048 N informix phylog
> c00000003c9349e0 3 0x1 3 1 2048 N informix logfiles
> c00000003c934b78 4 0x1001 4 5 2048 N informix db0
> c00000003c934d10 5 0x2001 5 1 2048 N T informix tempdb1
> c00000003c937028 6 0x2001 6 1 2048 N T informix tempdb2
> c00000003c9371c0 7 0x2001 7 1 2048 N T informix tempdb3
> c00000003c937358 8 0x1 12 2 2048 N informix dw0
> 8 active, 2047 maximum
>
> Chunks
> address chunk/dbs offset size free bpages flags pathname
> c00000003b2bf028 1 1 4 64000 46215 PO-- /exe_prd/dblinks/ROOTDBS_PRD
> c00000003c9333d0 2 2 3 49149 10096 PO-- /exe_prd/dblinks/PHYLOG_PRD
> c00000003c933570 3 3 3 1048571 48518 PO--
/exe_prd/dblinks/LOGFILES_PRD
> c00000003c933710 4 4 3 1032189 64 PO-- /exe_prd/dblinks/DB0_PRD
> c00000003c9338b0 5 5 3 114685 114536 PO-- /exe_prd/dblinks/TEMPDB1_PRD
> c00000003c933a50 6 6 3 114685 113039 PO-- /exe_prd/dblinks/TEMPDB2_PRD
> c00000003c933bf0 7 7 3 114685 114536 PO-- /exe_prd/dblinks/TEMPDB3_PRD
> c00000003c933d90 8 4 3 1032189 29 PO-- /exe_prd/dblinks/DB0_PRD_CH2
> c00000003c934028 9 4 3 1032189 12 PO-- /exe_prd/dblinks/DB0_PRD_CH3
> c00000003c9341c8 10 4 3 1032189 992 PO-- /exe_prd/dblinks/DB0_PRD_CH4
> c00000003c934368 11 4 3 1032189 374031 PO--
/exe_prd/dblinks/DB0_PRD_CH5
> c00000003c934508 12 8 3 1000000 127645 PO-- /exe_prd/dblinks/DW0_PRD
> c00000003c9346a8 13 8 3 900000 899997 PO--
/exe_prd/dblinks/DW0_PRD_CH2
> 13 active, 2047 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: disabled
>
>
>
************************************************************************
**
> *****
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
> See you at the IIUG Informix 2008 Conference
> The Power Conference for Informix Professionals
> April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas
> http://www.iiug.org/conf
> Registration Now Open!!
Everett -- This is the primary server in an HDR pair, could that have something to do with it not filling the temp table round-robin?
The DBSPACETEMP environmnt varibale is not set. The tempspaces are defined in the onconfig file on this system. # DBSPACETEMP: # OnLine equivalent of DBTEMP for SE. This is the list of dbspaces # that the OnLine SQL Engine will use to create temp tables etc. # If specified it must be a colon separated list of dbspaces that exist # when the OnLine system is brought online. If not specified, or if # all dbspaces specified are invalid, various ad hoc queries will create # temporary files in /tmp instead. DBSPACETEMP tempdb1,tempdb2,tempdb3 # Default temp dbspaces
I don't think so, we run HDR here, too. If you have access to the source code, try building the temp table with an explicit fragment by round robin and see what happens. You may have to do it in a prepared statement or SQL block. --EEM > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > BRIAN MINNICK > Sent: Wednesday, February 06, 2008 11:46 AM > To: ids@iiug.org > Subject: Re: RE: RE: One temp space fills and 4gl fails [11208] > > Everett -- > > This is the primary server in an HDR pair, could that have something to do > with it not filling the temp table round-robin? > > > ************************************************************************ ** > ***** > Forum Note: Use "Reply" to post a response in the discussion forum. > > See you at the IIUG Informix 2008 Conference > The Power Conference for Informix Professionals > April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas > http://www.iiug.org/conf > Registration Now Open!!
Brian,
Informix does not create fragmented temp tables unless directed to do so
(explicit table create statement for temp tables). Temporary tables are
create in a single DBSpace (This has been true from version 7 through
version 10, I am uncertain about version 11 I have not attempted any
temp table operation in version 11 as yet).
When the manual speaks about creating temp tables in round robin fashion
it is refers to the fact that the first temp table goes into the first
dbspace then next goes in the next dbspace...
It is working the way I would expect it to work. Depending on how you
created the temp table, you could fragment the table via the SQL used to
create it. If you are not using the power of parallelization in your
application (setting PDQ, having fragmented tables ...) then having
multiple tempDBS may not be beneficial to you.
George
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Everett Mills
Sent: Wednesday, February 06, 2008 10:32 AM
To: ids@iiug.org
Subject: RE: RE: One temp space fills and 4gl fails [11207]
Brian-
Your temp spaces are all marked for unlogged temp data. They
should fill by round robin unless something is preventing that. As Kern
said, look for down spaces (I don't see any here) or the DBSPACETEMP
environment variable overriding the default behavior.
--EEM
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> BRIAN MINNICK
> Sent: Wednesday, February 06, 2008 11:22 AM
> To: ids@iiug.org
> Subject: Re: RE: One temp space fills and 4gl fails [11205]
>
> IBM Informix Dynamic Server Version 10.00.FC3R1 -- On-Line (Prim) --Up 3
> days
> 10:40:10 -- 1403348 Kbytes
>
> Dbspaces
> address number flags fchunk nchunks pgsize flags owner name
> c00000003b2bee88 1 0x1 1 1 2048 N informix rootdbs
> c00000003c934848 2 0x1 2 1 2048 N informix phylog
> c00000003c9349e0 3 0x1 3 1 2048 N informix logfiles
> c00000003c934b78 4 0x1001 4 5 2048 N informix db0
> c00000003c934d10 5 0x2001 5 1 2048 N T informix tempdb1
> c00000003c937028 6 0x2001 6 1 2048 N T informix tempdb2
> c00000003c9371c0 7 0x2001 7 1 2048 N T informix tempdb3
> c00000003c937358 8 0x1 12 2 2048 N informix dw0
> 8 active, 2047 maximum
>
> Chunks
> address chunk/dbs offset size free bpages flags pathname
> c00000003b2bf028 1 1 4 64000 46215 PO-- /exe_prd/dblinks/ROOTDBS_PRD
> c00000003c9333d0 2 2 3 49149 10096 PO-- /exe_prd/dblinks/PHYLOG_PRD
> c00000003c933570 3 3 3 1048571 48518 PO--
/exe_prd/dblinks/LOGFILES_PRD
> c00000003c933710 4 4 3 1032189 64 PO-- /exe_prd/dblinks/DB0_PRD
> c00000003c9338b0 5 5 3 114685 114536 PO-- /exe_prd/dblinks/TEMPDB1_PRD
> c00000003c933a50 6 6 3 114685 113039 PO-- /exe_prd/dblinks/TEMPDB2_PRD
> c00000003c933bf0 7 7 3 114685 114536 PO-- /exe_prd/dblinks/TEMPDB3_PRD
> c00000003c933d90 8 4 3 1032189 29 PO-- /exe_prd/dblinks/DB0_PRD_CH2
> c00000003c934028 9 4 3 1032189 12 PO-- /exe_prd/dblinks/DB0_PRD_CH3
> c00000003c9341c8 10 4 3 1032189 992 PO-- /exe_prd/dblinks/DB0_PRD_CH4
> c00000003c934368 11 4 3 1032189 374031 PO--
/exe_prd/dblinks/DB0_PRD_CH5
> c00000003c934508 12 8 3 1000000 127645 PO-- /exe_prd/dblinks/DW0_PRD
> c00000003c9346a8 13 8 3 900000 899997 PO--
/exe_prd/dblinks/DW0_PRD_CH2
> 13 active, 2047 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: disabled
>
>
>
************************************************************************
**
> *****
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
> See you at the IIUG Informix 2008 Conference
> The Power Conference for Informix Professionals
> April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas
> http://www.iiug.org/conf
> Registration Now Open!!
************************************************************************
*******
Forum Note: Use "Reply" to post a response in the discussion forum.
See you at the IIUG Informix 2008 Conference
The Power Conference for Informix Professionals
April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas
http://www.iiug.org/conf
Registration Now Open!!
The way I have always understood the temp dbspaces to work is that once a query is started in a temp dbspace, it has to stay within that dbspace for that query. What is happening is that your query is returning more results than can be stored in the dbspace in which the query found itself. To best understand this behavior, think of your results as going into a temporary table. This table cannot be assigned to more than one dbspace and cannot be fragmented. You will either need to modify your query to reduce the amount of data being returned or add additional chunk(s) to your temp dbspaces. Take care. Clifton M. Bean Informix DBA / AIX System Admin Currency Technics & Metrics Phone: (972) 812-1411 x244 -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of BRIAN MINNICK Sent: Wednesday, February 06, 2008 11:54 AM To: ids@iiug.org Subject: Re: RE: RE: One temp space fills and 4gl fails [11209] The DBSPACETEMP environmnt varibale is not set. The tempspaces are defined in the onconfig file on this system. # DBSPACETEMP: # OnLine equivalent of DBTEMP for SE. This is the list of dbspaces # that the OnLine SQL Engine will use to create temp tables etc. # If specified it must be a colon separated list of dbspaces that exist # when the OnLine system is brought online. If not specified, or if # all dbspaces specified are invalid, various ad hoc queries will create # temporary files in /tmp instead. DBSPACETEMP tempdb1,tempdb2,tempdb3 # Default temp dbspaces **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. See you at the IIUG Informix 2008 Conference The Power Conference for Informix Professionals April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas http://www.iiug.org/conf Registration Now Open!!
Brian I raised a query ("Multiple TEMP dbspace query (I should know this.......)") back on 17/1/08 and among the replies was on from Jacques Renaut as follows: " As Art mentioned if you have multiple temp spaces it should fragment the temp tables, however, to address your concern, the answer is yes, if for some reason 1 of your temp dbspaces gets full and an object in that temp dbspace needs to grow, it will still fail. It won't be able to just spill over into your other temp dbspaces. So while adding 1 or more temp dbspaces is probably a good idea, if any one of them fills to 100%, it could still cause operations that require temp space to fail." So it's expected behaviour, I think........ BRIAN MINNICK wrote: > Got a 4gl program that fills up one temp dbspace inserting into a temp table > then fails. I have 3 temp dbspaces, but it's not overflowing into the other > two, it just fills up the tempdb3 and fails. > > I thought there would be an automatic overflow into the other two temp spaces, > could this be a bug? > > Informix version 10.0.FC3R1 > HPUX B11.11 > # temporary files in /tmp instead. > > DBSPACETEMP tempdb1,tempdb2,tempdb3 # Default temp dbspaces > > Thanks, > > Brian Minnick > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > See you at the IIUG Informix 2008 Conference > The Power Conference for Informix Professionals > April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas > http://www.iiug.org/conf > Registration Now Open!! > >
Hmmmmm, according to the IBM Informix Dynamic Server Administrator's Guide, "If you have more than one temporary dbspace and execute a SELECT statement into a temporary table, the results of the query are inserted in round robin order." It doesn't mention explicitly fragementing the temp table. Could this be happenning, due to the developer has created the temporary table first and is performing singleton inserts within a foreach loop causing it to stay in only one dbspace? This 4gl code is pretty old, it's EXE's warehouse management system. Thanks, Brian
George Palmer wrote: > Brian, > George, Brian: Implicit temp tables are always fragmented round robin across all of the appropriate temp dbspaces. NOTE the word 'appropriate' here. By default all temp tables are LOGGED which means that they cannot be created in temp dbspaces but only in logged dbspaces. If there are ONLY temp dbspaces in DBSPACETEMP then they will be created in the ROOTDB space which is normally small and easily and quickly filled. In 11.10 and later (soon enough) you can change the default behavior to specify that temp tables default to WITHOUT LOG behavior with an environment variable. Explicit temp tables are created either where you tell them to be created. However, if no IN clause is specified, and they are LOGGED then like any other CREATE TABLE they'll default to the database's home dbspace or ROOTDB (I forget and don't have time to look it up right now) but are NOT fragmented at all. Art S. Kagel > Informix does not create fragmented temp tables unless directed to do so > (explicit table create statement for temp tables). Temporary tables are > create in a single DBSpace (This has been true from version 7 through > version 10, I am uncertain about version 11 I have not attempted any > temp table operation in version 11 as yet). > > When the manual speaks about creating temp tables in round robin fashion > it is refers to the fact that the first temp table goes into the first > dbspace then next goes in the next dbspace... > > It is working the way I would expect it to work. Depending on how you > created the temp table, you could fragment the table via the SQL used to > create it. If you are not using the power of parallelization in your > application (setting PDQ, having fragmented tables ...) then having > multiple tempDBS may not be beneficial to you. > > George > > ================================================================================ =========== Please access the attached hyperlink for an important electronic communications disclaimer: http://www.oninit.com/home/disclaimer.php ================================================================================ ===========
Art-- They key word is "implicit" temp tables get automatically fragmented over the temp dbspaces. In this case the temp table is explicitly created and with "no log". To get the critical report out, I temporarily added a large chuck to each of the temp spaces. For subsequent runs I modified the 4gl code to fragement the table round robin over all the tempspaces. Thanks everybody for your help (especially, Art, Clifton, George, Everett, and Kern) Brian Minnick
Related threads
- IDS 10 table-level restore
- Informix Development Webinar December 11, 2007
- ontape -p/r with changed ROOTPATH
- Migrate from HP PA-RISC to HP ITANIUM by ontape