Informix 7.24 UC5
Baan IVb - PS 6.1c.05.01

I will be reorging a 430MB table this weekend to fix table extents. I have always done it through the GUI but Baan Support recommends I do this through the command line since we have a 3-tier environment and the communication between the Informix/Baan servers is lacking. I have created and tested the Baan environment Informix server and all appears to be in working order...so my questions are:

1) After I lock baan down (on the Baan server) What user do I log in as to run bdbreconfig6.1 (on the Informix server)? -- root or bsp?

2) Does my modified inf_storage file look right? - I want the FIRST extent to be 500 MB and the NEXT extent to be 50MB.

tdinv700:100:T:group:1:30:INITIAL 125000 NEXT 13000 LOCK row DBSPACE datdbs1

3) After that I'm going to type the following command from the directory I want to drop the table to:

(bsp@Infx_Server) /informix/reorg> bdbreconfig6.1 -Ntdinv700 -Z -pB40clow -C100 2>tdinv700.log

Everything look ok?

Thanks for your help;
Theresa

Theresa,

Some answers

1) bsp is preferred

2) I think it should be:

tdinv700:100:T:group:1:30:INITIAL 500000 NEXT 50000 LOCK row DBSPACE datdbs1

The Informix database driver manual states that the INITIAL and NEXT extents values are in KB:


storage parameters
Description These are defined by the specific database driver
implementation and often map to table and index
creation options available in the host RDBMS.
The following Informix storage parameters are defined:
INITIAL <n>
Specifies the size of the initial extent to be created
when creating the table. The variable n specifies the
size in KB. The minimum size is 16 pages. The initial
extent size is used when creating the table and cannot
be changed later. If no size is specified, Informix uses
16 pages as default.
NEXT <n>
Specifies the size of next extent to be allocated for the
table. The variable n specifies the size in KB. This is
used while creating a table. If no size is specified,
Informix uses 16 pages as default.


Yet I am not an Informix admin and it could be very well be 'pages' instead of KB. In that case, your entry would be right with a 4KB page size. You could test with creating a dummy table?

3) Should be OK but allow for appropriate disk space of course

I don't understand the reason using bdbreconfig6.1 instead of the GUI. It doesn't really matter if you run either on the same server (locally). The main advantage of using the CLI utility is that you can script it and put in e.g the crontab or run it with a nohup so that you would be safeguarded from possible client disconnections.

Good luck with your weekend work! :)

Hi Patrick, thank you for your response - I really appreciated it.

The problem is that I still am not 100% sure if Informix is calculating in Pages or KB??? The beginning of Aug I had a consultant in and we reorged a tfgld410 which was 8GB (14 million rows), that appeared to have calculated in pages:

tfgld410:100:T:group:1:30:INITIAL 450000 NEXT 100000 LOCK row DBSPACE datdbs4

We started with 263 table extents and ended with 5 (48 hours later :-))

Then a week ago I did a very small (34MB) table which had 163 table extents, and after finished that one I had 10 extents??? I was fairly confident in my math, so the only thing I can figure is Informix calculated the size for this table in KB and not pages?

tdinv700:200:T:group:1:30:INITIAL 10000 NEXT 2500 LOCK row DBSPACE datdbs1

So I am still not sure what is going on? I feel like I am missing a very important piece of the puzzle? I have spoken with Informix Tech support about my confusion and they just keep saying that "extents are calculated in KB" -- but then they were unable to explain the reorg of my 8GB-tfgld410???

Thanks again for your help;
Theresa

Theresa,

No worries! I think you should have it confirmed by Baan Support as the database driver is mainly developed by Baan. At least the ora_storage parameters are alot easier since you specify units of storage (K, M) as well.

Are you sure that bdbreconfig is going to do what you want? Normally it is used to create a new table because the table definition has changed: it needs a .new runtime table definition for that. This is what the manual says:

DESCRIPTION
bdbreconfig6.1 reads a table name and company number(s) given as
arguments and converts them into a new table definition matching the new
data dictionary definition. It requires a new data dictionary with a
`.new' extension, the current data dictionary (e.g.dtimcs016) and tables
matching those in the current data dictionary. After successful
completion, old tables are deleted and new tables are generated.

bdbreconfig6.1 finds the optimum way to reconfigure and if there is no
real change in two data dictionaries (e.g. dtimcs016 and dtimcs016.new)
it prints the message "No conversion required". Furthermore it sees to
it that a database remains consistent. When more than one table is given
to be reconfigured and if an error occurs in the middle of the
operation, it reverts to the original state.


I have tried it via bdbreconfig but I normally use the bdbpre and bdbpost to 'compress' my tables (or use the RDBMS tools).

Rgrds,

Hi Theresa,
the number you specify at inf_storage is in Kbyte.The extent size which you can see within sysextents is again in Kbytes.
even if you specify an initial extent for a table larger that 2 Gbytes don't forget the chunk size limitation of informix which would cause you 8 Gbyte table divide into at least 4 extents.
Beside this , I remember I had to reorginise the "users" table with the CLI using bdbreconfig6.1 and the table were successfully reorganised.It takes a dump of the table first drops the table and loads the data again into the table.If it fails to load the table you still have the dump of the table.
I hope this will help you...


Murat