Going from DB2 Enterprise to DB2 Express-C

Downgrading from DB2 Enterprise to DB2 Express-C is a fairly straightforward process.

Stop DB2.

db2stop force

Uninstall DB2 Enterprise.

sudo su - db2inst1
/opt/ibm/db2/V9.7/install/db2_deinstall -a
exit

Install DB2 Express-C without creating any users, etc.

sudo ./db2_install

Update the instance with the new edition information.

sudo /opt/ibm/db2/V9.7/instance/db2iupdt db2inst1

You might encounter this error.

db2inst1@vhost0074:~> db2start
SQL5043N  Support for one or more communications protocols
failed to start successfully. However, core database manager
functionality started successfully.

Look up your DB2 TCP/IP service name. It should be similar to db2c_db2inst1 where db2inst1 is the instance owner.

cat /etc/services | grep db2

Make sure it’s set correctly in the DB2 configuration.

db2 update dbm cfg using SVCENAME db2c_db2inst1

Start DB2.

db2start

You should now have a working DB2 Express-C installation with all the same databases.

Enable NFS services for HADR

You might need to enable network file system (NFS) services when configuring the High Availability Disaster Recovery (HADR) or Database Partioning (DPF) DB2 features. For example, with HADR, you might want to set up a slave database that shares a backup with the master database through NFS.

These commands should enable NFS services on most distributions of Linux:

sudo /sbin/chkconfig nfs on
sudo /sbin/chkconfig nfslock on
sudo /sbin/chkconfig rpcgssd on
sudo /sbin/chkconfig rpcidmapd on
sudo /sbin/chkconfig portmap on
sudo /etc/init.d/iptables restart
sudo /etc/init.d/nfslock restart
sudo /etc/init.d/portmap restart
sudo /etc/init.d/nfs restart

Some of the services might not exist on your distribution. Additional steps may be needed.

IBM Smart Business Cloud for Test & Development might be one of the environments where you’d have to do this.

Changing the DB2 hostname

SQL6031N  Error in the db2nodes.cfg file at line number "1".  Reason code "10".

DB2 caches your machine’s hostname in several places. If your machine is changing its hostname, or if you are somehow moving an existing installation to a machine with a different hostname, you will need to adjust the hostname stored by DB2. Hostname change is common in virtual and cloud environments, so even better than adjusting it would be writing a script that adjusts it for you.

Here’s my rudimentary stab at a such a script:

#!/bin/sh
echo -n "Discovering the new hostname "
UNAME_CACHE=$(uname -n)

echo -n "Adjusting hostname list for unconfigured database partitioning feature "
# this may need to be changed if DPF preconfigured
if [ -e /home/db2inst1/sqllib/db2nodes.cfg ]; then
    chmod 666 /home/db2inst1/sqllib/db2nodes.cfg
    su - db2inst1 -c "cp /home/db2inst1/sqllib/db2nodes.cfg /home/db2inst1/sqllib/db2nodes.cfg.old"
    su - db2inst1 -c "echo 0 $UNAME_CACHE 0 > /home/db2inst1/sqllib/db2nodes.cfg"
fi

