size of dbspace

Posted on 2011-10-17
Last Modified: 2012-08-13

I was trying to create a table in my new built database in Sybase IQ 15.3 encountered the following error.  It looks like the dbspace runs out of space.  Is there a way to make the dbspace grow as needed or what's the best practice.  Thanks.

The table 'Shop_facts (twang)' could not be created in the database.
java.sql.SQLException: [Sybase][ODBC Driver][Sybase IQ]You have run out of space in IQ_SYSTEM_MAIN DBSpace.
-- (s_blockmap.cxx 4176)
You have run out of space in IQ_SYSTEM_MAIN DBSpace.
-- (s_blockmap.cxx 4176)
[Sybase][ODBC Driver][Sybase IQ]You have run out of space in IQ_SYSTEM_MAIN DBSpace.
-- (s_blockmap.cxx 4176)
SQLCODE: -1009170
SQL Statement: CREATE TABLE "twang"."Shop_facts" (
            "Shop_facts_id" INTEGER NULL,
      "Article_id" INTEGER NULL,
      "Color_code" INTEGER NULL,
      "Week_id" INTEGER NULL,
      "Shop_id" INTEGER NULL,
      "Margin" "money" NULL,
      "Amount_sold" "money" NULL,
      "Quantity_sold" INTEGER NULL,
      "Returned" BIT NULL,
      "VersionNumber" INTEGER NULL
Question by:thomaszhwang

    Author Comment

    Also when I tried to use ALTER DBSPACE command to increase the size of the dbspace.  I ran into the following error.

    Could not execute statement.
    There is insufficient reserve space to add 64000 blocks to segment
    D:\SybaseData\SybaseDemo\ Only 0 blocks or less may be
    -- (s_db.cxx 2817)
    SQLCODE=-1009157, ODBC 3 State="HY000"
    Line 1, column 1

    LVL 24

    Accepted Solution

    Ok, the issue is that you seem a bit confused about the IQ storage hierarchy.

    Firstly, you create objects (like tables) and data in the database. The database is itself built on at least three dbspaces - but you never directly work with dbspaces, those are just areas of storage that your database is defined on.

    So what's actually happening here is that you ran out of space in your database, and IQ is helpfully informing you that the type of space you ran out of is the "Main" space. That is to say, not "Temp" (like ASE's tempdb - temporary data only) or "Catalog" (system tables).

    You fix this by extending/ALTERing the database to make more room... and you extend the database onto one or more dbspaces.

    So the sequence here is to create one or more dbspaces, and then add them to the "Main" section of the database.

    Have a read of the IQ manuals, particularly the System Administration Guide vol 1, chapter 5, available for free at:

    It sounds like you're pretty new to IQ. Some of the other manuals there have a lot of stuff that will be helpful. Just like any other database, IQ is hard to get grips with just by diving in headfirst. I know it's often considered a rude thing to say to an IT person but you really do need to read the manuals...
    LVL 2

    Assisted Solution


    You actually need to define a IQ_MAIN (or name of your choice) DbSpace, and to this space allocate one or more DbFiles.  You have been creating user-objects in IQ_SYSTEM_MAIN, which is a system work area for things like the "free list", Table Level Versioning metadata, and checkpoint area...  you want to do the following :

    revoke create on IQ_SYSTEM_MAIN from public

    grant create on IQ_MAIN to public   (where IQ_MAIN is the name of your user-defined DbSpace, where all your database objects will be placed..)  

    grant resource to whomever    (where whomever is the user who will be creating schema objects..)

    IQ does not grow dynamically, so you allocate an amount of space  that will last you a while before adding addtional DbFiles to your user-defined DbSpace.  There is the capability of creating a DbFile "with reserve" and this is an artificial ceiling of space... so lets say I create 50G DbFile with 10G of "reserve"  this means I can use 40G, it will fill up, and then I can alter it to increase it by up to 10G.  I try to plan ahead what I will need and not rely on the reserve feature, allocating new DbFiles of uniform size as I go.....  (depending on what I think my database will grow by in the next few months..)  - it is alway good to leave yourself enough "breathing room" of between 10 and 20 percent on mainstore  as well....


    - David



    Author Comment

    Thanks Joe.  Are you saying I cannot directly extend a dbspace by adding more space to an existing dbfile using the following code?  I did find the code from the manual.

    Open in new window


    Author Comment

    Thanks David.  It makes sense to me.  It also looks like you explained my previous comment.

    Author Closing Comment

    LVL 24

    Expert Comment

    by:Joe Woodhouse
    Right - you can increase the size of a dbspace but that's not what you needed to do here. :) I think David explained it more helpfully.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    These days socially coordinated efforts have turned into a critical requirement for enterprises.
    For Sennheiser, comfort, quality and security are high priority areas. This paper addresses the security of Bluetooth technology and the supplementary security that Sennheiser’s Contact Center and Office (CC&O) headsets provide.  
    This video discusses moving either the default database or any database to a new volume.
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now