Migrating DB2 from little endian Linux to big endian AIX
Here comes the DB2 export and import utility for cross platform database migration
Typically, data migration is performed between operating systems that use the same endian format. IBM® DB2® database systems support some backup and restore operations between different operating systems and hardware platforms. This article explains the migration of a DB2 database from a Linux® (little endian) system to an IBM AIX® (big endian) system.
Big endian and little endian
A load word or store word instruction uses only one memory address. The lowest address of the four bytes is used for the address of a block of four contiguous bytes.
Big endian byte order: The most significant byte (the big end) of the data is placed at the byte with the lowest address. The rest of the data is placed in order in the next three bytes in memory.
Little endian byte order: The least significant byte (the little end) of the data is placed at the byte with the lowest address. The rest of the data is placed in order in the next three bytes in memory.
Database and endian format
The supported platforms for DB2 backup and restore operations can be grouped under one of the following three families:
- Big-endian Linux and UNIX®
- Little-endian Linux and UNIX
- Microsoft® Windows®
A database backup from one platform family can be restored on any system within the same platform family only. For Windows operating systems, you can restore a database that was created on DB2 Version 10.1 on a DB2 Version 10.5 database system. For Linux and UNIX operating systems, if the endianness (big endian or little endian) of the backup and restore platforms is the same, you can restore backups that were produced on the earlier versions.
Here comes the DB2 export and import utility: A solution we implemented for DB2 migration from little endian (Linux) to big endian (AIX) because we could not find a similar solution anywhere online.
We implemented it using the following server specifications:
- DB2 source (little endian – Linux) server specification:
- Operating system level: Red Hat Enterprise Linux Workstation release 6.4 (Santiago)
- DB2 level (version): DB2 v10.1.0.3 (uses 64 bits)
- DB2 destination (big endian – AIX) server specification:
- Operating system level: AIX 22.214.171.124
- DB2 level(version): DB2 v10.1.0.3 (uses 64 bits)
Exporting DB2 database data from a Linux system to an AIX system
Perform the following steps to export the DB2 database data from the source little endian (Linux) server to the destination big endian (AIX) server:
Generate a Data Definition Language (DDL) file using the
db2lookcommand (for example, dbname.sql). The
db2lookcommand generates the DDL statements by object type. Note that this command ignores all objects under the SYSTOOLS schema except user-defined functions and stored procedures. Using the
db2lookutility, we were able to generate DDL statements for schemas, DDL statements for table, DDL statements for user-defined functions and procedures, DDL statements for foreign keys, and DDL statements for trigger queries in SQL files.
#db2look -d <DBname>-a -e -m -l -x -f -o DBname.sql
-d <DBname>: Is the alias name of the production database that is to be queried. DBname can be the name of a DB2 for Linux.
-a: Generates DDL statements for objects that were created by any user, including inoperative objects.
-e: Extracts DDL statements.
-m: Generates the UPDATE statements that are required to replicate the statistics on tables, statistical views, columns, and indexes.
-l: Generates DDL statements for user-defined table spaces, user-defined database partition groups, and user-defined buffer pools.
-x: Generates authorization DDL statements such as
-f: Extracts the configuration parameters and registry variables that affect the query optimizer.
-o: Writes the output to the DBname.sql file
-- This CLP file was created using DB2LOOK Version "10.1" -- Timestamp: Sun Jan 28 20:09:30 CST 2018 -- Database Name: <DBname> -- Database Manager Version: DB2/AIX64 Version 10.1.3 -- Database Codepage: 1208 -- Database Collating Sequence is: IDENTITY CONNECT TO <DBname>; ------------------------------------------------ -- DDL Statements for Schemas ------------------------------------------------ -- Running the DDL below will explicitly create a schema in the -- new database that corresponds to an implicitly created schema -- in the original database.
Separate the foreign key and the triggers from the DBname.sql file.
- If we run DBname.sql without separation of the foreign key, we may have to face missing primary key errors during import. Row import is rejected due to dependencies between tables. You can see the error messages under tabXX.msg.
- From the above generated SQL DBname.sql file, search for triggers, and separate the triggers into a new file named DBname_Trigger.sql. You will get error (“SQL3550W The field value in row “noX.” and column “no.X” is not NULL, but the target column has been defined as “GENERATED ALWAYS”) if you run the DDL file (for example, dbname.sql) without separating triggers.
Export all data from the source server little endian (Linux) using the
db2moveutility to retrieve a list of all user tables in a database from the system catalog and export these tables to the PC/IXF format. Export all the tables that meet the filtering criteria according to the option specified. If you do not specify an option, then all tables are exported. Internal staging information is stored in the db2move.lst file.
The following output is displayed.
***** DB2 MOVE ***** Action: EXPORT Start time: Tue Mar 13 01:18:43 2018 Connecting to database TESTDEV ... successful! Server: DB2 Common Server V10.1.3 Binding package automatically ... /home/authtest/sqllib/bnd/db2common.bnd ... successful! Binding package automatically ... /home/authtest/sqllib/bnd/db2move.bnd ... successful! EXPORT: 5 rows from table "JAZZAUTH"."ACTION" EXPORT: 7 rows from table "JAZZAUTH"."ACTION_FROM_RECORD_STATE" EXPORT: 1238 rows from table "SYSTOOLS"."ALTOBJ_INFO" EXPORT: 4 rows from table "SYSTOOLS"."AM_BASE_TASKS" EXPORT: 36 rows from table "SYSTOOLS"."AM_BASE_TASK_RPTS" EXPORT: 212335 rows from table "JAZZAUTH"."ATTRIBUTE" EXPORT: 251 rows from table "JAZZAUTH"."AUDIT"
The following files are generated when using the
- EXPORT.out: Shows the summarized result of the EXPORT action.
- db2move.lst: Holds the list of original table names, their corresponding PC/IXF file names (tabnnn.ixf), and message file names (tabnnn.msg). This list, the exported PC/IXF files, and large object (LOB) files (tabnnnc.yyy) are used as input to the db2move
- tabnnn.ixf: Is the exported PC/IXF file of a specific table.
- tabnnn.msg: Is the export message file of the corresponding table.
- tabnnnc.yyy: The exported LOB files of a specific table.nnn is the table number. ‘c’ is a letter of the alphabet. ‘yyy’ is a number ranging from 001 to 999. These files are created only if the table being exported contains LOB data. If created, these LOB files are placed in the path of the LOB directories. There is a total of 26,000 possible names for the LOB files.
- System.msg: Is the message file that contains system messages for creating or deleting a file or directory commands. This is only used if the action is
EXPORT, and a LOB path is specified.
Copy all the files, that is, the SQL files (DBname.sql, DBname_Foreignkey.sql, “DBname_Trigger.sql”) and the exported data, to any directory on the AIX destination system (big endian).
Connect to the database and capture the output of the following command to grant a similar level permission to the connection ID.
“db2 "select char(grantee,8) as grantee, char(granteetype,1) as type, \ char(dbadmauth,1) as dbadm, char(createtabauth,1) as createtab, \ char(bindaddauth,1) as bindadd, char(connectauth,1) as connect, \ char(nofenceauth,1) as nofence, char(implschemaauth,1) as implschema, \ char(loadauth,1) as load, char(externalroutineauth,1) as extroutine, \ char(quiesceconnectauth,1) as quiesceconn, \ char(libraryadmauth,1) as libadm, char(securityadmauth,1) \ as securityadm from syscat.dbauth order by grantee"
Importing DB2 database data to an AIX system
Perform the following steps to import DB2 database data to the destination big endian (AIX) server:
Create a database using the following commands with the same size of the buffer pool and tablespace of the source server little endian (Linux).
db2 ‘CREATE DATABASE <DBname>AUTOMATIC STORAGE YES USING CODESET UTF-8 TERRITORY US PAGESIZE 32 K CONNECT TO <DBname>’;
Run a SQL query that was generated on the source server little endian (Linux), that is, the DBname.sql script to create a table index. Note: Do not run DBname_Foreignkey.sql and Dbname_Trigger.sql before the import to avoid errors.
db2 -tvf DBname.sql
Grant similar level permissions to the connection ID and instance captured in step 4 during export.
Import all the tables listed in the db2move.lst internal staging file. Use the -io option for import-specific actions. It loads the actual data into the blank tables which are created using the db2 -tvf DBname.sqlcommand (in step 2). While importing, the data utility shows the progress (table wise as shown below):
db2move <DBname> import
The following output is displayed.
***** DB2MOVE ***** Action: IMPORT Start time: Tue Mar 13 05:35:32 2018 Connecting to database TESTDEV ... successful! Server: DB2 Common Server V10.1.3 * IMPORT: table "JAZZAUTT"."ACTION" -Rows read: 5 -Inserted: 5 -Rejected: 0 -Committed: 5
The following files act as the input while importing data:
- db2move.lst: An output file from the EXPORT action.
- tabnnn.ixf: An output file from the EXPORT action.
tabnnnc.yyy: An output file from the EXPORT action.
The following files are generated when using the Import command:
IMPORT.out: The summarized result of the IMPORT action.
- tabnnn.msg: The import message file of the corresponding table.
- db2move.lst: An output file from the EXPORT action.
Run the DBname_Foreignkey.sql and DBname_Trigger.sql files separately to create a connection between the tables.
db2 -tvf DBname_Foreignkey.sql db2 -tvf DBname_Trigger.sql
After running DBname_Foreignkey.sql and 8DBname_Trigger.sql*, connect to the database using the following command:
db2 connect to dbname(If this command fails due to a rollforward required, run the next command)
db2 'rollforward db dbname to end of logs and complete'
Perform a rebind to provide a quick way to re-create a package. This enables the user to take advantage of a change in the system without a need for the original bind file. For example, if it is likely that a particular SQL statement can take advantage of a newly created index, the
REBINDcommand can be used to re-create the package and also the
REBINDcommand will commit the transaction if auto-commit is enabled.
Reorganize an index or a table. You can reorganize all indexes that are defined on a table by rebuilding the index data into unfragmented, physically contiguous pages. On a data partitioned table, you can reorganize a specific nonpartitioned index on a partitioned table, or you can reorganize all the partitioned indexes on a specific data partition.
db2 connect to dbname DB2 REORGCHK UPDATE STATISTICS ON TABLE ALL db2 terminate
Before enabling a connection between the database and the application, take a backup of the database for reutilization or if required to restore.
This article helps users and database administrators to quickly and efficiently migrate a database from a Linux (little endian) system to an AIX (big endian) system.