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 1 - 4 bytes, the character limit for an identifier in a Unicode table whose limit is 128 bytes must range 32 - 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 occurs. 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 that is 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® utilities retrieving data and converting it to a user-specified code page. If a Db2 utility, such as export, is retrieving the data and forcing conversion to a user-specified code page (by 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 be single-byte characters only) 128
Buffer pool name 18
Column name2 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 identifier1 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 name2,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 name2 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:
  1. Individual host language compilers might have a more restrictive limit on variable names.
  2. 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.
  3. 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 (Emax) for REAL values 38
Smallest REAL value -3.402E+38
Largest REAL value +3.402E+38
Minimum exponent (Emin) for REAL values -37
Smallest positive REAL value +1.175E-37
Largest negative REAL value -1.175E-37
Maximum exponent (Emax) for DOUBLE values 308
Smallest DOUBLE value -1.79769E+308
Largest DOUBLE value +1.79769E+308
Minimum exponent (Emin) for DOUBLE values -307
Smallest positive DOUBLE value +2.225E-307
Largest negative DOUBLE value -2.225E-307
Maximum exponent (Emax) for DECFLOAT(16) values 384
Smallest DECFLOAT(16) value1 -9.999999999999999E+384
Largest DECFLOAT(16) value 9.999999999999999E+384
Minimum exponent (Emin) for DECFLOAT(16) values -383
Smallest positive DECFLOAT(16) value 1.000000000000000E-383
Largest negative DECFLOAT(16) value -1.000000000000000E-383
Maximum exponent (Emax) for DECFLOAT(34) values 6144
Smallest DECFLOAT(34) value1 -9.999999999999999999999999999999999E+6144
Largest DECFLOAT(34) value 9.999999999999999999999999999999999E+6144
Minimum exponent (Emin) for DECFLOAT(34) values -6143
Smallest positive DECFLOAT(34) value 1.000000000000000000000000000000000E-6143
Largest negative DECFLOAT(34) value -1.000000000000000000000000000000000E-6143
Note:
  1. 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 Emin. For a subnormal number, the minimum value of the exponent is Emin - (precision-1), called Etiny, 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) 255
Maximum length of CHAR (in CODEUNITS32) 63
Maximum length of VARCHAR (in bytes or OCTETS)2 32,672
Maximum length of VARCHAR (in CODEUNITS32)2 8168
Maximum length of LONG VARCHAR (in bytes)1 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)2 16,336
Maximum length of VARGRAPHIC (in CODEUNITS32) 8168
Maximum length of LONG VARGRAPHIC (in double-byte characters)1 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 BINARY (in bytes) 255
Maximum length of VARBINARY (in bytes)2 32,672
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:
  1. The LONG VARCHAR and LONG VARGRAPHIC data types are deprecated and might be removed in a future release.
  2. For page size-specific string limits for column-organized 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 VARCHAR (in CODEUNITS32) 980 2004 4052 8148
Maximum length of VARGRAPHIC (in bytes) 1960 4008 8104 16,296
Maximum length of VARBINARY (in bytes) 3920 8016 16,208 32,592
Note: A column-organized table has an overhead of 176 bytes per page.
Table 5. Page Size-specific String Limits for Column-organized Tables. These limits are only applicable when you increase the column length by using ALTER TABLE
Description 4K page size limit 8K page size limit 16K page size limit 32K page size limit
Maximum length of VARCHAR (in bytes) 3888 7984 16,176 32,560
Maximum length of VARGRAPHIC (in bytes) 1944 3992 8088 16,280
Table 6. 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 7. 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 8. Database Manager Limits
Category Description Limit
Applications Maximum number of host variable declarations in a precompiled program3 Storage. This number can go down with future versions. It is recommended to stay under 10000.
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 that are changed in a unit of work storage
Maximum number of cursors that are 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 server4 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)8 8192
Maximum number of referencing columns in a foreign key 64
Maximum combined length of referencing columns in a foreign key (in bytes)8 8192
Maximum length of a check constraint specification (in bytes) 65,535
Databases Maximum database partition number 999
Maximum members in a Db2 pureScale® environment 128
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 overhead6 8 indexpagesize/4
Maximum length of a variable index key part (in bytes)7 1022 or storage
Maximum size of an index per database partition in an SMS table space (in terabytes)6 64
Maximum size of an index per database partition in a regular DMS table space (in gigabytes)6 512
Maximum size of an index per database partition in a large DMS table space (in terabytes)6 64
Maximum length of a variable index key part for an index over XML data (in bytes)7 pagesize/4 - 207
Log records Maximum Log Sequence Number 0xFFFF FFFF FFFF FFFF
Monitoring Maximum number of simultaneously active event monitors 128
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
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 that are referenced in an SQL statement or a view storage
Maximum number of host variable references in a dynamic SQL statement 32,767
Maximum number of constants in a statement storage
Maximum number of elements in a select list6 1012
Maximum number of predicates in a WHERE or HAVING clause storage
Maximum number of columns in a GROUP BY clause6 1012
Maximum total length of columns in a GROUP BY clause (in bytes)6 32,677
Maximum number of columns in an ORDER BY clause6 1012
Maximum total length of columns in an ORDER BY clause (in bytes)6 32,677
Maximum level of subquery nesting storage
Maximum number of subqueries in a single statement storage
Maximum number of values in an insert operation6 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 6 10 1012
Maximum number of columns in a view1 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 key5 500
Maximum length of a row, including all overhead2 6 9 32,677
Maximum number of rows in a non-partitioned table, per database partition 128 x 1010
Maximum number of rows in a data partition, per database partition 128 x 1010
Maximum size of a table per database partition in a regular table space (in gigabytes)3 6 512
Maximum size of a table per database partition in a large DMS table space (in terabytes)6 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) 3 6 512
Maximum size of a large DMS table space (in terabytes) 3 6 64
Maximum size of a temporary DMS table space (in terabytes) 3 6 64
Maximum number of table objects in a DMS table space See Table 9
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
Maximum number of user-defined service subclasses per service superclass 61
Note:
  1. This maximum can be achieved by 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.
  2. The actual data for BLOB, CLOB, LONG VARCHAR, DBCLOB, and LONG VARGRAPHIC columns are not included in this count. However, information about the location of that data does take up some space in the row.
  3. The numbers that are shown are architectural limits and approximations. The practical limits might be less.
  4. The actual value is controlled by the max_connections and max_coordagents database manager configuration parameters.
  5. This is an architectural limit. The limit on the most columns in an index key should be used as the practical limit.
  6. For page size-specific values, see Table 9.
  7. 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.
  8. The maximum can be less, depending on index options.
  9. If the extended_row_sz database configuration parameter is set to ENABLE and there are VARCHAR, VARBINARY, or VARGRAPHIC columns in the table, the maximum row size is 1,048,319 bytes, which includes all overhead.
  10. Must account for columns that are generated internally by the database manager. The RANDOM_DISTRIBUTION_KEY is an example: it is created for random distribution tables that use the random by generation method.
Table 9. 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 space1 51,9712
53,2123		 53,299 53,747 54,264
Maximum number of columns in a row-organized table 500 1012 1012 1012
Maximum number of columns in a column-organized table 1012 1012 1012 1012
Maximum length of a row in a row-organized table, including all overhead 4005 8101 16,293 32,677
Maximum length of a row in a column-organized table, including all overhead 1,048,319 1,048,319 1,048,319 1,048,319
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 per database partition (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 5004 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:
  1. 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.
  2. When extent size is two pages.
  3. When extent size is any size other than two pages.
  4. 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.