IQ_SYSTEM_MAIN DBSpace.
Yakov Y
IQ_SYSTEM_MAIN DBSpace ran out of space. I am trying to cleanup, but i
cant - getting message bellow.
Is there a way to delete/truncate or drop table? Or to add space is
the only way?
Could not execute statement.
You have run out of space in IQ_SYSTEM_MAIN DBSpace.
-- (s_blockmap.cxx
4502)
SQLCODE=-1009170, ODBC 3 State="HY000"
Line 1, column 1
Mark Mumy
offending connection so space should be freed up. However.... As this is
the IQ_SYSTEM_MAIN if it is full and we need space for system work and it is
that system work that has caused it to fill up, a reboot may be required.
Are you using IQ_SYSTEM_MAIN for user data? This is strongly discouraged.
How large is IQ_SYSTEM_MAIN compared to your user defined main store
(assuming you have one)? It should be 1-2% with a minimum of about 4-8 GB.
Here's a snippet from my IQ hardware sizing guide on main store.
http://www.sybase.com/detail?id=1062015
IQ_SYSTEM_MAIN
In IQ 12.7 we talked in terms of dbspaces. A dbspace was a flat file or raw
device. There was a 1 to 1 mapping.
In IQ 15, that has changed. A dbspace is a logical storage container for
data. This new dbspace will be made up of one or more flat files or raw
devices. In IQ 15 terms, a dbspace is made up of files. Files (sometimes
referred to as dbfiles) are the physical storage, while a dbspace is the
logical store. In IQ, a file is a filesystem based file or a raw device.
IQ 15 only allows 1 temp dbspace. This dbspace can be made up of 1 or more
files (flat file or raw device). To add a file to a dbspace in IQ, you
would use the ALTER DBPSACE command to add more storage to the IQ temporary
store.
The meaning of the term ("dbspace") varies according to the product version
you are using. Sybase IQ 12.7 implemented one-to-one mapping between a
dbspace and a database file. With DEFAULT_DISK_STRIPING option 'ON', Sybase
IQ automatically distributed data across all available dbspaces for optimal
performance and ease of administration. Users could not specify in which
dbspace a table or index should be created.
The term file, with a corresponding logical filename and physical file path,
refers to each operating system file to be used to store data.
Each dbspace name, file name, and physical file path must be unique. The
file name can be the same as the dbspace name.
A store is one or more dbspaces that store persistent or temporary data for
a special purpose. Sybase IQ has three types of data stores:
. The catalog store contains the SYSTEM dbspace and up to twelve
additional catalog dbspaces.
. The IQ main store contains the IQ_SYSTEM_MAIN dbspace and other user
dbspaces.
. The IQ temporary store contains the IQ_SYSTEM_TEMP dbspace.
It is highly recommended that IQ_SYSTEM_MAIN NOT be used for user data.
Sybase IQ allows for the creation of user defined dbspaces for this purpose.
It is best to think of IQ_SYSTEM_MAIN, in its entirety, as a system area
much like master (and master.dat) in Sybase ASE: something that should never
be used to store user data. In Sybase IQ, it is time to think of
IQ_SYSTEM_MAIN as a system area like master and our catalog. In most RDBMS
engines, it is not typical to mix user data and structures with system data
and structures. Sybase IQ should be no different beginning with version 15.
In the new IQ_SYSTEM_MAIN, the dbspace and file free list is maintained.
Additionally, versioning space, some node to node communication, the TLV
replay, and more is done on this dbspace. By default, 20% of IQ_SYSTEM_MAIN
is reserved for this. If more space is needed in IQ_SYSTEM_MAIN to store
user data, more space will be reserved. Also, when adding space to
IQ_SYSTEM_MAIN the entire multiplex must be shut down and the nodes
synchronized.
For these reasons, it is best to leave IQ_SYSTEM_MAIN as a system area with
little or no user data.
IQ_SYSTEM_MAIN Sizing
Sizing the default main store is relatively straightforward and based on the
size of the user defined dbspaces, in total, as well as the number of nodes
in the multiplex.
For databases that are less than 100 GB, it is recommended that
IQ_SYSTEM_MAIN be at least 4 GB in size, and typically sized at 5-10% of the
user defined main space size (5-10 GB for a 100 GB database). If this
instance is migrated to a multiplex, an additional 1 GB of space should be
added per node in the multiplex. For a 2 node system with a 100 GB
database, the size would then be 7-12 GB in size.
For databases that exceed 100 GB, it is recommended that IQ_SYSTEM_MAIN be
at least 8 GB for a simplex and 16 GB for a multiplex. IQ_SYSTEM_MAIN would
typically be sized at 1-2% of the user defined main space size (10-20 GB for
a 1 TB database). If this instance is migrated to a multiplex, an
additional 0.1-0.3% (1-3 GB per 1 TB) of space should be added per node in
the multiplex. For a 4 node system with a 1 TB database, the size would be
the 16 GB minimum plus 1-3 GB per node (4 nodes) in the multiplex; or 20-28
GB.
Mark
============================================================
--
You received this message because you are subscribed to the Google Groups
"iqug" group.
To post to this group, send email to iq...@googlegroups.com.
To unsubscribe from this group, send email to
iqug+uns...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/iqug?hl=en.
Yakov Y.
It looks like i will have to rebuild my server.
Mark Mumy
you don't have one yet) and add storage to it. Then move the data from one
dbspace to another. When the data is moved, simply drop the dbfiles from
IQ_SYSTEM_MAIN.
Yakov Y
May i ask you one more question - what is the best way to move data
from main to user dbspace?
Mark Mumy
You do that via ALTER TABLE.
ALTER TABLE foo MOVE TO NEW_DB_SPACE;
The MOVE TO clause moves all table objects including columns, indexes,
unique constraints, primary key, foreign keys, and metadata resided in the
same dbspace as the table is mapped to the new dbspace.
Each table object can reside in only one dbspace. Any type of ALTER MOVE
blocks any modification to the table for the entire duration of the move.