Sybase system tables fragmented. What to do?

[cougar91]

Hi we are running ASE 12.5.3 on LINUX and I noticed some blocking on system tables sysobjects and syscolumns from time to time. I ran optdiag and it shows both table with very low clustering ratio. I want to see if I can defragment them but they being system tables I wanted to check here to see how I should go about it and if anyone has done something like this before? Appreciate feedback & comments.

 

[ptheriault]

You could try running reorg. Here is a link with more info.

http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sag/@Generic__BookTextView/62769;pt=63894

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[cougar91]

Hi Paul,

Unfortunately not using data-only locking so can't do REORG. Thought about dropping and recreating a clustered index but since this is a system table, how do I proceed? I seem to remember there is some type of DB option to set so that you can apply DDL (or is it DML only) to system tables in Sybase?

 

[ptheriault]

Yes there is.

sp_configure "allow updates to system tables",1

I would be very careful while making changes to system tables.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[cougar91]

> sp_configure "allow updates to system tables",1

But I think this is only DML, not DDL. I need to drop and recreate clustered index. Any other idea?

 

[ptheriault]

You correct that is to make DML changes. Have you tried to drop and recreate the clustered index? Did you get an error? If it didn't let you this may not be possible. I'll continue to research and post back if I find anything for you.
Sorry I can't be of more immediate help.

- Paul
- If at first you don't succeed, find out if the loser gets anything.