MAXIMUM SIZE OF A DB2 DATABASE V.5

[SFox180089]

I know it is somewhere in the tombs of IBM documention... Does any one knows
the maximum size a db2 database could be... Also the maximum size and still
have good performance??
Steven.fox@ dfas.mil

[rpar...@postoffice.swbell.net]

The maximum size is at tablespace level. Assuming you are working on OS/390,
the answer is:

For simple and segmented tablespaces, the max is 64 Gigabytes.
For partitioned tablespaces, the maximum is 64 Gigabytes unless you specify
LARGE.

With LARGE, you can have up to 254 partitions, each with a max of 4 Gb; giving a
theoretical maximum of 1 terabyte.

What is the maximum size with good performance. It depends on a many factors,
like the characteristics of the SQL (direct access or scans), data volatility,
maintenance windows, online requirements, etc etc etc.

Richard Arnold
DBA

[Andy Lankester]

Max database size is limited by OBID being SMALLINT, so the total number
of objects (indexes, tspaces, partitions etc) cannot exceed 32767. So
assuming LARGE tspaces of 254 parts with just a partitioning index gives
about 64 TB per database, and you can have 32760 databases giving a
total per DB2/group of about 2 EB(ExaBytes). Big enough?

Performance impact of database size is mainly due to processes that lock
the DBD (DDL etc). If you have end-users who do a lot of DDL then
consider giving each user his/her own database. Otherwise a database
per application seems to work OK.

Andy Lankester
BMC Software Ltd

[Blair Kenneth Adamache]

We have published a 1 TB TPC-D benchmark on NT, demonstrating our confidence in
DB2's scalability. The result is currently second in Power (to Oracle) second
in Throughput (to a DB2 result on AIX), and first in price/performance. All
other 1 TB TPC-D results are either Unix or Terradata.

The DB2 Universal Database SQL Reference does not list a maximum database size.
The maximum number of objects per tablespace is 65,534. The maximum table size
is 64 GB per partition. You can get up to 128 GB per partition by using 8K
pages, but this limits the number of objects per table space to 13,305.

Note that the 1 TB benchmark size is data only. When you add in disk for
indexes, temporary tables and logs, our DB2/NT product was addressing over 8 TB
of disk space.

On Unix and OS/390, we have many customers with several terrabytes of data on
DB2.

[sver...@hotmail.com]

Maybe this is of some use :

Table 28. Database Managar Page Size Specific Limits

Description 4K page size limit 8K page size limit
1 Most columns in a table 500 1 012
3 Maximum length of a row including
all overhead b 4 005 8 101
4 Maximum size of a table per
partition (in gigabytes) c 64 128
15 Most elements in a select list 500 1 012
17 Maximum number of columns in a
GROUP BY clause 500 1 012
18 Maximum total length of columns in
a GROUP BY clause (in bytes) 4 005 8 101
19 Maximum number of columns in an
ORDER BY clause 500 1 012
20 Maximum total length of columns in an
ORDER BY clause (in bytes) 4 005 8 101
29 Most values in an INSERT statement 500 1 012
30 Most SET clauses in a single
UPDATE statement 500 1 012
45 Maximum size of a regular DMS table
space (in gigabytes)c 64 128
53 Most table objects in DMS table space f 6 648 13 305

(see Appendix A - SQL Limits)

The maximum size and still having a good performance depends on a lot of
factors (e.g. what kind of server, number of partitions/nodes, number of
users, kind of application/queries (OLTP, Datawarehousing, ..), what is the
definition of 'good performance' etc..), so I can't really answer that
question.

Regards,

Simon
Devote

In article <19990127181735...@ng-fw1.aol.com>,

sfox1...@aol.com (SFox180089) wrote:
> I know it is somewhere in the tombs of IBM documention... Does any one knows
> the maximum size a db2 database could be... Also the maximum size and still
> have good performance??
> Steven.fox@ dfas.mil
>
>

-----------== Posted via Deja News, The Discussion Network ==----------
http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own