Solved

How to increase the db size in informix?

Posted on 2008-06-18
11
3,436 Views
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
0
Comment
Question by:bozer
  • 6
  • 5
11 Comments
 
LVL 2

Expert Comment

by:TheLastStraw
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.
0
 

Author Comment

by:bozer
ID: 21820201
Hello Again,

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


Dbspaces
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

Chunks
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
0
 
LVL 2

Expert Comment

by:TheLastStraw
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.

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

Author Comment

by:bozer
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.
0
 
LVL 2

Expert Comment

by:TheLastStraw
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.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 2

Expert Comment

by:TheLastStraw
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.
0
 

Author Comment

by:bozer
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?
0
 
LVL 2

Expert Comment

by:TheLastStraw
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.
0
 

Author Comment

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

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

Accepted Solution

by:
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)
0
 

Author Closing Comment

by:bozer
ID: 31468285
thanks
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

This article describes some very basic things about SQL Server filegroups.
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

758 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now