wiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
{{::under_construction.jpg?150}}

[[database:db2_list_cmd|DB2 list commands]]

[[database:db2_backup_restore|DB2 backup / restore]]

[[database:db2_clone_steps|DB2 clone steps]]

[[database:db2_install_steps|DB2 install steps]]

====== DB2 command line ======

https://www.ibm.com/docs/en/db2/11.5?topic=instances-designing

https://www.ibm.com/developerworks/servicemanagement/library/sm-tsm-db2/

===== Check DB2 version =====

<cli prompt='$'>
[db2sc@tpc ~]$ db2level
DB21085I  This instance or install (instance name, where applicable: "db2sc")
uses "64" bits and DB2 code release "SQL11013" with level identifier
"0200010F".
Informational tokens are "DB2 v11.1.3.3", "s18xxxxx00", "DYN18xxxxx00AMD64",
and Fix Pack "3b".
Product is installed at "/opt/ibm/db2/V11.1".
</cli>

List installed binaries
<cli prompt='#'>
[root@tpc ~] # db2ls

Install Path                       Level   Fix Pack   Special Install Number   Install Date                  Installer UID
---------------------------------------------------------------------------------------------------------------------
/opt/ibm/db2/V11.1               11.1.3.3        3b                           Wed Mar 13 13:11:02 2019 CET             0
/opt/ibm/db2/V11.5               11.5.4.0        0                            Wed Jan  5 12:38:15 2022 CET             0
</cli>

===== Check DB2 status =====

<cli prompt='$'>
[tsminst1@tsm81 ~]$ db2pd -

Database Member 0 -- Active -- Up 23 days 02:58:58 -- Date 2021-03-12-10.05.12.208598

</cli>


===== Validate a DB2 installation =====

DB2 does provide a tool that allows for the validation of a DB2 server install:
<cli prompt='#'>
[root@tsm81 ~]# /opt/tivoli/tsm/db2/bin/db2val -a
DBI1379I The db2val command is running. This can take several minutes.

DBI1335I Installation file validation for the DB2 copy installed at
/opt/tivoli/tsm/db2 was successful.

DBI1339I The instance validation for the instance tsminst1 was
successful.

DBI1343I The db2val command completed successfully. For details, see
the log file /tmp/db2val-170915_112507.log.
</cli>

===== DB2 Upgrade =====

First backup the DB

Install the new binaries using package (./setup)

Precheck for instance upgrade (as instance owner)
<cli prompt='$'>
[db2sc@ibmsc ~]$ /opt/ibm/db2/V11.5/bin/db2ckupgrade TPCDB -l db2ckupgrade.log
DBT5508I The db2ckupgrade utility completed successfully. The database or databases can be upgraded.
[db2sc@ibmsc ~]$ cat db2ckupgrade.log
Version of DB2CKUPGRADE being run: VERSION "11.5"
Database: "TPCDB"
DBT5508I The db2ckupgrade utility completed successfully. The database or databases can be upgraded.
[db2sc@ibmsc ~]$ db2 list applications
SQL1611W No data was returned by Database System Monitor.
[db2sc@ibmsc ~]$ db2 force application all
DB20000I The FORCE APPLICATION command completed successfully.
DB21024I This command is asynchronous and may not be effective immediately.
[db2sc@ibmsc ~]$ db2 terminate
DB20000I The TERMINATE command completed successfully.
[db2sc@ibmsc ~]$ db2stop
01/05/2022 12:47:32 0 0 SQL1064N DB2STOP processing was successful.
SQL1064N DB2STOP processing was successful.
</cli>

Upgrade the instance (as root)
<cli prompt='#'>
[root@ibmsc ~]# /opt/ibm/db2/V11.1/bin/db2ilist
db2sc
[root@ibmsc ~]# /opt/ibm/db2/V11.5/instance/db2iupgrade db2sc
DBI1446I The db2iupgrade command is running.

DB2 installation is being initialized.
...
DBI1070I Program db2iupgrade completed successfully.
</cli>

Upgrade the database (as instance owner)
<cli prompt='$'>
[db2sc@ibmsc ~]$ db2start
01/05/2022 12:53:22 0 0 SQL1063N DB2START processing was successful.
SQL1063N DB2START processing was successful. 

[db2sc@ibmsc ~]$ db2 UPGRADE DATABASE tpcdb
DB20000I The UPGRADE DATABASE command completed successfully.
</cli>

===== create a SAMPLE DB with DB2 =====

Create a sample database, test connection, and delete (drop):
<cli prompt='$'>
[db2inst1@dev-tpc ~]$ db2sampl

  Creating database "SAMPLE"...
  Connecting to database "SAMPLE"...
  Creating tables and data in schema "DB2INST1"...
  Creating tables with XML columns and XML data in schema "DB2INST1"...

  'db2sampl' processing complete.
</cli>
  
===== connect to DB2 =====
  
<cli prompt='$'>
[db2inst1@dev-tpc ~]$ db2 connect to sample

   Database Connection Information

 Database server        = DB2/LINUXX8664 9.7.4
 SQL authorization ID   = DB2INST1
 Local database alias   = SAMPLE

[db2inst1@dev-tpc ~]$ db2 "select * from staff where dept = 20"

ID     NAME      DEPT   JOB   YEARS  SALARY    COMM     
------ --------- ------ ----- ------ --------- ---------
    10 Sanders       20 Mgr        7  98357.50         -
    20 Pernal        20 Sales      8  78171.25    612.45
    80 James         20 Clerk      -  43504.60    128.20
   190 Sneider       20 Clerk      8  34252.75    126.50

  4 record(s) selected.

[db2inst1@dev-tpc ~]$ db2 connect reset

[db2inst1@dev-tpc ~]$ db2 terminate
</cli>

===== Update a user password =====

For example, use the CONNECT statement to change the password of the user instance,
<cli prompt='$'>
$ db2 "CONNECT TO <database> USER <userid> USING <password> NEW <new_password> CONFIRM <new_password>"
</cli>
===== DB2 list command =====

==== List instance ====

List all instances DB2
<cli prompt='#'>
[root@prtsm02 ~]# /opt/tivoli/tsm/db2/bin/db2ilist 
tsmsrv2
</cli>

List detailed informations from DB2 registry
<cli prompt='#'>
[root@prtsm02 ~]# /opt/tivoli/tsm/db2/bin/db2greg -dump
S,RSCT,3.2.1.2,/usr/sbin/rsct,DG_NOT_ALLOWED,DB2_INSTALLED,0,0,-,1485695122,0
S,DB2,11.1.0.0,/opt/tivoli/tsm/db2,,,0,0,,1485695251,0
V,DB2GPRF,DB2SYSTEM,prtsm02,/opt/tivoli/tsm/db2,
I,DB2,11.1.0.0,tsmsrv2,/tsmsrv2/sqllib,,1,0,/opt/tivoli/tsm/db2,,
V,DB2GPRF,DB2INSTDEF,tsmsrv2,/opt/tivoli/tsm/db2,
</cli>

The instance records will start with "I", the installation records (called "Service" records) will start with an "S", and the variable records will start with a "V". You need to locate the appropriate records. 

==== List databases ====

List all databases
<cli prompt='$'>
[db2inst1@dev-tpc ~]$ db2 list database directory show detail 

 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         =
</cli>

List all active databases
<cli prompt='$'>
[db2inst1@dev-tpc ~]$ db2 LIST ACTIVE DATABASES

                           Active Databases

Database name                              = LDAPDB2
Applications connected currently           = 17
Database path                              = /home/ldapdb2/ldapdb2/NODE0000/SQL00001/
</cli>

==== List users ====

List all users qnd properties
<cli prompt='$'>
[db2inst1@dev-tpc ~]$ db2 "select * from syscat.tabauth"
</cli>

==== List tables ====

