How do I find the size of a DB2 (luw) database?

Question

I know you can look at the size of an uncompressed backup, but that's not practical.

Is there a command to find the size of the database while it is online? (In Linux/Unix/windows)

I imagine what you meant to ask was how to do this for a DB2 LUW database. The answer(s) for other DB2 platforms (IBM i & z/OS) would be different. — WarrenT, Jun 17 '14 at 22:33

score 4 · Accepted Answer · answered Jun 17 '14 at 13:10

When connected to a database as db2admin (or with similar permissions), use the following command:

 call get_dbsize_info(?,?,?,-1);

The first three parameters are output parameters:

Value of output parameters
--------------------------
Parameter Name  : SNAPSHOTTIMESTAMP
Parameter Value : 2014-06-17-13.59.55.049000

Parameter Name  : DATABASESIZE
Parameter Value : 334801764352

Parameter Name  : DATABASECAPACITY
Parameter Value : 1115940028416

Return Status = 0

The size is given in bytes, so divide by 1024^3 to get Gb.

The final parameter is how often the snapshot is refreshed. -1 is to use default settings.

Further reading...

Note: This command does not take into account logs, etc. - so, it may appear much larger on disk.

score 1 · Answer 2 · edited Jun 24 '14 at 15:23

1

Use db2top

l(for session)

p(when press small p it will show the total size of db n used size of db)

edited Jun 24 '14 at 15:23

Xan

74,770
16
179
206

answered Jun 24 '14 at 15:00

Tom123456

77
1
9

How exactly have you created the I and P keys in your post? Pretty cool! Is db2top only for UNIX? – ESP Sep 18 '14 at 16:07
@ESP you use the HTML tag `` – Burgi Feb 15 '17 at 21:22

score 1 · Answer 3 · answered May 18 '22 at 17:07

1

For specific schema, in KBytes, use:

SELECT sum(TOTAL_P_SIZE) FROM (
SELECT TABNAME, (DATA_OBJECT_P_SIZE + INDEX_OBJECT_P_SIZE + LONG_OBJECT_P_SIZE +
LOB_OBJECT_P_SIZE + XML_OBJECT_P_SIZE) as TOTAL_P_SIZE
FROM SYSIBMADM.ADMINTABINFO
WHERE TABSCHEMA='PUBLIC'
)

Reference: https://www.ibm.com/support/pages/how-do-i-find-out-disk-space-usage-managing-server-octigate-database-tables

answered May 18 '22 at 17:07

Matheus Santz

538
6
7

only thing thing that worked for me using a db2 database and needing to use pure sql with some django tables which I think might have messed up the other commands – Matthias Aug 24 '23 at 09:28

score 0 · Answer 4 · answered May 24 '22 at 08:52

0

Following command will show you memory used by database online :

db2pd -dbptnmem

You can monitor variety of stuff with db2pd command :

https://www.ibm.com/docs/en/db2/11.1?topic=commands-db2pd-monitor-troubleshoot-db2-engine-activities

answered May 24 '22 at 08:52

Nilesh Solanki

336
4
19

How do I find the size of a DB2 (luw) database?

4 Answers4