Example to create a Windows Batch script to run DB2 backup commands

Contents of d:\db2_backup.bat

db2cmd /c /w "db2 backup database sampledb online to "d:\backup" include logs without prompting"

And schedule the script using Windows Scheduler.

DB2 Online backup and restoration example

C:\Program Files\IBM\SQLLIB\BIN>db2 connect to sampledb

C:\Program Files\IBM\SQLLIB\BIN>db2 get db config |findstr LOGRETAIN
 Log retain for recovery enabled             (LOGRETAIN) = OFF

C:\Program Files\IBM\SQLLIB\BIN>db2 get db config |findstr LOGARCHMETH1
 First log archive method                 (LOGARCHMETH1) = OFF


C:\Program Files\IBM\SQLLIB\BIN>db2 update db config using logretain "recovery"

C:\Program Files\IBM\SQLLIB\BIN>db2 update db config using logarchmeth1 "disk:c:\DB2\Logs"

C:\Program Files\IBM\SQLLIB\BIN>db2stop force

C:\Program Files\IBM\SQLLIB\BIN>db2start


C:\Program Files\IBM\SQLLIB\BIN>db2 backup database sampledb online to "d:\backup" include logs without prompting

Backup successful. The timestamp for this backup image is : 20150803223509

C:\Program Files\IBM\SQLLIB\BIN>db2 force application all

C:\Program Files\IBM\SQLLIB\BIN>db2 restore database sampledb from "d:\backup" taken at 20150803223509

C:\Program Files\IBM\SQLLIB\BIN>db2 rollforward database sampledb to end of backup and complete

                                 Rollforward Status

 Input database alias                   = sampledb
 Number of nodes have returned status   = 1

 Node number                            = 0
 Rollforward status                     = not pending
 Next log file to be read               =
 Log files processed                    = S0000004.LOG - S0000005.LOG
 Last committed transaction             = 2015-08-03-14.35.14.000000 UTC

DB20000I  The ROLLFORWARD command completed successfully.

Enable DB2 statement monitoring using event trace

C:\Program Files\IBM\SQLLIB\BIN>db2 connect to SAMPLEDB

   Database Connection Information

 Database server        = DB2/NT64 9.7.6
 SQL authorization ID   = ADMINIST...
 Local database alias   = SAMPLEDB

C:\Program Files\IBM\SQLLIB\BIN>db2 get monitor switches

            Monitor Recording Switches
Buffer Pool Activity Information  (BUFFERPOOL) = OFF
Lock Information                        (LOCK) = OFF
Sorting Information                     (SORT) = OFF
SQL Statement Information          (STATEMENT) = OFF
Table Activity Information             (TABLE) = OFF
Take Timestamp Information         (TIMESTAMP) = ON  03/31/2015 15:34:19.915686
Unit of Work Information                 (UOW) = OFF


C:\Program Files\IBM\SQLLIB\BIN>db2 update monitor switches using statement on
DB20000I  The UPDATE MONITOR SWITCHES command completed successfully.

C:\Program Files\IBM\SQLLIB\BIN>db2 create event monitor stmon for statements write to file 'C:\temp'
DB20000I  The SQL command completed successfully.


C:\Program Files\IBM\SQLLIB\BIN>db2 set event monitor stmon state=1
DB20000I  The SQL command completed successfully.

C:\Program Files\IBM\SQLLIB\BIN>db2 create table tbl_donghua(id integer)
DB20000I  The SQL command completed successfully.

C:\Program Files\IBM\SQLLIB\BIN>db2 insert into tbl_donghua values (1)
DB20000I  The SQL command completed successfully.

C:\Program Files\IBM\SQLLIB\BIN>db2 select * from tbl_donghua

ID
-----------
          1

  1 record(s) selected.


C:\Program Files\IBM\SQLLIB\BIN>db2 drop table tbl_donghua
DB20000I  The SQL command completed successfully.

C:\Program Files\IBM\SQLLIB\BIN>db2 set event monitor stmon state 0
DB20000I  The SQL command completed successfully.

C:\Program Files\IBM\SQLLIB\BIN>db2evmon -path c:\temp > c:\temp\db2stmt.sql

Reading c:\temp\00000000.EVT ...

