Migrating Data Between DB2 Servers

DB2 Support for Data Migration

When it is time to upgrade from DB2 on Intel to DB2 on Power for example, taking a backup/restore approach isn’t possible as DB2’s backups are platform dependent. The solution is to use a DB2 command called db2move.

Basics of db2move

db2move allows to export data from DB2 at different levels of granularity. It also has a very simple syntax to allow exporting all of the data and structure of a database.

Exmaple of db2move


su - db2inst1
mkdir /tmp/db2export
cd /tmp/db2export
db2move sample export

Where db2inst1 is the db2 instance owner, /tmp/db2export is where the DB2 data and structures are being exported, and sample is the database name.

For more DB2 Support hints, please visit our DB2 category.


Migrating Data to DB2 9.7 LUW

Problem

You reached the limit or your unhappy with the performance or features of your database server, and you want to move to DB2. The transition can be simple with respect to your code, as it may be just a matter of changing a few lines of code for the connection string, and setting the correct compatibility mode in DB2. But how do you move your data?

Solution

IBM has published a data migration toolkit, that has been updated to work best with DB2 9.7. Make sure you have service pack 2 installed, which is the latest service pack available as of this writing.

If you are migrating from MySQL or PostgreSQL, enable the ability to do limits and offsets in the where clause with:
db2stop force
echo Set compatibility to 01 or 0F
db2set DB2_COMPATIBILITY_VECTOR=01
db2start

References


DB2 9.7.2 Log files on Windows 7

Problem

Since Windows Vista (that includes Windows 7), the place where programs store some of their information has changed. This makes finding the DB2 log files (equivalent of db2diag.log) more difficult.

Solution

The log files have moved under the directory tree node C:/Program Data/IBM/DB2/DB2COPY1/DB2. Under that directory there is a log and an events directory. This being Windows, the events directory has the logs that on Linux/Unix are the equivalent of the db2diag.log.

References

Windows Vista: Where is db2diag.log location?


Create Explain Tables in DB2 9.7

Problem

The explain tables allow to create access plans and visualize them. Before the explain feature of DB2 can be used, the EXPLAIN tables need to be generated.

Solution

To create the EXPLAIN tables, the following command needs to be issued while connected to the database that the access plain needs to be generated in:
CALL SYSPROC.SYSINSTALLOBJECTS('EXPLAIN','C',NULL,'EXPLAIN_SCHEMA')


Compiling C code on Windows 7 for DB2 9.7 64bit

Problem

The %DB2PATH%\SQLLIB\samples\c directory on windows contains many examples of C routines to extend DB2 or to work with DB2 from C programs. This article describes how to compile the sample code for 64 bit.

Solution

  • Set the environment (VC includes the batch file that allows to set the environment variable): C:\progra~2\micros~2.0\vc\vcvarsall.bat x64

Netapp Automation for DB2 9.7 (or Oracle)

Problem

You have one or more NetApp storage systems (F960 or later series), running Data ONTAP® 7G (or later). You would like to take advantage of the snapshot capabilities, to facilitate the database backup process. However, you don’t want to use the default root login for the automated logins, nor do you want to use the unsecure rsh, as these options would violate corporate security policies (especially if you have a compliance commitment to ISO 27002, PCI or HIPAA).

Solution

