Moving Data Between Systems with Different Endian Formats

You can move data from a database in big-endian format to a database in little-endian format.

Prerequisites

Note: Before you begin, make sure that you have a process for capturing your database and table schema.

The following example loads a table named lineitem and identifies one extract file on UFS (file system) called lineitem_binary.inp.

Check operating system documentation for the maximum file size for your system. For example, an extract file on Sun Solaris x64 has a maximum size of 512GB.

Task

This procedure moves table definitions but does not include migration of database objects, such as stored procedures or events, which you must re-create.

For example, SAP Sybase IQ databases built on Sun64 SPARC systems store binary data in big-endian (most significant byte first) format. Because Sun Solaris x64 is a little-endian system, you cannot upgrade SAP Sybase IQ databases built on Sun64 SPARC with ALTER DATABASE UPGRADE to run on Sun Solaris x64 systems.

To move data for each database across hardware platforms of different endian structures, you must:

Copy the database schema from the source platform (tables, indexes, and so on).
Create a new database on the target platform.
Perform a binary data dump from the source database.
Load data into the new target database.

Activate the extract utility:

SET TEMPORARY OPTION Temp_Extract_Name1 =
'lineitem_binary.inp'

SET TEMPORARY OPTION Temp_Extract_Name2 = ''

Set up a binary extract of the lineitem table:

SET TEMPORARY OPTION Temp_Extract_Binary = 'on'

SET TEMPORARY OPTION Temp_Extract_Swap = 'off'

Place output in the file lineitem_binary.inp:
```
SELECT * FROM lineitem
```

Turn off the extract utility:

SET TEMPORARY OPTION Temp_Extract_Name1 = ''

Create a duplicate of your database on the target system.

Assuming table lineitem as defined below, load the lineitem table as follows:

LOAD TABLE lineitem
( l_orderkey     BINARY WITH NULL BYTE, 
l_partkey        BINARY WITH NULL BYTE, 
l_suppkey        BINARY WITH NULL BYTE, 
l_linenumber     BINARY WITH NULL BYTE,
l_quantity       BINARY WITH NULL BYTE,
l_extendedprice  BINARY WITH NULL BYTE,
l_discount       BINARY WITH NULL BYTE,
l_tax            BINARY WITH NULL BYTE,
l_returnflag     BINARY WITH NULL BYTE,
l_linestatus     BINARY WITH NULL BYTE,
l_shipdate       BINARY WITH NULL BYTE,
l_commitdate     BINARY WITH NULL BYTE,
l_receiptdate    BINARY WITH NULL BYTE,
l_shipinstruct   BINARY WITH NULL BYTE,
l_shipmode       BINARY WITH NULL BYTE,
l_comment        BINARY WITH NULL BYTE )
FROM 'C:\\mydata\\lineitem_binary.inp' 
FORMAT BINARY
STRIP OFF
QUOTES OFF
ESCAPES OFF
PREVIEW ON
BYTE ORDER HIGH;
COMMIT

Note particularly two clauses:

BINARY WITH NULL BYTE is required when loading a binary file.
BYTE ORDER HIGH specifies the byte order from the system where the data originated. The source database in this example is a big-endian platform; therefore, this data requires byte order HIGH. (Little-endian databases require byte order LOW.)

When loading a multiplex database, use absolute (fully qualified) paths in all file names. Do not use relative path names.