Maximum parameters of the database
1.Introduction.
2. Limits Associated with Database Objects in NDB Cluster.
3.Table of limits in Sql.
1.SQL or Structured Query Language is a set of instructions used for interacting with relational databases. As a matter of fact, it is the only language that is understood by most of databases. SQL statements are used basically for performing tasks such as updating data in a database or retrieving data from the database. The common RDBM systems that use SQL are Microsoft SQL Server, Oracle, Access, Ingres, and Sybase. SQL is composed of three major components called the Data Definition Language, Data Manipulation Language and Data Control Language. RDBMS consists of one or more objects known as tables where data or information is stored. You can use select statements along with specified conditions to retrieve desired data from your tables. By default, all records that satisfy those conditions are returned. However, you might just want a subset of records. In SQL, this can be accomplished using the LIMIT statement.
Use of the Limit keyword in MySQL:
Limit is used in MySQL Select statement to specifically limit query results to those that lie within a particular range. It can accept one or two arguments and the value of those arguments should be either zero or positive integer constants.
LIMIT clause with single argument:
The general syntax for Limit keyword is as follows:
[SQL statement] LIMIT[N]
Here, N is the number of records that needs to be retrieved. Note that with the limit keyword, the order by clause is usually included in an SQL query. If a single argument is used, it will specify the maximum number of rows to return from the start of the result set. Without the order by clause, the fetched results are dependent on the database defaults.
Example:
Let’s create a table named “store_table” for this example.
2.Some database objects such as tables and indexes have different limitations when using the NDBCLUSTER storage engine:
Database and table names. When using the NDB storage engine, the maximum allowed length both for database names and for table names is 63 characters. A statement using a database name or table name longer than this limit fails with an appropriate error.
Number of database objects. The maximum number of all NDB database objects in a single NDB Cluster—including databases, tables, and indexes—is limited to 20320.
Attributes per table. The maximum number of attributes (that is, columns and indexes) that can belong to a given table is 512.
Attributes per key. The maximum number of attributes per key is 32.
Row size. The maximum permitted size of any one row is 14000 bytes.
Each BLOB or TEXT column contributes 256 + 8 = 264 bytes to this total; this includes JSON columns. See String Type Storage Requirements, as well as JSON Storage Requirements, for more information relating to these types.
In addition, the maximum offset for a fixed-width column of an NDB table is 8188 bytes; attempting to create a table that violates this limitation fails with NDB error 851 Maximum offset for fixed-size columns exceeded. For memory-based columns, you can work around this limitation by using a variable-width column type such as VARCHAR or defining the column as COLUMN_FORMAT=DYNAMIC; this does not work with columns stored on disk. For disk-based columns, you may be able to do so by reordering one or more of the table's disk-based columns such that the combined width of all but the disk-based column defined last in the CREATE TABLE statement used to create the table does not exceed 8188 bytes, less any possible rounding performed for some data types such as CHAR or VARCHAR; otherwise it is necessary to use memory-based storage for one or more of the offending column or columns instead.
BIT column storage per table. The maximum combined width for all BIT columns used in a given NDB table is 4096.
FIXED column storage. NDB Cluster 7.5 and later supports a maximum of 128 TB per fragment of data in FIXED columns. (Previously, this was 16 GB.)
DB2 Version 9.7 for Linux, UNIX, and Windows
SQL and XML limits
The following tables describe certain SQL and XML limits. Adhering to the most restrictive case can help you to design application programs that are easily portable.
Table 1 lists limits in bytes. These limits are enforced after conversion from the application code page to the database code page when creating identifiers. The limits are also enforced after conversion from the database code page to the application code page when retrieving identifiers from the database. If, during either of these processes, the identifier length limit is exceeded, truncation occurs or an error is returned.
Character limits vary depending on the code page of the database and the code page of the application. For example, because the width of a UTF-8 character can range from 1 to 4 bytes, the character limit for an identifier in a Unicode table whose limit is 128 bytes will range from 32 to 128 characters, depending on which characters are used. If an attempt is made to create an identifier whose name is longer than the limit for this table after conversion to the database code page, an error is returned.
Applications that store identifier names must be able to handle the potentially increased size of identifiers after code page conversion has occurred. When identifiers are retrieved from the catalog, they are converted to the application code page. Conversion from the database code page to the application code page can result in an identifier becoming longer than the byte limit for the table. If a host variable declared by the application cannot store the entire identifier after code page conversion, it is truncated. If that is unacceptable, the host variable can be increased in size to be able to accept the entire identifier name.
The same rules apply to DB2® utilities retrieving data and converting it to a user-specified code page. If a DB2 utility, such as export, is retrieving the data and forcing conversion to a user-specified code page (using the export CODEPAGE modifier or the DB2CODEPAGE registry variable), and the identifier expands beyond the limit that is documented in this table because of code page conversion, an error might be returned or the identifier might be truncated.
Table 1. Identifier Length Limits
|
Description
|
Maximum in Bytes
|
Alias name
|
128
|
Attribute name
|
128
|
Audit policy name
|
128
|
Authorization name (can only be single-byte characters)
|
128
|
Buffer pool name
|
18
|
Column name2
|
128
|
Constraint name
|
128
|
Correlation name
|
128
|
Cursor name
|
128
|
Data partition name
|
128
|
Data source column name
|
255
|
Data source index name
|
128
|
Data source name
|
128
|
Data source table name (remote-table-name)
|
128
|
Database partition group name
|
128
|
Database partition name
|
128
|
Event monitor name
|
128
|
External program name
|
128
|
Function mapping name
|
128
|
Group name
|
128
|
Host identifier1
|
255
|
Identifier for a data source user (remote-authorization-name)
|
128
|
Identifier in an SQL procedure (condition name, for loop identifier, label, result set locator, statement name, variable name)
|
128
|
Index name
|
128
|
Index extension name
|
18
|
Index specification name
|
128
|
Label name
|
128
|
Namespace uniform resource identifier (URI)
|
1000
|
Nickname
|
128
|
Package name
|
128
|
Package version ID
|
64
|
Parameter name
|
128
|
Password to access a data source
|
32
|
Procedure name
|
128
|
Role name
|
128
|
Savepoint name
|
128
|
Schema name2,3
|
128
|
Security label component name
|
128
|
Security label name
|
128
|
Security policy name
|
128
|
Sequence name
|
128
|
Server (database alias) name
|
8
|
Specific name
|
128
|
SQL condition name
|
128
|
SQL variable name
|
128
|
Statement name
|
128
|
Table name
|
128
|
Table space name
|
18
|
Transform group name
|
18
|
Trigger name
|
128
|
Trusted context name
|
128
|
Type mapping name
|
18
|
User-defined function name
|
128
|
User-defined method name
|
128
|
User-defined type name2
|
128
|
View name
|
128
|
Wrapper name
|
128
|
XML element name, attribute name, or prefix name
|
1000
|
XML schema location uniform resource identifier (URI)
|
1000
|
Note
Individual host language compilers might have a more restrictive limit on variable names.
The SQLDA structure is limited to storing 30-byte column names, 18-byte user-defined type names, and 8-byte schema names for user-defined types. Because the SQLDA is used in the DESCRIBE statement, embedded SQL applications that use the DESCRIBE statement to retrieve column or user-defined type name information must conform to these limits.
Schema names that are shorter than 8-bytes are padded with blanks and stored in the catalog as 8-byte names.
|
Table 2. Numeric Limits
|
Description
|
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
|
31
|
Maximum exponent (Emax) for REAL values
|
38
|
Smallest REAL value
|
-3.402E+38
|
Largest REAL value
|
+3.402E+38
|
Minimum exponent (Emin) for REAL values
|
-37
|
Smallest positive REAL value
|
+1.175E-37
|
Largest negative REAL value
|
-1.175E-37
|
Maximum exponent (Emax) for DOUBLE values
|
308
|
Smallest DOUBLE value
|
-1.79769E+308
|
Largest DOUBLE value
|
+1.79769E+308
|
Minimum exponent (Emin) for DOUBLE values
|
-307
|
Smallest positive DOUBLE value
|
+2.225E-307
|
Largest negative DOUBLE value
|
-2.225E-307
|
Maximum exponent (Emax) for DECFLOAT(16) values
|
384
|
Smallest DECFLOAT(16) value1
|
-9.999999999999999E+384
|
Largest DECFLOAT(16) value
|
9.999999999999999E+384
|
Minimum exponent (Emin) for DECFLOAT(16) values
|
-383
|
Smallest positive DECFLOAT(16) value
|
1.000000000000000E-383
|
Largest negative DECFLOAT(16) value
|
-1.000000000000000E-383
|
Maximum exponent (Emax) for DECFLOAT(34) values
|
6144
|
Smallest DECFLOAT(34) value1
|
-9.999999999999999999999999999999999E+6144
|
Largest DECFLOAT(34) value
|
9.999999999999999999999999999999999E+6144
|
Minimum exponent (Emin) for DECFLOAT(34) values
|
-6143
|
Smallest positive DECFLOAT(34) value
|
1.000000000000000000000000000000000E-6143
|
Largest negative DECFLOAT(34) value
|
-1.000000000000000000000000000000000E-6143
|
Note
These are the limits of normal decimal floating-point numbers. Valid decimal floating-point values include the special values NAN, -NAN, SNAN, -SNAN, INFINITY and -INFINITY. In addition, valid values include subnormal numbers.
Subnormal numbers are nonzero numbers whose adjusted exponents are less than Emin. For a subnormal number, the minimum value of the exponent is Emin - (precision-1), called Etiny, where precision is the working precision (16 or 34). That is, subnormal numbers extend the range of numbers close to zero by 15 or 33 orders of magnitude for DECFLOAT(16) or DECFLOAT(34), respectively. Subnormal numbers are different from normal numbers because the maximum number of digits for a subnormal number is less than the working precision (16 or 34). Decimal floating-point cannot represent the subnormal numbers with the same accuracy as it can represent normal numbers. The smallest positive subnormal number for DECFLOAT(34) is 1x10-6176, which contains only one digit, whereas the smallest positive normal number for DECFLOAT(34) is 1.000000000000000000000000000000000x10-6143, which contains 34 digits. The smallest positive subnormal number for DECFLOAT(16) is 1x10-398.
|
Table 3. 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)1
|
32 700
|
Maximum length of CLOB (in bytes)
|
2 147 483 647
|
Maximum length of serialized XML (in bytes)
|
2 147 483 647
|
Maximum length of GRAPHIC (in double-byte characters)
|
127
|
Maximum length of VARGRAPHIC (in double-byte characters)
|
16 336
|
Maximum length of LONG VARGRAPHIC (in double-byte characters)1
|
16 350
|
Maximum length of DBCLOB (in double-byte 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 hexadecimal constant digits
|
32 672
|
Largest instance of a structured type column object at run time (in gigabytes)
|
1
|
Maximum size of a catalog comment (in bytes)
|
254
|
Note
The LONG VARCHAR and LONG VARGRAPHIC data types are deprecated and might be removed in a future release.
|
Table 4. XML Limits
|
Description
|
Limit
|
Maximum depth of an XML document (in levels)
|
125
|
Maximum size of an XML schema document (in bytes)
|
31 457 280
|
Table 5. 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.000000000000
|
Largest TIMESTAMP value
|
9999-12-31-24.00.00.000000000000
|
Smallest timestamp precision
|
0
|
Largest timestamp precision
|
12
|
Table 6. Database Manager Limits
|
Description
|
Limit
|
Applications
|
Maximum number of host variable declarations in a precompiled program3
|
storage
|
Maximum length of a host variable value (in bytes)
|
2 147 483 647
|
Maximum number of declared cursors in a program
|
storage
|
Maximum number of rows changed in a unit of work
|
storage
|
Maximum number of cursors opened at one time
|
storage
|
Maximum number of connections per process within a DB2 client
|
512
|
Maximum number of simultaneously opened LOB locators in a transaction
|
4 194 304
|
Maximum size of an SQLDA (in bytes)
|
storage
|
Maximum number of prepared statements
|
storage
|
Buffer Pools
|
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
|
Concurrency
|
Maximum number of concurrent users of a server4
|
64 000
|
Maximum number of concurrent users per instance
|
64 000
|
Maximum number of concurrent applications per database
|
60 000
|
Maximum number of databases per instance concurrently in use
|
256
|
Constraints
|
Maximum number of constraints on a table
|
storage
|
Maximum number of columns in a UNIQUE constraint (supported through a UNIQUE index)
|
64
|
Maximum combined length of columns in a UNIQUE constraint (supported through a UNIQUE index, in bytes)9
|
8192
|
Maximum number of referencing columns in a foreign key
|
64
|
Maximum combined length of referencing columns in a foreign key (in bytes)9
|
8192
|
Maximum length of a check constraint specification (in bytes)
|
65 535
|
Databases
|
Maximum database partition number
|
999
|
Indexes
|
Maximum number of indexes on a table
|
32 767 or storage
|
Maximum number of columns in an index key
|
64
|
Maximum length of an index key including all overhead7 9
|
indexpagesize/4
|
Maximum length of a variable index key part (in bytes)8
|
1022 or storage
|
Maximum size of an index per database partition in an SMS table space (in terabytes)7
|
64
|
Maximum size of an index per database partition in a regular DMS table space (in gigabytes)7
|
512
|
Maximum size of an index per database partition in a large DMS table space (in terabytes)7
|
64
|
Maximum length of a variable index key part for an index over XML data (in bytes)7
|
pagesize/4 - 207
|
Log records
|
Maximum Log Sequence Number
|
0xFFFF FFFE FFFF FFEF
|
Monitoring
|
Maximum number of simultaneously active event monitors
|
128
|
With DB2 partitioned database environment, maximum number of simultaneously active GLOBAL event monitors
|
32
|
Routines
|
Maximum number of parameters in a procedure with LANGUAGE SQL
|
32 767
|
Maximum number of parameters in an external procedure with PROGRAM TYPE MAIN
|
32 767
|
Maximum number of parameters in an external procedure with PROGRAM TYPE SUB
|
90
|
Maximum number of parameters in a cursor value constructor
|
32 767
|
Maximum number of parameters in a user-defined function
|
90
|
Maximum number of nested levels for routines
|
64
|
Maximum number of schemas in the SQL path
|
64
|
Maximum length of the SQL path (in bytes)
|
2048
|
Maximum total size of local variables declared inside a routine (in gigabytes)3
|
4
|
Security
|
Maximum number of elements in a security label component of type set or tree
|
64
|
Maximum number of elements in a security label component of type array
|
65 535
|
Maximum number of security label components in a security policy
|
16
|
SQL
|
Maximum total length of an SQL statement (in bytes)
|
2 097 152
|
Maximum number of tables referenced in an SQL statement or a view
|
storage
|
Maximum number of host variable references in an SQL statement
|
32 767
|
Maximum number of constants in a statement
|
storage
|
Maximum number of elements in a select list7
|
1012
|
Maximum number of predicates in a WHERE or HAVING clause
|
storage
|
Maximum number of columns in a GROUP BY clause7
|
1012
|
Maximum total length of columns in a GROUP BY clause (in bytes)7
|
32 677
|
Maximum number of columns in an ORDER BY clause7
|
1012
|
Maximum total length of columns in an ORDER BY clause (in bytes)7
|
32 677
|
Maximum level of subquery nesting
|
storage
|
Maximum number of subqueries in a single statement
|
storage
|
Maximum number of values in an insert operation7
|
1012
|
Maximum number of SET clauses in a single update operation7
|
1012
|
Tables and Views
|
Maximum number of columns in a table 7
|
1012
|
Maximum number of columns in a view1
|
5000
|
Maximum number of columns in a data source table or view that is referenced by a nickname
|
5000
|
Maximum number of columns in a distribution key5
|
500
|
Maximum length of a row including all overhead2 7
|
32 677
|
Maximum number of rows in a non-partitioned table, per database partition
|
128 x 1010
|
Maximum number of rows in a data partition, per database partition
|
128 x 1010
|
Maximum size of a table per database partition in a regular table space (in gigabytes)3 7
|
512
|
Maximum size of a table per database partition in a large DMS table space (in terabytes)7
|
64
|
Maximum number of data partitions for a single table
|
32 767
|
Maximum number of table partitioning columns
|
16
|
Maximum number of fields in a user-defined row type
|
1012
|
Table Spaces
|
Maximum size of a LOB object per table or per table partition (in terabytes)
|
4
|
Maximum size of a LF object per table or per table partition (in terabytes)
|
2
|
Maximum number of table spaces in a database
|
32 768
|
Maximum number of tables in an SMS table space
|
65 532
|
Maximum size of a regular DMS table space (in gigabytes) 3 7
|
512
|
Maximum size of a large DMS table space (in terabytes) 3 7
|
64
|
Maximum size of a temporary DMS table space (in terabytes) 3 7
|
64
|
Maximum number of table objects in a DMS table space6
|
See Table 7
|
Maximum number of storage paths in an automatic storage database
|
128
|
Maximum length of a storage path that is associated with an automatic storage database (in bytes)
|
175
|
Triggers
|
Maximum run-time depth of cascading triggers
|
16
|
User-defined Types
|
Maximum number of attributes in a structured type
|
4082
|
Note
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.
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.
The numbers shown are architectural limits and approximations. The practical limits may be less.
The actual value is controlled by the max_connections and max_coordagents database manager configuration parameters.
This is an architectural limit. The limit on the most columns in an index key should be used as the practical limit.
See footnote 1 in Table 7.
For page size-specific values, see Table 7.
This 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.
The maximum can be less, depending on index options.
|
Table 7. Database Manager Page Size-specific Limits
|
Description
|
4K page size limit
|
8K page size limit
|
16K page size limit
|
32K page size limit
|
Maximum number of table objects in a DMS table space1
|
51 9712
53 2123
|
53 299
|
53 747
|
54 264
|
Maximum number of columns in a table
|
500
|
1012
|
1012
|
1012
|
Maximum length of a row including all overhead
|
4005
|
8101
|
16 293
|
32 677
|
Maximum size of a table per database partition in a regular table space (in gigabytes)
|
64
|
128
|
256
|
512
|
Maximum size of a table per database partition in a large DMS table space (in terabytes)
|
8
|
16
|
32
|
64
|
Maximum length of an index key including all overhead (in bytes)
|
1024
|
2048
|
4096
|
8192
|
Maximum size of an index per database partition in an SMS table space (in terabytes)
|
8
|
16
|
32
|
64
|
Maximum size of an index per database partition in a regular DMS table space (in gigabytes)
|
64
|
128
|
256
|
512
|
Maximum size of an index per database partition in a large DMS table space (in terabytes)
|
8
|
16
|
32
|
64
|
Maximum size of a regular DMS table space per database partition (in gigabytes)
|
64
|
128
|
256
|
512
|
Maximum size of a large DMS table space (in terabytes)
|
8
|
16
|
32
|
64
|
Maximum size of a temporary DMS table space (in terabytes)
|
8
|
16
|
32
|
64
|
Maximum number of elements in a select list
|
5004
|
1012
|
1012
|
1012
|
Maximum number of columns in a GROUP BY clause
|
500
|
1012
|
1012
|
1012
|
Maximum total length of columns in a GROUP BY clause (in bytes)
|
4005
|
8101
|
16 293
|
32 677
|
Maximum number of columns in an ORDER BY clause
|
500
|
1012
|
1012
|
1012
|
Maximum total length of columns in an ORDER BY clause (in bytes)
|
4005
|
8101
|
16 293
|
32 677
|
Maximum number of values in an insert operation
|
500
|
1012
|
1012
|
1012
|
Maximum number of SET clauses in a single update operation
|
500
|
1012
|
1012
|
1012
|
Maximum records per page for a regular table space
|
251
|
253
|
254
|
253
|
Maximum records per page for a large table space
|
287
|
580
|
1165
|
2335
|
Do'stlaringiz bilan baham: |
