Database size - DB2 Database

kaming

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

Nov 12 '05 #1

Subscribe Reply

6116

Phil Sherman

I don't know of any SQL that will do that but the following command will:
list tablespace containers for n show detail
where n is the tablespace number (TSID) for the tablespace you are
interested in viewing. From my RHEL4 UDB 8.1 FP9 system after connecting
to database SAMPLE:

[.....]$ db2 list tablespace containers for 2 show detail

Tablespace Containers for Tablespace 2

Container ID = 0
Name =
/home/db2inst1/db2inst1/NODE0000/SQL00001/SQLT0002.0
Type = Path
Total pages = 407
Useable pages = 407
Accessible = Yes
UDB 8.2 has a "Storage management tool" that will store snapshot data in
tables that are accessable with SQL. It can also "monitor" storage and
tack actions when user-defined threshholds have been reached. Check the
administration manuals for "Managing Storage".

Phil Sherman

kaming wrote:

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

Nov 12 '05 #2

Keith

Check out get_dbsize_info procedure.....
Keith Ponnapalli
IBM Certified Advanced Database Administrator - DB2 UDB V8.1 for
Linux,UNIX, and Windows
INFORMIX Certified Database Administrator
Phil Sherman wrote:

I don't know of any SQL that will do that but the following command will:
list tablespace containers for n show detail
where n is the tablespace number (TSID) for the tablespace you are
interested in viewing. From my RHEL4 UDB 8.1 FP9 system after connecting
to database SAMPLE:

[.....]$ db2 list tablespace containers for 2 show detail

Tablespace Containers for Tablespace 2

Container ID = 0
Name =
/home/db2inst1/db2inst1/NODE0000/SQL00001/SQLT0002.0
Type = Path
Total pages = 407
Useable pages = 407
Accessible = Yes
UDB 8.2 has a "Storage management tool" that will store snapshot data in
tables that are accessable with SQL. It can also "monitor" storage and
tack actions when user-defined threshholds have been reached. Check the
administration manuals for "Managing Storage".

Phil Sherman

kaming wrote:
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

Nov 12 '05 #3

Keith

I don't know of any SQL that will do that but the following command will:
list tablespace containers for n show detail
where n is the tablespace number (TSID) for the tablespace you are
interested in viewing. From my RHEL4 UDB 8.1 FP9 system after connecting
to database SAMPLE:

[.....]$ db2 list tablespace containers for 2 show detail

Tablespace Containers for Tablespace 2

Container ID = 0
Name =
/home/db2inst1/db2inst1/NODE0000/SQL00001/SQLT0002.0
Type = Path
Total pages = 407
Useable pages = 407
Accessible = Yes
UDB 8.2 has a "Storage management tool" that will store snapshot data in
tables that are accessable with SQL. It can also "monitor" storage and
tack actions when user-defined threshholds have been reached. Check the
administration manuals for "Managing Storage".

Phil Sherman

kaming wrote:
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

Nov 12 '05 #4

Phil Sherman

Thanks - I missed this one because it appears to be documented only in
the information center material. There is a one-liner reference to it in
the SQL Reference V1, buried in table 16 - supported functions. I also
noted that, in the documentation, it states "expensive to run" and that
the default is to update if the information is older than 30 min.

It would be very nice if these administrative routines were documented
in the administration manuals.

Phil Sherman

Keith wrote:

Check out get_dbsize_info procedure.....
Keith Ponnapalli
IBM Certified Advanced Database Administrator - DB2 UDB V8.1 for
Linux,UNIX, and Windows
INFORMIX Certified Database Administrator
Phil Sherman wrote:
I don't know of any SQL that will do that but the following command will:
list tablespace containers for n show detail
where n is the tablespace number (TSID) for the tablespace you are
interested in viewing. From my RHEL4 UDB 8.1 FP9 system after connecting
to database SAMPLE:

[.....]$ db2 list tablespace containers for 2 show detail

Tablespace Containers for Tablespace 2

Container ID = 0
Name =
/home/db2inst1/db2inst1/NODE0000/SQL00001/SQLT0002.0
Type = Path
Total pages = 407
Useable pages = 407
Accessible = Yes
UDB 8.2 has a "Storage management tool" that will store snapshot data in
tables that are accessable with SQL. It can also "monitor" storage and
tack actions when user-defined threshholds have been reached. Check the
administration manuals for "Managing Storage".

Phil Sherman

kaming wrote:
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

