Like any other database, DB2 on IBM i has certain limits for maximum object size, maximum number of rows and many more attributes of database objects, such as tables and indexes. The limits are not always the same, some of the limits are being raised with newer releases of IBM i. The current SQL and database limits for IBM i 7.1 are documented here: SQL limits.
As you can see in the infocenter documentation, the maximum size of a non-partitioned table and the maximum size of a data partition are shown as 1.7 terabytes. However, we recently discovered another limit that should be proactively monitored on tables containing columns with variable length data, such as BLOB, CLOB, DBCLOB, VARCHAR and VARGRAPHIC columns. When the variable length data makes up a significant share of the overall table data, the maximum number of variable-length segments may be exhausted before the size limit of 1.7 terabytes has been reached. It should be very rare that one encounters this limit, however it can be checked to ensure that this limit, and hence an error SQL0904 type 6, is avoided.
On October 7, 2013 IBM has announced IBM i 7.1 technology refresh 7 (see IBM i 7.1 Technology Refresh 7 offers performance, usability, and integration enhancements). General availability for IBM i 7.1. TR7 is planned for November 15, 2013. With this technology refresh, the tracking of important system limits (catalog view QSYS2/SYSLIMITS) will be enhanced to show when tables reach the maximum number of variable-length segments. You can see more details here: Tracking important system limits. This function will also be made available for IBM i 6.1 at a later time.
We recommend installing IBM i 7.1 TR7 to be able to check these limits in an easy way.
If you wish to check these limits prior to installing IBM i 7.1 TR7, you can still find out if you have any tables which may be close to the limit by performing the following steps on tables that are close to or above 1 terabyte in size by following the manual steps below.
If the critical table contains a lot of deleted rows, you may be able to free up space by reorganizing the table as described in SAP Note 84081. Please note that the space occupied by variable-length data can only be freed completely during an offline reorganization. While the offline reorganization is running, you may temporarily need twice the space of the table available on disk.
If reorganizing the table does not help, you can either archive some of the data and delete it from the table, or you can use table partitioning in order to distribute the data over multiple partitions that can each have the limit of one non-partitioned table. Background information about table partitioning in BW systems can be found in SAP Note 815186. Partitioning of non-BW tables should only be implemented after consulting the SAP on IBM i porting team. If you are planning to do that, please open a ticket for component BC-DB-DB4.
The above information will also be made available through SAP Note 1930962.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
14 | |
11 | |
11 | |
11 | |
9 | |
9 | |
8 | |
6 | |
6 | |
6 |