db2 V8的有关SQL limits

上一篇 / 下一篇 2008-08-06 10:34:35 / 个人分类：db2

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 7. Identifier Length Limits
Description	Limit in Bytes
Longest authorization name (can only be single-byte characters)	30
Longest constraint name	18
Longest correlation name	128
Longest condition name	64
Longest cursor name	18
Longest unqualified column name (nickname)	128
Longest data source index name	128
Longest data source name	128
Longest data source table name (`remote-table-name`)	128
Longest external program name	8
Longest host identifier^a	255
Longest identifier of a data source user (`remote-authorization-name`)	30
Longest label name	64
Longest method name	18
Longest parameter name^b	128
Longest password to access a data source	32
Longest savepoint name	128
Longest schema name^c	30
Longest server (database alias) name	8
Longest SQL variable name	64
Longest statement name	18
Longest transform. group name	18
Longest unqualified column name	30
Longest unqualified package name	8
Longest unqualified user-defined type, user-defined function, user-defined method, buffer pool, table space, database partition group, trigger, index, or index specification name	18
Longest unqualified table name, view name, stored procedure name, sequence name, nickname, or alias	128
Longest wrapper name	128
Notes: ^aIndividual host language compilers may have a more restrictive limit on variable names. ^bParameter names in an SQL procedure are limited to 64 bytes. ^cThe schema name for a user-defined type is limited to 8 bytes.

Table 8. Numeric Limits
Description	Limit
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
Smallest SMALLINT value	-32 768
Largest SMALLINT value	+32 767
Largest decimal precision	31
Smallest DOUBLE value	-1.79769E+308
Largest DOUBLE value	+1.79769E+308
Smallest positive DOUBLE value	+2.225E-307
Largest negative DOUBLE value	-2.225E-307
Smallest REAL value	-3.402E+38
Largest REAL value	+3.402E+38
Smallest positive REAL value	+1.175E-37
Largest negative REAL value	-1.175E-37

Table 9. String Limits
Description	Limit
Maximum length of CHAR (in bytes)	254
Maximum length of VARCHAR (in bytes)	32 672
Maximum length of LONG VARCHAR (in bytes)	32 700
Maximum length of CLOB (in bytes)	2 147 483 647
Maximum length of GRAPHIC (in characters)	127
Maximum length of VARGRAPHIC (in characters)	16 336
Maximum length of LONG VARGRAPHIC (in characters)	16 350
Maximum length of DBCLOB (in characters)	1 073 741 823
Maximum length of BLOB (in bytes)	2 147 483 647
Maximum length of character constant	32 672
Maximum length of graphic constant	16 336
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 hex constant digits	16 336
Maximum size of a catalog comment (in bytes)	254
Largest instance of a structured type column object at run time (in gigabytes)	1

Table 10. Datetime Limits
Description	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 11. Database Manager Limits
Description	Limit
Most columns in a table^g	1 012
Most columns in a view^a	5 000
Maximum length of a row including all overhead^b^g	32 677
Maximum size of a table per partition (in gigabytes)^c^g	512
Maximum size of an index per partition (in gigabytes)	512
Most rows in a table per partition	4 x 10⁹
Longest index key including all overhead (in bytes)	1 024
Most columns in an index key	16
Most indexes on a table	32 767 or storage
Most tables referenced in an SQL statement or a view	storage
Most host variable declarations in a precompiled program^c	storage
Most host variable references in an SQL statement	32 767
Longest host variable value used for insert or update (in bytes)	2 147 483 647
Longest SQL statement (in bytes)	72 097 152
Most elements in a select list^g	1 012
Most predicates in a WHERE or HAVING clause	storage
Maximum number of columns in a GROUP BY clause^g	1 012
Maximum total length of columns in a GROUP BY clause (in bytes)^g	32 677
Maximum number of columns in an ORDER BY clause^g	1 012
Maximum total length of columns in an ORDER BY clause (in bytes)^g	32 677
Maximum size of an SQLDA (in bytes)	storage
Maximum number of prepared statements	storage
Most declared cursors in a program	storage
Maximum number of cursors opened at one time	storage
Most tables in an SMS table space	65 534
Maximum number of constraints on a table	storage
Maximum level of subquery nesting	storage
Maximum number of subqueries in a single statement	storage
Most values in an INSERT statement^g	1 012
Most SET clauses in a single UPDATE statement^g	1 012
Most columns in a UNIQUE constraint (supported via a UNIQUE index)	16
Maximum combined length of columns in a UNIQUE constraint (supported via a UNIQUE index) (in bytes)	1 024
Most referencing columns in a foreign key	16
Maximum combined length of referencing columns in a foreign key (in bytes)	1 024
Maximum length of a check constraint specification (in bytes)	65 535
Maximum number of columns in a partitioning key^e	500
Maximum number of rows changed in a unit of work	storage
Maximum number of packages	storage
Most constants in a statement	storage
Maximum concurrent users of server^d	64 000
Maximum number of parameters in a stored procedure	32 767
Maximum number of parameters in a user defined function	90
Maximum run-time depth of cascading triggers	16
Maximum number of simultaneously active event monitors	32
Maximum size of a regular DMS table space (in gigabytes)^c^g	512
Maximum size of a long DMS table space (in terabytes)^c	2
Maximum size of a temporary DMS table space (in terabytes)^c	2
Maximum number of databases per instance concurrently in use	256
Maximum number of concurrent users per instance	64 000
Maximum number of concurrent applications per database	60 000
Maximum number of connections per process within a DB2 client	512
Maximum depth of cascaded triggers	16
Maximum partition number	999
Most table objects in DMS table space^f	51 000
Longest variable index key part (in bytes)^h	1022 or storage
Maximum number of columns in a data source table or view that is referenced by a nickname	5 000
Maximum NPAGES in a buffer pool for 32-bit releases	1 048 576
Maximum NPAGES in a buffer pool for 64-bit releases	2 147 483 647
Maximum total size of all buffer pool slots (4K)	2 147 483 646
Maximum number of nested levels for stored procedures	16
1Maximum number of table spaces in a database	132 768
Maximum number of attributes in a structured type	4082
Maximum number of simultaneously opened LOB locators in a transaction	32 100
Notes: ^aThis 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. ^bThe 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. ^cThe numbers shown are architectural limits and approximations. The practical limits may be less. ^dThe actual value will be the value of the MAXAGENTS configuration parameter. ^eThis is an architectural limit. The limit on the most columns in an index key should be used as the practical limit. ^fTable objects include data, indexes, LONG VARCHAR or 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. ^gFor page size-specific values, seeTable 12. ^hThis is limited only by the longest index key, including all overhead (in bytes). As the number of index key parts increases, the maximum length of each key part decreases.

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