Feature: Support Larger Page Size for Elliott Database Files

Feature: Support Larger Page Size for Elliott Database Files

Release Date: 6/24/2025
Versions: 8.6 and Higher

Background

This article provides an explanation of how Elliott BTR files' page size may affect the following two factors:
  1. The maximum database file size
  2. Performance to load a table from disk to memory
Currently, Elliott's default PSQL(Zen) database file format is V9.5 and the page size is 4K.  The following shows the maximum file size by page size:
  1. 4K - 64GB
  2. 8K - 128GB
  3. 16K - 256GB
 The page size is the unit of physical I/O for accessing the database.  For most files, this page size works well.  Elliott will support 4K, 8K or 16K page sizes. However, there are two circumstances where a very large database file could benefit from a larger page size:
  1. When the size of the file may grow beyond the 64GB limit allowed by a 4K page size.  This limit of 64GB is more than sufficient for almost all database files.  However, large Elliott installation customers may see certain of their files grow beyond that limit if they do not purge old data:
    1. IMINVTRX.BTR
    2. IMLSHST.BTR
    3. IMINVSRH.BTR
    4. perhaps others.
  2. When a SQL search needs to scan all records in the file without the aid of an index on the field.  One example would be when looking for a specific string of characters in a non-index column like CPORDPMT_COMMENT_1 column of the CPORDPMT table, so all records in the table need to be searched.  If the CPORDPMT file is very large, and the data files are not already cached in PSQL (Zen) server memory, it will take some time to load the entire table from disk to load into the database engine cache memory for searching.  A larger page size means fewer physical I/Os are necessary to read the entire file.
Here is a recent example comparing the timing between a 4K and a 16K page size on the table CPORDPMT (COP Order Payment) table. 
  1. Table Size: 4K = 1.1475GB and 16K = 1.1437GB
  2. Number of records: 974,902
Compare this SQL statement "SELECT * FROM CPORDPMT WHERE CPORDPMT_COMMENT_1 like '%123%'" and the results are listed in below table:
 Task
 4K Page Size
 16K Page Size
 Comments
First SELECT requiring a full scan
   3'26"
   1'08"
The first SELECT takes about 3 times longer using a 4K page size than using a 16K page size.  The 4K page size requires almost 4 times the number of physical I/Os. 
Repeat the same SELECT after the full scan
   8"
   8"
Once the PSQL cache contains all the records in the table, the difference in timing is negligible.

With this release, it is possible to change the default page size from 4096 (4K) to 16,384 (16K).

Use DDF2BTR to Change Page Size to 16K

You can change the page size of an existing file by running the DDF2BTR.Exe utility, which has been modified in the latest 8.6 release to include a new command-line switch, /16.

So, for example, to change the page size of IMINVTRX.BTR to 16K, run the following command at a DOS prompt after navigating to <ElliottRoot>\Bin86:

DDF2BTR /F /16 ..\DATA\IMINVTRX.BTR

In this example, /F tells the utility to force a rebuild, even if the file format matches the DDF indexes and /16 tells it to recreate the file with a 16K page size. The following is a list of command options with the latest 8.6 DDF2BTR utility:

Record Locking Issue with Larger Page Size

Since PSQL (Zen) database performs locking at page level (i.e., not at the record level), theoretically having more records per page (e.g. with 16K page size) could lead to more page locking conflicts.  We at Netcellent do not think this is likely.  However, after changing the page size to 16K for a database file, be on the lookout for this issue.  If you detect this is happening and it is causing a performance problem, you can rerun the DDF2BTR utility without specifying /16K and the file will be rebuilt with a 4K page size.

What Tables Are Suitable for 16K Page Size

You can use either the PSQL built-in Rebuild utility or DDF2BTR to rebuild certain tables with 16K page size if (1) The table size is very big; and (2) The table is typically used for read-only access and there's no concern with record locking. Typically, this is a table used for historical purposes.

The following are a list of tables that may fit these two conditions:
  1. APDISFIH - AP Distribution History
  2. ARDISFIH - AR Distribution History
  3. ARHISFIL - AR History File
  4. CPBOXFHS - COP Box History File
  5. CPBOXHSR - COP Box History Serial File
  6. CPBOXHST - COP Box History File
  7. CPHODHDR - COP History Order Header File
  8. CPHODLIN - COP History Order Line Item File
  9. CPHSTTRX - COP Sales History Trx File
  10. CPINAHDR - COP Invoice Archive History Header File
  11. CPINALIN - COP Invoice Archive History Line Item File
  12. CPINVHDR - COP Invoice Header File
  13. CPINVLIN - COP Invoice Line Item File
  14. CPLINAUD - COP Line Item Audit Trail
  15. GLTRXFIL - GL Transaction File
  16. IMBATFWF - IM BATF Report File
  17. IMBINHST - IM Bin History (related to IMINVTRX)
  18. IMDISFIH - IM Distribution History
  19. IMINVSRH - Inventory Trx Serial (related to IMINVTRX)
  20. IMINVTRX - IM Inventory Trx Audit Trail
  21. IMLOCHST - IM Location History
  22. IMLSHST  - IM Lost Serial History
  23. POAUDHDR - PO Order Header Audit File
  24. POAUDLIN - PO Order Line Item Audit File
  25. SY12MONS - SM 12 Month History File (related to IMLOCHST)
  26. SYACTLOG - SM Activity Log File
  27. SYATTRIB - SM Attribute File

Using PSQL (Zen) Rebuild vs DDF2BTR Utility

Note that if the table is already properly segmented and matches the corresponding DDF, using the PSQL Rebuild utility to change the page size will be faster than the DDF2BTR utility. With the PSQL Rebuild utility, the default page size is to use the "EXISTING" page size.  You must manually override it to "Optimal (Data Access)," then choose 16384 page size and uncheck the "Save Settings On Exit" option. See sample screen below:



If you try to rebuild your data from Elliott 8.5 to 8.6 format, you should use the DDF2BTR utility since the PSQL (Zen) utility does not have that capability.  On the other hand, if you still want to retain your data in an existing format and just want to selectively change certain tables' page size to 16K, then you can use the PSQL Rebuild utility for better performance.

Regardless of whether you choose the PSQL (Zen) built-in Rebuild or the Elliott-provided DDF2BTR utility, you must perform the page size changes on the PSQL (Zen) database server console. Also, you should make sure the file is not being accessed by other users when you rebuild the page size.  So you will typically do this at nighttime or over a weekend. If you are running on a 24x7 basis, then you need to schedule maintenance downtime to perform the rebuild.

Author: JEG/EMK
Programs: DDF2BTR.CBL, DDF2BTRF.CBL

    • Related Articles

    • CPORDLS (Order Serial/Lot File) Btrieve Page Size

      Page Size For any Btrieve table, the page size can be from 512 bytes to 16,384 bytes as follows: File Format 7 - 9.0: 512 bytes - 8192 bytes File Format 9.5: 1024 bytes - 16384 bytes A page is the minimum I/O unit when Pervasive tries to retrieve or ...

    • An Example of DDF Files in DATA Folder that Can Confuse PSQL

      Release Date: 04/11/2024 Version: All Q - I tried to access Elliott Item master column ITEM_CASE_SIZE in Report Desk. In one of the Report Desk reports, I wanted to drag ITEM_CASE_SIZE to the report body. But I can't find that column under the ...

    • A Comparison of Elliott 7.5, 8.2, 8.5, 8.6 and 8.6 V2 DDF Files

      Release Date: 11/15/2022 Last Updated: 06/25/2024 Version: 7.5 & Up DDF Files are used to define the PSQL database schema. Generally speaking, we distribute five different types of DDF files for different versions of Elliott releases: 7.5 DDF: These ...

    • DDF Files in DATA Folder May Confuse PSQL

      Release Date: 04/27/2018 Q - We recently noticed a strange phenomenon where if I try to access Elliott column names in the ARCUSFIL, I find the Customer AR account is defined as CUS_AR_ACCT_NO and it is 24 digits. See sample screen below: I have an ...

    • Elliott 8.6 Database Convention & Report Desk/PowerSearch

      Release Date: 08/27/2021 Revision Date: 4/11/2022 Version: 8.6 Elliott 8.6 Database Naming Convention Elliott 8.6 now supports Report Desk and PowerSearch, features based on the PSQL relational database engine. Databases must be created for Report ...