Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2003
    Posts
    39

    Arrow Unanswered: SMS Tablespace 1(TEMPSPACE1) is FULL or file is too large (at OS or user limit)

    hi,
    we are running db2 v7.2 on solaris

    While loading a table with 11 million rows i am getting the following error in db2diag.log

    String Title:Path/Filename PID:7263 Node:000
    /db2/db200008/dbnc3db/db200008/NODE0000/SQL00001/load/DB20000A.PID/DB200004.OID

    2004-04-06-12.37.40.100501 Instance:db200008 Node:000
    PID:18754(db2pfchr) Appid:none
    buffer_pool_services sqlbWritePageToContainer Probe:20

    SMS Tablespace 1(TEMPSPACE1) is FULL or file is too large (at OS or user limit).
    Detected on Container 0. ContPage= 51906 Obj=10 Type=128

    2004-04-06-12.37.40.151583 Instance:db200008 Node:000
    PID:18754(db2pfchr) Appid:none
    buffer_pool_services sqlbGetVictimSlot Probe:727
    DIA3612C Disk was full.

    ZRC=0xFFFFD60C

    2004-04-06-12.37.40.215422 Instance:db200008 Node:000
    PID:18754(db2pfchr) Appid:none
    buffer_pool_services sqlbErrorHandler Probe:0
    DIA3612C Disk was full.




    2004-04-06-12.37.38.680186 Instance:db200008 Node:000
    PID:11324(db2lfrm0) Appid:*LOCAL.db200008.040406135800
    database_utilities DIAG_ERROR Probe:0 Database:NC3DB

    Error!!! -3531, 0, Detected in file: sqluldat.C, line 1227

    2004-04-06-12.37.38.741659 Instance:db200008 Node:000
    PID:7263(db2agent (SNC3UAT)) Appid:*LOCAL.db200008.040406135800
    database_utilities call_sqluvload Probe:40 Database:NC3DB

    Load Error: Load failed for table NC3 .PARM in tablespace 10.

    2004-04-06-12.37.38.789214 Instance:db200008 Node:000
    PID:7263(db2agent (SNC3UAT)) Appid:*LOCAL.db200008.040406135800
    database_utilities sqlu_register_table_load Probe:150 Database:NC3DB

    CA before rollback0000 0000 0000 0000 0000 0000 ffff fc38 ...............8
    0000 0000 0000 0000 0000 0000 0000 0000 ................
    0000 0000 0000 0000 0000 0000 0000 0000 ................
    0000 0000 0000 0000 0000 0000 0000 0000 ................
    0000 0000 0000 0000 0000 0000 0000 0000 ................
    0000 0000 0000 0000 5351 4c55 5343 4f4d ........SQLUSCOM
    ffff d60c 0000 000c 0000 0000 0000 0000 ................
    0000 0000 0000 0000 0000 0000 0000 0000 ................
    0000 0035 3730 3131 ...57011




    The ulimit is
    ulimit -a
    core file size (blocks) unlimited
    data seg size (kbytes) unlimited
    file size (blocks) unlimited
    open files 256
    pipe size (512 bytes) 10
    stack size (kbytes) 8192
    cpu time (seconds) unlimited
    max user processes 29995
    virtual memory (kbytes) unlimited


    I have created the other DMS TS with 2 containers of 2 GB each
    I thought while building indexes or soring the default TEMPSPACE will
    be small enough to load the table.I could see the load being shifting to
    the newly created DMS TS by looking the high water mark field of list tablespaces , (High water mark (pages) = 448304)
    Still I am getting the message given above.
    Kindly suggest me to how to solve the problem,Any information will be appreciated
    Thanks

    ashaq

  2. #2
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    What is the path for your TEMPSPACE?
    db2 "list tablespace containers for 1 show detail"

    Do a "df -k" for the path and make sure it's that disk that's not filling up.

    Are there messages before that related to the load? Curious what stage it's in, I assume index create.
    --
    Jonathan Petruk
    DB2 Database Consultant

  3. #3
    Join Date
    Dec 2003
    Posts
    39
    The disk space is not becoming full ,there is sufficiend space provided

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Check with your system admin if the file system is large file enabled ...

    Or you may try creating a file larger than 2 Gigs to see if it does

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Originally posted by sathyaram_s
    Check with your system admin if the file system is large file enabled ...

    Or you may try creating a file larger than 2 Gigs to see if it does

    Cheers
    Sathyaram
    A quick fix might be to add a container (path) to the TEMPSPACE tablespace, preferably on a separate physical disk.
    --
    Jonathan Petruk
    DB2 Database Consultant

  6. #6
    Join Date
    Dec 2003
    Posts
    39
    Kindly tell me how can I check on solaris if the file system is large
    file enabled..

    I added the another container for SMS Tempspace1 ,the initial size was 2k ,After starting the Load each containers start growing but after
    reaching to the 866298 k ,both containers stopped growing .
    bash-2.03$ du -sk /db2/db200008/dbnc3db/db200008/NODE0000/SQL00001/SQLT0000.5
    866298 /db2/db200008/dbnc3db/db200008/NODE0000/SQL00001/SQLT0000.5

    du -sk /db2/db200008/dbnc3db/db200008/NODE0000/SQL00001/SQLT0000.4
    866538 /db2/db200008/dbnc3db/db200008/NODE0000/SQL00001/SQLT0000.4



    but the DMS type temporary TS was simultaneously being used and growing

    eg.
    Tablespace ID = 16
    Name = TEMPSPACE2
    Type = System managed space
    Contents = System Temporary data
    State = 0x0000
    Detailed explanation:
    Normal
    Total pages = 432965
    Useable pages = 432965
    Used pages = 432965
    Free pages = Not applicable
    High water mark (pages) = Not applicable
    Page size (bytes) = 4096
    Extent size (pages) = 32
    Prefetch size (pages) = 32
    Number of containers = 2

    Finally after loading for 6 hours it fails during the index rebuilding stage

    Need your sugesstions


    Ashaq

  7. #7
    Join Date
    Dec 2003
    Posts
    39
    Thanks Satyaram ,Petruk

    the problem is solved now,I deleted the existing Temp Ts and created a new one with 3 containers on different file system ,the problem was due to the less space available.

Posting Permissions

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