Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399

    Unanswered: problem restoring AIX db backup on Linux

    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
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    Quote Originally Posted by Marcus_A View Post
    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?
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  4. #4
    Join Date
    Oct 2004
    Location
    DELHI INDIA
    Posts
    338
    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.

    Jayanta Datta
    DB2 UDB DBA
    IBM India, Global Delivery
    New Delhi

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by MarkhamDBA View Post
    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?
    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).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by JAYANTA_DATTA View Post
    a> Run the entire DDL (from the AIX one --db2look) in the newly created DB (in Linux).
    b> Drop all the FKs.
    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.

  7. #7
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    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
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  8. #8
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    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.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  9. #9
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    Quote Originally Posted by Cougar8000 View Post
    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
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  10. #10
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by MarkhamDBA View Post
    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).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  11. #11
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Quote Originally Posted by Marcus_A View Post
    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;
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  12. #12
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by Cougar8000 View Post
    Why forget federated?
    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).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  13. #13
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Quote Originally Posted by Marcus_A View Post
    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
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  14. #14
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Markham,

    Couple month back Bella posted a good write up on federation. You might want to search for it.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  15. #15
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    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.
    Last edited by MarkhamDBA; 02-02-10 at 15:06.
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •