SQL - Structured Query Language - Limites no DB2



Desenvolvido por DORNELLES Carlos Alberto - Analista de Sistemas - Brasília DF. - cad_cobol@hotmail.com

Limites do DB2

 Os limites de armazenamento do sistema podem impedir os limites aqui especificados.
 O limite para artigos não especificados abaixo é armazenamento de sistema


Identifier Length Limits - Tabela 25
 Item  Limite
Longest alias, synonym, collection ID, correlation  name, statement name, or name of a column, cursor,  index, table, view, or table check constraint  18 bytes
 Longest authorization name, package name, or name of  a plan, database, table space, storage group, or  referential constraint  8 bytes
 Longest host identifier  64 bytes
 Longest server name or location identifier  16 bytes


Limites numéricos - Tabela 26
 Item  Limite
 Menor valor INTEGER  -2147483648
 Maior valor INTEGER  2147483647
 Menor valor SMALLINT  -32768
 Maior valor SMALLINT  32767
 Maior valor SMALLINT  32767
 Maior precisão decimal  31
 Menor valor REAL  About -7.2*10(75)
 Maior valor REAL  About 7.2*10(75)
 Menor valor positivo REAL  About 5.4*10(-79)
 Maior valor negativo REAL  About -5.4*10(-79)
 Menor valor FLOAT  About -7.2*10(75)
 Maior valor FLOAT  About 7.2*10(75)
 Menor valor positivo FLOAT  About 5.4*10(-79)
 Maior valor negativo FLOAT  About -5.4*10(-79)
 Menor valor DECIMAL  1 - 10(31)
 Maior valor DECIMAL  10(31) - 1


Limites dos tamanhos de String (String Length Limits) - Tabela 27
 Item  Limite
 Maximum length of CHAR  255 bytes
 Maximum length of GRAPHIC  127 characters
 Maximum length of VARCHAR  4046 bytes, for 4KB pages
 8128 bytes for 8-KB pages
 16320 bytes for 16-KB pages
 32704 bytes, for 32KB pages
 Maximum length of VARGRAPHIC  4046 bytes (2023 DBCS characters), for 4KB pages
 8128 bytes (4064 DBCS characters) for 8-KB pages
 16320 bytes (8160 DBCS characters for 16-KB) pages
 32704 bytes (16352 DBCS characters), for 32KB  pages
 Maximum length of CLOB  2 147 483 647 bytes (2 gigabytes - 1 byte)
 Maximum length of DBCLOB  1 073 741 823 DBCS characters
 Maximum length of BLOB  2 147 483 647 bytes (2 gigabytes - 1 byte)
 Maximum length of a character constant  255 bytes
 Maximum length of a hexadecimal constant  254 digits
 Maximum length of a graphic string constant  124 characters
 Maximum length of a concatenated character string   2 147 483 647 bytes (2 gigabytes - 1 byte)
 Maximum length of a concatenated graphic string  1 073 741 824 DBCS characters
 Maximum length of a concatenated binary string  2 147 483 647 bytes (2 gigabytes - 1 byte)


Limites de data e hora (Datetime Limits) - Tabela 28
 Item  Limite
 Smallest DATE value (shown in ISO format)  0001-01-01
 Largest DATE value (shown in ISO format)  9999-12-31
 Smallest TIME value (shown in ISO format)  00.00.00
 Largest TIME value (shown in ISO format)  24.00.00
 Smallest TIMESTAMP value  0001-01-01-00.00.00.000000
 Largest TIMESTAMP value  9999-12-31-24.00.00.000000


Limites dos comandos de SQL do DB2 (DB2 Limits on SQL Statements) - Tabela 29
 Item  Limite
 Maximum number of columns in a table or view (the  value depends on the complexity of the CREATE VIEW  statement)  750 or fewer
