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
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
db2move sample export
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.
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?
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:
echo Set compatibility to 01 or 0F
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.
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
Windows Vista: Where is db2diag.log location?
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.
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:
%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.
- Set the environment (VC includes the batch file that allows to set the environment variable):
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).
Create a restricted users that has only login access and the ability to manage snapshots:
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).
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:
Allowed Capabilities: cli-snap*,login-ssh,login-telnet
Password min/max age in days: 0/4294967295
- 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.
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.
Here is a list of tips you can use to write good
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)
FETCH ONLY x ROWS to limit the results set
MERGE instead of
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.”
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.
- 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(4, "PUBLIC ID");
registerProcStatement.setObject(5, contentDtd, java.sql.Types.BLOB);
FileIO class can be found in Java Cookbook, Second Edition
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
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