473,692 Members | 2,293 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

maximum size of a database/tablespace

What is the maximum permissible size of a database? Is there any limitation.

What is the maximum # of tablespace's allowed in a database?

Thanks for your response.
Nov 12 '05 #1
2 28585
Kums wrote:
What is the maximum permissible size of a database? Is there any limitation.

What is the maximum # of tablespace's allowed in a database?

Thanks for your response.


Where is your documentation? Is the documentation also available online? Is
there a SQL Reference book? Does it have SQL Limits? WHat does it say?

Questions, questions, so many questions - so little answers ...
Jan M. Nelken
Nov 12 '05 #2

"Kums" <hi****@gmail.c om> wrote in message
news:ca******** *************** ***@posting.goo gle.com...
What is the maximum permissible size of a database? Is there any limitation.
What is the maximum # of tablespace's allowed in a database?

Thanks for your response.


The answer is "it depends". The version of DB2 that you are running and the
Operating System you are running on can be a factor in the answer.

You can find these values in the SQL Limits appendix in the SQL Reference
manual for your version and platform. The manuals for all but the oldest
versions of DB2 are available online at the IBM website.

The following is the SQL Limits appendix for DB2 V7.2 on Windows/Linux/Unix,
just to give you an example of typical values. As you will see, the maximum
size of a database isn't given directly but can be determined based on the
tablespace type and page size that you choose:

----------------------------------------------------------------------------
------------------------------------
SQL Reference
----------------------------------------------------------------------------
----

Appendix A. SQL Limits
The following tables describe certain SQL limits. Adhering to the most
restrictive case can help the programmer design application programs that
are easily portable.
Table 29. Identifier Length Limits
Description Limit in Bytes
1 Longest authorization name (can only be single-byte characters) 30
2 Longest constraint name 18
3 Longest correlation name 128
4 Longest condition name 64
5 Longest cursor name 18
6 Longest data source column name 128
7 Longest data source index name 128
8 Longest data source name 128
9 Longest data source table name (remote-table-name) 128
10 Longest external program name 8
11 Longest host identifier a 255
12 Longest identifier of a data source user
(remote-authorization-name) 30
13 Longest label name 64
14 Longest method name 18
15 Longest parameter nameb 128
16 Longest password to access a data source 32
17 Longest savepoint name 128
18 Longest schema name c 30
19 Longest server (database alias) name 8
20 Longest SQL variable name 64
21 Longest statement name 18
22 Longest transform group name 18
23 Longest unqualified column name 30
24 Longest unqualified package name 8
25 Longest unqualified user-defined type, user-defined function,
buffer pool, table space, nodegroup, trigger, index, or index specification
name 18
26 Longest unqualified table name, view name, stored procedure,
nickname, or alias 128
27 Longest wrapper name 128
Notes:
a
Individual host language compilers may have a more restrictive
limit on variable names.

b
Parameter names in an SQL procdure are limited to 64 bytes.

c
The schema name for a user-defined structured type is limited
to 8 bytes.


Table 30. Numeric Limits
Description Limit
1 Smallest INTEGER value -2 147 483 648
2 Largest INTEGER value +2 147 483 647
3 Smallest BIGINT value -9 223 372 036 854 775 808
4 Largest BIGINT value +9 223 372 036 854 775 807
5 Smallest SMALLINT value -32 768
6 Largest SMALLINT value +32 767
7 Largest decimal precision 31
8 Smallest DOUBLE value -1.79769E+308
9 Largest DOUBLE value +1.79769E+308
10 Smallest positive DOUBLE value +2.225E-307
11 Largest negative DOUBLE value -2.225E-307
12 Smallest REAL value -3.402E+38
13 Largest REAL value +3.402E+38
14 Smallest positive REAL value +1.175E-37
15 Largest negative REAL value -1.175E-37

Table 31. String Limits
Description Limit
1 Maximum length of CHAR (in bytes) 254
2 Maximum length of VARCHAR (in bytes) 32 672
3 Maximum length of LONG VARCHAR (in bytes) 32 700
4 Maximum length of CLOB (in bytes) 2 147 483 647
5 Maximum length of GRAPHIC (in characters) 127
6 Maximum length of VARGRAPHIC (in characters) 16 336
7 Maximum length of LONG VARGRAPHIC (in characters) 16 350
8 Maximum length of DBCLOB (in characters) 1 073 741 823
9 Maximum length of BLOB (in bytes) 2 147 483 647
10 Maximum length of character constant 32 672
11 Maximum length of graphic constant 16 336
12 Maximum length of concatenated character string 2 147 483 647
13 Maximum length of concatenated graphic string 1 073 741 823
14 Maximum length of concatenated binary string 2 147 483 647
15 Maximum number of hex constant digits 16 336
16 Maximum size of a catalog comment (in bytes) 254
17 Largest instance of a structured type column object at runtime 1
GB

