CODEWITHSUNDEEP
databasedb2admindb2-luw
ESP
ESP

Reputation: 999

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

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)

Upvotes: 4

Views: 16488

Answers (4)

Nilesh Solanki
Nilesh Solanki

Reputation: 356

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

Upvotes: 0

Matheus Sant Ana
Matheus Sant Ana

Reputation: 690

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

Upvotes: 1

Tom123456
Tom123456

Reputation: 77

Use db2top

l(for session)

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

Upvotes: 1

ESP
ESP

Reputation: 999

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.

Upvotes: 4

Related Questions