I have unfortunately been
blessed cursed into using DB2 for all our server database task over SQL Server. If you have spent any time reading this blog, you have seen other posts related to DB2.
For example, some prior posts:
Using Powershell to access DB2
Connecting to a DB2 database using C#
The truth of the matter is, DB2 is not going away. So I’m stuck and using it having to face the fact that we have over 40+ DB2 servers, makes managing these servers a PITA. Unlike SQL Server, where I can just bring up SQL Management Studio and add a remote server and I’m done, DB2 makes this less than trivial. I’m not sure why it’s the way that is is; other than trying to support an array of non-Windows systems.
Due to firewall rules, most of our servers cannot be accessed outside of their subnet for security reasons. Having to log into each server’s console, launch DB2 Control Center, browse out to each database, and perform whatever task needs to be done is very tedious, to say the least.
Fortunately, there is a slightly better way. This involved either using another DB2 server or installing the DB2 Server Tools to another server and use it as your centralized access point into your other DB2 servers.
In my case, I have a standard Windows 2008 R2 server with the DB2 Server Tools installed. There is no DB2 server installed already, which requires me to edit the c:\windows\system32\drivers\etc\services file and adding a line so the rest of the wizard, explained later, will work.
As shown below, add a “db2c_DB2” line with port “50000/tcp” defined.
Since I do not have DB2 installed locally, the wizard for some reason still expects to find this entry in the file to know what port to talk to the remote server over. Lame.
Next, launch the DB2 “Configuration Assistant” from the DB2COPY –> Set-up Tools Program group. I’m using DB2 9.5 and I believe earlier versions you could launch this tool directly from DB2 Control Center’s Tools menu. YMMV.
Once the program is launched, you can right-click in the grid and select “Add database wizard”. You will then be prompted through a series of questions –
Select “Manually configure a connection to a database” and then click “Next”:
Then select “TCP/IP” and then click “Next”:
Then enter your FQDN for your “Host name”. “Service name” must match the name you placed into your “services” file at the start of this tutorial. It must match. If it does not match, when you click the “Retrieve” button, you will probably get a Java error. It took a few times to figure this out before I wised-up. Click “Retrieve” button and 50000 port should populate automatically.
Finally click “Next”:
Under “Database name” enter the database name on the remote system that you want to access. Under “Database alias” enter either the same name or a similar name. Then click “Next”:
Then select “Register this database for CLI/DOBC”. Ensure “As system data source” is selected. Then for “Data source name” enter your database name again. Select “Next”:
From this screen, for “Operating system” select “Windows” from the dropdown. Then click “Next”:
Then for “System name” enter your server’s NETBIOS name. For host name, ensure the FQDN of the server is shown. Then click “Next”:
Finally, last step, ensure that the “Use authentication value in server’s DBM Configuration” is selected. Click “Finish”:
There is a test dialogue that will appear. You can click the “Test” button and enter the remote server’s credentials to DB2 to verify that your setup works. If you have additional databases on the same remote server, you can click the “Add” button to quickly add additional databases.
If you were to return back to DB2 “Control Center” and refresh, you should see each of your remote databases listed under “All Databases”. Example below:
I haven’t used Control Center enough when accessing the remote databases to determine whether its significantly faster than getting onto the old server the way I have been; as it seems to take Control Center 60+ seconds to connect and list all the database objects. I’m hoping this is a one-time event and subsequent connections are much faster. I really do miss using SQL Server!!!
Until next time, hope this helps!