Configuration of DB2 CLI/ODBC Driver

IBM provides a CLI/ODBC driver for DB2 which is a light-weight shared library providing a basic ODBC API for accessing DB2. The driver is delivered in its binary form with shared libraries and header files, and it is available from IBM’s website. I recently downloaded the following version: ibm_data_server_driver_for_odbc_cli_linuxx64_v11.1.tar.gz. This post will cover setting up the driver for use with C++ programs. The SAMPLE database provided with DB2 will be used for testing out the connectivity.

DB2 installs in /opt/ibm on Linux, so I will install the driver to the same location and give ownership to the db2inst1 user which owns the instance of DB2 that is running on my system.

$   gzip -d ibm_data_server_driver_for_odbc_cli_linuxx64_v11.1.tar.gz
$   tar -xf ibm_data_server_driver_for_odbc_cli_linuxx64_v11.1.tar
$   sudo mv clidriver /opt/ibm/
$   sudo chown -R db2inst1 /opt/ibm/clidriver
$   sudo chgrp -R db2iadm1 /opt/ibm/clidriver

For each database that will be accessed via the CLI/ODBC library, a configuration for the libraries is required in the db2cli.ini file in the /opt/ibm/clidriver/cfg directory. Use the sample provided by IBM in the cfg directory to create the configuration.

$   cd /opt/ibm/clidriver/cfg
$   cp db2cli.ini.sample db2cli.ini
$   vim db2cli.ini

For example, to access the SAMPLE database provided with DB2, add the following configuration to db2cli.ini:

[SAMPLE]
Database=SAMPLE
Protocol=TCPIP
Port=50000
uid=db2inst1
pwd=db2inst1

If this is not done, there will be errors when calling SQLConnect() in C++ code later. Also make sure to copy db2cli.cfg to the instance user’s cfg directory.

$   su db2inst1
$   cp /opt/ibm/clidriver/cfg/db2cli.ini ~/sqllib/cfg/db2cli.ini

DB2 also provides a utility to validate the configuration file: db2cli with the validate option. Use this to make sure that db2cli is using the right configuration file, and that the tool can read the configuration for the SAMPLE database.

$   cd ~/sqllib/bin
$   ./db2cli validate -dsn SAMPLE

===============================================================================
Client information for the current copy:
===============================================================================

Client Package Type : IBM DB2 Express-C
Client Version (level/bit): DB2 v11.1.1.1 (s1612051900/64-bit)
Client Platform : Linux/X8664
Install/Instance Path : /opt/ibm/db2/V11.1_01
DB2DSDRIVER_CFG_PATH value: <not-set>
db2dsdriver.cfg Path : /home/db2inst1/sqllib/cfg/db2dsdriver.cfg
DB2CLIINIPATH value : <not-set>
db2cli.ini Path : /home/db2inst1/sqllib/cfg/db2cli.ini
db2diag.log Path : /home/db2inst1/sqllib/db2dump/db2diag.log

===============================================================================
db2dsdriver.cfg schema validation for the entire file:
===============================================================================

Note: The validation utility could not find the configuration file
db2dsdriver.cfg. The file is searched at
“/home/db2inst1/sqllib/cfg/db2dsdriver.cfg”.

===============================================================================
db2cli.ini validation for data source name “SAMPLE”:
===============================================================================

[ Keywords used for the connection ]

Keyword Value
—————————————————————————
DATABASE SAMPLE
PROTOCOL TCPIP
PORT 50000
UID db2inst1
PWD ********

===============================================================================
db2dsdriver.cfg validation for data source name “SAMPLE”:
===============================================================================

Note: The validation utility could not find the configuration file
db2dsdriver.cfg. The file is searched at
“/home/db2inst1/sqllib/cfg/db2dsdriver.cfg”.

===============================================================================
The validation is completed.
===============================================================================

Now it is possible to test out connection to the SAMPLE database via CLI/ODBC using the same tool. [Kudos to Easysoft for this tip!]

