How to defragment the IQ database

[former_member1368358]

Hi

How to defragment the IQ database，

I created hundreds of tables，the catelog db size grew by 50MB,

when i drop all tables,

the catelog db size no reduction, but increased some.

Thanks

[c_baker]

Why is 50MB of space an issue when IQ can store TB of data in the IQ dbspaces?

Are you creating the tables in 'SYSTEM' dbspace by any chance instead of the IQ dbspaces?

With respect to the IQ catalog (.db) file, what you should be more careful of, is the size of the catalog transaction log (.log) file. This can actually grow much larger than the .db file, and given the higher frequency of IQ catalog checkpoints, will mostly contain already committed and checkpointed transactions. This may grow large and can be truncated with the backup utilities to keep it smaller (this transaction log only manages transactions for the catalog only, not for IQ as a whole. There is a completely different mechanism for logging data changes in IQ dbspaces). Reducing the size of the catalog .log file can improve IQ startup times.

As far as actual defragmentation is concerned, again, don't worry about the catalog store. It is used as a metadata store for IQ operations and does not (or should not) contain any user data. The SQLA engine that manages the catalog store will reuse space, if available, instead of allocating more space. 50MB of catalog is small enough to most likely fit into the catalog cache (-c) memory by default anyway, so performance of this filesystem based storage will not be heavily impacted.

As far as the IQ dbspaces, again, they are already allocated. IQ efficiently manages this space and will reuse blocks and pages that are empty. I would not try to second guess the IQ allocation mechnisms for the various indexes, etc. If you are concerned about needing to defragment already-allocated tables, you can use the sp_iqrebuildindex stored procedure to optimize placement of already loaded data.

Chris

[Former Member]

Hi Christine Zhong

Whether there is a tool to get to empty page information,

Check those that can be used repeatedly, those that can not be reused.

Whether follow-up plans to provide similar defragmentation tools.

[former_member193055]

The IQ catalog store is managed by SQL Anywhere. The size does not shrink if you delete data from the catalog db. In fact, an empty page is marked as free in the catalog store for future used. The only way to reduce the size of the catalog store is to rebuild it.