470,596 Members | 1,564 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,596 developers. It's quick & easy.

IMPORT with some defaults

Hi!

I have a ASCII file that I need to import with METHOD L, since columns are
fixed length. The problem is that I have more columns in a table that I'm
importing to that there are columns in the file. Is it possible to specify
some default values for columns that are not part of ASCII file? I'd like
that default value to be used in INSERT_UPDATE import option.

Best regards,
Kovi
--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
Nov 6 '06 #1
5 3084
Kovi,

Have you looked at the column mapping in the IMPORT command?
Any column not listed should get the column DEFAULT.
Assuming you have the table for yourself you could just set the default
with ALTER TABLE (even make it a generated column if you wish).
Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/Fo...Forum2006.html
Nov 6 '06 #2
Serge Rielau wrote:
Kovi,

Have you looked at the column mapping in the IMPORT command?
Any column not listed should get the column DEFAULT.
Assuming you have the table for yourself you could just set the default
with ALTER TABLE (even make it a generated column if you wish).
Cheers
Serge
Yes, I was also thinking something like that.
So...
If I have a table TABLE1 as:
CREATE TABLE TABLE1 (
COL1 BIGINT NOT NULL,
COL2 DECIMAL(18, 2),
COL3 VARCHAR(10),
COL4 BIGINT DEFAULT 9)

And IMPORT like:
IMPORT FROM FILE OF ASC MODIFIED BY DATEFORMAT="YYYYMMDD"
TIMEFORMAT="HHMMSS" IMPLIEDDECIMAL STRIPTBLANKS METHOD L (1 5, 6 10) NULL
INDICATORS (0, 0) MESSAGES FILE.MSG INSERT_UPDATE INTO TABLE1(COL1, COL2,
NULL, DEFAULT) ?

I'm not sure this will work.

Best regards,
Kovi

--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
Nov 6 '06 #3
Gregor Kovac wrote:
Hi!

I have a ASCII file that I need to import with METHOD L, since columns are
fixed length. The problem is that I have more columns in a table that I'm
importing to that there are columns in the file. Is it possible to specify
some default values for columns that are not part of ASCII file? I'd like
that default value to be used in INSERT_UPDATE import option.

Best regards,
Kovi
--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
ANother option is to CREATE a separate import TABLE that matches the
file exactly. Import into that TABLE and then issue a standard INSERT
statement from that TABLE into the real TABLE. That should give you
more control over the process.

B.

Nov 6 '06 #4
Brian Tkatch wrote:
Gregor Kovac wrote:
>Hi!

I have a ASCII file that I need to import with METHOD L, since columns
are fixed length. The problem is that I have more columns in a table that
I'm importing to that there are columns in the file. Is it possible to
specify some default values for columns that are not part of ASCII file?
I'd like that default value to be used in INSERT_UPDATE import option.

Best regards,
Kovi
--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

ANother option is to CREATE a separate import TABLE that matches the
file exactly. Import into that TABLE and then issue a standard INSERT
statement from that TABLE into the real TABLE. That should give you
more control over the process.

B.
Yes, but I don't really like this option. Why? Since there can be INSERTs
and UPDATEs and there are several fields in a primary key I don't want to
write complex SQL statements, if I don't need them (providing Serge gives
me a working IMPORT statement :))) ).

Best regards,
Kovi

--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
Nov 6 '06 #5
Brian Tkatch wrote:
ANother option is to CREATE a separate import TABLE that matches the
file exactly. Import into that TABLE and then issue a standard INSERT
statement from that TABLE into the real TABLE. That should give you
more control over the process.
Another alternative (avoiding the additional base table) would be to define
a view comprised only of the columns in question. Add some INSTEAD OF
triggers to the view for INSERT/UPDATE statements to provide the values for
missing columns, and then finally IMPORT into the view.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Nov 6 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

303 posts views Thread by mike420 | last post: by
2 posts views Thread by Scott | last post: by
4 posts views Thread by Zytan | last post: by
5 posts views Thread by George Sakkis | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.