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.

 

 

GNU C++ and Gold Linker

The Gold linker (ld.gold) is a ELF linker developed at Google and added to the binutils toolset that can be used in place of standard linker (ld) from binutils.  The Gold linker offers faster object linking times for C++ programs, which is particularly attractive to large C++ code bases. Please follow this link (Linux Foundation) for an interesting read on how it works.

Most Linux platforms will default to using the standard linker, so below are a few options for overriding and using the ld.gold linker:

  1.  Use the -fuse flag on gcc/g++:  g++ -fuse-ld=gold test.cpp
  2. Setting “export LD=ld.gold” so that the $LD environment variable uses ld.gold rather than ld (this is useful for projects using makefiles)
  3. As the super user, running binutils-config –linker ld.gold to change the default

Easy CRC checksums with Boost

The Boost CRC library makes it painless to compute the CRC checksum on data.  For HDL designers, the library can be useful in generating test vectors for use in testbenches for CRC modules.  The Boost documentation provides a complicated example program that shows how to use the library, but I stripped it down to create an even simpler example of how to use do a CCITT CRC-16 checksum.

#include <boost/crc.hpp>
#include <boost/integer.hpp>
#include <cassert>

int main(int argc, char *argv[])
{
  unsigned char const test_data[] = { 0x31, 0x32, 0x33, 0x34, 0x35, 0x36, 0x37, 0x38, 0x39 };
  std::size_t const test_data_len = sizeof(test_data) /   sizeof(test_data[0]);
  boost::uint16_t const expected_result = 0x29B1;

  boost::crc_ccitt_type checksum_agent;
  checksum_agent.process_bytes(test_data, test_data_len);

  assert(checksum_agent.checksum() == expected_result);
  return 0;
}

Using C++0x features with g++

With the announcement recently that the C++0x spec has finally been finalized–I suppose it should be called C++11 at this point–I will now try to learn more of the new features that will be available.  I happy that much of what I currently use in Boost will become standardized (such as shared_ptr, threads, etc.)

Just as an FYI to myself, to play with C++0x features in g++, add the “-std=gnu++0x” compiler option.  For example, it get the following program to build:

// decltype.cpp
int f() { return 0; }

int main()
{
   decltype(f()) i = 8;
   return 0;
}

use the following on the command line:

g++ -std=gnu++0x decltype.cp

Final Thoughts on Ada

I’ve been dabbling with Ada off and on for the past three years now.  It has been very interesting and it has changed the way I think about software.  That said, I think I am letting it go now.  Let me explain.

I have been working on a model and software tool for a ASIC design course I’m taking this semester.   Essentially I need to do some preprocessing of data that is to be stored in a ROM in the hardware.  We are free to use any technology to implement the software part, and I was torn between C++ and Ada initially.  I’m most proficient and productive in C++, but I thought it would be a good opportunity to really learn how to use Ada in a non-trivial way.

I ended up picking Ada, and at first all was well.  The compiler caught a lot of silly errors and the algorithms just seemed to flow.  Despite my unfamiliarity with Ada, the syntax felt intuitive and I did not run into too many issues implementing my algorithms. I particulary apprecaited the assignment operator (:=) and wished for it in C++ at a later point.  Exceptions when dereferencing my pointers was also much more pleasant than core dumps and gdb sessions.

Ada let me down in three areas however: strings, text I/O, and generic containers.  Working with strings and text in Ada is just down-right painful, and I think Ada really shows its age here.  The I/O subsystem is also as primitive as its gets, and something as simple as printing an integer as a hexadecimal number without the 16#…# was not easy and I ended up having to writing somewhat of a hack.  I am sure there is a better way, but there just isn’t a large enough community and Internet pressence around Ada to quickly figure out the better way to do things.

Generics in Ada were also quite awkward to work with.  The most annoying aspect about generics is the having to use package methods to operate on the generic objects.  The containers package also lacks some very fundamental data structures such as simple queues.  In general I don’t like generics in Ada, they feel like an afterthought and a poor immitation of the Standard Template Library of C++.  I realize there are third party libraries like the Booch Components or Charles Library, but to a novice Ada developer I found it difficult to build and install things with GPR files rather than Makefiles.

In the end, I reverted back to C++.  Despite the cryptic nature of C++ and a frustrating debugging session where the cause was “if( a = b)”–which Ada would have caught–I was able quickly reimplement the code in C++ and move onto the hardware design…the reason for enrolling in a hardware design course.  Where C++ really shined was with the STL and Boost library.  STL containers and algorithms are simple to use, and Boost shared_ptr saves one from having to worry about memory management and allows one to focus on algorithms and the problem at hand.  And program_options makes creating a command line program a breeze.

I am sure that Ada really shines in embedded systems where string processing is much less common, and the features of Ada are in high demand. To master Ada, I also think one would need access to a strong mentor as the books and available resources for Ada are lacking in this day and age.

I came to appreciate many featuers in Ada, but in the end I just do not think it is the right tool for the type of things I do at work, school, and home.  C++ does everything I need at the momement, and I should probably focus my attention and software energies on learning new C++0x features or even exploring Erlang, my favorite programming technology at the moment.

Boost: Thread timing and timeouts

