database:db2
This is an old revision of the document!
Table of Contents
DB2 command line
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.
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
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
db2inst1@dev-tpc ~]$ db2 describe table LDAPDB2.AIXUSERID
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
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 one | 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) =
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 one using LOGARCHMETH1 'DISK:/home/db2inst1/backup/ArchiveDest'
You can take offline backup for safety, activate the database and connect to it.
To take online backup
$ db2 backup database one online to /home/db2inst1/onlinebackup/ compress include logs
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:
Prepare a DB to use online backup
If a database uses the default of circular logging, you will not be able to take an online backup (SQL2413N Online backup is not allowed because the database is not recoverable or a backup pending condition is in effect)
To understand if a database is using circular logging, you can use:
$ db2 get db cfg for SAMPLE |grep LOGARCHMETH First log archive method (LOGARCHMETH1) = DISK:/db_arch_logs/SAMPLE/ Second log archive method (LOGARCHMETH2) = OFF
Replace ‘SAMPLE’ above with your database name. In the above example, the database is using archive logging and not circular logging. If both LOGARCHMETH1 and LOGARCHMETH2 are set to OFF, then the database is using circular logging, and online backups will not be possible.
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)
database/db2.1636581699.txt.gz · Last modified: 2021/11/10 23:01 by manu
Except where otherwise noted, content on this wiki is licensed under the following license: GNU Free Documentation License 1.3
