IQ_SYSTEM_MAIN Out of Space

IQ_SYSTEM_MAIN is a special dbspace that contains structures necessary for the database to open: the IQ checkpoint log, IQ rollforward/rollback data for each committed transaction and each active checkpointed transaction, the incremental backup metadata, and database space and identity metadata. (Reference)

If you are getting error messages on “out of space” on IQ_SYSTEM_MAIN – do follow below steps,

• First of all, please avoid creating or placing user tables in the IQ_SYSTEM_MAIN. You are advisable to create another DBspace for user tables. Once you have created another user DBspace, do revoke creation on IQ_SYSTEM_MAIN.

##EXAMPLE##
CREATE DBSPACE NEWDBSPACE USING
FILE NEWDBSPACE1 'E:\data\NEWDBSPACE1.IQ' SIZE 10000,
FILE NEWDBSPACE2 'E:\data\NEWDBSPACE2.IQ' SIZE 10000,
FILE NEWDBSPACE3 'E:\data\NEWDBSPACE3.IQ' SIZE 10000

GRANT CREATE ON NEWDBSPACE TO NEWUSER;
REVOKE CREATE ON IQ_SYSTEM_MAIN FROM NEWUSER;
SET OPTION NEWUSER.DEFAULT_DBSPACE = 'NEWDBSPACE';

• Once you have created above USER DBspace. You can move the user tables from the IQ_SYSTEM_MAIN to the newly created USER DBspace. Refer here.

• Increase the IQ_SYSTEM_MAIN. Refer here.