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
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.
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.
Awesome instructions. Easier to follow than IBM’s online docs
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 .
For local databases, you’re looking for:
That means that on AIX, you can do:
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.
thanks ..
This command uses DB2 & Linux commands combined to list the local (Indirect) databases
db2 list db directory | grep -B 5 Indirect | grep alias
[…] 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 […]
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???
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.
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)
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.
[…] 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 […]
Thank you so much!! Worked like a charm, and so easy to follow !!!!!!!!!!!!
[…] I am going to cheat and tell you to go here: How to Catalog a DB2 Database […]
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.
If the db2profile from $INSTHOME/sqllib is properly sourced, these commands can be performed from any directory.
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.
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.
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.
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.
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
You need to run db2 terminate command after each of the uncatalog commands so that the directory gets updated.
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.
I don’t have a blog entry on federation yet, unfortunately.
Hi Ember,
Is there a way we can uncatalog all the odbc entries, nodes and database entries with 1-3 commands without having to uncatalog each of them?
We wanted to uncatalog all the old entries before importing the new ones. Please help
I don’t have an exact answer for you. If I were trying to find one, I’d try using db2cfimp, though I’m not sure that’s destructive of anything that’s there. I’d also be looking at using db2dsdriver.cfg instead of traditional cataloging.
Hi,
I followed all the instructions but still facing error “The database alias or name {} not found at the remote node”.
Steps done
(1) Windows 10 machine – Installed DB2 Client Version 11.1.5
(2) Registered the profile
(3) Ran the DB2 command to catalog the node and database
(4) Terminated the instance
(5) I can see the newly cataloged Db in the DB2 list DB Directory command
But connect to database is still throwing error. Anything still missing?
This error indicates that you are able to communicate with a db2 instance listening on the port specified, but it cannot find a database with the name you’ve specified. Do you have the database name correct? Are you connecting to the correct port for the database? There can be different instances on the remote server, listening on different ports.
Do you know where db2 client stores the catalog information on the client?
Specifically, after I execute the command db2 catalog tcpip node remote server where does the db2 command store that information on the client? what path and filename?
Thank you,
I’m not sure why you’d want to know. It is not generally human readable. But it is stored in sqldbdir in the sqllib directory, which is in the instance owner’s home directory. If you want something that you can actually catalog things by editing a text file, look instead at the db2 dsn driver methodology.
Hi Ember.
I connected remote database (catalog database).
Can i perform update insert delete etc query on catalog database where i catalog ?
Or Catalog database is read only ?
You should be able to perform any command you have the rights to, which may include both reading and writing. If your ID does not have permissions for something, then you wouldn’t be able to do those things.
Hi Ember,
I need to catalog alot of databases and odbc datasources on Windows, usually I write a cmd script with all catalog commands I need, but it tooks too long time.
Is there any other way to catalog a list of databases and datasources faster?
best regards.
Look to db2cfexp. You should be able to use it and db2cfimp to catalog them all once and then copy that to other servers. Not sure if it works for odbc.
very helpful this post