DB2 11 - SQL - Limits in DB2 for z/OS


Volta a página anterior

Volta ao Menu Principal


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

DB2 11 - SQL - Limits in DB2 for z/OS

Db2® for z/OS® has system limits, object and SQL limits, length limits for identifiers and strings, and limits for certain data type values.

System storage limits might preclude the limits specified in this section.
The limit for items not that are not specified below is limited by system storage.

The following table shows the length limits for identifiers.

Identifier length limits
The term byte(s) in this table means the number of bytes for the UTF-8 representation unless noted otherwise.

Item Limit
External-java-routine-name 1305 bytes
Name of an alias 1, auxiliary table, collection, clone table, constraint, correlation, cursor (except for DECLARE CURSOR WITH RETURN or the EXEC SQL utility), distinct type (both parts of two-part name), function (both parts of two-part name), host identifier, index, JARs, parameter, procedure, role, schema, sequence, specific, statement, storage group, savepoint, SQL condition, SQL label, SQL parameter, SQL variable, synonym, table, trigger, view, XML attribute name, XML element name 128 bytes
Name of an authorization ID or name of a security label. 8 bytes
Routine version identifier 64 EBCDIC bytes, and the UTF-8 representation of the name must not exceed 122 bytes.
Name of a column 30 bytes 1
Name of cursor that is created with DECLARE CURSOR WITH RETURN 30 bytes
Name of cursor that is created with the EXEC SQL utility 8 bytes
Name of a location 16 bytes
Name of buffer pool name, catalog, database, plan, program, table space 8 bytes
Name of package 8 bytes (Only 8 EBCDIC characters are used for packages that are created with the BIND PACKAGE command.
128 bytes can be used for packages that are created as a result of the CREATE FUNCTION (SQL scalar) statement, the CREATE PROCEDURE (SQL - native) statement, the CREATE TRIGGER statement, or a BIND command that specifies a zFS file as DBRM library.)
Name of a profile that is created with CREATE TRUSTED CONTEXT or ALTER TRUSTED CONTEXT 127 bytes
Notes:
  1. If the column name length or the distinct type schema or name length is greater than 30 Unicode bytes, truncation occurs in the sqlname field of the SQLDA when those objects are described in an application.

Numeric limits
Shows the minimum and maximum limits for numeric values.

Item Limit
Smallest SMALLINT value -32768
Largest SMALLINT value 32767
Smallest INTEGER value -2147483648
Largest INTEGER value 2147483647
Smallest BIGINT value -9223372036854775808
Largest BIGINT value 9223372036854775807
Smallest REAL value About -7.2x1075
Largest REAL value About 7.2x1075
Smallest positive REAL value About 5.4x10-79
Largest negative REAL value About -5.4x10-79
Smallest FLOAT value About -7.2x1075
Largest FLOAT value About 7.2x1075
Smallest positive FLOAT value About 5.4x10-79
Largest negative FLOAT value About -5.4x10-79
Smallest DECIMAL value 1 - 1031
Largest DECIMAL value 1031 - 1
Largest decimal precision 31
Smallest DECFLOAT(16) value1 -9.999999999999999x10384
Largest DECFLOAT(16) value1 9.999999999999999x10384
Smallest positive DECFLOAT(16) value1 1.000000000000000x10-383
Largest negative DECFLOAT(16) value1 -1.000000000000000x10-383
Smallest DECFLOAT(34) value1 -9.999999999999999999999999999999999x106144.
Largest DECFLOAT(34) value1 9.999999999999999999999999999999999x106144.
Smallest positive DECFLOAT(34) value1 1.000000000000000000000000000000000x10-6143
Largest negative DECFLOAT(34) value1 -1.000000000000000000000000000000000x10-6143
Coefficient length for DECFLOAT values DECFLOAT(16) is 16 digits; DECFLOAT(34) is 34 digits
Maximum Exponent (Emax) for DECFLOAT values DECFLOAT(16) is 384; DECFLOAT(34) is 6144
Minimum Exponent (Emin) for DECFLOAT values DECFLOAT(16) is -383; DECFLOAT(34) is -6143
Bias for DECFLOAT values DECFLOAT(16) is 398; DECFLOAT(34) is 6176
Note:
  1. These are the limits for normal numbers in DECFLOAT. DECFLOAT also contains special values such as NaN and Infinity that are also valid.
    DECFLOAT also supports subnormal numbers that are outside of the documented range.

String length limits
The following table shows the length limits for strings.

Item Limit
Maximum length of CHAR 255 bytes
Maximum length of GRAPHIC 127 double-byte characters
Maximum length of BINARY 255 bytes
Maximum length1 of VARCHAR
  • 4046 bytes for a column in a table in a table space with 4 KB pages
  • 8128 bytes for a column in a table in a table space with 8 KB pages
  • 16320 bytes for a column in a table in a table space with 16 KB pages
  • 32704 bytes for a column in a table in a table space with 32 KB pages

