The following tables describe certain SQL and database limits imposed by the DB2® for i database manager.
| 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 | 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![]() |
| 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 |
| 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 |
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 | 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 |
| 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 |
| Datalink Limits | DB2 for i Limit |
|---|---|
| Maximum length of DATALINK | 32718 |
| Maximum length of DATALINK comment | 254 |
| 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 | 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 | 5000![]() |
| 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 4000 |
| Maximum number of columns in an index key | 120 |
| Maximum length of an index key | 327676 |
| Maximum size of a non-partitioned index | 1 terabyte |
| Maximum size of a partition of a partitioned index | 1 terabyte |
| 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 |
| Maximum total length of columns in a GROUP BY clause | 32766 11 |
| Maximum number of columns in an ORDER BY clause | total ORDER BY length |
| Maximum total length of columns in an ORDER BY clause | 32766 11 |
| 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 | 90 |
| 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 | storage13 |
| Maximum length of a host variable value (in bytes) | 2 147 483 647 |
| Maximum number of declared cursors in a program | storage |
| Maximum number of cursors opened at one time | storage 14 |
| Maximum number of rows changed in a unit of work | 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 | 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 |