Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2012
    Posts
    177

    Unanswered: Issue in SYSCATSPACE

    Hi all,

    In our setup db2 v9.7 with fixpack 9.

    Facing issues in the tablespace level.

    Select Statement is taking long time to execute and ends with The file system is full.. SQLCODE=-968, SQLSTATE=57011, DRIVER=4.12.79. This is partitioned by range.

    I have checked the db2diag.log file:

    2014-08-11-16.04.53.029454+600 I10296E531 LEVEL: Error
    PID : 12833 TID : 46965952866624PROC : db2sysc 0
    INSTANCE: db2inst1 NODE : 000 DB : TIS_DWHT
    APPHDL : 0-31620 APPID: 149.176.13.223.54074.140811060452
    AUTHID : VR_COG
    EDUID : 94682 EDUNAME: db2agent (TIS_DWHT) 0
    FUNCTION: DB2 UDB, common communication, sqlcctest, probe:50
    MESSAGE : sqlcctest RC
    DATA #1 : Hexdump, 2 bytes
    0x00002AB71CEF3AC0 : 3600 6.
    /file system
    ...skipping
    (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.

    2014-08-11-16.17.47.098234+600 E205567E1116 LEVEL: Error (OS)
    PID : 12833 TID : 46912927426880PROC : db2sysc 0
    INSTANCE: db2inst1 NODE : 000
    EDUID : 41 EDUNAME: db2pclnr (DWH) 0
    FUNCTION: DB2 UDB, oper system services, sqlowrite, probe:60
    MESSAGE : ZRC=0x850F000C=-2062614516=SQLO_DISK "Disk full."
    DIA8312C Disk was full.


    Issue in the SYSCATSPACE,

    Tablespaces for Current Database:

    Tablespace ID = 0
    Name = SYSCATSPACE
    Type = Database managed space
    Contents = All permanent data. Regular table space.
    State = 0x0000
    Detailed explanation:
    Normal
    Total pages = 57344
    Useable pages = 57340
    Used pages = 56068
    Free pages = 1272
    High water mark (pages) = 56068
    Page size (bytes) = 4096
    Extent size (pages) = 4
    Prefetch size (pages) = 4
    Number of containers = 1


    I believe it's due to pages issue, How to sort out this issue?

    Thanks,
    laxman.

  2. #2
    Join Date
    Sep 2013
    Location
    India
    Posts
    246
    Do your DB has no tempspace ? try to filter out your select / or select less no of columns as page size is only 4K.
    ssumit

  3. #3
    Join Date
    Sep 2013
    Location
    India
    Posts
    246
    check

    ulimit -a
    ssumit

  4. #4
    Join Date
    Sep 2012
    Posts
    177
    Hi Sumit,

    Thanks for your respone.

    It's a simple select statement and i have not used any condition. And rest of the tables in this DBs are working fine.

    And we have temporary tablespaces in our setup.

    [db2inst1@vrdtisdbhcl01 ~]$ ulimit -a
    core file size (blocks, -c) 0
    data seg size (kbytes, -d) unlimited
    scheduling priority (-e) 0
    file size (blocks, -f) unlimited
    pending signals (-i) 256237
    max locked memory (kbytes, -l) 32
    max memory size (kbytes, -m) unlimited
    open files (-n) 1024
    pipe size (512 bytes, -p) 8
    POSIX message queues (bytes, -q) 819200
    real-time priority (-r) 0
    stack size (kbytes, -s) 10240
    cpu time (seconds, -t) unlimited
    max user processes (-u) 256237
    virtual memory (kbytes, -v) unlimited
    file locks (-x) unlimited


    Thanks,
    laxman.

  5. #5
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    the process is a page cleaner - writing pages to disk
    try to monitor - the filesystem that gets full - the file that is increasing and leading to this
    is autoresize activated for tablespace ?
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  6. #6
    Join Date
    Sep 2012
    Posts
    177
    Hi Guy,

    Thanks for your reply.

    DB is partitioned across two servers node0 & node 1. While executing the simple select statement .TMP is keep on increasing and ends with file system full issue.

    /db2home/db2inst1/NODE0000/DWH/T0000001

    2.9G C0000000.TMP

    /db2home/db2inst1/NODE0001/DWH/T0000001

    3.5G C0000000.TMP

    And we have 8K,16K, 32K temp tablespaces in this DB, I am not sure why it's using TEMPSPACE1??

    Thanks,
    laxman.

  7. #7
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    the tmpspace is needed to resolve the query for where/order/join... - look at explain plan....
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  8. #8
    Join Date
    Sep 2013
    Location
    India
    Posts
    246
    are you including any ordering / sorting in query ?

    You may need to extend your filesystem if container is already set to auto extend YES.
    ssumit

Posting Permissions

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