SQL limits

The following tables describe certain SQL and database limits imposed by the DB2® for i database manager.

Note:

System storage limits may preclude the limits specified here. For example, see Maximum row sizes.
A limit of storage means that the limit is dependent on the amount of storage available.
A limit of statement means that the limit is dependent on the limit for the maximum length of a statement.

Table 1. Identifier Length Limits
Identifier Limits	DB2 for i Limit
Longest authorization name	10¹
Longest correlation name	128
Longest cursor name	128
Longest descriptor name	128
Longest external program name (string form)	279²
Longest external program name (unqualified form)	10
Longest host identifier³	128
Longest package version-id	64
Longest partition name	10
Longest savepoint name	128
Longest schema name	128
Longest server name	18
Longest statement name	128
Longest SQL condition name	128
Longest SQL label	128
Longest unqualified alias name	128
Longest unqualified column name	128
Longest unqualified constraint name	128
Longest unqualified distinct type name	128
Longest unqualified function name	128
Longest unqualified global variable name	128
Longest unqualified index name	128
Longest unqualified nodegroup name	10
Longest unqualified package name	10
Longest unqualified procedure name	128
Longest unqualified sequence name	128
Longest unqualified specific name	128
Longest unqualified SQL parameter name	128
Longest unqualified SQL variable name	128
Longest unqualified system column name	10
Longest unqualified system object name	10
Longest unqualified system schema name	10
Longest unqualified table and view name	128
Longest unqualified trigger name	128
Longest unqualified XSR object name	128
Longest XML element name, attribute name, prefix name, or processing instruction name specified in XMLELEMENT, XMLFOREST, XMLATTRIBUTES, XMLNAMESPACES or XMLPI	128
Longest XML element name, attribute name, prefix name, or processing instruction name for a parsed XML document	1000
Longest XML schema location uniform resource identifier (URI)	1000

Table 2. Numeric Limits
Numeric Limits	DB2 for i 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	63
Maximum exponent (E^max) for REAL values	38
Smallest REAL value⁴	-3.4x10³⁸
Largest REAL value⁴	+3.4x10³⁸
Minimum exponent (E^min) for REAL values	-38
Smallest positive REAL value⁴	+1.18x10^-38
Largest negative REAL value⁴	-1.18x10^-38
Maximum exponent (E^max) for DOUBLE values	308
Smallest DOUBLE value⁴	-1.79x10³⁰⁸
Largest DOUBLE value⁴	+1.79x10³⁰⁸
Minimum exponent (E^min) for DOUBLE values	-308
Smallest positive DOUBLE value⁴	+2.23x10^-308
Largest negative DOUBLE value⁴	-2.23x10^-308
Maximum exponent (E^max) for DECFLOAT(16) values	384
Smallest DECFLOAT(16) value⁵	-9.999999999999999x10³⁸⁴
Largest DECFLOAT(16) value⁵	9.999999999999999x10³⁸⁴
Minimum exponent (E^min) for DECFLOAT(16) values	-383
Smallest positive DECFLOAT(16) value⁵	1x10^-383
Largest negative DECFLOAT(16) value⁵	-1x10^-383
Maximum exponent (E^max) for DECFLOAT(34) values	6144
Smallest DECFLOAT(34) value⁵	-9.999999999999999999999999999999999x10⁶¹⁴⁴
Largest DECFLOAT(34) value⁵	9.999999999999999999999999999999999x10⁶¹⁴⁴
Minimum exponent (E^min) for DECFLOAT(34) values	-6143
Smallest positive DECFLOAT(34) value⁵	1x10^-6143
Largest negative DECFLOAT(34) value⁵	-1x10^-6143

Table 3. String Limits
String Limits	DB2 for i Limit
Maximum length of CHAR (in bytes)	32765⁶
Maximum length of VARCHAR (in bytes)	32739⁶
Maximum length of CLOB (in bytes)	2 147 483 647
Maximum length of GRAPHIC (in double-byte characters)	16382⁶
Maximum length of VARGRAPHIC (in double-byte characters)	16369⁶
Maximum length of DBCLOB (in double-byte characters)	1 073 741 823
Maximum length of BINARY (in bytes)	32765⁶
Maximum length of VARBINARY (in bytes)	32739⁶
Maximum length of BLOB (in bytes)	2 147 483 647
Maximum length of serialized XML (in bytes)	2 147 483 647
Maximum length of character constant	32740
Maximum length of a graphic constant	16370
Maximum length of binary constant	32740
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 762
Maximum length of catalog comments	2000⁷
Maximum length of column label (in bytes)	60
Maximum length of SQL routine label	128
Maximum length of table, package, or alias label	50
Maximum length of C NUL-terminated	32739⁶
Maximum length of C NUL-terminated graphic	16369⁶

Table 4. XML Limits
XML Limits	DB2 for i Limit
Maximum length of an XML schema document (in bytes)	2 147 483 647
Maximum length of a parsed XML entity	Storage

Table 5. Datetime Limits
Datetime Limits	DB2 for i 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.000000
Largest TIMESTAMP value	9999-12-31-24.00.00.000000

Table 6. DataLink Limits
Datalink Limits	DB2 for i Limit
Maximum length of DATALINK	32718
Maximum length of DATALINK comment	254