List all tables using **list tables** or **list tables for all**
<cli prompt='$'>
[db2inst1@dev-tpc ~]$ db2 LIST TABLES for all

Table/View                      Schema          Type  Creation time
------------------------------- --------------- ----- --------------------------
ACLINHERIT                      LDAPDB2         T     2011-02-23-10.49.51.236767
ACLPERM                         LDAPDB2         T     2011-02-23-10.49.51.479535
ACLPROP                         LDAPDB2         T     2011-02-23-10.49.51.123359
AIXADMINGROUPID                 LDAPDB2         T     2011-02-23-14.55.31.528944
AIXADMINUSERID                  LDAPDB2         T     2011-02-23-14.55.31.791244
AIXFUNCMODE                     LDAPDB2         T     2011-02-23-14.55.28.652156
AIXGROUPID                      LDAPDB2         T     2011-02-23-14.55.32.032848
AIXSCREENS                      LDAPDB2         T     2011-02-23-14.55.28.382944
AIXUSERID                       LDAPDB2         T     2011-02-23-14.55.32.311096
</cli>

List tables for a specific schema:
<cli prompt='$'>
[db2inst1@dev-tpc ~]$ db2 LIST TABLES for schema TSMDB1
</cli>


==== List column description ====

Example with fake values
<cli prompt='$'>
db2inst1@dev-tpc ~]$ db2 describe table LDAPDB2.AIXUSERID
                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
NAME                            LDAPDB2   VARCHAR                     31     0 No
UID                             LDAPDB2   INTEGER                     40     0 No
GID                             LDAPDB2   INTEGER                     40     0 No
CLOUDURL                        LDAPDB2   VARCHAR                    871     0 No
CLOUDID                         LDAPDB2   VARCHAR                    256     0 Yes
BUCKETNAME                      LDAPDB2   VARCHAR                     64     0 Yes
DESCRIPTION                     LDAPDB2   VARCHAR                    256     0 Yes
KEYLOCATION                     LDAPDB2   VARCHAR                    257     0 Yes
CLOUDVENDOR                     LDAPDB2   VARCHAR                    101     0 Yes
HEALTH                          LDAPDB2   INTEGER                      4     0 Yes

  10 record(s) selected.

</cli>

==== Misc ====

To verify that the TPCDB database
  db2 list db directory
  
Are the tables and views present in the TPCDB database?\\
To verify that the tables and views are present, run the following DB2 commands:
  db2 connect to TPCDB
  db2 list tables for schema TPC
  db2 disconnect TPCDB

List connections on a DB
  db2 list applications
  
Suppress a DB instance:
  db2idrop <inst_name>
  To force in case of failure, use
  db2iset -d <inst_name>
  

==== DB2 licenses ====

Register new licenses:
<cli prompt='$'>
$ db2licm -a /install/tsm/6.3.4/COI/PackageSteps/DB2/FILES/ese/db2/license/db2ese.lic
</cli>

Check licenses:
<cli prompt='$'>
$ db2licm -l
Product name:                     "DB2 Enterprise Server Edition"
License type:                     "Restricted"
Expiry date:                      "Permanent"
Product identifier:               "db2ese"
Version information:              "9.7"
</cli>

==== List DB2 logs ====

To list all logs (archives and active logs)
<cli prompt='#'>
[tsminst1@tsm]/tsm/tsminst1#  db2pd -logs -db TSMDB1

Database Partition 0 -- Database TSMDB1 -- Active -- Up 50 days 23:49:45 -- Date 2016-09-29-14.22.49.159143

Logs:
Current Log Number            57970
Pages Written                 123389
Cur Commit Disk Log Reads     97
Cur Commit Total Log Reads    537
Method 1 Archive Status       Success
Method 1 Next Log to Archive  57970
Method 1 First Failure        n/a
Method 2 Archive Status       n/a
Method 2 Next Log to Archive  n/a
Method 2 First Failure        n/a
Log Chain ID                  1
Current LSN                   0x00001C4E605FDCED

