how to reduce the growth of BI file while CIM

[mjdarm]

Hi All,

We have requirement to pass a CIM to QAD's simulation cost rollup. This, we need will need to do for around 50 sites and around 24 cost sets for each site ie 50 * 24 CIMs.

Version of QAD is eB2 character version on unix.

While doing this, the process, it takes hell a lot of time. Moreover, the BI file grows like anything. Sometime it crosses 2gb and the database crashes.

Is there any parameter we need to look into to reduce the growth of BI file and improve the performance?

The DB is in multi user mode. The BI file which i mention above is actually the b1 file.

The parameter for main database in thestartup script is below
-L 400000 -c 350 -B 1000

The parameter while connecting the db is below
-Bt 350 -D 100 -mmax 3000 -nb 200 -s 63

Please note that this is being done in development environment and there are hardly 10 users. The server is IBM AIX and it has minimum configuration like 1 GB RAM and 1 processor.

Any idea will help us. Little urgent please.

Mugundan

 

[Kishor Chari]

Are you doing this all at one go? have you tried splitting it bt sites. When you say DB crashes what is the error encountered in the lg file?

 

[mjdarm]

Hi Kishore,

In the log file, I see the below error.

** Insufficient disk space to extend the before-image file.
(854)
We are in the process of increasing the BI file size to complete the process.

Ultimate aim is to reduce the BI size growth.

In the program, we are looping to si_mstr and then to cs_mstr (for 24 months), creating a CIM file for one site + 1 month combination, load the cim file.
We are creating the CIM file and loading for one site + month combination. In case I have 50 sites and 24 months, I create 50 * 24 cim files and load it.
It creates 1 CIM file, load it and then continue with another combination.

We have tried to create a Single CIM file for all combination and loading it and individual file as well.
There is no difference in the BI file size growth.

Mugundan

 

[Kishor Chari]

For which program is the cim written for?

 

[mjdarm]

simulation cost rollup.

 

[Kishor Chari]

3.13 .18 or 19?

 

[mjdarm]

30.13.19.

I have sent a gmail invite to you. accept it. we can discuss over chat.

 

[Kishor Chari]

Can you not load just one month @ a time this will make it just 50 CIM trans per run. So during the day do only 3 mnths. Stop / Start db. This way you can complete the task in 4 days.

Else create variable structure. And use a larger disk drive for bi extents.

 

[mjdarm]

Hi Kishore,

We have tried with 1 item per CIM also. Still generates a huge BI file.
Moreover, we can't break this process over days. On completion of this rollup, we have other processes to do. Hence, this needs to be completed on the same day.

This will most probably run on a CRON job in the early mornings.

We can try making a variable extent but in the mornings, if the variable crosses 2 GB, then it could lead to DB crash and their daily transactions would get affected.

Mugundan

 

[Kishor Chari]

Method 2: EnableLargeFiles
With the Enterprise License for Progress 9.1C and later, large files can be enabled against the database with the "proutil -C EnableLargeFiles" utility. This will allow the current bi file to extend beyond this 2GB limit when access resumes.
Please bear the following in mind before doing so:
a) This utility is only available with Enterprise Licenses
b) Ensure that the files ystem on which the before-image resides is enabled to support large files
c) The Progress license must support large files for the operating system (ie: even though SCO or Linux may support large files, the Progress product does not in version 9)
d) When the database is eventually re-started, roll-back recovery will kick in. During this phase the before-image can grow to up twice as much its current size in a worst case scenario, so make sure that there will be enough space for the before-image extent to expand.