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 PRD database.

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:

1. It doesn’t (directly) support many utilities like LOAD or IMPORT that are supported in the DB2 CLP
2. 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
3. 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:

connect idbjorh@server.company.com:50000/PRD;

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 server¹.

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 ADMIN_CMD procedure.

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 clpplus:

connect idbjorh@PRD;

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.

Mystery, Solved

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):

1. The connection string
2. The database and node directories
3. The db2cli.ini file
4. The db2dsdriver.cfg file

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 db2dsdriver.cfg file.

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.