By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,850 Members | 1,771 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,850 IT Pros & Developers. It's quick & easy.

Import does not complain about missing column.

P: n/a

Folks,

Today, I was exporting a table in one database and then importing it in
another database. The table in destination database was missing one column
(my mistake while creating the table), but import did not complain about it.
Source table:

Column Type Type
name schema name Length Scale Nulls
---------------- --------- ------------------ -------- ----- -----
PROJECT_ID SYSIBM CHARACTER 14 0 No
DESCRIPTION SYSIBM CHARACTER 40 0 No
CONTRACT_ID SYSIBM CHARACTER 10 0 No
STATUS SYSIBM CHARACTER 10 0 No
ESTIMATE SYSIBM INTEGER 4 0 No
Export command:

db2 "export to project.ixf of ixf select * from project"

Destination table:

Column Type Type
name schema name Length Scale Nulls
---------------- --------- ------------------ -------- ----- -----
PROJECT_ID SYSIBM CHARACTER 14 0 No
DESCRIPTION SYSIBM CHARACTER 40 0 No
CONTRACT_ID SYSIBM CHARACTER 10 0 No
STATUS SYSIBM CHARACTER 10 0 No
Import command:

db2 "import from project.ixf of ixf replace into project"
Why does Import command not display error or warning about missing column.
--
Hemant Shah /"\ ASCII ribbon campaign
E-mail: No************@xnet.com \ / ---------------------
X against HTML mail
TO REPLY, REMOVE NoJunkMail / \ and postings
FROM MY E-MAIL ADDRESS.
-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
I haven't lost my mind, Above opinions are mine only.
it's backed up on tape somewhere. Others can have their own.
Feb 6 '07 #1
Share this Question
Share on Google+
6 Replies


P: n/a
Why should it complain? You gave import valid data for the (first) four
columns in the target table and some extra trash in the input record for
each row to be loaded. If you'd let import create the table (possible
with ixf), then you would have had all of the columns. How can import
determine that the missing column in the target table was an error?
Maybe that's what you wanted to do.

Phil Sherman
Hemant Shah wrote:
Folks,

Today, I was exporting a table in one database and then importing it in
another database. The table in destination database was missing one column
(my mistake while creating the table), but import did not complain about it.
Source table:

Column Type Type
name schema name Length Scale Nulls
---------------- --------- ------------------ -------- ----- -----
PROJECT_ID SYSIBM CHARACTER 14 0 No
DESCRIPTION SYSIBM CHARACTER 40 0 No
CONTRACT_ID SYSIBM CHARACTER 10 0 No
STATUS SYSIBM CHARACTER 10 0 No
ESTIMATE SYSIBM INTEGER 4 0 No
Export command:

db2 "export to project.ixf of ixf select * from project"

Destination table:

Column Type Type
name schema name Length Scale Nulls
---------------- --------- ------------------ -------- ----- -----
PROJECT_ID SYSIBM CHARACTER 14 0 No
DESCRIPTION SYSIBM CHARACTER 40 0 No
CONTRACT_ID SYSIBM CHARACTER 10 0 No
STATUS SYSIBM CHARACTER 10 0 No
Import command:

db2 "import from project.ixf of ixf replace into project"
Why does Import command not display error or warning about missing column.

Feb 7 '07 #2

P: n/a
I think that load/import should atleast display warning message when
number of columns in the file does not match the number of columns in the
table. It warns about other thing like code page conversion, etc.

What would happen if instead of last column, one of the columns from the
middle was missing?
While stranded on information super highway Phil Sherman wrote:
Why should it complain? You gave import valid data for the (first) four
columns in the target table and some extra trash in the input record for
each row to be loaded. If you'd let import create the table (possible
with ixf), then you would have had all of the columns. How can import
determine that the missing column in the target table was an error?
Maybe that's what you wanted to do.

Phil Sherman
Hemant Shah wrote:
>Folks,

Today, I was exporting a table in one database and then importing it in
another database. The table in destination database was missing one column
(my mistake while creating the table), but import did not complain about it.
Source table:

Column Type Type
name schema name Length Scale Nulls
---------------- --------- ------------------ -------- ----- -----
PROJECT_ID SYSIBM CHARACTER 14 0 No
DESCRIPTION SYSIBM CHARACTER 40 0 No
CONTRACT_ID SYSIBM CHARACTER 10 0 No
STATUS SYSIBM CHARACTER 10 0 No
ESTIMATE SYSIBM INTEGER 4 0 No
Export command:

db2 "export to project.ixf of ixf select * from project"

Destination table:

Column Type Type
name schema name Length Scale Nulls
---------------- --------- ------------------ -------- ----- -----
PROJECT_ID SYSIBM CHARACTER 14 0 No
DESCRIPTION SYSIBM CHARACTER 40 0 No
CONTRACT_ID SYSIBM CHARACTER 10 0 No
STATUS SYSIBM CHARACTER 10 0 No
Import command:

db2 "import from project.ixf of ixf replace into project"
Why does Import command not display error or warning about missing column.

--
Hemant Shah /"\ ASCII ribbon campaign
E-mail: No************@xnet.com \ / ---------------------
X against HTML mail
TO REPLY, REMOVE NoJunkMail / \ and postings
FROM MY E-MAIL ADDRESS.
-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
I haven't lost my mind, Above opinions are mine only.
it's backed up on tape somewhere. Others can have their own.
Feb 7 '07 #3

P: n/a
Ian
Hemant Shah wrote:
I think that load/import should atleast display warning message when
number of columns in the file does not match the number of columns in the
table. It warns about other thing like code page conversion, etc.

What would happen if instead of last column, one of the columns from the
middle was missing?
DB2 wouldn't complain if the data types match. It will only complain if
you're trying to (for example) load character data into a field defined
as INT.

DB2 doesn't know what fields exist in your file and how they map to the
fields in the table. That's what the METHOD N/P modifiers are for.

Even though IXF embeds a table definition, I believe that DB2 only
references this data if you are using the REPLACE_CREATE option for
import or load.

Are you asserting that DB2 should keep track of this for IXF files only?
Or for delimited files, too?


Ian
Feb 7 '07 #4

P: n/a
While stranded on information super highway Ian wrote:
Hemant Shah wrote:
>I think that load/import should atleast display warning message when
number of columns in the file does not match the number of columns in the
table. It warns about other thing like code page conversion, etc.

What would happen if instead of last column, one of the columns from the
middle was missing?

DB2 wouldn't complain if the data types match. It will only complain if
you're trying to (for example) load character data into a field defined
as INT.

DB2 doesn't know what fields exist in your file and how they map to the
fields in the table. That's what the METHOD N/P modifiers are for.

Even though IXF embeds a table definition, I believe that DB2 only
references this data if you are using the REPLACE_CREATE option for
import or load.

Are you asserting that DB2 should keep track of this for IXF files only?
Or for delimited files, too?
I think it should be for both file types. Both filetypes contain enough
information to determine number columns, if the number of columns in the
file do not match the number of columns in the table, it should atleast
display a warning message.
>
Ian
--
Hemant Shah /"\ ASCII ribbon campaign
E-mail: No************@xnet.com \ / ---------------------
X against HTML mail
TO REPLY, REMOVE NoJunkMail / \ and postings
FROM MY E-MAIL ADDRESS.
-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
I haven't lost my mind, Above opinions are mine only.
it's backed up on tape somewhere. Others can have their own.
Feb 8 '07 #5

P: n/a
Hemant Shah wrote:
>Are you asserting that DB2 should keep track of this for IXF files only?
Or for delimited files, too?

I think it should be for both file types. Both filetypes contain enough
information to determine number columns, if the number of columns in the
file do not match the number of columns in the table, it should atleast
display a warning message.
I tend to agree with you. Your best bet is to open a PMR with IBM support,
however.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany
Feb 8 '07 #6

P: n/a
While stranded on information super highway Knut Stolze wrote:
Hemant Shah wrote:
>>Are you asserting that DB2 should keep track of this for IXF files only?
Or for delimited files, too?

I think it should be for both file types. Both filetypes contain enough
information to determine number columns, if the number of columns in the
file do not match the number of columns in the table, it should atleast
display a warning message.

I tend to agree with you. Your best bet is to open a PMR with IBM support,
however.
I will do that. Thanks.
>
--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany
--
Hemant Shah /"\ ASCII ribbon campaign
E-mail: No************@xnet.com \ / ---------------------
X against HTML mail
TO REPLY, REMOVE NoJunkMail / \ and postings
FROM MY E-MAIL ADDRESS.
-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
I haven't lost my mind, Above opinions are mine only.
it's backed up on tape somewhere. Others can have their own.
Feb 8 '07 #7

This discussion thread is closed

Replies have been disabled for this discussion.