Upgrading DB2 LUW from 9.5 with XML Extender to 9.7

Summary

The IBM documentation has good information on how to do the upgrade in general, and how to go through the preparation steps. Unfortunately the information about XML Extender is sparse, and all you get is

ADM4104E One or more databases are enabled for XML Extender. You must remove the XML Extender functionality from the instance and databases before upgrading. Please refer to the DB2 Information Center for details on the steps to upgrade XML Extender including how to disable XML Extender in databases.

This is misleading at best, since chances are that the XML extender was installed with the default DB2 install and never used, which was the case for the first upgrade I had to do. You get this error message regardless of having any column, table, database, or instance using the XML Extender. However, to find out how to get rid of the XML Extender is quite a challenge.

Steps

Follow the instructions here until step 6: Upgrading a DB2 server with XML Extender to DB2 Version 9.7

Once you’re at step 6, here are the details, assuming you have installed DB 9.5 to the default path.

  • First, check that XML Extender is installed:

    db2ls -a -q -b /opt/ibm/db2/V9.5
  • Then proceed to uninstall XML extender:

    cd /path/to/install/media
    ./db2_deinstall -F XML_EXTENDER -b /opt/ibm/db2/V9.5

This completes step 6 and you can proceed with the following step – step 7 – in the documentation. Make sure you use the correct db2iupdt executable:

/opt/ibm/db2/V9.5/instance/db2iupdt

Caveat

Even after fixing everything the upgrade tool complains about, you may still get odd error messages, that claim that you have not corrected the problems. That is some odd behavior of the error message reporting in DB2. For example, the front end was still complaining about VIEWs even though there weren’t any, and the back-end was still complaining about XML Extender even after it was uninstalled. Thus, if you are sure that all the issues are in fact fixed, you can go ahead and force the upgrade with the “-F” option.

References


Installing IBM Systems Director on an AIX 6.1 LPAR with a DB2 back-end

