Maximum parameters of the database


Download 40.22 Kb.
Sana21.04.2020
Hajmi40.22 Kb.
#100570
Bog'liq
Maximum parameters of the database


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

Download 40.22 Kb.

Do'stlaringiz bilan baham:




Ma'lumotlar bazasi mualliflik huquqi bilan himoyalangan ©fayllar.org 2024
ma'muriyatiga murojaat qiling