Nov 12 '05 #5

Gert van der Kooij

In article <Cx************ ****@newssvr33. news.prodigy.co m>, Phil
Sherman (ps******@ameri tech.net) says...

It would be very nice if these administrative routines were documented
in the administration manuals.

Phil Sherman

It's documented in the 8.2 manuals at http://tinyurl.com/95k8a

Nov 12 '05 #6

kaming

Thanks you very much for your help.

Henry

"Gert van der Kooij" <ge**@invalid.n l> wrote in message
news:MP******** *************** *@news.xs4all.n l...

In article <Cx************ ****@newssvr33. news.prodigy.co m>, Phil
Sherman (ps******@ameri tech.net) says...
It would be very nice if these administrative routines were documented
in the administration manuals.

Phil Sherman

It's documented in the 8.2 manuals at http://tinyurl.com/95k8a

Nov 12 '05 #7

Similar topics

1956

Database size

by: Sharif T. Karim | last post by:

How would I get the total size of a mysql database in MB format? -- Sharif T. Karim ....you don't know wrath yet...

PHP

8218

Getting database size: a stored procedure to do it......

by: Robin Tucker | last post by:

As I had real problems working my head around sp_spaceused, I've written an SP to do it (I also noted a lot of questions about this when "searching"). Pass in a database name and it will return the size of the database as a float (ie. 0.75 for 0.75mb). Update usage set to 1 indicates the DB should update its size information before giving you...

Microsoft SQL Server

21190

Database size question

by: dchow | last post by:

Our database size is currently 4G and is incrementing at a rate of 45M/day. What is the max size of a SQL database? And what is the size beyond which the server performance will start to go down?

Microsoft SQL Server

5172

Decrease database size

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...

DB2 Database

8703

Database Size Limiting

by: Campano, Troy | last post by:

Is there a way to limit the size that an individual database can take up? My understanding is that all your databases go into a file system which is your 'catalog cluster'. But all the databases can grow until that filesystem is full. Is there a way to limit how big a database can be? Can you allocated space when the database is set up...

PostgreSQL Database

8185

How to collect DB2 Database size Version Lesser than 8.2

by: vijay.db | last post by:

Hi Group, It's really confusing to calculate the size of the DB2 UDB database in versions lesser than 8.2. In Version 8.2 if we run the query db2 "call get_dbsize_info(?,?,?,0)" it gives us an approximate size of the database. But it we try calculating the file system sizes of the database home directory, containers file system it...

DB2 Database

2254

Is there anything else to gather database size info?

by: Laurence | last post by:

Hi folks, My database DB03 is circular logging. I called the db2 procedure: get_dbsize_info to get the database size as below: Parameter Name : SNAPSHOTTIMESTAMP Parameter Value : 2008-04-08-06.39.49.518070 Parameter Name : DATABASESIZE Parameter Value : 24666112

DB2 Database

13068

database size vs. its performance

by: aleu | last post by:

Hi all, Could you please advise whether there are documents describing impact of MS SQL server 2005 database size on its performance? I have essentially two things in mind when writing the "database size": 1.) number of records stored within the database (e.g. one customer's account with 5,000 records vs. another account with 500,000...

Microsoft SQL Server

1282

Strange database size

by: ramprat | last post by:

Hi Everyone, I don't know if this is a problem but I have a database I have been building and currently there are 10 or less tables in it with 100 or less records in each. In many cases there are only around 10 records in some tables. I've got less than 10 forms in the database and one or two small queries. The forms have code behind command...

Microsoft Access / VBA

1775

Why is the database size is larger than the server disk space?

by: rednemesis | last post by:

Good Day everyone! When I calculated the size of the database on my system using the formula: Total Page size x Page size (4096) = Total Database Size Used page size x Page size (4096) = Total Used Page Size I then add up the results from all the tablespaces. My question is, with the result, why is the size of Total Database Size and...

DB2 Database

7885

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...

C / C++

8106

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

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...

Online Marketing

7638

The easy way to turn off automatic updates for Windows 10/11

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...

Windows Server

7948

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...

General

6250

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...

Career Advice

5484

Access Europe - Using VBA to create a class based on a table - Wed 1 May

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...

Microsoft Access / VBA

5213

Couldn’t get equations in html when convert word .docx file to html file in C#.

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...

C# / C Sharp

3642

Trying to create a lan-to-lan vpn between two differents networks

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...

Networking - Hardware / Configuration

923

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

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...

General