How to catalog a DB2 database

So I’m doing a lot of posts in the DB2 Basics area this week. That’s because they are quick for me to write, and they’re questions I get all the time. So bear with me if you read for the more detailed Commerce topics – I’m not abandoning them.

DB2 Client

DB2 databases can be cataloged on DB2 servers or DB2 clients. Before trying cataloging, make sure you at least have the DB2 client installed. DB2 Clients are free (no licensing required). All App servers should have DB2 clients already installed on them. Web servers should not. You can download DB2 Clients here: https://www-304.ibm.com/support/docview.wss?uid=swg27007053

Select the same version and FixPack as the servers you’re connecting to. Then select the proper OS. Finally, select “IBM Data Server Client” (9. versions) or “Administration Client” (8. versions). Installation and Instance creation are outside the scope of this post.

Make sure you have all the inputs

You need:

  • REMOTE: Short host name with proper entry in hosts file OR Fully qualified host name OR IP address (that is the order of preference if you have all)
  • SERVER: Port number the db2 server is listening on. To get this, log into db2 server as the db2instance owner (frequently db2inst1), and do:
$ db2 get dbm cfg |grep SVCENAME
TCP/IP Service name                          (SVCENAME) = db2c_db2inst1
$ cat /etc/services |grep db2c_db2inst1
db2c_db2inst1   50001/tcp
  • NODENAME: This is a name that you make up.  You might want to develop a standard for your company and stick to it.

Catalog the node

The general form of the syntax for doing this is:

db2 catalog tcpip node <NODENAME> remote <REMOTE> server <PORT>

For example:

Logged in (su – is fine) as the db2 instance owner or a privileged user:

$ db2 catalog tcpip node prod_ecom remote servername.domain.com server 50001

Catalog the database

The general form of the syntax for doing this is:

db2 catalog database <database_name> at node <node_name>

For example:

Logged in (su – is fine) as the db2 instance owner or a privileged user:

db2 catalog database wcs_prod at node prod_ecom

Refresh directory

After cataloging, do a db2 terminate to ensure everything shows up

$ db2 terminate
DB20000I  The TERMINATE command completed successfully.

Always test a connection!!

It is very important to test the connection because a typo could cause it not to work. Or there could be network issues preventing connection.
The general form of the syntax for doing this is:

db2 connect to <database_name> user <user_name> (you will then be prompted for a password)

For example:

Logged in (su – is fine) as the db2 instance owner or a privileged user:

db2 connect to wcs_prod user ecrooks
Enter current password for ecrooks:

Database Connection Information

Database server        = DB2/LINUX 8.2.9 
SQL authorization ID   = ECROOKS 
Local database alias   = WCS_PROD

You may also like...

24 Responses

  1. harihara says:

    Hi,
    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.
    Thanks,
    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:

    Hi,
    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 ?

    Greats
    Patrick

  11. Praveen says:

    Hi,

    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 *