Many ways are there to find db size in db2. The easiest way to find the data base size is to run the following commands
I) db2 connect to dbname
db2 call get_dbsize_info(?,?,?,-1), where get_dbsize_info is the built-in stored procedure.
In the above procedure, first 3 parameters are output parameters and the 4th parameter is the only input parameter.The value of output parameters of my data base (SAMPLE) are returned as given below.
Parameter Name : SNAPSHOTTIMESTAMP Parameter Value : 2014-06-15-00.32.43.272000 Parameter Name : DATABASESIZE Parameter Value : 165322752 Parameter Name : DATABASECAPACITY Parameter Value : 404513460224 Return Status = 0
When you run the above procedure first time for any database, the table STMG_DBSIZE_INFO with one row having the fields of SNAPSHOT_TIMESTAMP, DB_SIZE, DB_CAPACITY is created and the value of output paramaeters are cached in the table. Every time when you run the same procedure, the db size is not recalculated. When you run the same again within 30 minutes, the db size is returned from the cached value. When you run the procedure after 30 mins, the db size is recalculated and cached in the table. The input parameter, -1 indicates the default refresh time of 30 mins where as 0 tells an immediate refresh of the cached value. Any positive value (n) indicates that the db size is returned from the cached value for the next n minutes from the old SNAPSHOT_TIMESTAMP.
Now, running the query "select * from SYSTOOLS.STMG_DBSIZE_INFO" will output the same value.
db2 "select * from SYSTOOLS.STMG_DBSIZE_INFO" SNAPSHOT_TIMESTAMP DB_SIZE DB_CAPACITY -------------------------- -------------------- -------------------- 2014-06-15-00.32.43.272000 165322752 404513460224
II) You can also calculate the db size from the total size of the all data files related to the db.
Change to the directory where data files of your data base are stored and use the command du in linux. In windows, right click on the folder where data files are captured and click on properties which shows the size.
III) You can also find the approx db size using the current db backup size.
Take the backup of the database and you can find the size of the backup file using ls command in linux.
Note: Database capacity is different from database size. Database size is calculated from used pages for all table spaces both in DMS and SMS where as Database capacity is calculated from the usable pages for all table spaces in DMS and SMS.
DMS - Database Managed Space
SMS - System Managed Space
More on db size, you can refer the ibm technical tutorials.
0 comments:
Post a Comment