Fw: _temptable filling up rootdbs

This is a multi-part message in MIME format. ------=_NextPart_000_001C_01C3A842.07642F60 Content-Type: text/plain; charset="big5" Content-Transfer-Encoding: quoted-printable maybe, you can try DBTEMP=3D/tmp, or a bigger filesystem Gary ----- Original Message -----=20 From: "Jordan" <jordan.bruce @aongsi.ca=20 To: informix-list@iiug.org=20 Sent: Saturday, November 08, 2003 7:01 AM Subject: Re: _temptable filling up rootdbs "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 =3D 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 =3D "system" > LET args.divn =3D "*" > LET args.sdiv =3D "*" > LET args.edate1 =3D "01/10/2003" > LET args.edate2 =3D "31/10/2003" > LET ytd_start =3D "01/08/2003" > # LET args.edate1 =3D "01/03/2003" > # LET args.edate2 =3D "31/03/2003" > # LET ytd_start =3D "01/01/2003" > > > DISPLAY "select" > SELECT COUNT(*) INTO counter > FROM e06_extr x, corp_plan y > WHERE x.creator =3D args.creator > AND (x.corp =3D y.corp ) > AND (x.plan =3D y.plan ) > AND (x.divn MATCHES args.divn OR x.divn =3D args.divn ) > AND (x.sdiv MATCHES args.sdiv OR x.sdiv =3D args.sdiv ) > AND (x.extr_type !=3D "1N") ## 51093 -- exclude "1N" extract =types > AND (x.extr_type !=3D "6B") ## 51332 -- exclude "6B" extract = types > AND ( ((x.extr_type =3D "00" AND x.start_dt =3D ytd_start ) = OR > (x.extr_type =3D "00" AND x.start_dt =3D args.edate1 ) OR > (x.extr_type =3D "99" AND x.end_dt =3D args.edate2 )) OR > ((x.extr_type !=3D "00" AND x.extr_type !=3D "99") AND > (x.start_dt >=3D ytd_start AND x.start_dt <=3D args.edate2) = AND > (x.end_dt >=3D ytd_start AND x.end_dt <=3D 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 . ------=_NextPart_000_001C_01C3A842.07642F60 Content-Type: text/html; charset="big5" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META http-equiv=3DContent-Type content=3D"text/html; charset=3Dbig5"> <META content=3D"MSHTML 6.00.2800.1264" name=3DGENERATOR> <STYLE></STYLE> </HEAD> <BODY bgColor=3D#ffffff> <DIV><FONT size=3D2></FONT>&nbsp;</DIV> <DIV>maybe, you can try DBTEMP=3D/tmp, or a bigger = filesystem<BR><BR>Gary</DIV> <DIV><FONT size=3D2></FONT>&nbsp;</DIV> <DIV>&nbsp;</DIV> <DIV><FONT size=3D2><BR></FONT>&nbsp;</DIV> <DIV style=3D"FONT: 10pt =B7s=B2=D3=A9=FA=C5=E9">----- Original Message = -----=20 <DIV style=3D"BACKGROUND: #e4e4e4; font-color: black"><B>From:</B> <A=20 title=3Dremove@ace.iiug.org href=3D"mailto:remove@ace.iiug.org">"Jordan" = &lt;jordan.bruce @aongsi.ca</A> </DIV> <DIV><B>To:</B> <A title=3Dinformix-list@iiug.org=20 href=3D"mailto:informix-list@iiug.org">informix-list@iiug.org</A> </DIV> <DIV><B>Sent:</B> Saturday, November 08, 2003 7:01 AM</DIV> <DIV><B>Subject:</B> Re: _temptable filling up root