Ixutil Commands on SAS spds Cluster Tables

Question

Can I reorganize indexes from SAS SPDS cluster tables? those. whether IXUTIL commands can be run directly in the SAS cluster tables (without nonclustering at first).

I tried to run cluster tables with various changes, but still get errors like:

• Segmentation error
• Failed to create reorganization index Index_name
• Failed to open dataset_name dataset

This makes me think that IXUTIL commands are probably not created for SAS SPDS cluster tables.

When I lock a cluster table and run IXUTIL commands for individual members, the commands work fine. I searched a lot on the internet for tips, tricks, etc. I didn’t find anything.

Answer 1

I did not find any documentation that says that in order to run Ixutil commands, we must first expand the cluster tables.

The documentation available around says that if the cluster table has a 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 searches and experiments, and the conclusions below are based solely on my experience:

• Ixutil statistics can be run on cluster tables; you do not have to put the table first.
• Ixutil runstats and Ixutil reorg cannot be run in the cluster table, if the commands are run, they will cause errors, as indicated in the question.

If runstats and reorg are to be run in a cluster table, it must first be nonclustered. This makes sense because indexes are created on individual members of the cluster tables, and not on the cluster table itself, so if indexes need to be reorganized, this must be done for individual members, that is, non-clustered tables.

Another conclusion, which is mostly not related to the question, but will be useful if someone is looking for work with Ixutil reorg:

• A table of clusters with unique indexes (it does not matter even if it has a composite / hybrid index) does not benefit from Ixutil reorg, if this command is executed in a table, it will say that - Ixutil reorg is completed successfully, unique indexes do not need to be reorganized indexes . This is also not indicated in the documentation.

Answer 2

I checked all available manuals for SAS SPDS. I also read a few technical notes, trying to find any evidence that ixutil can be used in clustered tables (in particular, dynamic cluster tables).

It is not indicated anywhere that ixutil cannot be used in dynamic clustered tables.

But in all sections devoted to "Dynamic clustered tables", it is noted that there is a very limited number of operations that are allowed to run in dynamic cluster tables: Creating a cluster, destroying a cluster, adding tables, listing tables . For any other operation, we need to expand the tables first.

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