I am trying to restore db on Linux from backup taken at AIX64 v9.5 and getting this error:

db2 restore db db_aaa from .
SQL2570N An attempt to restore on target OS "Linux-x86-64" from a backup
created on source OS "AIX-64" failed due to the incompatibility of operating
systems or an incorrect specification of the restore command. Reason-code:
"1".

I guess our OSes have different 'endianness'.

Is there any other way to restore a database from another platform besides using db2move? a way too too much hassle...

Please help

Yes, different endianness, so you cannot do it with a backup and restore. db2look and db2move never seem that difficult to me. Trick is to pull out the FK's in the db2look and run them after the data is loaded with db2move.

FKs?

I exported userspace1 and systoolspace tablespaces on AIX. would it be enough to import them on Linux? or should I export other TSs - SYSCATSPACE and SYSTOOLSTMPSPACE - as well? I think I don't have to as I don't have any tables in those 2 TSs in the source database, right?

1> FK --> foreign key.
2> Steps involved:
a> Run the entire DDL (from the AIX one --db2look) in the newly created DB (in Linux).
b> Drop all the FKs.
c> run DB2move IMPORT for all the data-files you got from the DB in AIX.
d> recreate the FKs.

FK's are foreign keys in the db2look. They are all together toward the end of the db2look output.

You should first create a database on the Linux system. This will create the standard tablespaces, DB2 system catalog (with syscat tablespace). When you do the db2look and run them on Linux you would ignore any DDL pertaining to objects that already exist in your new database. That may or may not include systoolspace (you can create it if you want to).

Almost. You can edit the generated db2look file and make 2 copies. The 1st (upperhalve) , containing the creation of tablespaces and tables can be executed. The 2nd (lowerhalve) , containing all the FK contstaints will can run AFTER you've loaded all the data.
The 1st few lines (connect etc) have to be replicated into the 2nd verion of the file.

This way you do not have to drop FK's because they are never created in the 1st place.

We don't use FKs - makes life easier for me

I thought I could use
db2move with 'import REPLACE_CREATE' or
db2move with 'load DDL_AND_LOAD'
to create all objects and load tables. Isn't that true?

Or is it better to create all objects in the target database using DDLs from db2look and then load/import data into tables?

Thanks in advance

use db2look to create objects on Linux. Then set up federation to AIX and use import/load to move data. Much cleaner and easier then exporting/moving/reading then loading. You can even write a quick .sh to do it for you.

I am moving a database from AIX to Linux (not the other way around).

You caught me off guard here. Never worked with anything 'federated' before. Why do I need to set up federation if both databases are DB2? If it has anything to do with XML, we don't use XML data type columns (we store XML data in some-char columns). I did a quick read and could not find any specific commands to set up federation (for an server, an instance or a database only?) Your help will be appreciated.

thanks in advance

Forget about federated.

You should use db2look to create the tables first. Not all table and column attributes are included with REPLACE_CREATE option of db2move (import).

Why forget federated?

AS I have said originally, just like you are now. Use db2look to recreate objects.

Then set up federation between two db and whola. Move data from table to table with out creating files. No fus no mus. No chances of corrupting data while you export/import.

markham

on the target db

1. CREATE SERVER "TOQA"
TYPE DB2/AIX
VERSION '9.1'
WRAPPER "TOQA"
OPTIONS
(DBNAME 'Source_db_name'
);
2. CREATE USER MAPPING FOR ID_THAT_WILL_BE_USED
SERVER "TOQA"
OPTIONS
(REMOTE_AUTHID 'ID_THAT_WILL_BE_USED '
,REMOTE_PASSWORD ''
);
p.s. remote auth and pwd are case sensitive. Type in correctly. PWD will not be stored or visible to others, so no security issues

3. Then run this
db2 -x "select 'CREATE NICKNAME SCHEMA_NAME'||'.'||tabname|| ' FOR TOQA.SOURCE_SCHEMA_NAME' ||'.'||tabname||';' from syscat.tables where tabschema = 'BACKUP_DEC_09' and type = 'T'" >> nickname.ddl

You might need to twick line3, but this should give you an idea.

Then create a script to load from cursor

declare c1 cursor for select * from nickname_schema.tabname;
load from c1 of cursor replace into target_schema.target_table nonrecoverable;

Because it is too much work and db2move is much simpler, faster, and no UOW problems to deal with (too many inserts in one UOW).

True, to a degree. I am not sure why you say that it is too much work. As I see it, it is actually less work.

You also have to have enough file space to hold your data files. Which you do not if you use federation.

You are taking a chance on having data corrupted while you are exporting it, which you do not using federation.

And no need to FTP files if they are on diff boxes.

I have done Billion + record table move this way with out a glitch using LOAD from cursor.

I have done it both ways and federated always worked better for me, but that is just me

Markham,

Couple month back Bella posted a good write up on federation. You might want to search for it.

setting federation seems too complex to me so this is what i did:

db2move on source server/database
db2look on whole source database
tar all created files to files.tar
ftped files.tar and db2look.sql to Linux server
ran db2 -tvf db2look.sql to create all objects in target database (successful)
untar files.tar on Linux
db2move CIBCPROD load -lo INSERT

db2move LOAD worked well for about 300 tables but 4 tables had the problems as follows:
-------------------------------------------
* LOAD: table "DBA "."AB_AAA"
-Rows read: 273
-Loaded: 273
-Rejected: 0
-Deleted: 269
-Committed: 273

SQL3110N The utility has completed processing. "273" rows were read from the input file.
...
SQL3509W The utility has deleted "269" rows from the table.
-----------------------------------------
* LOAD: table "DBA "."AB_BBB"
*** ERROR -3088. Check message file tab3.msg!
*** SQLCODE: -3088 - SQLSTATE:
*** SQL3088N The source column specified to be loaded into database column "1" is not compatible with the database column, but the database column is not nullable.
-------------------------------------------
I checked tables columns definitions/DDLs of source and target tables and they are the same. Will be investigating but any idea what it might be? Can it be the difference in some end-record-mark in the ixf files on Linux and AIX? Difference in some settings between AIX and Linux?

When I created new (target) database on Linux I had to set
using codeset ISO8859-1 territory en_US
otherwise codeset was set to UTF-8 and did not display French text in some tables properly.