Results 1 to 3 of 3

Thread: tempspace usage

  1. #1
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11

    Unanswered: tempspace usage

    DB2 10.1 fp2 on linux x64
    simple database without dpf having a simple table
    only db in the instance/machine
    (0)[db2inst1@plx00003 retrieve]$ db2 describe table legal.gm_ssin

    Data type Column
    Column name schema Data type name Length Scale Nulls
    ------------------------------- --------- ------------------- ---------- ----- ------
    ID SYSIBM BIGINT 8 0 Yes
    SSIN SYSIBM CHARACTER 11 0 Yes
    PART SYSIBM INTEGER 4 0 Yes
    we have 500G free space
    /dev/mapper/vg02-db2data_fs1_lv 567667808 432871916 129035252 78% /mnt/db2data/fs1
    /dev/mapper/vg02-db2data_fs2_lv 567667808 432857680 129049488 78% /mnt/db2data/fs2
    /dev/mapper/vg02-db2data_fs3_lv 567667808 432857848 129049320 78% /mnt/db2data/fs3
    /dev/mapper/vg02-db2data_fs4_lv 567667808 432857748 129049420 78% /mnt/db2data/fs4

    trying to create an index just on ssin
    the table keeps about 3billion rows - the index should not more than 100G
    but trying this we always get fs full for temp space usage
    I even tried select count(*),part from legal.gm_ssin group by rollup(part)
    both use all space left
    I can understand it uses bufferpool pages, but why is this operation creating so much tempspace
    any idea how to resolve a similar problem - we don't have additional space available for the moment....
    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

  2. #2
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    If compression is available, compress the table and any existing indexes before creating the new compressed index.

    Check the number of containers for the temp tablespace concerned - it might be fewer than you think (meaning there may be less available storage than you might expect because fewer filesystems are being used). I've seen this before.

    Sometimes a smaller extent-size can help with allocation issues.

    If the database was created with older versions of DB2, verify that the temp tablespace(s) support large RIDs.

    You might consider limiting the temp space in DB2 via registry variable DB2_OPT_MAX_TEMP_SIZE (with db2set -immediate - no restart needed), although I'm unsure if 'create index' will respect this variable , though easy to try.


    Failing the above:
    You might temporarily allocate lots more space for the temporary tablespace container(s) - if your sysadmin / storage-admin is willing to do this and space is available...
    Anything to avoid reloading the data I guess...

  3. #3
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    thanks for the update
    db was created in V10.1
    all data/temp is using automatic storage and I verified for tempspace - it is using 4 containers and also df indicates equal grow over 4 fs when running
    I will see if DB2_OPT_MAX_TEMP_SIZE can help
    for the moment we can not get additional space - so if all this does not help, it has to wait..
    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

Posting Permissions

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