IBM Systems Director is a very powerful management tool that comes free with PowerVM (that is: free to anyone that purchases a Power System). I have gathered here the different steps. Most of the information comes from the official documentation: Installing IBM Systems Director on the management server. In this guide I focus on the installation using AIX 6.1 in an LPAR with DB2 as the back-end database server.

  • First, prepare the database as described in Preparing the IBM DB2 Universal Database. The DB2 client can be installed following the steps described in Installing the DB2 9.5 Client on AIX 6.1
  • Then, make a note of the following configuration parameters for the Director installer:


    DbmsApplication = DB2
    DbmsServerName = fqdn_of_db2_server
    DbmsTcpIpListenerPort=database_port (for example 50000)
    DbmsDatabaseName = database_name
    DbmsDatabaseAppHome = path_to_sqllib (for example /home/db2inst1/sqllib)
    DbmsUserId = database_user_name
    DbmsPassword = (encrypted_user_password populated by /opt/ibm/director/bin/configDB.sh)

    Create a database for IBM Systems Director (ISD), and make sure you can connect to the DB2 database from the AIX LPAR that will serve for the ISD install.

  • Next, the network is prepared, as described in Preparing firewalls and proxies for IBM Systems Director. Remember to also open up access from the management server to the other LAPRs and servers that ISD will have to access.
  • make sure the ports needed for ISD are available:
    netstat -an | grep LISTEN | egrep "951(0|4|5)"
    If the ports are in use, modify them as follows:
    netstat -an | grep LISTEN | egrep "991(0|4|5)"
    if there is no output, then:
    /var/opt/tivoli/ep/runtime/agent/toolkit/bin/configure.sh -unmanaged -port 9910 -jport 9914 -nport 9915 -force
  • Next, AIX needs to be patched if necessary, as described in Preparing to install IBM Systems Director Server on AIX. Since we are on AIX 6.1, there is little to do if all the fixpacks are up to date. Installing CSM is one step I recommend if you have IVM managed systems (having csm.hc_utils already installed from the install CD is necessary):


    mkdir csm
    wget 'ftp://ftp.software.ibm.com/software/server/csm/csm-aix-1.7.0.19.power.tar.gz'
    gtar xvzf csm-aix-1.7.0.19.power.tar.gz
    cd installp/ppc
    inutoc .
    installp -acgXYd . csm.hc_utils
    cd ../../../director (where you unpacked the director download see below if you haven't done that part yet)
    installp -acgXYd . Director.Server.ext.FSPProxy.rte

  • Download IBM Systems Director if not already done from IBM, unpack it, and run the installer:
    mkdir director
    gtar xvzf path_to_download/SysDir6_1_Server_AIX.tar.gz
    server/dirinstall.server
  • Configure the database access:
    cd /opt/ibm/director/proddata/
    cp cfgdbcmd.rsp cfgdbcmd.rsp-dist
    vi cfgdbcmd.rsp

    Edit the file cfgdbcmd.rsp and select the lines that apply to DB2. Then populate the password with:
    /opt/ibm/director/bin/configDB.sh
    /opt/ibm/director/bin/cfgdbcmd.sh -dbAdmin db2_instance_user -dbAdminPW db2_instance_user_pass
    this will take a while to complete, as there are over 1,000 tables to create, with constraints and indexes, and then the tables are pre-populated.
  • Then a final configuration to create the resource manager user ID
    /opt/ibm/director/bin/configAgtMgr.sh
    This is very simple, just provide a user id and a password for the ISD to use internally.
  • Now you are ready to start the ISD:
    /opt/ibm/director/bin/smstart
    This will take a while, and it will probably hang if you don’t have enough memory. A minimum of 2GB is necessary.
  • Follow the startup progress:
    /opt/ibm/director/bin/smstatus -r
    The output should be as follows:
    Inactive
    Starting
    Active
    I didn’t time, but it takes over 10 minutes for the ISD to become ready in a small LPAR on a p5

That’s it, from here, you can continue on with the ISD documentation: Configuring IBM Systems Director Server after installation.
Let me know how it goes and how you like ISD.


Installing the DB2 9.5 Client on AIX 6.1

  • In case an application such as the IBM Systems Director needs the DB2 client to be installed, the process has a couple of pitfalls. Here are steps that make the installation very simple.
  • Download the DB2 client from IBM: http://www.ibm.com/software/data/db2/9/download.html
  • Create a directory and unpack the tarball in that directory, and install the client:
    wget 'https://www6.software.ibm.com/sdfdl/v2/regs2/smkane/dsclients/IBM_DATA_SERVER_CLIENT/Xa.2/Xb.YZomhqTjd0WgkJqNENbocjIpCoqHi4eEhNMEPXE/Xc.v9.5_aix64_client.tar.gz/Xd./Xf.LPr.D1vk/Xg.4962564/Xi.swg-idsc11/XY.regsrvs/XZ.JOzao7W3aaunX0L_IE0SvHPww1I/v9.5_aix64_client.tar.gz'
    mkdir db2
    cd db2
    gtar xvzf ../v9.5_aix64_client.tar.gz
    cd client
    ./db2_install
  • Create users in the system for the client to use:
    mkgroup -'A' id='999' db2iadm1
    mkgroup -'A' id='998' db2fadm1
    mkgroup -'A' id='997' dasadm1
    useradd -u 1004 -g db2iadm1 -m -d /home/db2inst1 db2inst1
    useradd -u 1003 -g db2fadm1 -m -d /home/db2fenc1 db2fenc1
    useradd -u 1002 -g dasadm1 -m -d /home/dasusr1 dasusr1
  • Create a client instance for the client to use:
    /opt/ibm/db2/V9.5/instance/db2icrt -a server -u db2fenc1 db2inst1
  • Add the db2 profile to the default profile:
    echo ". /home/db2inst1/sqllib/db2profile" >> /etc/profile
  • Test the db2 client:
    su - someuser
    db2
  • Add DB2 servers to the catalog:
    db2 catalog tcpip node node_name remote fqdn_of_db2_server server port_number_of _server_instance
    db2 catalog database database_name at node node_name authentication server
  • At that point you are ready to test the database connection:
    db2 connect to database_name user user_name using user_password

XQuery and pureXML in DB2

I just came across the following two articles that have some good pointers for using the native XML capabilities of DB2: