Limits in Db2 for z/OS

Db2 for z/OS has system limits, object and SQL limits, length limits for identifiers and strings, and limits for certain data type values.

System storage limits might preclude the limits specified in this section. The limit for items not that are not specified below is limited by system storage.

The following table shows the length limits for identifiers.

Table 1. Identifier length limits. The term byte(s) in this table means the number of bytes for the UTF-8 representation unless noted otherwise.
Item	Limit
External-java-routine-name	1305 bytes
Name of an alias ¹, auxiliary table, collection, clone table, constraint, correlation, cursor (except for DECLARE CURSOR WITH RETURN or the EXEC SQL utility), distinct type (both parts of two-part name), function (both parts of two-part name), host identifier, index, JARs, parameter, procedure, role, schema, sequence, specific, statement, storage group, savepoint, SQL condition, SQL label, SQL parameter, SQL variable, synonym, table, trigger, view, XML attribute name, XML element name	128 bytes
Name of an authorization ID or name of a security label.	8 bytes
Routine version identifier	64 EBCDIC bytes, and the UTF-8 representation of the name must not exceed 122 bytes.
Name of a column	128 bytes ¹
Name of cursor that is created with DECLARE CURSOR WITH RETURN	30 bytes
Name of cursor that is created with the EXEC SQL utility	8 bytes
Name of a location	16 bytes
Name of a database, table space, or buffer pool	8 bytes
Name of a plan or program	8 bytes
Name of a package	8 bytes (Only 8 EBCDIC characters are used for packages that are created with the BIND PACKAGE command. 128 bytes can be used for packages that are created as a result of the CREATE FUNCTION (SQL scalar) statement, the CREATE PROCEDURE (SQL - native) statement, the CREATE TRIGGER statement, or a BIND command that specifies a zFS file as DBRM library.)
Name of a profile that is created with CREATE TRUSTED CONTEXT or ALTER TRUSTED CONTEXT	127 bytes
Name of an ICF catalog.	8 bytes
Notes: The 128 byte limit for the length column names is in effect when the TABLE_COLUMN_NAME_EXPANSION system parameter is on. Otherwise the limit for column names is 30 bytes. The limit is for the column name in UTF8, and its equivalent in the system EBCDIC CCSID. In a data sharing environment, it is recommended that this system parameter be enabled for all members of the data sharing group. However, there are some restrictions on the use of column names longer than 30 bytes. For more information, see Column names longer than 30 bytes.

Table 2 shows the minimum and maximum limits for numeric values.

Table 2. Numeric limits
Item	Limit
Smallest SMALLINT value	-32768
Largest SMALLINT value	32767
Smallest INTEGER value	-2147483648
Largest INTEGER value	2147483647
Smallest BIGINT value	-9223372036854775808
Largest BIGINT value	9223372036854775807
Smallest REAL value	About -7.2x10⁷⁵
Largest REAL value	About 7.2x10⁷⁵
Smallest positive REAL value	About 5.4x10^-79
Largest negative REAL value	About -5.4x10^-79
Smallest FLOAT value	About -7.2x10⁷⁵
Largest FLOAT value	About 7.2x10⁷⁵
Smallest positive FLOAT value	About 5.4x10^-79
Largest negative FLOAT value	About -5.4x10^-79
Smallest DECIMAL value	1 - 10³¹
Largest DECIMAL value	10³¹ - 1
Largest decimal precision	31
Smallest DECFLOAT(16) value¹	-9.999999999999999x10³⁸⁴
Largest DECFLOAT(16) value¹	9.999999999999999x10³⁸⁴
Smallest positive DECFLOAT(16) value¹	1.000000000000000x10^-383
Largest negative DECFLOAT(16) value¹	-1.000000000000000x10^-383
Smallest DECFLOAT(34) value¹	-9.999999999999999999999999999999999x10⁶¹⁴⁴.
Largest DECFLOAT(34) value¹	9.999999999999999999999999999999999x10⁶¹⁴⁴.
Smallest positive DECFLOAT(34) value¹	1.000000000000000000000000000000000x10^-6143
Largest negative DECFLOAT(34) value¹	-1.000000000000000000000000000000000x10^-6143
Coefficient length for DECFLOAT values	DECFLOAT(16) is 16 digits; DECFLOAT(34) is 34 digits
Maximum Exponent (E_max) for DECFLOAT values	DECFLOAT(16) is 384; DECFLOAT(34) is 6144
Minimum Exponent (E_min) for DECFLOAT values	DECFLOAT(16) is -383; DECFLOAT(34) is -6143
Bias for DECFLOAT values	DECFLOAT(16) is 398; DECFLOAT(34) is 6176
Note: 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.

