database:db2
Table of Contents
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
[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".
List installed binaries
[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
Check DB2 status
[tsminst1@tsm81 ~]$ db2pd - Database Member 0 -- Active -- Up 23 days 02:58:58 -- Date 2021-03-12-10.05.12.208598
Validate a DB2 installation
DB2 does provide a tool that allows for the validation of a DB2 server install:
[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.
DB2 Upgrade
First backup the DB
Install the new binaries using package (./setup)
Precheck for instance upgrade (as instance owner)
[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.
Upgrade the instance (as root)
[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.
Upgrade the database (as instance owner)
[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.
create a SAMPLE DB with DB2
Create a sample database, test connection, and delete (drop):
[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.
connect to DB2
[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
Update a user password
For example, use the CONNECT statement to change the password of the user instance,
$ db2 "CONNECT TO <database> USER <userid> USING <password> NEW <new_password> CONFIRM <new_password>"
DB2 list command
List instance
List all instances DB2
[root@prtsm02 ~]# /opt/tivoli/tsm/db2/bin/db2ilist tsmsrv2
List detailed informations from DB2 registry
[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,
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
[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 =
List all active databases
[db2inst1@dev-tpc ~]$ db2 LIST ACTIVE DATABASES Active Databases Database name = LDAPDB2 Applications connected currently = 17 Database path = /home/ldapdb2/ldapdb2/NODE0000/SQL00001/
List users
List all users qnd properties
[db2inst1@dev-tpc ~]$ db2 "select * from syscat.tabauth"
List tables
List all tables using list tables or list tables for all
[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
List tables for a specific schema:
[db2inst1@dev-tpc ~]$ db2 LIST TABLES for schema TSMDB1
List column description
Example with fake values
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.
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:
$ db2licm -a /install/tsm/6.3.4/COI/PackageSteps/DB2/FILES/ese/db2/license/db2ese.lic
Check licenses:
$ db2licm -l Product name: "DB2 Enterprise Server Edition" License type: "Restricted" Expiry date: "Permanent" Product identifier: "db2ese" Version information: "9.7"
List DB2 logs
To list all logs (archives and active logs)
[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
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
$ db2 list application Auth Id Application Appl. Application Id DB # of Name Handle Name Agents -------- -------------- ---------- --------------------- ----------------------------------------- -------- ----- DB2INST1 db2bp 39 *LOCAL.db2inst1.140722043938 ONE 1
To force application using app. Handled id
$ db2 "force application (39)" DB20000I The FORCE APPLICATION command completed successfully. DB21024I This command is asynchronous and may not be effective immediately.
To terminate Database Connection
$ db2 terminate
To deactivate Database
$ db2 deactivate database one
To take the backup file
$ db2 backup database <db_name> to <location_folder>
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
$ 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) =
$ 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
Create an online backup
The command that is used to backup the SAMPLE database is:
$ db2 backup db SAMPLE online to /db2/backups include logs
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
$ mkdir backup $ mkdir backup/ArchiveDest
To provide user permissions for folder
$ chown db2inst1:db2iadm1 backup/ArchiveDest
To update configuration LOGARCHMETH1
$ db2 update database configuration for SAMPLE using LOGARCHMETH1 'DISK:/home/db2inst1/backup/ArchiveDest'
You can take offline backup for safety, activate the database and connect to it.
First backup OFFLINE, required to enable new parameters
$ db2 backup database SAMPLE to /home/db2inst1/onlinebackup/
To take online backup
$ db2 backup database SAMPLE online to /home/db2inst1/onlinebackup/ compress include logs
Backup type: FULL, INCREMENTAL or DIFFERENTIAL, backup to TSM
$ db2 backup db SAMPLE use tsm $ db2 backup db SAMPLE online incremental delta use tsm $ db2 backup db SAMPLE online incremental use tsm
Verify Backup file using following command:
$ db2ckbkp <location/backup file>
Example:
$ db2ckbkp/home/db2inst1/ONE.0.db2inst1.DBPART000.20140722112743.001
Listing the history of backup files
$ 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:
List all archive logs
$ 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
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:
$ db2 update database configuration for SAMPLE using NUM_DB_BACKUPS 2 DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.
$ db2 update database configuration for SAMMPLE using REC_HIS_RETENTN 0 DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.
$ 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.
Restore DB
List DB2 backup/restore progress:
$ 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
Restore a DB from a file (backup of DB dbtest2 into the DB dbtest4):
$ 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
DB2 instance file location: /opt/IBM/db2/V10.1/global.reg
Stop the DB2 database
[tsminst1@tsmsrv1] /tsminst1 > db2 force application all [tsminst1@tsmsrv1] /tsminst1 > db2stop force [tsminst1@tsmsrv1] /tsminst1 > db2start
Backup the DB2 database into a folder large enough to contain the backup
[tsminst1@tsmsrv1] /tsminst1 > db2 "backup db tsmdb1 to /backup compress "
These commands can be used to restore the database Backup of DB2 database
[tsminst1@tsmsrv1] /tsminst1 > db2start [tsminst1@tsmsrv1] /tsminst1 > db2 "restore db tsmdb1 from /backup replace existing without rolling forward"
DB2 table size
[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.
DB2 table size
You can get the underlying table physical size from the SYSIBMADM.ADMINTABINFO table.
The command is given below.
[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
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
[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
DB2 databse explorer (opensource)
DB2 reorg
To reorganize a DB2 database, you can enable the parameter: AUTO_REORG
[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
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
[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
List DB size
[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
https://datageek.blog/2014/06/02/how-to-tell-when-a-table-reorg-is-needed/
Uninstall DB2
[root@lnx01] /tmp/db2/universal # ./db2_deinstall -a -b /opt/ibm/db2/V11.5 DBI1016I Program db2_deinstall is performing uninstallation. Please
database/db2.txt · Last modified: 2024/05/28 17:56 by manu
Except where otherwise noted, content on this wiki is licensed under the following license: GNU Free Documentation License 1.3
