Mainframe Blog

Db2 Limits in Db2 12

7 minute read
Stephen Watts

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.

Order Now! Get your free Db2 collateral from BMC!

Get your free Reference Guide and Catalog Tables Poster for z/OS from BMC. This collateral helps DB2 for z/OS users with daily activities in administering and programming for DB2 on z/OS.


These postings are my own and do not necessarily represent BMC's position, strategies, or opinion.

See an error or have a suggestion? Please let us know by emailing blogs@bmc.com.

BMC Bring the A-Game

From core to cloud to edge, BMC delivers the software and services that enable nearly 10,000 global customers, including 84% of the Forbes Global 100, to thrive in their ongoing evolution to an Autonomous Digital Enterprise.
Learn more about BMC ›

About the author

Stephen Watts

Stephen Watts (Birmingham, AL) has worked at the intersection of IT and marketing for BMC Software since 2012.

Stephen contributes to a variety of publications including CIO.com, Search Engine Journal, ITSM.Tools, IT Chronicles, DZone, and CompTIA.