Simple db2 commands on linux from installation to basic commands and backup restore.
- check if db2 install pre-requisites are satisfied
./db2prereqcheck
- extract installer
$ tar xvzf db2exc_975_LNX_x86_64.tar.gz
- Install db2
./db2_install
- logoff and login using db2inst1/<password>
- check db2 installation details
[db2inst1@ibmdb2 logs]$ db2level
DB21085I Instance "db2inst1" uses "64" bits and DB2 code release "SQL09075"
with level identifier "08060107".
Informational tokens are "DB2 v9.7.0.5", "special_27924", "IP23292_27924", and
Fix Pack "5".
Product is installed at "/opt/ibm/db2/V9.7".
- check license info
[db2inst1@ibmdb2 ~]$ db2licm -l
Product name: "DB2 Express-C"
License type: "Unwarranted"
Expiry date: "Permanent"
Product identifier: "db2expc"
Version information: "9.7"
Max number of CPUs: "2"
Max amount of memory (GB): "2"
Enforcement policy: "Soft Stop"
- launch DB2 First Steps
/home/db2inst1/sqllib/bin/db2fs
- check db2 environment
[db2inst1@ibmdb2 sqllib]$ set |grep DB2DIR
DB2DIR=/opt/ibm/db2/V9.7
[db2inst1@ibmdb2 sqllib]$ set |grep DB2INSTANCE
DB2INSTANCE=db2inst1
[db2inst1@ibmdb2 sqllib]$ set |grep USER
USER=db2inst1
USERNAME=db2inst1
- check if db2 is running
[db2inst1@ibmdb2 sqllib]$ ps -ef |grep db2sysc |grep -v grep
db2inst1 3113 3111 0 01:23 ? 00:00:00 db2sysc
- get help on commands
[db2inst1@ibmdb2 sqllib]$ db2 ?
db2 [option ...] [db2-command | sql-statement |
[? [phrase | message | sqlstate | class-code]]]
option: -a, -c, -d, -e{c|s}, -finfile, -i, -lhistfile, -o, -m, -n,
-p, -q, -rreport, -s, -t, -td;, -v, -w, -x, -zoutputfile.
db2-command:
ACTIVATE DATABASE GET CONTACTGROUPS RECONCILE
ADD CONTACT GET CONTACTS RECOVER
ADD CONTACTGROUP GET/UPDATE DB CFG REDISTRIBUTE DB PARTITION
ADD DATALINKS MANAGER GET/UPDATE DBM CFG REFRESH LDAP
ADD DBPARTITIONNUM GET DBM MONITOR SWITCHES REGISTER LDAP
ADD XMLSCHEMA GET DESCRIPTION FOR HEALTH REGISTER XMLSCHEMA
ARCHIVE LOG GET NOTIFICATION LIST REGISTER XSROBJECT
ATTACH GET HEALTH SNAPSHOT REORG INDEXES/TABLE
AUTOCONFIGURE GET INSTANCE REORGCHK
BACKUP DATABASE GET MONITOR SWITCHES RESET ADMIN CFG
BIND GET RECOMMENDATIONS RESET ALERT CFG
CATALOG APPC NODE GET ROUTINE RESET DB CFG
CATALOG APPN NODE GET SNAPSHOT RESET DBM CFG
CATALOG DATABASE HELP RESET MONITOR
CATALOG DCS DATABASE HISTORY RESTART DATABASE
CATALOG LDAP DATABASE IMPORT RESTORE DATABASE
CATALOG LDAP NODE INITIALIZE TAPE REWIND TAPE
CATALOG LOCAL NODE INSPECT ROLLFORWARD DATABASE
CATALOG NPIPE NODE LIST ACTIVE DATABASES RUNCMD
CATALOG NETBIOS NODE LIST APPLICATIONS RUNSTATS
CATALOG ODBC DATA SOURCE LIST COMMAND OPTIONS SET CLIENT
CATALOG TCPIP NODE LIST DATABASE DIRECTORY SET RUNTIME DEGREE
CHANGE DATABASE COMMENT LIST DB PARTITION GROUPS SET TABLESPACE CONTAINERS
CHANGE ISOLATION LEVEL LIST DATALINKS MANAGERS SET TAPE POSITION
COMPLETE XMLSCHEMA LIST DBPARTITIONNUMS SET UTIL_IMPACT_PRIORITY
CREATE DATABASE LIST DCS APPLICATIONS SET WRITE
CREATE TOOLS CATALOG LIST DCS DIRECTORY START DATABASE MANAGER
DEACTIVATE DATABASE LIST DRDA INDOUBT START HADR
DECOMPOSE XML DOCUMENT LIST HISTORY STOP DATABASE MANAGER
DECOMPOSE XML DOCUMENTS LIST INDOUBT TRANSACTIONS STOP HADR
DEREGISTER LIST NODE DIRECTORY TAKEOVER HADR
DESCRIBE LIST ODBC DATA SOURCES TERMINATE
DETACH LIST PACKAGES/TABLES UNCATALOG DATABASE
DROP CONTACT LIST TABLESPACE CONTAINERS UNCATALOG DCS DATABASE
DROP CONTACTGROUP LIST TABLESPACES UNCATALOG LDAP DATABASE
DROP DATABASE LIST UTILITIES UNCATALOG LDAP NODE
DROP DATALINKS MANAGER LOAD UNCATALOG NODE
DROP DBPARTITIONNUM LOAD QUERY UNCATALOG ODBC DATA
DROP TOOLS CATALOG MIGRATE DATABASE UNQUIESCE DATABASE
ECHO PING UNQUIESCE INSTANCE
EDIT PREP/PRECOMPILE UPDATE ALERT CFG
EXPORT PRUNE HISTORY/LOGFILE UPDATE COMMAND OPTIONS
FORCE APPLICATION PUT ROUTINE UPDATE CONTACT
GET/UPDATE ADMIN CFG QUERY CLIENT UPDATE CONTACTGROUP
GET ALERT CFG QUIESCE DATABASE UPDATE NOTIFICATION LIST
GET AUTHORIZATIONS QUIESCE INSTANCE UPDATE HISTORY
GET/UPDATE CLI CFG QUIESCE TABLESPACES UPDATE LDAP NODE
GET CONNECTION STATE QUIT UPDATE MONITOR SWITCHES
GET CONTACTGROUP REBIND XQUERY
Note: Some commands are operating system specific and may not be available.
For further help:
? db2-command - help for specified command
? OPTIONS - help for all command options
? HELP - help for reading help screens
The preceding three options can be run as db2 <option> from an OS prompt.
!db2ic - DB2 Information Center (Windows only)
This command can also be run as db2ic from an OS prompt.
- stopping and starting db2 instance
[db2inst1@ibmdb2 ~]$ db2stop
SQL1064N DB2STOP processing was successful.
[db2inst1@ibmdb2 ~]$ db2start
SQL1063N DB2START processing was successful.
- known database instances
[db2inst1@ibmdb2 ~]$ db2ilist
db2inst1
- DB2 Problem determination tool
[db2inst1@ibmdb2 ~]$ db2pd
db2pd> You are running db2pd in interactive mode.
db2pd> If you want command line mode, rerun db2pd with valid options.
db2pd> Type -h or -help for help.
db2pd> Type q to quit.
db2pd> -osinfo
Operating System Information:
OSName: Linux
NodeName: ibmdb2.localdomain
Version: 2
Release: 6
Machine: x86_64
CPU Information:
TotalCPU OnlineCPU ConfigCPU Speed(MHz) HMTDegree Cores/Socket
1 1 1 2399 1 1
Physical Memory and Swap (Megabytes):
TotalMem FreeMem AvailMem TotalSwap FreeSwap
3019 2081 n/a 4000 4000
Virtual Memory (Megabytes):
Total Reserved Available Free
7019 n/a n/a 6081
Message Queue Information:
MsgSeg MsgMax MsgMap MsgMni MsgTql MsgMnb MsgSsz
n/a 65536 65536 3072 65536 65536 16
Shared Memory Information:
ShmMax ShmMin ShmIds ShmSeg
68719476736 1 4096 4096
Semaphore Information:
SemMap SemMni SemMns SemMnu SemMsl SemOpm SemUme SemUsz SemVmx SemAem
256000 1024 256000 256000 250 32 n/a 20 32767 32767
CPU Load Information:
Short Medium Long
0.180000 0.050000 0.010000
CPU Usage Information (percent):
Total Usr Sys Wait Idle
18.000000 n/a n/a n/a 82.000000
db2pd> q
- get Database Manager Configuration
[db2inst1@ibmdb2 ~]$ db2 get dbm cfg
Database Manager Configuration
Node type = Database Server with local and remote clients
Database manager configuration release level = 0x0d00
CPU speed (millisec/instruction) (CPUSPEED) = 1.102137e-07
Max number of concurrently active databases (NUMDB) = 8
Federated Database System Support (FEDERATED) = NO
Transaction processor monitor name (TP_MON_NAME) =
Default charge-back account (DFT_ACCOUNT_STR) =
Java Development Kit installation path (JDK_PATH) = /home/db2inst1/sqllib/java/jdk64
Diagnostic error capture level (DIAGLEVEL) = 3
Notify Level (NOTIFYLEVEL) = 3
Diagnostic data directory path (DIAGPATH) = /home/db2inst1/sqllib/db2dump/
Alternate diagnostic data directory path (ALT_DIAGPATH) =
Size of rotating db2diag & notify logs (MB) (DIAGSIZE) = 0
Default database monitor switches
Buffer pool (DFT_MON_BUFPOOL) = OFF
Lock (DFT_MON_LOCK) = OFF
Sort (DFT_MON_SORT) = OFF
Statement (DFT_MON_STMT) = OFF
Table (DFT_MON_TABLE) = OFF
Timestamp (DFT_MON_TIMESTAMP) = ON
Unit of work (DFT_MON_UOW) = OFF
Monitor health of instance and databases (HEALTH_MON) = ON
SYSADM group name (SYSADM_GROUP) = DB2IADM1
SYSCTRL group name (SYSCTRL_GROUP) =
SYSMAINT group name (SYSMAINT_GROUP) =
SYSMON group name (SYSMON_GROUP) =
Client Userid-Password Plugin (CLNT_PW_PLUGIN) =
Client Kerberos Plugin (CLNT_KRB_PLUGIN) =
Group Plugin (GROUP_PLUGIN) =
GSS Plugin for Local Authorization (LOCAL_GSSPLUGIN) =
Server Plugin Mode (SRV_PLUGIN_MODE) = UNFENCED
Server List of GSS Plugins (SRVCON_GSSPLUGIN_LIST) =
Server Userid-Password Plugin (SRVCON_PW_PLUGIN) =
Server Connection Authentication (SRVCON_AUTH) = NOT_SPECIFIED
Cluster manager (CLUSTER_MGR) =
Database manager authentication (AUTHENTICATION) = SERVER
Alternate authentication (ALTERNATE_AUTH_ENC) = NOT_SPECIFIED
Cataloging allowed without authority (CATALOG_NOAUTH) = NO
Trust all clients (TRUST_ALLCLNTS) = YES
Trusted client authentication (TRUST_CLNTAUTH) = CLIENT
Bypass federated authentication (FED_NOAUTH) = NO
Default database path (DFTDBPATH) = /home/db2inst1
Database monitor heap size (4KB) (MON_HEAP_SZ) = AUTOMATIC(90)
Java Virtual Machine heap size (4KB) (JAVA_HEAP_SZ) = 2048
Audit buffer size (4KB) (AUDIT_BUF_SZ) = 0
Size of instance shared memory (4KB) (INSTANCE_MEMORY) = AUTOMATIC(524288)
Agent stack size (AGENT_STACK_SZ) = 1024
Sort heap threshold (4KB) (SHEAPTHRES) = 0
Directory cache support (DIR_CACHE) = YES
Application support layer heap size (4KB) (ASLHEAPSZ) = 15
Max requester I/O block size (bytes) (RQRIOBLK) = 32767
Workload impact by throttled utilities(UTIL_IMPACT_LIM) = 10
Priority of agents (AGENTPRI) = SYSTEM
Agent pool size (NUM_POOLAGENTS) = AUTOMATIC(100)
Initial number of agents in pool (NUM_INITAGENTS) = 0
Max number of coordinating agents (MAX_COORDAGENTS) = AUTOMATIC(200)
Max number of client connections (MAX_CONNECTIONS) = AUTOMATIC(MAX_COORDAGENTS)
Keep fenced process (KEEPFENCED) = YES
Number of pooled fenced processes (FENCED_POOL) = AUTOMATIC(MAX_COORDAGENTS)
Initial number of fenced processes (NUM_INITFENCED) = 0
Index re-creation time and redo index build (INDEXREC) = RESTART
Transaction manager database name (TM_DATABASE) = 1ST_CONN
Transaction resync interval (sec) (RESYNC_INTERVAL) = 180
SPM name (SPM_NAME) = ibmdb2_
SPM log size (SPM_LOG_FILE_SZ) = 256
SPM resync agent limit (SPM_MAX_RESYNC) = 20
SPM log path (SPM_LOG_PATH) =
TCP/IP Service name (SVCENAME) = db2c_db2inst1
Discovery mode (DISCOVER) = SEARCH
Discover server instance (DISCOVER_INST) = ENABLE
SSL server keydb file (SSL_SVR_KEYDB) =
SSL server stash file (SSL_SVR_STASH) =
SSL server certificate label (SSL_SVR_LABEL) =
SSL service name (SSL_SVCENAME) =
SSL cipher specs (SSL_CIPHERSPECS) =
SSL versions (SSL_VERSIONS) =
SSL client keydb file (SSL_CLNT_KEYDB) =
SSL client stash file (SSL_CLNT_STASH) =
Maximum query degree of parallelism (MAX_QUERYDEGREE) = ANY
Enable intra-partition parallelism (INTRA_PARALLEL) = NO
No. of int. communication buffers(4KB)(FCM_NUM_BUFFERS) = AUTOMATIC(1024)
No. of int. communication channels (FCM_NUM_CHANNELS) = AUTOMATIC(512)
db2start/db2stop timeout (min) (START_STOP_TIME) = 10
- create database
[db2inst1@ibmdb2 ~]$ db2 create database test alias testaj
DB20000I The CREATE DATABASE command completed successfully.
- list databases
[db2inst1@ibmdb2 ~]$ db2 list database directory
System Database Directory
Number of entries in the directory = 2
Database 1 entry:
Database alias = TESTAJ
Database name = TEST
Local database directory = /home/db2inst1
Database release level = d.00
Comment =
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname =
Alternate server port number =
Database 2 entry:
Database alias = SAMPLE
Database name = SAMPLE
Local database directory = /home/db2inst1
Database release level = d.00
Comment =
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname =
Alternate server port number =
- Uncatalog database (specify either database name or alias)
[db2inst1@ibmdb2 ~]$ db2 uncatalog database testaj
DB20000I The UNCATALOG DATABASE command completed successfully.
DB21056W Directory changes may not be effective until the directory cache is
refreshed.
[db2inst1@ibmdb2 ~]$ db2 list database directory
System Database Directory
Number of entries in the directory = 1
Database 1 entry:
Database alias = SAMPLE
Database name = SAMPLE
Local database directory = /home/db2inst1
Database release level = d.00
Comment =
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname =
Alternate server port number =
- Drop database (specify either database name or alias)
[db2inst1@ibmdb2 ~]$ db2 drop database SAMPLE
DB20000I The DROP DATABASE command completed successfully.
[db2inst1@ibmdb2 ~]$ db2 list database directory
SQL1057W The system database directory is empty. SQLSTATE=01606
- Codeset, Territory, and Collation : They affect the type of textual data that can be accommodated
within the database.
The collation setting can take one of the following values: IDENTITY, SYSTEM, COMPATIBILITY,
IDENTITY_16BIT, UCA400_NO, UCA400_LSK, UCA400_LTH, NLSCHAR
[db2inst1@ibmdb2 ~]$ db2 create database sample3 alias testutf using codeset utf-8 territory US collate using system
DB20000I The CREATE DATABASE command completed successfully.
-Tablespaces & Storage Management Type:
Five types of tablespaces in DB2:
• System tablespace: to store the system tables and objects that form system catalog. named SYSCATSPACE by default.
• System temporary tablespace: One tablespace (per page size) to support temporary objects named TEMPSPACE1 by default.
• Regular tablespace: first regular or large tablespace will be named USERSPACE1 by default.
• Large tablespace: supporting LOB-style data.
• User temporary tablespace: used to store user-defined global temporary tables.
Two Storage Management Types:
• System managed storage tablespace (SMS)
• Database managed storage tablespace (DMS)
[db2inst1@ibmdb2 ~]$ db2 create database sample4 automatic storage yes on '/home/db2inst1' catalog tablespace managed by automatic storage
DB20000I The CREATE DATABASE command completed successfully.
[db2inst1@ibmdb2 ~]$ db2 drop database sample4
DB20000I The DROP DATABASE command completed successfully.
- tablespace managed by SYSTEM
db2 => create database SAMPLE5 using codeset utf-8 territory US collate using system PAGESIZE 4096 catalog Tablespace managed by system using ('/home/db2inst1/db2inst1/NODE0000/SAMPLE5/cattbs' ) user tablespace managed by system using ( '/home/db2inst1/db2inst1/NODE0000/SAMPLE5/usertbs' ) temporary tablespace managed by system using ( '/home/db2inst1/db2inst1/NODE0000/SAMPLE5/temptbs' )
DB20000I The CREATE DATABASE command completed successfully.
- tablespace managed by database
db2 => create database SAMPLE6 automatic storage no on '/home/db2inst1'
alias test6 using codeset utf-8 territory us collate using system pagesize 4096
catalog tablespace managed by database using
(FILE '/home/db2inst1/db2inst1/NODE0000/SAMPLE6/cattbs.001')
user tablespace managed by database using
(FILE '/home/db2inst1/db2inst1/NODE0000/SAMPLE6/usertbs.001' 10240,
FILE '/home/db2inst1/db2inst1/NODE0000/SAMPLE6/usertbs.002' 10240,
FILE '/home/db2inst1/db2inst1/NODE0000/SAMPLE6/usertbs.003' 10240)
temporary tablespace managed by database using
(FILE '/home/db2inst1/db2inst1/NODE0000/SAMPLE6/temptbs.001' 10240)
DB20000I The CREATE DATABASE command completed successfully.
- create bufferpool (DB2’s mechanism for caching data from the database in memory)
db2 =>create bufferpool testbp size 10000 pagesize 4K
DB20000I The SQL command completed successfully.
- providing tablespace storage options to improve performance
db2 => create database SAMPLE7 automatic storage no on '/home/db2inst1' alias testdb7 using codeset utf-8 territory us collate using system pagesize 4096 catalog tablespace managed by system using ('/home/db2inst1/db2inst1/NODE0000/SAMPLE7/syscat') extentsize 16 prefetchsize 16 overhead 10 transferrate 0.2 user tablespace managed by database using (FILE ' home/db2inst1/db2inst1/NODE0000/SAMPLE7/usertbs/usertbs.001' 102400, FILE ' /home/db2inst1/db2inst1/NODE0000/SAMPLE7/usertbs/usertbs.002' 102400, FILE ' /home/db2inst1/db2inst1/NODE0000/SAMPLE7/usertbs/usertbs.003' 102400) extentsize 16 prefetchsize 64 overhead 10 transferrate 0.2 temporary tablespace managed by database using (FILE ' /home/db2inst1/db2inst1/NODE0000/SAMPLE7/temptbs/temp.001' 204800) extentsize 16 prefetchsize 16 overhead 4 transferrate 0.05
DB20000I The CREATE DATABASE command completed successfully.
db2 => connect to sample7
Database Connection Information
Database server = DB2/LINUXX8664 9.7.5
SQL authorization ID = DB2INST1
Local database alias = SAMPLE7
- listing tablespace for connected database
db2 => list tablespaces
Tablespaces for Current Database
Tablespace ID = 0
Name = SYSCATSPACE
Type = System managed space
Contents = All permanent data. Regular table space.
State = 0x0000
Detailed explanation:
Normal
Tablespace ID = 1
Name = TEMPSPACE1
Type = Database managed space
Contents = System Temporary data
State = 0x0000
Detailed explanation:
Normal
Tablespace ID = 2
Name = USERSPACE1
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
- list containers for a tablespace
db2 => list tablespace containers for 1
Tablespace Containers for Tablespace 1
Container ID = 0
Name = /home/db2inst1/db2inst1/NODE0000/SQL00001/ /home/db2inst1/db2inst1/NODE0000/SAMPLE7/temptbs/temp.001
Type = File
db2 => disconnect sample7
DB20000I The SQL DISCONNECT command completed successfully.
- putting database in archive log
db2 => archive log for database sample7
SQL2417N Archive log is not allowed on non-recoverable databases.
db2 => connect to sample7
Database Connection Information
Database server = DB2/LINUXX8664 9.7.5
SQL authorization ID = DB2INST1
Local database alias = SAMPLE7
- setting logretain parameter to allow db to put in archive log
db2 => update db cfg using LOGARCHMETH1 LOGRETAIN
DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.
SQL1363W One or more of the parameters submitted for immediate modification
were not changed dynamically. For these configuration parameters, all
applications must disconnect from this database before the changes become
effective.
db2 => disconnect sample7
DB20000I The SQL DISCONNECT command completed successfully.
db2 => archive log for database sample7
SQL1116N A connection to or activation of database "SAMPLE7" cannot be made
because of BACKUP PENDING. SQLSTATE=57019
- checking for error
db2 => ? SQL1116N
SQL1116N A connection to or activation of database "<name>" cannot be
made because of BACKUP PENDING.
Explanation:
The specified database requires a backup to be taken to provide a
starting point for roll-forward recovery.
No connection was made.
User response:
Back up the database by calling the BACKUP routine or, if roll-recovery
is not required, set the database configuration parameters LOGARCHMETH1
and LOGARCHMETH2 to OFF.
sqlcode: -1116
sqlstate: 57019
db2 => drop database sample7
SQL1035N The database is currently in use. SQLSTATE=57019
db2 => disconnect sample7
DB20000I The SQL DISCONNECT command completed successfully.
- take backup of database
db2 => backup database sample7 to /home/db2inst1/db2backups
Backup successful. The timestamp for this backup image is : 20161002005808
db2 => archive log for database sample7
DB20000I The ARCHIVE LOG command completed successfully.
- list backup files
[db2inst1@ibmdb2 db2backups]$ pwd
/home/db2inst1/db2backups
[db2inst1@ibmdb2 db2backups]$ ls
SAMPLE7.0.db2inst1.NODE0000.CATN0000.20161002005808.001
- backup format:
• DBALIAS: Your database name or cataloged alias.
• TYPEID: The type of backup: 0 for full and 3 for tablespace.
• INSTANCE: The instance name from which the backup was taken.
• NODE: The database node for a partitioned database. In this single-partition world, it is
always NODE0000.
• CATALOGNODE: The catalog node number for a partitioned database. Again, because
you’re dealing only with single-partitioned DB2, it always reads CATN0000.
• SEQUENCE: The sequence number for backups taken at the current timestamp.
mkdir -p /home/db2inst1/db2backups/sample7/bkp1 /home/db2inst1/db2backups/sample7/bkp2 /home/db2inst1/db2backups/sample7/bkp3 /home/db2inst1/db2backups/sample7/bkp4
- take backup to different mount points or directories with parallelism
[db2inst1@ibmdb2 db2backups]$ db2 backup database sample7 to /home/db2inst1/db2backups/sample7/bkp1, /home/db2inst1/db2backups/sample7/bkp2, /home/db2inst1/db2backups/sample7/bkp3, /home/db2inst1/db2backups/sample7/bkp4 with 16 buffers buffer 4096 parallelism 4
Backup successful. The timestamp for this backup image is : 20161002011148
- list files
[db2inst1@ibmdb2 db2backups]$ ls ./sample7/bkp1 ./sample7/bkp2 ./sample7/bkp3 ./sample7/bkp4
./sample7/bkp1:
SAMPLE7.0.db2inst1.NODE0000.CATN0000.20161002011148.001
./sample7/bkp2:
SAMPLE7.0.db2inst1.NODE0000.CATN0000.20161002011148.002
./sample7/bkp3:
SAMPLE7.0.db2inst1.NODE0000.CATN0000.20161002011148.003
./sample7/bkp4:
SAMPLE7.0.db2inst1.NODE0000.CATN0000.20161002011148.004
- backup syntax
Backup database database_name [user user_name [using password]]
Tablespace (tablespace_name [, tablespace_name ...] )
[online] [incremental [delta]]
[use advanced_storage_options]
to backup_location [, backup_location ...]
[with n buffers] [buffer size] [parallelism n]
[without prompting]
- tablespace backup
[db2inst1@ibmdb2 tbsbkp]$ db2 backup database sample7 tablespace userspace1 to /home/db2inst1/db2backups/sample7/tbsbkp
Backup successful. The timestamp for this backup image is : 20161002011959
- Online backup
db2 => backup database sample7 online to /home/db2inst1/db2backups/sample7/bkp1, /home/db2inst1/db2backups/sample7/bkp2 include logs
Backup successful. The timestamp for this backup image is : 20161002013954
- Incremental and delta backup
backup database sample7 online incremental delta to /home/db2inst1/db2backups/sample7/bkp1, /home/db2inst1/db2backups/sample7/bkp2
SQL2426N The database has not been configured to allow the incremental backup
operation. Reason code = "1".
- set trackmod ON to take incremental backup
db2 => update db cfg for sample7 using trackmod on
DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.
- take full backup first after trackmod ON for incremental backup
db2 => backup database sample7 to /home/db2inst1/db2backups/sample7
Backup successful. The timestamp for this backup image is : 20161002014701
- now you can take incremental backup
db2 => backup database sample7 online incremental delta to /home/db2inst1/db2backups/sample7/bkp1, /home/db2inst1/db2backups/sample7/bkp2
Backup successful. The timestamp for this backup image is : 20161002014714
- restore database
[db2inst1@ibmdb2 tbsbkp]$ db2 restore database sample7 from /home/db2inst1/db2backups/sample7 taken at 20161002014701
SQL2539W Warning! Restoring to an existing database that is the same as the
backup image database. The database files will be deleted.
Do you want to continue ? (y/n) y
DB20000I The RESTORE DATABASE command completed successfully.
[db2inst1@ibmdb2 tbsbkp]$ db2 connect to sample7
SQL1117N A connection to or activation of database "SAMPLE7" cannot be made
because of ROLL-FORWARD PENDING. SQLSTATE=57019
[db2inst1@ibmdb2 tbsbkp]$ db2 rollforward database sample7 complete
Rollforward Status
Input database alias = sample7
Number of nodes have returned status = 1
Node number = 0
Rollforward status = not pending
Next log file to be read =
Log files processed = -
Last committed transaction = 2016-10-02-05.47.03.000000 UTC
DB20000I The ROLLFORWARD command completed successfully.
- other options for rollforward
db2 rollforward database sample7 to end of logs and complete
db2 rollforward database sample7 to 20161002014714
[db2inst1@ibmdb2 tbsbkp]$ db2 connect to sample7
Database Connection Information
Database server = DB2/LINUXX8664 9.7.5
SQL authorization ID = DB2INST1
Local database alias = SAMPLE7
- restore backup of TESTDB to another database RESTDB
db2 => create database TESTDB automatic storage no on '/home/db2inst1' using codeset utf-8 territory US collate using system pagesize 4096 catalog tablespace managed by system using ('/home/db2inst1/db2inst1/NODE0000/TESTDB') extentsize 16 prefetchsize 16 overhead 10 transferrate 0.2 user tablespace managed by database using (FILE ' home/db2inst1/db2inst1/NODE0000/TESTDB/usertbs.001' 102400, FILE ' /home/db2inst1/db2inst1/NODE0000/TESTDB/usertbs.002' 102400, FILE ' /home/db2inst1/db2inst1/NODE0000/TESTDB/usertbs.003' 102400) extentsize 16 prefetchsize 64 overhead 10 transferrate 0.2 temporary tablespace managed by database using (FILE ' /home/db2inst1/db2inst1/NODE0000/TESTDB/temp.001' 204800) extentsize 16 prefetchsize 16 overhead 4 transferrate 0.05
[db2inst1@ibmdb2 TESTDB]$ db2 update db cfg for TESTDB using NEWLOGPATH /home/db2inst1/db2inst1/NODE0000/TESTDB/logs
DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.
[db2inst1@ibmdb2 TESTDB]$ db2 update db cfg for TESTDB using trackmod on
DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.
[db2inst1@ibmdb2 TESTDB]$ db2 connect to testdb
Database Connection Information
Database server = DB2/LINUXX8664 9.7.5
SQL authorization ID = DB2INST1
Local database alias = TESTDB
[db2inst1@ibmdb2 TESTDB]$ db2 update db cfg using LOGARCHMETH1 LOGRETAIN
DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.
SQL1363W One or more of the parameters submitted for immediate modification
were not changed dynamically. For these configuration parameters, all
applications must disconnect from this database before the changes become
effective.
archive log for database TESTDB
[db2inst1@ibmdb2 TESTDB]$ db2 backup database TESTDB online to /home/db2inst1/db2backups/TESTDB include logs
Backup successful. The timestamp for this backup image is : 20161002111535
/home/db2inst1/db2inst1/NODE0000/RESTDB/logs
[db2inst1@ibmdb2 RESTDB]$ db2 restore database TESTDB from /home/db2inst1/db2backups/TESTDB taken at 20161002111535 to /home/db2inst1/db2inst1/NODE0000/RESTDB into RESTDB newlogpath /home/db2inst1/db2inst1/NODE0000/RESTDB/logs redirect without prompting
SQL1277W A redirected restore operation is being performed. Table space
configuration can now be viewed and table spaces that do not use automatic
storage can have their containers reconfigured.
DB20000I The RESTORE DATABASE command completed successfully.
[db2inst1@ibmdb2 RESTDB]$ db2 "set tablespace containers for 0 using (PATH '/home/db2inst1/db2inst1/NODE0000/RESTDB/syscat')"
DB20000I The SET TABLESPACE CONTAINERS command completed successfully.
[db2inst1@ibmdb2 NODE0000]$ db2 "set tablespace containers for 1 using (FILE '/home/db2inst1/db2inst1/NODE0000/RESTDB/usertbs.001' 204800)"
DB20000I The SET TABLESPACE CONTAINERS command completed successfully.
[db2inst1@ibmdb2 NODE0000]$ db2 "set tablespace containers for 2 using (FILE '/home/db2inst1/db2inst1/NODE0000/RESTDB/temp001' 204800)"
DB20000I The SET TABLESPACE CONTAINERS command completed successfully.
[db2inst1@ibmdb2 RESTDB]$ db2 restore db TESTDB continue
DB20000I The RESTORE DATABASE command completed successfully.
[db2inst1@ibmdb2 logs]$ db2 rollforward database RESTDB to end of logs
Rollforward Status
Input database alias = RESTDB
Number of nodes have returned status = 1
Node number = 0
Rollforward status = DB working
Next log file to be read = S0000000.LOG
Log files processed = S0000000.LOG - S0000000.LOG
Last committed transaction = 2016-10-02-15.15.37.000000 UTC
DB20000I The ROLLFORWARD command completed successfully.
[db2inst1@ibmdb2 logs]$ db2 connect to restdb
SQL1117N A connection to or activation of database "RESTDB" cannot be made
because of ROLL-FORWARD PENDING. SQLSTATE=57019
[db2inst1@ibmdb2 NODE0000]$ cd TESTDB/logs/
[db2inst1@ibmdb2 logs]$ ls
S0000000.LOG S0000003.LOG S0000006.LOG S0000009.LOG S0000012.LOG
S0000001.LOG S0000004.LOG S0000007.LOG S0000010.LOG S0000013.LOG
S0000002.LOG S0000005.LOG S0000008.LOG S0000011.LOG SQLLPATH.TAG
[db2inst1@ibmdb2 logs]$ cp S0*.LOG ../../RESTDB/logs/
[db2inst1@ibmdb2 logs]$ db2 rollforward database RESTDB to end of logs
Rollforward Status
Input database alias = RESTDB
Number of nodes have returned status = 1
Node number = 0
Rollforward status = DB working
Next log file to be read = S0000001.LOG
Log files processed = S0000000.LOG - S0000000.LOG
Last committed transaction = 2016-10-02-15.15.37.000000 UTC
DB20000I The ROLLFORWARD command completed successfully.
[db2inst1@ibmdb2 logs]$ db2 rollforward database RESTDB to end of logs and complete
Rollforward Status
Input database alias = RESTDB
Number of nodes have returned status = 1
Node number = 0
Rollforward status = not pending
Next log file to be read =
Log files processed = S0000000.LOG - S0000000.LOG
Last committed transaction = 2016-10-02-15.15.37.000000 UTC
DB20000I The ROLLFORWARD command completed successfully.
[db2inst1@ibmdb2 logs]$ db2 connect to restdb
Database Connection Information
Database server = DB2/LINUXX8664 9.7.5
SQL authorization ID = DB2INST1
- check if db2 install pre-requisites are satisfied
./db2prereqcheck
- extract installer
$ tar xvzf db2exc_975_LNX_x86_64.tar.gz
- Install db2
./db2_install
- logoff and login using db2inst1/<password>
- check db2 installation details
[db2inst1@ibmdb2 logs]$ db2level
DB21085I Instance "db2inst1" uses "64" bits and DB2 code release "SQL09075"
with level identifier "08060107".
Informational tokens are "DB2 v9.7.0.5", "special_27924", "IP23292_27924", and
Fix Pack "5".
Product is installed at "/opt/ibm/db2/V9.7".
- check license info
[db2inst1@ibmdb2 ~]$ db2licm -l
Product name: "DB2 Express-C"
License type: "Unwarranted"
Expiry date: "Permanent"
Product identifier: "db2expc"
Version information: "9.7"
Max number of CPUs: "2"
Max amount of memory (GB): "2"
Enforcement policy: "Soft Stop"
- launch DB2 First Steps
/home/db2inst1/sqllib/bin/db2fs
- check db2 environment
[db2inst1@ibmdb2 sqllib]$ set |grep DB2DIR
DB2DIR=/opt/ibm/db2/V9.7
[db2inst1@ibmdb2 sqllib]$ set |grep DB2INSTANCE
DB2INSTANCE=db2inst1
[db2inst1@ibmdb2 sqllib]$ set |grep USER
USER=db2inst1
USERNAME=db2inst1
- check if db2 is running
[db2inst1@ibmdb2 sqllib]$ ps -ef |grep db2sysc |grep -v grep
db2inst1 3113 3111 0 01:23 ? 00:00:00 db2sysc
- get help on commands
[db2inst1@ibmdb2 sqllib]$ db2 ?
db2 [option ...] [db2-command | sql-statement |
[? [phrase | message | sqlstate | class-code]]]
option: -a, -c, -d, -e{c|s}, -finfile, -i, -lhistfile, -o, -m, -n,
-p, -q, -rreport, -s, -t, -td;, -v, -w, -x, -zoutputfile.
db2-command:
ACTIVATE DATABASE GET CONTACTGROUPS RECONCILE
ADD CONTACT GET CONTACTS RECOVER
ADD CONTACTGROUP GET/UPDATE DB CFG REDISTRIBUTE DB PARTITION
ADD DATALINKS MANAGER GET/UPDATE DBM CFG REFRESH LDAP
ADD DBPARTITIONNUM GET DBM MONITOR SWITCHES REGISTER LDAP
ADD XMLSCHEMA GET DESCRIPTION FOR HEALTH REGISTER XMLSCHEMA
ARCHIVE LOG GET NOTIFICATION LIST REGISTER XSROBJECT
ATTACH GET HEALTH SNAPSHOT REORG INDEXES/TABLE
AUTOCONFIGURE GET INSTANCE REORGCHK
BACKUP DATABASE GET MONITOR SWITCHES RESET ADMIN CFG
BIND GET RECOMMENDATIONS RESET ALERT CFG
CATALOG APPC NODE GET ROUTINE RESET DB CFG
CATALOG APPN NODE GET SNAPSHOT RESET DBM CFG
CATALOG DATABASE HELP RESET MONITOR
CATALOG DCS DATABASE HISTORY RESTART DATABASE
CATALOG LDAP DATABASE IMPORT RESTORE DATABASE
CATALOG LDAP NODE INITIALIZE TAPE REWIND TAPE
CATALOG LOCAL NODE INSPECT ROLLFORWARD DATABASE
CATALOG NPIPE NODE LIST ACTIVE DATABASES RUNCMD
CATALOG NETBIOS NODE LIST APPLICATIONS RUNSTATS
CATALOG ODBC DATA SOURCE LIST COMMAND OPTIONS SET CLIENT
CATALOG TCPIP NODE LIST DATABASE DIRECTORY SET RUNTIME DEGREE
CHANGE DATABASE COMMENT LIST DB PARTITION GROUPS SET TABLESPACE CONTAINERS
CHANGE ISOLATION LEVEL LIST DATALINKS MANAGERS SET TAPE POSITION
COMPLETE XMLSCHEMA LIST DBPARTITIONNUMS SET UTIL_IMPACT_PRIORITY
CREATE DATABASE LIST DCS APPLICATIONS SET WRITE
CREATE TOOLS CATALOG LIST DCS DIRECTORY START DATABASE MANAGER
DEACTIVATE DATABASE LIST DRDA INDOUBT START HADR
DECOMPOSE XML DOCUMENT LIST HISTORY STOP DATABASE MANAGER
DECOMPOSE XML DOCUMENTS LIST INDOUBT TRANSACTIONS STOP HADR
DEREGISTER LIST NODE DIRECTORY TAKEOVER HADR
DESCRIBE LIST ODBC DATA SOURCES TERMINATE
DETACH LIST PACKAGES/TABLES UNCATALOG DATABASE
DROP CONTACT LIST TABLESPACE CONTAINERS UNCATALOG DCS DATABASE
DROP CONTACTGROUP LIST TABLESPACES UNCATALOG LDAP DATABASE
DROP DATABASE LIST UTILITIES UNCATALOG LDAP NODE
DROP DATALINKS MANAGER LOAD UNCATALOG NODE
DROP DBPARTITIONNUM LOAD QUERY UNCATALOG ODBC DATA
DROP TOOLS CATALOG MIGRATE DATABASE UNQUIESCE DATABASE
ECHO PING UNQUIESCE INSTANCE
EDIT PREP/PRECOMPILE UPDATE ALERT CFG
EXPORT PRUNE HISTORY/LOGFILE UPDATE COMMAND OPTIONS
FORCE APPLICATION PUT ROUTINE UPDATE CONTACT
GET/UPDATE ADMIN CFG QUERY CLIENT UPDATE CONTACTGROUP
GET ALERT CFG QUIESCE DATABASE UPDATE NOTIFICATION LIST
GET AUTHORIZATIONS QUIESCE INSTANCE UPDATE HISTORY
GET/UPDATE CLI CFG QUIESCE TABLESPACES UPDATE LDAP NODE
GET CONNECTION STATE QUIT UPDATE MONITOR SWITCHES
GET CONTACTGROUP REBIND XQUERY
Note: Some commands are operating system specific and may not be available.
For further help:
? db2-command - help for specified command
? OPTIONS - help for all command options
? HELP - help for reading help screens
The preceding three options can be run as db2 <option> from an OS prompt.
!db2ic - DB2 Information Center (Windows only)
This command can also be run as db2ic from an OS prompt.
- stopping and starting db2 instance
[db2inst1@ibmdb2 ~]$ db2stop
SQL1064N DB2STOP processing was successful.
[db2inst1@ibmdb2 ~]$ db2start
SQL1063N DB2START processing was successful.
- known database instances
[db2inst1@ibmdb2 ~]$ db2ilist
db2inst1
- DB2 Problem determination tool
[db2inst1@ibmdb2 ~]$ db2pd
db2pd> You are running db2pd in interactive mode.
db2pd> If you want command line mode, rerun db2pd with valid options.
db2pd> Type -h or -help for help.
db2pd> Type q to quit.
db2pd> -osinfo
Operating System Information:
OSName: Linux
NodeName: ibmdb2.localdomain
Version: 2
Release: 6
Machine: x86_64
CPU Information:
TotalCPU OnlineCPU ConfigCPU Speed(MHz) HMTDegree Cores/Socket
1 1 1 2399 1 1
Physical Memory and Swap (Megabytes):
TotalMem FreeMem AvailMem TotalSwap FreeSwap
3019 2081 n/a 4000 4000
Virtual Memory (Megabytes):
Total Reserved Available Free
7019 n/a n/a 6081
Message Queue Information:
MsgSeg MsgMax MsgMap MsgMni MsgTql MsgMnb MsgSsz
n/a 65536 65536 3072 65536 65536 16
Shared Memory Information:
ShmMax ShmMin ShmIds ShmSeg
68719476736 1 4096 4096
Semaphore Information:
SemMap SemMni SemMns SemMnu SemMsl SemOpm SemUme SemUsz SemVmx SemAem
256000 1024 256000 256000 250 32 n/a 20 32767 32767
CPU Load Information:
Short Medium Long
0.180000 0.050000 0.010000
CPU Usage Information (percent):
Total Usr Sys Wait Idle
18.000000 n/a n/a n/a 82.000000
db2pd> q
- get Database Manager Configuration
[db2inst1@ibmdb2 ~]$ db2 get dbm cfg
Database Manager Configuration
Node type = Database Server with local and remote clients
Database manager configuration release level = 0x0d00
CPU speed (millisec/instruction) (CPUSPEED) = 1.102137e-07
Max number of concurrently active databases (NUMDB) = 8
Federated Database System Support (FEDERATED) = NO
Transaction processor monitor name (TP_MON_NAME) =
Default charge-back account (DFT_ACCOUNT_STR) =
Java Development Kit installation path (JDK_PATH) = /home/db2inst1/sqllib/java/jdk64
Diagnostic error capture level (DIAGLEVEL) = 3
Notify Level (NOTIFYLEVEL) = 3
Diagnostic data directory path (DIAGPATH) = /home/db2inst1/sqllib/db2dump/
Alternate diagnostic data directory path (ALT_DIAGPATH) =
Size of rotating db2diag & notify logs (MB) (DIAGSIZE) = 0
Default database monitor switches
Buffer pool (DFT_MON_BUFPOOL) = OFF
Lock (DFT_MON_LOCK) = OFF
Sort (DFT_MON_SORT) = OFF
Statement (DFT_MON_STMT) = OFF
Table (DFT_MON_TABLE) = OFF
Timestamp (DFT_MON_TIMESTAMP) = ON
Unit of work (DFT_MON_UOW) = OFF
Monitor health of instance and databases (HEALTH_MON) = ON
SYSADM group name (SYSADM_GROUP) = DB2IADM1
SYSCTRL group name (SYSCTRL_GROUP) =
SYSMAINT group name (SYSMAINT_GROUP) =
SYSMON group name (SYSMON_GROUP) =
Client Userid-Password Plugin (CLNT_PW_PLUGIN) =
Client Kerberos Plugin (CLNT_KRB_PLUGIN) =
Group Plugin (GROUP_PLUGIN) =
GSS Plugin for Local Authorization (LOCAL_GSSPLUGIN) =
Server Plugin Mode (SRV_PLUGIN_MODE) = UNFENCED
Server List of GSS Plugins (SRVCON_GSSPLUGIN_LIST) =
Server Userid-Password Plugin (SRVCON_PW_PLUGIN) =
Server Connection Authentication (SRVCON_AUTH) = NOT_SPECIFIED
Cluster manager (CLUSTER_MGR) =
Database manager authentication (AUTHENTICATION) = SERVER
Alternate authentication (ALTERNATE_AUTH_ENC) = NOT_SPECIFIED
Cataloging allowed without authority (CATALOG_NOAUTH) = NO
Trust all clients (TRUST_ALLCLNTS) = YES
Trusted client authentication (TRUST_CLNTAUTH) = CLIENT
Bypass federated authentication (FED_NOAUTH) = NO
Default database path (DFTDBPATH) = /home/db2inst1
Database monitor heap size (4KB) (MON_HEAP_SZ) = AUTOMATIC(90)
Java Virtual Machine heap size (4KB) (JAVA_HEAP_SZ) = 2048
Audit buffer size (4KB) (AUDIT_BUF_SZ) = 0
Size of instance shared memory (4KB) (INSTANCE_MEMORY) = AUTOMATIC(524288)
Agent stack size (AGENT_STACK_SZ) = 1024
Sort heap threshold (4KB) (SHEAPTHRES) = 0
Directory cache support (DIR_CACHE) = YES
Application support layer heap size (4KB) (ASLHEAPSZ) = 15
Max requester I/O block size (bytes) (RQRIOBLK) = 32767
Workload impact by throttled utilities(UTIL_IMPACT_LIM) = 10
Priority of agents (AGENTPRI) = SYSTEM
Agent pool size (NUM_POOLAGENTS) = AUTOMATIC(100)
Initial number of agents in pool (NUM_INITAGENTS) = 0
Max number of coordinating agents (MAX_COORDAGENTS) = AUTOMATIC(200)
Max number of client connections (MAX_CONNECTIONS) = AUTOMATIC(MAX_COORDAGENTS)
Keep fenced process (KEEPFENCED) = YES
Number of pooled fenced processes (FENCED_POOL) = AUTOMATIC(MAX_COORDAGENTS)
Initial number of fenced processes (NUM_INITFENCED) = 0
Index re-creation time and redo index build (INDEXREC) = RESTART
Transaction manager database name (TM_DATABASE) = 1ST_CONN
Transaction resync interval (sec) (RESYNC_INTERVAL) = 180
SPM name (SPM_NAME) = ibmdb2_
SPM log size (SPM_LOG_FILE_SZ) = 256
SPM resync agent limit (SPM_MAX_RESYNC) = 20
SPM log path (SPM_LOG_PATH) =
TCP/IP Service name (SVCENAME) = db2c_db2inst1
Discovery mode (DISCOVER) = SEARCH
Discover server instance (DISCOVER_INST) = ENABLE
SSL server keydb file (SSL_SVR_KEYDB) =
SSL server stash file (SSL_SVR_STASH) =
SSL server certificate label (SSL_SVR_LABEL) =
SSL service name (SSL_SVCENAME) =
SSL cipher specs (SSL_CIPHERSPECS) =
SSL versions (SSL_VERSIONS) =
SSL client keydb file (SSL_CLNT_KEYDB) =
SSL client stash file (SSL_CLNT_STASH) =
Maximum query degree of parallelism (MAX_QUERYDEGREE) = ANY
Enable intra-partition parallelism (INTRA_PARALLEL) = NO
No. of int. communication buffers(4KB)(FCM_NUM_BUFFERS) = AUTOMATIC(1024)
No. of int. communication channels (FCM_NUM_CHANNELS) = AUTOMATIC(512)
db2start/db2stop timeout (min) (START_STOP_TIME) = 10
- create database
[db2inst1@ibmdb2 ~]$ db2 create database test alias testaj
DB20000I The CREATE DATABASE command completed successfully.
- list databases
[db2inst1@ibmdb2 ~]$ db2 list database directory
System Database Directory
Number of entries in the directory = 2
Database 1 entry:
Database alias = TESTAJ
Database name = TEST
Local database directory = /home/db2inst1
Database release level = d.00
Comment =
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname =
Alternate server port number =
Database 2 entry:
Database alias = SAMPLE
Database name = SAMPLE
Local database directory = /home/db2inst1
Database release level = d.00
Comment =
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname =
Alternate server port number =
- Uncatalog database (specify either database name or alias)
[db2inst1@ibmdb2 ~]$ db2 uncatalog database testaj
DB20000I The UNCATALOG DATABASE command completed successfully.
DB21056W Directory changes may not be effective until the directory cache is
refreshed.
[db2inst1@ibmdb2 ~]$ db2 list database directory
System Database Directory
Number of entries in the directory = 1
Database 1 entry:
Database alias = SAMPLE
Database name = SAMPLE
Local database directory = /home/db2inst1
Database release level = d.00
Comment =
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname =
Alternate server port number =
- Drop database (specify either database name or alias)
[db2inst1@ibmdb2 ~]$ db2 drop database SAMPLE
DB20000I The DROP DATABASE command completed successfully.
[db2inst1@ibmdb2 ~]$ db2 list database directory
SQL1057W The system database directory is empty. SQLSTATE=01606
- Codeset, Territory, and Collation : They affect the type of textual data that can be accommodated
within the database.
The collation setting can take one of the following values: IDENTITY, SYSTEM, COMPATIBILITY,
IDENTITY_16BIT, UCA400_NO, UCA400_LSK, UCA400_LTH, NLSCHAR
[db2inst1@ibmdb2 ~]$ db2 create database sample3 alias testutf using codeset utf-8 territory US collate using system
DB20000I The CREATE DATABASE command completed successfully.
-Tablespaces & Storage Management Type:
Five types of tablespaces in DB2:
• System tablespace: to store the system tables and objects that form system catalog. named SYSCATSPACE by default.
• System temporary tablespace: One tablespace (per page size) to support temporary objects named TEMPSPACE1 by default.
• Regular tablespace: first regular or large tablespace will be named USERSPACE1 by default.
• Large tablespace: supporting LOB-style data.
• User temporary tablespace: used to store user-defined global temporary tables.
Two Storage Management Types:
• System managed storage tablespace (SMS)
• Database managed storage tablespace (DMS)
[db2inst1@ibmdb2 ~]$ db2 create database sample4 automatic storage yes on '/home/db2inst1' catalog tablespace managed by automatic storage
DB20000I The CREATE DATABASE command completed successfully.
[db2inst1@ibmdb2 ~]$ db2 drop database sample4
DB20000I The DROP DATABASE command completed successfully.
- tablespace managed by SYSTEM
db2 => create database SAMPLE5 using codeset utf-8 territory US collate using system PAGESIZE 4096 catalog Tablespace managed by system using ('/home/db2inst1/db2inst1/NODE0000/SAMPLE5/cattbs' ) user tablespace managed by system using ( '/home/db2inst1/db2inst1/NODE0000/SAMPLE5/usertbs' ) temporary tablespace managed by system using ( '/home/db2inst1/db2inst1/NODE0000/SAMPLE5/temptbs' )
DB20000I The CREATE DATABASE command completed successfully.
- tablespace managed by database
db2 => create database SAMPLE6 automatic storage no on '/home/db2inst1'
alias test6 using codeset utf-8 territory us collate using system pagesize 4096
catalog tablespace managed by database using
(FILE '/home/db2inst1/db2inst1/NODE0000/SAMPLE6/cattbs.001')
user tablespace managed by database using
(FILE '/home/db2inst1/db2inst1/NODE0000/SAMPLE6/usertbs.001' 10240,
FILE '/home/db2inst1/db2inst1/NODE0000/SAMPLE6/usertbs.002' 10240,
FILE '/home/db2inst1/db2inst1/NODE0000/SAMPLE6/usertbs.003' 10240)
temporary tablespace managed by database using
(FILE '/home/db2inst1/db2inst1/NODE0000/SAMPLE6/temptbs.001' 10240)
DB20000I The CREATE DATABASE command completed successfully.
- create bufferpool (DB2’s mechanism for caching data from the database in memory)
db2 =>create bufferpool testbp size 10000 pagesize 4K
DB20000I The SQL command completed successfully.
- providing tablespace storage options to improve performance
db2 => create database SAMPLE7 automatic storage no on '/home/db2inst1' alias testdb7 using codeset utf-8 territory us collate using system pagesize 4096 catalog tablespace managed by system using ('/home/db2inst1/db2inst1/NODE0000/SAMPLE7/syscat') extentsize 16 prefetchsize 16 overhead 10 transferrate 0.2 user tablespace managed by database using (FILE ' home/db2inst1/db2inst1/NODE0000/SAMPLE7/usertbs/usertbs.001' 102400, FILE ' /home/db2inst1/db2inst1/NODE0000/SAMPLE7/usertbs/usertbs.002' 102400, FILE ' /home/db2inst1/db2inst1/NODE0000/SAMPLE7/usertbs/usertbs.003' 102400) extentsize 16 prefetchsize 64 overhead 10 transferrate 0.2 temporary tablespace managed by database using (FILE ' /home/db2inst1/db2inst1/NODE0000/SAMPLE7/temptbs/temp.001' 204800) extentsize 16 prefetchsize 16 overhead 4 transferrate 0.05
DB20000I The CREATE DATABASE command completed successfully.
db2 => connect to sample7
Database Connection Information
Database server = DB2/LINUXX8664 9.7.5
SQL authorization ID = DB2INST1
Local database alias = SAMPLE7
- listing tablespace for connected database
db2 => list tablespaces
Tablespaces for Current Database
Tablespace ID = 0
Name = SYSCATSPACE
Type = System managed space
Contents = All permanent data. Regular table space.
State = 0x0000
Detailed explanation:
Normal
Tablespace ID = 1
Name = TEMPSPACE1
Type = Database managed space
Contents = System Temporary data
State = 0x0000
Detailed explanation:
Normal
Tablespace ID = 2
Name = USERSPACE1
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
- list containers for a tablespace
db2 => list tablespace containers for 1
Tablespace Containers for Tablespace 1
Container ID = 0
Name = /home/db2inst1/db2inst1/NODE0000/SQL00001/ /home/db2inst1/db2inst1/NODE0000/SAMPLE7/temptbs/temp.001
Type = File
db2 => disconnect sample7
DB20000I The SQL DISCONNECT command completed successfully.
- putting database in archive log
db2 => archive log for database sample7
SQL2417N Archive log is not allowed on non-recoverable databases.
db2 => connect to sample7
Database Connection Information
Database server = DB2/LINUXX8664 9.7.5
SQL authorization ID = DB2INST1
Local database alias = SAMPLE7
- setting logretain parameter to allow db to put in archive log
db2 => update db cfg using LOGARCHMETH1 LOGRETAIN
DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.
SQL1363W One or more of the parameters submitted for immediate modification
were not changed dynamically. For these configuration parameters, all
applications must disconnect from this database before the changes become
effective.
db2 => disconnect sample7
DB20000I The SQL DISCONNECT command completed successfully.
db2 => archive log for database sample7
SQL1116N A connection to or activation of database "SAMPLE7" cannot be made
because of BACKUP PENDING. SQLSTATE=57019
- checking for error
db2 => ? SQL1116N
SQL1116N A connection to or activation of database "<name>" cannot be
made because of BACKUP PENDING.
Explanation:
The specified database requires a backup to be taken to provide a
starting point for roll-forward recovery.
No connection was made.
User response:
Back up the database by calling the BACKUP routine or, if roll-recovery
is not required, set the database configuration parameters LOGARCHMETH1
and LOGARCHMETH2 to OFF.
sqlcode: -1116
sqlstate: 57019
db2 => drop database sample7
SQL1035N The database is currently in use. SQLSTATE=57019
db2 => disconnect sample7
DB20000I The SQL DISCONNECT command completed successfully.
- take backup of database
db2 => backup database sample7 to /home/db2inst1/db2backups
Backup successful. The timestamp for this backup image is : 20161002005808
db2 => archive log for database sample7
DB20000I The ARCHIVE LOG command completed successfully.
- list backup files
[db2inst1@ibmdb2 db2backups]$ pwd
/home/db2inst1/db2backups
[db2inst1@ibmdb2 db2backups]$ ls
SAMPLE7.0.db2inst1.NODE0000.CATN0000.20161002005808.001
- backup format:
• DBALIAS: Your database name or cataloged alias.
• TYPEID: The type of backup: 0 for full and 3 for tablespace.
• INSTANCE: The instance name from which the backup was taken.
• NODE: The database node for a partitioned database. In this single-partition world, it is
always NODE0000.
• CATALOGNODE: The catalog node number for a partitioned database. Again, because
you’re dealing only with single-partitioned DB2, it always reads CATN0000.
• SEQUENCE: The sequence number for backups taken at the current timestamp.
mkdir -p /home/db2inst1/db2backups/sample7/bkp1 /home/db2inst1/db2backups/sample7/bkp2 /home/db2inst1/db2backups/sample7/bkp3 /home/db2inst1/db2backups/sample7/bkp4
- take backup to different mount points or directories with parallelism
[db2inst1@ibmdb2 db2backups]$ db2 backup database sample7 to /home/db2inst1/db2backups/sample7/bkp1, /home/db2inst1/db2backups/sample7/bkp2, /home/db2inst1/db2backups/sample7/bkp3, /home/db2inst1/db2backups/sample7/bkp4 with 16 buffers buffer 4096 parallelism 4
Backup successful. The timestamp for this backup image is : 20161002011148
- list files
[db2inst1@ibmdb2 db2backups]$ ls ./sample7/bkp1 ./sample7/bkp2 ./sample7/bkp3 ./sample7/bkp4
./sample7/bkp1:
SAMPLE7.0.db2inst1.NODE0000.CATN0000.20161002011148.001
./sample7/bkp2:
SAMPLE7.0.db2inst1.NODE0000.CATN0000.20161002011148.002
./sample7/bkp3:
SAMPLE7.0.db2inst1.NODE0000.CATN0000.20161002011148.003
./sample7/bkp4:
SAMPLE7.0.db2inst1.NODE0000.CATN0000.20161002011148.004
- backup syntax
Backup database database_name [user user_name [using password]]
Tablespace (tablespace_name [, tablespace_name ...] )
[online] [incremental [delta]]
[use advanced_storage_options]
to backup_location [, backup_location ...]
[with n buffers] [buffer size] [parallelism n]
[without prompting]
- tablespace backup
[db2inst1@ibmdb2 tbsbkp]$ db2 backup database sample7 tablespace userspace1 to /home/db2inst1/db2backups/sample7/tbsbkp
Backup successful. The timestamp for this backup image is : 20161002011959
- Online backup
db2 => backup database sample7 online to /home/db2inst1/db2backups/sample7/bkp1, /home/db2inst1/db2backups/sample7/bkp2 include logs
Backup successful. The timestamp for this backup image is : 20161002013954
- Incremental and delta backup
backup database sample7 online incremental delta to /home/db2inst1/db2backups/sample7/bkp1, /home/db2inst1/db2backups/sample7/bkp2
SQL2426N The database has not been configured to allow the incremental backup
operation. Reason code = "1".
- set trackmod ON to take incremental backup
db2 => update db cfg for sample7 using trackmod on
DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.
- take full backup first after trackmod ON for incremental backup
db2 => backup database sample7 to /home/db2inst1/db2backups/sample7
Backup successful. The timestamp for this backup image is : 20161002014701
- now you can take incremental backup
db2 => backup database sample7 online incremental delta to /home/db2inst1/db2backups/sample7/bkp1, /home/db2inst1/db2backups/sample7/bkp2
Backup successful. The timestamp for this backup image is : 20161002014714
- restore database
[db2inst1@ibmdb2 tbsbkp]$ db2 restore database sample7 from /home/db2inst1/db2backups/sample7 taken at 20161002014701
SQL2539W Warning! Restoring to an existing database that is the same as the
backup image database. The database files will be deleted.
Do you want to continue ? (y/n) y
DB20000I The RESTORE DATABASE command completed successfully.
[db2inst1@ibmdb2 tbsbkp]$ db2 connect to sample7
SQL1117N A connection to or activation of database "SAMPLE7" cannot be made
because of ROLL-FORWARD PENDING. SQLSTATE=57019
[db2inst1@ibmdb2 tbsbkp]$ db2 rollforward database sample7 complete
Rollforward Status
Input database alias = sample7
Number of nodes have returned status = 1
Node number = 0
Rollforward status = not pending
Next log file to be read =
Log files processed = -
Last committed transaction = 2016-10-02-05.47.03.000000 UTC
DB20000I The ROLLFORWARD command completed successfully.
- other options for rollforward
db2 rollforward database sample7 to end of logs and complete
db2 rollforward database sample7 to 20161002014714
[db2inst1@ibmdb2 tbsbkp]$ db2 connect to sample7
Database Connection Information
Database server = DB2/LINUXX8664 9.7.5
SQL authorization ID = DB2INST1
Local database alias = SAMPLE7
- restore backup of TESTDB to another database RESTDB
db2 => create database TESTDB automatic storage no on '/home/db2inst1' using codeset utf-8 territory US collate using system pagesize 4096 catalog tablespace managed by system using ('/home/db2inst1/db2inst1/NODE0000/TESTDB') extentsize 16 prefetchsize 16 overhead 10 transferrate 0.2 user tablespace managed by database using (FILE ' home/db2inst1/db2inst1/NODE0000/TESTDB/usertbs.001' 102400, FILE ' /home/db2inst1/db2inst1/NODE0000/TESTDB/usertbs.002' 102400, FILE ' /home/db2inst1/db2inst1/NODE0000/TESTDB/usertbs.003' 102400) extentsize 16 prefetchsize 64 overhead 10 transferrate 0.2 temporary tablespace managed by database using (FILE ' /home/db2inst1/db2inst1/NODE0000/TESTDB/temp.001' 204800) extentsize 16 prefetchsize 16 overhead 4 transferrate 0.05
[db2inst1@ibmdb2 TESTDB]$ db2 update db cfg for TESTDB using NEWLOGPATH /home/db2inst1/db2inst1/NODE0000/TESTDB/logs
DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.
[db2inst1@ibmdb2 TESTDB]$ db2 update db cfg for TESTDB using trackmod on
DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.
[db2inst1@ibmdb2 TESTDB]$ db2 connect to testdb
Database Connection Information
Database server = DB2/LINUXX8664 9.7.5
SQL authorization ID = DB2INST1
Local database alias = TESTDB
[db2inst1@ibmdb2 TESTDB]$ db2 update db cfg using LOGARCHMETH1 LOGRETAIN
DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.
SQL1363W One or more of the parameters submitted for immediate modification
were not changed dynamically. For these configuration parameters, all
applications must disconnect from this database before the changes become
effective.
archive log for database TESTDB
[db2inst1@ibmdb2 TESTDB]$ db2 backup database TESTDB online to /home/db2inst1/db2backups/TESTDB include logs
Backup successful. The timestamp for this backup image is : 20161002111535
/home/db2inst1/db2inst1/NODE0000/RESTDB/logs
[db2inst1@ibmdb2 RESTDB]$ db2 restore database TESTDB from /home/db2inst1/db2backups/TESTDB taken at 20161002111535 to /home/db2inst1/db2inst1/NODE0000/RESTDB into RESTDB newlogpath /home/db2inst1/db2inst1/NODE0000/RESTDB/logs redirect without prompting
SQL1277W A redirected restore operation is being performed. Table space
configuration can now be viewed and table spaces that do not use automatic
storage can have their containers reconfigured.
DB20000I The RESTORE DATABASE command completed successfully.
[db2inst1@ibmdb2 RESTDB]$ db2 "set tablespace containers for 0 using (PATH '/home/db2inst1/db2inst1/NODE0000/RESTDB/syscat')"
DB20000I The SET TABLESPACE CONTAINERS command completed successfully.
[db2inst1@ibmdb2 NODE0000]$ db2 "set tablespace containers for 1 using (FILE '/home/db2inst1/db2inst1/NODE0000/RESTDB/usertbs.001' 204800)"
DB20000I The SET TABLESPACE CONTAINERS command completed successfully.
[db2inst1@ibmdb2 NODE0000]$ db2 "set tablespace containers for 2 using (FILE '/home/db2inst1/db2inst1/NODE0000/RESTDB/temp001' 204800)"
DB20000I The SET TABLESPACE CONTAINERS command completed successfully.
[db2inst1@ibmdb2 RESTDB]$ db2 restore db TESTDB continue
DB20000I The RESTORE DATABASE command completed successfully.
[db2inst1@ibmdb2 logs]$ db2 rollforward database RESTDB to end of logs
Rollforward Status
Input database alias = RESTDB
Number of nodes have returned status = 1
Node number = 0
Rollforward status = DB working
Next log file to be read = S0000000.LOG
Log files processed = S0000000.LOG - S0000000.LOG
Last committed transaction = 2016-10-02-15.15.37.000000 UTC
DB20000I The ROLLFORWARD command completed successfully.
[db2inst1@ibmdb2 logs]$ db2 connect to restdb
SQL1117N A connection to or activation of database "RESTDB" cannot be made
because of ROLL-FORWARD PENDING. SQLSTATE=57019
[db2inst1@ibmdb2 NODE0000]$ cd TESTDB/logs/
[db2inst1@ibmdb2 logs]$ ls
S0000000.LOG S0000003.LOG S0000006.LOG S0000009.LOG S0000012.LOG
S0000001.LOG S0000004.LOG S0000007.LOG S0000010.LOG S0000013.LOG
S0000002.LOG S0000005.LOG S0000008.LOG S0000011.LOG SQLLPATH.TAG
[db2inst1@ibmdb2 logs]$ cp S0*.LOG ../../RESTDB/logs/
[db2inst1@ibmdb2 logs]$ db2 rollforward database RESTDB to end of logs
Rollforward Status
Input database alias = RESTDB
Number of nodes have returned status = 1
Node number = 0
Rollforward status = DB working
Next log file to be read = S0000001.LOG
Log files processed = S0000000.LOG - S0000000.LOG
Last committed transaction = 2016-10-02-15.15.37.000000 UTC
DB20000I The ROLLFORWARD command completed successfully.
[db2inst1@ibmdb2 logs]$ db2 rollforward database RESTDB to end of logs and complete
Rollforward Status
Input database alias = RESTDB
Number of nodes have returned status = 1
Node number = 0
Rollforward status = not pending
Next log file to be read =
Log files processed = S0000000.LOG - S0000000.LOG
Last committed transaction = 2016-10-02-15.15.37.000000 UTC
DB20000I The ROLLFORWARD command completed successfully.
[db2inst1@ibmdb2 logs]$ db2 connect to restdb
Database Connection Information
Database server = DB2/LINUXX8664 9.7.5
SQL authorization ID = DB2INST1
Local database alias = RESTDB
No comments:
Post a Comment
Leave a Reply...