Database file sizes
Before you design and create a database file, you need to know the maximum size allowed for the file.
The following table lists the maximum values for database files.
| Description | Maximum value |
|---|---|
| Number of bytes in a record | 32 766 bytes |
| Number of fields in a record format | 8 000 fields |
| Number of key fields in a file | 120 fields |
| Size of key for physical and logical files | 32 768 characters1 |
| Size of key for ORDER BY (SQL) and KEYFLD (OPNQRYF) | 10 000 bytes |
| Number of records contained in a file member | 4 294 967 294 records2 |
| Number of bytes in a file member | 1 869 162 846 624 bytes3 |
| Number of bytes in an access path | 1 099 511 627 776 bytes3 5 |
| Number of keyed logical files built over a physical file member | 3686 files |
| Number of physical file members in a logical file member | 32 members |
| Number of members that can be joined | 256 members |
| Size of a character or DBCS field | 32 766 bytes4 |
| Size of a zoned decimal or packed decimal field | 63 digits |
| Maximum number of distinct database files that can be in use at one time | ~500 000 |
| Maximum number of members in a physical or logical file | 32 767 |
| Maximum number of constraints per physical file | 5000 constraints |
| Maximum number of triggers per physical file | 300 triggers |
| Maximum number of recursive insert and update trigger calls | 200 |
1 When a first-changed-first-out (FCFO) access path is specified for the file, the maximum value for the size of the key for physical and logical files is 32 763 for ACCPTHSIZ(*MAX1TB) and 1995 characters for ACCPTHSIZ(*MAX4GB).
2 For files with keyed sequence access paths, the maximum number of records in a member varies and can be estimated using the following formulas.
2,867,200,000
10 + (.8 x key length)
725,680,000,000
12 + (.8 x key length)
These are estimated values. The actual maximum number of records can vary significantly.
3 Both the number of bytes in a file member and the number of bytes in an access path must be looked at when message CPF5272 is sent indicating that the maximum system object size has been reached.
4 The maximum size of a variable-length character or DBCS field is 32 740 bytes. DBCS-graphic field lengths are expressed in terms of characters; therefore, the maximums are 16 383 characters (fixed length) and 16 370 characters (variable length).
5 The maximum is 4 294 966 272 bytes if the access path is created with a maximum size of 4 gigabytes (GB), ACCPTHSIZ(*MAX4GB).
These are maximum values. There are situations where the actual limit you experience will be less than the stated maximum. For example, certain high-level languages can have more restrictive limits than those described above.
Keep in mind that performance can suffer as you approach some of these maximums. For example, the more logical files you have built over a physical file, the greater the chance that system performance can suffer (if you are frequently changing data in the physical file that causes a change in many logical file access paths).
Normally, an IBM® i database file can grow until it reaches the maximum size allowed on the operating system. The operating system normally does not allocate all the file space at once. Rather, it occasionally allocates additional space as the file grows larger. This method of automatic storage allocation provides the best combination of good performance and effective auxiliary storage space management.
If you want to control the size of the file, the storage allocation, and whether the file should be connected to auxiliary storage, you can use the SIZE, ALLOCATE, and CONTIG parameters on the Create Physical File (CRTPF) and the Create Source Physical File (CRTSRCPF) commands.
- For a physical file (excluding the access path) that does not
contain null-capable fields:
Disk size = (number of valid and deleted records + 1) x
The size of the physical file depends on the SIZE and ALLOCATE parameters on the CRTPF and CRTSRCPF commands. If you specify ALLOCATE(*YES), the initial allocation and increment size on the SIZE keyword must be used instead of the number of records.
(record length + 1) + 20480 x (number of members) + 8192
- For a physical file (excluding the access path) that contains
null-capable fields:
Disk size = (number of valid and deleted records + 1) x
The size of the physical file depends on the SIZE and ALLOCATE parameters on the CRTPF and CRTSRCPF commands. If you specify ALLOCATE(*YES), the initial allocation and increment size on the SIZE keyword must be used instead of the number of records.
(record length + 1) + 20480 x (number of members) +
8192 + ((number of fields in format ÷ 8) rounded up) x
(number of valid and deleted records + 1) - For a logical file (excluding the access path):
Disk size = (12288) x (number of members) + 8192
- For a keyed sequence access path the generalized equation for
index size, per member, is:
let a = (LimbPageUtilization - LogicalPageHeaderSize) *
(LogicalPageHeaderSize - LeafPageUtilization - 2 * NodeSize)
let b = NumKeys * (TerminalTextPerKey + 2 * NodeSize) *
(LimbPageUtilization - LogicalPageHeaderSize + 2 * NodeSize)
+ CommonTextPerKey * [ LimbPageUtilization + LeafPageUtilization
- 2 * (LogicalPageHeaderSize - NodeSize) ]
- 2 * NodeSize * (LeafPageUtilization - LogicalPageHeaderSize
+ 2 * NodeSize)
let c = CommonTextPerKey * [ 2 * NodeSize - CommonTextPerKey
- NumKeys * (TerminalTextPerKey + 2 * NodeSize) ]
then NumberLogicalPages = ceil( [ -b - sqrt(b ** 2 - 4 * a * c) ]
/ (2 * a))
and TotalIndexSize = NumberLogicalPages * LogicalPageSize
This equation is used for both three and four byte indexes by changing the set of constants in the equation as follows.Constant Three-byte index Four-byte index NodeSize 3 4 LogicalPageHeaderSize 16 64 LimbPageUtilization .75 * LogicalPageSize .75 * LogicalPageSize LeafPageUtilization .75 * LogicalPageSize .80 * LogicalPageSize The remaining constants, CommonTextPerKey and TerminalTextPerKey, are probably best estimated by using the following formulas:
CommonTextPerKey = [ min(max(NumKeys - 256,0),256)
+ min(max(NumKeys - 256 * 256,0),256 * 256)
+ min(max(NumKeys - 256 * 256 * 256,0),
256 * 256 * 256)
+ min(max(NumKeys - 256 * 256 * 256 * 256,0),
256 * 256 * 256 * 256) ]
* (NodeSize + 1) / NumKeys
TerminalTextPerKey = KeySizeInBytes - CommonTextPerKey
This should reduce everything needed to calculate the index size to the type of index (that is, 3 or 4 byte), the total key size, and the number of keys. The estimate should be greater than the actual index size because the common text estimate is minimal.
Given this generalized equation for index size, the LogicalPageSize is as follows.Table 1. LogicalPageSize values Key Length *MAX4GB (3-byte) LogicalPageSize *MAX1TB (4-byte) LogicalPageSize 1 - 500 4096 bytes 8192 bytes 501 - 1000 8192 bytes 16 384 bytes 1001 - 2000 16 384 bytes 32 768 bytes 2001 - 10 000 N/A 65 536 bytes 10 001 - 18 000 N/A 131 072 bytes 18 001 - 26 000 N/A 262 144 bytes 26 001 - 32 768 N/A 524 288 bytes The LogicalPageSizes in Table 1 generate the following LimbPageUtilizations.Key Length *MAX4GB (3-byte) LimbPageUtilization *MAX1TB (4-byte) LimbPageUtilization 1 - 500 3072 bytes 6144 bytes 501 - 1000 6144 bytes 12 288 bytes 1001 - 2000 12 288 bytes 24 576 bytes 2001 - 10 000 N/A 49 152 bytes 10 001 - 18 000 N/A 98 304 bytes 18 001 - 26 000 N/A 196 608 bytes 26 001 - 32 768 N/A 393 216 bytes The LogicalPageSizes in Table 1 generate the following LeafPageUtilizations.Key Length *MAX4GB (3-byte) LeafPageUtilization *MAX1TB (4-byte) LeafPageUtilization 1 - 500 3072 bytes 6554 bytes 501 - 1000 6144 bytes 13 107 bytes 1001 - 2000 12 288 bytes 26 214 bytes 2001 - 10 000 N/A 52 428 bytes 10 001 - 18 000 N/A 104 857 bytes 18 001 - 26 000 N/A 209 715 bytes 26 001 - 32 768 N/A 419 430 bytes Then to simplify the generalized equation for index size, let:
CommonTextPerKey = 0
which would cause:
TerminalTextPerKey = KeySizeInBytes
b = NumKeys * (KeySizeInBytes + 2 * NodeSize) *
(LimbPageUtilization - LogicalPageHeaderSize + 2 * NodeSize)
- 2 * NodeSize * (LeafPageUtilization - LogicalPageHeaderSize
+ 2 * NodeSize)
c = 0
NumberLogicalPages = ceil( [ -b - sqrt(b ** 2 ) ]
/ (2 * a))
= ceil[ (-2 * b) / (2 * a) ]
= ceil[ -b/a ]