dell emc ibm db2 database agent · devices which are encapsulated data domain vdisk devices. when...

1 |

Dell EMC Database Agent for IBM DB2 © 2017 Dell Inc.


ProtectPoint DB2 Technology

DELL EMC IBM DB2 DATABASE AGENT

Backup and restore IBM DB2 environments with Dell EMC DB2 database agent. The database agent integrates directly into DB2 ACS to create snapshot based backups from primary storage to protection storage.

Intel text. Flex area for Intel kicker.

2 |


TABLE OF CONTENTS

Executive Summary .......................................................................................................................................... 3

Technology Overview ....................................................................................................................................... 3

Prerequisites ................................................................................................................................................... 4

Storage Setup for VMAX and XtremIO ................................................................................................................. 5

DB2 Host Setup ............................................................................................................................................. 16

Backup, Restore, and Delete ............................................................................................................................ 17

Troubleshooting .............................................................................................................................................. 22

Logs.............................................................................................................................................................. 24

DB2 Helpful Commands ................................................................................................................................... 26

3 |


Executive Summary

Dell EMC delivers Database Administrators (DBAs) complete control of DB2 backup, recovery, and disaster recovery with

advanced integration between Dell EMC Data Domain Boost, Dell EMC ProtectPoint Technology, and IBM DB2 ACS. This

white paper describes the key considerations for Database Agent for DB2, the setup and configuration steps, provides

examples of both backup and restore, and gives illustrates how to maintain your environment.

Technology Overview Dell EMC ProtectPoint Technology provides up to 20x faster backups and 10x faster recovery while eliminating impact on

the DB2 servers. By integrating primary storage (VMAX3, VMAX AF, XtremIO) and protection storage (Data Domain),

ProtectPoint technology reduces cost and complexity by eliminating traditional backup applications while still providing

the benefits of native backup. Overall, ProtectPoint provides the performance of snapshots with the functionality of

backups.

There are two data paths ProtectPoint technology can leverage. One for VMAX and one for XtremIO. In the VMAX

scenario (shown in Figure 1), DB2 database files are stored on tdevs that are linked to FTS (Federated Tiered Storage)

devices which are encapsulated Data Domain vDisk devices. When the DB2 admin triggers a backup the following occurs:

Database agent tells DB2 database to quiesce

Database agent has SMI-S take a snapshot of the tdevs that store the DB2 database files

Database agent tells the DB2 database to unquiesce

FTS Technology finds the changed blocks since the last backup

FTS devices (encapsulated Data Domain vDisk devices) send the changed blocks to Data Domain to be stored as a

full Data Domain static image

Figure 1

4 |


The data path for XtremIO is a little bit different. XtremIO is architected from the ground up as an All Flash Array, so

there is no concept of FTS (Federated Tiered Storage) to track the changed blocks between backups. Therefore, the

XtremIO datapath relies on RecoverPoint to trigger snapshots, track the change blocks, and move the data to Data

Domain. In this scenario when a DB2 admin triggers a backup the following occurs:

Database agent tells DB2 database to quiesce

Database agent informs RecoverPoint to snapshot the XtremIO LUNs DB2 database files are stored on

Database agent tells the DB2 database to unquiesce

RecoverPoint finds the changed blocks since the last backup

RecoverPoint sends the changed block to Data Domain via Boost

Data Domain stores the data in full static images on Data Domain vDisk devices

Figure 2

Perquisite Check List Integrating DB2, primary storage and protection storage with ProtectPoint Technology to enable faster backups and

restores requires several prerequisites to be met. Requirements are slightly different depending on the data path being

leveraged.

For VMAX the following is required:

VMAX3 or VMAX AF

Data Domain 4200+ with FC HBAs

DB2 host FC zoned to VMAX

Solution Enabler

VMAX FC zoned to Data Domain which requires 4 DX ports on VMAX

A virtual machine for Enterprise Copy Data Management (eCDM) to be deployed

A virtual machine for Data Domain Management Center (DDMC) to be deployed

5 |


For XtremIO the following is required:

XtremIO

Data Domain 4200+ with FC HBAs

RecoverPoint cluster

DB2 host FC zoned to XtremIO

RecoverPoint FC zoned to Data Domain

A virtual machine for Enterprise Copy Data Management (eCDM) to be deployed

A virtual machine for Data Domain Management Center (DDMC) to be deployed

Storage Configuration Setup

VMAX:

This section will go over step by step the setup of VMAX3 or VMAX AF and Data Domain, so ProtectPoint DB2 database

agent backups and restore work successfully. At a glance we are manually doing the following steps, and using eCDM to

automate the rest as you can see in Table 1:

Table 1

Operation ProtectPoint eCDM 1.1

Install SAN connectivity, configure VMAX DX ports, zoning Manual Manual

Create user Manual Manual

Create DDBoost storage unit Manual Manual

Create DD vDisk Pool(s) Manual Manual

Create vDisk Devices of same geometry and size as primary LUNs Manual Automated

Create vDisk device group for backup and restore Manual Automated

Create access group and add initiators to group Manual Manual

Add devices to access group Manual Automated

Encapsulate the vDisk Devices to VMAX FTS devices Manual Automated

Establish and Link devices on VMAX Manual Automated

Register Data Domain, XtremIO, DD vDisk Pools, and XtremIO in

RecoverPoint Manual Manual

Install database agent(s) Manual Manual

6 |


1. Enable vdisk and boost protocols in Data Domain. VDisk is a scsi protocol that allows Data Domain to create vDisk devices to store the DB2 database files being backed up in their native format which allows for faster restores.

a. Example: vdisk enable b. Example: ddboost enable

2. In Data Domain create a user for the vDisk Pool and Storage Unit the DB2 admin will be backing up to. a. Example: user add db2boost

3. Create a Storage Unit with a user assigned. The storage-unit is where logs and catalogs are stored. a. Example: ddboost storage-unit create db2boost user db2boost

4. Create a Data Domain vDisk Pool with same user as the Data Domain Storage Unit. The vDisk Pool is where the database files are stored as static images.

a. Example: vdisk pool create db2_vmax user db2boost

5. Create access group so the Data Domain FC HBA ports can see the VMAX DX ports a. Example: scsitarget group create vmax0312_dd5659 service vdisk b. Example: scsitarget group add vmax0312_dd5659 add <dx port initiators> c. Run this command in SYMCLIto discover what the DX port initiators are: symsan –sid <> list –sanports –

dx all –port all 6. In eCDM create a Protection Plan to automate the rest of the storage configuration for ProtectPoint. The steps

that will be automated are the following: a. Creation of vDisk device groups for backup and restore purposes b. Creation of vDisk devices that match in geometric size to the production LUNs being backed up in both

the restore and backup vdisk device group c. Adding the vDisk devices to the access group d. Encapsulation of the backup and restore vDisks on the VMAX FTS devices e. The establishment and link between the production devices and the backup encapsulated devices f. Placement of the encapsulated restore devices (vDisk restore devices) in a restore Storage Group on

VMAX 7. In eCDM create a Protection Plan like below. Make sure active protection is unchecked and provisioning is

checked. a. Set the credentials of the Data Domain Pool in eCDM, so eCDM can access the pool.

7 |


b. Set the credentials of the Data Domain Pool (db2_vmax) in eCDM, so it can create the vDisk devices and vDisk device groups.

8 |


c. Tag the Data Domain pool (db2_vmax) and VMAX Storage Group (db2_bl) with the same tag, so eCDM knows where to create the vDisk devices on DD and the encapsulated devices on VMAX

d. Assign the Protection Plan created to VMAX Storage Group db2_bl by clicking the shield icon on the far right of the storage group

8. Validate the automated steps eCDM created on Data Domain either in the eCDM Activity Monitor in the UI or manually by doing the following:

a. eCDM created backup (eCDM_P1492118895679-db2_bl) device group and restore (eCDM_R1492118905188-db2_bl) device group

b. eCDM created 11 vDisk devices in both the restore and backup vDisk device-group that match in geometric size to the production db2 LUNs

9 |


9. Validate the automated steps eCDM created on VMAX either in the eCDM Activity Monitor in the UI or manually by doing the following:

a. eCDM established the link between the production DB2 LUNs and the backup encapsulated devices on Data Domain

b. Restore FTS devices were placed in a restore Storage Group (eCDM_db2_bl_Rec) with the restore

encapsulated vDisk devices from Data Domain.

10 |


XtremIO

This section will go over step by step the setup of XtremIO and Data Domain, so ProtectPoint DB2 database agent

backups and restore work successfully. At a glance we are manually doing the following steps, and using eCDM to

automate the rest as you can see in Table 1:

Table 2

1. Enable vdisk and boost protocols in Data Domain. VDisk is a scsi protocol that allows Data Domain to create

vDisk devices to store the DB2 database files being backed up in their native format which allows for faster restores.

a. Example: vdisk enable b. Example: ddboost enable

2. In Data Domain create a user for the vDisk Pool and Storage Unit the DB2 admin will be backing up to. a. Example: user add ppuser

3. Create a Storage Unit with a user assigned. The storage-unit is where logs and catalogs are stored. a. Example: ddboost storage-unit create db2boost user ppuser

4. Create a two Data Domain vDisk Pools with same user as the Data Domain Storage Unit. One pool is for the backups and the other pool is for the restore. The vDisk Pool is where the database files are stored as static images.

a. Example backup pool: vdisk pool create bl_xio_backup_ecdm user ppuser b. Example restore pool: vdisk pool create bl_xio_restore_ecdm user ppuser

Operation ProtectPoint eCDM 1.1

Install SAN connectivity, configure VMAX DX ports, zoning Manual Manual

Create user Manual Manual

Create DDBoost storage unit Manual Manual

Create DD vDisk Pool(s) Manual Manual

Create vDisk Devices of same geometry and size as primary LUNs Manual Automated

Create vDisk device group for backup and restore Manual Automated

Create access group and add initiators to group Manual Manual

Add devices to access group Manual Automated

Encapsulate the vDisk Devices to VMAX FTS devices Manual Automated

Establish and Link devices on VMAX Manual Automated

Register Data Domain, XtremIO, DD vDisk Pools, and XtremIO in

RecoverPoint Manual Manual

Install database agent(s) Manual Manual

11 |


5. Create access group so the Data Domain FC HBA ports can see the RecoverPoint ports a. Example: ddboost fc group create <group name>

i. ddboost fc group add <group name> device-set ii. ddboost fc group add <group name> initiators <WWN of RP initiators>

6. In RecoverPoint register the XtremIO, Data Domain, and Data Domain backup vDisk Pool

7. In XtremIO map the LUNs that the DB2 database is stored on to RecoverPoint.

12 |


13 |


8. In eCDM create a Protection Plan to automate the rest of the storage configuration for ProtectPoint Technology. The steps that will be automated are the following:

a. Creation of vDisk device groups for backup and restore purposes b. Creation of vDisk devices that match in geometric size to the production LUNs being backed up in both

the restore and backup vdisk device group c. Adding the vDisk devices to the access group d. A Consistency Group in RecoverPoint with the XtremIO DB2 LUNs that were mapped to RecoverPoint.

9. In eCDM create a Protection Plan like below. Make sure active protection is unchecked and provisioning is checked.

a. Set the credentials of the Data Domain Pool in eCDM, so eCDM can access the pool.

b. Set the credentials of the Data Domain Pools (bl_xio158_backup_ecdm and bl_xio158_restore_ecdm) in eCDM, so it can create the vDisk devices and vDisk device groups.

14 |


