IDMS TUNE INDEX

[Denise Basore]

Hey Everybody - I'm looking for Tune Index advice. 
We have some index sets that index millions of records. They are fine on space but have lots of orphans.
I'm wondering if it's safe to attempt Tune Index against them. None of our test regions are production-sized and I don't have disk space available to copy production and try to run local mode.

[Tommy Petersen]

If you do not specify any of the sub parameters, it will just fix orphans.  
It does a good job at rebalancing and resequencing.
When I first ran it, I used a page reserve and ran out of space, and it rolled out beautifully.

[Denise Basore]

Thanks Tommy for your answer.

In the many months since I posted this, I've tuned many indexes. I do have to be careful with index sets that have tens of millions of records. The bottom line is I watch the storage pool while tune index is running against a very large index and, if I start to go short-on-storage, I cancel the job. Like you said, it rolls out beautifully.

[Margaret Sliming]

Hi Denise, since others are responding to your 3-year old question, thought I would throw in a trick a DBA taught me thirty years ago.  This method stores the index records in the same order of the key values.  That way, when walking an index, you will likely only need one index I/O for many data I/O.  If the index is MA, the schema and subschema have to be changed and recompiled to make the index Optional manual.  Programmer writes a program to disconnect all the data records from the index to be updated and extract the key values and db-keys.  The file of key values is sorted by key value and db-key.  Another program reads this file of key values, obtains the data record by DB-key and connects it to the index.  The Connection function stores an index record which in this case, will be sequenced by both key value and db-key.  When the database is updated, the index is changed back to MA with the schema and subschema recompiled.   It sounds more complicated than it is and it really speeds up retrieval.

[Tommy Petersen]

I never realized this was an old post.  It just showed up in my email last week, and I had run Tune Index the week before that, so I thought what "perfect timing".
The developers had run a purge program and, after I ran Tune Index, complained that not enough space was freed up, and I could confidently say that it would be because not enough records were deleted.

[Margaret Sliming]

I just got an email from it the other day.  I didn’t notice the date of Denise’s post until after I sent my reply.

[Margaret Sliming]

I learned something.  I didn’t know there was such a tool called ‘Tune Index’.  Will have to check it out.

[Margaret Sliming]

If anyone is interested in trying my suggestion, I can provide you with the disconnect and connect programs as well as the JCL.  For applications with really large indexes, this will cut I/O significantly.