Otherwise, 32704 bytes

Maximum length of VARCHAR that can be indexed by an XML index 1000 bytes after conversion to UTF-8
Maximum length1 of VARGRAPHIC
  • 2023 double-byte characters for a column in a table in a table space with 4 KB pages
  • 4064 double-byte characters for a column in a table in a table space with 8 KB pages
  • 8160 double-byte characters for a column in a table in a table space with 16 KB pages
  • 16352 double-byte characters for a column in a table in a table space with 32 KB pages

Otherwise, 16352 double-byte characters

Maximum length1 of VARBINARY
  • 4046 bytes for a column in a table in a table space with 4 KB pages
  • 8128 bytes for a column in a table in a table space with 8 KB pages
  • 16320 bytes for a column in a table in a table space with 16 KB pages
  • 32704 bytes for a column in a table in a table space with 32 KB pages

Otherwise, 32704 bytes

Maximum length of CLOB 2147483647 bytes (2 GB - 1 byte)
Maximum length of DBCLOB 1073741823 double-byte characters
Maximum length of BLOB 2147483647 bytes (2 GB - 1 byte)
Maximum length of a character constant 32704 UTF-8 bytes
Maximum length of a hexadecimal character constant 32704 hexadecimal digits
Maximum length of a graphic string constant 16352 double-byte characters (32704 bytes when expressed in UTF-8)
Maximum length of a hexadecimal graphic string constant 32704 hexadecimal digits
Maximum length of a text string used for a scalar expression 4000 UTF-8 bytes
Maximum length of a concatenated character string 2147483647 bytes (2 GB - 1 byte)
Maximum length of a concatenated graphic string 1073741824 double-byte characters
Maximum length of a concatenated binary string 2147483647 bytes (2 GB - 1 byte)
Maximum length of XML pattern text 4000 bytes after conversion to UTF-8
Maximum length of an XML element or attribute name in an XML document 1000 bytes
Maximum length of a namespace uri 1000 bytes
Maximum length of a namespace prefix 998 bytes
Largest depth of an internal XML tree 128 levels
Note:
  1. The maximum length can be achieved only if the column is the only column in the table.
    Otherwise, the maximum length depends on the amount of space remaining on a page.

Datetime limits
The following table shows the minimum and maximum limits for datetime values.

Item Limit
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 WITHOUT TIME ZONE value 0001-01-01-00.00.00.000000000000
Largest TIMESTAMP WITHOUT TIME ZONE value 9999-12-31-24.00.00.000000000000 1
Smallest TIMESTAMP WITH TIME ZONE value 0001-01-01-00.00.00.000000000000 +00:00
Largest TIMESTAMP WITH TIME ZONE value 9999-12-31-24.00.00.000000000000 +00:00 1
TIMESTAMP precision range 0 to 12
TIME ZONE hour range -24 to 24
TIME ZONE minute range 0 to 59
Note:
  1. The maximum value is stated as a UTC value.
    When a timestamp without a time zone is compared to a timestamp with time zone, a necessary adjustment is made using the implicit time zone.
    During that adjustment, the timestamp without time zone could be converted to a value that is greater than the maximum value for a timestamp with time zone value (this could occur on operations such as comparison and assignment).
    This situation can be avoided by using '9999-12-30-00.00.00.000000000000' as the maximum value for timestamp without time zone and '9999-12-30-00.00.00.000000000000 +00:00' as the maximum value for timestamp with time zone columns.

Db2 limits on SQL statements
The following table shows the Db2 limits on SQL statements.

Item Limit
Maximum number of columns that are in a table or view (the value depends on the complexity of the CREATE VIEW statement) or columns returned by a table function. 750 or fewer (including hidden columns)
749 if the table is a dependent
Maximum number of columns that can be referenced in the target of MERGE statement. 749
Maximum number of expressions that can be referenced in the source of a MERGE statement. 750
Approximate maximum number of base tables in a view, SELECT, UPDATE, INSERT, MERGE, or DELETE 225
Maximum number of rows that can be inserted with a single INSERT or MERGE statement 32767
Maximum row and record sizes for a table See the maximum record size table 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 2 GB.

4096, depending on what is specified for DSSIZE or LARGE and the page size.

Maximum sum of the lengths of limit key values of a partition boundary 765 UTF-8 bytes
Maximum size of a partition (table space or index) For table spaces that are not defined with LARGE or a DSSIZE greater than 2 GB:
  • 4 GB, for 1 to 16 partitions
  • 2 GB, for 17 to 32 partitions
  • 1 GB, for 33 to 64 partitions