Boost.Thread has the ability to use timeouts for obtaining mutex resources or even just blocking a thread with the sleep() method.  Creating a timeout is, thankfully, a very easy task. All one needs to know is that the Boost.Thread library takes timeout parameters in the form of boost::system_time (as of Boost 1.35).  This is actually just a simple typedef to a POSIX data structure in the Boost.DateTime library.  From boost/thread/thread_time.hpp:

typedef boost::posix_time::ptime system_time;

To create a simple timeout, the following code will usually suffice:

boost::system_time timeout = boost::get_system_time() + boost::posix_time::milliseconds(n);

The vaule for n is simply an integer value, perhaps randomly generated? There is no need to dig into the large and versatile Boost.DateTime library, and with boost::system_time there is no longer a need to use boost::xtime unless working with an older code base that has yet to be refactored.  With random time period generation and timeouts covered, we are now ready to move onto exploring some multithreaded code.

Boost: a random time period generator

Random numbers play a critical role in the verification of designs, allowing testers to apply stimuli and stress a system beyond the known boundary conditions.  The C++ standard library supplies the random() function, which after seeding, produces a simple pseudo-random sequence.  For many applications, this simple function is enough; alternatively, it is very limiiting if your application needs to grow and support different testing methods.  Though there are many random number libraries freely available, most modern C++ applications utilize Boost, and the Boost libraries contain the Boost.Random library as a compile-only header library.

A glance in the boost/random/ directory can be intimidating at first.  There are various header files with cryptic mathematical names, but at its heart, however, the library is easy to understand once you understand the background concepts and have a statistical reference at your side. There are two key concepts that you need to understand before setting out to use the library: generators and distributions.

Background

Based on the Boost.Random documentation, generators can be classified into roughly three variations:

  • Uniform – number sequences uniformly distributed over a finite range
  • Pseudo – numbers sequences generated by an algorithmic process that simulates random behavior
  • Non-deterministic – number sequences generated by stochastic processes

Determining which type of generator is best for your application depends on many factors, but for most test applications the pseudo-random generator is adequate.  Examples of the pseudo-random generators are minstd_rand and rand48, which are both declared in boost/random/linear_congruential.hpp.  Refer to the boost documentation and header files for more information on the various generators available.

Distributions represent the distribution, or layout, of a sequence of numbers over finite interval based on the corresponding random variable of the same name.  When selecting a distribution for your tests, refer to an undergraduate level Statistics textbook or your favorite statistical reference. Boost.Random supports a large number of statistical distributions, including, but not limited to:

  • Normal
  • Uniform
  • Binomial
  • Exponential
  • Gamma
  • Bernoulli
  • Geometric
  • Poisson

If you need a distribution or generator not included in Boost.Random, you can implement new classes that conform to the Boost.Random interface causing minimal change to your code. More often than not, the library already has a sufficient amount at your disposal.  Popular pseudo-random generators, such as the linear feedback shift that is ubiquitous in hardware engineering (boost/random/linear_feedback_shift.hpp) are available out-of-the-box and ready to use with some simple template parameters at instantiations.

In the remainder of this post, I am going to demonstrate the use of generators and distributions in developing a simple class that generates pseudo-random numbers for time periods.

Generating Random Time Periods

When testing multi-threaded code or even just trying to understand concepts in the Boost.Threads library, I find that having the ability to quickly and easily generate random time intervals beneficial.  One thread can be blocked for X milliseconds, another by Y milliseconds, allowing one to verify the behavior of the various synchronization mechanisms in Boost.Threads.  Using boost::variate_generator, we can bind a generator to a distribution and create a random sequence of numbers representing time periods.  I use a simple class like the RandomTimePeriod class listed below.

#ifndef RANDOMTIMEPERIOD_H
#define RANDOMTIMEPERIOD_H
#include <boost/random.hpp>
#include <boost/shared_ptr.hpp>

class RandomTimePeriod
{
private:
  // accept default template param of 'int'
  typedef boost::uniform_int<> distribution_type;
  typedef boost::minstd_rand   generator_type;
  typedef boost::variate_generator< generator_type,
            distribution_type > RandomNumberGenerator;

public:
  RandomTimePeriod(int max_ms=100)
  {
    // Magic number 42 - if you don't understand this,
    //    look into Douglas Adams--now!
    generator_type generator(42);
    distribution_type distribution(0,max_ms);
    _rng.reset(new RandomNumberGenerator(generator, distribution));
  }

  int next()
  {
    (*_rng)();
  }

private:
  boost::shared_ptr _rng;
};
#endif

The code above should be rather straightforward.  Typedefs are used to make the constructor code more readable.  The constructor takes a parameter specifying the max time period in milliseconds, and it constructs the random number generator member variable.  The generator is passed the infamous value of 42 as its seed value, but in real test code one should provide a better method of obtaining a seed.  Calling the next() method on a RandomTimePeriod object will return a pseudo-random number. Internally the random number generator is stored in a smart pointer so that when the object’s reference count reaches zero, the object is properly cleaned up.  Very simple, right?  The following driver program demontrates how to use the RandomTimePeriod class.

int main(int argc, char* argv[])
{
  RandomTimePeriod rtp;
  for( int i=0; i<25; ++i){
    std::cout << rtp.next() << std::endl;
  }
  return 0;
}

Armed with a simple random number generator, we can now focus on creating better tests for the verification of our designs.  Stay tuned for more tutorials on Boost libraries, including Boost.Thread.