Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2014
    Posts
    20

    Question Unanswered: How to get rid of *.IN1 files in db2

    Hi,
    I am using the db2 database of 9.7 Version,fixpack 3,edition is ESE and Operating system is Linux( 5.4 Tikanga).

    The table spaces in this database are SMS. The disk utilization of the server related to this table apace is about to full.

    I found that one file(SQL00004.IN1) is the one which is having high utilization. I came to know that this is a temporary file created during an index reorg operation and this file is updating continuously.

    Can any one help me how to get rid of this file or how to reduce the utilization.

    Thanks in advance..

  2. #2
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    According to the description in the Database directories and files topic, SQL*.IN1 files contain index table data.
    Actually, there is some contradictive information about these *.IN1 files. They are referred as "Shadow copy files" for indexes during an index reorg here:
    Classic (offline) table reorganization.
    Normally INX file is for indexes, but when you run "reorg indexes all for table mytable", then IN1 file appears and INX file becomes zero-length file. Consider this as a "migration" of indexes from INX file to IN1.
    When you run the command above again indexes "migrate" back from IN1 (it becomes zero-length file) to INX.

    What's the length of your SQL00004.IN* files?
    You can try to run this "reorg indexes all" command against your table:
    Code:
    select rtrim(tabschema)||'.'||tabname
    from syscat.tables
    where tbspaceid=<your_tablespace_id> and tableid=4
    Regards,
    Mark.

  3. #3
    Join Date
    Jan 2014
    Posts
    20

    Question How to get rid of *.IN1 files in db2

    Hi Mark,

    There are 2 files with the sequence SQL00004.IN* and the size of SQL00004.IN1 is 260GB where as for SQL00004.INX is 48 K.

    But I am not running any reorg on this table. Also this IN1 file is continuoulsy updating with the current time stamp.

    Kindly help how to proceed further.

    Thanks in advance..

  4. #4
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi Santosh,

    It seems that you have the situation I described above.
    As for previous reorgs - you can try to run the following command to check if such a reorg was ever run against your table (if these history records are still in the history file of course):
    Code:
    db2 list history reorg containing TABSCHEMA.TABNAME for db your_db_name
    if you are concerning about high I/O activity on this index, then try to understand if you have an adequate bufferpool for this tablespace running the following statement, for example:
    Code:
    select 
      POOL_INDEX_P_READS + POOL_TEMP_INDEX_P_READS BP_IX_PR
    , POOL_INDEX_L_READS + POOL_TEMP_INDEX_L_READS BP_IX_LR
    , 100*(POOL_INDEX_L_READS + POOL_TEMP_INDEX_L_READS - POOL_INDEX_P_READS - POOL_TEMP_INDEX_P_READS)/NULLIF(POOL_INDEX_L_READS + POOL_TEMP_INDEX_L_READS, 0) BP_IX_HR
    from table(mon_get_tablespace('YOUR_TBSPACE_NAME', -1))
    Regards,
    Mark.

  5. #5
    Join Date
    Jan 2014
    Posts
    20

    Question H

    Hi Mark,

    There is no output available for the list history for re-org command.

    Please clarify me that by running the re-org indexes for that table will have any loss of data on that table?

    Also let me know about the the utilization of the indexes and tables after re-org process.Will the SQL*.IN1 and SQL*.INX have the same size after this process?

    Thanks in advance..

    Regards,
    Santosh K

  6. #6
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Santosh,

    Index reorg doesn't lead to any data loss.
    Actually, it might be not a good idea to reorg such a large index object just for understanding the internal data movement between IN1 and INX files. Sorry for my previous unclear suggestion. I meant you can try to reproduce the situation when the IN1 file is large and the INX file is very small or zero-length for some table. Do do this you can create some test table and indexes on it in this tablespace and run a 'reorg indexes all' command on this table to check how the sizes of these index files will change.
    Regards,
    Mark.

Tags for this Thread

Posting Permissions

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