How to catalog a DB2 database

You may also like...

24 Responses

  1. harihara says:

    Can you please post the differences between normal database and partitioned database . I am aware about the installation part. Need to know the important concepts in dpf and also the commands. I referred IBM website but couldn’t find required info.
    Harihara jannu.

    • Ember Crooks says:

      Unfortunately, I haven’t worked with DPF in years – It is not appropriate for the e-commerce databases I work with. I’ll see if I can find someone to write a guest blog entry on it for me.

  2. Mihai says:

    Awesome instructions. Easier to follow than IBM’s online docs

  3. harihara says:

    Is it possible to list databases related to instance only – rather then cataloged db’s,
    as ‘db2 list database directory’ will list all the database including cataloged databases .

    • Ember Crooks says:

      For local databases, you’re looking for:

      Directory entry type                 = Indirect

      That means that on AIX, you can do:

      db2 list db directory |grep -p Indirect

      unfortunately the -p option for grep does not work on Linux.

      There’s no way that I know of listing only local databases through db2 commands alone, unless they all happen to have the same database home directory.

  4. Shan says:

    To catalog a new database or a database “that was in another server and migrated to new server” – Should we definitely shutdown, start and restart for it to work? my DBA says that has to be done for cache to clear???

    • Ember Crooks says:

      Restarting the app may be necessary depending on the application. DB2 itself and the operating system should not have to be restarted in most situations.

  5. iron says:

    Is it possible to catalog another database on same instance? I always have problems connecting to another cataloged database (even empty one) although i can easily connect to the first one. Any idea? (maybe i need to make new node for it? but it would get same parameters….confused)

    • Ember Crooks says:

      Yes, you can catalog two and even dozens of databases on the same instance. I’ve never had problems in this area. What errors do you see? I frequently have more than one database on the same node cataloged and different nodes as well.

  6. tiffany says:

    Thank you so much!! Worked like a charm, and so easy to follow !!!!!!!!!!!!

  7. Joye says:

    I like the procedure very much !! You are straight to the point with a fair enough description. Thank you very much!!

    May be there is one more thing you might need to add; these all commands are performed from the /home/inst-name/sqllib directory of the specific instance the person is using.

    • Ember Crooks says:

      If the db2profile from $INSTHOME/sqllib is properly sourced, these commands can be performed from any directory.

  8. Ajay says:

    Can I update a table in a remote Db2 instance from a local Db2/instance without requiring a federation server setup by using your example? Scenario:

    1. Machine1, port: 20121, Db2inst1
    2. Machine 2, port:20121, Db2inst2

    My app is connected to DB2inst1 on Machine1 but it may need to issue an update some tables in DB2inst2. I will never have a situation where I will be connected to Db2inst2 and need to update tables DB2inst1.

    • Ember Crooks says:

      You can, but you have to have a separate connection for each database, and you can only be connected to one database at a time. Without federation or replication, you have to do something like:
      1. connect to db1 db2inst1
      2. issue selects/updates, etc
      3. disconnect from db1 on db2inst1
      4. connect to db2 on db2inst2
      5. issue updates, etc
      6. disconnect from db2 on db2inst2
      7. connect to db1 on db2inst1 and continue processing

      If you need to do updates to db2 on db2inst2 while remaining connected to db1 on db2inst1, then you would have to use either federation or replication.

  9. Venkat says:

    Hi Ember,

    In which situation we need to catalog the DB. can u please explain clearly from APP SERVER to DB SERVER point of view.Do we must have client software in app server.

    • Ember Crooks says:

      If you are using type 2 jdbc drivers, and some other connection types then you’ll need the database cataloged. To catalog the database, you need the client software installed on the app server. Generally for JDBC type 4 drivers, you will not need the client software installed or the database cataloged. It depends heavily on your application code what is needed.

  10. PatricK says:

    Hi Ember,

    I want to uncatalog one db. So I execute:
    db2 uncatalog DATABASE MyDB
    db2 uncatalog node MyDB

    But nothing happend, no finish message like “DB20000I The UNCATALOG NODE command completed successfully.”
    If I execute:
    db2 list node directoy

    The entry is still there.
    Maybe you have a hint for me ?


  11. Praveen says:


    I need help establishing a connection between two servers to create nicknames. I have tried cataloging the node and database. You have shared the information on how to catalog but also need help creating the wrapper, server connection and user mapping. I have tried creating the nickname but got the error “Unexpected error code “42S22” received from data source “SERVERTEST”. Associated text and tokens are “func=”do_prep” msg=” SQL0206N “TBNAME” is not valid “.. SQLCODE=-1822, SQLSTATE=560BD, DRIVER=4.16.53”.
    Kindly help.

  1. August 6, 2014

    […] even non-DBAs are interested in them and I continue to rank highly in Google search results. My blog entry on how to catalog a DB2 database gets a ridiculous 50 page views per day, which is more than my whole blog got per day for the first […]

  2. December 23, 2014

    […] top ten most viewed articles this year (not necessarily posted this year) were: How to catalog a DB2 database How to Find the Size of a DB2 Database Explain Part 2 – Command Line Explain Plans Using db2exfmt […]

  3. February 24, 2015

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

Leave a Reply

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