Results 1 to 13 of 13
  1. #1
    Join Date
    Sep 2003
    Location
    canada
    Posts
    230

    Unanswered: Tablespace full because of .TDA files growing so fast.

    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.

  2. #2
    Join Date
    Nov 2005
    Location
    Toronto
    Posts
    65
    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?
    IBM Certified DBA for DB2 UDB
    IBM Certified Database Developer for DB2 UDB
    DB2 Tech Support, IBM Toronto Software Lab

  3. #3
    Join Date
    Sep 2003
    Location
    canada
    Posts
    230
    Quote Originally Posted by wangzhonnew
    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.

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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.

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    Last edited by Marcus_A; 11-21-05 at 20:46.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Nov 2005
    Location
    Toronto
    Posts
    65
    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
    IBM Certified DBA for DB2 UDB
    IBM Certified Database Developer for DB2 UDB
    DB2 Tech Support, IBM Toronto Software Lab

  7. #7
    Join Date
    Sep 2003
    Location
    canada
    Posts
    230
    Quote Originally Posted by Marcus_A
    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.
    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?

  8. #8
    Join Date
    Sep 2003
    Location
    canada
    Posts
    230
    Quote Originally Posted by wangzhonnew
    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 wangzhonnew, the link was very usefull site.

  9. #9
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by M_RAS
    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?
    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

  10. #10
    Join Date
    Dec 2005
    Posts
    8

    what's the HWM(high water mark) for

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

  11. #11
    Join Date
    Dec 2005
    Posts
    8

    is there a latch in bufferpool

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

  12. #12
    Join Date
    Jun 2010
    Posts
    1

    DIA8312C Disk was full

    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

  13. #13
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Your disk is obviously full. Next time open a new thread instead of using one that is 5 years old.

    Andy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •