Results 1 to 6 of 6
  1. #1
    Join Date
    May 2012
    Posts
    3

    Unanswered: Issue with MQT Refresh

    I am trying to refresh the MQT and facing the issue like

    The table space "TEMPSPACE1" (ID "1") is full. Detected on
    container
    "/db2/db2inst1/sandip/db2inst1/NODE0000/SSC/T0000001/C0000000.TMP" (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.

    or

    DB2 Database Error: ERROR [57011] [IBM][DB2/LINUXX8664] SQL0968C The file system is full. SQLSTATE=57011

    To optimize it I have created 4 indexes on the base tables used in the MQT. The sql performance enhanced and sql involved in mqt is fetching data. the table is containing .374 million data. I have run the explain query command and same has been attached for ready reference. Would somebody help me to what needs to be done as the suggested query is not running.
    Attached Files Attached Files

  2. #2
    Join Date
    Nov 2011
    Posts
    334
    I think the problem is clear :"The table space "TEMPSPACE1" (ID "1") is full"。
    Why do you think creating indexes will solve it?

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I assume that the temporary tablespace is needed to spool an intermediate result set (due to sorting or other SQL processing) in order to populate the MQT. In that case it might have helped to add some indexes on the base tables if DB2 can use the indexes to reduce or eliminate need for temporary tables being created in system temporary tablespace.

    If the problem is that "there are user limits in place with respect to maximum file size and these limits have been reached" be aware that some Linux/UNIX systems have a limit of 2GB for file size, but this can usually be relaxed by changing the OS config. Contact your OS systems administrator for details.

    If the problem is not max file size for your OS, usually system temporary tablespaces are set up with SMS (or automatic storage is using SMS for them) so check the amount of space available on the mount point.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    May 2012
    Posts
    3

    Question

    Quote Originally Posted by fengsun2 View Post
    I think the problem is clear :"The table space "TEMPSPACE1" (ID "1") is full"。
    Why do you think creating indexes will solve it?
    no I didn't say that creating indexes will solve the issue..rather I said after creating the indexes the query used in mqt performing better although the mqt refresh is not happening. I have tried to use

    I have altered the table space to set auto extent yes and submiited the mqt
    refresh as :

    nohup db2 "refresh table MQT_SSC_Invoice_Detail" > SSMQTRefresh.log &

    and its running for a long time. Now can u please tell me is there any way to see whats going on inside as the in the file itself nothing is written after 2 hrs.

  5. #5
    Join Date
    May 2012
    Posts
    3

    Issue with MQT

    I have altered the table space to set auto extent yes and submiited the mqt
    refresh as :

    nohup db2 "refresh table MQT_SSC_Invoice_Detail" > SSMQTRefresh.log &

    and its running for a long time. Now can anybody please tell me is there any way to see whats going on inside as the in the file itself nothing is written after 2 hrs.

  6. #6
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    You can check the application status in 'list applications show detail'. Capture several iterations of db2 snapshots (ie. applications, tables) to see if it's making any progress.

Posting Permissions

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