Table 7. Database Manager Limits
Database Manager Limits	DB2 for i Limit
Relational Database
Maximum number of schemas	storage
Maximum number of tables in a relational database	storage
Maximum number of nodes in a nodegroup	32
Schemas
Maximum number of objects in a schema	approximately 360 000
Tables and Views
Maximum number of columns in a table	8000
Maximum number of columns in a view	8000
Maximum length of a row without LOBs including all overhead	32766
Maximum length of a row with LOBs including all overhead	3 758 096 383
Maximum number of rows in a non-partitioned table	4 294 967 288
Maximum number of rows in a data partition	4 294 967 288
Maximum size of a non-partitioned table	1.7 terabytes
Maximum size of a data partition	1.7 terabytes
Maximum number of data partitions in a single partitioned table	256
Maximum number of table partitioning columns	120
Maximum number of tables referenced in a view or materialized query table	256⁸
Maximum number of dependent views, materialized query tables, and indexes on a table or view.	storage
Constraints
Maximum number of constraints on a table	5000
Maximum number of columns in a UNIQUE constraint	120
Maximum combined length of columns in a UNIQUE constraint (in bytes)	32767⁶
Maximum number of referencing columns in a foreign key	120
Maximum combined length of referencing columns in a foreign key (in bytes)	32767⁶
Maximum length of a CHECK constraint (in bytes)	statement
Triggers
Maximum number of triggers on a table	300
Maximum runtime depth of cascading triggers	200
Indexes
Maximum number of indexes on a table	approximately 15000
Maximum number of columns in an index key	120
Maximum length of an index key	32767⁶
Maximum size of a non-partitioned index	1.7 terabytes
Maximum size of a partition of a partitioned index	1.7 terabytes
SQL
Maximum length of an SQL statement (in bytes)	2 097 152
Maximum number of tables referenced in an SQL statement	1000⁸
Maximum number of variables and constants in an SQL statement	4096⁹
Maximum number of elements in a select list	approximately 8000¹⁰
Maximum number of predicates in a WHERE or HAVING clause	statement
Maximum number of columns in a GROUP BY clause	total GROUP BY length
Maximum total length of columns in a GROUP BY clause	3.5 Gigabytes¹¹
Maximum number of elements in a CUBE grouping	10
Maximum number of columns in an ORDER BY clause	total ORDER BY length
Maximum total length of columns in an ORDER BY clause	3.5 Gigabytes ¹¹
Maximum levels of recursion for a hierarchical query	250
Maximum levels allowed for a subquery	256
Maximum number of values in an insert operation	8000
Maximum number of SET clauses in a single update operation	8000
Routines
Maximum number of parameters in a procedure	1024¹²
Maximum number of parameters in a function	1024
Maximum number of nested levels for routines	storage
Types
Maximum cardinality of an array type	2 147 483 647
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 length of an MQ message CLOB value (in bytes)	2M
Maximum length of an MQ message varying length value (in bytes)	32000
Maximum number of declared cursors in a program	storage
Maximum number of cursors opened at one time	storage ¹⁴
Maximum number of rows locked in a unit of work	500 000 000
Maximum number of locators in a transaction	16 000 000 ¹⁵
Maximum size of an SQLDA (in bytes)	16 777 215
Maximum number of prepared statements	storage
Maximum number of savepoints active at one time	storage
Maximum number of simultaneously allocated CLI handles in a process	160 000 ¹⁶
Maximum size of a package	500 megabytes¹⁷
Maximum length of a path	8843
Maximum number of schemas in a path	268
Maximum length of a password	127
Maximum length of a hint	32
Maximum size of a program, service program, or module associated space (in bytes)	16 777 216
Maximum size of the diagnostics area	90K
Maximum size of an array variable	4GB

¹ As an application requester, DB2 for i can send an authorization name of up to 255 bytes.

² For REXX procedures, the limit is 33.

³ For an RPG, COBOL, or REXX program, the limit is 64.

⁴ The values shown are approximate.

⁵ These are the limits for normal numbers in DECFLOAT. DECFLOAT also contains special values such as NaN and Infinity that are also valid. DECFLOAT also supports subnormal numbers that are outside of the documented range.

⁶ If the column is NOT NULL, the maximum is one more.

⁷ For sequences the limit is 500.

⁸

The maximum number of partitions (members) referenced may exceed 1000. In CREATE VIEW, DELETE, and UPDATE statements the maximum number of partitions (members) is 256. End of change

⁹ If the statement is not read-only, the limit is 2048. The limit is approximate and may be less if very large string constants or string variables are used.

¹⁰ The limit is based on the size of internal structures generated for the parsed SQL statement.

¹¹

The limit is 32766 if CQE processed the select statement. The limit will be less if an ICU collating sequence or ALWCPYDTA(*NO) is used. End of change

¹² SQL procedures are limited to 1024 parameters. The number of parameters for external procedures depends on the PARAMETER STYLE:

PARAMETER STYLE GENERAL has a maximum of 1024.
PARAMETER STYLE GENERAL WITH NULLS has a maximum of 1023.
PARAMETER STYLE SQL or PARAMETER STYLE DB2SQL has a maximum of 508.
PARAMETER STYLE JAVA or PARAMETER STYLE DB2GENERAL has a maximum of 90.

The maximum number of parameters for external procedures is also limited by the maximum number of parameters allowed by the licensed program used to compile the external program.

¹³ In RPG/400® and PL/I programs when the old parameter passing technique is used, the limit is approximately 4000. The limit is based on the number of pointers allowed in the program. In all other cases, the limit is based on operating system constraints.

¹⁴ The maximum number of cursors open at one time in a single job is approximately 21 754.

¹⁵ The maximum number of locators in a transaction in SQL Server mode is 209 000.

¹⁶ The maximum number of allocated handles per DRDA connection is 500.

¹⁷ The maximum size Start of change

of a DRDA package End of change

can be increased to 1 gigabyte by using a QAQQINI option. Start of change

Other types of packages are limited to 16M. End of change