dB2 LUW v9.7

How to copy an entire schema between AIX and Windows:

A few months ago I had the need to setup a standalone environment in my laptop in order to have independency while testing and develop a new system architecture.

The problem was the dB2 schema that I needed was on a AIX machine. So I had to copy it all and move to my Windows machine.

Here’s the step-by-step of how to do it:

  1. On your AIX machine, export data of schema MY_SCHEMA

  2. db2move DBNAME export -sn MY_SCHEMA

    The output of this produced is:

    • EXPORT.out-Contains a summary of the completed action (ASCII).

    • db2move.lst -Contains a list of original table names, their corresponding PC/IXF file names, and message file names (ASCII).

    • tabn.ixf -Contains exported data from a user table, identified by n (binary).

    • tabn.msg -Contains messages about the requested action against a user table, identified by n (ASCII).

    • tabna.nnn -Contains exported large object (LOB) data for a user table, identified by n. The file name extension is a number ranging from 001 to 999, and a is an alphabetic character.These LOB files, which are created only if the table being exported contains LOB data, are placed in the LOB path directories system.msg

  3. Extracts all DDL statements for database objects (-e) for a specific schema (-z). Also generates tablespaces, databsse partition groups, and bufferpools (-l)

  4. db2look -d MY_SCHEMA -o db2look_sch.sql -e -z MY_SCHEMA -l

  5. Compress it all and download into your Windows machine

  6. tar cf - * | gzip > /big/schdb2.tar.gz
    rec aixmachinename /big/schdb2.tar.gz

    This rec funtion is intended to downdload the file from the remote aix machine through scp tool

  7. On the Windows machine, open a bash console, e.g. cygwin and do this:

  8. sed -i 's_/pfdata/db2ipf2/DBNAME/db2ipf2/_C:\\DB2\\_' db2look_sch.sql
    sed -i 's_/pfdata/db2ipf2/MY_SCHEMA/_C:\\DB2\\NODE0000\\SAMPLE\\_' db2look_sch.sql
    sed -i 's_/pfdata/db2ipf2/datatmp\_tbs/_C:\\DB2\\NODE0000\\_' db2look_sch.sql
    sed -i -r 's/(USING *\(FILE .* )([0-9]*)\)/\11 M\)/' db2look_sch.sql

  9. Open a DB2 CLP console

  10. sed -i 's_MY_SCHEMA_SAMPLE_' db2look_sch.sql
    sed -i 's/AIXDBOWNER /WINDBOWNER/' db2look_sch.sql

  11. Run DDL definition:
  12. db2 -tvf db2look_sch.sql > db2look_sch.log

  13. Load data

  14. db2move SAMPLE load

  15. Finally you will get SQLCODE=-668 SQLSTATE=57016 with “reason code”=1. To fix it please check snippet with SET_INTEGRITY

  16. You might also have Not Authorized SQLCODES 551N. Fix it running GRANT with db2admin

  17. GRANT ALL ON SECURITY_MANAGER.T_SECURITY_MAINTENANCE TO DB2USER

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s