Tag Archives: DB2

03.23.15
by

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.

 

09.18.14
by

Getting Around Documentation Errors With Connections Scripts

I’ve been meaning to write this blog for a while.  And by “a while” I mean since v4 of Connections.  IBM supply a series of scripts with the Connections install that are found in the install directory under the folder connnections.sql.  These scripts are used for a variety of things but most people will have to use them if migrating from an earlier version of Connections to a new one.  The scripts are under the database type folder for each application so the scripts for the Blogs database on DB is in

/connections.sql/blogs/db2

Now you can put those scripts where you want obviously, but that’s where you will find them.  In that folder there are lots of files that are basically a series of SQL commands written out for you.  Each command line terminates with a ; or a @ to identify that’s the end of the command.  When running these commands with db2 you use a different syntax depending on whether the SQL file ends each line in a ; or @.  For example

;  means our command line is written as “db2 - tvf {filename} >{writetoalogfile} “

@ means our command line is written as “db2 -td@ -vf {filename} >{writetologfile}”

Writing to a log file isn’t compulsory but I always do so I can check if the script ran OK.

The problem is that on the  IBM Documentation site they often give the wrong syntax for each database (oh and they aren’t consistent) so on this page the instructions for the profiles database are

“db2 -tvf predbxferxx.sql”

If you run that (and the clue is it takes less than a second which is suspicious) you will see no errors but if you check your log you will see a single line saying

“End of file reached while reading the command”

That basically means we used the wrong line terminator, we told it -tvf so it looked for a ; at the end of each line but if we open the predbxfer45.sql we can see each line ends in @.  If we change the command to

“db2 -td@ -vf predbxfer45.sql”

it runs perfectly.

It would be nice if the IBM documentation was correct but it’s a simple problem to catch and fix.