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

1. CREATE TABLE <schema>.<new table name> LIKE <schema>.<existing table name>
2. 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')

Find more info on an error code

db2diag -rc <number including minus sign>

Find out if a database has more than one partition

db2pd -  -alldbpartitionnums

DB2 error ADM6017E

FUNCTION: DB2 UDB, buffer pool services, sqlbClnrAsyncWriteCompletion, probe:0
MESSAGE : ADM6017E  The table space "PSAPTEMPX" (ID "30") is full. Detected on
          container "/db2/SID/saptemp1/NODE0000/tempX/PSAPTEMPX.container000"
          (ID "0").  The underlying file system is full or the maximum allowed
          space usage for the file system has been reached. It is also possible
          that there are user limits in place with respect to maximum file size
          and these limits have been reached.

Refer to SAP note:
334608 - DB6: SMS tablespace ##(PSAPTEMP##) is FULL

Create an additional temporary tablespace with the same page size.

db2 list tablespaces show detail

Tablespace ID                        = 30
Name                                 = PSAPTEMPX
Type                                 = System managed space
Contents                             = Temporary data
State                                = 0x0000
   Detailed explanation:
     Normal
Total pages                          = 1
Useable pages                        = 1
Used pages                           = 1
Free pages                           = Not applicable
High water mark (pages)              = Not applicable
Page size (bytes)                    = 4096
Extent size (pages)                  = 8


db2 list tablespace containers for 30

            Tablespace Containers for Tablespace 30

Container ID                         = 0
Name                                 = C:\db2\SID\sapdatat
Type                                 = Path

Now create a new temporary tablespace on another disk with more freespace.
db2 "create temporary tablespace PSAPTEMPY PAGESIZE 4096 managed by system using ('D:\db2\SID\sapdatat')"

After the new temporary tablespace has been successfully created, you can delete tablespace PSAPTEMPX:

db2 "drop tablespace PSAPTEMPX"