Address            StartLSN         State      Size       Pages      Filename
0x0A0006019FA1CC50 00001C4E42400010 0x00000000 131072     131072     S0057970.LOG
0x0A000601DA960FD0 00001C4E62400010 0x00000000 131072     131072     S0057971.LOG
0x0A000601A1979BD0 00001C4E82400010 0x00000000 131072     131072     S0057972.LOG
0x0A000601DA9241B0 00001C4EA2400010 0x00000000 131072     131072     S0057973.LOG
0x0A000601A197AC50 00001C4EC2400010 0x00000000 131072     131072     S0057974.LOG
0x0A000601A19C0630 00001C4EE2400010 0x00000000 131072     131072     S0057975.LOG
0x0A000601A115FE30 00001C4F02400010 0x00000000 131072     131072     S0057976.LOG
0x0A000601DA99C270 00001C4F22400010 0x00000000 131072     131072     S0057977.LOG
0x0A000601DA999CF0 00001C4F42400010 0x00000000 131072     131072     S0057978.LOG
0x0A000601A10DAB70 00001C4F62400010 0x00000000 131072     131072     S0057979.LOG
0x0A0006019FABEED0 00001C4F82400010 0x00000000 131072     131072     S0057980.LOG
</cli>

===== Backup/restore DB2 database =====

The types of backups that can be taken include:
  * Full – the entire database is backed up
  * Incremental – changes since the last full backup are backed up (restore requires a full image and the incremental image)
  * Delta – changes since the last full or incremental or delta backup are backed up (restore requires a full image and all incremental and delta images since the full image)

==== Offline backup ====

To list the active applications/databases
<cli prompt='$'>
$ db2 list application  
Auth Id  Application    Appl.      Application Id                                                
DB       # of   
         Name           Handle              
Name    Agents  
-------- -------------- ---------- ---------------------
----------------------------------------- -------- -----  
DB2INST1 db2bp          39         
*LOCAL.db2inst1.140722043938                                   
ONE      1  
</cli>

To force application using app. Handled id
<cli prompt='$'>
$ db2 "force application (39)"   
DB20000I  The FORCE APPLICATION command completed 
successfully.  

DB21024I  This command is asynchronous and may not 
be effective immediately. 
</cli>

To terminate Database Connection
<cli prompt='$'>
$ db2 terminate  
</cli>

To deactivate Database
<cli prompt='$'>
$ db2 deactivate database one   
</cli>

To take the backup file
<cli prompt='$'>
$ db2 backup database <db_name> to <location_folder>   
</cli>

Example:
  db2 backup database one to /home/db2inst1/ 
    Backup successful. The timestamp for this backup image is : 
    20140722105345  

==== Online backup ====

To start, you need to change the mode from Circular logging to Archive Logging.

To check if the database is using circular or archive logging
<cli prompt='$'>
$ db2 get db cfg for SAMPLE | grep LOGARCH   
First log archive method (LOGARCHMETH1) = OFF  
 Archive compression for logarchmeth1  (LOGARCHCOMPR1) = OFF 
 Options for logarchmeth1              (LOGARCHOPT1) =   
 Second log archive method             (LOGARCHMETH2) = OFF  
 Archive compression for logarchmeth2  (LOGARCHCOMPR2) = OFF  
 Options for logarchmeth2              (LOGARCHOPT2) =   
</cli>
<cli prompt='$'>
$ db2 connect to SAMPLE
Database Connection Information
Database server        = DB2/LINUXX8664 10.1.3
SQL authorization ID   = DB2INST1
Local database alias   = SAMPLE
$ db2 update db cfg for SAMPLE using LOGARCHMETH1 DISK:/db2/archivelogs
$ db2 update database configuration for SAMPLE using NUM_DB_BACKUPS  2
$ db2 update database configuration for SAMMPLE using REC_HIS_RETENTN 0
$ db2 update database configuration for SAMPLE using AUTO_DEL_REC_OBJ ON
</cli>

Create an online backup

