A Tale of Two Connections
It was the best of times, it was the worst of times, it was the age of wisdom, it was the age of foolishness, it was the epoch of belief, it was the epoch of incredulity, it was the season of Light, it was the season of Darkness, it was the spring of hope, it was the winter of despair, we had everything before us, we had nothing before us, we were all going direct to Heaven, we were all going direct the other way–in short, the period was so far like the present period, that some of its noisiest authorities insisted on its being received, for good or for evil, in the superlative degree of comparison only.
It’s amazing how well this famous Dickens quote describes the situation in this post. However, fortunately for you, I won’t deliver this blog entry in serial form over 26 weeks!
When in London…
A few weeks ago I was working in one of my customers’ development environments, and unwittingly issued a
db2 connect to prd statement to connect to a database – muscle memory at work. However, amazingly, after prompting me for a password, the connection was successful.
This customer’s standards state that applications should not connect across environments (i.e., development, QA, production). Although this rule is not enforced, the developers and DBAs do follow the rule, and do not allow applications in one environment to connect to databases in another environment.
You can imagine my shock, then, that the connection was successful. Immediately, I looked at the database directory to see whether someone had cataloged the
PRD database, but I found the following:
idbjorh@darnay (db2inst1): /home/idbjorh $ db2 list db directory System Database Directory Number of entries in the directory = 2 Database 1 entry: Database alias = DEV Database name = DEV Local database directory = /home/db2inst1 Database release level = 10.00 Comment = Directory entry type = Indirect Catalog database partition number = 0 Alternate server hostname = Alternate server port number = Database 2 entry: Database alias = DEV2 Database name = DEV2 Local database directory = /home/db2inst1 Database release level = 10.00 Comment = Directory entry type = Indirect Catalog database partition number = 0 Alternate server hostname = Alternate server port number =
Given these database catalog entries, I struggled trying to figure out how I could, using the CLP, connect to the
PRD database when it wasn’t cataloged.
Perhaps the catalog entry was cached? I tried issuing
db2 terminate (which terminates the DB2 backend process, eliminating any cached connection entries), but this didn’t prevent the connection.
Next, I logged completely out of the server and back in again –- but still I was able to connect to the
Meanwhile, in Paris…
Are you familiar with the
clpplus utility? It is the (relatively) new tool that IBM introduced in DB2 9.7 to provide users converting from Oracle a way to execute scripts that had been written for Oracle’s
sqlplus utility in DB2. It is an interesting tool, and has a number of very handy features when compared with the DB2 Command Line Processor (CLP) that make want to use it in lieu of the CLP on a daily basis. However, there are a few issues that keep me using the DB2 CLP:
- It doesn’t (directly) support many utilities like
IMPORTthat are supported in the DB2 CLP
- It’s written completely in Java and uses Type 4 JDBC connections, so it does not use or have access to the database catalog entries
- There is an annoying bug where clpplus sometimes drops characters you type and returns an error when you try to execute a query.
A side effect of the second issue is that you must use JDBC URLs to connect to each database. This is something of a pain, because every time you start
clpplus, you have to issue a command like:
Furthermore, because all connections now rely on TCP/IP, you can’t take advantage of the the fact that you can connect to a database without a username or password when you’re logged on to the DB2 server1.
Trying to love clpplus
I really wanted to use
clpplus, so after some digging I found that it is possible to work around the first and second issues described above.
For the first issue, you can use many of the unsupported utilities via the
For the second issue, clpplus does have the ability to read a
db2dsdriver.cfg file to find databases. The
db2dsdriver.cfg file is used by the DB2 Data Server Driver clients (that do not include the DB2 CLP) to define Data Source Names (DSNs) to DB2 databases. By setting up a
db2dsdriver.cfg file and setting the
DB2DSDRIVER_CFG_PATH environment variable, I could now shorthand to connect to a database when starting
I was still prompted for a password to connect, but at least I did not have to try to remember the hostname and port number for each database I wanted to connect to.
You can read more about using the Data Server Driver clients and setting up the
db2dsdriver.cfg file in a post I wrote for IDUG about using the various light clients that IBM provides.
Ultimately, though, I stopped using
clpplus on a regular basis due to the third issue mentioned above.
What I had not realized, when I set up the
db2dsdriver.cfg file in my experiments with
clpplus, is that the
db2dsdriver.cfg file is not used by the Data Server Driver client packages only! Even the DB2 CLP (which is part of the IBM Data Server Client – one of the packages that’s included when you install a DB2 server) can and will use this file to “look up” databases if they are not included in the database catalog.
In fact, the CLP looks in a number of locations besides the Database Catalog for a database when you issue a
CONNECT statement (shown in order of precedence):
- The connection string
- The database and node directories
In my case, the
DB2DSDRIVER_CFG_PATH environment variable was still set, and the
db2dsdriver.cfg file in that directory contained an entry for the
PRD database. Thus, when the CLP failed to find
PRD in the database directory, it looked in the
db2cli.ini file and then the
Oh, and please keep it “our little secret” that I had a production database defined in the
db2dsdriver.cfg file in the development environment.
– Ian Bjorhovde (@idbjorh)
Ian Bjorhovde is the principal consultant for DataProxy System Solutions in Phoenix, Arizona. He has worked in the IT industry for over 18 years and has spent the last 15 years as a consultant developing and supporting large DB2 LUW databases with clients in many different industries. He is an IBM Champion for Information Management, produces a DB2-focused podcast called The Whole Package Cache and has presented at RUG, IDUG and IBM conferences.
When you are logged directly into the DB2 server, you have already been authenticated by the operating system, so DB2 allows you connect to a database without providing a password. i.e.,
db2 connect to prd↩