For table spaces that are defined with a DSSIZE greater than 4 GB:

  • 256 GB, depending on the page size (for 1 to 64 partitions for 4 KB pages, for 1 to 128 partitions for 8 KB pages, for 1 to 256 partitions for 16 KB pages, and 1 to 512 partitions for 32 KB pages)

Maximum size of a non-partitioned index for a partitioned table space

For 5-byte EA table spaces:

  • 16 TB for 4 KB pages
  • 32 TB for 8 KB pages
  • 64 TB for 16 KB pages
  • 128 TB for 32 KB pages

For table spaces that are defined with LARGE:

  • 16 TB

Maximum length of an index key Partitioning index: 255-n
Nonpartitioning index that is padded: 2000-n
Nonpartitioning index that is not padded: 2000-n-2m

Where n is the number of columns in the key that allow nulls and m is the number of varying-length columns in the key

Maximum number of bytes used in the partitioning of a partitioned index 255 (This maximum limit is subject to additional limitations, depending on the number of partitions in the table space.
The number of partitions * (106 + limit key size) must be less than 65394.)
Maximum number of columns in an index key 64
Maximum number of expressions in an index key 64
Maximum number of tables in a FROM clause 225 or fewer, depending on the complexity of the statement
Maximum number of subqueries in a statement 224
Maximum total length of host and indicator variables pointed to in an SQLDA 32767 bytes

2147483647 bytes (2 GB - 1 byte) for a LOB, subject to the limitations that are imposed by the application environment and host language

Maximum size of application SQLDA for any statement that references host variables or parameter markers 99016 bytes
Maximum length of host variable used for insert or update operation 32704 bytes for a non-LOB

2147483647 bytes (2 GB - 1 byte) for a LOB, subject to the limitations that are imposed by the application environment and host language

Maximum number of host variables or parameter markers in a statement 16,000
Maximum length of an SQL statement 2097152 bytes
Maximum number of elements in a select list 750 or fewer, depending on whether the select list is for the result table of static scrollable cursor1
Maximum number of predicates in a WHERE or HAVING clause Limited by storage
Maximum total length of columns of a query operation requiring sort and evaluating column functions (MULTIPLE DISTINCT and GROUP BY) 65529 bytes
Maximum length of a sort key 32707 bytes
Maximum length of a check constraint 3800 bytes
Maximum number of bytes that can be passed in a single parameter of an SQL CALL statement 32765 bytes for a non-LOB

2147483647 bytes (2 GB - 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 64 nesting levels
Maximum length of the SQL path 2048 bytes
Maximum length of a WLM environment name in a CREATE PROCEDURE, CREATE FUNCTION, ALTER PROCEDURE, or ALTER FUNCTION statement. 32 bytes
Maximum number of XPath level in the XMLPATTERN clause of the CREATE INDEX statement. 50 nesting levels
Note:
  1. If the scrollable cursor is read-only, the maximum number is 749 less the number of columns in the ORDER BY that are not in the select list. If the scrollable cursor is not read-only, the maximum number is 747.

Db2 system limits
The following table shows the Db2 system limits.

Item Limit
Maximum number of concurrent Db2 or application agents Limited by the EDM pool size, buffer pool size, and the amount of storage that is used by each Db2 or application agent
Maximum number of concurrently active audit policies 32
Maximum size of a non-LOB table or table space 128 terabytes (TB)
Maximum size of a simple or segmented table space 64 GB
Maximum size of a log space
  • 6-byte format: 248 bytes
  • 10-byte format: 280 bytes
Maximum size of an active log data set 4 GB minus 1 byte
Maximum size of an archive log data set 4 GB minus 1 byte
Maximum number of active log copies 2
Maximum number of archive log copies 2
Maximum number of active log data sets (each copy) 93
Maximum number of archive log volumes (each copy) 10000
Maximum number of databases accessible to an application or user Limited by system storage and EDM pool size
Maximum number of databases 65217
Maximum number of implicitly created databases Maximum value of the sequence SYSIBM.DSNSEQ_IMPLICITDB, with a default of 10000
Maximum number of internal objects for each database 1 32767
Maximum number of indexes on declared global temporary tables 10000
Maximum size of an EDM pool The installation parameter maximum depends on available space
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 GB
Maximum partitioned data set size See item Table 5
Maximum LOB data set size 256 GB
Maximum number of table spaces that can be defined in a work file database 500
Maximum number of tables and triggers that can be defined in a work file database 11767
Note:
  1. The number of internal object descriptors (OBDs) for external objects are as follows:
    • Table space: 2
    • Table: 1
    • Index: 2
    • Check constraint: 1
    • Referential integrity relationship: 2
    • Auxiliary relationship for each LOB column: 1
    • XML relationship for each XML column: 1
    • Trigger: 1
    • View that has an INSTEAD OF trigger: 1


© Copyright IBM Corp.

Volta a página anterior

Volta ao Menu Principal