Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


How to increase the db size in informix?

Posted on 2008-06-18
Medium Priority
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.
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.


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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
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…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

730 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