DB2 Basics: Administrative Commands

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
    db2 list tables for schema

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

  3. harihara says:


    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”

    DELL q
    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

    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 *