I using db2 v8 fixpack 7 aix 5.2.

Recently, when I run application after feaw hours, application failed because of TEMP tablespace become full (.TDA growing so fast) and consume more than 20 gig disk spaces, please help and let me know how can prevent .TDA growing.

can you paste the error message in db2diag.log here?
and what operation did you do during that time?
is there a big transaction running requires large temp space?

UNIX and yes you are right.


2005-11-19-21.37.33.357046-300 E37208C755 LEVEL: Error

PID : 46100 TID : 1 PROC : db2agntp (XXXXXXX) 0

INSTANCE: db2 NODE : 000 DB : XXXXXXX

APPHDL : 0-231 APPID: NF000001.C116.0EB1B9233341

FUNCTION: DB2 UDB, oper system services, sqloseekwrite64, probe:100

MESSAGE : ZRC=0x850F000C=-2062614516=SQLO_DISK "Disk full."

DIA8312C Disk was full.



2005-11-19-21.37.36.795825-300 E42402C687 LEVEL: Error

PID : 49464 TID : 1 PROC : db2pfchr 0

INSTANCE: db2 NODE : 000

FUNCTION: DB2 UDB, buffer pool services, sqlbWritePageToDisk, probe:20

MESSAGE : ADM6017E The table space "TEMPSPACE1" (ID "1") is full. Detected on

container "/zzzzzz/db2/NODE0000/SQL00001/SQLT0001.0" (ID

"0"). The underlying file system is full or the maximum allowed

space usage for the file system has been reached. It is also possible

that there are user limits in place with respect to maximum file size

and these limits have been reached.

I should say I checked and we do not have any user limit and file system support larg file and file system has 22 gig free spaces, when I start the job after few hours consume all free spaces.

Here are some choices; what will work depends on what is actually consuming temporary space:
- review your SQL code and the execution plan to minimize use of temporary space;
- increase temporary space allocation;
- reduce the volume of your transaction by limiting the number of records it processes in a single run.

DB2 uses the tempspace during a job for a number of reasons, including system tempory tables that it builds to process a query, sort work area, etc.

In any case, you should upgrade to FP 10 just in case there is problem with DB2 that has fixed.

AIX APAR:
IY67743
RM ON J2 TAKES A LONG TIME
6
On AIX 5.3 GA/ML01 and 5.2 ML04 / ML05 (bos.up/mp/mp64 5.2.0.40 or greater) , DB2 may suffer performance degradation and/or intermittent hangs . This is caused by an excessively long time spent truncating large JFS2 files used for temporary DB2 data . This is a recommended APAR .

http://www-1.ibm.com/support/docview...=utf-8&lang=en

Thank you n_i and Marcus,
n_i please

1- how can I use SQL to use less temporary spabes?
2- yes I can create more containers but DISK becomes full

