size of dbspace


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
Who is Participating?
Joe WoodhousePrincipal ConsultantCommented:
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...
thomaszhwangAuthor Commented:
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


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


Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

thomaszhwangAuthor Commented:
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

thomaszhwangAuthor Commented:
Thanks David.  It makes sense to me.  It also looks like you explained my previous comment.
thomaszhwangAuthor Commented:
Joe WoodhousePrincipal ConsultantCommented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.