How to Find the Size of a DB2 Database

You may also like...

15 Responses

  1. Santosh B says:

    Thank You. This is a useful post for DBAs.

  2. leo says:

    Good article,

    Side question…
    Ember, Are you really a crook?


    • Ember Crooks says:

      The really funny thing is that my husband’s family has at least one member in law enforcement in each generation. Think “Sheriff Crooks” and “Officer Crooks”.

  3. nobantu says:

    Thank you so much ,

    I am kindly looking for history of the size of database for past months , and what are option that i can use.

    • Ember Crooks says:

      You must keep/store that data yourself in order to have it. If you have not been doing so, but have regular backup images, the backup sizes can give you a rough idea.

  4. Gerardo says:

    thanks, most useful 🙂

  5. Jon Ostrowski says:

    Thanks for this information!

  6. Hell Demons says:

    Good post for DBAs

  7. Nitesh says:

    can we get details about growth of a SMS tablespace from any SQL ? My idea is if we will get information how we expanded our tablespaces we can do a reverse calculation from current size and can provide not accurate but a near about database size. I am able to fetch DMS tablespace extension detail from SYSIBMADM.DB_HISTORY[db2 -x “select CMD_TEXT from SYSIBMADM.DB_HISTORY where OPERATION=’T’ and SQLSTATE is NULL with ur “] but unable to find out details for SMS tablespace.

    • Ember Crooks says:

      The data you’re getting is only about DMS tablespaces that are auto resized. SMS tablespaces do not record such data – they simply increase or decrease in size without noting it. Really, you should be recording data over time to get this kind of information. Remember that for DMS tablespaces, they do not auto-decrease, so if you delete a lot of data, that may not be reflected in your database size unless you also decrease the DMS tablespace sizes manually. I like to look at size information at the table level using the sysibmadm.admintabinfo system view.

  8. vineet says:

    Is it necessary to have statistics up-to-date for accurate size determination using get_dbsize_info Function?

    • Ember Crooks says:

      No. get_dbsize_info adds up the pages used by tablespaces. Runstats collects data at the table, index, and possibly the statistical view level. get_dbsize_info is actually pretty expensive when it runs because it calculates the sizes fresh (assuming it has not been run in the last 30 minutes, and you specify the -1 parameter in the last position).

  9. David Colton says:

    Excellent post. Just what I needed as a part time DBA looking to get database size statistics.

  10. Raj S says:

    good article. Thanks.
    Recently, I noticed that in our case over the period of time database is growing and now DBSIZE > DBCAPACITY which leads me to search other options to calculate DB size and capacity since get_dbsize_info is not giving correct information.

    Any ideas or admin views which report both DBSIZE and DBCAPACITY correctly rather than going so many hoops or scripts. Thanks in advance

    Value of output parameters
    Parameter Name : SNAPSHOTTIMESTAMP
    Parameter Value : 2015-01-19-

    Parameter Name : DATABASESIZE
    Parameter Value : 3331877879808

    Parameter Name : DATABASECAPACITY
    Parameter Value : 3959737793024

    Return Status = 0

    • Ember Crooks says:

      In the output you give, database capacity is still larger than database size. A stored procedure is required as this looks at so many different areas of data, not all of which are tracked internally. You can always look at the actual filesystems and space used/available on them. SYSIBMADM.DBPATHS is useful for that approach, adding in tracking space for the diagnostic log.

      It is also a good idea to track table growth over time – I often use SYSIBMADM.ADMINTABINFO for that.

Leave a Reply

Your email address will not be published. Required fields are marked *