How to increase the db size in informix?

Posted on 2008-06-18
Last Modified: 2013-12-24
Hello experts,

I need to increase the size of my informix database, I used google to figure it out but Im not quite sure how to do it and how to do it safely. Would you please explain me how to do it, I'll appreciate it.

I also use WinSQL, I'm hoping to do it with TSQL.

Thank you
Question by:bozer
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5

Expert Comment

ID: 21811603
You cannot increase the size of a Database using SQL. SQL can be used to allocate the initial space already assigned to your instance of Informix to a table in the database or to decide what size additional units of space should be allocated to a table as it reaches its current limits.  

Informix is initially assigned space to use for stroing its databases. This space is called a dbspace with the first one ( and possibly only one) being the root dbspace.  Space is allocated to a dbspace in units called chunks each of which in the past has a two gig limit. So a dbspace can be made up of a number of chunks. To add space available to your database you need to add chunks to the dbspaces your database tables reside in.  Again on an unmanaged system you will probably find there is only one dbspace which you have to add the chunks to.

I am only familiar with managing this on Unix systems but the commands for  findinging out your dbspaces was

onstat -d

This will show dbspaces and chunks assigned to each along with usage on chunks. High usage just means space allocated to tables in database but this doesn't mean space is used. To find if free space in table you need to run monitoring utilities on tables.

In Windows environment I believe Informix/IBm provided some console software to monitor same which i am not familiar with.

Hope this helps.

Author Comment

ID: 21820201
Hello Again,

I am in a rush so I need to be more specific. Here's my database details on Windows platform:

address  number   flags      fchunk   nchunks  pgsize   flags    owner    name
0EA787E8 1        0x40001    1        1        4096     N  B     informix rootdbs
0EA78E10 2        0x40001    2        1        4096     N  B     informix IBP_prim
0EB71430 3        0x48001    3        1        4096     N SB     informix sbspace
0EB71590 4        0x42001    4        1        4096     N TB     informix IBP_temp_dbspace
0EB716F0 5        0x40001    5        1        4096     N  B     informix IBP_llog_dbspace
0EB71850 6        0x40001    6        1        4096     N  B     informix IBP_plog_dbspace
0EB719B0 7        0x40001    7        1        4096     N  B     informix IBP_data_dbspace
 7 active, 2047 maximum

address  chunk/dbs  offset     size       free       bpages     flags pathname
0EA78948 1     1    0          12800      10659                 PO-B  D:\IFMXDATA\IBP_db_IBPistrs1\rootdbs_dat.000
0EB829E8 2     2    0          12800      12747                 PO-B  D:\IFMXDATA\IBP_db_IBPistrs1\IBP_prim_dat.000
0EB82B68 3     3    0          12800      11879      11879      POSB  D:\IFMXDATA\IBP_db_IBPistrs1\sbspace_dat.000
                 Metadata 868      554      868
0EB82CE8 4     4    0          51200      50197                 PO-B  D:\ifmxdata\IBP_db_IBPistrs1\IBP_temp_dat.000
0EB82E68 5     5    0          51200      12747                 PO-B  C:\IFMXDATA\IBP_LLOGS\IBP_llog_dat.000
0EA78B10 6     6    0          51200      6347                  PO-B  D:\ifmxdata\IBP_db_IBPistrs1\IBP_plog_dat.000
0EA78C90 7     7    0          786432     266545                PO-B  D:\ifmxdata\IBP_db_IBPistrs1\IBP_data_dat.000
 7 active, 32766 maximum

I would like to grow / increase my IBP_data_dat file.

I am trying this and it doesnt work:

onspaces -a IBP_data_dbspace -p d:\ifmxdata\IBP_db_IBPistrs1\IBP_data_dat.000 -o 0 -s 10240000

Can you check it out and give me the specific command that would work for me?

Thank you

Expert Comment

ID: 21820385
Once a chunk is added to a dbspace you cannot modify its size but that doesn't really cause you a problem.  The chunk you are attempting to modify is assigned toi dbspace IBP_data_dbspace (dbspace 7) The database data will be assigned to the dbspace not directly to the chunk.

To make more space available to the dbspace add an additional chunk to the dbspace using your command above but referencing a new filename.  You can continually do this as more space is required.

onspaces -a IBP_data_dbspace -p d:\ifmxdata\IBP_db_IBPistrs1\IBP_data_dat.001 -o 0 -s 10240000

The only way to resize a chunk is to drop it from the dbspace and recreate it using different  sizing parameters.  This probably isn't an option for you as 2/3 of space is in use. You would also need to perform a backup between the drop and recreate.

Again, no access to Windows version but concept should be same.
Database Solutions Engineer FAQs

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller single-server environments.


Author Comment

ID: 21820587
I tried that before but I get this message after the command:

C:\db\INFORMIX>onspaces -a IBP_data_dbspace -p d:\ifmxdata\IBP_db_IBPistrs1\IBP_data_dat.001 -o 0 -s 10240000

The file d:\ifmxdata\IBP_db_IBPistrs1\IBP_data_dat.001 does not exist.

Expert Comment

ID: 21820831
Chunk size used to be limited to 2 Gigs, the -s option used to be Kilobytes which would suggest you are trying to create a 10 gig chunk. Drop a zero off and try again which should give you a 1 gig chunk.

Check your documentation to see what limit of chunk is on your system  How long before message comes up?  Is it failing to create the file and then stopping or immediately. If immediate try creating a file manually and then run the command which should get informix to format the file and resize it appropriately.

Expert Comment

ID: 21820936
After a quick bit of research it appears a chunk can now be 2-4TB.
 Based on that your could attempt changing your command below
onspaces -a IBP_data_dbspace -p d:\ifmxdata\IBP_db_IBPistrs1\IBP_data_dat.000 -o 0 -s 10240000

The main problem here would be you are already using this space in ypour online instance. You need to change the offset the -o option to allow for the space already used, ie the 786432 pages. Find out your page size  and then multiply the 786432 by page size. For example if 2K page size then command would be

onspaces -a IBP_data_dbspace -p d:\ifmxdata\IBP_db_IBPistrs1\IBP_data_dat.000 -o 1572864 -s 10240000

-s value still looks verylarge compared to what you are currently using if figures from production system.

Author Comment

ID: 21821597
Still No luck

C:\db\INFORMIX>onspaces -a cvp_data_dbspace -p d:\ifmxdata\cvp_db_cvpistrs1\cvp_data_dat.000 -o 1572864 -s 1024000
The chunk specified already exists.

Is there any other way? If the database is in use, should I take the server offline?

Expert Comment

ID: 21822974
No you should be able to add space while database online. From your onstat command output your page size is 4K . You would need to double the offset. Message is just protecting you from overwriting existing space and protecting data contained in it.

Author Comment

ID: 21829626
Not working, it still tells me "The chunk specified already exists."

This is really critical, is there anything else I can do?

Accepted Solution

TheLastStraw earned 300 total points
ID: 21831971
Afraid I don't know what steps you can take from here. My expertise in this area is on Unix platform but concepts should be the same.  Perhaps someone else on forum may be able to help.  

Unless you are going to add do some major upsizing on your Database you should have some time to investigate further as 1/3 of your assigned diskspace not assigned yet to tables in any database. This is in addition to space which will be free in the tables already created. Free space 266, 545 X 4K = 1 Gig plus of original 3G Data Space (786432 X 4K)

Author Closing Comment

ID: 31468285

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

636 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