749 if the table is a dependent
 Maximum number of base tables in a view  225
 Maximum row and record sizes for a table  See "Maximum record size" in topic 6.24 under CREATE  TABLE
 Maximum number of volume IDs in a storage group  133
 Maximum number of partitions in a partitioned table  space or partitioned index  64 for table spaces that are not defined with LARGE or  a DSSIZE greater than 2G
 254 for table spaces that are defined with LARGE or a  DSSIZE greater than 2G
 Maximum size of a partition (table space or index)  For table spaces that are not defined with LARGE or a  DSSIZE greater than 2G::

        4 gigabytes, for 1 to 16 partitions
        2 gigabytes, for 17 to 32 partitions
        1 gigabyte, for 33 to 64 partitions

 For table spaces that are large:

        4 gigabytes, for 1 to 254 partitions

 For table spaces that are defined with a DSSIZE  greater than 2G:

         64 gigabytes, for 1 to 254 partitions
 Maximum size of a DBRM entry  131072 bytes
 Longest index key  255 bytes less the number of key columns that allow  nulls. See Table 18 in topic 6.20.
  Maximum number of bytes used in the partitioning of a partitioned index (30)  40
 Maximum number of columns in an index key  64
 Maximum number of tables in a FROM clause  225 or fewer, depending on the complexity of the  statement
 Maximum total length of host and indicator variables pointed to in an SQLDA  32767 bytes

 2 147 483 647 bytes (2 gigabytes - 1 byte) for a
 LOB, subject to the limitations imposed by the  application environment and host language
 Longest host variable used for insert or update  32704 bytes for a non-LOB

 2 147 483 647 bytes (2 gigabytes - 1 byte) for a
 LOB, subject to the limitations imposed by the  application environment and host language
 Longest SQL statement  32765 bytes
 Maximum number of elements in a select list  750 or fewer, depending on whether the select list is  for the result table of a scrollable cursor (41)
 Maximum number of predicates in a WHERE or HAVING clause  750
 Maximum total length of columns of a query operation requiring a sort key (SELECT DISTINCT, ORDER BY, GROUP BY, UNION without the ALL keyword, and the DISTINCT column function)  4000 bytes
 Maximum length of a table check constraint  3800 bytes
 Maximum number of parameters of a stored procedure and any CALL statement referencing the procedure  As many as can be defined by the parameter list stored  in SYSPROCEDURES.PARMLIST. The maximum length  of the column is 3000 bytes.
 Maximum number of bytes that can be passed in a single parameter of an SQL CALL statement  32765 bytes for a non-LOB

 2 147 483 647 bytes (2 gigabytes - 1 byte) for a
 LOB, subject to the limitations imposed by the  application environment and host language
 Maximum number of stored procedures, triggers, and user-defined functions that an SQL statement can implicitly or explicitly reference  16 nesting levels
 Maximum length of the SQL path  254 bytes


Limites do sistema DB2 (DB2 System Limits) - Tabela 30
 Item  Limite
 Maximum number of concurrent DB2 or application agents  Limited by the EDM pool size, buffer pool size, and the  amount of storage used by each DB2 or application  agent
 Largest table or table space  16 terabytes
 Largest log space  2(48)
 Largest active log data set  4 gigabytes -1
 Largest archive log data set  4 gigabytes -1
 Maximum number of active log copies  2
 Maximum number of archive log copies  2
 Maximum number of active log data sets (each copy)  31
 Maximum number of archive log volumes (each copy)  1000
 Maximum number of databases accessible to an application or end user  Limited by system storage and EDM pool size
 Largest EDM pool  The installation parameter maximum depends on  available space
 Maximum number of databases  65217
 Maximum number of rows per page  255 for all table spaces except catalog and directory  tables spaces, which have a maximum of 127
 Maximum simple or segmented data set size  2 gigabytes
 Maximum partitioned data set size  See item "maximum size of a partition" in Table 29
 Maximum LOB data set size  64 gigabytes
 Largest simple or segmented table space  64 gigabytes


Tabela 29
O comprimento máximo só pode ser alcançado se a coluna for a única coluna na tabela.
Caso contrário, o comprimento máximo depende da quantia de espaço que permanece em uma página.


Tabela 30
Se a chave de um índice dividido é mais longa que 40 bytes, só os primeiros 40 bytes serão usados para determinar o maior valor de cada partição.


© Copyright IBM Corp.