Ixutil commands on SAS spds Cluster tables

Question

Can indexes be reorganized of SAS SPDS Cluster tables? i.e. Can IXUTIL commands be run directly on SAS Cluster tables ( without unclustering first).

I tried running on the cluster tables with various alterations but so far only getting errors like:

• Segmentation fault
• Could not Reorg Index Index_name
• Could not open dataset dataset_name

This makes me think that probably IXUTIL commands are not made for SAS SPDS Cluster tables

When I uncluster the cluster table and run the IXUTIL commands on individual members, the commands run fine. I have searched a lot on the internet for clues, hints etc have not found a thing.

Answer 1

I checked all available versions of manuals for SAS SPDS. I also read through few technical notes trying to find any evidence that ixutil can be used on clustered tables (specifically dynamic clustered tables).

Nowhere it is stated that ixutil can not be used on dynamic clustered tables.

But, in all sections that talk about "Dynamic Clustered Tables", it is noted that there are very restricted number of operations that are allowed to be run on dynamic clustered tables: Creating a cluster, undoing a cluster, adding tables, listing tables. For any other operation, we need to first uncluster the tables.

I will add references if the above discussion seems relevant to you.

Answer 2

I have not found any documentation which says that for running Ixutil commands we have to uncluster the cluster tables first.

The documentation available around says, if the cluster table has hybrid index we can run the Ixutil commands:

http://support.sas.com/documentation/cdl/en/spdsag/69111/HTML/default/viewer.htm#p0dp0zats425t8n1ms5xdg6ut82s.htm

http://support.sas.com/documentation/cdl/en/spdsug/67140/HTML/default/viewer.htm#p1e8tlzjl677v8n1w4th6f4idtqa.htm

I have done some extensive search and experiments on it and below findings are solely based on my experience:

• Ixutil stats can be run on cluster tables, no need to uncluster the table first.
• Ixutil runstats and Ixutil reorg cannot be run on clustered table, if commands are run it will throw errors as mentioned in the question.

If runstats and reorg are to be run on cluster table, it has to be unclustered first. It makes sense because indexes are created on the individual members of the cluster tables and not on the cluster table itself so if indexes are to be reorganized , it has to be done on individual members i.e. unclustered tables.

Another finding which is not basically related to the question but would be helpful, if someone is looking to work with Ixutil reorg:

• Cluster table with unique indexes (does not matter even if it has composite/hybrid index) do not get benefited with Ixutil reorg, if this command is run on the table, it will say that - Ixutil reorg completed successfully, Unique indexes do not need reorganizing of the indexes. This is also no where mentioned in the available documentation.