Marcus, you are right the application is using a lot of sort so needs for temporary spaces, how can I reduce this sorting ( I know, one way is to create proper index , what else?

Thank you wangzhonnew, the link was very usefull site.

Well, you seem to have answered your own questions. If the data need to be sorted there are only two ways of doing that: in advance (by creating an index) or during the query execution time.

Look closely at your query text; may be it doesn't need the sort? May be the sort is applied in a subquery, where it doesn't make sense? May be you can convince the optimizer that the sort should be applied at a later stage?

There are other things that can potentially spill to disk and use up the temp space, e.g. GROUP BY, temporary tables, or hash joins. I don't believe that hash join can spill 20 GB of data though..

These are just wild guesses; it's tough to troubleshoot something you've never seen. If you could post the query plan may be somebody would be able to help

I know that HWM can influence db backup/restore, I don't know where and when in db HWM can possibly act on?

it seems there are latches in oracle buffer,how about db2?

Hi,
I'm in DB2 v8.1.1.96 FixPak 10, AIX 5.3
I see the similar type of error in db2diag.log..

2010-06-10-12.19.08.740810+000 I110045556A610 LEVEL: Error
PID : 512158 TID : 1 PROC : db2loggr (ETLPDI21) 0
INSTANCE: dbinspd5 NODE : 000 DB : ETLPDI21
FUNCTION: DB2 UDB, oper system services, sqlowrite, probe:200
MESSAGE : ZRC=0x850F000C=-2062614516=SQLO_DISK "Disk full."
DIA8312C Disk was full.
DATA #1 : File handle, PD_TYPE_SQO_FILE_HDL, 8 bytes
0x0FFFFFFFFFFFCDB0 : 0000 00F0 0000 0000 ........
DATA #2 : unsigned integer, 8 bytes
262144
DATA #3 : signed integer, 8 bytes
192512
DATA #4 : signed integer, 4 bytes
2

2010-06-10-12.19.08.741367+000 I110046167A357 LEVEL: Error
PID : 512158 TID : 1 PROC : db2loggr (ETLPDI21) 0
INSTANCE: dbinspd5 NODE : 000 DB : ETLPDI21
FUNCTION: DB2 UDB, data protection, sqlpgifl, probe:50
RETCODE : ZRC=0x850F000C=-2062614516=SQLO_DISK "Disk full."
DIA8312C Disk was full.

2010-06-10-12.19.08.786670+000 I110046525A340 LEVEL: Warning
PID : 512158 TID : 1 PROC : db2loggr (ETLPDI21) 0
INSTANCE: dbinspd5 NODE : 000 DB : ETLPDI21
FUNCTION: DB2 UDB, data protection, sqlpgCallGIFL, probe:2750
MESSAGE : Error rc: -2062614516, when initializing log file: 121868

2010-06-10-12.19.08.786930+000 I110046866A315 LEVEL: Error
PID : 512158 TID : 1 PROC : db2loggr (ETLPDI21) 0
INSTANCE: dbinspd5 NODE : 000 DB : ETLPDI21
FUNCTION: DB2 UDB, data protection, sqlpgadf, probe:1970
MESSAGE : rc 0 at probe 0, callgiflRC -2062614516

2010-06-10-12.19.08.787084+000 I110047182A356 LEVEL: Error
PID : 512158 TID : 1 PROC : db2loggr (ETLPDI21) 0
INSTANCE: dbinspd5 NODE : 000 DB : ETLPDI21
FUNCTION: DB2 UDB, data protection, sqlpgadf, probe:0
RETCODE : ZRC=0x850F000C=-2062614516=SQLO_DISK "Disk full."
DIA8312C Disk was full.

2010-06-10-12.19.08.787303+000 I110047539A314 LEVEL: Error
PID : 512158 TID : 1 PROC : db2loggr (ETLPDI21) 0
INSTANCE: dbinspd5 NODE : 000 DB : ETLPDI21
FUNCTION: DB2 UDB, data protection, sqlpgadf, probe:0
MESSAGE : Last log file not required: S0121632.LOG.

2010-06-10-12.19.08.787483+000 I110047854A403 LEVEL: Error
PID : 1069284 TID : 1 PROC : db2agent (ETLPDI21) 0
INSTANCE: dbinspd5 NODE : 000 DB : ETLPDI21
APPHDL : 0-545 APPID: A5838B5B.E695.137690120016
FUNCTION: DB2 UDB, data protection, sqlpgPostLoggrWithoutLatching, probe:930
MESSAGE : db2logger: rc=-2062614516 sem rc=0 type=10

2010-06-10-12.19.08.787846+000 I110048258A149 LEVEL: Error
PID:1069284 TID:1 NODE:000 Title: SQLP_DBCB
Dump File:/db2/diag/dbinspd5/10692841.000

2010-06-10-12.19.08.789138+000 I110048408A428 LEVEL: Error
PID : 1069284 TID : 1 PROC : db2agent (ETLPDI21) 0
INSTANCE: dbinspd5 NODE : 000 DB : ETLPDI21
APPHDL : 0-545 APPID: A5838B5B.E695.137690120016
FUNCTION: DB2 UDB, data protection, sqlpgResSpace, probe:570
RETCODE : ZRC=0x850F000C=-2062614516=SQLO_DISK "Disk full."
DIA8312C Disk was full.

Please help me to find the cause of this error and how can i resolve it.
I'm seeing this error daily in db2diag.log from last 4 days at the same time...

Thanks in advance

Your disk is obviously full. Next time open a new thread instead of using one that is 5 years old.

Andy