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.
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:
where tbspaceid=<your_tablespace_id> and tableid=4
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):
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:
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.