Export for another instance with different column order

olel · #1 (**permalink**) 12-07-11, 09:53

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

sathyaram_s · #2 (**permalink**) 12-07-11, 16:41

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

olel · #3 (**permalink**) 12-07-11, 17:01

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?

stolze · #4 (**permalink**) 12-07-11, 17:05

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

Marcus_A · #5 (**permalink**) 12-07-11, 17:50

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.

sathyaram_s · #6 (**permalink**) 12-07-11, 18:12

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:~>

Marcus_A · #7 (**permalink**) 12-07-11, 20:11

Quote:

Originally Posted by sathyaram_s

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.

olel · #8 (**permalink**) 12-08-11, 03:02

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.

stolze · #9 (**permalink**) 12-08-11, 15:05

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech