JdV's Notes: DB2 Notes

Log In To DB2 As a Specific User

db2 CONNECT TO <database> USER <user ID> USING <password>

To bring a database out of backup pending mode

Ensure DB is offline
Run the following command:

db2dart <dbname> /CHST /WHAT DBBP OFF

Info from here:
http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/core/r0003477.htm

Create or Copy a Table

CREATE TABLE <schema>.<new table name> LIKE <schema>.<existing table name>
INSERT INTO <schema>.<new table name> SELECT * FROM <schema>.<existing table name>

Delete or Drop a Table

DROP TABLE <schema>.<table name>

Rename a Table

RENAME TABLE <schema>.<new table name> TO <new table name>

To see DB2 version

Run the following command at OS level:

db2level

Backups

To take an ad-hoc online backup

db2 "backup db <DBSID> online to /staging/area, /staging/area, /staging/area, /staging/area"

(The number of '/staging/area's governs the size of the dumped file.)

To take an ad-hoc offline backup

db2 "backup db <DBSID> to /staging/area, /staging/area, /staging/area, /staging/area"

(The DB2 default is to take offline backups. The number of '/staging/area's governs the size of the dumped file.)'

View DB2 backup history

To view the backup history in a decent, readable format:

db2 "select substr(comment,1,20) "TYPE", substr(start_time,7,2)||'/'||substr(start_time,5,2)||'/'||substr(start_time,1,4)||' '||substr(start_time,9,2)||':'||substr(start_time,11,2) "START", substr(end_time,7,2)||'/'||substr(end_time,5,2)||'/'||substr(end_time,1,4)||' '||substr(end_time,9,2)||':'||substr(end_time,11,2) "END", SQLCODE "ERROR" from sysibmadm.DB_history Where ENTRY_STATUS='A' and comment like 'DB2 BACKUP%' group by substr(comment,1,20), start_time, end_time, SQLCODE order by end_time desc"

To view all of the backup histories:

db2 list history BACKUP all for <SID>

To view all of the history since a certain date:

db2 list history BACKUP since 20130301 for <SID>

To view the archive log history since a certain date:

db2 list history archive log since 20130301 for <SID>

Provide DB2 diagnostic info to SAP

Refer to following SAP note 83819 - DB6: collect support data

db2support <work_dir> -d <dbsid> -f

This produces a file called db2support.zip in the 'work_dir'

Find Schema Names in a DB2 Database

db2 select schemaname from syscat.schemata

List Tables in a DB2 Database

db2 list tables for schema sapsr3db
or
db2 select tabname from syscat.tables where tabschema = ''

Check the validity/consistency of a DB2 archive log file

db2cklog check

(more info here)

Find database size

db2 "call get_dbsize_info(?,?,?,-1)"

Value of output parameters
--------------------------
Parameter Name : SNAPSHOTTIMESTAMP
Parameter Value : 2015-04-30-08.55.09.658199

Parameter Name : DATABASESIZE
Parameter Value : 1630123229184

Parameter Name : DATABASECAPACITY (total amount of disk space allocated)
Parameter Value : 4276539830272

Return Status = 0

To run a script file of DB2 commands

db2 -tvs <filename>.clp

Find DB2 startup time

db2 get snapshot for dbm | grep -i manager

Database Manager Snapshot
Database manager status = Active
Start Database Manager timestamp = 02/20/2015 18:13:27.587803
Remote connections to db manager = 111
Remote connections executing in db manager = 1
Local connections executing in db manager = 0

Restart db2vend process cleanly

db2pd -db <db sid> -fvp lam1 term

(Note: use 'lam1' for 'log archive method 1')