The following tables describe certain SQL limits. Adhering to the
most restrictive case can help the programmer design application programs that
are easily portable.
|
| Description
| Limit in Bytes
|
| 1
| Longest authorization name (can only be single-byte characters)
| 8
|
| 2
| Longest constraint name
| 18
|
| 3
| Longest correlation name
| 18
|
| 4
| Longest cursor name
| 18
|
| 5
| Longest external program name
| 8
|
| 6
| Longest host identifier a
| 255
|
| 7
| Longest schema name
| 8
|
| 8
| Longest server (database alias) name
| 8
|
| 9
| Longest statement name
| 18
|
| 10
| Longest unqualified column name
| 18
|
| 11
| Longest unqualified package name
| 8
|
| 12
| Longest unqualified user-defined type, user-defined function, stored
procedure, buffer pool, table space, nodegroup, table, view, alias, trigger or
index name.
| 18
|
| Notes: |
- a
- Individual host language compilers may have a more restrictive limit on
variable names.
|
|
|
| Description
| Limit
|
| 1
| Maximum length of CHAR (in bytes)
| 254
|
| 2
| Maximum length of VARCHAR (in bytes)
| 4 000
|
| 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)
| 2 000
|
| 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
| 4 000
|
| 11
| Maximum length of graphic constant
| 2 000
|
| 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
| 4 000
|
| 16
| Maximum size of a catalog comment (in bytes)
| 254
|
|
| 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
| 8 101
|
| 4
| Maximum size of a table per partition (in gigabytes) c g
| 128
|
| 5
| Maximum size of an index per partition (in gigabytes)
| 64
|
| 6
| Most rows in a table per partition
| 4 x 109
|
| 7
| Longest index key including all overhead (in bytes)
| 255
|
| 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)
| 32 765
|
| 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
| 8 101
|
| 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
| 8 101
|
| 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)
| 255
|
| 33
| Most referencing columns in a foreign key
| 16
|
| 34
| Maximum combined length of referencing columns in a foreign key (in
bytes)
| 255
|
| 35
| Maximum length of a check constraint specification (in bytes)
| 32765
|
| 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
| 128
|
| 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 g
| 13 305
|
| 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 28.
|
|
