Appendix A. SQL limits

The following tables describe certain SQL and database limits imposed by the DB2 UDB for iSeries database manager.

Note:
Table 77. Identifier Length Limits
Identifier Limits DB2 UDB for iSeries Limit
Longest authorization name 1084
Longest correlation name 128
Longest cursor name 18
Longest descriptor name 128
Longest external program name (string form) 27985
Longest external program name (unqualified form) 10
Longest host identifier86 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 UDB for iSeries 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 value87 -1.79x10308
Largest DOUBLE value87 +1.79x10308
Smallest positive DOUBLE value87 +2.23x10-308
Largest negative DOUBLE value87 -2.23x10-308
Smallest REAL value87 -3.4x1038
Largest REAL value87 +3.4x1038
Smallest positive REAL value87 +1.18x10-38
Largest negative REAL value87 -1.18x10-38

 

Table 79. String Limits
String Limits DB2 UDB for iSeries Limit
Maximum length of CHAR (in bytes) 3276588
Maximum length of VARCHAR (in bytes) 3273988
Maximum length of CLOB (in bytes) 2 147 483 647
Maximum length of GRAPHIC (in double-byte characters) 1638288
Maximum length of VARGRAPHIC (in double-byte characters) 1636988
Maximum length of DBCLOB (in double-byte characters) 1 073 741 823
Maximum length of BINARY (in bytes) 3276588
Maximum length of VARBINARY (in bytes) 3273988
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 200089
Maximum length of column label 60
Longest SQL routine label 128
Longest table, package, or alias label 50
Maximum length of C NUL-terminated 3273988
Maximum length of C NUL-terminated graphic 1636988

 

Table 80. Datetime Limits
Datetime Limits DB2 UDB for iSeries 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 UDB for iSeries Limit
Maximum length of DATALINK 32718
Maximum length of DATALINK comment 254
Table 82. Database Manager Limits
Database Manager Limits DB2 UDB for iSeries Limit
Most columns in a table 8000
Most 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 parameters in a function 90
Maximum number of parameters in a procedure 102490
Maximum size of a non-partitioned table 1.7 terabytes
Maximum size of an index 1 terabyte
Most rows in a non-partitioned table 4 294 967 288
Longest index key 3276788
Most columns in an index key 120
Most indexes on a table approximately 4000
Most tables referenced in an SQL statement 100091
Most tables referenced in a view or materialized query table 25691
Most host variable declarations in a precompiled program storage92
Most host variables and constants in an SQL statement 409693
Longest host variable used for insert or update (in bytes) 2 147 483 647
Longest SQL statement (in bytes) 2 097 152
Longest CHECK constraint (in bytes) statement
Most elements in a select list94 approximately 8000
Most 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 size of an SQLDA 16 777 215
Maximum number of prepared statements storage
Most declared cursors in a program storage
Maximum number of cursors opened at one time storage 95
Most tables in a relational database storage
Maximum number of triggers on a table 300
Maximum number of nested trigger invocations 200
Maximum length of a password 128
Maximum number of constraints on a table 300
Maximum length of a path 348396
Maximum number of schemas in a path 268
Maximum length of a hint 32
Maximum number of objects in a schema approximately 360 000
Maximum levels allowed for a subselect 256
Maximum number of rows changed in a unit of work 500 000 000
Maximum number of locators in a transaction 16 000 000 97
Maximum number of savepoints active at one time storage
Maximum number of simultaneously allocated CLI handles in a process 160 000 98
Maximum number of nodes in a nodegroup 32
Maximum number of partitions in a partitioned table 256
Maximum size of a package 500 megabytes99
Maximum number of dependent views, materialized query tables, and indexes on a table or view. 32767100
Maximum number of schemas 2370101

  

 


84.
As an application requester, DB2 UDB for iSeries can send an authorization name of up to 255 bytes.
85.
For REXX procedures, the limit is 33.
86.
For a C program, the limit is 128.
87.
The values shown are approximate.
88.
If the column is NOT NULL, the maximum is one more.
89.
For sequences the limit is 500.
90.
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.
91.
The maximum number of members (and partitions) referenced is also 256.
92.
In RPG/400(R) 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.
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.
The maximum number of cursors open at one time in a single job is approximately 21 754.
96.
The maximum length of a path in DRDA(R) is 255.
97.
The maximum number of locators in a transaction in SQL Server mode is 209 000.
98.
The maximum number of allocated handles per DRDA connection is 500.
99.
The maximum size can be increased to 1 gigabyte by using a QAQQINI option.
100.
The maximum number of dependents may be less if a table is referenced more than once in a view.
101.
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 2370 schemas.