https://github.com/famousdraw

Migrating DB2 from little endian Linux to big endian AIX

IBM and AWS: Build a better architecture for your business.Learn more
IBM Developer
 
 

IT Infrastructure

Tutorial

Archived | Migrating DB2 from little endian Linux to big endian AIX

https://developer.ibm.com/tutorials/migrating-db2-from-little-endian-linux-to-big-endian-aix/

Here comes the DB2 export and import utility for cross platform database migration

 

By Chandan Kabat, Ram Gawande
Published 2018年9月27日

Archived content

 

Archive date: 2022-11-22

This content is no longer being updated or maintained. The content is provided “as is.” Given the rapid evolution of technology, some content, steps, or illustrations may have changed.

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.

Endian memory structure

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 7.1.0.0
    • 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:

  1. Generate a Data Definition Language (DDL) file using the db2look command (for example, dbname.sql). The db2look command 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 db2look utility, 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

    Command parameters:
    -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 GRANT statements.
    -f: Extracts the configuration parameters and registry variables that affect the query optimizer.
    -o: Writes the output to the DBname.sql file

    Output example:

      -- 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.

  2. 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.

  3. Export all data from the source server little endian (Linux) using the db2move utility 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.

    db2move <DBname>Export

    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 command:

    • 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 IMPORT or LOAD action.
    • 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.

  4. 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).

  5. 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:

  1. 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>’;

  2. 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

  3. Grant similar level permissions to the connection ID and instance captured in step 4 during export.

  4. 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.

  5. 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

  6. 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'

  7. 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 REBIND command can be used to re-create the package and also the REBIND command will commit the transaction if auto-commit is enabled.

    db2rbind dbname

  8. 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

  9. Before enabling a connection between the database and the application, take a backup of the database for reutilization or if required to restore.

Summary

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.

Legend
 
  • IT Infrastructure
    Databases
    Linux
    IBM AIX
    IBM Power
    IBM Db2 Database
 
 
 
 
----------------------------------------
如果你觉得文章有用，欢迎打赏。

 

 

posted on 2022-12-01 10:29  红色MINI  阅读(75)  评论(0)  编辑  收藏  举报

导航