Separating database system tables from the rest.

One of the issues I've had to deal with is the fragmentation of the system tables within my larger databases. I thought that separating those tables from all of the user tables within a database might be a great ideas, for example: create database asdfg in asdfg_system . . . . ; I guess my thinking would be: 1. Any growth of the system tables wouldn't affect anything else except system tables. 2. I would be able to reorg at the dbspace level for ALL dbspaces, as I could unload tables, drop and recreate dbspaces, and recreate and reload tables without bypassing those dbspaces containing system tables. Any thoughts? comments? -- John Carlson Informix DBA WHSmith USA #include std_disclaimer.h /* These are my opinions, not my company's opinion */

"Carlson@WHSmith" wrote: > > One of the issues I've had to deal with is the fragmentation of the > system tables within my larger databases. I thought that separating > those tables from all of the user tables within a database might be a > great ideas, for example: > > create database asdfg in asdfg_system . . . . ;> > I guess my thinking would be: > > 1. Any growth of the system tables wouldn't affect anything else except > system tables. > 2. I would be able to reorg at the dbspace level for ALL dbspaces, as I > could unload tables, drop and recreate dbspaces, and recreate and reload > tables without bypassing those dbspaces containing system tables. > > Any thoughts? comments? Yes. Don't worry about the fragmented system tables. In OL5.xx this was a major problem. In IDS 7/8/9 it is NOT a problem if your Data Dictionary Cache table is large enough to hold the dictionary entries for all of your active tables. You can expand the cache using the undocumented, but supported, ONCONFIG parameters DD_HASHMAX and DD_HASHSIZE. The former is the number of entries per hash bucket and defaults to 10, the latter is the number of buckets and defaults to either 31 or 128 depending on version (see onstat -g dic output). Since IDS now caches the data dictionaries for the most active tables and the cache size of adjustable, fragmentation of the system catalog tables is no longer an issue, except as it may affect the fragmentation of data tables. And of course you can always reorg the fragmented data tables. Art S. Kagel