SQL Reference

Appendix A. SQL Limits

The following tables describe certain SQL limits. Adhering to the most restrictive case can help the programmer design application programs that are easily portable.

Table 29. Identifier Length Limits
  Description Limit in Bytes
1 Longest authorization name (can only be single-byte characters) 30
2 Longest constraint name 18
3 Longest correlation name 128
4 Longest condition name 64
5 Longest cursor name 18
6 Longest data source column name 128
7 Longest data source index name 128
8 Longest data source name 128
9 Longest data source table name (remote-table-name) 128
10 Longest external program name 8
11 Longest host identifier a 255
12 Longest identifier of a data source user (remote-authorization-name) 30
13 Longest label name 64
14 Longest method name 18
15 Longest parameter nameb 128
16 Longest password to access a data source 32
17 Longest savepoint name 128
18 Longest schema name c 30
19 Longest server (database alias) name 8
20 Longest SQL variable name 64
21 Longest statement name 18
22 Longest transform group name 18
23 Longest unqualified column name 30
24 Longest unqualified package name 8
25 Longest unqualified user-defined type, user-defined function, buffer pool, table space, nodegroup, trigger, index, or index specification name 18
26 Longest unqualified table name, view name, stored procedure, nickname, or alias 128
27 Longest wrapper name 128
Notes:

a
Individual host language compilers may have a more restrictive limit on variable names.

b
Parameter names in an SQL procdure are limited to 64 bytes.

c
The schema name for a user-defined structured type is limited to 8 bytes.

Table 30. Numeric Limits
  Description Limit
1 Smallest INTEGER value -2 147 483 648
2 Largest INTEGER value +2 147 483 647
3 Smallest BIGINT value -9 223 372 036 854 775 808
4 Largest BIGINT value +9 223 372 036 854 775 807
5 Smallest SMALLINT value -32 768
6 Largest SMALLINT value +32 767
7 Largest decimal precision 31
8 Smallest DOUBLE value -1.79769E+308
9 Largest DOUBLE value +1.79769E+308
10 Smallest positive DOUBLE value +2.225E-307
11 Largest negative DOUBLE value -2.225E-307
12 Smallest REAL value -3.402E+38
13 Largest REAL value +3.402E+38
14 Smallest positive REAL value +1.175E-37
15 Largest negative REAL value -1.175E-37

Table 31. String Limits
  Description Limit
1 Maximum length of CHAR (in bytes) 254
2 Maximum length of VARCHAR (in bytes) 32 672
3 Maximum length of LONG VARCHAR (in bytes) 32 700
4 Maximum length of CLOB (in bytes) 2 147 483 647
5 Maximum length of GRAPHIC (in characters) 127
6 Maximum length of VARGRAPHIC (in characters) 16 336
7 Maximum length of LONG VARGRAPHIC (in characters) 16 350
8 Maximum length of DBCLOB (in characters) 1 073 741 823
9 Maximum length of BLOB (in bytes) 2 147 483 647
10 Maximum length of character constant 32 672
11 Maximum length of graphic constant 16 336
12 Maximum length of concatenated character string 2 147 483 647
13 Maximum length of concatenated graphic string 1 073 741 823
14 Maximum length of concatenated binary string 2 147 483 647
15 Maximum number of hex constant digits 16 336
16 Maximum size of a catalog comment (in bytes) 254
17 Largest instance of a structured type column object at runtime 1 GB

Table 32. Datetime Limits
  Description Limit
1 Smallest DATE value 0001-01-01
2 Largest DATE value 9999-12-31
3 Smallest TIME value 00:00:00
4 Largest TIME value 24:00:00
5 Smallest TIMESTAMP value 0001-01-01-00.00.00.000000
6 Largest TIMESTAMP value 9999-12-31-24.00.00.000000

Table 33. Database Manager Limits
  Description Limit
