Adding DB2 Datasources Using db2cli

I recently blogged about Advanced Query Tool which I have started using on my VMs instead of one of the heavyweight IBM DB2 client tools.  AQT is a lightweight client that allows me to examine the databases easily but it works by reading in the datasources defined in Windows in the ODBC Manager.  To create datasources you must have some kind of DB2 client or driver installed.  I installed the minimal drivers which gives me this

Screen Shot 2015-03-23 at 11.18.05

Selecting ‘Configure DB2 .Net Data Provider” calls up a GUI interface that walks you through setting up a datasource.  To find the ODBC Manager under Windows 7 or 8 go to Control Panel and click on System and Security then click on Administrative Tools.

Each driver is configured to connect to a specific server and port.  The configuration is stored in db2cli.ini and db2dsdriver.cfg.  But what if you want to change the server or port address for a datasource.  Or in my case, add multiple datasources pointing to different servers?  Well you could try running the GUI tool again but that decision was made for me when Windows 7 decided to stop running the file and I decided I preferred to have more visibility / control of the configuration.  So instead I chose to use the command line to change and create new datasources.

Open an administrative command prompt and go to the DB2 client install directory (in my case c:\IBM\SQLLIB) and run

db2cli writecfg add -dsn PEOPLEDB_SA -database PEOPLEDB -host db2.turtlehost.net -port 50000

That writes a new datasource called PEOPLEDB_SA (you can call it what you want so long as it’s unique) that is connecting to a database called PEOPLEDB on server db2.turtlehost.net port 50000.  You don’t need to be able to connect to the database when you run the db2cli - it doesn’t validate or test at this point.

Now we need to add this newly created DSN to the Windows registry by running

db2cli registerdsn -add -alldsn

When you’re all done you need to restart Windows for the new registry to be read by AQT and you can go ahead and test the connection and use AQT to connect to the database by choosing the PEOPLE_SA datasource.