The command that is used to backup the SAMPLE database is:
<cli prompt='$'>
$ db2 backup db SAMPLE online to /db2/backups include logs
</cli>

The DB2 backup INCLUDE LOGS option will contain the logs that are generated during the time that the backup is performed. The backup processing will include the First Active Log when the backup is started though to the log that is truncated at the end of the backup.

In the above output, the highlighted values are [logarchmeth1 and logarchmeth2] in off mode, which implies that the current database in “CIRCULLAR LOGGING” mode. If you need to work with ‘ARCHIVE LOGGING’ mode, you need to change or add path in the variables logarchmeth1 and logarchmeth2 present in the configuration file.

Updating logarchmeth1 with required archive directory
To make directories
<cli prompt='$'>
$ mkdir backup 
$ mkdir backup/ArchiveDest
</cli>
   
To provide user permissions for folder
<cli prompt='$'>
$ chown db2inst1:db2iadm1 backup/ArchiveDest 
</cli>

To update configuration LOGARCHMETH1
<cli prompt='$'>
$ db2 update database configuration for SAMPLE using LOGARCHMETH1 'DISK:/home/db2inst1/backup/ArchiveDest'
</cli>

You can take offline backup for safety, activate the database and connect to it.

First backup OFFLINE, required to enable new parameters
<cli prompt='$'>
$ db2 backup database SAMPLE to /home/db2inst1/onlinebackup/       
</cli>

To take online backup
<cli prompt='$'>
$ db2 backup database SAMPLE online to /home/db2inst1/onlinebackup/ compress include logs       
</cli>

Backup type: FULL, INCREMENTAL or DIFFERENTIAL, backup to TSM
<cli prompt='$'>
$ db2 backup db SAMPLE use tsm
$ db2 backup db SAMPLE online incremental delta use tsm
$ db2 backup db SAMPLE online incremental use tsm
</cli>
   
Verify Backup file using following command:
<cli prompt='$'>
$ db2ckbkp <location/backup file>   
</cli>

Example:
  $ db2ckbkp/home/db2inst1/ONE.0.db2inst1.DBPART000.20140722112743.001 

Listing the history of backup files
<cli prompt='$'>
$ db2 list history backup all for one    
                    List History File for one 
  
Number of matching file entries = 4 
 
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  
Backup ID  
 -- --- ------------------ ---- --- ------------ ------------ 
 --------------
  B  D  20140722105345001   F    D  S0000000.LOG S0000000.LOG 

 ------------------------------------------------------------ 
 ----------------   
 
 Contains 4 tablespace(s): 
 00001 SYSCATSPACE  
 
 00002 USERSPACE1
 
 00003 SYSTOOLSPACE 
 
 00004 TS1 
  ------------------------------------------------------------ 
  ---------------- 
  Comment: DB2 BACKUP ONE OFFLINE  
  
 Start Time: 20140722105345  
 
   End Time: 20140722105347
   
     Status: A
 ------------------------------------------------------------ 
 ---------------- 
 EID: 3 Location: /home/db2inst1 

 
 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  
 Backup ID
 -- --- ------------------ ---- --- ------------ ------------ 
 --------------  
  B  D  20140722112239000   N       S0000000.LOG S0000000.LOG   
 ------------------------------------------------------------ 
 ------------------------------------------------------------- 
 ------------------------------- 
 
 Comment: DB2 BACKUP ONE ONLINE  
 
 Start Time: 20140722112239 
 
   End Time: 20140722112240  
   
     Status: A 
 ------------------------------------------------------------ 
 ----------------  
  EID: 4 Location: 
