Re: _temptable filling up rootdbs
Jordan, you should create the temporary table "corp_plan" with the
"WITH NO LOG" syntax:
CREATE TEMP TABLE corp_plan
(
corp CHAR(8) NOT NULL,
plan CHAR(8) NOT NULL
) WITH NO LOG;
With the "WITH NO LOG" syntax the actions made on the temporary table
(INSERTS's/UPDATE's/DELETE's, etc ...) are __NOT__ logged.
Vitorino Ribeiro
"Jordan" <jordan.bruce<remove>@aongsi.ca> wrote in message news:<cuVqb.11125$fg4.427280@news20.bellglobal.com>...
> "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