Create a restricted users that has only login access and the ability to manage snapshots:

  • Setup ssh on the filer:secureadmin setup ssh (it is recommended that you select long keys when you are asked 1024 and 768 for ssh v1 – ssh1 shouldn’t be enabled anyway – 2048 for ssh2).
  • Start ssh on the filer: secureadmin enable ssh2 (at this point you should be able to log in to the filer with ssh as root with your admin password)
  • Create group / role / user:
    useradmin user add snapuser -g Users
    useradmin role add snaps -c "Snapshot Manager" -a cli-snap*,login-ssh,login-telnet
    useradmin group add cli-snapshot-group -r snaps
    useradmin user modify snapuser -f -g cli-snapshot-group
    useradmin user list snapuser

    The last command allows you to check your work, and the output should like:
    Name: snapuser
    Info:
    Rid: 131075
    Groups: cli-snapshot-group
    Full Name:
    Allowed Capabilities: cli-snap*,login-ssh,login-telnet
    Password min/max age in days: 0/4294967295
    Status: enabled
  • Put your public keys in the authorized keys file on the filer:/etc/sshd/snapuser/.ssh/authorized_keys (typically you do that by mounting the filer root volume on one of your AIX boxes – any OS that can mount the root volume should work).
  • At this point you are ready to test by logging in via ssh to the snapuser account. Keep in mind that before you can successfully log in, you have to log out from the NetApp.

References


DB2 Query Writing Best Practices

Problem

Writing efficient SQL SELECTqueries for DB2 can be tricky. Some general SQL guidelines apply, and there are also specific practices that apply only to DB2.

Solution

Here is a list of tips you can use to write good SELECT queries:

  • Avoid SELECT * in your queries. Instead, state the columns you want to select.
  • Use the consistent acronyms to alias your table names (makes it easier to read joins)
  • Use FETCH ONLY x ROWS to limit the results set
  • Use CASE or MERGE instead of UNION ALL

References


DB2 Discovery Kit

I have posted a number of tips to use DB2. If you haven’t used DB2 before, it is one of the best databases out there, and best of all, it is available completely free from IBM. Get your DB2 discovery kit from IBM. As Grant Allen puts it in Beggining DB2, “IBM has even thought of those of you who can’t get to their website to obtain DB2. You can e-mail or telephone IBM’s DB2 team to arrange to have the Discovery Kit shipped to you.”




Register DTD for pureXML with DB2 9.7

Problem

Even though XML schema has been around for a decade, still many APIs reference a DTD in the XML documents sent back and fourth. Such is the OpenSRS API. DB2 requires an XML schema or a DTD to be registered before it will process XML documents that refer to an XML Schema or a DTD. The process of registering such XSROBJECTs with DB2 has changed in DB2 since version 8.

Solution

  • register a DTD using the DB2 9.7 command line:
    db2 CONNECT TO SAMLE
    db2 REGISTER XSROBJECT 'dtd-file-name.dtd' FROM /path/to/dtd/file AS sample.dtd-name DTD

    If the command is successful, the output is
    DB20000I The REGISTER XSROBJECT command completed successfully.
  • Register a DTD from a Java program:
    String schemaName = "SAMPLE";
    String dtdName = "dtd-name";
    String dtdFile = "dtd-file-name.dtd";
    Reader fis = new FileReader(dtdFile);
    byte[] contentDtd = FileIO.readerToString(fis).getBytes();
    String registerProcQuery = "CALL SYSPROC.XSR_DTD (?,?,?,?,?)";
    PreparedStatement registerProcStatement = conn.prepareCall(registerProcQuery);
    registerProcStatement.setString(1, schemaName);
    registerProcStatement.setString(2, dtdName);
    registerProcStatement.setString(3, dtdFile);
    registerProcStatement.setString(4, "PUBLIC ID");
    registerProcStatement.setObject(5, contentDtd, java.sql.Types.BLOB);
    registerProcStatement.execute();

    The FileIO class can be found in Java Cookbook, Second Edition

References



Change Host Name of DB2 9.7 Server on AIX

Synopsis

After changing the name of the host on which DB2 9.7 is running, the following error message is received when trying to start the database:
09/28/2009 02:32:50 0 0 SQL6048N A communication error occurred during START or STOP DATABASE MANAGER processing.
SQL1032N No start database manager command was issued. SQLSTATE=57019

Solution

For each database instance on the machine where the name changed, and on each federated server instance, the file $INST_HOME/sqllib/db2nodes.cfg needs to be edited, and the old host name changed to the new name for each occurrence. The format of the node lines is:
0 host.domain.tld 0

References