How to increase the db size in informix?

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
Who is Participating?
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)
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.
bozerAuthor Commented:
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
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

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.
bozerAuthor Commented:
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.
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.
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.
bozerAuthor Commented:
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?
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.
bozerAuthor Commented:
Not working, it still tells me "The chunk specified already exists."

This is really critical, is there anything else I can do?
bozerAuthor Commented:
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.