c. Tag the Data Domain pools ((bl_xio158_backup_ecdm and bl_xio158_restore_ecdm) and XtremIO Consistency Group (bl_eCDM) with the same tag, so eCDM knows where to create the vDisk devices on Data Domain.

d. Assign the Protection Plan created to XtremIO Consistency Group bl_ecdm by clicking the shield icon on the far right of the storage group

15 |


10. Validate the automated steps eCDM created on Data Domain either in the eCDM Activity Monitor in the UI or manually by doing the following:

a. eCDM created backup (eCDM_P1492118895679-db2_bl) device group and restore (eCDM_R1492118905188-db2_bl) device group

b. eCDM created 11 vDisk devices in both the restore and backup vDisk device-group that match in geometric size to the production db2 LUNs

11. Validate the automated steps eCDM created on VMAX either in the eCDM Activity Monitor in the UI or manually by doing the following:

a. eCDM created the vDisk device groups and vDisk devices in Data Domain

16 |


b. eCDM created a Consistency Group in eCDM with the XtremIO DB2 LUNs (eCDM_bl_ecdm_protection)

DB2 Host Setup The host requires the DB2 database agent and Solution Enabler to be installed if the database is stored on VMAX. Also if

the database is stored on VMAX ensure gatekeepers are zoned to the DB2 host, so the host can see the VMAX.

1. Zoned gatekeepers to DB2 server a. Sign onto Unisphere b. Created 6 volumes in an existing storage group that are mapped to

the DB2 server c. The gatekeeper creation is a one-time thing. d. Then in vCenter added them as RDM to DB2 server VM.

2. Then do a discover on the DB2 server through SYMCLI:

a. ./symcfg discover -sid <>

17 |


b. Example of showing the disks VMAX can see on the host: ./sympd list

18 |


3. Next, install the Database Agent on the DB2 server, so it can integrate directly into ACS to create backups and

restores. Once it is installed move the configuration file under /home. Then edit the configuration file, so the

database agent knows where to send backup and restores. Refer to the Storage Setup section to find the

parameter that may be needed for the configuration file. If using the parameters from the examples in the

VMAX Storage Setup section the requied parts of the configuration file would look like this.

DB2INSTANCE = db2inst1

CLIENT = dmbun141.lss.emc.com

DDBOOST_USER =db2boost

DEVICE_HOST =10.99.213.101

DEVICE_PATH =/db2boost

VMAX_FASTX_RESTORE_SG = eCDM_db2_bl_Rec

DEVICE_POOL =db2_vmax

DDVDISK_USER = db2boost

RESTORE_TYPE_ORDER=PIT

PIT or Rollback o PIT is default for RESTORE_TYPE_ORDER.

This allows database specific restore. o Rollback will come from the local snapshot from the VMAX if available – this is the default

This is destructive and will delete everything on the LUN You may require a psrollback.res so that filesystems found on the LUN that you are rolling

back are ignored. RESTORE_TYPE_ORDER=rollback

VMAX_FASTX_RESTORE_SG = eCDM_db2_bl_Rec o Not the default so it has to be set (default NsrSnappSG) o eCDM created this for us

DEVICE_POOL: eCDM created 11 vDisk devices in the pool db2_vmax for both backup and restore.

RESTORE_DEVICE_POOL = used to restore from DD RESTORE_DEVICE_GROUP = used to restore from DD

DDVDISK_USER = is the user the database agent needs to access the Data Domain devices for backup and restore

DEVICE_PATH: This is the storage unit the database agent uses to store the logs and catalog information.

DEVICE_HOST: The Data Domain CLIENT: The DB2 host

4. Register the Lockbox.

a. You need to know the db2 group id if you are not accepting the default of the root group.

./ddbmadmin -L

Provide full pathname for the lockbox, or press Enter to accept the default

directory (/opt/dpsapps/common/lockbox):

Using the default pathname '/opt/dpsapps/common/lockbox' for lockbox.

Provide a group ID for lockbox ownership, or type 0 to accept the 'root user'

group as the lockbox owner: 102

Lockbox has been successfully created in the directory

'/opt/dpsapps/common/lockbox' with group ownership 102.

19 |


5. Next, register the Data Domain and RecoverPoint (if database is stored on XtremIO):

[db2inst1@dmbun141 ~]$ /opt/dpsapps/dbappagent/bin/ddbmadmin -P -z

/home/db2inst1/db2.cfg

Performing the registration of the device host '10.99.213.101' for DD

Boost user 'db2boost'.

Enter password:

Confirm password:

Logging in to the device host '10.99.213.101' with DD Boost

credentials.

Logging in to the device host '10.99.213.101' with DD Boost

credentials was successful.

Performing the registration of the device host '10.99.213.101' for DD

VDISK user 'ppuser'.

Enter password:

Confirm password:

Logging in to the device host '10.99.213.101' with DD VDISK

credentials.

Logging in to the device host '10.99.213.101' with DD VDISK

credentials was successful.

Lockbox directory is '/opt/dpsapps/common/lockbox'.

Device host '10.99.213.101' for DD Boost user 'db2boost' has been

registered in the lockbox.

Device host '10.99.213.101' for DD VDISK user 'ppuser' has been

registered in the lockbox.

6. Now setup DB2 backup utility parameters, VENDOROPT and LOGARCHOPT. a. As an example for this Database called PLUTO using the configuration file called db2inst1.cfg found in

/home/db2inst1/config. i. Make sure to include the “@” and run with the db2 userid.

db2 update db cfg for PLUTO using vendoropt @/home/db2inst1/db2.cfg

7. Ensure that VENDOROPT has been set by running:

db2 get db cfg for PLUTO | grep VENDOROPT

db2inst1@db2-01:~> db2 get db cfg for PLUTO | grep VENDOR

First log archive method (LOGARCHMETH1) =

VENDOR:/usr/lib/ddbda/lib64/libddboostdb2.so

Vendor options (VENDOROPT) = @/home/db2inst1/db2inst1.cfg

8. Now set the LOGARCHOPT parameters. This is required to point the log backup to the Data Domain.

db2 update db cfg for PLUTO using logarchmeth1

VENDOR:/usr/lib/ddbda/lib64/libddboostdb2.so logarchopt1

@/home/db2inst1/db2.cfg

9. Ensure that LOGARCHMETH has been set by running:

20 |


db2 get db cfg for PLUTO | grep LOGARCH

[db2inst1@db2-02 scripts]$ db2 get db cfg for PLUTO | grep LOGARCH

First log archive method (LOGARCHMETH1) =

VENDOR:/usr/lib/ddbda/lib64/libddboostdb2.so

Archive compression for logarchmeth1 (LOGARCHCOMPR1) = OFF

Options for logarchmeth1 (LOGARCHOPT1) =

@/home/db2inst1/db2inst1.cfg

Second log archive method (LOGARCHMETH2) = OFF

Archive compression for logarchmeth2 (LOGARCHCOMPR2) = OFF

Options for logarchmeth2 (LOGARCHOPT2) =

10. Set Retention to 14 days

db2 update db cfg for PLUTO using num_db_backups 14

db2 update db cfg for PLUTO using rec_his_retentn 14

db2 update db cfg for PLUTO using auto_del_rec_obj on

11. Restart the DB to activate these changes.

db2 force applications all

db2 deactivate db PLUTO

db2 backup db pluto to /dev/null (only required the first time you setup

logarchmeth)

db2 activate db PLUTO

DB2 Backup with Database Agent Now, it is time to run a backup!

Here is an example of a backup command using the ProtectPoint Technology to create a snapshot of the DB2 LUNs on

VMAX or XtremIO that send the changed blocked to Data Domain:

db2 backup db PLUTO online use snapshot library

/opt/dpsapps/dbappagent/lib/lib64/libddboostdb2.so options

@/home/db2inst1/db2.cfg

Now, try a restore.

1. First determine the backup to restore from:

21 |


db2acsutil LOAD /opt/dpsapps/dbappagent/lib/lib64/libddboostdb2.so

options '@/home/db2inst1/db2.cfg' query snapshot db PLUTO

2. Then run a restore. It is the same command for PIT restore or Rollback restore. What determines which restore

will happen depends on how the configuration file is set. db2 restore db pluto use snapshot library


@/home/db2inst1/db2.cfg taken at 20170419130621 LOGTARGET include

force

3. If restoring over top of the existing database with rollback:

date; db2 restore db pluto use snapshot library


@/home/db2inst1/db2.cfg taken at 20170419130621 LOGTARGET include

force replace existing; date

4. Rollforward to end of backup and logs

db2 rollforward database pluto to end of backup

db2 rollforward database pluto to end of logs and complete

[db2inst1@dmbun141 ~]$ db2 rollforward database pluto to end of backup

Rollforward Status

Input database alias = pluto

Number of members have returned status = 1

Member ID = 0

Rollforward status = DB working

Next log file to be read = S0000004.LOG

Log files processed = S0000002.LOG - S0000002.LOG

Last committed transaction = 2017-04-19-17.06.36.000000

UTC

DB20000I The ROLLFORWARD command completed successfully.

[db2inst1@dmbun141 ~]$ db2 rollforward database pluto to end of logs

and complete

Rollforward Status

Input database alias = pluto

Number of members have returned status = 1

Member ID = 0

Rollforward status = not pending

Next log file to be read =

Log files processed = S0000002.LOG - S0000002.LOG

22 |


Last committed transaction = 2017-04-19-17.06.36.000000

UTC

DB20000I The ROLLFORWARD command completed successfully.

5. To delete a snapshot query the snapshots taken to determine the one to delete:


options '@/home/db2inst1/db2.cfg' query snapshot db PLUTO

[db2inst1@dmbun141 ~]$ db2acsutil LOAD


'@/home/db2inst1/db2.cfg' query snapshot db PLUTO

Instance Database Part Image Time Host First Log

========== ========== ==== ============== ========== ==========

db2inst1 PLUTO 0 20170418095510 2

db2inst1 PLUTO 0 20170418093000 1

6. Then delete:


options '@/home/db2inst1/db2.cfg' delete snapshot db PLUTO taken

at 20170418093000

Troubleshooting The first thing you do is enable traces so that the correct data is collected and you can review the errors in the

ProtectPoint logs. Within the db2.cfg file comment out the following parameters for debug and enable level 9 and add in

the Proxy Pfile location and name.

DEBUG_LEVEL = 9

DPRINTF = true

PROXY_PFILE=/home/db2inst1/pfile.cfg

Common Errors A. Reason Code 11 generally caused due to:

a. ddbsm daemon is NOT started b. Issues in db2.cfg file.

c. If database is NOT on snappable LUNs

a. In other words all DB2 must be on VMAX or XtremIO LUNs d. Solution Enabler not installed

23 |


If ddbsm daemon is NOT started/running this error will show in the log /opt/dpsapps/dbappagent/logs/ddbsmd.log

[root@dmbun141 logs]# cat ddbsmd.log

139545 1492438789 5 1 23 3138410272 30336 0 dmbun141.lss.emc.com ddbsmd 32

Unable to spawn process '%s': %s 2 23 6 ddbsmd 24 25 No such file or directory









Check for ddbsmd process using ps –ef | grep ddbsm. If it is not running run ./ddbsm start in the directory opt/dpsapps/dbappagent/bin/

[db2inst1@dmbun141 bin]$ ./ddbsm

usage: ddbsm {start|stop}

Configuration File must have these parameters at a minimum. Ensure that they are correct.

DB2INSTANCE = db2inst1

CLIENT = dmbun141.lss.emc.com

DDBOOST_USER =db2boost

DEVICE_HOST =10.99.213.101

DEVICE_PATH =/db2boost

VMAX_FASTX_RESTORE_SG = eCDM_db2_bl_Rec

DEVICE_POOL =db2_vmax

DDVDISK_USER = db2boost

a. Solution Enabler not installed on the DB2 server

Look to the ddbsmd.* log in /opt/dpsapps/dbappagent/logs

root@dmbun141 logs]# vi ddbsmd.2017_04_17.18_50_46.19220.1492469446.trace

