Differences

This shows you the differences between two versions of the page.

--- database:db2 [2021/11/19 23:07]
manu
+++ database:db2 [2024/05/28 17:56] (current)
manu [connect to DB2]
@@ Line 6: / Line 6: @@
 [[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 =====
@@ Line 36: / Line 62: @@
 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
+/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
+/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>
@@ Line 79: / Line 153: @@
 </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 =====
@@ Line 170: / Line 249: @@
 ==== 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
+record(s) selected.
 </cli>
@@ Line 330: / Line 426: @@
 The command that is used to backup the SAMPLE database is:
 <cli prompt='$'>
-$ db2 backup db sample online to /db2/backups include logs
+$ db2 backup db SAMPLE online to /db2/backups include logs
 </cli>
@@ Line 351: / Line 447: @@
 To update configuration LOGARCHMETH1
 <cli prompt='$'>
-$ db2 update database configuration for one using LOGARCHMETH1 'DISK:/home/db2inst1/backup/ArchiveDest'
+$ 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 one online to /home/db2inst1/onlinebackup/ compress include logs
+$ 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='$'>
@@ Line 504: / Line 612: @@
 </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 ====
@@ Line 658: / Line 802: @@
 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

Differences

Page Tools