SAP IQ out of space in IQ_SYSTEM_MAIN DBSpace

[Former Member]

Dear expert,

We the following environment:

• SAP IQ SP08 Database on a separate server
• SAP Data Services 4.2 SP04 which loads data into the IQ database.

We have created a IQ_USER_MAIN DBSpace, granting to it 'Create' privilege to PUBLIC, revoking 'Create' privilege from IQ_SYSTEM_MAIN, and setting the default DBSpace with the following command: {SET OPTION PUBLIC.DEFAULT_DBSPACE = 'IQ_USER_MAIN'}.

The problem comes every time the scheduled batch jobs run... They try to write in the IQ_SYSTEM_MAIN unless I manually execute every time the command {SET OPTION PUBLIC.DEFAULT_DBSPACE = 'IQ_USER_MAIN'} before the job execution... The jobs fail, and it gives the error 'You have run out of space in IQ_SYSTEM_MAIN DBSpace'.

The strange thing is that when I check the properties with the command 'sp_iqcheckoptions', it always shows IQ_USER_MAIN as a default DBSpace.

Are we missing some configuration within the IQ_USER_MAIN DBSpace? It does not make sense the fact of having to manually execute the DEFAULT command every time to avoid writing data in the IQ_SYSTEM_MAIN...

Any help/advice would be really appreciated.

Thank you very much and best regards,

Pau

[markmumy · Accepted solution]

WWhat does sp_iqdbspace, sp_iqdbspaceinfo, and sp_iqdbspaceobjectingo show?

you likely have objects in system main. setting the option only controls future table creations. So if you created tables before setting the option they will be in system main not user main.

Mark

[Former Member · Accepted solution]

Hi Mark,

Yes you are right. I am aware of the fact that some "user" objects were already created on the DB main dbspace before setting the default option...

What would you recommend as a best practice to correct this issue?

Thank you very much for your fast reply,

Pau

[markmumy · Accepted solution]

I would use the alter table move command. It allows you to move objects from one dbspace to another.

Mark