Other ERPsFree calculatorPDF, Excel, Word exportQADAll QAD tools

QAD Progress OpenEdge Storage Area Sizing Calculator

This calculator sizes a Type II storage area for a large QAD table, such as tr_hist or ld_det, on Progress OpenEdge 11 and 12. It shows how records per block, block size and mean record size decide how many blocks you need, and how big the area grows.

Your numbers

Rows in table

rows

Current record count, from tabanalys.

Mean record size

bytes

The Mean column for the table in proutil dbanalys or tabanalys.

Database block size

Records per block (RPB)

Create limit

bytes

Space kept free in each block for record growth.

Annual growth

Planning horizon

3 years

Fixed extent size

Your results

Area size today

5.4496

GB of data blocks, excluding index areas.

Area size at end of horizon

7.6563

Records that fit by space

Usable block space (block size minus create limit minus about 64 bytes of header) divided by mean record size plus a 2-byte directory entry.

Effective records per block

The lower of RPB and what fits by space.

Blocks needed

714,286

Fixed extents needed

Plus one variable extent at the end of the area.

Unused record slots

45.3%

Unused row addresses per block when RPB is higher than what fits. Costs address space only, not disk.

Unused block space

Disk space left empty in each block when RPB is too low. This is the costly case.

Block header overhead and per-record overhead are approximations. Use dbanalys on a copy of your database to check real fill, and size index areas separately.

Save results:

Area size today vs mean record size

Sweeps Mean record size from 50 to 1000, holding your other inputs fixed.

Want your storage layout reviewed?

Send us your dbanalys output and structure file and we will suggest RPB, areas and extents for your largest QAD tables.

Optional. The tool and every export work without this. No spam.

Was this tool useful?

How to use it

Run proutil dbname -C dbanalys (or tabanalys) on a recent copy of the QAD database and take the record count and the Mean record size for the table. Pick the database block size and the RPB you plan for the area, keep the create limit at its default unless you changed it, and enter growth and horizon. The two primary outputs give the area size today and at the end of the horizon; the extents output tells you how many fixed extents of your chosen size to define in the structure file, with a variable extent last.

Block size is set when the database is created: changing it means a dump and load.
RPB is set per area: changing it means moving the table to a new area.
Size index areas separately; this tool covers data blocks only.

The method behind it

Each data block holds at most RPB records, and also only as many as fit in the space left after the block header and the create limit. Usable space is block size minus create limit minus about 64 bytes of header, divided by mean record size plus 2 bytes for the record's directory entry. The effective records per block is the lower of the two. Blocks are rows divided by that, rounded up, and size is blocks times block size. Growth compounds yearly. RPB must be a power of 2 from 1 to 256. Overheads are approximations: the dbanalys fill figures on your own data are the real check.

Worked example: 25 million rows at 220 bytes

With 8 KB blocks and a 150-byte create limit, usable space is about 7,978 bytes, so 35 records of 222 bytes fit. With RPB 64 the block fills by space at 35 records; 25,000,000 rows need about 714,286 blocks, about 5.45 GB. At 12 percent growth for 3 years the area reaches about 7.66 GB, or 4 fixed extents of 2 GB. Choosing RPB 32 instead caps each block at 32 records and leaves about 10 percent of every block empty, so the area is about 9 percent bigger for no benefit.

Choosing RPB and gotchas

A practical rule: divide the block size by mean record size plus overhead, then pick the next power of 2 above that number. Too high an RPB only wastes row addresses; too low wastes disk and buffer pool. Tables with very different record sizes belong in different areas. QAD databases often use 8 KB blocks on Unix and Linux, but that is a common practice, not a rule. Add extents online with prostrct add or addonline before the last fixed extent fills.

Frequently Asked Questions

How do I choose records per block in OpenEdge?

Take the mean record size from dbanalys, add overhead, divide the block size by that figure and choose the next power of 2 above the result, between 1 and 256. Setting RPB slightly high wastes only row addresses; setting it low leaves empty space in every block. Put tables with very different record sizes in separate areas.

Where do I get the mean record size for a QAD table?

Run proutil dbname -C dbanalys or proutil dbname -C tabanalys against the database or a recent copy. The record block summary lists each table with record count, total size and the Min, Max and Mean record size. Use the Mean figure in this calculator and rerun the analysis yearly as data changes.

Can I change the block size of a QAD database?

Not in place. The block size is fixed when the database is created, so changing it means creating a new database with the new block size and dumping and loading the data. Many sites combine that with an upgrade or a move to Type II storage areas, because the dump and load already requires downtime.

How do I add space to a storage area?

Write a structure file with the new extents for the area and run prostrct add with the database offline, or prostrct addonline while it runs on versions that support it. Then check the result with prostrct list or prostrct statistics. Add space before the variable extent starts growing on a busy system.

If you want to ask your QAD database about growth, history and open transactions in plain English on your own servers, we can set that up alongside your DBA routines.

Talk to an Expert Explore More Free Tools