$   cd ~/sqllib/bin
$   db2 activate SAMPLE                  # set the active DB in the DB2 instance
$   echo “SELECT * from staff” | ./db2cli execsql -dsn SAMPLE
IBM DATABASE 2 Interactive CLI Sample Program
(C) COPYRIGHT International Business Machines Corp. 1993,1996
All Rights Reserved
Licensed Materials – Property of IBM
US Government Users Restricted Rights – Use, duplication or
disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
> SELECT * from staff
FetchAll: Columns: 7
ID NAME DEPT JOB YEARS SALARY COMM
10, Sanders, 20, Mgr , 7, 98357.50, –
20, Pernal, 20, Sales, 8, 78171.25, 612.45
30, Marenghi, 38, Mgr , 5, 77506.75, –
40, O’Brien, 38, Sales, 6, 78006.00, 846.55
50, Hanes, 15, Mgr , 10, 80659.80, –
60, Quigley, 38, Sales, -, 66808.30, 650.25
70, Rothman, 15, Sales, 7, 76502.83, 1152.00
80, James, 20, Clerk, -, 43504.60, 128.20
90, Koonitz, 42, Sales, 6, 38001.75, 1386.70
100, Plotz, 42, Mgr , 7, 78352.80, –
110, Ngan, 15, Clerk, 5, 42508.20, 206.60
120, Naughton, 38, Clerk, -, 42954.75, 180.00
130, Yamaguchi, 42, Clerk, 6, 40505.90, 75.60
140, Fraye, 51, Mgr , 6, 91150.00, –
150, Williams, 51, Sales, 6, 79456.50, 637.65
160, Molinare, 10, Mgr , 7, 82959.20, –
170, Kermisch, 15, Clerk, 4, 42258.50, 110.10
180, Abrahams, 38, Clerk, 3, 37009.75, 236.50
190, Sneider, 20, Clerk, 8, 34252.75, 126.50
200, Scoutten, 42, Clerk, -, 41508.60, 84.20
210, Lu, 10, Mgr , 10, 90010.00, –
220, Smith, 51, Sales, 7, 87654.50, 992.80
230, Lundquist, 51, Clerk, 3, 83369.80, 189.65
240, Daniels, 10, Mgr , 5, 79260.25, –
250, Wheeler, 51, Clerk, 6, 74460.00, 513.30
260, Jones, 10, Mgr , 12, 81234.00, –
270, Lea, 66, Mgr , 9, 88555.50, –
280, Wilson, 66, Sales, 9, 78674.50, 811.50
290, Quill, 84, Mgr , 10, 89818.00, –
300, Davis, 84, Sales, 5, 65454.50, 806.10
310, Graham, 66, Sales, 13, 71000.00, 200.30
320, Gonzales, 66, Sales, 4, 76858.20, 844.00
330, Burke, 66, Clerk, 1, 49988.00, 55.50
340, Edwards, 84, Sales, 7, 67844.00, 1285.00
350, Gafney, 84, Clerk, 5, 43030.50, 188.00
FetchAll: 35 rows fetched.
>

The connection is looking good!  Finally, when building C++ software that uses the driver, be sure to link to add the following compile and link options to g++ or clang++:

-I/opt/ibm/clidriver/include
-L/opt/ibm/clidriver/lib
-ldb2

The relevant include file is “sqlcli1.h” and the primary library of interest is libdb2.so.1.

 

 

Advertisements

Installing DB2 on Debian/Ubuntu

IBM’s DB2 product is delivered as a binary installer for the Linux platform in a tarball format. It would be nice if IBM produced rpm or deb packages for distributing DB2, but I will complain no further. The installer has gotten *much better* since when I first battled with installing DB2 on Linux back in 2006. After downloading the binary, unpack it and kick it off!

root#   mkdir -p /opt/ibm/db2expc_installer
root#   cp v11.1_linuxx64_expc.tar.gz /opt/ibm/db2expc_installer
root#   cd /opt/ibm/db2expc_installer
root#   tar -xfz v11.1_linuxx64_expc.tar.gz
root#   cd expc
root#   ./db2_installer

This failed of course, no binary delivered in this form ever works out of the box. This was good though, as I really wanted to run db2_setup instead of the installer. The setup program is a Java GUI that makes it very simple to go through the configuration, including creating user accounts required by the DB2 RDBMS.

 Requirement not matched for DB2 database “Server” . Version: “11.1.1.1”.

Summary of prerequisites that are not met on the current system:

DBT3514W The db2prereqcheck utility failed to find the following 32-bit library file: “/lib/i386-linux-gnu/libpam.so*”.
DBT3520E The db2prereqcheck utility could not find the library file libaio.so.1.
DBT3514W The db2prereqcheck utility failed to find the following 32-bit library file: “libstdc++.so.6”.

Ugh…32-bit binaries required.

root#   dpkg –add-architecture i386
root#   apt-get update
root#   apt-get install libaio1 libstdc++6:i386 libpam0g:i386

Now it is possible to run the installer, this time with the GUI to make the job easy.

root#  ./db2setup

DBI1190I  db2setup is preparing the DB2 Setup wizard which will guide
you through th6:ie program setup process. Please wait.

The GUI setup program holds one’s hand the whole way, allowing one to setup db2inst1 and db2fenc1 users. After the installation is complete, I ran visudo to add my user account to the sudoers file for DB2 management:

bryan ALL=(db2inst1) /opt/ibm/db2/V11.1_01/bin/*
bryan ALL=(db2fenc1) /opt/ibn/db2/V11.1_01/bin/*

Finally, I usually like to create the SAMPLE database so that I can test out SQL commands and other IBM tools on a test database that actually has a good amount of data and tables.

db2inst1$   ./db2sampl

Creating database “SAMPLE”…
Connecting to database “SAMPLE”…
Creating tables and data in schema “DB2INST1″…
Creating tables with XML columns and XML data in schema “DB2INST1″…

‘db2sampl’ processing complete.

Now to install the CLI/ODBC library for C++ programs, and maybe IBM Data Studio…

 

 

FreeBSD Major/Minor Version Upgrades

Last year at some point I installed FreeBSD 10.2-RELEASE on an old i686 computer. In the time between now and then, there was a 10.3 minor release as well as an 11.0 major release. I am not in a hurry to get to FreeBSD 11.0-RELEASE yet, but I thought it was time to make the minor version upgrade to 10.3-RELEASE. Fortunately, upgrading FreeBSD is an easy process.

The current version of FreeBSD installed on the machine is shown below.

root@bsdbox:~ # uname -a
FreeBSD bsdbox 10.2-RELEASE FreeBSD 10.2-RELEASE #0 r286666: Wed Aug 12 19:31:38 UTC 2015 root@releng1.nyi.freebsd.org:/usr/obj/usr/src/sys/GENERIC i386

To upgrade to the next minor version, use the freebsd-update command with the upgrade option and the release version.

root@bsdbox:~ # freebsd-update -r 10.3-RELEASE upgrade

Depending on your system, this command could take a while to run while it inspects the system. On this i686 machine, I just let the command run and came back after an hour or so to check on its progress.

Looking up update.FreeBSD.org mirrors… 4 mirrors found.
Fetching metadata signature for 10.2-RELEASE from update6.freebsd.org… done.
Fetching metadata index… done.
Fetching 1 metadata files… done.
Inspecting system…

The following components of FreeBSD seem to be installed:
kernel/generic src/src world/base

The following components of FreeBSD do not seem to be installed:
world/doc world/games

Does this look reasonable (y/n)?

After inspecting the system, the upgrade process is just making sure I am happy with the parts that will be upgraded. Enter “y” to continue, and then if you have an i686 machine, walk away and give it some time to fetch the updates.

Fetching metadata signature for 10.3-RELEASE from update6.freebsd.org… done.
Fetching metadata index… done.
Fetching 1 metadata patches. done.
Applying metadata patches… done.
Fetching 1 metadata files… done.
Inspecting system… done.
Fetching files from 10.2-RELEASE for merging… done.
Preparing to download files…
…(output truncated for the sake of sanity)…
590….39600….39610….39620….39630….39640….39650….39660….39670….39680….39690….39700….39710….39720….39730….39740….39750….39760….39770….39780….39790….39800….39810….39820….39830….39840….39850….39860….39870….39880….39890….39900….39910….39920….39930….39940….39950….39960….39970….39980….39990….40000….40010….40020….40030 done.
Applying patches… done.
Fetching 873 files… done.
Attempting to automatically merge changes in files… done.

The following file could not be merged automatically: /etc/ssh/ssh_config
Press Enter to edit this file in vi and resolve the conflicts
manually…

I had made some modifications to /etc/ssh/ssh_config on my baseline system, so rather than just overwriting the file, FreeBSD is offering me the opportunity to look at a diff of my original and the new version that FreeBSD needs to install. Luckily for me, I had just added one line, and I no longer needed it, so I edit out the changes and let FreeBSD move onward. Once you save the changes and exit out of the editor, the upgrade process continues.

…(truncated)…
/var/db/etcupdate/current/root/.k5login
/var/db/etcupdate/current/root/.login
/var/db/etcupdate/current/root/.profile
/var/db/etcupdate/log
/var/db/mergemaster.mtree
/var/yp/Makefile.dist
To install the downloaded upgrades, run “/usr/sbin/freebsd-update install”.

Excellent! The updates have been downloaded, my system has been examined, and all of the necessary modifications have been merged into the software to install. To initiate the actual install:

root@bsdbox:~ # freebsd-update install
Installing updates…
Kernel updates have been installed. Please reboot and run
“/usr/sbin/freebsd-update install” again to finish installing updates.

At this point, the system is ready to be rolled over to the next version of FreeBSD. Reboot the system:

root@bsdbox:~ # shutdown -r +1

Once the system comes back up, check out the version:

root@bsdbox:~ # uname -a
FreeBSD bsdbox 10.3-RELEASE-p11 FreeBSD 10.3-RELEASE-p11 #0: Mon Oct 24 18:47:18 UTC 2016 root@amd64-builder.daemonology.net:/usr/obj/usr/src/sys/GENERIC i386

Nice! To finalize the install, run the freebsd-update command with the install option as directed in the output before the last system reboot.

root@bsdbox:~ # freebsd-update install

That is it, the system is now upgraded from FreeBSD 10.2 to 10.3! An upgrade to a major version is no different from the above, just specify the proper version. For example, to upgrade to FreeBSD 11.0:

root@bsdbox:~ # freebsd-update -r 11.0-RELEASE upgrade

Easy!

FreeBSD and Iomega ZIP Drives

Iomega ZIP drives and disks are dead technology, but I have an old box from 1999 that has a fully functioning 100MB ZIP drive. Out of the box, FreeBSD supports these devices through the SCSI subsystem. During the boot process, FreeBSD recognizes the drive as /dev/da0.

da0 at ata0 bus 0 scbus0 target 1 lun 0
da0: <IOMEGA ZIP 100 12.A> Removable Direct Access SCSI device
da0: 11.100MB/s transfers (PIO3, ATAPI 12bytes, PIO 65534bytes)
da0: Attempt to query device size failed: NOT READY, Medium not present

The last line is not a critical failure, it just means that there is no ZIP disk inserted in the drive. Rebooting the machine with a ZIP disk inserted results in the following output:

da0 at ata0 bus 0 scbus0 target 1 lun 0
da0: <IOMEGA ZIP 100 12.A> Removable Direct Access SCSI device
da0: 11.100MB/s transfers (PIO3, ATAPI 12bytes, PIO 65534bytes)
da0: 96MB (196608 512 byte sectors: 255H 255S/T 3C)

The last line shows that the ZIP disk media was recognized, and the number of 512-byte blocks is listed. A disk can be inserted at any time, and interestingly enough, when a disk is inserted into the driver, there are no messages in /var/log/messages indicating which partition the media is listed as. Running a listing on the devices in /dev can reveal some hints though.

root@bsdbox:/media # ls /dev
acpi consolectl kbd0 pci ttyv4
….
… da0 …
… da0s4 …

The drive represents the ZIP media on /dev/da0s4. According to older documentation from FreeBSD 6, slice 4 is where the device driver places the media partition.  To access the ZIP disk, first create a mount point on the filesystem, such as /media/zipdisk, and then mount the device using the mount(8). The zip disks I have are FAT formatted, so I need to use the “-t msdosfs” option.

root@bsdbox:/meda # mkdir /media/zipdisk
root@bsdbox:/meda # mount -t msdosfs /dev/da0s4 /media/zipdisk

This disk is now available on /media/zipdisk for reading and writing.  When finished with the disk, before physically ejecting it, be sure to unmount the filesystem first.

root@bsdbox:/meda # umount /media/zipdisk

Now it is safe to press the disk eject button on the drive. Of course for frequent use of a ZIP drive, entries should be added to /etc/fstab to make things easier. For infrequent use, however, the above methodology should work just fine!

Iomega ZIP drives!

I still have my old computer from back when I started at university in 1999! I keep thinking it is time to get rid off the machine, but then again it working just fine.  The only problems with it are that the CD-RW drive is broken and that the Pentium-III consumes a lot of power compared to modern machines. It is a great machine for FreeBSD, however, since all of the hardware is supported by FreeBSD now: wireless NIC, NVidia AGI card, etc.  It also has physical serial ports–two of them!  Last year I installed FreeBSD 10.2, and it has been powered down since. I just got too busy with school work. This morning I came across my stack of Iomega ZIP disks and thought I should see if I still have any data on them. Would my ZIP drive still function after all of this time?

The Iomega ZIP drive was a bid deal for me personally. All of the workstations in the university’s computer science and computer engineering labs had ZIP drives because often our work would not fit on a regular floppy disk. And at this time, there were no USB thumbdrives! I would frequently work in the lab, saving Visual C++ 6.0 projects  or Altera Max-Plus II FPGA projects to my zip disks. I used to carry two around just in case I filled up one disk. I would then continue working at home, taking advantage of the quieter environments to fix my C++ or VHDL code issues. The next day I would take the work I’d accomplished at home and move forward.

Looking back it was really a bit unnecessary though. The school should have prepared a better remote working environment.  The computer science department’s UNIX (Solaris 7 and 8!) network allowed for remote access, so for many of my computer science projects I just worked remotely via ssh on the Solaris machines. But the engineering department didn’t have any remote access capabilities, so I could not use FTP or SCP to transfer work between the engineering network and home. So thus it was the trusty Iomega ZIP disk technology that made my life just that much easier.

When you read about ZIP drives on the Internet, there are lots of complaints about the “click of death” and how unreliable the drives were. I must have been very lucky I suppose. I never had an issue with ZIP drives, and they were extremely reliable. I suppose that the issues was worse for external ZIP drives rather than internal drives. I used zip drives nearly day in and day out for 4 years. Amazingly, the Iomega ZIP drive in my old machine is still working today!

I went through all eight of my disks, and it was a worthwhile experience as I found some old documents and photos. Most of the disks were empty, but one had a bunch of academic papers I had been reading about computational electromagnetics, and another disk had many cover letters and resumes I had written back when I was trying to find a job. I also had some photos from my college days. I got rid of most of the data there, but decided to keep the photos.

In 2003 I purchased a 128 MB USB thumbdrive, and from that time forward I quite using the ZIP drives. The technology now is long dead, and rightly so, USB-based storage is clearly the way to go. But I will alwasy fondly recall the Iomega ZIP disk, much like those before me have memories of 5.25-inch floppy disks. I am going to hold on to these disks for awhile longer. I may keep it around to play around with, but that is about it. After all, I have no where else to transfer the disks to!

FreeBSD and USB Thumbdrives

USB thumbdrives are a convenient way of moving relatively small volumes of data around machines. FreeBSD fully supports such devices. Insert the thumbdrive into an open USB port and check to make sure FreeBSD detects its using the command below.

# dmesg | tail

USB drives are handled by the SCSI subsystem so look for output that would resemble the following:

ugen0.3: <vendor 0x0d7d> at usbus0
umass0: <vendor 0x0d7d USB DISK 2.0, class 0/0, rev 2.00/0.50, addr 3> on usbus0
umass0:  SCSI over Bulk-Only; quirks = 0xc180
umass0:2:0:-1: Attached to scbus2
da1 at umass-sim0 bus 0 scbus2 target 0 lun 0
da1: < USB DISK 2.0 1.16> Removable Direct Access SCSI device
da1: Serial Number 073A0C251C0B
da1: 1.000MB/s transfers
da1: 124MB (253952 512 byte sectors: 64H 32S/T 124C)
da1: quirks=0x3<NO_SYNC_CACHE,NO_6_BYTE>

From the output above, we know the device is /dev/da1.  You can also query the SCSI system to see if the device is found. Note this output also shows “da1”.

root@bsdbox:~ # camcontrol devlist
<WDC WD400EB-00CPF0 06.04G06>      at scbus0 target 0 lun 0 (pass0,ada0)
<IOMEGA ZIP 100 12.A>              at scbus0 target 1 lun 0 (pass1,da0)
<LG CD-ROM CRD-8400B 1.03>         at scbus1 target 0 lun 0 (cd0,pass2)
<SONY CD-RW  CRX220E1 6YS1>        at scbus1 target 1 lun 0 (cd1,pass3)
< USB DISK 2.0 1.16>               at scbus2 target 0 lun 0 (da1,pass4)
Trying to mount just /dev/da1 is not going to succeed. We have to tell FreeBSD which partition to mount. Luckily, for most USB thumbdrives without any encryption (such as IronKeys), there is just one partition on the drive. As such, we can specify /dev/da1s1 to get slice #1, or the first partition. To mount the partition manually, we can use the mount(8) command while specifying the filesystem on the device. The USB thumbdrive I have is FAT formatted, so the “-t msdosfs” option to the mount command will tell FreeBSD to override the default format of UFS. Before running the mount command, however, make sure you have a place to mount the filesystem. I happened to choose /media/usb.
root@bsdbox:~ # mkdir /media/usb
root@bsdbox:~ # mount -t msdosfs /dev/da1s1 /media/usb
The mount command should complete silently, and you should be able to access /media/usb and copy files to and from the device. When you have finished with the device and want to remove it, use the umount command.
root@bsdbox:~ # umount /media/usb
It is safe to remove the device now without potentially damaging the filesystem on the device. After physically unplugging the device from the USB port, if you go back and look at the messages on the system (dmesg | tail), you will see something like the following:
ugen0.3: <vendor 0x0d7d> at usbus0 (disconnected)
umass0: at uhub0, port 2, addr 3 (disconnected)
da1 at umass-sim0 bus 0 scbus2 target 0 lun 0
da1: < USB DISK 2.0 1.16> s/n 073A0C251C0B detached
(da1:umass-sim0:0:0:0): Periph destroyed