Differences

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

--- database:db2 [2021/11/10 23:15]
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 306: / Line 402: @@
 To check if the database is using circular or archive logging
 <cli prompt='$'>
-$ db2 get db cfg for one | grep LOGARCH
+$ db2 get db cfg for SAMPLE | grep LOGARCH
 First log archive method (LOGARCHMETH1) = OFF
  Archive compression for logarchmeth1  (LOGARCHCOMPR1) = OFF
@@ Line 314: / Line 410: @@
  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.
@@ Line 331: / 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 484: / 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 638: / 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