Table 32. Datetime Limits
Description Limit
1 Smallest DATE value 0001-01-01
2 Largest DATE value 9999-12-31
3 Smallest TIME value 00:00:00
4 Largest TIME value 24:00:00
5 Smallest TIMESTAMP value 0001-01-01-00.00.00.000000
6 Largest TIMESTAMP value 9999-12-31-24.00.00.000000

Table 33. Database Manager Limits
Description Limit
1 Most columns in a table g 1 012
2 Most columns in a view a 5 000
3 Maximum length of a row including all overhead b g 32 677
4 Maximum size of a table per partition (in gigabytes) c g 512
5 Maximum size of an index per partition (in gigabytes) 512
6 Most rows in a table per partition 4 x 109
7 Longest index key including all overhead (in bytes) 1 024
8 Most columns in an index key 16
9 Most indexes on a table 32 767 or storage
10 Most tables referenced in an SQL statement or a view storage
11 Most host variable declarations in a precompiled program c
storage
12 Most host variable references in an SQL statement 32 767
13 Longest host variable value used for insert or update (in bytes)
2 147 483 647
14 Longest SQL statement (in bytes) 65 535
15 Most elements in a select listg 1 012
16 Most predicates in a WHERE or HAVING clause storage
17 Maximum number of columns in a GROUP BY clause g 1 012
18 Maximum total length of columns in a GROUP BY clause (in bytes)g
32 677
19 Maximum number of columns in an ORDER BY clause g 1 012
20 Maximum total length of columns in an ORDER BY clause (in bytes) g
32 677
21 Maximum size of an SQLDA (in bytes) storage
22 Maximum number of prepared statements storage
23 Most declared cursors in a program storage
24 Maximum number of cursors opened at one time storage
25 Most tables in an SMS table space 65 534
26 Maximum number of constraints on a table storage
27 Maximum level of subquery nesting storage
28 Maximum number of subqueries in a single statement storage
29 Most values in an INSERT statement g 1 012
30 Most SET clauses in a single UPDATE statement g 1 012
31 Most columns in a UNIQUE constraint (supported via a UNIQUE index)
16
32 Maximum combined length of columns in a UNIQUE constraint
(supported via a UNIQUE index) (in bytes) 1 024
33 Most referencing columns in a foreign key 16
34 Maximum combined length of referencing columns in a foreign key
(in bytes) 1 024
35 Maximum length of a check constraint specification (in bytes) 65
535
36 Maximum number of columns in a partitioning key e 500
37 Maximum number of rows changed in a unit of work storage
38 Maximum number of packages storage
39 Most constants in a statement storage
40 Maximum concurrent users of server d 64 000
41 Maximum number of parameters in a stored procedure 32 767
42 Maximum number of parameters in a user defined function 90
43 Maximum run-time depth of cascading triggers 16
44 Maximum number of simultaneously active event monitors 32
45 Maximum size of a regular DMS table space (in gigabytes)c g 512
46 Maximum size of a long DMS table space (in terabytes)c 2
47 Maximum size of a temporary DMS table space (in terabytes)c 2
48 Maximum number of databases per instance concurrently in use 256
49 Maximum number of concurrent users per instance 64 000
50 Maximum number of concurrent applications per database 1 000
51 Maximum depth of cascaded triggers 16
52 Maximum partition number 999
53 Most table objects in DMS table space f 51 000
54 Longest variable index key part (in bytes) 255
55 Maximum number of columns in a data source table or view that is
referenced by a nickname 5 000
56 Maximum NPAGES in a bufferpool for 32 bit releases 524 288
57 Maximum NPAGES in a bufferpool for 64 bit releases 2 147 483 647
58 Maximum number of nested levels for stored procedures 16
59 Maximum number of tablespaces in a database 4096
60 Maximum number of attributes in a structured type 4082
Notes:
a
This maximum can be achieved using a join in the CREATE VIEW
statement. Selecting from such a view is subject to the limit of most
elements in a select list.

b
The actual data for BLOB, CLOB, LONG VARCHAR, DBCLOB, and LONG
VARGRAPHIC columns is not included in this count. However information about
the location of that data does take up some space in the row.

c
The numbers shown are architectural limits and approximations.
The practical limits may be less.

d
The actual value will be the value of the MAXAGENTS
configuration parameter. See the Administration Guide for information on
MAXAGENTS.

e
This is an architectural limit. The limit on the most columns
in an index key should be used as a practical limit.

