Shulou(Shulou.com)05/31 Report--

Editor to share with you how to check the size of the database, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's learn about it!

Usually before we do a database backup (tablespace backup), we need to know the size of the database (tablespace), and then allocate space to the backup files and roughly estimate the backup time.

Here are several ways to view data size or tablespace size (tablespace backup):

1. View the usage of tablespaces and their utilization

Db2 "select substr (tbsp_name,1,20) as TABLESPACE_NAME,substr (tbsp_content_type,1,10) as TABLESPACE_TYPE,sum (tbsp_total_size_kb) / 1024 as TOTAL_MB,sum (tbsp_used_size_kb) / 1024 as USED_MB,sum (tbsp_free_size_kb) / 1024 as FREE_MB,tbsp_page_size AS PAGE_SIZE from SYSIBMADM.TBSP_UTILIZATION group by tbsp_name,tbsp_content_type,tbsp_page_size order by 1"

two。 Count the table space usage of all nodes

Db2 "select substr (TABLESPACE_NAME,1,20) as TBSPC_NAME,bigint (TOTAL_PAGES * PAGE_SIZE) / 1024 db_name', 1024 as TOTAL_MB,used_pagesPAGE_SIZE/1024/1024 as USED_MB, free_pagesPAGE_SIZE/1024/1024 as FREE_MB from table (snapshot_tbs_cfg ('db_name',-2)) as snapshot_tbs_cfg"

Replace db_name with the name of the database to be looked up

3. Operation of querying database size

Db2 "select (sum (total_pages) * 4096) / (1024024) Tot_allocat_spceinMB from table (snapshot_tbs_cfg (',-1)) TBS_SPCE"

The 4096 here is the default value of 4K pagesize, which can be modified according to the actual situation.

4. Estimate database size based on tablespace usage

First connect to the database:

Db2 connect to db_name

View the tablespace detail:

Db2 list tablespaces show detail

As you can see in the figure, there are multiple tablespaces under the database. We select tablespaces of the order of magnitude * * to participate in the calculation (if there are multiple tablespaces of the same order of magnitude and the order of magnitude of * *, we need to add them up first) as shown in the red border; we ignore several tablespaces in the blue box (because these tablespaces are only fractions relative to large tablespaces)

Calculation formula:

(Total pages * Page size) / (1024024) GB

Bring into the calculation:

(174030848,4096) / (1024,1024) GB ~ 679.808 GB

Compared with the results of option 3:

It can be found that there is not much difference between the estimated results and the results of option 3; this method is relatively simple and complex code is not allowed to be remembered.

The above is all the contents of the article "how to check the size of the database". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.