DB2 Server for VSE & VM: SQL Reference

Appendix A. SQL Limits

The tables that follow describe certain limits imposed by this product.

Table 14. Identifier Length Limits

Identifier Limits	DB2 Server for VSE & VM
Longest authorization name	8
Longest constraint name	18
Longest correlation name	18
Longest cursor name	18
Longest host identifier	256 a
Longest long identifier	18
Longest short identifier	8
Longest server name	18
Longest statement name	18
Longest unqualified column name	18
Longest unqualified package name	8
Longest unqualified table/view/index name	18

Table 15. Numeric Limits

Numeric Limits	DB2 Server for VSE & VM
Smallest INTEGER value	-2147483648
Largest INTEGER value	+2147483647
Smallest SMALLINT value	-32768
Largest SMALLINT value	+32767
Largest decimal precision	31
Smallest FLOAT value	-7.2x1075
Largest FLOAT value	+7.2x1075
Smallest positive FLOAT value	+5.4x10-79
Largest negative FLOAT value	-5.4x10-79
Smallest REAL value	-7.2x1075
Largest REAL value	+7.2x1075
Smallest Positive REAL value	+5.4x10-79
Largest Negative REAL value	-5.4x10-79

Table 16. String Limits

String Limits	DB2 Server for VSE & VM
Maximum byte count of CHAR	254
Maximum byte count of VARCHAR	32767
Maximum character count of GRAPHIC	127
Maximum character count of VARGRAPHIC	16383
Maximum byte count of character constant	254
Longest concatenated character string	254
Longest concatenated graphic string	127
Maximum character count of a graphic constant b	127

Table 17. Datetime Limits

Datetime Limits c	DB2 Server for VSE & VM
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.000000
Largest TIMESTAMP value	9999-12-31-24.00.00.000000

Table 18. Database Manager Limits

Database Manager Limits	DB2 Server for VSE & VM
Most columns in a table	255
Most columns in a view	140 d
Maximum byte count of a row including all overhead	4080 e
Maximum byte count of a table f	32 x 109
Maximum byte count of an index f	32 x 109
Most rows in a table	2 x 109
Longest index key	255
Most columns in an index key	16
Most indexes on a table	255
Most tables referenced in an SQL statement or a view g	15
Most host variable declarations in a preprocessed program	storage
Most host variables in an SQL statement	256
Longest host variable used for insert or update	32767
Longest SQL statement	8192
Most elements in a select list	255
Most predicates in a WHERE or HAVING clause	200
Most JOIN columns	40
Maximum number of columns in a GROUP BY clause	16
Maximum total length of columns in a GROUP BY clause	255
Maximum number of columns in an ORDER BY clause	16
Maximum total length of columns in an ORDER BY clause	255
Maximum size of an SQLDA	22524
Maximum number of prepared statements	512 h
Most declared cursors in a program	512 h
Maximum number of cursors opened at one time	storage
Most tables in a relational database	storage
Most CCSID overrides in an INSERT or SELECT statement i	80

Notes

a

Individual host language compilers may further restrict this. The database manager, and not the FORTRAN compiler, places a limit of 18 on host identifiers in FORTRAN programs.

b

May be further restricted by preprocessors and utilities.

c

Shown in ISO format.

d

e

The row length of a formatted data row is 4080 bytes including overhead items such as the data value of the row, null byte, and the varchar length field. These items and others affecting the length of a row in a table are discussed in DB2 Server for VSE & VM Database Administration.

f

The numbers shown are architectural limits. The practical limits may be less.

g

In a complex select-statement, the number of tables that can be joined may be significantly less.

h

In C, COBOL and PL/I the sum of the number of declared cursors and the number of prepared statements that are not referenced by a cursor must not be greater than 512. In REXX, the sum of the number of declared cursors and the number of prepared statements that are not referenced by a cursor must not be greater than 40.

i

Though a table may be created with more than 80 different combinations of CCSID and datatype, insert-statement and select-statement impose a limitation of 80 CCSID overrides. For overrides above 80, use a second insert-statement or select-statement.