About SAP IQ_SYSTEM_MAIN

[Former Member]

Hi guys,

I have some doubts about SAP IQ file systems as I have been using this technlogy lately.

It seems my tables are being created in IQ_SYSTEM_MAIN and eventually it runs out of space. I have run the dbspace info command and it returned the following:

I went to SAP Control Center and the dbspaces menu is greyed out so I can't change the size of it so it seems I am kinda stuck but I would like to understand what needs to be done so the database auto-increments it's size and I don't need to worry about the dbspace size.

Thank you

[john_ting · Accepted solution]

In case if you run out of IQ_SYSTEM_MAIN... You need to increased the size of

this dbspace.

You can add more IQ_SYSTEM_MAIN

as follows:

The following command will add another file and increase the size of IQ_SYSTEM_MAIN by 1000MB

alter dbspace IQ_SYSTEM_MAIN add file IQ_SYSTEM_MAIN_01

'/SBTG_data2/sybase_data/ISEC_SYBASE_IQ_devs/IQ_SYSTEM_MAIN_01.iq' size

1000MB

So assume for example if the original size is 400, then after this command is executed the size of IQ_SYSTEM_MAIN will be 1400 MB.

I would   make sure to Create user main dbspaces so that users do not try to place user tables or indexes in IQ_SYSTEM_MAIN.

The best practice is to avoid placing user tables or indexes in IQ_SYSTEM_MAIN. The administrator may allow user tables to be created in IQ_SYSTEM_MAIN, especially if these tables are small, very important tables. However, the recommended method is that immediately after creating the database, the administrator creates a second main dbspace (a user main dbspace), revokes CREATE privilege in dbspace IQ_SYSTEM_MAIN from PUBLIC, grants CREATE privilege for the new main dbspace to selected users or PUBLIC, and sets PUBLIC.DEFAULT_DBSPACE to the new user main dbspace.

For example:

CREATE DBSPACE user_main USING FILE user_main

'user_main1' SIZE 10000;

GRANT CREATE ON user_main TO PUBLIC;

REVOKE CREATE ON IQ_SYSTEM_MAIN FROM PUBLIC;

SET OPTION PUBLIC.DEFAULT_DBSPACE = 'user_main';

Hope this helps

regards

John

[Former Member]

Ok guys thanks for your help.

Another question that is happening, and maybe it's because of this, my .db file is not increasing as I'm loading tables.

Is the .iq file that holds the data and not the .db file?

Is it possible to increase the current .iq file without adding a new one?

Thank you

[markmumy]

The .db file is the catalog.  It should not grow significantly.  All data goes into the .iq files.  You can expand the .iq file ONLY if you created it with the RESERVE clause.  Likely you did not as this is not common.  Adding a second file, or more, is the best path for you.  Also, you can add a second file and make it significantly larger.  Then mark the first, small file for read only, migrate data to the new disk and drop the old disk.

Mark.

[Former Member]

Ah I see thank you. Then my steps should be:

1 - Create a new dbspace file and size it to 1GB to start as John posted.

2 - Alter tables to the new dbspace (is this only possible 1 table by 1?)

3 - When I need more space should will I be able to do it or do i need to create it with that reserve command? I could somehow add more space from 100 to 300 mb but not it's greyed on on scc.

[c_baker]

Don't stick to only 1GB at a time increases.  Make your initial dbfile larger and add several files to your dbspace immediately, depending on your underlying storage.

I would strongly suggest reading the IQ Hardware Sizing Guide first, to understand IQ storage and create the dbspace large enough to start with.  1GB is actually pretty small nowadays.

You can also set the default dbspace for new table DDL as follows:

GRANT CREATE ON <new_dbspace> to PUBLIC ;

REVOKE CREATE ON IQ_SYSTEM_MAIN from PUBLIC ;

SET OPTION PUBLIC.DEFAULT_DBSPACE = '<new_dbspace>';

That way no objects are created in IQ_SYSTEM_MAIN. 

I think you can use the ALTER TABLE <tablename> MOVE TO <new_dbspace>; command to move an existing table from the IQ_SYSTEM_MAIN dbspace to your new dbspace

Chris

[Former Member]

At the moment in my project it's unclear what which size the database will because of history requirements. In this case, what is your recommendation ?

I read the IQ_SYSTEM_MAIN should be 5-10% of the user dbspaces but when you don't have a clear idea of dbspace size you will need, is there any guideline? (like creating multiple X GB files to reach the desirable total space)

[Former Member]

Ok guys, I've changed the all the tables dbspace but when I tried to create a new table on IQ_SYSTEM_MAIN it was still allowed. Can the dba always create tables on any dbspace no matter what? Thank you

[tayeb_hadjou]

DBA is not member of PUBLIC. You need separately set the DEFAULT_DBSPACE option for DBA too.

[markmumy]

First, don't use IQ_SYSTEM_MAIN user data.  Consider this a system area only.   Read my IQ Hardware Sizing Guide for more details on this.

One of the side effects of putting data on system main is that it fills up and can halt our operations.  Also, if you're running multiplex, it requires that you shut everything down, start in simplex, add space, restart in mpx mode, then resync all the servers.

Add a user defined space, as John describes, then simply run an alter table move to move it from system main to the new space.

Mark