Database file sizes
Before you design and create a database file, you need to know the maximum size allowed for the file.
IBM® i System Health Services can be used to
track important system limits. For example, you can monitor if you are approaching the maximum size
of an index. For more information, see System Health Services. 
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 288 records |
| Number of bytes in a file member | 1.7 terabytes2 |
| Number of bytes in an access path | 16 terabytes2 4 |
| 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 bytes3 |
| 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 This limit is the size of the internal data space, not the size of the entire object. This size will vary based on the field types of the file. 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.
3 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).
4 The maximum is 4 294 966 272 bytes if the access path
is created with a maximum size of four gigabytes (GB), ACCPTHSIZ(*MAX4GB). The maximum is two
terabytes (TB) if the index is an encoded vector index (EVI). When an access path is created with
ACCPTHSIZ(*MAX1TB) the maximum size is determined by the logical page size (PAGESIZE). When the
logical page size is 8K the maximum size is 4TB, when the logical page size is 16K the maximum size
is 8TB, and when the logical page size is 32-512K the maximum size is 16TB.
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.
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.