DB2 Basics: Administrative Commands

I had been in conversation with a respected DBA who is a well known speaker and author. I had mentioned how difficult it is to come up with advanced topics to write and speak about. That is when he responded “Advanced topics only narrow the audience”.

He is right. Ember’s top blog entry day in and day out is “How to catalog a database”. There is something in simplicity. Speak on something everyone would be interested in.

Inspired by a recent developerWorks article “IBM AIX Commands You Shouldn’t Leave Home Without“, I developed DB2 Commands 101.

Are you a new DB2 LUW DBA? A OS System Administrator covering for your DBA? Or just need a quick reference guide? This should help.

The commands below are common commands I use daily for basic administration tasks. It is not all inclusive and does not take into account a multi-partitioned environment, but this should be a good start.

Without further ado …

What version and fixpack of DB2?

db2level

What DB2 products are installed?

Must issue from the install directory (i.e. /opt/IBM/db2/V(VERSIONNO)/install)

db2ls -q -b -a

For Example:

db2ls -q -b /opt/IBM/db2/V10.5 -a

Starting DB2:

db2start

Stopping DB2:

All connections off, DBs are deactivated:

db2stop

DB’s active, connections incoming, but want to force off and stop DB2:

db2stop force

What databases are available?

db2 "LIST DATABASE DIRECTORY"

What databases are available and LOCAL to this machine?

db2 "LIST DATABASE DIRECTORY" | grep -p Indirect | grep alias
*Note: -p command works in AIX only. Otherwise use previous command and look for "Indirect"

How to catalog a database?

I am going to cheat and tell you to go here: How to Catalog a DB2 Database

What databases are active and online?

db2 "LIST ACTIVE DATABASES"

Activate a database:

db2 "ACTIVATE DATABASE dbname"

Deactivating a database:

(All connections must be removed first, see “force” command):

db2 "DEACTIVATE DATABASE dbname"

Force off all connections to a database:

db2 "FORCE APPLICATION ALL"

Force off specific connections to a database:

db2 "FORCE APPLICATION (pid)"
db2 "FORCE APPLICATION (pid, pid, pid)"

Where “pid” is the “Appl. Handle” in a “LIST APPLICATIONS” command.

How do I connect to a database?

If logged in as ID with CONNECT permission on the database, and local:

db2 "CONNECT TO (dbname)"

If not Instance ID or Remote:

db2 "CONNECT TO (dbname) USER (username)"

This will prompt for a password. If use use the “using” command with password it can be pulled up in command line recall.

Disconnecting from a database:

db2 "TERMINATE"

Database uptime:

db2pd -

Who is connected to the database?

db2 "LIST APPLICATIONS"

How many connections are there to the database?

db2 "LIST ACTIVE DATABASES" | grep connected

List of tablespaces and their state (verbose output):

db2 "LIST TABLESPACES SHOW DETAIL"

List of tablespaces:

Are they in normal state (abbreviated, Normal State is: 0x0000)?

db2 "LIST TABLESPACES SHOW DETAIL" | grep State

Grant the ability to connect to the database:

An Individual:

db2 "GRANT CONNECT ON DATABASE TO USER (username)"

An OS group:

db2 "GRANT CONNECT ON DATABASE TO GROUP (groupname)"

Grant authorization to do something to a table

(Select from it, delete from it, etc):

db2 "GRANT <SELECT, INSERT, UPDATE, or DELETE> ON (SCHEMA).(TABLENAME) TO (USER or GROUP) (username or groupname)"

SQL to see what is causing a lock wait:

db2 “SELECT AGENT_ID, LOCK_MODE, LOCK_OBJECT_TYPE, AGENT_ID_HOLDING_LK, LOCK_MODE_REQUESTED FROM SYSIBMADM.SNAPLOCKWAIT”

SQL for top 20 SQL by Number of Executions:

db2 “SELECT NUM_EXECUTIONS as EXECUTIONS, AVERAGE_EXECUTION_TIME_S as TIME_SECONDS, STMT_TEXT as TEXT FROM SYSIBMADM.TOP_DYNAMIC_SQL WHERE
(STMT_TEXT like ‘SELECT%’ or STMT_TEXT like ‘select%’) ORDER BY NUM_EXECUTIONS DESC FETCH FIRST 20 ROWS ONLY”

SQL for top 20 SQL by Execution Time:

db2 “SELECT AVERAGE_EXECUTION_TIME_S as TIME_SECONDS, NUM_EXECUTIONS as EXECUTIONS, STMT_TEXT as TEXT FROM SYSIBMADM.TOP_DYNAMIC_SQL WHERE
(STMT_TEXT like ‘SELECT%’ or STMT_TEXT like ‘select%’) ORDER BY AVERAGE_EXECUTION_TIME_S DESC FETCH FIRST 20 ROWS ONLY”

Using DB2’s internal monitoring and troubleshooting tool:

db2top -d

Useful options at menu: B Bottleneck; b Bufferpools; D Dyn. SQL; U Locks;

Look at how optimizer runs SQL (Explain Plan):

db2expln -d -f -g -z \; -o ORIG_EXPLAIN.out

Where file.sql is a txt file containing the problem SQL for analysis. The SQL ends with a semi-colon.

See if DB2 recommends any indexes (DB2 Advise):

db2advis -d -i | tee ORIG_ADVISE.out

Where file.sql is a txt file containing the problem SQL for analysis. The SQL ends with a semi-colon.

Backing up a database (Offline):

No connections, Database Deactivated:

db2 "BACKUP DATABASE (dbname) TO (/directory) "

Backing up a database (Online):

DB up and active with connections:

db2 "BACKUP DATABASE (dbname) ONLINE TO (/directory)"

See details on a backup file:

(Online or offline, how granular, compressed, log path, etc)

db2ckbkp -h

Processes in memory after a DB2STOP (That may need to be killed):

AIX:

 ipcs | grep db2

Linux (as Instance ID):

ipcs

Kill a process in memory after an IPCS:

ipcrm -<q, m, or s> PID

Where is the DB2 Error Log Held?

Default: 
(instancehome)/sqllib/db2dump/db2diag.log

To discover where error log is, if custom: 
db2 "GET DBM CFG" | grep DIAGPATH

How do I turn on Command Line Recall on AIX or LINUX:

set -o vi

Michael Krafick is an occasional contributor to db2commerce.com. He has been a production support DBA for over 12 years in data warehousing and highly transactional OLTP environments. He was acknowledged as a top ten session speaker for “10 Minute Triage” at the 2012 IDUG Technical Conference. Michael also has extensive experience in setting up monitoring configurations for DB2 Databases as well as preparing for high availability failover, backup, and recovery. He can be reached at “Michael.Krafick (at) icloud (dot) com”. Linked-in Profile: http://www.linkedin.com/in/michaelkrafick. Twitter: @mkrafick

Mike’s blog posts include:
10 Minute Triage: Assessing Problems Quickly (Part I)
10 Minute Triage: Assessing Problems Quickly (Part II)
Now, now you two play nice … DB2 and HACMP failover
Technical Conference – It’s a skill builder, not a trip to Vegas.
Why won’t you just die?! (Cleaning DB2 Process in Memory)
Attack of the Blob: Blobs in a Transaction Processing Environment
Automatic Storage Tablespaces (AST): Compare and Contrast to DMS
DB2 v10.1 Column Masking
Automatic Storage (AST) and DMS
Reloacting the Instance Home Directory
Informational Constraints: Benefits and Drawbacks
Options to Encrypt Data at Rest in DB2

• Author Details
• Twitter

Michael Krafick

https://www.youtube.com/discoverdb2

michael.krafick@gmail.com

Michael Krafick is an aspiring Zamboni driver and well known twig-eater. During the day he is a Sr. Database Engineer for a Fortune 500 company. He is a frequent contributor to datageek.blog, an IBM champion, member of the DB2 Technical Advisory Board, and IDUG speaker Hall of Fame inductee. With over 18 years experience in DB2 for LUW, Mike works hard to educate and mentor others.

My Tweets