The following table shows the length limits for strings.

Table 3. String length limits
Item	Limit
Maximum length of CHAR	255 bytes
Maximum length of GRAPHIC	127 double-byte characters
Maximum length of BINARY	255 bytes
Maximum length¹ of VARCHAR	4046 bytes for a column in a table in a table space with 4 KB pages 8128 bytes for a column in a table in a table space with 8 KB pages 16320 bytes for a column in a table in a table space with 16 KB pages 32704 bytes for a column in a table in a table space with 32 KB pages Otherwise, 32704 bytes
Maximum length of VARCHAR that can be indexed by an XML index	1000 bytes after conversion to UTF-8
Maximum length¹ of VARGRAPHIC	2023 double-byte characters for a column in a table in a table space with 4 KB pages 4064 double-byte characters for a column in a table in a table space with 8 KB pages 8160 double-byte characters for a column in a table in a table space with 16 KB pages 16352 double-byte characters for a column in a table in a table space with 32 KB pages Otherwise, 16352 double-byte characters
Maximum length¹ of VARBINARY	4046 bytes for a column in a table in a table space with 4 KB pages 8128 bytes for a column in a table in a table space with 8 KB pages 16320 bytes for a column in a table in a table space with 16 KB pages 32704 bytes for a column in a table in a table space with 32 KB pages Otherwise, 32704 bytes
Maximum length of CLOB	2147483647 bytes (2 GB - 1 byte)
Maximum length of DBCLOB	1073741823 double-byte characters
Maximum length of BLOB	2147483647 bytes (2 GB - 1 byte)
Maximum length of a character constant	32704 UTF-8 bytes
Maximum length of a hexadecimal character constant	32704 hexadecimal digits
Maximum length of a graphic string constant	16352 double-byte characters (32704 bytes when expressed in UTF-8)
Maximum length of a hexadecimal graphic string constant	32704 hexadecimal digits
Maximum length of a text string used for a scalar expression	4000 UTF-8 bytes
Maximum length of a concatenated character string	2147483647 bytes (2 GB - 1 byte)
Maximum length of a concatenated graphic string	1073741824 double-byte characters
Maximum length of a concatenated binary string	2147483647 bytes (2 GB - 1 byte)
Maximum length of XML pattern text	4000 bytes after conversion to UTF-8
Maximum length of an XML element or attribute name in an XML document	1000 bytes
Maximum length of a namespace uri	1000 bytes
Maximum length of a namespace prefix	998 bytes
Largest depth of an internal XML tree	128 levels
Note: The maximum length can be achieved only if the column is the only column in the table. Otherwise, the maximum length depends on the amount of space remaining on a page.

The following table shows the minimum and maximum limits for datetime values.

Table 4. Datetime limits
Item	Limit
Smallest DATE value (shown in ISO format)	0001-01-01
Largest DATE value (shown in ISO format)	9999-12-31
Smallest TIME value (shown in ISO format)	00.00.00
Largest TIME value (shown in ISO format)	24.00.00
Smallest TIMESTAMP WITHOUT TIME ZONE value	0001-01-01-00.00.00.000000000000
Largest TIMESTAMP WITHOUT TIME ZONE value	9999-12-31-24.00.00.000000000000 ¹
Smallest TIMESTAMP WITH TIME ZONE value	0001-01-01-00.00.00.000000000000 +00:00
Largest TIMESTAMP WITH TIME ZONE value	9999-12-31-24.00.00.000000000000 +00:00 ¹
TIMESTAMP precision range	0 to 12
TIME ZONE hour range	-24 to 24
TIME ZONE minute range	0 to 59
Note: The maximum value is stated as a UTC value. When a timestamp without a time zone is compared to a timestamp with time zone, a necessary adjustment is made using the implicit time zone. During that adjustment, the timestamp without time zone could be converted to a value that is greater than the maximum value for a timestamp with time zone value (this could occur on operations such as comparison and assignment). This situation can be avoided by using '9999-12-30-00.00.00.000000000000' as the maximum value for timestamp without time zone and '9999-12-30-00.00.00.000000000000 +00:00' as the maximum value for timestamp with time zone columns.

The following table shows the Db2 limits on SQL statements.

