Backing up database have multiple reasons - It could be for creating a replica database of particular instance, or to prevent the data loss, or it may be you want to preserve the snapshot of particular database state. In my case, its option #3. Lets see how to take cold back up of one of the DB2 instance.
DB2ADMIN@nrc3asqdb14:/local/db/DB2ADMIN$ db2 "connect to TESTDB76"
Database Connection Information
Database server = DB2/AIX64 10.5.10
SQL authorization ID = DB2ADMIN
Local database alias = TESTDB76
DB2ADMIN@nrc3asqdb14:/local/db/DB2ADMIN$ db2 "list active databases"
Active Databases
Database name = TESTDB76
Applications connected currently = 2
Database path = /local/db/DB2ADMIN/DB2ADMIN/NODE0000/SQL00004/MEMBER0000/
DB2ADMIN@nrc3asqdb14:/local/db/DB2ADMIN$ db2 "list applications"
Auth Id Application Appl. Application Id DB # of
Name Handle Name Agents
-------- -------------- ---------- -------------------------------------------------------------- -------- -----
DB2ADMIN db2bp 38026 *LOCAL.DB2ADMIN.190603121247 TSTDB76 1
TSTDBUSR db2jcc_applica 28146 10.13.25.130.42990.190531210658 TSTDB76 1
DB2ADMIN@nrc3asqdb14:/local/db/DB2ADMIN$ db2 "force application (28146)"
DB20000I The FORCE APPLICATION command completed successfully.
DB21024I This command is asynchronous and may not be effective immediately.
DB2ADMIN@nrc3asqdb14:/local/db/DB2ADMIN$
DB2ADMIN@nrc3asqdb14:/local/db/DB2ADMIN$ db2 "list applications" | grep TSTDB76
DB2ADMIN db2bp 38026 *LOCAL.DB2ADMIN.190603121247 TSTDB76 1
Before proceeding with any sort of backup (hot/cold) we need to check if we have enough space to accomodate the backup. In DB2 we can get this information by simply giving call to one of the procedure - get_dbsize_info
It accepts one parameter and sends out three parameters viz. Snapshottimestamp (of data type timestamp), Databasesize (of data type bigINT), and Databasecapacity (also, of data type bigINT). The input parameter is of data type integer and have its value as -1 (which means refresh the cache value using default value of 30 mins). For more information, please visit here GET_DBSIZE_INFO.
EXAMPLE:
-------
db2 "call get_dbsize_info(?,?,?,-1)"
Value of output parameters
--------------------------
Parameter Name : SNAPSHOTTIMESTAMP
Parameter Value : 2019-06-03-10.54.54.003607
Parameter Name : DATABASESIZE
Parameter Value : 4781162496
Parameter Name : DATABASECAPACITY
Parameter Value : 217042366464
Return Status = 0
Once we get the database size information, next we will need to figure out if that much space is available on our database server -
EXAMPLE:
-------
df
Filesystem 512-blocks Free %Used Iused %Iused Mounted on
/dev/hd4 12582912 1475720 89% 96337 29% /
/dev/hd2 19660800 5314136 73% 94747 14% /usr
/dev/hd9var 6422528 3041680 53% 8593 3% /var
/dev/hd3 10485760 10330056 2% 412 1% /tmp
/dev/hd1 16777216 16654920 1% 179 1% /home
/dev/hd11admin 262144 261384 1% 13 1% /admin
/proc - - - - - /proc
/dev/hd10opt 20971520 10136376 52% 23914 3% /opt
/dev/lv_dbbackup 104857600 104799088 1% 17 1% /db2backup
/dev/lv_local 775946240 390568352 50% 12257 1% /local
Once we determine where to extract our backup, next we need to proceed with the actual process of cold database backup. For that, we need to take our database down by terminating all connections linked to the concerned database and later we will have to deactivate our concerned database so that it doesn't accepts anymore connections.
db2 "list applications"
Auth Id Application Appl. Application Id DB # of
Name Handle Name Agents
-------- -------------- ---------- -------------------------------------------------------------- -------- -----
TSTDBUSR EPDatabase 38011 10.13.24.179.37498.190603120503 TSTDB99 1
TSTDBUSR EPDatabase 37977 10.13.24.179.37446.190603120208 TSTDB99 1
TSTDBUSR db2jcc_applica 449 10.13.23.75.56834.190518075636 TSTDB99 1
TSTDBUSR db2jcc_applica 7 10.13.24.179.36344.190518072303 TSTDB99 1
TSTDBUSR db2jcc_applica 18638 10.13.21.183.49190.190530152903 TSTDB02 1
TSTDBUSR db2jcc_applica 47486 10.13.24.210.48596.190525080810 TSTDB02 1
TSTDBUSR EPDatabase 37978 10.13.21.183.61685.190603120419 TSTDB02 1
TSTDBUSR EPDatabase 37965 10.13.24.246.52938.190603120720 TSTDB11 1
TSTDBUSR db2jcc_applica 152 10.13.26.77.49808.190518072529 TSTDB11 1
DB2ADMIN db2bp 38026 *LOCAL.DB2ADMIN.190603121247 TSTDB76 1
TSTDBUSR db2jcc_applica 28146 10.13.25.130.42990.190531210658 TSTDB76 1
TSTDBUSR db2jcc_applica 38033 10.61.35.249.50805.190603121615 TSTDB98 1
Once we have identified the the application handle then we will need to kill it force fully. If not done so, DB2 will not allow us to take the backup. Force application disconnects the user (application) off the database.
db2 "force application (28146)"
DB20000I The FORCE APPLICATION command completed successfully.
DB21024I This command is asynchronous and may not be effective immediately.
The terminate command disconnects current session from which the database i.e. it disconnects the command line processor's backend process. It is recommended to execute terminate command prior to db2stop or before taking backup.
db2 "terminate"
DB20000I The TERMINATE command completed successfully.
Post terminate, we will need to deactivate our database which will take our concerned database down and prevent any user from connecting to the instance. DB2 stops all necessary database services which accepts connection to the database.
db2 "deactivate db TSTDB76"
SQL1495W Deactivate database is successful, however, there is still a
connection to the database.
Since we have one more active connection, we need to identify that and will have kill that. Until that is done, we cannot proceed with the backup process. To identify the connection, we will have to list the applications again -
db2 "list applications"
Auth Id Application Appl. Application Id DB # of
Name Handle Name Agents
-------- -------------- ---------- -------------------------------------------------------------- -------- -----
TSTDBUSR db2jcc_applica 449 10.13.23.75.56834.190518075636 TSTDB99 1
TSTDBUSR EPDatabase 38133 10.13.24.179.37848.190603122603 TSTDB99 1
TSTDBUSR EPDatabase 38146 10.13.24.179.37798.190603122308 TSTDB99 1
TSTDBUSR db2jcc_applica 7 10.13.24.179.36344.190518072303 TSTDB99 1
TSTDBUSR db2jcc_applica 18638 10.13.21.183.49190.190530152903 TSTDB02 1
TSTDBUSR db2jcc_applica 47486 10.13.24.210.48596.190525080810 TSTDB02 1
TSTDBUSR EPDatabase 38179 10.13.21.183.35547.190603122519 TSTDB02 1
TSTDBUSR EPDatabase 38142 10.13.24.246.53030.190603123723 TSTDB11 1
TSTDBUSR db2jcc_applica 152 10.13.26.77.49808.190518072529 TSTDB11 1
TSTDBUSR db2jcc_applica 38154 10.13.25.130.56042.190603122931 TSTDB76 1
db2 "force application(38154)"
DB20000I The FORCE APPLICATION command completed successfully.
DB21024I This command is asynchronous and may not be effective immediately.
db2 deactivate database TSTDB76
DB20000I The DEACTIVATE DATABASE command completed successfully.
Once we have killed the application and have deactivated the database, its perfect to move on to our next logical task - backing up the database (cold). The general syntax to backup the database is db2 "backup database <databse_name> to <location>"
db2 "backup database TSTDB76 to ."
Backup successful. The timestamp for this backup image is : 20190603084430
ls -lrt
total 9594272
-rw------- 1 DB2ADMIN db2admin 4912263168 Jun 03 08:44 TSTDB76.0.DB2ADMIN.DBPART000.20190603084430.001
Now that we have completed our backup activity, we should bring our database back to normal state where user can connect with the database.
db2 "activate database TSTDB76"
DB20000I The ACTIVATE DATABASE command completed successfully.
This bring us to the logical end of the data backup activity. Normally, after such backup, backup files are moved to some location from where can be further consumed. If you have any doubt in this, please ask lots and lots of question until you get the clarity. This will also help me gain new insight.
db2 "Happy Exploring"
DB20000I ENJOY!