SQLCA Information 
 
 sqlcaid : SQLCA     sqlcabc: 136   sqlcode: -2413   sqlerrml: 0 
 
 sqlerrmc:   
 sqlerrp : sqlubIni  
 sqlerrd : (1) 0                (2) 0                (3) 0 
 
           (4) 0                (5) 0                (6) 0  
		   
 sqlwarn : (1)      (2)      (3)      (4)        (5)       (6)  
 
           (7)      (8)      (9)      (10)       (11)  
 sqlstate: 
 
 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  
 Backup ID
  -- --- ------------------ ---- --- ------------ ------------ 
  -------------- 
   B  D  20140722112743001   F    D  S0000000.LOG S0000000.LOG   
 
 ------------------------------------------------------------ 
 ---------------- 
 Contains 4 tablespace(s): 
 
 00001 SYSCATSPACE 
 
 00002 USERSPACE1 
 
 00003 SYSTOOLSPACE 
 
 00004 TS1
  ------------------------------------------------------------- 
  ---------------- 
  Comment: DB2 BACKUP ONE OFFLINE 
  
 Start Time: 20140722112743 
 
   End Time: 20140722112743 
   
     Status: A 
 ------------------------------------------------------------- 
  ---------------- 
 EID: 5 Location: /home/db2inst1 
 
 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log 
 Backup ID   
  ------------------------------------------------------------- 
  ----------------
  
R  D  20140722114519001   F                                
20140722112743 

 ------------------------------------------------------------ 
 ----------------  
 Contains 4 tablespace(s):  
 
 00001 SYSCATSPACE 
 
  00002 USERSPACE1 
  
 00003 SYSTOOLSPACE  
 
 00004 TS1
 ------------------------------------------------------------ 
 ----------------  
Comment: RESTORE ONE WITH RF
  
 Start Time: 20140722114519 
 
   End Time: 20140722115015  
     Status: A  
	 
 ------------------------------------------------------------ 
 ----------------  
  EID: 6 Location:  
</cli>

List all archive logs 
<cli prompt='$'>
$ db2 list history archive log all for TSMDB1

                    List History File for TSMDB1

Number of matching file entries = 3


 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  X  D  20230109220043      1    D  S0001393.LOG C0000000
 ----------------------------------------------------------------------------

 ----------------------------------------------------------------------------
    Comment:
 Start Time: 20230109220043
   End Time: 20230109230358
     Status: A
 ----------------------------------------------------------------------------
  EID: 6696 Location: /tsm/tsmarchlog/tsminst1/TSMDB1/NODE0000/LOGSTREAM0000/C0000000/S0001393.LOG


 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  X  D  20230109230357      1    D  S0001394.LOG C0000000
 ----------------------------------------------------------------------------

 ----------------------------------------------------------------------------
    Comment:
 Start Time: 20230109230357
   End Time: 20230110085109
     Status: A
 ----------------------------------------------------------------------------
  EID: 6702 Location: /tsm/tsmarchlog/tsminst1/TSMDB1/NODE0000/LOGSTREAM0000/C0000000/S0001394.LOG
</cli>
==== MAINTENANCE OF BACKUPS AND ARCHIVE LOGS ====

Clearly, you don’t want to manage this process yourself. If you decide to keep 2 days of online backups we want this process to be automated. This is simple to do and DB2 provides 3 parameters that will set this up.

^Parameter ^	Value ^	Description ^
|NUM_DB_BACKUPS|	2|	Number of database backups to retain|
|AUTO_DEL_REC_OBJ|	ON|	Auto deletion of recovery objects    (backups and archive logs)|
|REC_HIS_RETENTN|	0|	This sets the maximum number of days to keep the backups.|

If AUTO_DEL_REC_OBJ is set to ON, automated history file pruning and recovery object deletion are carried out based on the timestamp of the backup selected by the NUM_DB_BACKUPS database configuration parameter

The commands to set these parameters are:
<cli prompt='$'>
$ db2 update database configuration for SAMPLE using NUM_DB_BACKUPS  2
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
</cli>

<cli prompt='$'>
$ db2 update database configuration for SAMMPLE using REC_HIS_RETENTN 0
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
</cli>

<cli prompt='$'>
$ db2 update database configuration for SAMPLE using AUTO_DEL_REC_OBJ ON
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
You will need to restart the database in order for these parameters to become active.
</cli>

==== Restore DB ====

