Skip to Content
author's profile photo Former Member
Former Member

About SAP IQ_SYSTEM_MAIN

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • Best Answer
    Posted on Aug 23, 2016 at 05:21 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Aug 23, 2016 at 07:46 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Aug 23, 2016 at 05:40 PM

    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.

    SAP Sybase IQ 16.0 Hardware Sizing Guide

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.