Backup DB2 – How to Determine Free Space Needed for DB2 Backup File

backupdb2db2-10.5db2-luw

I have a db2 database on Linux (DB2 10.5 fp08).
Now I would like to backup it, before backup I can get the database size with db2 "call GET_DBSIZE_INFO (?,?,?,0)"
For example it is 6 GB.

I know if the backup location has >6 GB free space is Ok for this backup.

But do you know if there is any offical documents/post about this.
What is the best higher free space for this kind of backup?

Best Answer

The backup image contains only used extents, so it might be substantially smaller than the sum of tablespace sizes, depending on the tablespaces' types and their physical properties.

On the other hand, in DB2 10.5 the backup image will by default include log files that are modified during the backup, so depending on how active the database is during the backup you may need to include the log sizes into your calculation.

Finally, the backup image can be compressed. Compression ratio is impossible to estimate in advance.

As a result, you can only estimate the upper limit for free space required to store the backup image, which would be the sum total of the current tablespace sizes plus the size of included log files plus the backup image header.

Related Solutions

Restore DB2 Database – Cannot Connect After Offline Backup

Found the answer here.

I needed to run the command

db2 rollforward db database_name to end of backup and complete;

This commits everything to the logs and places the database in a no longer pending state, thus allowing connection to it. I needed to do this for each database restored.

EDIT: Found this nifty nugget while attending a DB2NightShow episode. With 9.7 FP2, they introduced a registry variable called DB2_RESTORE_GRANT_ADMIN_AUTHORITY. This is handy if you are restoring a backup from one instance to another, as it automatically grants instance owner SECADM, DBADM, DATAACCESS, and ACCESSCTRL on the databases restored. With 9.7 FP5, the setting is dynamic and does not require bouncing of the instance. I realize my original question had nothing to do with security, but it can be encountered and thought that this would be a handy tidbit placed here.

EDIT #2: I recently found out that if the backup is an offline backup based on an archival logged database, I could have included the clause WITHOUT ROLLING FORWARD in my RESTORE command. This would have taken the database out of roll forward pending state immediately upon successful restore, which would mean that I would not have had to then issue the ROLLFORWARD DB command.

Db2 LUW help for an oracle user : create database hangs without any output, but database got created

hmm, scratch my earlier answer about a trailing ';'
I still suspect it's "hanging" because it is not a complete statement, it's waiting on you to complete it. (don't have a db2 instance available at the moment to test unfortunately)

try this:

CREATE DATABASE mytestdb
  AUTOMATIC STORAGE YES

It's a good idea to run

db2 list applications

in another session while you wait for the command to complete. That would tell you if something is indeed happening.

Best Answer

Related Solutions

Restore DB2 Database – Cannot Connect After Offline Backup

Db2 LUW help for an oracle user : create database hangs without any output, but database got created

Related Question