1 Most columns in a table g 1 012
2 Most columns in a view a 5 000
3 Maximum length of a row including all overhead b g 32 677
4 Maximum size of a table per partition (in gigabytes) c g 512
5 Maximum size of an index per partition (in gigabytes) 512
6 Most rows in a table per partition 4 x 109
7 Longest index key including all overhead (in bytes) 1 024
8 Most columns in an index key 16
9 Most indexes on a table 32 767 or storage
10 Most tables referenced in an SQL statement or a view storage
11 Most host variable declarations in a precompiled program c storage
12 Most host variable references in an SQL statement 32 767
13 Longest host variable value used for insert or update (in bytes) 2 147 483 647
14 Longest SQL statement (in bytes) 65 535
15 Most elements in a select listg 1 012
16 Most predicates in a WHERE or HAVING clause storage
17 Maximum number of columns in a GROUP BY clause g 1 012
18 Maximum total length of columns in a GROUP BY clause (in bytes)g 32 677
19 Maximum number of columns in an ORDER BY clause g 1 012
20 Maximum total length of columns in an ORDER BY clause (in bytes) g 32 677
21 Maximum size of an SQLDA (in bytes) storage
22 Maximum number of prepared statements storage
23 Most declared cursors in a program storage
24 Maximum number of cursors opened at one time storage
25 Most tables in an SMS table space 65 534
26 Maximum number of constraints on a table storage
27 Maximum level of subquery nesting storage
28 Maximum number of subqueries in a single statement storage
29 Most values in an INSERT statement g 1 012
30 Most SET clauses in a single UPDATE statement g 1 012
31 Most columns in a UNIQUE constraint (supported via a UNIQUE index) 16
32 Maximum combined length of columns in a UNIQUE constraint (supported via a UNIQUE index) (in bytes) 1 024
33 Most referencing columns in a foreign key 16
34 Maximum combined length of referencing columns in a foreign key (in bytes) 1 024
35 Maximum length of a check constraint specification (in bytes) 65 535
36 Maximum number of columns in a partitioning key e 500
37 Maximum number of rows changed in a unit of work storage
38 Maximum number of packages storage
39 Most constants in a statement storage
40 Maximum concurrent users of server d 64 000
41 Maximum number of parameters in a stored procedure 32 767
42 Maximum number of parameters in a user defined function 90
43 Maximum run-time depth of cascading triggers 16
44 Maximum number of simultaneously active event monitors 32
45 Maximum size of a regular DMS table space (in gigabytes)c g 512
46 Maximum size of a long DMS table space (in terabytes)c 2
47 Maximum size of a temporary DMS table space (in terabytes)c 2
48 Maximum number of databases per instance concurrently in use 256
49 Maximum number of concurrent users per instance 64 000
50 Maximum number of concurrent applications per database 1 000
51 Maximum depth of cascaded triggers 16
52 Maximum partition number 999
53 Most table objects in DMS table space f 51 000
54 Longest variable index key part (in bytes) 255
55 Maximum number of columns in a data source table or view that is referenced by a nickname 5 000
56 Maximum NPAGES in a bufferpool for 32 bit releases 524 288
57 Maximum NPAGES in a bufferpool for 64 bit releases 2 147 483 647
58 Maximum number of nested levels for stored procedures 16
59 Maximum number of tablespaces in a database 4096
60 Maximum number of attributes in a structured type 4082
Notes:

a
This maximum can be achieved using a join in the CREATE VIEW statement. Selecting from such a view is subject to the limit of most elements in a select list.

b
The actual data for BLOB, CLOB, LONG VARCHAR, DBCLOB, and LONG VARGRAPHIC columns is not included in this count. However information about the location of that data does take up some space in the row.

c
The numbers shown are architectural limits and approximations. The practical limits may be less.

d
The actual value will be the value of the MAXAGENTS configuration parameter. See the Administration Guide for information on MAXAGENTS.

e
This is an architectural limit. The limit on the most columns in an index key should be used as a practical limit.

f
Table objects include data, indexes, LONG VARCHAR/VARGRAPHIC columns, and LOB columns. Table objects that are in the same table space as the table data do not count extra toward the limit. However, each table object that is in a different table space than the table data does contribute one toward the limit for each table object type per table in the table space in which the table object resides.

g
For page size specific values, please refer to Table 34.

Table 34. Database Manager Page Size Specific Limits
  Description 4K page size limit 8K page size limit 16K page size limit 32K page size limit
1 Most columns in a table 500 1 012 1 012 1 012
3 Maximum length of a row including all overhead 4 005 8 101 16 293 32 677
4 Maximum size of a table per partition (in gigabytes) 64 128 256 512
5 Maximum size of an index per partition (in gigabytes) 64 128 256 512
15 Most elements in a select list 500 1 012 1 012 1 012
17 Maximum number of columns in a GROUP BY clause 500 1 012 1 012 1 012
18 Maximum total length of columns in a GROUP BY clause (in bytes) 4 005 8 101 16 293 32 677
19 Maximum number of columns in an ORDER BY clause 500 1 012 1 012 1 012
20 Maximum total length of columns in an ORDER BY clause (in bytes) 4 005 8 101 16 293 32 677
29 Most values in an INSERT statement 500 1 012 1 012 1 012
30 Most SET clauses in a single UPDATE statement 500 1 012 1 012 1 012
45 Maximum size of a regular DMS table space (in gigabytes) 64 128 256 512

[ Top of Page | Previous Page | Next Page ]