Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2009
    Posts
    26

    Unanswered: Export for another instance with different column order

    I have some installations of the same application. Their databases are basicly the same, but the column order in some tables differs.

    I would like to export my data from one database to all the other databases. If I use

    Code:
    EXPORT TO table.ixf OF IXF SELECT * FROM schema.table;
    the import fails because of the different coulmn order.

    I could use

    Code:
    EXPORT TO table.ixf OF IXF SELECT field1, field2, field3 FROM schema.table;
    using the column order of the target database, but then I would have to use a different export script for each target database.

    Is it possible to create an export which could be imported in any target database? In MySQL you could do an export which leads to an SQL script with a lot of inserts. As these inserts are like

    Code:
    INSERT INTO (field1, field2, field3) VALUES ("1", "a", "b")
    the column order doesn't matter.

    Thanks in advance,
    Ole

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    dont know if i understood you right. But, let me try ...

    If your target table tab1 has columns in order col1,col2 and the corresponding source columns arre cola and colb, then

    Code:
    export to a.ixf of ixf select cola,colb from t1
    import from a.ixf of ixf insert into tab1(col1,col2)
    You can have the columns in any source and any target in any order, the above should work

    Thanks

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Oct 2009
    Posts
    26
    Don't know if I understood YOU right, but I try to explain it again.

    My source columns are colA, colB and colC in this order. The target databases are as follows:

    db1: colA, colC, colB
    db2: colB, colA, colC
    db3: colB, colC, colA

    If I do

    Code:
    export to a.ixf of ixf select colA,colB, colC from t1
    I will have to do

    Code:
    import from a.ixf of ixf insert into tab1(colA,colC,colB)
    for db1, but

    Code:
    import from a.ixf of ixf insert into tab1(colB,colA,colC)
    for db2 and

    Code:
    import from a.ixf of ixf insert into tab1(colB,colC,colA)
    for db3 right? That means I have to write different import statements for each database, correct? Is it possible to match the columns by name and not by position maybe on import?

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    If LOAD is an option instead of IMPORT, you may want to have a look at "METHOD N". LOAD - IBM DB2 9.7 for Linux, UNIX, and Windows
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    No matter what method you use, in DB2 you will have to specify the column names either in the export (to change the order) or in the import/load.
    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
    Aug 2001
    Location
    UK
    Posts
    4,650
    hmmm, it is not only 11 pm here. why am I already confused .

    let me try again. sorry if this does not suit your requirement.

    in your example you have "INSERT INTO (field1, field2, field3) VALUES ("1", "a", "b")" which to me means, field1 or source is to be imported to field1 of target irrespective of the column order.

    Code:
    db2inst1@host1:~> db2 "create table source1(field1 int,field2 int, field3 int)"
    DB20000I  The SQL command completed successfully.
    db2inst1@host1:~> db2 "create table target1(field3 int,field2 int, field1 int)"
    DB20000I  The SQL command completed successfully.
    db2inst1@host1:~> db2 "create table target2(field1 int,field3 int, field2 int)"
    DB20000I  The SQL command completed successfully.
    db2inst1@host1:~> db2 "insert into source1 values(1,2,3)"                                                        DB20000I  The SQL command completed successfully.
    DB20000I  The SQL command completed successfully.
    db2inst1@host1:~> db2 "select * from source1"
    
    FIELD1      FIELD2      FIELD3
    ----------- ----------- -----------
              1           2           3
    
      1 record(s) selected.
    
    
    db2inst1@host1:~> db2 "export to source1.ixf of ixf select field1,field2,field3 from source1"
    SQL3104N  The Export utility is beginning to export data to file
    "source1.ixf".
    
    SQL27984W  The export command completed successfully. Some recreate
    information has not been saved to the PC/IXF file during Export. This file
    will not be supported in Import CREATE mode. Reason code="7".
    
    SQL3105N  The Export utility has finished exporting "1" rows.
    
    
    Number of rows exported: 1
    
    db2inst1@host1:~> db2 "import from source1.ixf of ixf insert into target1(field1,field2,field3)"
    SQL3150N  The H record in the PC/IXF file has product "DB2    02.00", date
    "20111207", and time "180554".
    
    SQL3153N  The T record in the PC/IXF file has name "source1.ixf", qualifier "",
    and source "            ".
    
    SQL3109N  The utility is beginning to load data from file "source1.ixf".
    
    SQL3110N  The utility has completed processing.  "1" rows were read from the
    input file.
    
    SQL3221W  ...Begin COMMIT WORK. Input Record Count = "1".
    
    SQL3222W  ...COMMIT of any database changes was successful.
    
    SQL3149N  "1" rows were processed from the input file.  "1" rows were
    successfully inserted into the table.  "0" rows were rejected.
    
    
    Number of rows read         = 1
    Number of rows skipped      = 0
    Number of rows inserted     = 1
    Number of rows updated      = 0
    Number of rows rejected     = 0
    Number of rows committed    = 1
    
    db2inst1@host1:~> db2 "import from source1.ixf of ixf insert into target2(field1,field2,field3)"
    SQL3150N  The H record in the PC/IXF file has product "DB2    02.00", date
    "20111207", and time "180554".
    
    SQL3153N  The T record in the PC/IXF file has name "source1.ixf", qualifier "",
    and source "            ".
    
    SQL3109N  The utility is beginning to load data from file "source1.ixf".
    
    SQL3110N  The utility has completed processing.  "1" rows were read from the
    input file.
    
    SQL3221W  ...Begin COMMIT WORK. Input Record Count = "1".
    
    SQL3222W  ...COMMIT of any database changes was successful.
    
    SQL3149N  "1" rows were processed from the input file.  "1" rows were
    successfully inserted into the table.  "0" rows were rejected.
    
    
    Number of rows read         = 1
    Number of rows skipped      = 0
    Number of rows inserted     = 1
    Number of rows updated      = 0
    Number of rows rejected     = 0
    Number of rows committed    = 1
    
    db2inst1@host1:~> db2 "select field1,field2,field3 from source1 union all select field1,field2,field3 from target1 union all select field1,field2,field3 from target2"
    
    FIELD1      FIELD2      FIELD3
    ----------- ----------- -----------
              1           2           3
              1           2           3
              1           2           3
    
      3 record(s) selected.
    
    db2inst1@host1:~>
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by sathyaram_s View Post
    hmmm, it is not only 11 pm here. why am I already confused .

    let me try again. sorry if this does not suit your requirement.

    in your example you have "INSERT INTO (field1, field2, field3) VALUES ("1", "a", "b")" which to me means, field1 or source is to be imported to field1 of target irrespective of the column order.
    He doesn't want to have to code any detail about column names in the export or import/load commands. He wants to export the data with "select * from table-name" and import/load the data into a table that has columns in a different order, and have DB2 automatically figure out that the column order has changed without coding any column names. In theory, for an IXF file, I suppose IBM could have figured this out automatically since the column names are imbedded in the export IXF file and the import could therefore read the column names to know where the data is located on the import file, but I don't think DB2 does it automatically.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    Oct 2009
    Posts
    26
    Marcus got it exactly. Some googling brought me to this IMPORT documentation. If I understand that right it should be possible to let DB2 figure out the column order automatically by comparing the column names using the method N. That should be the same option stolze is referring to for the LOAD command.

  9. #9
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You are assuming that columns with the same name will have the same or at least compatible types. Furthermore, you assume that the semantics are such that values of columns with the same name are identical in different databases. Well, maybe you know that this is the case in your situation - but DB2 cannot make such gross assumptions, of course. This would imply a default behavior that may just be plain wrong in some situations.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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