• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4220
  • Last Modified:

size of dbspace

Hi,

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
SQLSTATE: HY000
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
) IN "IQ_SYSTEM_MAIN"
0
thomaszhwang
Asked:
thomaszhwang
  • 4
  • 2
2 Solutions
 
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\IQMainStore.iq. Only 0 blocks or less may be
added.
-- (s_db.cxx 2817)
SQLCODE=-1009157, ODBC 3 State="HY000"
Line 1, column 1

ALTER DBSPACE IQ_SYSTEM_MAIN
ALTER FILE IQ_SYSTEM_MAIN ADD 500MB
0
 
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:

    http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.help.iq.15.3/title.htm

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...
0
 
drittenhCommented:
Thomas,

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....

HTH,

- David

 

0
Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

 
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.
ALTER DBSPACE IQ_SYSTEM_MAIN
ALTER FILE IQ_SYSTEM_MAIN ADD 500MB

Open in new window

0
 
thomaszhwangAuthor Commented:
Thanks David.  It makes sense to me.  It also looks like you explained my previous comment.
0
 
thomaszhwangAuthor Commented:
Thanks.
0
 
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.
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now