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 101
Longest correlation name 128
Longest cursor name 128
Longest descriptor name 128
Longest external program name (string form) 2792
Longest external program name (unqualified form) 10
Longest host identifier3 128
Longest package version-id 64
Longest partition name 10
Longest savepoint name 128
Longest schema name Start of change128End of change
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
Start of changeLongest unqualified global variable nameEnd of change Start of change128End of change
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
Start of changeLongest unqualified system object nameEnd of change 10
Start of changeLongest unqualified system schema nameEnd of change Start of change10End of change
Longest unqualified table and view name 128
Longest unqualified trigger name 128
Start of changeLongest unqualified XSR object nameEnd of change Start of change128End of change
Start of changeLongest XML element name, attribute name, prefix name, or processing instruction name specified in XMLELEMENT, XMLFOREST, XMLATTRIBUTES, XMLNAMESPACES or XMLPIEnd of change Start of change128End of change
Start of changeLongest XML element name, attribute name, prefix name, or processing instruction name for a parsed XML documentEnd of change Start of change1000End of change
Start of changeLongest XML schema location uniform resource identifier (URI)End of change Start of change1000End of change

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 (Emax) for REAL values 38
Smallest REAL value4 -3.4x1038
Largest REAL value4 +3.4x1038
Minimum exponent (Emin) for REAL values -38
Smallest positive REAL value4 +1.18x10-38
Largest negative REAL value4 -1.18x10-38
Maximum exponent (Emax) for DOUBLE values 308
Smallest DOUBLE value4 -1.79x10308
Largest DOUBLE value4 +1.79x10308
Minimum exponent (Emin) for DOUBLE values -308
Smallest positive DOUBLE value4 +2.23x10-308
Largest negative DOUBLE value4 -2.23x10-308
Maximum exponent (Emax) for DECFLOAT(16) values 384
Smallest DECFLOAT(16) value5 -9.999999999999999x10384
Largest DECFLOAT(16) value5 9.999999999999999x10384
Minimum exponent (Emin) for DECFLOAT(16) values -383
Smallest positive DECFLOAT(16) value5 1x10-383
Largest negative DECFLOAT(16) value5 -1x10-383
Maximum exponent (Emax) for DECFLOAT(34) values 6144
Smallest DECFLOAT(34) value5 -9.999999999999999999999999999999999x106144
Largest DECFLOAT(34) value5 9.999999999999999999999999999999999x106144
Minimum exponent (Emin) for DECFLOAT(34) values -6143
Smallest positive DECFLOAT(34) value5 1x10-6143
Largest negative DECFLOAT(34) value5 -1x10-6143

  

Table 3. String Limits
String Limits DB2 for i Limit
Maximum length of CHAR (in bytes) 327656
Maximum length of VARCHAR (in bytes) 327396
Maximum length of CLOB (in bytes) 2 147 483 647
Maximum length of GRAPHIC (in double-byte characters) 163826
Maximum length of VARGRAPHIC (in double-byte characters) 163696
Maximum length of DBCLOB (in double-byte characters) 1 073 741 823
Maximum length of BINARY (in bytes) 327656
Maximum length of VARBINARY (in bytes) 327396
Maximum length of BLOB (in bytes) 2 147 483 647
Start of changeMaximum length of serialized XML (in bytes)End of change Start of change2 147 483 647End of change
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 20007
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 327396
Maximum length of C NUL-terminated graphic 163696

 

Table 4. XML Limits
XML Limits DB2 for i Limit
Start of changeMaximum length of an XML schema document (in bytes)End of change Start of change2 147 483 647End of change
Start of changeMaximum length of a parsed XML entityEnd of change Start of changeStorageEnd of change
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 Start of change120End of change
Maximum number of tables referenced in a view or materialized query table 2568
Maximum number of dependent views, materialized query tables, and indexes on a table or view. storage
Constraints
Maximum number of constraints on a table Start of change5000End of change
Maximum number of columns in a UNIQUE constraint 120
Maximum combined length of columns in a UNIQUE constraint (in bytes) 327676
Maximum number of referencing columns in a foreign key 120
Maximum combined length of referencing columns in a foreign key (in bytes) 327676
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 Start of change15000End of change
Maximum number of columns in an index key 120
Maximum length of an index key 327676
Maximum size of a non-partitioned index Start of change1.7 terabytesEnd of change
Maximum size of a partition of a partitioned index Start of change1.7 terabytesEnd of change
SQL
Maximum length of an SQL statement (in bytes) 2 097 152
Maximum number of tables referenced in an SQL statement 10008
Maximum number of variables and constants in an SQL statement 40969
Maximum number of elements in a select list approximately 800010
Maximum number of predicates in a WHERE or HAVING clause statement
Maximum number of columns in a GROUP BY clause total GROUP BY length
Start of changeMaximum total length of columns in a GROUP BY clauseEnd of change Start of change3.5 Gigabytes11End of change
Start of changeMaximum number of elements in a CUBE groupingEnd of change Start of change10End of change
Maximum number of columns in an ORDER BY clause total ORDER BY length
Start of changeMaximum total length of columns in an ORDER BY clauseEnd of change Start of change3.5 Gigabytes 11End of change
Start of changeMaximum levels of recursion for a hierarchical queryEnd of change Start of change250End of change
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 102412
Maximum number of parameters in a function Start of change1024End of change
Maximum number of nested levels for routines storage
Start of changeTypesEnd of change
Start of changeMaximum cardinality of an array typeEnd of change Start of change2 147 483 647End of change
Applications
Maximum number of host variable declarations in a precompiled program storage13
Maximum length of a host variable value (in bytes) 2 147 483 647
Start of changeMaximum length of an MQ message CLOB value (in bytes) End of change Start of change2MEnd of change
Start of changeMaximum length of an MQ message varying length value (in bytes) End of change Start of change32000End of change
Maximum number of declared cursors in a program storage
Maximum number of cursors opened at one time storage 14
Start of changeMaximum number of rows locked in a unit of workEnd of change 500 000 000
Maximum number of locators in a transaction 16 000 000 15
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 16
Maximum size of a package 500 megabytes17
Maximum length of a path Start of change8843End of change
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
Start of changeMaximum size of the diagnostics areaEnd of change Start of change90KEnd of change
Start of changeMaximum size of an array variableEnd of change Start of change4GBEnd of change
1 As an application requester, DB2 for i can send an authorization name of up to 255 bytes.
2 For REXX procedures, the limit is 33.
3 For an RPG, COBOL, or REXX program, the limit is 64.
4 The values shown are approximate.
5 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.
6 If the column is NOT NULL, the maximum is one more.
7 For sequences the limit is 500.
8 Start of changeThe 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
9 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.
10 The limit is based on the size of internal structures generated for the parsed SQL statement.
11 Start of changeThe 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
12 SQL procedures are limited to 1024 parameters. The number of parameters for external procedures depends on the PARAMETER STYLE: 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.
13 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.
14 The maximum number of cursors open at one time in a single job is approximately 21 754.
15 The maximum number of locators in a transaction in SQL Server mode is 209 000.
16 The maximum number of allocated handles per DRDA connection is 500.
17 The maximum size Start of changeof a DRDA packageEnd of change can be increased to 1 gigabyte by using a QAQQINI option. Start of changeOther types of packages are limited to 16M.End of change