DB2 SQL limits - IBM Rational Developer for i V9.5.1


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 SQL limits - IBM Rational Developer for i V9.5.1

The following tables describe certain SQL and database limits imposed by the Db2® for i database manager.

Note:
  • System storage limits may preclude the limits specified here.
  • A limit of storage means that the limit is dependent on the amount of storage available.
  • A limit of statement means that the limit is dependent on the limit for the maximum length of a statement.
Identifier Length Limits

Identifier Limits Db2 for i Limit
Longest authorization name 101
Longest correlation name 128
Longest cursor name 128
Longest descriptor name 128
Longest external program name (string form) 2792
Longest external program name (unqualified form) 10
Longest host identifier3 128
Longest package version-id 64
Longest partition name 10
Longest savepoint name 128
Longest schema name 128
Longest server name 18
Longest statement name 128
Longest SQL condition name 128
Longest SQL label 128
Longest unqualified alias name 128
Longest unqualified column name 128
Longest unqualified constraint name 128
Longest unqualified distinct type name 128
Longest unqualified function name 128
Longest unqualified global variable name 128
Longest unqualified index name 128
Longest unqualified mask name 128
Longest unqualified nodegroup name 10
Longest unqualified package name 10
Longest unqualified permission name 128
Longest unqualified procedure name 128
Longest unqualified sequence name 128
Longest unqualified specific name 128
Longest unqualified SQL parameter name 128
Longest unqualified SQL variable name 128
Longest unqualified system column name 10
Longest unqualified system object name 10
Longest unqualified system schema name 10
Longest unqualified table and view name 128
Longest unqualified trigger name 128
Longest unqualified XSR object name 128
Longest XML element name, attribute name, prefix name, or processing instruction name specified in XMLELEMENT, XMLFOREST, XMLATTRIBUTES, XMLNAMESPACES or XMLPI 128
Longest XML path name specified in XMTABLE 128
Longest XML element name, attribute name, prefix name, or processing instruction name for a parsed XML document 1000
Longest XML schema location uniform resource identifier (URI) 1000
Longest JSON path name 128

Numeric Limits

Numeric Limits Db2 for i 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 63
Maximum exponent (Emax) for REAL values 38
Smallest REAL value4 -3.4x1038
Largest REAL value4 +3.4x1038
Minimum exponent (Emin) for REAL values -38
Smallest positive REAL value4 +1.18x10-38
Largest negative REAL value4 -1.18x10-38
Maximum exponent (Emax) for DOUBLE values 308
Smallest DOUBLE value4 -1.79x10308
Largest DOUBLE value4 +1.79x10308
Minimum exponent (Emin) for DOUBLE values -308
Smallest positive DOUBLE value4 +2.23x10-308
Largest negative DOUBLE value4 -2.23x10-308
Maximum exponent (Emax) for DECFLOAT(16) values 384
Smallest DECFLOAT(16) value5 -9.999999999999999x10384
Largest DECFLOAT(16) value5 9.999999999999999x10384
Minimum exponent (Emin) for DECFLOAT(16) values -383
Smallest positive DECFLOAT(16) value5 1x10-383
Largest negative DECFLOAT(16) value5 -1x10-383
Maximum exponent (Emax) for DECFLOAT(34) values 6144
Smallest DECFLOAT(34) value5 -9.999999999999999999999999999999999x106144
Largest DECFLOAT(34) value5 9.999999999999999999999999999999999x106144
Minimum exponent (Emin) for DECFLOAT(34) values -6143
Smallest positive DECFLOAT(34) value5 1x10-6143
Largest negative DECFLOAT(34) value5 -1x10-6143

String Limits

String Limits Db2 for i Limit
Maximum length of CHAR (in bytes) 327656
Maximum length of VARCHAR (in bytes) 327396
Maximum length of CLOB (in bytes) 2 147 483 647
Maximum length of GRAPHIC (in double-byte characters) 163826
Maximum length of VARGRAPHIC (in double-byte characters) 163696
Maximum length of DBCLOB (in double-byte characters) 1 073 741 823
Maximum length of BINARY (in bytes) 327656
Maximum length of VARBINARY (in bytes) 327396
Maximum length of BLOB (in bytes) 2 147 483 647
Maximum length of serialized XML (in bytes) 2 147 483 647
Maximum length of character constant 32740
Maximum length of a graphic constant 16370
Maximum length of binary constant 32740
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 762
Maximum length of catalog comments 20007
Maximum length of column label (in bytes) 60
Maximum length of SQL routine label 128
Maximum length of table, package, or alias label 50
Maximum length of C NUL-terminated 327396
Maximum length of C NUL-terminated graphic 163696

XML Limits

XML Limits Db2 for i Limit
Maximum length of an XML schema document (in bytes) 2 147 483 647
Maximum length of a parsed XML entity 2 147 483 647
Maximum depth of an internal XML tree 128

JSON Limits

JSON Limits Db2 for i Limit
Maximum length of an JSON document (in bytes) 2 147 483 647

Datetime Limits

Datetime Limits Db2 for i 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

DataLink Limits

Datalink Limits Db2 for i Limit
Maximum length of DATALINK 32718
Maximum length of DATALINK comment 254

Database Manager Limits

