db2 V8的有关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 7. Identifier Length LimitsDescriptionLimit in BytesLongest authorization name (can only be single-byte characters)30Longest constraint name18Longest correlation name128Longest condition name64Longest cursor name18Longest unqualified column name (nickname)128Longest data source index name128Longest data source name128Longest data source table name (remote-table-name)128Longest external program name8Longest host identifier^a255Longest identifier of a data source user (remote-authorization-name)30Longest label name64Longest method name18Longest parameter name^b128Longest password to access a data source32Longest savepoint name128Longest schema name^c30Longest server (database alias) name8Longest SQL variable name64Longest statement name18Longest transform. group name18Longest unqualified column name30Longest unqualified package name8Longest unqualified user-defined type, user-defined function, user-defined method, buffer pool, table space, database partition group, trigger, index, or index specification name18Longest unqualified table name, view name, stored procedure name, sequence name, nickname, or alias128Longest wrapper name128

Notes:

1. ^aIndividual host language compilers may have a more restrictive limit on variable names.
2. ^bParameter names in an SQL procedure are limited to 64 bytes.
3. ^cThe schema name for a user-defined type is limited to 8 bytes.

Table 8. Numeric LimitsDescriptionLimitSmallest INTEGER value-2 147 483 648Largest INTEGER value+2 147 483 647Smallest BIGINT value-9 223 372 036 854 775 808Largest BIGINT value+9 223 372 036 854 775 807Smallest SMALLINT value-32 768Largest SMALLINT value+32 767Largest decimal precision31Smallest DOUBLE value-1.79769E+308Largest DOUBLE value+1.79769E+308Smallest positive DOUBLE value+2.225E-307Largest negative DOUBLE value-2.225E-307Smallest REAL value-3.402E+38Largest REAL value+3.402E+38Smallest positive REAL value+1.175E-37Largest negative REAL value-1.175E-37

Table 9. String LimitsDescriptionLimitMaximum length of CHAR (in bytes)254Maximum length of VARCHAR (in bytes)32 672Maximum length of LONG VARCHAR (in bytes)32 700Maximum length of CLOB (in bytes)2 147 483 647Maximum length of GRAPHIC (in characters)127Maximum length of VARGRAPHIC (in characters)16 336Maximum length of LONG VARGRAPHIC (in characters)16 350Maximum length of DBCLOB (in characters)1 073 741 823Maximum length of BLOB (in bytes)2 147 483 647Maximum length of character constant32 672Maximum length of graphic constant16 336Maximum length of concatenated character string2 147 483 647Maximum length of concatenated graphic string1 073 741 823Maximum length of concatenated binary string2 147 483 647Maximum number of hex constant digits16 336Maximum size of a catalog comment (in bytes)254Largest instance of a structured type column object at run time (in gigabytes)1

Table 10. Datetime LimitsDescriptionLimitSmallest DATE value0001-01-01Largest DATE value9999-12-31Smallest TIME value00:00:00Largest TIME value24:00:00Smallest TIMESTAMP value0001-01-01-00.00.00.000000Largest TIMESTAMP value9999-12-31-24.00.00.000000

Table 11. Database Manager LimitsDescriptionLimitMost columns in a table^g1 012Most columns in a view^a5 000Maximum length of a row including all overhead^bg32 677Maximum size of a table per partition (in gigabytes)^cg512Maximum size of an index per partition (in gigabytes)512Most rows in a table per partition4 x 10⁹Longest index key including all overhead (in bytes)1 024Most columns in an index key16Most indexes on a table32 767 or storageMost tables referenced in an SQL statement or a viewstorageMost host variable declarations in a precompiled program^cstorageMost host variable references in an SQL statement32 767Longest host variable value used for insert or update (in bytes)2 147 483 647Longest SQL statement (in bytes)72 097 152Most elements in a select list^g1 012Most predicates in a WHERE or HAVING clausestorageMaximum number of columns in a GROUP BY clause^g1 012Maximum total length of columns in a GROUP BY clause (in bytes)^g32 677Maximum number of columns in an ORDER BY clause^g1 012Maximum total length of columns in an ORDER BY clause (in bytes)^g32 677Maximum size of an SQLDA (in bytes)storageMaximum number of prepared statementsstorageMost declared cursors in a programstorageMaximum number of cursors opened at one timestorageMost tables in an SMS table space65 534Maximum number of constraints on a tablestorageMaximum level of subquery nestingstorageMaximum number of subqueries in a single statementstorageMost values in an INSERT statement^g1 012Most SET clauses in a single UPDATE statement^g1 012Most columns in a UNIQUE constraint (supported via a UNIQUE index)16Maximum combined length of columns in a UNIQUE constraint (supported via a UNIQUE index) (in bytes)1 024Most referencing columns in a foreign key16Maximum combined length of referencing columns in a foreign key (in bytes)1 024Maximum length of a check constraint specification (in bytes)65 535Maximum number of columns in a partitioning key^e500Maximum number of rows changed in a unit of workstorageMaximum number of packagesstorageMost constants in a statementstorageMaximum concurrent users of server^d64 000Maximum number of parameters in a stored procedure32 767Maximum number of parameters in a user defined function90Maximum run-time depth of cascading triggers16Maximum number of simultaneously active event monitors32Maximum size of a regular DMS table space (in gigabytes)^cg512Maximum size of a long DMS table space (in terabytes)^c2Maximum size of a temporary DMS table space (in terabytes)^c2Maximum number of databases per instance concurrently in use256Maximum number of concurrent users per instance64 000Maximum number of concurrent applications per database60 000Maximum number of connections per process within a DB2 client512Maximum depth of cascaded triggers16Maximum partition number999Most table objects in DMS table space^f51 000Longest variable index key part (in bytes)^h1022 or storageMaximum number of columns in a data source table or view that is referenced by a nickname5 000Maximum NPAGES in a buffer pool for 32-bit releases1 048 576Maximum NPAGES in a buffer pool for 64-bit releases2 147 483 647Maximum total size of all buffer pool slots (4K)2 147 483 646Maximum number of nested levels for stored procedures161Maximum number of table spaces in a database132 768Maximum number of attributes in a structured type4082Maximum number of simultaneously opened LOB locators in a transaction32 100

Notes:

1. ^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.
2. ^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.
3. ^cThe numbers shown are architectural limits and approximations. The practical limits may be less.
4. ^dThe actual value will be the value of the MAXAGENTS configuration parameter.
5. ^eThis is an architectural limit. The limit on the most columns in an index key should be used as the practical limit.
6. ^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.
7. ^gFor page size-specific values, seeTable 12.
8. ^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 LimitsDescription4K page size limit8K page size limit16K page size limit32K page size limitMost columns in a table5001 0121 0121 012Maximum length of a row including all overhead4 0058 10116 29332 677Maximum size of a table per partition (in gigabytes)64128256512Maximum size of an index per partition (in gigabytes)64128256512Most elements in a select list5001 0121 0121 012Maximum number of columns in a GROUP BY clause5001 0121 0121 012Maximum total length of columns in a GROUP BY clause (in bytes)4 0058 10116 29332 677Maximum number of columns in an ORDER BY clause5001 0121 0121 012Maximum total length of columns in an ORDER BY clause (in bytes)4 0058 10116 29332 677Most values in an INSERT statement5001 0121 0121 012Most SET clauses in a single UPDATE statement5001 0121 0121 012Maximum size of a regular DMS table space (in gigabytes)64128256512