“ Failed to set up SymApi handle: Unable to get installed Solutions Enabler

version, vmax error code 1, Solutions Enabler is not installed,

24 |


/usr/lib64/libsymlvm64.so: cannot open shared object file: No such file or

directory

[msg #83 BrcSession.cpp 493 PSDBG 5] Failed to initialize snapshot providers:

Failed to set up SymApi handle: Unable to get installed Solutions Enabler version,

vmax error code 1, Solutions Enabler is not installed, /usr/lib64/libsymlvm64.so:

cannot open shared object file: No such file or directory

Error [msg #85 BrcApi.cpp 390 PSDBG -1] Failed to create brc_session: Failed to

initialize snapshot providers: Failed to set up SymApi handle: Unable to get

installed Solutions Enabler version, vmax error code 1, Solutions Enabler is not

installed, /usr/lib64/libsymlvm64.so: cannot open shared object file: No such file

or directory

[msg #86 BrcApi.cpp 400 PSDBG 3] Leaving pb_init”

Logs

1. Ddbsmd.log

tail -f /opt/dpsapps/dbappagent/logs/ddbsmd.log

2. Tail DDBEA log

tail -f /opt/dpsapps/dbappagent/logs/libddboostdb2_acs_DB2_2017_05_05.11_39_29.16570.log

(you have to take the most recent one, you can do a ls -lia /opt/dpsapps/dbappagent/logs/*acs*

25 |


3. You can also tail the db2diag.log

tail -f /home/db2inst1/sqllib/db2dump/db2diag.log

To kill the restore process

ps -ef | grep db2

For the last db2inst1 with db2vend

db2inst1 18000 3112 0 12:03 ? 00:00:00 db2vend (db2agent - 61 (PLUTO)) 0

kill -9 3112

26 |


Useful DB2 commands

1. To Connect to a DB

db2 connect to CUSTOMER

2. List tablespaces

db2 list tablespaces

3. List database name

db2 list database directory

4. Query DB2 Instance Name

db2inst1@db2-01:~> db2ilist

db2inst1

5. Backup from CLI

db2 backup db CUSTOMER online load /usr/lib/libnsrdb2.so options @/nsr/apps/config/db2inst1.cfg dedup_device

6. Restore

db2 restore database CUSTOMER load /usr/lib/libnsrdb2.so open 2 sessions options @/nsr/apps/config/db2inst1.cfg

then Rollforward

db2 rollforward database CUSTOMER to end of logs and complete

7. List History

db2 list history backup since 20141016170000 for CUSTOMER

27 |


Lists all backups and restore since yyyymmddhhmmss (so this is listing all backups since Oct 16, 2014 at 5 oclock).

8. Prune History

db2 prune history 201501111842

Where 201501111842 is a timestamp that you want to delete up to.

Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID

-- --- ------------------ ---- --- ------------ ------------ --------------

B D 20150111184230001 N O S0000019.LOG S0000019.LOG

----------------------------------------------------------------------------

Contains 3 tablespace(s):

00001 SYSCATSPACE

00002 USERSPACE1

00003 SYSTOOLSPACE

----------------------------------------------------------------------------

Comment: DB2 BACKUP CUSTOMER ONLINE

Start Time: 20150111184230

End Time: 20150111184328

Status: A

----------------------------------------------------------------------------

EID: 47 Location: /usr/lib/ddbda/lib64/libddboostdb2.so

9. checking vendoropt configuration

db2 get db cfg for CUSTOMER | grep VENDOR

10. checking logs configuration

db2 get db cfg for CUSTOMER | grep LOG

28 |


11. List history of backups for a particular DB

db2 list history backup all for <database>

12. Disconnect from Database

db2 disconnect CUSTOMER

13. If the restore is pending, then do the following:

db2 restore database CUSTOMER continue

14. To drop a Database

db2 drop database PLUTO

15. To kill a backup job.

db2 disconnect CUSTOMER

db2 list applications for db CUSTOMER

db2 "force application (6)"

dell emc ibm db2 database agent · devices which are encapsulated data domain vdisk devices. when...

Documents