echo -n "Making registry writable "
chmod 666 /var/db2/*

echo -n "Adjusting the DB2 hostname "
UNAME_CACHE=$(uname -n)
/opt/ibm/db2/V9.7/adm/db2set -g db2system=$UNAME_CACHE

echo -n "Updating DAS configuration "
# todo
# db2 uncatalog node

db2 catalog admin tcpip node $UNAME_CACHE remote $UNAME_CACHE system $UNAME_CACHE
db2 update admin cfg using DB2SYSTEM $UNAME_CACHE
db2 update admin cfg using SMTP_SERVER $UNAME_CACHE

This doesn’t cover all cases. For example, if you use extended operating system security, you may also need to set the db2accountname and db2instowner parameters. A quick check with db2set -all should tell you if this applies in your case — if there is something that looks like a hostname in those parameters already, you need to change them.

I may also have missed other necessary changes. Please let me know if I did.:-)

References

• IBM Support | Changing hostname of DB2 server
• Fillmore Group | Changing the hostname of a DB2 server
• Diary of a Database Geek | The importance of db2nodes.cfg

DB2 and usernames with spaces

DB2 v9.5 uses the Windows username as the default schema. Unfortunately, it does not support spaces in schema names. Accordingly, usernames – e.g. “Jane Smith” — that have a space can make DB2 unhappy.

I recently helped someone resolve an issue with this command:

db2sampl -force -name SAMPLE

Which brought up this error:

  Creating database "SAMPLE"...
  Connecting to database "SAMPLE"...
  Creating tables and data in schema "JANE SMITH"...

--ERROR----
  SQLSTATE = 42601
  Native Error Code = -443

[IBM][CLI Driver][DB2/NT64] SQL0443N  Routine "*L_SAMPLE" (specific name "") has  returned an error SQLSTATE with diagnostic text "SQLSTATE 42601: A character,  token, or clause is invalid or". SQLSTATE=42601
--

  Creating tables with XML columns and XML data in schema "JANE SMITH"...

--ERROR----
  SQLSTATE = 42601
  Native Error Code = -443

[IBM][CLI Driver][DB2/NT64] SQL0443N  Routine "*L_SAMPLE" (specific name "") has  returned an error SQLSTATE with diagnostic text "SQLSTATE 42601: A character,  token, or clause is invalid or". SQLSTATE=42601
--

  'db2sampl' processing complete.

The full routine name is CREATE_SQL_SAMPLE. It tries to create tables for Jane Smith, fails, and blazes bravely on. Interestingly, an empty database results.

The workaround is to log in as a user without a space, such as “db2admin”, run db2sampl, and then log back in as yourself.

For running future queries, you can specify a different schema:

SET SCHEMA=db2admin;

And, of course, you can always explicitly specify a schema in your queries:

SELECT * FROM db2admin.tablename;

Install DB2 via the command line

I installed DB2 on an Ubuntu Linux server via SSH and the command line installer this morning. This is not particularly exotic, but is different from using the GUI installer on either platform. Still, I should jot down my steps:

1. SSH to your server. On Windows, use Putty or the OpenSSL package in Cygwin.
2. Open up a text mode browser like lynx or links
3. Go to http://www.ibm.com/db2/express/
4. Download Express-C. Get some tea and snacks while that happens.
5. tar xzvvf the file and cd into the directory
6. If the next step fails, you may need to install some libraries. I had to sudo apt-get install libstdc++5
7. sudo ./db2setup
8. You now need to manually configure the DB2 server
   1. Set up users and groups
   2. Create a DB2 Administration Server
   3. Create a DB2 instance
   4. Create links to DB2 files
   5. Configure TCP/IP communication
      1. Configure services file
      2. Update database manager
      3. Set communications protocols
   6. Apply license (already done, verifiable by db2licm -l)

Extra notes just in case:

• sudo is the usual way to escalate privileges on Ubuntu.
• sudo su - db2inst1 is the way to switch users to, in this case, user db2inst1.
• Default settings in the steps above make db2inst1 the user with SYSADM permissions for DB2.
• For SFTP file transfer, you can use WinSCP or Filezilla with the same credentials as for SSH.

pureXML in DB2

pureXML is the native XML storage capability in IBM DB2. It allows for whole XML documents to be stored in a DB2 table column, and for them to be easily queried via either SQL or XQuery or some combination of the two. Getting Started with DB2 has a decent introduction to both . There’s also schema validation, versioning, and other esoteric features.

I’ve played with it a fair bit and found it quite performant in my admittedly limited scenario of a couple thousand xml docs.

Conor O’Mahony blogs extensively on the topic. In terms of community, there’s an official site, a comprehensive wiki, a forum.

Rails and DB2 data types

When creating a table in a Rails migration, you have to specify data types using platform-agnostic names. The mapping of Rails types onto DB2 types is defined in ibm_db_adapter.rb:

:primary_key => @servertype.primary_key,
:string      => { :name => "varchar", :limit => 255 },
:text        => { :name => "clob" },
:integer     => { :name => "integer" },
:float       => { :name => "float" },
:datetime    => { :name => "timestamp" },
:timestamp   => { :name => "timestamp" },
:time        => { :name => "time" },
:date        => { :name => "date" },
:binary      => { :name => "blob" },

# A boolean can be represented  by a smallint,
# adopting the convention that False is 0 and True is 1
:boolean     => { :name => "smallint"},
:xml         => { :name => "xml"},
:decimal     => { :name => "decimal" }

Useful Resources

InfoCenter | DB2 Data Types
dW | DB2 and Ruby on Rails, Part 1 (May 2007)
dW | An Introduction to Ruby on Rails for DB2 Developers (June 2006)

The DB2 adapter is now called ibm_db. You can refresh your installation by typing gem install ibm_db at the command line and choosing the latest win32 release.

No implementation defined for org.apache.commons.logging.LogFactory

While writing a DB2 stored procedure that invoked a SOAP/WSDL web service using Apache Axis as part of WSIF, I ran into this doozie:

org.apache.commons.discovery.DiscoveryException:
No implementation defined for org.apache.commons.logging.LogFactory

Ultimately, it’s caused by a too restrictive lib/security/java.policy file that ships with DB2.

Wrong Solution

The standard way to define an implementation is to create the following commons-logging.properties file and place it anywhere in your CLASSPATH (such as the root of a JAR file):

# Default
#org.apache.commons.logging.LogFactory = org.apache.commons.logging.impl.LogFactoryImpl

# SimpleLog
#org.apache.commons.logging.Log = org.apache.commons.logging.impl.SimpleLog 

# JDK 1.4 logger
#org.apache.commons.logging.Log = org.apache.commons.logging.impl.Jdk14Logger

# Avalon Toolkit
#org.apache.commons.logging.Log = org.apache.commons.logging.impl.LogKitLogger

# Log4j (Recommended by Axis)
org.apache.commons.logging.Log = org.apache.commons.logging.impl.Log4JLogger

Alternatively, you can set the org.apache.commons.logging.Log configuration attribute for LogFactory programmatically.

Right Solution

Solution: Running an Axis SOAP client in Domino [or DB2]

My DB2 is installed into C:\Program Files\IBM\SQLLIB

1. Copy all your JARs to C:\Program Files\IBM\SQLLIB\java\jdk\jre\lib\ext
2. Open C:\Program Files\IBM\SQLLIB\java\jdk\jre\lib\security\
3. Open java.policy
4. Add:

permission java.util.PropertyPermission "java.protocol.handler.pkgs", "write";

5. Restart DB2

Unexpected for following namespace declaration

I ran into a problem running a simple test xquery. I hadn’t directly dealt with XML namespaces in xquery prior to this, but the documentation was clear enough:

XQUERY
declare default element namespace "http://posample.org"
for $x in db2-fn:xmlcolumn("PURCHASEORDER.PORDER")
return $x

Oops, EOF error. It needs a terminator.

XQUERY
declare default element namespace "http://posample.org"
for $x in db2-fn:xmlcolumn("PURCHASEORDER.PORDER")
return $x;

Unexpected “for” following the namespace declaration? Pardon? It turns out xquery prologs need to be terminated by a semicolon:

XQUERY
declare default element namespace "http://posample.org";
for $x in db2-fn:xmlcolumn("PURCHASEORDER.PORDER")
return $x;

Unexpected “http://posample.org”? But that’s the exact syntax given in the examples! Alas, this semicolon is distinct from the usual semicolon separator in SQL. It’s part of a single xquery statement, so what I need to do is change the SQL separator to something more exotic. This will allow xquery to be parsed correctly:

XQUERY
declare default element namespace "http://posample.org";
for $x in db2-fn:xmlcolumn("PURCHASEORDER.PORDER")
return $x@

Eureka.:)

Mapping DB2 databases after a reinstall

Due to the vagaries of software, I had to reinstall DB2 on my laptop. Unfortunately, the existing databases were not automatically added to the Control Center.

Physically, DB2 stores its databases in a directory similar to C:\DB2\NODE0000. Logically, there must a way to remap them. So, how does one remap them?

To list databases stored at a path:
% db2 list db directory on c:

To recatalog them:
% db2 catalog db SAMPLE on c:

Reference:
DB2 at a Glance | The DB2 Environment