| You can use this appendix as a handy reference for the various physical and structural limitations to which DB2 must conform. There are two columns , the first showing the V7 limit, the second showing the V8 limit. If the limit did not change between V7 and V8, the V8 column will be blank. | Item | V7 Limit | V8 Limit | | STOGROUP name | 8 bytes | 128 bytes | | Volumes per STOGROUP | 133 | | | Database name | 8 bytes | | | Maximum number of databases | 65,217 | | | Authorization ID | 8 bytes | 128 bytes | | Tablespace name | 8 bytes | | | Maximum segment size | 64 pages | | | Largest Partitioned Table Space | 16 terabytes | 128 terabytes | | Largest Segmented Table Space | 64 gigabytes | | | Largest Simple Table Space | 64 gigabytes | | | Table name | 18 bytes | 128 bytes | | View name | 18 bytes | 128 bytes | | Alias name | 18 bytes | 128 bytes | | Synonym name | 18 bytes | 128 bytes | | Column name | 18 bytes | 128 bytes | | Referential constraint name | 8 bytes | 128 bytes | | Check constraint name | 18 bytes | 128 bytes | | Maximum length of the check constraint text | 3,800 bytes | | | Cursor name (except for DECLARE CURSOR WITH RETURN [*] ) | 18 bytes | 128 bytes | | Host identifier | 64 bytes | | | Location name | 16 bytes | | | Number of base tables per view | 225 | | | Maximum number of columns in a table (or view) | 750 [**] | | | Index name (8 bytes recommended [***] ) | 18 bytes | 128 bytes | | Columns per index | 64 | | | Index key size (number of nullable columns [****] ) | 255 bytes | 2,000 bytes | | Plan name | 8 bytes | | | Package name | 8 bytes | | | Trigger package name | 8 bytes | 128 bytes | | Collection name | 18 bytes | 128 bytes | | Package Version name | 64 bytes | | | DBRM name | 8 bytes | | | Schema name | 8 bytes | 128 bytes | | Stored Procedure name | 18 bytes | 128 bytes | | User -defined function name | 18 bytes | 128 bytes | | Trigger name | 8 bytes | 128 bytes | | Maximum length of CHAR | 255 bytes | | | Largest VARCHAR | | | (4KB pages) | 4,046 bytes | | (8KB pages) | 8,128 bytes | | (16KB pages) | 16,320 bytes | | (32KB pages) | 32,704 bytes | | | Maximum length of GRAPHIC | 127 DBCS characters | | | Largest VARGRAPHIC | | | (4KB pages) | 4,046 bytes | | (8KB pages) | 8,128 bytes | | (16KB pages) | 16,320 bytes | | (32KB pages) | 32,704 bytes | | | Maximum length of BLOB | 2,147,483,647 bytes | | | Maximum length of CLOB | 2,147,483,647 bytes | | | Maximum length of DBCLOB | 1,073,741,824 DBCS characters | | | Largest SMALLINT | 32,767 | | | Smallest SMALLINT | “32,768 | | | Largest INTEGER | 2,147,483,647 | | | Smallest INTEGER | “2,147,483,648 | | | Largest DECIMAL | 10 31 “1 | | | Smallest DECIMAL | 1 “10 31 | | | Largest FLOAT | 7.2 x 10 75 | | | Smallest FLOAT | “7.2 x 10 75 | | | Smallest positive FLOAT | 5.4 x 10 “79 | | | Largest negative FLOAT | “5.4 x 10 79 | | | Smallest DATE | 0001-01-01 | | | Largest DATE | 9999-12-31 | | | Smallest TIME | 00.00.00 | | | Largest TIME | 24.00.00 | | | Smallest TIMESTAMP | 0001-01-01-00.00.00.000000 | | | Largest TIMESTAMP | 9999-12-31-24.00.00.000000 | | | Physical Storage | | | | SMALLINT | 2 bytes | | | INTEGER | 4 bytes | | | REAL | 4 bytes | | | DOUBLE PRECISION | 8 bytes | | | DECIMAL( p,m ) | ( TRUNCATE (p/2)+1) bytes | | | CHAR( n ) | n bytes | | | VARCHAR( n ) | n + 2 bytes | | | LONG VARCHAR | size of tablespace page | | | GRAPHIC( n ) | 2 * n | | | VARGRAPHIC( n ) | (2 * n ) + 2) bytes | | | LONG VARGRAPHIC | Size of tablespace page | | | DATE | 4 bytes | | | TIME | 3 bytes | | | TIMESTAMP | 10 bytes | | | Maximum LOB data set size | 64GB | | | Row length | | | (4KB pages) | 4,056 bytes | | (8KB pages) | 8,138 bytes | | (16KB pages) | 16,330 bytes | | (32KB pages) | 32,714 bytes | | | Row length (with EDITPROC ) | | | (4KB pages) | 4,046 bytes | | (8KB pages) | 8,128 bytes | | (16KB pages) | 16,320 bytes | | (32KB pages) | 32,704 bytes | | | Maximum number of rows per page | | | (user tables) | 255 | | (DB2 Catalog & Directory) | 127 | | | Maximum number of tables in a FROM clause | 15 | 225 | | Maximum number of tables per SELECT / INSERT / UPDATE / DELETE | 225 | | | Maximum number of subqueries in an SQL statement | 14 | | | Maximum number of triggers, stored procedures, and UDFs referenced by a single SQL statement | 16 nested levels | | | Maximum length of SQL path | 254 bytes | | | Largest SQL statement | 32,765 bytes | 2,097,152 bytes | | Columns [*****] per SELECT | 750 | | | Largest string literal | 255 bytes | 32,704 bytes | | SQL correlation ID | 18 bytes | 128 bytes | | Predicates per WHERE clause | 750 | | | Predicates per HAVING clause | 750 | | | Length of columns in ORDER BY | 4,000 | | | Length of columns in GROUP BY | 4,000 | | | Maximum length of host and indicator variables pointed to in SQLDA | 32,767 bytes 2 gigabytes “1 for a LOB [******] | | | Maximum size of a single stored procedure parm | 32,765 bytes 2 gigabytes “1 for a LOB [******] | | | Concurrent users | 2,000 | | | Open data sets | 32,727 | 100,000 | | Largest active log data set | 2GB | | | Largest archive log data set | 2GB | | | Maximum active log copies | 2 | | | Maximum archive log copies | 2 | | | Maximum active log data sets | 31 | 93 | | Maximum archive log volumes | 1,000 | 10,000 | | Maximum DBRM entry size | 131,072 bytes | |
[*] The maximum size of a cursor declared WITH RETURN is 30 bytes. [**] If the table is a dependent, it can contain a maximum of 749 columns. The value (749 or 750) depends on the complexity of the CREATE VIEW statement. [***] If the index name is longer than 8 bytes, DB2 derives an index space name using the index name. An index space name must be unique in the given database. The index space name that DB2 generates for index names of nine characters or more may be hard to track when you're performing DASD management and object monitoring. It also is helpful to have the index space names be the same when comparing files from different environments; for example, comparing QA to production. [****] The maximum length of the key for a partitioning index remains 255 even for DB2 V8. For V7 and V8, both partitioning and nonpartitioning indexes, you must subtract 1 for each nullable column in the index to determine the total maximum length of the columns that can be assigned to the index. For V8 padded indexes, you must also subtract 2 times the number of varying-length columns in the key. [*****] The maximum is for all items in the SELECT list, not just columns. For example, expressions and constants can be included in the SELECT list. [******] The maximum number of bytes passed for LOB data is subject to the capabilities of the application programming language and environment being used. |
