DB2 Basics: Administrative Commands

Krafick_Headshot

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

 


Krafick_HeadshotMichael 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

You may also like...

6 Responses

  1. Rafael Bottega says:

    Hi, congrats for your blog, it’s very usefull.
    I want to comment some commands (I work with DB2 LUW, then it can complement the post):

    What DB2 products are installed?
    db2licm -l
    It shows the name, version and licence of the product.

    What databases are available and LOCAL to this machine?
    db2 list db directory ON C:\
    It shows what databases are on C:\, cataloged or not.

    Force off all connections to a database:
    If you use “force applications all” it forces all connections in all databases. If you want to force only one database, I use the Quiesce command:
    first connect to the database
    db2 quiesce database immediate force connections
    db2 unquiesce database

    On Windows you can use the FINDSTR, this command is like the grep for Linux. Remember, It is case sensitive.
    db2 “LIST TABLESPACES SHOW DETAIL” | findstr State

    Finally, how you see the db2 errors:
    db2diag -time 2015-02-24 -level “Severe, Error” | db2diag -fmt “Time:%{ts} Message:@{msg}\n”
    It shows the last errors with level Severe and Error and compile it on the format “Time and Message” per line.

    Thank you.

  2. Shivraj says:

    Great article, Micheal !

    To add,

    db2 list tables for all
    and
    db2 list tables for schema

    are also handy commands for beginners if one wishes not to query catalog tables.

  3. harihara says:

    Hi,

    How to create a schema without any space. I had created a schema ‘hyd’ and when a letter is appended to schema names we see some space between schema name and letter. Is the below create schema command is correct ? Please advice,

    [db2inst1@db2winhari ~]$ db2 “create schema hyd authorization hari”
    DB20000I The SQL command completed successfully.
    [db2inst1@db2winhari ~]$ db2 “select schemaname concat ‘q’ from syscat.schemata”

    1
    ———————————————————————————————————————————
    DB2INST1q
    DELL q
    ERRORSCHEMAq
    HARI q
    HYD q
    NULLID q
    SCH1 q
    SCH10 q
    SCH11 q
    SCH12 q
    SCH13 q
    SCH14 q
    SCH15 q
    SCH2 q
    SCH3 q
    SCH4 q
    SCH5 q
    SCH6 q
    SCH7 q
    SCH8 q
    SCH9 q
    SQLJ q
    SYSCAT q
    SYSFUN q
    SYSIBM q
    SYSIBMADMq
    SYSIBMINTERNALq
    SYSIBMTSq
    SYSPROC q
    SYSPUBLICq
    SYSSTAT q
    SYSTOOLSq

    32 record(s) selected.

    • Ember Crooks says:

      Interesting. From your output, it looks like any schema of less than 8 characters has exactly one space appended to it. This is not something I’ve noted elsewhere or dealt with before. Sounds like a question for IBM support?

  1. February 24, 2015

    […] DB2 Basics: Administrative Commands […]

Leave a Reply

Your email address will not be published. Required fields are marked *