Table 5. Db2 limits on SQL statements
Item	Limit
Maximum number of columns that are in a table or view (the value depends on the complexity of the CREATE VIEW statement) or columns returned by a table function.	750 or fewer (including hidden columns) 749 if the table is a dependent
Maximum number of columns that can be referenced in the target of MERGE statement.	749
Maximum number of expressions that can be referenced in the source of a MERGE statement.	750
Approximate maximum number of base tables in a SELECT, UPDATE, INSERT, MERGE, or DELETE statement or view	1024
Maximum number of tables in a FROM clause	225 or fewer, depending on the complexity of the statement
Maximum number of rows inserted with a single MERGE statement or FOR `n` ROWS form of INSERT statement	32767
Maximum row and record sizes for a table	See the maximum record size table under CREATE TABLE.
Maximum number of volume IDs in a storage group	133
Maximum number of partitions in a partitioned table space or partitioned index	4096 for most table space types, depending on the DSSIZE value and buffer pool page size. 64 for table spaces not defined with a DSSIZE greater than 2GB (or the LARGE clause¹).
Maximum sum of the lengths of limit key values of a partition boundary	765 UTF-8 bytes
Maximum size of a partition (table space or index)	For partition-by-range table spaces with relative numbering: 1 TB For table spaces that are defined with a DSSIZE greater than 4 GB: 256 GB, depending on the page size (for 1 to 64 partitions for 4 KB pages, for 1 to 128 partitions for 8 KB pages, for 1 - 256 partitions for 16 KB pages, and 1 - 512 partitions for 32 KB pages) For table spaces that are defined with a DSSIZE of 4 GB (or the LARGE clause¹): 4 GB, for 1 - 4096 partitions For table spaces with a DSSIZE not greater than 2 GB (and not defined with the LARGE clause¹): 4 GB, for 1 - 16 partitions 2 GB, for 17 - 32 partitions 1 GB, for 33 - 64 partitions
Maximum size of a non-partitioned index for a partitioned table space	For 5-byte EA table spaces: 16 TB for 4 KB pages 32 TB for 8 KB pages 64 TB for 16 KB pages 128 TB for 32 KB pages For table spaces that are defined with the LARGE clause¹: 16 TB
Maximum length of an index key	Partitioning index: 255-`n` Nonpartitioning index that is padded: 2000-`n` Nonpartitioning index that is not padded: 2000 - `n` - 2`m` - 3`d` n Number of columns in the key that allow nulls m Number of varying-length columns in the key d Number of DECFLOAT columns in the key
Maximum number of bytes used in the partitioning of a partitioned index	255 (This maximum limit is subject to additional limitations, depending on the number of partitions in the table space. The number of partitions * (106 + limit key size) must be less than 65394.)
Maximum number of columns in an index key	64
Maximum number of expressions in an index key	64
Maximum number of subqueries in a statement	224
Maximum total length of host and indicator variables pointed to in an SQLDA	32767 bytes 2147483647 bytes (2 GB - 1 byte) for a LOB, subject to the limitations that are imposed by the application environment and host language
Maximum size of application SQLDA for any statement that references host variables or parameter markers	99016 bytes
Maximum length of host variable used for insert or update operation	32704 bytes for a non-LOB 2147483647 bytes (2 GB - 1 byte) for a LOB, subject to the limitations that are imposed by the application environment and host language
Maximum number of host variables or parameter markers in a statement	16000 for a distributed application that originated from a client other than z/OS. Otherwise: 2205 for an application without any LOB or distinct type host variables or parameter markers 750 for an application with one or more LOB or distinct type host variables or parameter markers
Maximum length of an SQL statement	2097152 bytes
Maximum number of elements in a select list	750 or fewer, depending on whether the select list is for the result table of static scrollable cursor²
Maximum number of elements in an IN list ³	32767
Maximum number of predicates in a WHERE or HAVING clause	Limited by storage
Maximum number of elements in a CUBE grouping	16
Maximum total length of columns of a query operation requiring sort and evaluating aggregate functions (MULTIPLE DISTINCT and GROUP BY)	65529 bytes
Maximum length of a sort key	32707 bytes
Maximum length of a check constraint	3800 bytes
Maximum number of bytes that can be passed in a single parameter of an SQL CALL statement	32765 bytes for a non-LOB 2147483647 bytes (2 GB - 1 byte) for a LOB, subject to the limitations imposed by the application environment and host language
Maximum number of stored procedures, triggers, and user-defined functions that an SQL statement can implicitly or explicitly reference	64 nesting levels
Maximum number of parameters in a procedure or function	The SQL statement that contains the parameter list cannot exceed the maximum length of an SQL statement.
Maximum length of the SQL path	2048 bytes
Maximum length of a WLM environment name in a CREATE PROCEDURE, CREATE FUNCTION, ALTER PROCEDURE, or ALTER FUNCTION statement.	32 bytes
Maximum number of XPath level in the XMLPATTERN clause of the CREATE INDEX statement.	50 nesting levels
Note: CREATE TABLESPACE statements support the LARGE clause for compatibility with earlier releases of Db2 for z/OS. However, the DSSIZE clause is the preferred method for specifying maximum partition size of 4 GB or larger. Do not specify LARGE if a DSSIZE clause is specified. For more information, see "Non-large table spaces (deprecated)" in Table space types and characteristics in Db2 for z/OS. If the scrollable cursor is read-only, the maximum number is 749 less the number of columns in the ORDER BY that are not in the select list. If the scrollable cursor is not read-only, the maximum number is 747. FL 503 The limit of 32767 (32K) elements in the IN list of an IN predicate can be exceeded, if all of the following conditions are true and the query runs on a V7 accelerator, which uses IBM® Db2 Warehouse to process SQL statements: Query acceleration is enabled and requested for the query. For more information, see Enabling acceleration of SQL queries. The application runs at Db2 application compatibility level V13R1M503 or higher. Option 12 is specified in the list of values for the QUERY_ACCEL_OPTIONS subsystem parameter. The target accelerator is IBM Db2 Analytics Accelerator V7 for z/OS. The IN list specifies only SQL constants. The query can be functionally supported by the target accelerator and IBM Db2 Warehouse. When such queries run on a V7 accelerator, the enforcement of any limits on the number of elements in the IN list predicate is handled by IBM Db2 Warehouse, instead of Db2 for z/OS. IBM Db2 Warehouse has no documented limit for elements in an IN list. However, practical limitations such as the memory and processing resources available for the query in IBM Db2 Warehouse are still likely to impose some limit on the number of elements that can be specified in the IN list predicate. For more information, see "Accelerator-only support for more than 32K elements in an IN list" in IN predicate.

