Re: _temptable filling up rootdbs

Hi, It seems no one has replied to your question yet .... The _temptable is an Internal temp table which Informix creates if AUTOINDEX path is chosen by the index. I had encountered a similar situation at a customer site where we found that somehow the engine wasn't using the index properly (even though it was there) but couldn't find the reason why _temptable was being created in the rootdbs since in the function where it chooses which dbspace it should create the temp table, it looks for the DBSPACETEMP onconfig parameter and if that's empty, looks for the env. variable with the same name ... if your problem is reproducible, I would suggest opening up a case with Tech Support and supplying the test case to them and I'm sure the problem would be identified and resolved. Meanwhile, if you can find out the table where this query is creating the implicit temp table, I would suggest dropping and re-creating the index or creating the index if its not there. HTH Thanx much, Rajib Sarkar Advisory Software Engineer (RAS) IBM Data Management Group Ph : (602)-217-2100 Fax: (602)-217-2100 T/L : 667-2100 As long as you derive inner help and comfort from anything, keep it -- Mahatma Gandhi "\\"Jordan\\" <jordan.bruce" To: informix-list@iiug.org Sent by: cc: owner-informix-list@iiug Subject: Re: _temptable filling up rootdbs .org 11/07/2003 04:01 PM Please respond to "\\"Jordan\\" <jordan.bruce"<remove@ac e.iiug.org> "Jordan @aongsi.ca>" <jordan.bruce<remove> wrote in message news:uzUqb.10425$Pg1.498809@news20.bellglobal.com... > Hello, > > I'm running AIX5L, IDS 9.3 UC2 > > The following 4gl program creates a _temptable in rootdbs which takes up all > of our rootdbs space and an eroor is returned > > > pengo test2 > select > Program stopped at "test2.4gl", line number 59. > SQL statement error number -264. > Could not write to a temporary file. > SYSTEM error number -131. > ISAM error: no free disk space> > > The database is nonlogging and the DBSPACETEMP = tmpdbs which is a temporary > dbspace (onstat -d shows "T" beside it). Why is _temptable being created > int rootdbs? How can I make informix sort in tmpdbs? > > Thanks > > Jordan > > > DATABASE pen1 > > GLOBALS > DEFINE args RECORD { for processing command line > arguments } > corpfile CHAR(80), > corp LIKE e01_mstr.corp, > divn LIKE e01_mstr.divn, > sdiv LIKE e01_mstr.sdiv, > plan LIKE e03_plan.plan, > creator CHAR(8), > class_break CHAR(14), > edate1 LIKE e01_mstr.effect_dt, > edate2 LIKE e01_mstr.effect_dt, > fund LIKE e06_extr.to_acct, > lang CHAR(1), > accessing CHAR(1), > period CHAR(1), > prt_time CHAR(1), > run_type CHAR(1), > out_file CHAR(14), > out_file2 CHAR(14), > err_file CHAR(14), > tst_file CHAR(14), > rpt_file CHAR(14), > exp_file1 CHAR(14), > exp_file2 CHAR(14), > app_wri CHAR(1), > online_batch CHAR(1) > END RECORD > # DEFINE ytd_start CHAR(10) > DEFINE ytd_start date > DEFINE counter INTEGER > END GLOBALS > > MAIN > > DATABASE mar1 > > CREATE TEMP TABLE corp_plan > ( corp char(8) not null, > plan char(8) not null);> > LOAD FROM "finme.txt" INSERT INTO corp_plan> > LET args.creator = "system" > LET args.divn = "*" > LET args.sdiv = "*" > LET args.edate1 = "01/10/2003" > LET args.edate2 = "31/10/2003" > LET ytd_start = "01/08/2003" > # LET args.edate1 = "01/03/2003" > # LET args.edate2 = "31/03/2003" > # LET ytd_start = "01/01/2003" > > > DISPLAY "select" > SELECT COUNT(*) INTO counter > FROM e06_extr x, corp_plan y > WHERE x.creator = args.creator > AND (x.corp = y.corp ) > AND (x.plan = y.plan ) > AND (x.divn MATCHES args.divn OR x.divn = args.divn ) > AND (x.sdiv MATCHES args.sdiv OR x.sdiv = args.sdiv ) > AND (x.extr_type != "1N") ## 51093 -- exclude "1N" extract types > AND (x.extr_type != "6B") ## 51332 -- exclude "6B" extract types > AND ( ((x.extr_type = "00" AND x.start_dt = ytd_start ) OR > (x.extr_type = "00" AND x.start_dt = args.edate1 ) OR > (x.extr_type = "99" AND x.end_dt = args.edate2 )) OR > ((x.extr_type != "00" AND x.extr_type != "99") AND > (x.start_dt >= ytd_start AND x.start_dt <= args.edate2) AND > (x.end_dt >= ytd_start AND x.end_dt <= args.edate2 )) )> > DISPLAY counter > SLEEP 30 > > END MAIN > I forgot to mention, when I run the same querirs from the 4gl program in dbaccess the _temptable is in the tmpdbspace and not as large Chunk Pathname Size Used Free 4 /dev/tmpdbs 496000 3939 492061 Description Offset Size ------------------------------------------------------------- -------- ---- ---- RESERVED PAGES 0 2 CHUNK FREELIST PAGE 2 1 (0x400001) tmpdbs:'informix'.TBLSpace 3 50 (0x400c80) marilive:'informix'.corp_plan 53 8 (0x400c81) marilive:'informix'._temptable 69 8 while _temptable was under rootdbs when running 4gl, as well as the size comparison Chunk Pathname Size Used Free 1 /dev/rootdbs 64000 61194 2806 Description Offset Size ------------------------------------------------------------- -------- ---- ---- RESERVED PAGES 0 12 CHUNK FREELIST PAGE 12 1 rootdbs:'informix'.TBLSpace 13 250 FREE 263 2750 marilive:'informix'._temptable 4294 59706 Can anyone shel some light? Jordan sending to informix-list

Since my last posting I found a way to stop this _temptable from being created. Originally we were using a prepared statement with the SCROLL_INSENSITIVE and READ_ONLY options. When executing a query with a large result set (and only sometimes Informix realises that this is a large result set), a temporay table is created in rootdbs. If I use the option FORWARD_ONLY instead it creates a result set in the temp space, which does not grow like the temptable in the rootdbs. I hope this helps others with a similar problem. Steve. Rajib Sarkar <rsarkar@us.ibm.com> wrote in message news:<bqepjm$kcu$1@terabinaries.xmission.com>... > Hi, > It seems no one has replied to your question yet .... > > The _temptable is an Internal temp table which Informix creates if > AUTOINDEX path is chosen by the index. I had encountered a similar > situation at a customer site where we found that somehow the engine wasn't > using the index properly (even though it was there) but couldn't find the > reason why _temptable was being created in the rootdbs since in the > function where it chooses which dbspace it should create the temp table, it > looks for the DBSPACETEMP onconfig parameter and if that's empty, looks for > the env. variable with the same name ... if your problem is reproducible, I > would suggest opening up a case with Tech Support and supplying the test > case to them and I'm sure the problem would be identified and resolved. > > Meanwhile, if you can find out the table where this query is creating the > implicit temp table, I would suggest dropping and re-creating the index or > creating the index if its not there. > > HTH > > Thanx much, > > Rajib Sarkar > Advisory Software Engineer (RAS) > IBM Data Management Group > Ph : (602)-217-2100 > Fax: (602)-217-2100 > T/L : 667-2100 > > As long as you derive inner help and comfort from anything, keep it -- > Mahatma Gandhi > > > > "\\"Jordan\\" > <jordan.bruce" To: informix-list@iiug.org > Sent by: cc: > owner-informix-list@iiug Subject: Re: _temptable filling up rootdbs > .org > > > 11/07/2003 04:01 PM > Please respond to > "\\"Jordan\\" > <jordan.bruce"<remove@ac > e.iiug.org> > > > > > > > "Jordan @aongsi.ca>" <jordan.bruce<remove> wrote in message > news:uzUqb.10425$Pg1.498809@news20.bellglobal.com... > > Hello, > > > > I'm running AIX5L, IDS 9.3 UC2 > > > > The following 4gl program creates a _temptable in rootdbs which takes up > all > > of our rootdbs space and an eroor is returned > > > > > pengo test2 > > select > > Program stopped at "test2.4gl", line number 59. > > SQL statement error number -264. > > Could not write to a temporary file. > > SYSTEM error number -131. > > ISAM error: no free disk space> > > > > > The database is nonlogging and the DBSPACETEMP = tmpdbs which is a > temporary > > dbspace (onstat -d shows "T" beside it). Why is _temptable being created > > int rootdbs? How can I make informix sort in tmpdbs? > > > > Thanks > > > > Jordan > > > > > > DATABASE pen1 > > > > GLOBALS > > DEFINE args RECORD { for processing command line > > arguments } > > corpfile CHAR(80), > > corp LIKE e01_mstr.corp, > > divn LIKE e01_mstr.divn, > > sdiv LIKE e01_mstr.sdiv, > > plan LIKE e03_plan.plan, > > creator CHAR(8), > > class_break CHAR(14), > > edate1 LIKE e01_mstr.effect_dt, > > edate2 LIKE e01_mstr.effect_dt, > > fund LIKE e06_extr.to_acct, > > lang CHAR(1), > > accessing CHAR(1), > > period CHAR(1), > > prt_time CHAR(1), > > run_type CHAR(1), > > out_file CHAR(14), > > out_file2 CHAR(14), > > err_file CHAR(14), > > tst_file CHAR(14), > > rpt_file CHAR(14), > > exp_file1 CHAR(14), > > exp_file2 CHAR(14), > > app_wri CHAR(1), > > online_batch CHAR(1) > > END RECORD > > # DEFINE ytd_start CHAR(10) > > DEFINE ytd_start date > > DEFINE counter INTEGER > > END GLOBALS > > > > MAIN > > > > DATABASE mar1 > > > > CREATE TEMP TABLE corp_plan > > ( corp char(8) not null, > > plan char(8) not null);> > > > LOAD FROM "finme.txt" INSERT INTO corp_plan> > > > LET args.creator = "system" > > LET args.divn = "*" > > LET args.sdiv = "*" > > LET args.edate1 = "01/10/2003" > > LET args.edate2 = "31/10/2003" > > LET ytd_start = "01/08/2003" > > # LET args.edate1 = "01/03/2003" > > # LET args.edate2 = "31/03/2003" > > # LET ytd_start = "01/01/2003" > > > > > > DISPLAY "select" > > SELECT COUNT(*) INTO counter > > FROM e06_extr x, corp_plan y > > WHERE x.creator = args.creator > > AND (x.corp = y.corp ) > > AND (x.plan = y.plan ) > > AND (x.divn MATCHES args.divn OR x.divn = args.divn ) > > AND (x.sdiv MATCHES args.sdiv OR x.sdiv = args.sdiv ) > > AND (x.extr_type != "1N") ## 51093 -- exclude "1N" extract types > > AND (x.extr_type != "6B") ## 51332 -- exclude "6B" extract types > > AND ( ((x.extr_type = "00" AND x.start_dt = ytd_start ) OR > > (x.extr_type = "00" AND x.start_dt = args.edate1 ) OR > > (x.extr_type = "99" AND x.end_dt = args.edate2 )) OR > > ((x.extr_type != "00" AND x.extr_type != "99") AND > > (x.start_dt >= ytd_start AND x.start_dt <= args.edate2) AND > > (x.end_dt >= ytd_start AND x.end_dt <= args.edate2 )) )> > > > DISPLAY counter > > SLEEP 30 > > > > END MAIN > > > > > I forgot to mention, when I run the same querirs from the 4gl program in > dbaccess the _temptable is in the tmpdbspace and not as large > > Chunk Pathname Size Used > Free > 4 /dev/tmpdbs 496000 3939 > 492061 > Description Offset > Size > ------------------------------------------------------------- -------- > ---- > ---- > RESERVED PAGES 0 > 2 > CHUNK FREELIST PAGE 2 1 > (0x400001) tmpdbs:'informix'.TBLSpace 3 50 >