DB2 Version 10.1 for Linux, UNIX, and Windows

SQL and XML limits

The tables in this topic describe SQL and XML limits. Adhering to the most restrictive case can help you to design application programs that are easily portable.

Table 1 lists limits in bytes. These limits are enforced after conversion from the application code page to the database code page when creating identifiers. The limits are also enforced after conversion from the database code page to the application code page when retrieving identifiers from the database. If, during either of these processes, the identifier length limit is exceeded, truncation occurs or an error is returned.

Character limits vary depending on the code page of the database and the code page of the application. For example, because the width of a UTF-8 character can range from 1 to 4 bytes, the character limit for an identifier in a Unicode table whose limit is 128 bytes will range from 32 to 128 characters, depending on which characters are used. If an attempt is made to create an identifier whose name is longer than the limit for this table after conversion to the database code page, an error is returned.

Applications that store identifier names must be able to handle the potentially increased size of identifiers after code page conversion has occurred. When identifiers are retrieved from the catalog, they are converted to the application code page. Conversion from the database code page to the application code page can result in an identifier becoming longer than the byte limit for the table. If a host variable declared by the application cannot store the entire identifier after code page conversion, it is truncated. If that is unacceptable, the host variable can be increased in size to be able to accept the entire identifier name.

The same rules apply to DB2®IBM® database utilities retrieving data and converting it to a user-specified code page. If a DB2an IBM database utility, such as export, is retrieving the data and forcing conversion to a user-specified code page (using the export CODEPAGE modifier or the DB2CODEPAGE registry variable), and the identifier expands beyond the limit that is documented in this table because of code page conversion, an error might be returned or the identifier might be truncated.

Table 1. Identifier Length Limits
Description	Maximum in Bytes
Alias name	128
Attribute name	128
Audit policy name	128
Authorization name (can only be single-byte characters)	128
Buffer pool name	18
Column name²	128
Constraint name	128
Correlation name	128
Cursor name	128
Data partition name	128
Data source column name	255
Data source index name	128
Data source name	128
Data source table name (remote-table-name)	128
Database partition group name	128
Database partition name	128
Event monitor name	128
External program name	128
Function mapping name	128
Group name	128
Host identifier¹	255
Identifier for a data source user (remote-authorization-name)	128
Identifier in an SQL procedure (condition name, for loop identifier, label, result set locator, statement name, variable name)	128
Index name	128
Index extension name	18
Index specification name	128
Label name	128
Namespace uniform resource identifier (URI)	1000
Nickname	128
Package name	128
Package version ID	64
Parameter name	128
Password to access a data source	32
Procedure name	128
Role name	128
Savepoint name	128
Schema name^2,3	128
Security label component name	128
Security label name	128
Security policy name	128
Sequence name	128
Server (database alias) name	8
Specific name	128
SQL condition name	128
SQL variable name	128
Statement name	128
Storage Group	128
Table name	128
Table space name	18
Transform group name	18
Trigger name	128
Trusted context name	128
Type mapping name	18
User-defined function name	128
User-defined method name	128
User-defined type name²	128
View name	128
Wrapper name	128
XML element name, attribute name, or prefix name	1000
XML schema location uniform resource identifier (URI)	1000
Note: Individual host language compilers might have a more restrictive limit on variable names. The SQLDA structure is limited to storing 30-byte column names, 18-byte user-defined type names, and 8-byte schema names for user-defined types. Because the SQLDA is used in the DESCRIBE statement, embedded SQL applications that use the DESCRIBE statement to retrieve column or user-defined type name information must conform to these limits. Schema names that are shorter than 8-bytes are padded with blanks and stored in the catalog as 8-byte names.

Table 2. Numeric Limits
Description	Limit
Smallest SMALLINT value	-32 768
Largest SMALLINT value	+32 767
Smallest INTEGER value	-2 147 483 648
Largest INTEGER value	+2 147 483 647
Smallest BIGINT value	-9 223 372 036 854 775 808
Largest BIGINT value	+9 223 372 036 854 775 807
Largest decimal precision	31
Maximum exponent (E_max) for REAL values	38
Smallest REAL value	-3.402E+38
Largest REAL value	+3.402E+38
Minimum exponent (E_min) for REAL values	-37
Smallest positive REAL value	+1.175E-37
Largest negative REAL value	-1.175E-37
Maximum exponent (E_max) for DOUBLE values	308
Smallest DOUBLE value	-1.79769E+308
Largest DOUBLE value	+1.79769E+308
Minimum exponent (E_min) for DOUBLE values	-307
Smallest positive DOUBLE value	+2.225E-307
Largest negative DOUBLE value	-2.225E-307
Maximum exponent (E_max) for DECFLOAT(16) values	384
Smallest DECFLOAT(16) value¹	-9.999999999999999E+384
Largest DECFLOAT(16) value	9.999999999999999E+384
Minimum exponent (E_min) for DECFLOAT(16) values	-383
Smallest positive DECFLOAT(16) value	1.000000000000000E-383
Largest negative DECFLOAT(16) value	-1.000000000000000E-383
Maximum exponent (E_max) for DECFLOAT(34) values	6144
Smallest DECFLOAT(34) value¹	-9.999999999999999999999999999999999E+6144
Largest DECFLOAT(34) value	9.999999999999999999999999999999999E+6144
Minimum exponent (E_min) for DECFLOAT(34) values	-6143
Smallest positive DECFLOAT(34) value	1.000000000000000000000000000000000E-6143
Largest negative DECFLOAT(34) value	-1.000000000000000000000000000000000E-6143
Note: These are the limits of normal decimal floating-point numbers. Valid decimal floating-point values include the special values NAN, -NAN, SNAN, -SNAN, INFINITY and -INFINITY. In addition, valid values include subnormal numbers. Subnormal numbers are nonzero numbers whose adjusted exponents are less than E_min. For a subnormal number, the minimum value of the exponent is E_min - (`precision`-1), called E_tiny, where `precision` is the working precision (16 or 34). That is, subnormal numbers extend the range of numbers close to zero by 15 or 33 orders of magnitude for DECFLOAT(16) or DECFLOAT(34), respectively. Subnormal numbers are different from normal numbers because the maximum number of digits for a subnormal number is less than the working precision (16 or 34). Decimal floating-point cannot represent the subnormal numbers with the same accuracy as it can represent normal numbers. The smallest positive subnormal number for DECFLOAT(34) is 1x10^-6176, which contains only one digit, whereas the smallest positive normal number for DECFLOAT(34) is 1.000000000000000000000000000000000x10^-6143, which contains 34 digits. The smallest positive subnormal number for DECFLOAT(16) is 1x10^-398.

Table 3. String Limits
Description	Limit
Maximum length of CHAR (in bytes or OCTETS)	254
Maximum length of CHAR (in CODEUNITS32)	63
Maximum length of VARCHAR (in bytes or OCTETS)²	32 672
Maximum length of VARCHAR (in CODEUNITS32)	8 168
Maximum length of LONG VARCHAR (in bytes)¹	32 700
Maximum length of CLOB (in bytes or OCTETS))	2 147 483 647
Maximum length of CLOB (in CODEUNITS32)	536 870 911
Maximum length of serialized XML (in bytes)	2 147 483 647
Maximum length of GRAPHIC (in double-byte characters or CODEUNITS16)	127
Maximum length of GRAPHIC (in CODEUNITS32)	63
Maximum length of VARGRAPHIC (in double-byte characters or CODEUNITS16)²	16 336
Maximum length of VARGRAPHIC (in CODEUNITS32)	8 168
Maximum length of LONG VARGRAPHIC (in double-byte characters)¹	16 350
Maximum length of DBCLOB (in double-byte characters or CODEUNITS16)	1 073 741 823
Maximum length of DBCLOB (in CODEUNITS32)	536 870 911
Maximum length of BLOB (in bytes)	2 147 483 647
Maximum length of character constant	32 672
Maximum length of graphic constant	16 336
Maximum length of concatenated character string	2 147 483 647
Maximum length of concatenated graphic string	1 073 741 823
Maximum length of concatenated binary string	2 147 483 647
Maximum number of hexadecimal constant digits	32 672
Largest instance of a structured type column object at run time (in gigabytes)	1
Maximum size of a catalog comment (in bytes)	254
Note: The LONG VARCHAR and LONG VARGRAPHIC data types are deprecated and might be removed in a future release. For page size-specific string limits for tables, see Table 4.

Table 4. Page Size-specific String Limits for Column-organized Tables
Description	4K page size limit	8K page size limit	16K page size limit	32K page size limit
Maximum length of VARCHAR (in bytes)	3920	8016	16 208	32 592
Maximum length of VARGRAPHIC (in bytes)	1960	4008	8104	16 296
Note: A table has an overhead of 176 bytes per page.

Table 5. XML Limits
Description	Limit
Maximum depth of an XML document (in levels)	125
Maximum size of an XML schema document (in bytes)	31 457 280

Table 6. Datetime Limits
Description	Limit
Smallest DATE value	0001-01-01
Largest DATE value	9999-12-31
Smallest TIME value	00:00:00
Largest TIME value	24:00:00
Smallest TIMESTAMP value	0001-01-01-00.00.00.000000000000
Largest TIMESTAMP value	9999-12-31-24.00.00.000000000000
Smallest timestamp precision	0
Largest timestamp precision	12

Table 7. Database Manager Limits
Category	Description	Limit
Applications	Maximum number of host variable declarations in a precompiled program³	storage
	Maximum length of a host variable value (in bytes)	2 147 483 647
	Maximum number of declared cursors in a program	storage
	Maximum number of rows changed in a unit of work	storage
	Maximum number of cursors opened at one time	storage
	Maximum number of connections per process within a database client	512
	Maximum number of simultaneously opened LOB locators in a transaction	4 194 304
	Maximum size of an SQLDA (in bytes)	storage
	Maximum number of prepared statements	storage
Buffer Pools	Maximum NPAGES in a buffer pool for 32-bit releases	1 048 576
	Maximum NPAGES in a buffer pool for 64-bit releases	2 147 483 647
	Maximum total size of all buffer pool slots (4K)	2 147 483 646
Concurrency	Maximum number of concurrent users of a server⁴	64 000
	Maximum number of concurrent users per instance	64 000
	Maximum number of concurrent applications per database	60 000
	Maximum number of databases per instance concurrently in use	256
Constraints	Maximum number of constraints on a table	storage
	Maximum number of columns in a UNIQUE constraint (supported through a UNIQUE index)	64
	Maximum combined length of columns in a UNIQUE constraint (supported through a UNIQUE index, in bytes)⁸	8192
	Maximum number of referencing columns in a foreign key	64
	Maximum combined length of referencing columns in a foreign key (in bytes)⁸	8192
	Maximum length of a check constraint specification (in bytes)	65 535
Databases	Maximum database partition number	999
Databases	Maximum members in a DB2 pureScale® environment	128
Databases	Maximum database partition number	999
Indexes	Maximum number of indexes on a table	32 767 or storage
	Maximum number of columns in an index key	64
	Maximum length of an index key including all overhead⁶ ⁸	indexpagesize/4
	Maximum length of a variable index key part (in bytes)⁷	1022 or storage
	Maximum size of an index per database partition in an SMS table space (in terabytes)⁶	64
	Maximum size of an index per database partition in a regular DMS table space (in gigabytes)⁶	512
	Maximum size of an index per database partition in a large DMS table space (in terabytes)⁶	64
	Maximum length of a variable index key part for an index over XML data (in bytes)⁷	pagesize/4 - 207
Log records	Maximum Log Sequence Number	0xFFFF FFFF FFFF FFFF
Monitoring	Maximum number of simultaneously active event monitors	128
Monitoring	In a partitioned database environment, maximum number of simultaneously active GLOBAL event monitors	32
Routines	Maximum number of parameters in a procedure with LANGUAGE SQL	32 767
	Maximum number of parameters in an external procedure with PROGRAM TYPE MAIN	32 767
	Maximum number of parameters in an external procedure with PROGRAM TYPE SUB	90
	Maximum number of parameters in a cursor value constructor	32 767
	Maximum number of parameters in a user-defined function	90
	Maximum number of nested levels for routines	64
	Maximum number of schemas in the SQL path	64
	Maximum length of the SQL path (in bytes)	2048
Maximum total size of local variables declared inside a routine (in gigabytes)³	4
Security	Maximum number of elements in a security label component of type set or tree	64
	Maximum number of elements in a security label component of type array	65 535
	Maximum number of security label components in a security policy	16
SQL	Maximum total length of an SQL statement (in bytes)	2 097 152
	Maximum number of tables referenced in an SQL statement or a view	storage
	Maximum number of host variable references in an SQL statement	32 767
	Maximum number of constants in a statement	storage
	Maximum number of elements in a select list⁶	1012
	Maximum number of predicates in a WHERE or HAVING clause	storage
	Maximum number of columns in a GROUP BY clause⁶	1012
	Maximum total length of columns in a GROUP BY clause (in bytes)⁶	32 677
	Maximum number of columns in an ORDER BY clause⁶	1012
	Maximum total length of columns in an ORDER BY clause (in bytes)⁶	32 677
	Maximum level of subquery nesting	storage
	Maximum number of subqueries in a single statement	storage
	Maximum number of values in an insert operation⁶	1012
Storage Groups	Maximum number of storage groups in a database	256
	Maximum number of storage paths in a storage group	128
	Maximum length of a storage path (in bytes)	175
Tables and Views	Maximum number of columns in a table ⁶	1012
	Maximum number of columns in a view¹	5000
	Maximum number of columns in a data source table or view that is referenced by a nickname	5000
	Maximum number of columns in a distribution key⁵	500
	Maximum length of a row including all overhead² ⁶ ⁹	32 677
	Maximum number of rows in a non-partitioned table, per database partition	128 x 10¹⁰
	Maximum number of rows in a data partition, per database partition	128 x 10¹⁰
	Maximum size of a table per database partition in a regular table space (in gigabytes)³ ⁶	512
	Maximum size of a table per database partition in a large DMS table space (in terabytes)⁶	64
	Maximum number of data partitions for a single table	32 767
	Maximum number of table partitioning columns	16
	Maximum number of fields in a user-defined row data type	1012
Table Spaces	Maximum size of a LOB object per table or per table partition (in terabytes)	4
	Maximum size of an LF object per table or per table partition (in terabytes)	2
	Maximum number of table spaces in a database	32 768
	Maximum number of tables in an SMS table space	65 532
	Maximum size of a regular DMS table space (in gigabytes) ³ ⁶	512
	Maximum size of a large DMS table space (in terabytes) ³ ⁶	64
	Maximum size of a temporary DMS table space (in terabytes) ³ ⁶	64
	Maximum number of table objects in a DMS table space	See Table 8
Triggers	Maximum runtime depth of cascading triggers	16
User-defined Types	Maximum number of attributes in a structured type	4082
Workload Manager	Maximum number of user-defined service superclasses per database	64
Workload Manager	Maximum number of user-defined service subclasses per service superclass	61
Note: This maximum can be achieved using a join in the CREATE VIEW statement. Selecting from such a view is subject to the limit of most elements in a select list. The actual data for BLOB, CLOB, LONG VARCHAR, DBCLOB, and LONG VARGRAPHIC columns is not included in this count. However, information about the location of that data does take up some space in the row. The numbers shown are architectural limits and approximations. The practical limits may be less. The actual value is controlled by the max_connections and max_coordagents database manager configuration parameters. This is an architectural limit. The limit on the most columns in an index key should be used as the practical limit. For page size-specific values, see Table 8. This is limited only by the longest index key, including all overhead (in bytes). As the number of index key parts increases, the maximum length of each key part decreases. The maximum can be less, depending on index options. If the extended_row_sz database configuration parameter is set to ENABLE and there are VARCHAR or VARGRAPHIC columns in the table, the maximum row size is 1 048 319 bytes.

Table 8. Database Manager Page Size-specific Limits
Description	4K page size limit	8K page size limit	16K page size limit	32K page size limit
Maximum number of table objects in a DMS table space¹	51 971² 53 212³	53 299	53 747	54 264
Maximum number of columns in a table	500	1012	1012	1012
Maximum number of columns in a table	1012	1012	1012	1012
Maximum length of a row in a table, including all overhead	4005	8101	16 293	32 677
Maximum length of a row in a table, including all overhead	32 677	32 677	32 677	32 677
Maximum size of a table per database partition in a regular table space (in gigabytes)	64	128	256	512
Maximum size of a table per database partition in a large DMS table space (in terabytes)	8	16	32	64
Maximum length of an index key including all overhead (in bytes)	1024	2048	4096	8192
Maximum size of an index per database partition in an SMS table space (in terabytes)	8	16	32	64
Maximum size of an index per database partition in a regular DMS table space (in gigabytes)	64	128	256	512
Maximum size of an index per database partition in a large DMS table space (in terabytes)	8	16	32	64
Maximum size of a regular DMS table space (in gigabytes)	64	128	256	512
Maximum size of a large DMS table space (in terabytes)	8	16	32	64
Maximum size of a temporary DMS table space (in terabytes)	8	16	32	64
Maximum number of elements in a select list	500⁴	1012	1012	1012
Maximum number of columns in a GROUP BY clause	500	1012	1012	1012
Maximum total length of columns in a GROUP BY clause (in bytes)	4005	8101	16 293	32 677
Maximum number of columns in an ORDER BY clause	500	1012	1012	1012
Maximum total length of columns in an ORDER BY clause (in bytes)	4005	8101	16 293	32 677
Maximum number of values in an insert operation	500	1012	1012	1012
Maximum number of SET clauses in a single update operation	500	1012	1012	1012
Maximum records per page for a regular table space	251	253	254	253
Maximum records per page for a large table space	287	580	1165	2335
Note: Table objects include table data, indexes, LONG VARCHAR columns, LONG VARGRAPHIC columns, and LOB columns. Table objects that are in the same table space as the table data do not count extra toward the limit. However, each table object that is in a different table space than the table data does contribute one toward the limit for each table object type per table in the table space in which the table object resides. When extent size is 2 pages. When extent size is any size other than 2 pages. In cases where the only system temporary table space is 4KB and the data overflows to the sort buffer, an error is generated. If the result set can fit into memory, there is no error.