Appendix A. SQL limits
The following tables describe certain SQL and database limits imposed by the DB2 for i5/OS database manager.
- 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 77. Identifier Length Limits Identifier Limits DB2 for i5/OS Limit Longest authorization name 1083 Longest correlation name 128 Longest cursor name 18 Longest descriptor name 128 Longest external program name (string form) 27984 Longest external program name (unqualified form) 10 Longest host identifier85 64 Longest package version-id 64 Longest partition name 10 Longest savepoint name 128 Longest schema name 10 Longest server name 18 Longest statement name 18 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 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 table, view, and index name 10 Longest unqualified table and view name 128 Longest unqualified trigger name 128
Table 78. Numeric Limits Numeric Limits DB2 for i5/OS 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 Smallest DOUBLE value86 -1.79x10308 Largest DOUBLE value86 +1.79x10308 Smallest positive DOUBLE value86 +2.23x10-308 Largest negative DOUBLE value86 -2.23x10-308 Smallest REAL value86 -3.4x1038 Largest REAL value86 +3.4x1038 Smallest positive REAL value86 +1.18x10-38 Largest negative REAL value86 -1.18x10-38
Table 79. String Limits String Limits DB2 for i5/OS Limit Maximum length of CHAR (in bytes) 3276587 Maximum length of VARCHAR (in bytes) 3273987 Maximum length of CLOB (in bytes) 2 147 483 647 Maximum length of GRAPHIC (in double-byte characters) 1638287 Maximum length of VARGRAPHIC (in double-byte characters) 1636987 Maximum length of DBCLOB (in double-byte characters) 1 073 741 823 Maximum length of BINARY (in bytes) 3276587 Maximum length of VARBINARY (in bytes) 3273987 Maximum length of BLOB (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 200088 Maximum length of column label 60 Longest SQL routine label 128 Longest table, package, or alias label 50 Maximum length of C NUL-terminated 3273987 Maximum length of C NUL-terminated graphic 1636987
Table 80. Datetime Limits Datetime Limits DB2 for i5/OS 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 81. DataLink Limits Datalink Limits DB2 for i5/OS Limit Maximum length of DATALINK 32718 Maximum length of DATALINK comment 254
Table 82. Database Manager Limits Database Manager Limits DB2 for i5/OS Limit Relational Database Maximum number of schemas 474089 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 3276690 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 8000 Maximum number of tables referenced in a view or materialized query table 25691 Maximum number of dependent views, materialized query tables, and indexes on a table or view. 6553592 Constraints Maximum number of constraints on a table 300 Maximum number of columns in a UNIQUE constraint 120 Maximum combined length of columns in a UNIQUE constraint (in bytes) 3276787 Maximum number of referencing columns in a foreign key 120 Maximum combined length of referencing columns in a foreign key (in bytes) 3276787 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 3276787 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 100091 Maximum number of variables and constants in an SQL statement 409693 Maximum number of elements in a select list94 approximately 8000 Maximum number of predicates in a WHERE or HAVING clause statement Maximum number of columns in a GROUP BY clause 120 Maximum total length of columns in a GROUP BY clause 32766 Maximum number of columns in an ORDER BY clause 32766 Maximum total length of columns in an ORDER BY clause 32766 Maximum levels allowed for a subselect 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 102495 Maximum number of parameters in a function 90 Maximum number of nested levels for routines storage Applications Maximum number of host variable declarations in a precompiled program storage96 Maximum length of a host variable (in bytes) 2 147 483 647 Maximum number of declared cursors in a program storage Maximum number of rows changed in a unit of work 500 000 000 Maximum number of cursors opened at one time storage 97 Maximum number of locators in a transaction 16 000 000 98 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 99 Maximum size of a package 500 megabytes100 Maximum length of a path 3483101 Maximum number of schemas in a path 268 Maximum length of a password 128 Maximum length of a password hint 32
83. As an application requester, DB2 for i5/OS can send an authorization name of up to 255 bytes.84. For REXX procedures, the limit is 33.85. For a C program, the limit is 128.86. The values shown are approximate.87. If the column is NOT NULL, the maximum is one more.88. For sequences the limit is 500.89. The maximum number of schemas is related to the maximum number of dependents. Each schema creates several catalog views. These views are provided for users and not used by the database manager. Deleting these views will allow many more than 4740 schemas.90. In a query result set, the maximum length of a row without LOBs is 64K. If the row exceeds 64K, large varying length columns may be converted to LOBs to allow the query to run.91. The maximum number of members (and partitions) referenced is also 256.92. The maximum number of dependents may be less if a table is referenced more than once in a view.93. 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.94. The limit is based on the size of internal structures generated for the parsed SQL statement.95. 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.96. 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.97. The maximum number of cursors open at one time in a single job is approximately 21 754.98. The maximum number of locators in a transaction in SQL Server mode is 209 000.99. The maximum number of allocated handles per DRDA® connection is 500.100. The maximum size can be increased to 1 gigabyte by using a QAQQINI option.101. The maximum length of a path in DRDA is 255.
- PARAMETER STYLE GENERAL has a maximum of 1024.
- PARAMETER STYLE GENERAL WITH NULLS has a maximum of 1023.
- PARAMETER STYLE SQL or PARAMETER STYLE DB2SQL has a maximum of 508.
- PARAMETER STYLE JAVA or PARAMETER STYLE DB2GENERAL has a maximum of 90.
[ Top of Page | Previous Page | Next Page | Contents |
Index ]