f
Table objects include data, indexes, LONG VARCHAR/VARGRAPHIC
columns, and LOB columns. Table objects that are in the same table space as
the table data do not count extra toward the limit. However, each table
object that is in a different table space than the table data does
contribute one toward the limit for each table object type per table in the
table space in which the table object resides.

g
For page size specific values, please refer to Table 34.


Table 34. Database Manager Page Size Specific Limits
Description 4K page size limit 8K page size limit 16K page size
limit 32K page size limit
1 Most columns in a table 500 1 012 1 012 1 012
3 Maximum length of a row including all overhead 4 005 8 101 16
293 32 677
4 Maximum size of a table per partition (in gigabytes) 64 128 256
512
5 Maximum size of an index per partition (in gigabytes) 64 128 256
512
15 Most elements in a select list 500 1 012 1 012 1 012
17 Maximum number of columns in a GROUP BY clause 500 1 012 1 012
1 012
18 Maximum total length of columns in a GROUP BY clause (in bytes) 4
005 8 101 16 293 32 677
19 Maximum number of columns in an ORDER BY clause 500 1 012 1 012
1 012
20 Maximum total length of columns in an ORDER BY clause (in bytes)
4 005 8 101 16 293 32 677
29 Most values in an INSERT statement 500 1 012 1 012 1 012
30 Most SET clauses in a single UPDATE statement 500 1 012 1 012
1 012
45 Maximum size of a regular DMS table space (in gigabytes) 64 128
256 512
----------------------------------------------------------------------------
---------------------------------------

Rhino
Nov 12 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
17644
by: Steve Gallagher | last post by:
I create my database and a number of tables from within a .bat file. When I set the FULL_TXT field to 3800 or less, all is well. Above 3800, I get SQL0286N A default table space could not be found with a page size of at least "8192" that authorization ID "GALLAGS" is authorized to use. SQLSTATE=42727 Now, I know this means that DB2 is trying to build into a tablespace that's too small to hold a row of this total size, but I cannot...
2
5243
by: Sue | last post by:
I have a large database currently using up 30GB of space -- problem is, I am running out of space on my harddrive and so have to do some cleanup. One of the tables has a blob field and I find that I can safely delete some of this blob data because I will not be needing it in the database anymore. So the question is, if I delete some of the blob data from this table will the database size decrease? Is there anything else I can do to...
6
6136
by: kaming | last post by:
Dear all, I would like to ask is there any DB2 SQL statement that can be used to show the overall database size? (i.e. display the allocated/used data files or tablespaces (either SMS or DMS) size??) Thanks! Henry
2
3116
by: p175 | last post by:
Hi folks, I've tried reading just about every post I can on raid stiping / extent size etc and I'm just getting myself more confused than ever. Here's my situation. On a Windows Server 2000 standard edition system with only 1gb ram using UDB 8.2.3 Enterprise, I have an existing database that uses two raid arrays striped with 8kb. Presently the tablespaces have a default extent of 24, default prefetch of 48 .
2
2747
by: ari | last post by:
Hi! Normally Google helps a lot with these simple questions, but now I cannot get correct search words. So I try here: I have db2 udb 8.1 fix pack 6, and it is on windows 2003 server. - what is the maximum table space size? 64GB? - what is the maximum container size for tablespace?
6
3800
by: mike_dba | last post by:
Can anyone tell me why a EEE system might be created to have two separate tablespaces with the same 4k pagesize ? They both appear to be in use as I can see the underlying files being modified. These were created with SMS and reside on the same disk and filesystem (separate directory on filesystem). One is called temp_4k and the other temp_sys. I would assume that the temp_sys was intended for use by the db2 catalog. I know of no way...
4
13236
by: Alvin SIU | last post by:
Hi all, I am now doing development using DB2. Q1. There is one tablespace to store 5 tables. But, when in production, there will be one tablespace for EACH of the tables.
2
3592
by: Woody Ling | last post by:
I am now using db2 v8.2 64bits without DPF. I want to create a very large table which is about 1000G and the record length is suitable for 32K page size. I find in the manual that the maximum size for 32K page size container is 512G only. Does it mean that the maximum size for 1 tablespace is also limited by 512G or I can create a tablespace with 2 containers (512G each) to support 1000G table? Thanks.
5
4151
by: Mark A | last post by:
I have an OLTP database that has a tablespace that will start out at about 1 TB, but may grow to 75 TB's in size over the next 5 years. When I add new containers (on a new mount point), I want to use the BEGIN NEW STRIPE SET command to avoid rebalancing. Anyone know the maximum number of STRIPE SET's that can be created for a tablespace.
0
8610
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8545
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
8968
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8810
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
1
6462
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5822
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4327
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4562
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
3
1961
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.