Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2003
    Location
    austin,tx
    Posts
    104

    Unanswered: correlate tablespace file to database object

    DB2 v9.7 on Windows..
    My disk is nearing full capacity and I'm trying to determine what I can shrink/drop/reorg to reclaim space.

    Question 1)
    Is there a way to to correlate the files in the tablespace directory to database objects? This would help me more easily determine which objects to concentrate on and I can also see how successful I am at shrinking them.

    Question 2)
    I see a couple of very large .IN1 files in the directory.
    According to this:
    IBM Knowledge Center

    .IN1 files are "temporary file is created during an index reorg operation".

    I've got one that is 67GBs.. There should not be a reorg currently running. The update date on the file is recent however. Is there any way to tell if this file is legit, in-use, etc?

    Thanks.

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    do not touch db-files...
    db2pd -d xx -reorg (online reorgs)
    db2 list utilities
    check if free space in tablespaces and resize them
    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

  3. #3
    Join Date
    Aug 2003
    Location
    austin,tx
    Posts
    104
    Running:
    db2pd -db <database> -reorg
    and
    db2pd -alldbs -reorg
    Shows no active reorgs. The .IN1 file in question was modified again overnight. It has the same modified date as a couple of .DAT and .INX files.

    No reorgs should have occurred last night, the automatic maintenance window for REORG is only open one day a week for a few hours, and that was several days ago. However a RUNSTATS task was running during the time these files were updated.
    Am I barking up the wrong tree and the .IN1 file is NOT a temp file from REORG, rather a permanent index file or something?

    How do I identify what it corresponds to? This one file is ~60GB of a 90GB total tablespace/database. No one object in my DB should have that high a percentage of the space used.

    Checking the (estimated) index sizes in my DB shows the largest is 10GB*.
    The sum of all the indexes on one particular table comes in at about 52GB*
    This file could almost be the combination of all the indexes on that one table, which would make sense if it was a temp file used during REORG..

    Any ideas?

    -------
    *calculated using this:
    SELECT T1.INDNAME,
    (T1.NLEAF * T3.PAGESIZE) / (1024.0 * 1024.0) as INDEX_LEAF_SUM_MBYTES
    FROM SYSCAT.INDEXES T1,
    SYSCAT.TABLES T2,
    SYSCAT.TABLESPACES T3
    WHERE T1.TABNAME = T2.TABNAME
    AND T1.TABSCHEMA = T2.TABSCHEMA
    AND COALESCE(T2.INDEX_TBSPACE, T2.TBSPACE) = T3.TBSPACE
    AND T3.TBSPACE = 'MYTABLESPACE'
    ORDER BY 2 DESC

  4. #4
    Join Date
    Aug 2003
    Location
    austin,tx
    Posts
    104
    <doublepost>

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I'd use ADMIN_GET_TAB_INFO_V97 to find the largest objects and REORGCHK to determine if anything can be done about them.
    ---
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    Aug 2003
    Location
    austin,tx
    Posts
    104
    Appreciate the advice. The problem I'm running into is the biggest problem table I have has very large indexes as well. REORG on a table will let you do an OFFLINE reorg in a separate tablespace, so I can do that on a different disk with more space. REORG on the indexes however can only be performed in the 'parent' tablespace. And I don't have enough room currently to do that.
    That's when I came across the large .IN1 file, and set off down the path of trying to figure out what it is.. When I read that .IN1 files are temporary files I thought maybe it was left over from an aborted REORG and possibly something that could be deleted to alleviate my disk space problems.. That's how I've come to this point.

    Of course I could just drop and recreate all the indexes, but I was hoping not to have to blindly break out that hammer.

  7. #7
    Join Date
    Aug 2003
    Location
    austin,tx
    Posts
    104
    Just an update ( if anybody cares )
    I mis-read or misunderstood the docs.. An offline table reorg redirected to a different tablesspace was successful. Apparently it rebuilds the indexes completely, so the tight space in the 'parent' tablespace was not an issue. I don't believe it will work for on online reorg (ALLOW READ ACCESS) or a REORG of just the indexes, but it did for an OFFLINE table REORG.

    The large .IN1 file is still there, although somewhat smaller, so I'm guessing it contains multiple indexes on the table in question (??) and is not a temporary file as the online docs indicated.

Posting Permissions

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