C:\Program Files\IBM\SQLLIB\BIN>db2 drop event monitor stmon
DB20000I  The SQL command completed successfully.

C:\Program Files\IBM\SQLLIB\BIN>db2 update monitor switches using statement off
DB20000I  The UPDATE MONITOR SWITCHES command completed successfully.

C:\Program Files\IBM\SQLLIB\BIN>db2 get monitor switches

            Monitor Recording Switches

Switch list for db partition number 0
Buffer Pool Activity Information  (BUFFERPOOL) = OFF
Lock Information                        (LOCK) = OFF
Sorting Information                     (SORT) = OFF
SQL Statement Information          (STATEMENT) = OFF
Table Activity Information             (TABLE) = OFF
Take Timestamp Information         (TIMESTAMP) = ON  03/31/2015 15:34:19.915686
Unit of Work Information                 (UOW) = OFF




5) Statement Event ...
  Appl Handle: 125
  Appl Id: *LOCAL.DB2.150331075101
  Appl Seq number: 00004

  Record is the result of a flush: FALSE
  -------------------------------------------
  Type     : Dynamic
  Operation: Execute Immediate
  Section  : 203
  Creator  : NULLID  
  Package  : SQLC2H23
  Consistency Token  : AAAAABBc
  Package Version ID  : 
  Cursor   : 
  Cursor was blocking: FALSE
  Text     : create table tbl_donghua(id integer)
  -------------------------------------------
  Start Time: 03/31/2015 15:54:26.746074
  Stop Time:  03/31/2015 15:54:26.852143
  Elapsed Execution Time:  0.106069 seconds
  Number of Agents created: 1
  User CPU: 0.015625 seconds
  System CPU: 0.000000 seconds
  Statistic fabrication time (milliseconds): 0
  Synchronous runstats time  (milliseconds): 0
  Fetch Count: 0
  Sorts: 0
  Total sort time: 0
  Sort overflows: 0
  Rows read: 10
  Rows written: 7
  Internal rows deleted: 0
  Internal rows updated: 0
  Internal rows inserted: 0
  Bufferpool data logical reads: 0
  Bufferpool data physical reads: 0
  Bufferpool temporary data logical reads: 0
  Bufferpool temporary data physical reads: 0
  Bufferpool index logical reads: 0
  Bufferpool index physical reads: 0
  Bufferpool temporary index logical reads: 0
  Bufferpool temporary index physical reads: 0
  Bufferpool xda logical page reads: 0
  Bufferpool xda physical page reads: 0
  Bufferpool temporary xda logical page reads: 0
  Bufferpool temporary xda physical page reads: 0
  SQLCA:
   sqlcode: 0
   sqlstate: 00000

42) Statement Event ...
  Appl Handle: 125
  Appl Id: *LOCAL.DB2.150331075101
  Appl Seq number: 00005

  Record is the result of a flush: FALSE
  -------------------------------------------
  Type     : Dynamic
  Operation: Execute Immediate
  Section  : 203
  Creator  : NULLID  
  Package  : SQLC2H23
  Consistency Token  : AAAAABBc
  Package Version ID  : 
  Cursor   : 
  Cursor was blocking: FALSE
  Text     : insert into tbl_donghua values (1)
44) Statement Event ...
  Appl Handle: 125
  Appl Id: *LOCAL.DB2.150331075101
  Appl Seq number: 00006

  Record is the result of a flush: FALSE
  -------------------------------------------
  Type     : Dynamic
  Operation: Prepare
  Section  : 201
  Creator  : NULLID  
  Package  : SQLC2H23
  Consistency Token  : AAAAABBc
  Package Version ID  : 
  Cursor   : SQLCUR201
  Cursor was blocking: FALSE
  Text     : select * from tbl_donghua
45) Statement Event ...
  Appl Handle: 125
  Appl Id: *LOCAL.DB2.150331075101
  Appl Seq number: 00006

  Record is the result of a flush: FALSE
  -------------------------------------------
  Type     : Dynamic
  Operation: Open
  Section  : 201
  Creator  : NULLID  
  Package  : SQLC2H23
  Consistency Token  : AAAAABBc
  Package Version ID  : 
  Cursor   : SQLCUR201
  Cursor was blocking: TRUE
  Text     : select * from tbl_donghua

Examples on DB2 cold backup

db2 => CONNECT TO SAMPLEDB

   Database Connection Information

Database server        = DB2/NT64 9.7.10
SQL authorization ID   = ADMINIST...
Local database alias   = SAMPLEDB

db2 => QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS
DB20000I  The QUIESCE DATABASE command completed successfully.
db2 => CONNECT RESET
DB20000I  The SQL command completed successfully.
db2 => BACKUP DATABASE SAMPLEDB TO "D:\backup" WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1 WITHOUT PROMPTING

Backup successful. The timestamp for this backup image is : 20141211074809

db2 => CONNECT TO SAMPLEDB

   Database Connection Information

Database server        = DB2/NT64 9.7.10
SQL authorization ID   = ADMINIST...
Local database alias   = SAMPLEDB

db2 => UNQUIESCE DATABASE
DB20000I  The UNQUIESCE DATABASE command completed successfully.
db2 => CONNECT RESET
DB20000I  The SQL command completed successfully.

db2 => list backup all for database sampledb

                    List History File for sampledb

Number of matching file entries = 1

Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
  B  D  20141211074809001   F    D  S0000000.LOG S0000000.LOG
----------------------------------------------------------------------------
  Contains 3 tablespace(s):

00001 SYSCATSPACE
00002 USERSPACE1
00003 SYSTOOLSPACE
----------------------------------------------------------------------------
    Comment: DB2 BACKUP SAMPLEDB OFFLINE
Start Time: 20141211074809
   End Time: 20141211074811
     Status: A
----------------------------------------------------------------------------
  EID: 2 Location: D:\backup

Sample Restoration Script:

CONNECT TO SAMPLEDB
QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS
CONNECT RESET
RESTORE DATABASE SAMPLEDB FROM "D:\backup" TAKEN AT 20141211074809 WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1 WITHOUT PROMPTING
CONNECT TO SAMPLEDB
UNQUIESCE DATABASE
CONNECT RESET

How to connect to DB2 database remotely

Catalog the node

Syntax: db2 catalog tcpip node <NODENAME> remote <REMOTE> server <PORT>

C:\Program Files\IBM\SQLLIB\BIN>db2 catalog tcpip node server1 remote 192.168.6.141 server 50000
DB20000I  The CATALOG TCPIP NODE command completed successfully.
DB21056W  Directory changes may not be effective until the directory cache is refreshed.

Catalog the database

Syntax:  db2 catalog database <database_name> at node <node_name>

C:\Program Files\IBM\SQLLIB\BIN>db2 catalog database sampledb at node server1
DB20000I  The CATALOG DATABASE command completed successfully.
DB21056W  Directory changes may not be effective until the directory cache is refreshed.

Refresh directory

C:\Program Files\IBM\SQLLIB\BIN>db2 terminate
DB20000I  The TERMINATE command completed successfully.

Connect to database

Syntax: db2 connect to <database_name> user <user_name> (you will then be prompted for a password)

C:\Program Files\IBM\SQLLIB\BIN>db2 connect to sampledb user db2admin using P@ssw0rd

   Database Connection Information

Database server        = DB2/NT64 9.7.6
SQL authorization ID   = DB2ADMIN
Local database alias   = SAMPLEDB

C:\Program Files\IBM\SQLLIB\BIN>db2 list tables

Table/View                      Schema          Type  Creation time

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

  0 record(s) selected.

Verify catalog database

 
C:\Program Files\IBM\SQLLIB\BIN>db2 list db directory

System Database Directory

Number of entries in the directory = 1

Database 1 entry:

Database alias                       = SAMPLEDB
Database name                        = SAMPLEDB
Node name                            = SERVER1
Database release level               = d.00
Comment                              =
Directory entry type                 = Remote
Catalog database partition number    = -1
Alternate server hostname            =
Alternate server port number         =

C:\Program Files\IBM\SQLLIB\BIN>db2 list node directory show detail

Node Directory

Number of entries in the directory = 1

Node 1 entry:

Node name                      = SERVER1
Comment                        =
Directory entry type           = LOCAL
Protocol                       = TCPIP
Hostname                       = 192.168.6.141
Service name                   = 50000
Remote instance name           =
System                         =
Operating system type          = None