Identifier length limits
| 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), 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 authid or name of a security label | 8 bytes |
| Routine version identifier | 64 EBCDIC bytes, and UTF-8 representation of the name must not exceed 122 bytes |
| Name of a column | 30 bytes |
| Name of a cursor that is created with DECLARE CURSOR WITH RETURN | 30 bytes |
| Name of a cursor created with EXEC SQL utility | 8 bytes |
| Name of a location | 16 bytes |
| Name of buffer pool name, catalog, database, plan, program, table space | 8 bytes |
| Name of 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 created with CREATE/ALTER TRUSTED CONTEXT | 127 bytes |
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.2*10(75) |
| Largest REAL value | About 7.2*10(75) |
| Smallest positive REAL value | About 5.4*10(-79) |
| Largest negative REAL value | About -5.4*10(-79) |
| Smallest FLOAT value | About -7.2*10(75) |
| Largest FLOAT value | About 7.2*10(75) |
| Smallest positive FLOAT value | About 5.4*10(-79) |
| Largest negative FLOAT value | About -5.4*10(-79) |
| Smallest DECIMAL value | 1 – 10(31) |
| Largest DECIMAL value | 10(31) – 1 |
| Largest DECIMAL precision | 31 |
| Smallest DECFLOAT(16) value | -9.999999999999999×10384 |
| Largest DECFLOAT(16) value | 9.999999999999999×10384 |
| Smallest positive DECFLOAT(16) value | 1.000000000000000×10-383 |
| Largest negative DECFLOAT(16) value | -1.0000000000000000×10-383 |
| Smallest DECFLOAT(34) value | -9999999999999999999999999999×10-6144 |
| Largest DECFLOAT(34 value | 9.99999999999999999999999999×10-6144 |
| Smallest positive DECFLOAT(34) value | 1.00000000000000000000000000×10-6143 |
| Largest negative DECFLOAT(34) value | -1.00000000000000000000000000×10-6143 |
| Coefficient length for DECFLOAT values | DECFLOAT(16) 16 digits; DECFLOAT(34) 34 digits |
| Max Exponent (Emax) for DECFLOAT | DECFLOAT(16) is 384; DECFLOAT(34) is 6144 |
| Min Exponent (Emin) for DECFLOAT | DECFLOAT(16) is -383; DECFLOAT(34) is -6143 |
| Bias for DECFLOAT values | DECFLOAT(16) is 398; DECFLOAT(34) is 6176 |
String Length Limits
| Item | Limit |
| Max length of CHAR | 255 bytes |
| Max length of GRAPHIC | 127 DBCS characters |
| Max length of BINARY | 255 bytes |
| Max length of VARCHAR | 4046 bytes for 4-KB pages 8128 bytes for 8-KB pages 16320 bytes for 16-KB pages 32704 bytes for 32-KB pages |
| Max length of VARCHAR indexed by an XML index | 100 bytes after conversion to UTF-8 |
| Max length of VARGRAPHIC | 2023 DBCS characters for 4-KB pgs 4064 DBCS characters for 8-KB pgs 8160 DBCS characters for 16-KB pgs 16352 DBCS characters for 32-KB pgs |
| Max length of VARBINARY | 32704 bytes |
| Max length of CLOB | 2 147 483 674 bytes (2GB – 1 byte) |
| Max length of DBCLOB | 1 073 741 824 DBCS characters |
| Max length of BLOB | 2 147 483 647 bytes (2GB – 1 byte) |
| Max length of a character constant | 32704 UTF-8 bytes |
| Max length of a hexadecimal character constant | 32704 hexadecimal digits |
| Max length of a graphic string constant | 32704 UTF-8 bytes |
| Max length of a hexadecimal graphic string constant | 32704 hexadecimal digits |
| Max length of a text string used for a scalar expression | 4000 UTF-8 bytes |
| Max length of a concatenated character string | 2 147 483 647 bytes (2GB – 1 byte) |
| Max length of a concatenated graphic string | 1 073 741 824 DBCS characters |
| Max length of a concatenated binary string | 2 147 483 647 bytes (2GB – 1 byte) |
| Max length of a XML pattern text | 4000 bytes after conversion to UTF-8 |
| Max 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 |
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.00000000000 |
| Largest TIMESTAMP WITH TIME ZONE value | 9999-12-31-24.00.00.00000000000 |
| Smallest TIMESTAMP WITH TIME ZONE value | 0001-01-01-00.00.00.00000000000 +00.00 |
| Largest TIMESTAMP WITH TIME ZONE value | 9999-12-31-24.00.00.00000000000 +00.00 |
| TIMESTAMP precision range | 0 to 12 |
| TIME ZONE hour range | -12 to 14 |
| TIME ZONE minute range | 0 to 59 |
DB2 Limits on SQL Statements
| Item | Limit |
| Max number of columns in a table or view (depending on complexity of the view) or columns returned by a table function. | 750 or fewer (including hidden columns) 749 if the table is a dependent |
| Max number of base tables in a view, SELECT, UPDATE, INSERT, or DELETE | 225 |
| Max number of rows that can be inserted with a single INSERT or MERGE statement | 32767 |
| Max row and record sizes for a table | Dependent on type of table created |
| Max number of volume IDs in a storage group | 133 |
| Max number of partitions in a partitioned tablespace or partitioned index | 64 for tablespaces that are not defined with LARGE or a DSSIZE>2GB 4096, depending on DSSIZE or LARGE and the page size |
| Max sum of the lengths of limit key values of a partition boundary | 765 UTF-8 bytes |
| Max size of a partition (tablespace or index) | For tablespaces that are not defined with LARGE or a DSSIZE greater than 2GB: 4GB, for 1 to 16 partitions 2GB, for 17 to 32 partitions 1GB, for 33 to 64 partitions For tablespaces that are defined with LARGE: 4GB for 1 to 4096 partitions For tablespaces defined with a DSSIZE>2GB: 64GB, depending on the page size, (1 to 256 partitions for 4KB, 1 to 512 partitions for 16KB, 1 to 1024 partitions for 32KB, and 1 to 2048 for 32KB) For range-partition tablespaces with relative number: 1TB |
| Maximum size of a non-partitioned index for a partitioned table space | For 5-byte EA table space: 16TB for 4KB pages 32TB for 8KB pages 64TB for 16KB pages 128TB for 32KB pages For LARGE tablespaces: 16TB |
| Max length of an index key | Partitioning index: 255-n Non-partitioning index padded 2000-n Non-partitioning index not padded 2000-n-2m N= number of columns in the key that allow nulls, and m is the number of varying length columns in key |
| Max number of bytes used in the partitioning of 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.) |
| Max number of expressions in an index key | 64 |
| Max number of columns in an index key | 64 |
| Max number of tables in a FROM clause | 225 or less, depending on the complexity of the statement |
| Max number of subqueries in a statement | 224 |
| Max total length of host and indicator variables pointed to in an SQLDA | 32767 bytes 2 147 483 647 bytes (2GB – 1 byte) for a LOB, subject to the limitations imposed by the application environment and host language |
| Longest host variable used for insert or update | 32704 bytes for a non-LOB 2 147 483 647 bytes (2GB – 1 byte) for a LOB, subject to the limitations imposed by the application environment and the host language |
| Maximum number of host variables or parameter markers used in a statement | 16,000 |
| Longest SQL statement | 2097152 bytes |
| Max number of elements in a select list | 750 or fewer, depending on whether the select list is for the result table of a static scrollable cursor |
| Max num of predicates WHERE or HAVING | Limited by storage |
| Max total length of columns of a query operation requiring a sort key (SELECT DISTINCT, ORDER BY, GROUP BY, UNION, EXCEPT and INTERSECT, without ALL, and DISTINCT keyword for aggregate functions) | 4000 bytes |
| Max total length of columns of a query operation requiring a sort and evaluating column functions (DISTINCT and GROUP BY) | 32600 bytes |
| Max length of a sort key | 16000 bytes |
| Max length of a table check constraint | 3800 bytes |
| Max number of bytes that can be passed in a single parameter of an SQL CALL statement | 32765 bytes for a non-LOB 2 147 483 647 bytes (2GB – 1 byte) for a LOB, subject to the limitations imposed by the application environment and host language |
| Max number of stored procedures, triggers, and user-defined functions that an SQL statement can implicitly or explicitly reference | 64 nesting levels |
| Max length of the SQL path | 2048 bytes |
| Max length of a WLM environment name in a CREAT/ALTERPROCEDURE/FUNCTION | 32 bytes |
| Max length of XPath level in XMLPATTERN clause of the CREATE INDEX statement | 50 nesting levels |
DB2 System Limits
| Item | Limit |
| Max number of concurrent DB2 or application agents | Limited by EDM pool size, buffer pool size, and amount of storage used by each DB2 or agent |
| Largest non-LOB table or tablespace | 128TB |
| Largest simple or segmented tablespace | 64GB |
| Largest log space | 6-byte format 2 48 bytes 10-byte format 2 80 bytes |
| Largest active log data set | 768GB – 1 byte |
| Largest archive log data set | 768GB – 1 byte |
| Max number of active log copies | 2 |
| Max number of archive log copies | 2 |
| Max number of active log data sets (each copy) | 93 |
| Max number of archive log data sets (each copy) | 10000 |
| Max number of databases accessible to an application or end user | Limited by storage system and EDM pool size |
| Largest EDM pool | The installation parameter maximum depends on available space |
| Max number of databases | 65271 |
| Max number of implicitly created databases | 10000 (SYSIBM.DSNSEQ_IMPLICITDB) |
| Max number of internal objects for each database | 32767 |
| Max number of indexes on declared global temporary tables | 10000 |
| Max number of rows per page | 255 for all tablespaces except catalog and directory (maximum of 127) |
| Max size of EDM pool | Depends on available space |
| Max simple or segmented data set size | 2GB |
| Max partitioned data set size | See ‘maximum size of a partition’ |
| Max LOB data set size | 256GB |
| Max number of rows that can be inserted with a single INSERT statement | 32767 rows |
| Max number of table spaces that can be defined in a work file database | 500 |
| Max number of tables and triggers that can be defined in a work file database | 11767 |
Disclaimer: This Db2® 12 for z/OS Reference Guide was developed to help users in their daily activities in administrating and programming in Db2 for z/OS. There are no guarantees expressed or implied with the contents in this guide. We want to provide a quality and useful reference for users. Please notify us of any mistakes or errors in this reference guide at blogs@bmc.com. Db2 is a registered trademark of the IBM Corporation.
