Moving data in one endian format to a system with a different endian format [CR 445754]

The following information was omitted from Sybase IQ Installation and Configuration Guide Chapter 3, “Migrating Data from Previous Versions.”

This section documents a procedure for moving data from a database in big-endian format to a database in little-endian format. This procedure moves table definitions but does not include migration of database objects, such as stored procedures or events, which you must recreate.

For example, 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, Sybase IQ databases built on Sun64 SPARC cannot be upgraded 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, etc.).

• Create a new database on the target platform.

• Perform a binary data dump from the source database.

• Load data into the new target database.

The following steps describe this process in detail.

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.

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

1. Activate the extract utility:

   SET TEMPORARY OPTION Temp_Extract_Name1 = 'lineitem_binary.inp' 
   

   SET TEMPORARY OPTION Temp_Extract_Name2 = ''
   

2. Set up a binary extract of the lineitem table:

   SET TEMPORARY OPTION Temp_Extract_Binary = 'on'
   

   SET TEMPORARY OPTION Temp_Extract_Swap = 'off'
   

3. Place output in the file lineitem_binary.inp:

   SELECT * FROM lineitem
   

4. Turn off the extract utility:

   SET TEMPORARY OPTION Temp_Extract_Name1 = '' 
   

5. Create a duplicate of your database on the target system.

6. 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 filenames. Do not use relative pathnames.