List DB2 backup/restore progress:
<cli prompt='$'>
$ db2 list utilities show detail

ID                               = 14346
Type                             = RESTORE
Database Name                    = DBTEST4
Partition Number                 = 0
Description                      = db
Start Time                       = 05/19/2014 09:29:09.946528
State                            = Executing
Invocation Type                  = User
Progress Monitoring:
      Completed Work             = 157494358016 bytes
      Start Time                 = 05/19/2014 09:29:09.946531
</cli>

Restore a DB from a file (backup of DB dbtest2 into the DB dbtest4):
<cli prompt='$'>
$ db2 restore db dbtest4 from /backup_db2/dbtest2/test2-201407180720 on /db2/dev4_data1,/db2/dev4_data2 dbpath on /db2/dev4_config into dbtest4 logtarget /work/tlog
</cli>

DB2 instance file location: /opt/IBM/db2/V10.1/global.reg

Stop the DB2 database
<cli prompt='>'>
[tsminst1@tsmsrv1] /tsminst1 > db2 force application all
[tsminst1@tsmsrv1] /tsminst1 > db2stop force
[tsminst1@tsmsrv1] /tsminst1 > db2start
</cli>

Backup the DB2 database into a folder large enough to contain the backup
<cli prompt='>'>
[tsminst1@tsmsrv1] /tsminst1 > db2 "backup db tsmdb1 to /backup compress "
</cli>

These commands can be used to restore the database Backup of DB2 database 
<cli prompt='>'>
[tsminst1@tsmsrv1] /tsminst1 > db2start
[tsminst1@tsmsrv1] /tsminst1 > db2 "restore db tsmdb1 from /backup replace existing without rolling forward"
</cli>

==== DB2 table size ====

<cli prompt='$'>
[db2inst1@ILMTPRD1 ~]$ db2 connect to BFENT

   Database Connection Information

 Database server        = DB2/LINUXX8664 10.5.3
 SQL authorization ID   = DB2INST1
 Local database alias   = BFENT

[db2inst1@ILMTPRD1 ~]$ db2
(c) Copyright IBM Corporation 1993,2007
Command Line Processor for DB2 Client 10.5.3

db2 => CALL GET_DBSIZE_INFO(?, ?, ?, 0)

  Value of output parameters
  --------------------------
  Parameter Name  : SNAPSHOTTIMESTAMP
  Parameter Value : 2019-08-02-09.51.29.835140

  Parameter Name  : DATABASESIZE
  Parameter Value : 10306035712

  Parameter Name  : DATABASECAPACITY
  Parameter Value : 49539272704

  Return Status = 0
db2 => quit
DB20000I  The QUIT command completed successfully.
</cli>

==== DB2 table size ====

You can get the underlying table physical size from the SYSIBMADM.ADMINTABINFO table.

The command is given below.

<cli prompt='>'>
[tsminst1@tsmsrv1] /tsminst1 > db2 "SELECT SUBSTR(TABSCHEMA,1,15) as SCHEMA, SUBSTR(TABNAME,1,20) as TABLENAME, DATA_OBJECT_P_SIZE ,INDEX_OBJECT_P_SIZE , LONG_OBJECT_P_SIZE , LOB_OBJECT_P_SIZE , XML_OBJECT_P_SIZE, (DATA_OBJECT_P_SIZE + INDEX_OBJECT_P_SIZE + LONG_OBJECT_P_SIZE + LOB_OBJECT_P_SIZE + XML_OBJECT_P_SIZE ) as TOTAL_P_SIZE from SYSIBMADM.ADMINTABINFO where TABNAME='table_name'"


SCHEMA          TABLENAME            DATA_OBJECT_P_SIZE   INDEX_OBJECT_P_SIZE  LONG_OBJECT_P_SIZE   LOB_OBJECT_P_SIZE    XML_OBJECT_P_SIZE    TOTAL_P_SIZE
--------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
SYSIBM          SYSVIEWDEP                            192                  384                    0                    0                    0                  576
...
TSMDB1          BACKUP_OBJECTS                   97118208            111546368                    0                    0                    0            208664576
TSMDB1          ARCHIVE_OBJECTS                   2410496              1507328                    0                    0                    0              3917824
</cli>
The total physical size is the sum of all the DATA , INDEX, LONG , LOB and XML OBJECT physical sizes (_P_SIZE indicates Physical Size).

