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.
How do I know if my largest tables are affected?
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.
- Identify large tables in your SAP application server ABAP by using transaction DB02 or DBACockpit -> Space -> State on Disk. Select Object Details, leave the wildcard character '*' for the Table Selection and select the Update function. To see the largest tables on top of the list, select the column labeled as Size (KB) and sort the data in descending order.
- To map the SQL name(s) of the largest table(s) to the object name(s) at the operating system level, you can use interactive SQL (STRSQL or the System i Navigator) and execute the following query:
SELECT SYSTEM_TABLE_NAME
FROM QSYS2/SYSTABLES
WHERE TABLE_NAME = '<SQL name>'
AND TABLE_SCHEMA = 'R3<SID>DATA' - With the object name, enter the following command at a command line:
DMPSYSOBJ OBJ(<object>) CONTEXT(R3<SID>DATA)
This will create a spooled file named QPSRVDMP in your job. - Use command WRKSPLF and option 5 to display the spooled file QPSRVDMP. Search for a string *QDDS using function key F16 repeatedly until you see the following text:
...
OBJECT TYPE- DATA SPACE *QDDS
NAME- <object> <object> TYPE- 0B SUBTYPE- 90
CREATION- 09/30/05 15:03:57 SIZE- 0052060000
OWNER- <SID>OWNER TYPE- 08 SUBTYPE- 01
ATTRIBUTES- 0800 ADDRESS- 146D46E631 000000
DATA SPACE ATTRIBUTES-
...
Make sure that you see the 0B as type and 90 as subtype - the search will also find other places where the object type is *QDDSI instead of *QDDS. Note the value to the right of the text ADDRESS, or copy it into the clipboard (in the example: 146D46E631 000000). - Use the command STRSST to start the system service tools and sign on with a service tools user. Select the following menu items in the given order:
1. Start a service tool
4. Display/Alter/Dump
2. Dump to printer
1. Machine Interface (MI) object
11. Data space (0B)
2. Find by object address - Type or copy the address that was found in the previous step (in the example: 146D46E631 000000) and hit Enter. On the next screen ("MI Object Found") press Enter again.
- On the screen "Select Format", select option 2. Base structure. Specify a dump title (free text) and hit Enter. After that, you will see a message "Dump to printer successfully submitted" at the bottom of the screen. The created spooled file has the name QPCSMPRT.
- Leave the system service tools by using function key F3 multiple times.
- Use command WRKSPLF and option 5 to display the spooled file QPCSMPRT. Search for a string #AUXS using function key F16. You will see a text similar to the following:
...
LAST1 000000000000AAAA AUXT@ 146D46E631 000DF
#AUXS 00000040 #AXSL 00000057
#AXEN 00000000000C0785 AXLUB 0B8DF2796E 70F60
...
The hexadecimal number to the right of the text #AUXS indicated the number of variable-length segments currently in use, in the example 00000040. If this value approaches 0000FFFD, you are likely to hit the described table size limit.
What do I do if I approach or reach the limit?
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.