The following table shows the Db2 system limits.

Table 6. Db2 system limits
Item	Limit
Maximum number of concurrent Db2 or application agents	Limited by the EDM pool size, buffer pool size, and the amount of storage that is used by each Db2 or application agent
Maximum number of concurrently active audit policies	32
Maximum size of a non-LOB table or table space	128 terabytes (TB)
Maximum size of a simple or segmented table space	64 GB
Maximum size of a physical log record	36,000 bytes
Maximum size of a log space	6-byte format: 2⁴⁸ bytes 10-byte format: 2⁸⁰ bytes
Maximum size of an active log data set	768 GB minus 1 byte
Maximum size of an archive log data set	768 GB minus 1 byte
Maximum number of active log copies	2
Maximum number of archive log copies	2
Maximum number of active log data sets (each copy)	93
Maximum number of archive log volumes (each copy)	10000
Maximum number of databases accessible to an application or user	Limited by system storage and EDM pool size
Maximum number of databases	65217
Maximum number of implicitly created databases	Maximum value of the sequence SYSIBM.DSNSEQ_IMPLICITDB, with a default of 10000
Maximum number of internal objects for each database ¹	32767
Maximum number of indexes on declared global temporary tables	10000
Maximum size of an EDM pool	The installation parameter maximum depends on available space
Maximum number of rows per page	255 for all table spaces except catalog and directory tables spaces, which have a maximum of 127
Maximum simple or segmented data set size	2 GB
Maximum partitioned data set size	See item maximum size of a partition in Table 5
Maximum LOB data set size	256 GB
Maximum number of data sets for a LOB table space	254
Maximum storage per thread for LOB data	2 GB
Maximum number of table spaces that can be defined in a work file database	500
Maximum number of tables and triggers that can be defined in a work file database	11767
Maximum number of active profiles in DSN_PROFILE_TABLE profile table	4096
Note: The number of internal object descriptors (OBDs) for external objects are as follows: Table space: 2 Table: 1 Index: 2 Check constraint: 1 Referential integrity relationship: 2 Auxiliary relationship for each LOB column: 1 XML relationship for each XML column: 1 Trigger: 1 View that has an INSTEAD OF trigger: 1