Database Manager Limits Db2 for i Limit
Relational Database
Maximum number of schemas storage
Maximum number of tables in a relational database storage
Maximum number of nodes in a nodegroup 32
Schemas
Maximum number of objects in a schema approximately 1 000 000
Tables and Views
Maximum number of columns in a table 8000
Maximum number of columns in a view 8000
Maximum length of a row without LOBs including all overhead 32766
Maximum length of a row with LOBs including all overhead 3 758 096 383
Maximum number of rows in a non-partitioned table 4 294 967 288
Maximum number of rows in a data partition 4 294 967 288
Maximum size of a non-partitioned table 1.7 terabytes
Maximum size of a data partition 1.7 terabytes
Maximum number of data partitions in a single partitioned table 256
Maximum number of table partitioning columns 120
Maximum number of tables referenced in a view or materialized query table 2568
Maximum number of dependent views, materialized query tables, and indexes on a table or view. storage
Constraints
Maximum number of constraints on a table 5000
Maximum number of columns in a UNIQUE constraint 120
Maximum combined length of columns in a UNIQUE constraint (in bytes) 327676
Maximum number of referencing columns in a foreign key 120
Maximum combined length of referencing columns in a foreign key (in bytes) 327676
Maximum length of a CHECK constraint (in bytes) statement
Triggers
Maximum number of triggers on a table 300
Maximum runtime depth of cascading triggers 200
Indexes
Maximum number of indexes on a table approximately 15000
Maximum number of columns in an index key 120
Maximum length of an index key 327676
Maximum size of a non-partitioned index 1.7 terabytes
Maximum size of a partition of a partitioned index 1.7 terabytes
SQL
Maximum length of an SQL statement (in bytes) 2 097 152
Maximum number of tables referenced in an SQL statement 10008
Maximum number of variables and constants in an SQL statement 327009
Maximum number of elements in a select list approximately 800010
Maximum number of predicates in a WHERE or HAVING clause statement
Maximum number of columns in a GROUP BY clause total GROUP BY length
Maximum total length of columns in a GROUP BY clause 3.5 Gigabytes11
Maximum number of elements in a CUBE grouping 10
Maximum number of columns in an ORDER BY clause total ORDER BY length
Maximum total length of columns in an ORDER BY clause 3.5 Gigabytes 11
Maximum levels of recursion for hierarchical query 250
Maximum levels allowed for a subquery 256
Maximum number of values in an insert operation 8000
Maximum number of SET clauses in a single update operation 8000
Routines
Maximum number of parameters in a procedure 200012
Maximum number of parameters in a function 200012
Maximum number of return columns in a table function 8000
Maximum number of nested levels for routines storage
Types
Maximum cardinality of an array type 2 147 483 647
Applications
Maximum number of host variable declarations in a precompiled program storage13
Maximum length of a host variable value (in bytes) 2 147 483 647
Maximum length of an MQ message CLOB value (in bytes) 2M
Maximum length of an MQ message varying length value (in bytes) 32000
Maximum number of declared cursors in a program storage
Maximum number of cursors opened at one time storage 14
Maximum number of rows locked in a unit of work 500 000 000
Maximum number of DDL statements in a unit of work 131 036
Maximum number of locators in a transaction 16 000 000 15
Maximum size of an SQLDA (in bytes) 16 777 215
Maximum number of prepared statements storage
Maximum number of savepoints active at one time storage
Maximum number of simultaneously allocated CLI handles in a process 160 000 16
Maximum size of a package 1008 megabytes17
Maximum length of a path 8843
Maximum number of schemas in a path 268
Maximum length of a password 127
Maximum length of a hint 32
Maximum size of a program, service program, or module associated space (in bytes) 16 777 216
Maximum size of the diagnostics area 90K
Maximum size of an array variable 4GB

1 As an application requester, Db2 for i can send an authorization name of up to 255 bytes.
2 For REXX procedures, the limit is 33.
3 For an RPG, COBOL, or REXX program, the limit is 64.
4 The values shown are approximate.
5 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.
6 If the column is NOT NULL, the maximum is one more.
7 For sequences the limit is 500.
8 The maximum number of members (and partitions) referenced is also 1000.
   In DELETE and UPDATE statements the maximum number is 256.
9 The limit is restricted by internal structures and may be less depending on how the constants are used in the statement and if very large string constants or variables are used.
10 The limit is based on the size of internal structures generated for the parsed SQL statement.
11 The limit is 32766 if CQE processed the select statement.
   The limit will be less if an ICU collating sequence or ALWCPYDTA(*NO) is used.
12 SQL procedures and SQL functions can have up to 2000 parameters.
   The number of parameters for external procedures and external functions cannot exceed 2000 and is limited by the maximum number of parameters allowed by the language.
13 In RPG/400® and PL/I programs when the old parameter passing technique is used, the limit is approximately 4000.
   The limit is based on the number of pointers allowed in the program. In all other cases, the limit is based on operating system constraints.
14 The maximum number of cursors open at one time in a single job is approximately 20 473.
15 The maximum number of locators in a transaction in SQL Server mode is 209 000.
16 The maximum number of allocated handles per DRDA connection is 500.
17 The maximum size of a DRDA package can be increased from 500 megabytes to 1 gigabyte by using a QAQQINI option.


© Copyright IBM Corp.

Volta a página anterior

Volta ao Menu Principal