generation of BIG sql00002.tda file

[Jens]

Hello,

we are currently experiencing a problem with our db2 v.7.1. A
developper is starting a ddl-script and as a consequence a
sql00002.tda file is generated (located in
d:\db2\Node0000\sql00004\sqlt0001.0) that keeps inflating until there
is no more space on the hard disk. As a consequence, the script
execution stops and an sql-error shown saying there was no more space
on the file system. The developper assured me that he did not change
the script in any way and that it used to run without problems. Could
it have to do with the configuration of the instance?

Any help is greatly appreciated,

Jens

[Lee Dilworth]

sounds a bit suspect ...check the script again....large workfiles typically
come from sql that requires a large sort for example, which cannot be done
in memory. you could check if anyone has altered sortheap/sheapthres but I
would start by monitoring what the script is doing.

[Pascal-Marc Gelinas]

That would be DML, not DDL.
From what i understand, the script contains create table, ... or similar
stuff.

PM

"Lee Dilworth" <lee_di...@hotmail.com> a écrit dans le message de news:
a4aq5l$1cbfrn$1...@ID-118488.news.dfncis.de...

[Gert van der Kooij]

In article <a4c32f$7in$1...@wanadoo.fr>, Pascal-Ma...@wanadoo.fr
says...

>
> "Lee Dilworth" <lee_di...@hotmail.com> a écrit dans le message de news:
> a4aq5l$1cbfrn$1...@ID-118488.news.dfncis.de...
> > sounds a bit suspect ...check the script again....large workfiles
> typically
> > come from sql that requires a large sort for example, which cannot be done
> > in memory. you could check if anyone has altered sortheap/sheapthres but I
> > would start by monitoring what the script is doing.
> >
> >
>
> That would be DML, not DDL.
> From what i understand, the script contains create table, ... or similar
> stuff.
>
> PM

That's right, but the sqlt00001.0 directoly almost always contains the
temp tablespace. I think the OP needs to post the script which
contains the SQL causing the problem.
If it's DML instead of DDL it could be a recursive loop caused by
unexpected data, but that's just a guess.

[Dwaine R Snow]

Hi,

The .TDA file is a Temporary DAta file and will only exist if you are doing
an overflowed sort.

The script you are running must be doing some DML as DDL will not cause any
sorting to occur...

..Dwaine

"Gert van der Kooij" <gko...@xs4all.nl> wrote in message
news:MPG.16d3cf869...@news.wanadoo.nl...

[Pierre Saint-Jacques]

Excuse me for jumping in but isn't a *.TDA file type created when one
REORGs a table?? Does this script do this to cause the TDA to appear??
Regards, Pierre.

[Jens]

Thanks very much for all your support,

however none of the above seems to be the case. I will hereby attach
the ddl-code which I stripped down a bit but which still fills up the
whole file system (this morning it used up 4.5 GB before the
interruption:

export to d:/ftp/pltf/tmp/mfg_08.csv OF DEL MODIFIED BY coldel;
chardel"" decpt. messages d:/ftp/pltf/tmp/mfg_08.txt
select distinct db2admin.eingabe.plvers,
(stamm2.land),
(stamm2.region),
stamm2.kunde,
(stamm2.customer),
stamm2.company,
stamm2.salesorg,
stamm2.distr_chan,
integer(stamm2.artikel),
(stamm2.hier),
(stamm2.gb),
(stamm2.hier2),
(stamm2.hier3),
(stamm2.hier4),
(stamm2.hier5),
(stamm2.hier6),
stamm2.intplan,
concat(substr(prog.monat,5,2),substr(prog.monat,1,4)),
prog.absatz,
prog.gratis,
prog.muster,
prog.prom,
prog.absatz+prog.gratis+prog.muster+prog.prom,
'PME', 0
from prog, stamm2, eingabe
where stamm2.land=prog.land and
stamm2.gb=prog.gb and
stamm2.kunde=prog.kunde and
stamm2.artikel=prog.artikel and
prog.gb='08' and
(prog.land,prog.gb,prog.kunde,prog.artikel) not in
( select eingabe.land,eingabe.gb,eingabe.kunde,eingabe.artikel
from eingabe);

Thank you very much,

Jens

[Dwaine R Snow]

Pierre,

Yes, a reorg can also cause a .TDA to be created.

When reorging in an SMS tablespace we create a .DTR file in the
tablespace/directory the table is in and also create a .TDA in the temp
tablespace where we do the actual "work" on the data...

But the .TDA could also be just a simple sort file due to an overflowed
sort. Without knowing what is in the script we can not know for suire which
of the operations (reorg or sort) caused the error to occur.

..Dwaine

"Pierre Saint-Jacques" <ses...@attglobal.net> wrote in message
news:3C6A1CAC...@attglobal.net...

[Dwaine R Snow]

Jens,

Have you looked at the explain for the stmt you are using to do the
export. I believe the optimizer is very likely choosing to create a temp
table during the execution of the sql stmt.

Thanks,
Dwaine

"Jens" <jensl...@yahoo.de> wrote in message
news:78e99a24.02021...@posting.google.com...

[Pascal-Marc Gelinas]

I see the unique keyword there... (the suspect in combination with concat)
I guess without the concat it goes better.
Seems to me that it's a query rewrite candidate.

I just wonder how may rows qualify for this...
Probably alot.

select count(*) from ...
...
where stamm2.land=prog.land and
...

i also smell
explain plan
reorg(s)?
runstats

explain plan

PM

[Jens]

Merci beaucoup,

finally the developper has agreed to withdraw the script and revise
it. If you are interested, I will verify what the actual reason for
the overflow was and publish it in this threat.. thanks again for your
help,

Jens

"Pascal-Marc Gelinas" <Pascal-Ma...@wanadoo.fr> wrote in message news:<a4ef86$5dh$1...@wanadoo.fr>...