==== DB2 activity history ====

<cli prompt='$'>
[tsminst1@tsmsrv1 ~]$ db2 list history since 20200731000000 for database tsmdb1
[tsminst1@tsmsrv1 ~]$ db2 -v "list history reorg all for tsmdb1"
list history reorg all for tsmdb1

                    List History File for tsmdb1

Number of matching file entries = 1


 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  G  I  20200806100407      N       S0095782.LOG S0095782.LOG
 ----------------------------------------------------------------------------
  Table:  "TSMDB1  "."SEQ_VOLUME_HISTORY"

 ----------------------------------------------------------------------------
    Comment: REORG INDEXES CLEANUP ALL RECL
 Start Time: 20200806100407
   End Time: 20200806100407
     Status: A
 ----------------------------------------------------------------------------
  EID: 100876
</cli>

===== DB2 databse explorer (opensource) =====

http://www.squirrelsql.org/

===== DB2 reorg =====

To reorganize a DB2 database, you can enable the parameter: AUTO_REORG
<cli prompt='$'>
[db2sc@lnx779l ~]$ db2 get db cfg for TPCDB | grep reorg
     Automatic reorganization               (AUTO_REORG) = OFF

[db2sc@lnx779l ~]$ db2 update db cfg for TPCDB using auto_reorg on
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.

[db2sc@lnx779l ~]$ db2 get db cfg for TPCDB | grep reorg
     Automatic reorganization               (AUTO_REORG) = ON
</cli>

The 3 folowing parameters have to be set to ON
  db2 update db cfg for <db_name> using auto_maint on
  db2 update db cfg for <db_name> using auto_tbl_maint on
  db2 update db cfg for <db_name> using auto_reorg on
 
List historic
<cli prompt='$'>
[db2sc@lnx779l ~]$ db2 -v "list history reorg all for TPCDB"
 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  G  T  20210122103457      F       S0000000.LOG S0000002.LOG
 ----------------------------------------------------------------------------
  Table:  "TPC     "."T_STORAGE_VOLUME"

 ----------------------------------------------------------------------------
    Comment: REORG
 Start Time: 20210122103457
   End Time: 20210122103457
     Status: A
 ----------------------------------------------------------------------------
  EID: 702


 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  G  T  20210122103457      F       S0000000.LOG S0000002.LOG
 ----------------------------------------------------------------------------
  Table:  "TPC     "."T_RES_REGISTERED_NAPI"

 ----------------------------------------------------------------------------
    Comment: REORG
 Start Time: 20210122103457
   End Time: 20210122103457
     Status: A
 ----------------------------------------------------------------------------
  EID: 703
</cli>

List DB size
<cli prompt='$'>
[db2sc@lnxl779l ~]$  db2 "CALL GET_DBSIZE_INFO (?, ?, ?, -1)"

  Value of output parameters
  --------------------------
  Parameter Name  : SNAPSHOTTIMESTAMP
  Parameter Value : 2024-03-01-13.18.40.360884

  Parameter Name  : DATABASESIZE
  Parameter Value : 381817823232

  Parameter Name  : DATABASECAPACITY
  Parameter Value : 489766322176

  Return Status = 0
</cli>

https://datageek.blog/2014/06/02/how-to-tell-when-a-table-reorg-is-needed/

===== Uninstall DB2 =====

<cli prompt='#'>
[root@lnx01] /tmp/db2/universal # ./db2_deinstall -a -b /opt/ibm/db2/V11.5
DBI1016I  Program db2_deinstall is performing uninstallation. Please
</cli>
wiki

User Tools

Site Tools

Page Tools
